12c Release 2 New Feature : Statistics Advisor

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

PURPOSE

To explain the new feature Statistics Advisor and how to use it.

SCOPE

This applies to all DBAs who manage Oracle Databases.

DETAILS

The optimizer cost based model relies on statistics collected about the objects involved in a query, and the database and host where the query runs.

The optimizer uses statistics to get an estimate of the number of rows (and number of bytes) retrieved from a table, partition, or index. The optimizer estimates the cost for the access, determines the cost for possible plans, and then picks the execution plan with the lowest cost. Thus, 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.

While these scripts are usually tested and reviewed, however, the owner of the suboptimal legacy scripts may not change them for fear of causing plan changes, hence the traditional script-based approach is proven to be problematic.  Some of these problems are discussed under Problems with a Traditional Script-Based Approach.

So, whether the statistics were collected automatically and/or manually, it is well known that inferior statistics cause query performance problems.  While, it is easy to identify stale, out-of-date statistics and missing statistics, it can be hard to identify poor quality statistics: 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. It uses the same advisor framework as Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors.  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. It 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. 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 and Actions.

2. 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.
   SQL> SET LINESIZE 208
   SET PAGESIZE 100
   COL ID FORMAT 99
   COL NAME FORMAT a33
   COL DESCRIPTION FORMAT a75SQL> SQL> SQL> SQL>
   SQL>
   SQL> SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION
   FROM V$STATS_ADVISOR_RULES
   ORDER BY RULE_ID;

   ID NAME RULE_TYPE DESCRIPTION
   — ——————————— ——— —————————————————————————
   0 SYSTEM
   1 UseAutoJob SYSTEM Use Auto Job for Statistics Collection
   2 CompleteAutoJob SYSTEM Auto Statistics Gather Job should complete successfully
   3 MaintainStatsHistory SYSTEM Maintain Statistics History
   4 UseConcurrent SYSTEM Use Concurrent preference for Statistics Collection
   5 UseDefaultPreference SYSTEM Use Default Preference for Stats Collection
   6 TurnOnSQLPlanDirective SYSTEM SQL Plan Directives should not be disabled
   7 AvoidSetProcedures OPERATION Avoid Set Statistics Procedures
   8 UseDefaultParams OPERATION Use Default Parameters in Statistics Collection Procedures
   9 UseGatherSchemaStats OPERATION Use gather_schema_stats procedure
   10 AvoidInefficientStatsOprSeq OPERATION Avoid inefficient statistics operation sequences
   11 AvoidUnnecessaryStatsCollection OBJECT Avoid unnecessary statistics collection
   12 AvoidStaleStats OBJECT Avoid objects with stale or no statistics
   13 GatherStatsAfterBulkDML OBJECT Do not gather statistics right before bulk DML
   14 LockVolatileTable OBJECT Statistics for objects with volatile data should be locked
   15 UnlockNonVolatileTable OBJECT Statistics for objects with non-volatile should not be locked
   16 MaintainStatsConsistency OBJECT Statistics of dependent objects should be consistent
   17 AvoidDropRecreate OBJECT Avoid drop and recreate object seqauences
   18 UseIncremental OBJECT Statistics should be maintained incrementally when it is beneficial
   19 NotUseIncremental OBJECT Statistics should not be maintained incrementally when it is not beneficial
   20 AvoidOutOfRange OBJECT Avoid Out of Range Histogram endpoints
   21 UseAutoDegree OBJECT Use Auto Degree for statistics collection
   22 UseDefaultObjectPreference OBJECT Use Default Object Preference for statistics collection
   23 AvoidAnalyzeTable OBJECT Avoid using analyze table commands for statistics collection

   24 rows selected.

The rules embody Oracle best practices based on the current feature set. If the best practices change from release to release, then the Optimizer Statistics Advisor rules also change.

The advisor organizes rules into the following classes:

System: This class checks the preferences for statistics collection, status of the automated statistics gathering job, use of SQL plan directives, and so on. Rules in this class have the value SYSTEM in V$STATS_ADVISOR_RULES.RULE_TYPE.

Operation: This class checks whether statistics collection uses the defaults, test statistics are created using the SET_*_STATS procedures, and so on. Rules in this class have the value OPERATION in V$STATS_ADVISOR_RULES.RULE_TYPE.

Object: This class checks for the quality of the statistics, staleness of statistics, unnecessary collection of statistics, and so on. Rules in this class have the value OBJECT in V$STATS_ADVISOR_RULES.RULE_TYPE.

