Oracle DB Stats

Gathering statistics is crucial for optimizing query performance in Oracle databases. In Oracle 19c, there are various options and techniques for gathering statistics, which are especially relevant for an L3 DBA who manages complex databases and performance tuning. Here’s a comprehensive overview of the options and methods available for gathering statistics:

**1. Automatic Statistics Gathering:

DBMS_STATS automatically gathers statistics during maintenance windows. By default, Oracle's auto job in the DBMS_STATS package collects statistics for all objects, including tables, indexes, and partitions.

Automatic Stats Collection Job:
This job is scheduled by default to run during off-peak hours. You can verify and modify its schedule using:
sql
Copy code


SELECT JOB_NAME, ENABLED, LAST_RUN_DATE, NEXT_RUN_DATE

FROM DBA_SCHEDULER_JOBS

WHERE JOB_NAME = 'GATHER_STATS_JOB';


Modify the Job Schedule

You can change the job schedule using the DBMS_SCHEDULER package. For example, to change the job to run at a different time:

sql

Copy code


BEGIN

   DBMS_SCHEDULER.set_attribute(

      name      => 'GATHER_STATS_JOB',

      attribute => 'repeat_interval',

      value     => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0'

   );

END;

/

In this example, the job is set to run daily at 2:00 AM. You can adjust the repeat_interval value to fit your requirements.


**2. Manual Statistics Gathering:

DBMS_STATS provides detailed control over statistics gathering. Here are the key procedures and options:

Gather Statistics for a Specific Table:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    ownname       => 'SCHEMA_NAME',

    tabname       => 'TABLE_NAME',

    estimate_percent => 100, -- Full scan

    cascade       => TRUE,   -- Gather index stats

    degree         => 4      -- Parallelism degree

  );

END;

/


Gather Statistics for All Tables in a Schema:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(

    ownname       => 'SCHEMA_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- Automatic sample size

    cascade       => TRUE,

    degree         => 4

  );

END;

/


Gather Statistics for the Whole Database:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_DATABASE_STATS(

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    cascade          => TRUE,

    degree           => 4

  );

END;

/


**3. Gather Statistics for Specific Indexes:

Gather Statistics for a Single Index:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_INDEX_STATS(

    ownname       => 'SCHEMA_NAME',

    indname       => 'INDEX_NAME',

    degree         => 4

  );

END;

/


Gather Statistics for All Indexes in a Schema:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_INDEX_STATS(

    ownname       => 'SCHEMA_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    cascade       => FALSE,

    degree         => 4

  );

END;

/


**4. Gather Statistics for Partitions:

Gather Statistics for a Specific Partition:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    ownname       => 'SCHEMA_NAME',

    tabname       => 'TABLE_NAME',

    partname      => 'PARTITION_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    cascade       => TRUE,

    degree         => 4

  );

END;

/


Gather Statistics for All Partitions in a Table:
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    ownname       => 'SCHEMA_NAME',

    tabname       => 'TABLE_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    cascade       => TRUE,

    degree         => 4,

    granularity    => 'ALL'

  );

END;

/


**5. Additional Options and Considerations:

Histograms: Gather histograms to understand the distribution of values within columns, which helps in optimizing queries.
sql
Copy code


BEGIN

  DBMS_STATS.GATHER_COLUMN_STATS(

    ownname       => 'SCHEMA_NAME',

    tabname       => 'TABLE_NAME',

    colname       => 'COLUMN_NAME',

    method_opt    => 'FOR ALL COLUMNS SIZE AUTO'

  );

END;

/


Incremental Statistics: Use incremental statistics for partitioned tables to optimize performance during stats gathering.
sql
Copy code


BEGIN

  DBMS_STATS.SET_TABLE_PREFS(

    ownname       => 'SCHEMA_NAME',

    tabname       => 'TABLE_NAME',

    pname         => 'INCREMENTAL',

    pvalue        => 'TRUE'

  );

END;

/


Monitor Statistics Gathering Jobs: Check the status and performance of statistics gathering jobs:
sql
Copy code


SELECT JOB_NAME, STATUS, LAST_RUN_DURATION

FROM DBA_SCHEDULER_JOB_RUN_DETAILS

WHERE JOB_NAME = 'GATHER_STATS_JOB';


**6. Using DBMS_STATS Utility Procedures:

DBMS_STATS provides various procedures to manage statistics collection, including:

DELETE: Delete statistics for objects if they need to be recalculated.
sql
Copy code


BEGIN

  DBMS_STATS.DELETE_TABLE_STATS(

    ownname       => 'SCHEMA_NAME',

    tabname       => 'TABLE_NAME'

  );

END;

/

Summary

