The Optimizer Statistics Advisor is an essential tool in Oracle 19c for maintaining efficient database performance. This advisor evaluates the quality of optimizer statistics and suggests improvements to enhance query execution. For those keen on mastering database optimization, understanding the functionalities of the Optimizer Stats Advisor is crucial.
About Optimizer Statistics Advisor
The Optimizer Stats Advisor provides comprehensive diagnostics on how statistics are gathered and maintained in your Oracle database. This tool is integrated into Oracle 19c to ensure that your optimizer statistics are accurate and up-to-date, thereby optimizing the execution plans of your queries.
Purpose of Optimizer Statistics Advisor
The primary goal of the Optimizer Statistics Advisor is to inspect and analyze the methods used for gathering statistics in your database. By identifying and diagnosing issues in the current statistics gathering processes, the advisor generates a report with findings and recommendations. This process helps in aligning your practices with the best standards for statistics collection.
Problems with Traditional Script-Based Approach
Traditional methods of gathering statistics often involve scripts that may not keep pace with the latest best practices. These scripts can lead to several issues:
- Outdated Practices: Legacy scripts might not incorporate new enhancements introduced in recent Oracle releases.
- Unnecessary Statistics Collection: Scripts may gather statistics more frequently than necessary, wasting resources.
- Inaccurate Statistics: Automatic jobs may not run due to misconfigurations, leading to stale or missing statistics.
Advantages of Optimizer Statistics Advisor
The Optimizer Stats Advisor offers a scalable and maintainable solution for managing optimizer statistics:
- Up-to-Date Recommendations: Encodes best practices that evolve with each Oracle release.
- Easy-to-Understand Reports: Provides clear findings and actionable recommendations.
- Script Implementation: Supplies scripts for implementing recommendations without altering application code.
- Integration with Existing Tools: Works seamlessly with SQL Tuning Advisor and AWR.
Components of Optimizer Statistics Advisor
The Optimizer Stats Advisor framework uses a combination of data dictionary and dynamic performance views to manage its metadata. The key components include rules, findings, recommendations, and actions.
Rules for Optimizer Statistics Advisor
Rules are the standards used by the advisor to evaluate the quality of statistics. These rules fall into three categories:
- System Rules: Check the preferences and status of automated statistics jobs.
- Operation Rules: Evaluate the methods and parameters used in statistics collection procedures.
- Object Rules: Assess the quality and relevance of statistics for specific database objects.
Example of Listing Rules
SET LINESIZE 208
SET PAGESIZE 100
COL ID FORMAT 99
COL NAME FORMAT a33
COL DESCRIPTION FORMAT a62
SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION
FROM V$STATS_ADVISOR_RULES
WHERE RULE_ID BETWEEN 1 AND 12
ORDER BY RULE_ID;
Findings for Optimizer Stats Advisor
Findings are the results generated when the advisor detects deviations from the established rules. Each finding corresponds to a specific rule violation and includes details on the issue and its impact.
Recommendations for Optimizer Stats Advisor
Based on the findings, the advisor offers recommendations for improving statistics collection practices. Each recommendation comes with a rationale explaining its importance and benefits.
Actions for Optimizer Statistics Advisor
Actions are the SQL or PL/SQL scripts provided by the advisor to implement the recommended changes. These scripts are designed to be executed directly or modified as needed.
📢 You might also like: Oracle 19c SQL Access to Tune SQL Statements (Category: Performance Management and Tuning)
Operational Modes
The Optimizer Stats Advisor operates in both automated and manual modes.
Automated Mode
In this mode, the predefined task AUTO_STATS_ADVISOR_TASK runs daily during the maintenance window. This task generates findings and recommendations but does not implement actions automatically.
Manual Mode
Users can manually create and execute tasks using the DBMS_STATS package. This mode allows for greater control and customization of the advisor’s operations.
Basic Tasks for Using Optimizer Statistics Advisor
Creating an Optimizer Stats Advisor Task
To create a task, use the DBMS_STATS.CREATE_ADVISOR_TASK function.
DECLARE
v_tname VARCHAR2(128);
BEGIN
v_tname := DBMS_STATS.CREATE_ADVISOR_TASK('my_task');
END;
/
Configuring Filters
Filters can be applied to include or exclude specific objects, rules, or operations.
Creating an Object Filter
DECLARE
report CLOB;
BEGIN
report := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
task_name => 'my_task',
stats_adv_opr_type => 'EXECUTE',
ownname => 'HR',
tabname => '%',
action => 'ENABLE'
);
END;
/
Creating a Rule Filter
DECLARE
report CLOB;
BEGIN
report := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
task_name => 'my_task',
stats_adv_opr_type => 'EXECUTE',
rule_name => 'AvoidStaleStats',
action => 'DISABLE'
);
END;
/
Executing an Optimizer Statistics Advisor Task
To execute the task, use the DBMS_STATS.EXECUTE_ADVISOR_TASK function.
DECLARE
v_ename VARCHAR2(128);
BEGIN
v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK('my_task');
END;
/
Generating a Report
Generate a report using the DBMS_STATS.REPORT_ADVISOR_TASK function.
SET LONG 1000000
SET PAGESIZE 0
SELECT DBMS_STATS.REPORT_ADVISOR_TASK('my_task', 'EXEC_1', 'TEXT', 'ALL', 'ALL') AS report
FROM DUAL;
Implementing Optimizer Statistics Recommendations
Generating a Script for Manual Implementation
Generate a script with DBMS_STATS.SCRIPT_ADVISOR_TASK for manual review and execution.
DECLARE
script CLOB;
BEGIN
script := DBMS_STATS.SCRIPT_ADVISOR_TASK('my_task');
END;
/
Printing the Script
Print the generated script for review.
DECLARE
v_len NUMBER(10);
v_offset NUMBER(10) := 1;
v_amount NUMBER(10) := 10000;
BEGIN
v_len := DBMS_LOB.getlength(:script);
WHILE (v_offset < v_len)
LOOP
DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:script, v_amount, v_offset));
v_offset := v_offset + v_amount;
END LOOP;
END;
/
Implementing All Recommendations
Use DBMS_STATS.IMPLEMENT_ADVISOR_TASK to automatically implement all recommendations.
DECLARE
result CLOB;
BEGIN
result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK('my_task', level => 'ALL');
END;
/
Conclusion
Oracle 19c’s Optimizer Statistics Advisor is a powerful tool for ensuring your database runs efficiently. By using this advisor, you can maintain accurate and up-to-date optimizer statistics, resulting in better execution plans and overall database performance. Regular use of the advisor’s recommendations will help you stay aligned with best practices and optimize your database operations effectively.
See more on Oracle’s website!
Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!
