Optimizer Statistics Advisor In 12.2
APPLIES TO:
Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.
PURPOSE
Goal of this document is to explain briefly about one of the new features in 12.2.0.1 for Optimizer Statistics.
SCOPE
Applicable to DBAs for Oracle Database 12.2 and above only.
DETAILS
Preface
It is well known that inferior statistics cause query performance problems. It is relatively easy to identify stale, out-of-date statistics and missing statistics, but poor quality statistics can be harder to identify: such as inconsistencies between tables and indexes, primary-key/foreign-key relationships and so on.
Inconsistencies in statistics are usually a result of not following recommended approaches, but it is not always easy to strictly adhere to these for a number of reasons. For example, Oracle continuously enhances statistics gathering features but enhancements can be overlooked post-upgrade (a good example is the recommendation to use AUTO_SAMPLE_SIZE rather than fixed percentages). DBAs may use legacy scripts to gather statistics manually so that there is a reluctance to change “proven” procedures. Sometimes statistics gathering can be overlooked and statistics might not be maintained during batch processing and there may be a perceived lack of time in batch windows.
To address these issues, Oracle Database 12.2 includes a new feature called the Optimizer Statistics Advisor. The goal of the advisor is to analyze how statistics are gathered, validate the quality of statistics already gathered and check the status of auto stats gathering (for example, checking for successful completion). To achieve this, it examines the data dictionary with respect to a set of rules. Where exceptions to the rules are found, findings may be generated and these, in turn, may lead to specific recommendations. The advisor will generate a report that lists findings (with the associated “broken” rule), and then list specific recommendations to remedy the situation. Finally, the recommendations can be implemented using a set of actions. Actions can be output in the form of a SQL script or they can be implemented automatically.
Statistics Advisor Framework
Optimizer Statistics Advisor is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks. The advisor task runs automatically in the maintenance window.
However DBAs can also run it on demand and view the advisor report. If the advisor makes recommendations, then in some cases users can run system-generated scripts to implement them.
Optimizer Statistics Advisor inspects how optimizer statistics are gathered.
The advisor automatically diagnoses problems in the existing practices for gathering statistics. The advisor does not gather a new or alternative set of optimizer statistics. The output of the advisor is a report of findings and recommendations, which helps DBAs follow best practices for gathering statistics.
Optimizer statistics play a significant part in determining the execution plan for queries. Therefore, it is critical for the optimizer to gather and maintain accurate and up-to-date statistics. The optimizer provides the DBMS_STATS package, which evolves from release to release, for this purpose. Typically, users develop their own strategies for gathering statistics based on specific workloads, and then use homegrown scripts to implement these strategies.
- Optimizer Statistics Advisor uses the same advisor framework as Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors.
- The Optimizer Statistics Advisor framework stores its metadata in data dictionary and dynamic performance views.
Components
There are four important components of Statistics Advisor: Rules, Findings, Recommendations & Actions.
1. Rule is an Oracle-supplied standard by which Optimizer Statistics Advisor performs its checks. There are 3 classes of rules, they are System, Operation & Object. Rules listed in V$STATS_ADVISOR_RULES.
2. Finding results when Optimizer Statistics Advisor examines the evidence stored in the database using dictionary views and concludes that the rules were not followed.
3. Optimizer Statistics Advisor makes recommendations based on each finding on how to achieve better statistics. The advisor stores the recommendations in DBA_ADVISOR_RECOMMENDATIONS.
4. An Optimizer Statistics Advisor action is a SQL or PL/SQL script that implements recommendations. When feasible, recommendations have corresponding actions. The advisor stores actions in DBA_ADVISOR_ACTIONS
Modes of Operation for Statistics Advisor
Optimizer Statistics Advisor supports both an automated and manual mode.
Automated: The predefined task AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window once per day. The task runs as part of the automatic optimizer statistics collection client. The automated task generates findings and recommendations, but does not implement actions automatically. REPORT_STATS_ADVISOR_TASK Procedure reports the results of an Optimizer Statistics Advisor task.
Manual: Users can create their own task using the DBMS_STATS.CREATE_ADVISOR_TASK function, and then run it at any time using the EXECUTE_ADVISOR_TASK procedure.
Unlike the automated task, the manual task can implement actions automatically. Alternatively, users can configure the task to generate a PL/SQL script, which can be run manually.
Code snippet for manual execution of Statistics Advisor:
v_tname VARCHAR2(128) := ‘adv_task’;
v_ename VARCHAR2(128) := NULL;
v_report CLOB := null;
v_script CLOB := null;
v_implementation_result CLOB;
BEGIN
v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname); /* To create the advisor task */
v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname); /* To execute the task */
v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname); /* To view the task report */
DBMS_OUTPUT.PUT_LINE(v_report);
v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname); /* To implement the recommendations */
END;
To generate a script that contains recommendations for the task adv_task, use the following code:
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘adv_task’;
:b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
END;
Script to print the report for AUTO_STATS_ADVISOR_TASK:
SET LONG 500000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000
SELECT DBMS_STATS.REPORT_ADVISOR_TASK(‘AUTO_STATS_ADVISOR_TASK’,NULL,’TEXT’,’ALL’,’ALL’) AS REPORT FROM DUAL;
Note: This would print the report for the last execution of AUTO_STATS_ADVISOR_TASK that runs every day automatically during the maintenance window.
Script to check the status of AUTO_STATS_ADVISOR_TASK executions:
SELECT EXECUTION_NAME, EXECUTION_END, STATUS FROM DBA_ADVISOR_EXECUTIONS WHERE TASK_NAME = ‘AUTO_STATS_ADVISOR_TASK’ ORDER BY 2;
WikiDBA 