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;

Statistics Gathering for External Tables: Gather statistics for external tables using the same DBMS_STATS procedures as for regular tables.

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:

ANALYZE: Analyze individual objects.

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;

IMPORT/EXPORT: Import or export statistics from one database to another.

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:

You have identified a slow-running SQL query with its SQL ID.
You want to use the SQL Tuning Advisor to get recommendations for optimizing this query.

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;

sql_id: Replace 'your_sql_id' with the actual SQL ID.
scope: DBMS_SQLTUNE.SCOPE_COMPREHENSIVE performs a thorough analysis. Use DBMS_SQLTUNE.SCOPE_BASIC for a quicker, less detailed analysis.
time_limit: Specify the maximum time (in seconds) for the tuning task.
task_name: Provide a unique name for the tuning task.

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

Connect to the database using SQL*Plus.
Identify the SQL ID of the query you want to tune.
Create a SQL Tuning Task using DBMS_SQLTUNE.CREATE_TUNING_TASK.
Execute the task using DBMS_SQLTUNE.EXECUTE_TUNING_TASK.
Check the status of the task.
Retrieve the tuning recommendations with DBMS_SQLTUNE.REPORT_TUNING_TASK.
Implement the recommended changes.
Clean up by dropping the tuning task if no longer needed.

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

Oracle 12c: Introduced in 2013, Oracle 12c had two releases: 12c Release 1 (12.1) and 12c Release 2 (12.2). Oracle 12.2 has limited support until March 2022.
Oracle 19c: Released in 2019, 19c is the terminal release of the 12.2 family and is considered a long-term support (LTS) release. It will have extended support until April 2027.

2. Multitenant Architecture (Pluggable Databases)

Oracle 12c: Multitenant architecture was introduced in Oracle 12c, allowing the use of pluggable databases (PDBs) within a container database (CDB). In 12c, only one PDB was allowed without purchasing the multitenant option.
Oracle 19c: Oracle 19c allows up to three PDBs in a CDB without any additional cost, making it more suitable for customers looking for small-scale multitenant environments.

3. Automatic Indexing

Oracle 12c: Does not support automatic indexing.
Oracle 19c: Introduces Automatic Indexing, which automates the creation, optimization, and management of indexes based on the workload without DBA intervention. This feature is a major performance enhancement in 19c.

4. Data Guard Enhancements

Oracle 12c: Data Guard is available with manual and some automated processes.
Oracle 19c: Enhances Data Guard by adding support for automatic role transitions, automatic block repair, and the DML redirect feature, which allows DML operations on Active Data Guard standby databases.

5. JSON Support

Oracle 12c: JSON support was introduced in 12c for storing and querying JSON data, but it lacked certain advanced features.
Oracle 19c: Improved JSON capabilities such as JSON Merge Patch, JSON Dataguard support, and optimized performance for JSON queries.

6. In-Memory Database Enhancements

Oracle 12c: Introduced the In-Memory option, which allows certain database objects to be stored in-memory for faster access, improving query performance.
Oracle 19c: Oracle 19c enhances the In-Memory functionality by adding In-Memory Hybrid Columnar Compression, automatic population of In-Memory tables, and support for Base Level In-Memory (which is free but with limited capacity).

7. Partitioning Improvements

Oracle 12c: Basic support for table partitioning, allowing better data management and query performance.
Oracle 19c: Introduces Automatic List Partitioning and Hybrid Partitioned Tables, which allows both partitioning and non-partitioned data to coexist in a single table (support for partitions to be stored in external storage systems like Oracle Cloud).

8. Performance Tuning Enhancements

Oracle 12c: Introduces features like SQL Plan Management (SPM) and Real-Time ADDM.
Oracle 19c: Builds on these features with improvements like Automatic SQL Plan Management, Real-Time Statistics, Automatic SQL Plan Baseline evolution, and Quarantine Plans (automatically preventing repeated execution of queries with poor performance).

9. Machine Learning and Autonomous Features

Oracle 12c: No built-in machine learning features.
Oracle 19c: Adds Machine Learning capabilities with AutoML models. It is also integrated into Oracle's Autonomous Database which automates several key database tasks like patching, backups, and tuning.

10. Other Key Enhancements in Oracle 19c

Automatic Database Diagnostic Monitor (ADDM) for Pluggable Databases (PDBs): In 19c, ADDM can now analyze performance in PDBs independently.
Automatic Workload Repository (AWR) at PDB level: In 12c, AWR data was collected only at the CDB level. In 19c, AWR snapshots can be taken at the PDB level, providing more granular performance data.
Private Temporary Tables: Available in 19c, these are temporary tables that exist only for the duration of a session or transaction.
Enhanced Optimizer Statistics: Oracle 19c introduces Real-Time Statistics, where statistics can be collected automatically and dynamically as DML is performed on the table.

11. Security Enhancements

Oracle 12c: Introduced Unified Auditing and Data Redaction.
Oracle 19c: Builds on the security model with Credential-less access for Autonomous Database and enhanced Transparent Data Encryption (TDE). FIPS 140-2 compliance is available out of the box in 19c.

12. Scalability and High Availability

Oracle 12c: Supports sharding for scalability, but configuration was complex.
Oracle 19c: Enhances sharding capabilities, allowing active duplication of shards and providing better scalability and disaster recovery across regions.

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)

Purpose: Includes critical bug fixes and security updates.
Frequency: Quarterly.
Scope: Focuses on security and stability without introducing new features.
Use: Regular maintenance to ensure database security and reliability.
Example: 12.2.0.1.200114 (Patch Set Update for Oracle 12.2).

2. Release Update (RU)

Purpose: Comprehensive patch that includes security updates, bug fixes, and minor feature enhancements.
Frequency: Quarterly.
Scope: Covers a broader range of fixes and performance improvements compared to PSUs.
Use: For databases that need more than just security patches, providing enhanced features and fixes.
Example: 19.8.0.0.0 (Release Update for Oracle 19c).

3. Critical Patch Update (CPU)

Purpose: Focuses purely on addressing security vulnerabilities.
Frequency: Quarterly (aligned with PSU and RU).
Scope: Security-only patches, no new features or general bug fixes.
Use: To fix security issues and safeguard the database.
Example: Security fixes for Oracle databases like 12.2.0.1

Page updated

Google Sites

Report abuse