Management optimizer statistics is crucial for maintaining optimal SQL performance in Oracle 19c. Understanding how to manage these statistics effectively can significantly enhance database performance.
Introduction to Optimizer Statistics
Optimizer statistics provide detailed information about database objects and their usage. These statistics are essential for the Oracle optimizer to make informed decisions about query execution plans. Accurate statistics help the optimizer estimate the number of rows and bytes retrieved from a table, partition, or index, thereby calculating the cost of access and determining the most efficient execution plan.
Types of Optimizer Statistics
Table Statistics
Table statistics include metadata that the optimizer uses to develop execution plans. This metadata covers:
- Number of rows: Essential for estimating cardinality.
- Number of blocks: Helps in determining the base table access cost.
- Average row length: Useful for calculating I/O costs.
- Number of empty data blocks: Important for understanding space utilization.
Example query to retrieve table statistics:
SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER='SH' AND TABLE_NAME='CUSTOMERS';
Column Statistics
Column statistics track information about the values and distribution of data in columns. Key aspects include:
- Number of distinct values (NDV): Crucial for estimating selectivity.
- Number of nulls: Helps in handling NULL values in predicates.
- Data distribution (histograms): Used to understand data skew and improve cardinality estimates.
Index Statistics
Index statistics provide information about the structure and usage of indexes. This includes:
- Number of leaf blocks: Indicates the size of the index.
- Number of levels: Helps in understanding the depth of the B-tree index.
- Index clustering factor: Measures how well-ordered the rows are in relation to the index, impacting the choice between index scans and full table scans.
System Statistics
System statistics describe hardware performance characteristics, such as I/O and CPU performance and utilization. These statistics enable the optimizer to make accurate cost estimates for I/O and CPU resources during query execution.
đŸ“¢ You might also like: Oracle 19c Using the Optimizer Statistics Advisor (Category: Performance Management and Tuning)
Gathering Optimizer Statistics
Oracle 19c provides several methods to gather optimizer statistics, primarily through the DBMS_STATS package. This package allows for flexible and comprehensive statistics collection, including:
- Table and column statistics: Essential for basic optimizer operations.
- Index statistics: Important for index-based query optimization.
- System statistics: Crucial for accurate resource cost estimation.
Automatic Statistics Collection
Oracle 19c features automatic statistics collection, which periodically gathers statistics for all schema objects with missing or stale statistics. This process runs during the maintenance window and helps maintain up-to-date statistics without manual intervention.
Manual Statistics Collection
For scenarios requiring immediate statistics updates or specific customizations, manual statistics collection can be performed using DBMS_STATS procedures. Key procedures include:
- GATHER_TABLE_STATS: Collects statistics for a specified table.
- GATHER_INDEX_STATS: Collects statistics for a specified index.
- GATHER_SYSTEM_STATS: Collects system statistics for CPU and I/O performance.
Gathering table statistics manually:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'CUSTOMERS');
Gather index statistics manually:
EXEC DBMS_STATS.GATHER_INDEX_STATS('SH', 'CUSTOMERS_IDX');
Gathering system statistics manually:
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');
Real-Time Statistics
Oracle 19c introduces real-time statistics, which are gathered dynamically during conventional DML operations. Real-time statistics complement traditional statistics and help the optimizer generate more accurate plans by providing up-to-date data during query execution.
Managing Statistics for Temporary Tables
Temporary tables, such as global and private temporary tables, require special consideration for statistics management. Oracle 19c allows gathering statistics for global temporary tables, which can be shared or session-specific. However, statistics for private temporary tables are not supported.
Global Temporary Tables
Global temporary tables can store intermediate session-private data for a specific duration. Statistics for these tables can be gathered and used to optimize queries that involve them. Depending on the scope, these statistics can be session-specific or shared.
Private Temporary Tables
Private temporary tables are visible only to the session that created them. Due to their temporary nature, statistics for these tables are not gathered or maintained. This limitation requires careful consideration when using private temporary tables in performance-critical applications.
Best Practices for Optimizer Statistics Management
To ensure optimal database performance, consider the following best practices:
- Regularly gather statistics: Use automatic statistics collection or schedule regular manual collections.
- Monitor and update statistics: Regularly check for stale or missing statistics and update them as needed.
- Leverage real-time statistics: Enable real-time statistics for conventional DML operations to keep statistics current.
- Use appropriate sampling methods: Choose the right sampling methods and granularity for your data and workload.
- Utilize histograms: Create histograms for columns with skewed data distributions to improve cardinality estimates.
Monitoring Statistics
Regularly monitoring statistics helps ensure they remain accurate and relevant. Tools like DBA_TAB_STATISTICS and DBA_IND_STATISTICS can be used to check the status and freshness of statistics.
Using Histograms
Histograms are essential for columns with skewed data distributions. They help the optimizer make better cardinality estimates by providing a detailed view of data distribution. Oracle 19c supports several types of histograms, including frequency and height-balanced histograms.
Managing Stale Statistics
Stale statistics can lead to suboptimal execution plans. Oracle provides mechanisms to identify and update stale statistics, ensuring that the optimizer has the most current data. The DBMS_STATS package includes procedures to check for and refresh stale statistics.
Configuring Automatic Optimizer Statistics Collection Using Cloud Control
You can enable and disable all automatic maintenance tasks, including automatic optimizer statistics collection, using Cloud Control.
Steps to configure:
- Access the Database Home page in Cloud Control.
- From the Administration menu, select Oracle Scheduler, then Automated Maintenance Tasks.
- Click Configure on the Automated Maintenance Tasks page.
- In the Task Settings section for Optimizer Statistics Gathering, select either Enabled or Disabled to enable or disable an automated task.
Example to enable the automated task:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE (
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
Configuring High-Frequency Automatic Optimizer Statistics Collection
High-frequency statistics collection complements the standard collection job and occurs every 15 minutes by default. Configure this task using DBMS_STATS.SET_GLOBAL_PREFS.
Example to enable high-frequency collection:
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');
Concurrent Statistics Gathering
Enable concurrent statistics gathering to allow the database to gather optimizer statistics on multiple tables or partitions simultaneously. This can be done using DBMS_STATS.SET_GLOBAL_PREFS.
Example to enable concurrent statistics gathering:
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
END;
/
Conclusion
Effective management of optimizer statistics in Oracle 19c is essential for maintaining high database performance. By understanding the types of statistics, how to gather them, and best practices for their management, you can ensure that the Oracle optimizer has the accurate information it needs to generate efficient execution plans. Regular monitoring and updating of statistics, combined with the use of real-time statistics, will help you optimize query performance and make the most of your Oracle 19c database.
See more on Oracle’s website!
Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!
