Assuring the Quality of Optimizer Statistics

Good quality statistics are essential to be able to generate optimal SQL execution plans, but sometimes statistics can be of poor quality and this fact could remain unnoticed. For example, older “inherited” systems might use scripts that are no longer understood by the database administrators and, understandably, there is a reluctance to change them. However, because Oracle continuously enhances statistics gathering features it is possible that best practice recommendations will be neglected.

For these reasons, Oracle Database 18c an advisor called the Optimizer Statistics Advisor to help you to improve the quality of statistics in the database. This diagnostic software analyzes information in the data dictionary, assesses the quality of statistics and discovers how statistics are being gathered. It will report on poor and missing statistics and generate recommendations to resolve these problems.

The principle behind its operation is to apply best-practice Rules to uncover potential problems. These problems are reported as a series of Findings, which in turn can lead to specific Recommendations. Recommendations can be implemented automatically using Actions (either immediately or via an auto-generated script to be executed by the database administrator).

The advisor task runs automatically in the maintenance window, but it can also be run on demand. The HTML or text report generated by the advisor can be viewed at any time and the actions can be implemented at any time.

The advisor task gathers and stores data in the data dictionary. It is a low performance overhead operation because it performs an analysis of optimizer statistics and statistics gathering information (that’s already held in the data dictionary). It does not perform a secondary analysis of data stored in application schema objects.

Once the task is complete, the report can be generated in HTML or text format and an action (SQL) script can be created too.

It is a simple matter to view the report generated by the automated task:

select dbms_stats.report_advisor_task('auto_stats_advisor_task') as report from dual;

Alternatively, users with the ADVISOR privilege can execute the task manually and report on the results using the following three-step process:

DECLARE
   tname   VARCHAR2(32767) := 'demo';   -- task name
BEGIN
   tname := dbms_stats.create_advisor_task(tname);
END;
/
DECLARE
   tname   VARCHAR2(32767) := 'demo';   -- task name
   ename   VARCHAR2(32767) := NULL;     -- execute name
BEGIN
   ename := dbms_stats.execute_advisor_task(tname);
END;
/  
SELECT dbms_stats.report_advisor_task('demo') AS report  
FROM dual;

The actions generated by the advisor can be implemented immediately:

DECLARE
    tname            VARCHAR2 (32767) := 'demo'; -- task name
    impl_result      CLOB;                       -- report of  
                                                    implementation
BEGIN
    impl_result := dbms_stats.implement_advisor_task(tname);
END;
/

In addition, Oracle Database 18c Real Application Testing includes useful performance assurance features such as SQL Performance Advisor Quick Check.