The rules check for the following problems:

How to gather statistics: For example, one rule might specify the recommended setting for an initialization parameter. Another rule might specify that statistics should be gathered at the schema level.

When to gather statistics: For example, the advisor may recommend that the maintenance window for the automatic statistics gathering job should be enabled, or that the window should be extended.

How to improve the efficiency of statistics gathering: For example, a rule might specify that default parameters should be used in DBMS_STATS, or that statistics should not be set manually.

Note:- In V$STATS_ADVISOR_RULES, each rule has a unique string ID that is usable in the DBMS_STATS procedures and reports. You can use a rule filter to specify rules that Optimizer Statistics Advisor should check. However, you cannot write new 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.
   SET LINESIZE 3000
   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;

   GENERAL INFORMATION
   ——————————————————————————-
   Task Name : AUTO_STATS_ADVISOR_TASK
   Execution Name : EXEC_136
   Created : 09-05-16 02:52:34
   Last Modified : 09-05-16 12:31:24
   ——————————————————————————-
   SUMMARY
   ——————————————————————————-
   For execution EXEC_136 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
   has 4 findings. The findings are related to the following rules:
   AVOIDSETPROCEDURES, USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, NOTUSEINCREMENTAL.
   Please refer to the finding section for detailed information.

   ——————————————————————————-
   FINDINGS
   ——————————————————————————-
   Rule Name: AvoidSetProcedures
   Rule Description: Avoid Set Statistics Procedures
   Finding: There are 5 SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures being
   used for statistics gathering.
   Recommendation: Do not use SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures.
   Gather statistics instead of setting them.
   Rationale: SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS will cause bad plans due to
   wrong or inconsistent statistics.
   —————————————————-
   Rule Name: UseDefaultParams
   Rule Description: Use Default Parameters in Statistics Collection Procedures
   Finding: There are 367 statistics operations using nondefault parameters.
   Recommendation: Use default parameters for statistics operations.
   Example:

   — Gathering statistics for ‘SH’ schema using all default parameter values:
   BEGIN dbms_stats.gather_schema_stats(‘SH’); END;
   Rationale: Using default parameter values for statistics gathering operations
   is more efficient.
   —————————————————-
   Rule Name: UseGatherSchemaStats
   Rule Description: Use gather_schema_stats procedure
   Finding: There are 318 uses of GATHER_TABLE_STATS.
   Recommendation: Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
   Example:

   — Gather statistics for ‘SH’ schema:
   BEGIN dbms_stats.gather_schema_stats(‘SH’); END;
   Rationale: GATHER_SCHEMA_STATS has more options available, including checking
   for staleness and gathering statistics concurrently. Also it is
   more maintainable for new tables added to the schema. If you only
   want to gather statistics for certain tables in the schema, specify
   them in the obj_filter_list parameter of GATHER_SCHEMA_STATS.
   —————————————————-
   Rule Name: NotUseIncremental

   Rule Description: Statistics should not be maintained incrementally when it is not
   Finding: Incremental option has been turned on for 10 tables, which will not benefit
   from using the incremental option.
   Schema:
   SH
   Objects:
   CAL_MONTH_SALES_MV
   CAL_MONTH_SALES_MV
   CHANNELS
   COUNTRIES
   CUSTOMERS
   DIMENSION_EXCEPTIONS
   FWEEK_PSCAT_SALES_MV
   FWEEK_PSCAT_SALES_MV
   PRODUCTS
   PROMOTIONS
   SUPPLEMENTARY_DEMOGRAPHICS
   TIMES

   Recommendation: Do not use the incremental option for statistics gathering on these objects.
   Example:
   —
   Turn off the incremental option for ‘SH.SALES’:
   dbms_stats.set_table_prefs(‘SH’, ‘SALES’, ‘INCREMENTAL’, ‘FALSE’);
   Rationale: The overhead of using the incremental option on these tables
   outweighs the benefit of using the incremental option.

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
   VARIABLE b_script CLOB
   DECLARE
   v_tname VARCHAR2(32767);
   BEGIN
   v_tname := ‘my_task’;
   :b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
   END;
   /

   DECLARE
   v_len NUMBER(10);
   v_offset NUMBER(10) :=1;
   v_amount NUMBER(10) :=10000;
   BEGIN
   v_len := DBMS_LOB.getlength(:b_script);
   WHILE (v_offset < v_len)
   LOOP
   DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:b_script,v_amount,v_offset));
   v_offset := v_offset + v_amount;
   END LOOP;
   END;
   /

   — Script generated for the recommendations from execution EXEC_23
   — in the statistics advisor task OPT_ADV_TASK1
   — Script version 12.2

   — No scripts will be provided for the rule AVOIDSETPROCEDURES. Please check the report
   — for more details.
   — No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please check the
   — report for more details.
   — No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please check
   — the report for more details.
   — No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the
   — report for more details.
   — No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDSETPROCEDURES. Please check the report
   — for more details.
   — No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please check the
   — report for more details.
   — No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please check
   — the report for more details.
   — No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the
   — report for more details.
   — No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report
   — for more details.
   — No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report
   — for more details.

   — Scripts for rule USEDEFAULTPARAMS
   — Rule Description: Use Default Parameters in Statistics Collection Procedures
   — Use the default preference value for parameters

   begin dbms_stats.set_global_prefs(‘PREFERENCE_OVERRIDES_PARAMETER’, ‘TRUE’); end;
   /

   — Scripts for rule USEDEFAULTOBJECTPREFERENCE
   — Rule Description: Use Default Object Preference for statistics collection
   — Setting object-level preferences to default values
   — setting CASCADE to default value for object level preference
   — setting ESTIMATE_PERCENT to default value for object level preference
   — setting METHOD_OPT to default value for object level preference
   — setting GRANULARITY to default value for object level preference
   — setting NO_INVALIDATE to default value for object levelpreference

   — Scripts for rule USEINCREMENTAL
   — Rule Description: Statistics should be maintained incrementally when it is beneficial.
   — Turn on the incremental option for those objects for which using incremental is helpful.

   — Scripts for rule UNLOCKNONVOLATILETABLE
   — Rule Description: Statistics for objects with non-volatile should not be locked
   — Unlock statistics for objects that are not volatile.

   — Scripts for rule LOCKVOLATILETABLE
   — Rule Description: Statistics for objects with volatile data should be locked
   — Lock statistics for volatile objects.

   — Scripts for rule NOTUSEINCREMENTAL
   — Rule Description: Statistics should not be maintained incrementally when it is not
   beneficial
   — Turn off incremental option for those objects for which using incremental is not helpful.

   begin dbms_stats.set_table_prefs(‘SH’, ‘CAL_MONTH_SALES_MV’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘CHANNELS’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘COUNTRIES’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘CUSTOMERS’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘DIMENSION_EXCEPTIONS’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘FWEEK_PSCAT_SALES_MV’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘PRODUCTS’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘PROMOTIONS’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘SUPPLEMENTARY_DEMOGRAPHICS’, ‘INCREMENTAL’, ‘FALSE’); end;
   /
   begin dbms_stats.set_table_prefs(‘SH’, ‘TIMES’, ‘INCREMENTAL’, ‘FALSE’); end;
   /

   — Scripts for rule USEAUTODEGREE
   — Rule Description: Use Auto Degree for statistics collection
   — Turn on auto degree for those objects for which using auto degree is helpful.

   — Scripts for rule AVOIDSTALESTATS
   — Rule Description: Avoid objects with stale or no statistics
   — Gather statistics for those objcts that are missing or have no statistics.

   — Scripts for rule MAINTAINSTATSCONSISTENCY
   — Rule Description: Statistics of dependent objects should be consistent
   — Gather statistics for those objcets that are missing or have no statistics.

Modes of Operation for Statistics Advisor: Optimizer Statistics Advisor supports both an automated and manual mode.

Automated Mode: 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.

As for any other task, you can configure the automated task, and generate reports. To learn how to configure the task, please refer to Configuring Automatic Optimizer Statistics Collection. REPORT_STATS_ADVISOR_TASK Procedure reports the results of an Optimizer Statistics Advisor task.  If the report recommends actions, then you can implement the actions manually.

Manual Mode: 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.

Optimizer Statistics Advisor Tasks (Workflow): This section explains the basic workflow for using Optimizer Statistics Advisor. All procedures and functions are in the DBMS_STATS package.

The following figure shows the automatic and manual paths in the workflow. If AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window, then your workflow begins by querying the report. In the manual workflow, you must use PL/SQL to create and execute the tasks. Typically, you perform Optimizer Statistics Advisor steps in the sequence shown in the following table.

This script illustrates a basic Optimizer Statistics Advisor session. It creates a task, executes it, generates a report, and then implements the recommendations.