As an L3 DBA, you'll use these options to gather and manage statistics based on the database size, workload, and performance requirements. Understanding and properly configuring these options helps ensure optimal query performance and efficient database operations.

SQL Tuning Adviser

To use the SQL Tuning Advisor through SQLPlus, follow these steps to generate and analyze tuning recommendations for a specific SQL query. This example assumes you have SQL access and familiarity with the basic SQLPlus commands.

Scenario: Optimizing a Slow-Running Query

Assumptions:

1. Connect to the Database

Start SQL*Plus and connect to your Oracle database as a user with DBA privileges:

bash

Copy code

sqlplus / as sysdba


2. Identify the SQL Statement

Find the SQL ID of the query you want to tune. You can retrieve this from the V$SQL view if you know the query text:

sql

Copy code

SELECT sql_id, sql_text

FROM v$sql

WHERE sql_text LIKE '%your_query%';


Replace %your_query% with a portion of the actual SQL query you are analyzing.

3. Create a SQL Tuning Task

Create a tuning task for the identified SQL query. You'll need the SQL ID from the previous step.

sql

Copy code


BEGIN

   DBMS_SQLTUNE.CREATE_TUNING_TASK(

      sql_id       => 'your_sql_id',

      scope        => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,

      time_limit    => 60,   -- Time limit in seconds

      task_name     => 'my_sql_tuning_task',

      description   => 'Tuning task for slow-running SQL'

   );

END;

/


4. Execute the SQL Tuning Task

Execute the tuning task to generate recommendations:

sql

Copy code

BEGIN

   DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_sql_tuning_task');

END;

/


5. Check the Status of the Tuning Task

You can check the status of the tuning task to ensure it has completed:

sql

Copy code

SELECT task_name, status, start_time, end_time

FROM DBA_SQLTUNE_TASKS

WHERE task_name = 'my_sql_tuning_task';


6. View the Recommendations

Once the task is complete, retrieve the tuning report which contains recommendations:

sql

Copy code

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') AS report

FROM dual;


7. Implement Recommendations

The tuning report will suggest various optimizations like SQL profiles, indexes, or materialized views. For example, to accept and create a SQL profile based on the recommendations:

sql

Copy code

BEGIN

   DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'my_sql_tuning_task');

END;

/


8. Drop the SQL Tuning Task (Optional)

After implementing the recommendations, you can drop the tuning task to clean up:

sql

Copy code


BEGIN

   DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'my_sql_tuning_task');

END;

/


Summary

This process helps you get actionable insights into improving SQL query performance in your Oracle database.

Difference between Oracle 12c and Oracle 19c

Oracle 12c and Oracle 19c are both versions of Oracle’s database management system, with Oracle 19c being a more advanced and stable release. Here are some of the key differences between Oracle 12c and Oracle 19c:

1. Long-Term Support

2. Multitenant Architecture (Pluggable Databases)

3. Automatic Indexing

4. Data Guard Enhancements

5. JSON Support

6. In-Memory Database Enhancements

7. Partitioning Improvements

8. Performance Tuning Enhancements

9. Machine Learning and Autonomous Features

10. Other Key Enhancements in Oracle 19c

11. Security Enhancements

12. Scalability and High Availability


Summary Table of Key Differences

Feature

Oracle 12c

Oracle 19c

Long-Term Support

Ends in March 2022

Supported until April 2027

Pluggable Databases (PDBs)

1 PDB without cost

3 PDBs without cost

Automatic Indexing

Not available

Available

Data Guard Enhancements

Basic support

Advanced features (DML Redirect, Automatic Failover)

In-Memory Enhancements

Introduced

Enhanced with new features

Partitioning

Available

Hybrid Partitioned Tables, Automatic Partitioning

SQL Tuning

SQL Plan Management, Real-Time ADDM

Automatic SQL Plan Management, Real-Time Statistics

Machine Learning

Not available

Available (AutoML, Integrated into Autonomous DB)

Performance Monitoring

CDB-level AWR snapshots

AWR snapshots at PDB level

Temporary Tables

Regular Temporary Tables

Private Temporary Tables

Sharding

Introduced but complex

Improved scalability and active duplication

Conclusion

Oracle 19c offers several performance, scalability, and usability improvements over Oracle 12c, with key enhancements in automatic management, security, multitenant databases, and machine learning capabilities. If you're using Oracle 12c, upgrading to Oracle 19c ensures better long-term support and access to modern features, especially in environments requiring high availability, optimization, and cloud integration.

ORACLE PATCH

The most common and critical Oracle patch types include:

1. Patch Set Update (PSU)

2. Release Update (RU)

3. Critical Patch Update (CPU)