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