8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Optimizer Statistics Advisor in Oracle Database 12c Release 2 (12.2)

The Optimizer Statistics Advisor was introduced in Oracle Database 12c Release 2 (12.2) to help validate the quality of the optimizer statistics and provide corrective actions, should they be necessary. The DBMS_STATS package has been extended to include routines to interact with the advisor.

Optimizer Statistics Advisor Rules
AUTO_STATS_ADVISOR_TASK
Generate a Report
Implement Advisor Recommendations
Manually Execute the Advisor
Configure Advisor Tasks
Considerations

Optimizer Statistics Advisor Rules

The Optimizer Statistics Advisor works through a list of rules provided by Oracle, which represent "best practices" for optimizer statistics. It checks each rule and where necessary generates findings, recommendations and actions involving calls to the DBMS_STATS package to take corrective measures. The list of rules can be displayed using the V$STATS_ADVISOR_RULES view. They may change over time with subsequent patches and releases.

SET LINESIZE 200

COLUMN name FORMAT A35
COLUMN description FORMAT A75

SELECT *
FROM   v$stats_advisor_rules
ORDER BY rule_id;

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

24 rows selected.

SQL>

AUTO_STATS_ADVISOR_TASK

The Optimizer Statistics Advisor runs a task called AUTO_STATS_ADVISOR_TASK during the maintenance window, so by default you don't need to do anything to start seeing advice about your database statistics. Like other advisors, we use the DBA_ADVISOR_EXECUTIONS view to identify the executions of interest.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER= pdb1;

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET LINESIZE 150
COLUMN task_name FORMAT A25
COLUMN execution_name FORMAT A20
COLUMN execution_end FORMAT A20
COLUMN execution_type FORMAT A20

SELECT task_name,
       execution_name,
       execution_start,
       execution_end,
       execution_type,
       status
FROM   dba_advisor_executions
WHERE  task_name = 'AUTO_STATS_ADVISOR_TASK'
AND    execution_end >= SYSDATE-2
ORDER BY 3;

TASK_NAME		  EXECUTION_NAME       EXECUTION_START	    EXECUTION_END	 EXECUTION_TYPE       STATUS
------------------------- -------------------- -------------------- -------------------- -------------------- -----------
AUTO_STATS_ADVISOR_TASK   EXEC_42	       23-AUG-2017 23:00:15 23-AUG-2017 23:00:20 STATISTICS	      COMPLETED
AUTO_STATS_ADVISOR_TASK   EXEC_52	       24-AUG-2017 23:00:27 24-AUG-2017 23:00:40 STATISTICS	      COMPLETED

SQL>

We can use this information to view the advice from the automatic runs, as shown below.

Generate a Report

Since the Optimizer Statistics Advisor uses the normal advisor framework, the results of the advisor executions can be viewed in the {CDB|DBA|ALL|USER}_ADVISOR_% views, but there is an easier way to get the results. An Optimizer Statistics Advisor report is generated using the REPORT_ADVISOR_TASK function in the DBMS_STATS package. It accepts the following parameters.

TASK_NAME : Name of the task.
EXECUTION_NAME : Name of the execution. When not specified the latest execution for the task is used. Default NULL.
TYPE : Allowable values are TEXT, HTML or XML. Default TEXT.
SECTION : Allowable values are SUMMARY, FINDINGS, ERRORS or ALL. Combinations are possible using "+" or "-" qualifiers. Default ALL.
LEVEL : Allowable values are BASIC, TYPICAL, ALL or SHOW_HIDDEN. Combinations are possible using "+" or "-" qualifiers. Default TYPICAL.

The following query displays the default report for the latest execution for the AUTO_STATS_ADVISOR_TASK task.

SET LINESIZE 200
SET LONG 1000000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.report_advisor_task('AUTO_STATS_ADVISOR_TASK') AS REPORT 
FROM   dual;

GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name	 : AUTO_STATS_ADVISOR_TASK
 Execution Name  : EXEC_52
 Created	 : 01-26-17 02:43:30
 Last Modified	 : 08-24-17 11:00:40
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_52 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
 has 1 finding(s). The findings are related to the following rules:
 AVOIDSTALESTATS. Please refer to the finding section for detailed information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:	    AvoidStaleStats
 Rule Description:  Avoid objects with stale or no statistics
 Finding:  There are 1 object(s) with stale statistics.
 Schema:
 CTXSYS
 Objects:
 DR$DBO

 Recommendation:  Regather statistics on objects with stale statistics.
 Example:
 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Stale statistics or no statistics will result in bad plans.
----------------------------------------------------
-------------------------------------------------------------------------------

SQL>

Implement Advisor Recommendations

The Optimizer Statistics Advisor recommendations can be implemented directly using the IMPLEMENT_ADVISOR_TASK function in the DBMS_STATS package. It accepts the following parameters.

TASK_NAME : Name of the task.
EXECUTION_NAME : Name of the execution. When not specified the latest execution for the task is used. Default NULL.
LEVEL : Allowable values are ALL or TYPICAL. Default TYPICAL.

The following code implements the recommendations and displays the result.

VARIABLE v_result CLOB

-- Implement recommendations.
DECLARE
  l_task_name  VARCHAR2(32767);
BEGIN
  l_task_name := 'AUTO_STATS_ADVISOR_TASK';
  :v_result := DBMS_STATS.implement_advisor_task(l_task_name);
END;
/


-- Display results.
SET LONG 10000

SELECT XMLTYPE(:v_result) AS results
FROM   dual;

RESULTS
--------------------------------------------------------------------------------
<implementation_results>
  <rule NAME="AVOIDSTALESTATS">
    <implemented>yes</implemented>
  </rule>
</implementation_results>

SQL>

We can see the statistics have just been analyzed.

COLUMN last_analyzed FORMAT A20
COLUMN stale_stats FORMAT A15

SELECT last_analyzed,
       stale_stats
FROM   dba_tab_statistics
WHERE  owner      = 'CTXSYS'
AND    table_name = 'DR$DBO';

LAST_ANALYZED	     STALE_STATS
-------------------- ---------------
25-AUG-2017 16:11:31 NO

SQL>

Alternatively the Optimizer Statistics Advisor recommendations can be turned into a script using the SCRIPT_ADVISOR_TASK function. It accepts the following parameters.

TASK_NAME : Name of the task.
EXECUTION_NAME : Name of the execution. When not specified the latest execution for the task is used. Default NULL.
DIR_NAME : If the directory name is specified, the function returns the script as a CLOB and writes it to a file in the specified directory. If not specified, the script is only present in the return value of the function. Default NULL.
LEVEL : Allowable values are ALL or TYPICAL. Default TYPICAL.

The following query displays the default script for the latest execution for the AUTO_STATS_ADVISOR_TASK task.

SET LINESIZE 200
SET LONG 1000000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.script_advisor_task('AUTO_STATS_ADVISOR_TASK')
FROM dual;
-- Script generated for the recommendations from execution EXEC_52
-- in the statistics advisor task AUTO_STATS_ADVISOR_TASK
-- Script version 12.2

-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.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 USEDEFAULTPARAMS.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 USECONCURRENT
-- Rule Description: Use Concurrent preference for Statistics Collection

-- Scripts for rule USEDEFAULTPREFERENCE
-- Rule Description: Use Default Preference for Stats Collection
-- Set global preferenes to default values.

-- 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 level preference

-- 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.

-- 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 objcts that are missing or have no statistics.
declare
obj_filter_list dbms_stats.ObjectTab;
obj_filter dbms_stats.ObjectElem;
obj_cnt number := 0;
begin
obj_filter_list := dbms_stats.ObjectTab();
obj_filter.ownname := 'CTXSYS';
obj_filter.objtype := 'TABLE';
obj_filter.objname := 'DR$DBO';
obj_filter_list.extend();
obj_cnt := obj_cnt + 1;
obj_filter_list(obj_cnt) := obj_filter;
dbms_stats.gather_database_stats(
obj_filter_list=>obj_filter_list);
end;
/

SQL>

Manually Execute the Advisor

There are a number of procedures and functions in the DBMS_STATS package that allow the advisor to be run manually, some of which are demonstrated below.

We create and populate a table, making sure there are no statistics. This will give us something for the advisor to find.

CONN test/test@pdb1

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE INDEX t1_desc ON t1(description);

INSERT INTO t1
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

EXEC DBMS_STATS.delete_table_stats('TEST', 'T1');

We create a new task and execute it using the CREATE_ADVISOR_TASK and EXECUTE_ADVISOR_TASK functions respectively. These optionally accept TASK_NAME and EXECUTION_NAME parameters. If they aren't specified system-generated names are created. We then generate the report associated with this task.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

SET SERVEROUTPUT ON
DECLARE
  l_task_name  VARCHAR2(32767);
  l_exec_name  VARCHAR2(32767);
  l_report     CLOB;
BEGIN
  l_task_name := DBMS_STATS.create_advisor_task;
  l_exec_name := DBMS_STATS.execute_advisor_task(task_name => l_task_name);
  l_report    := DBMS_STATS.report_advisor_task(
                   task_name      => l_task_name,
                   execution_name => l_exec_name);
  DBMS_OUTPUT.put_line(l_report);
END;
/
GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name	 : TASK_16
 Execution Name  : EXEC_75
 Created	 : 08-26-17 09:42:24
 Last Modified	 : 08-26-17 09:42:35

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_75 of task
TASK_16, the Statistics Advisor has 2
 finding(s). The findings are related to the following rules: USEDEFAULTPARAMS,
 AVOIDSTALESTATS. Please refer to the finding section for detailed
information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:
UseDefaultParams
 Rule Description:  Use Default Parameters in Statistics Collection Procedures
 Finding:  There are 1 statistics operation(s) using
nondefault parameters.
 Operation:

delete_table_stats(ownname=>'TEST', tabname=>'T1', cascade_parts=>TRUE, cascade_columns=>TRUE, cascade_indexes=>TRUE, stattype=>'ALL');

 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;
 -- Also the non default parameters can be overriden by setting
 'PREFERENCE_OVERRIDES_PARAMETER' preference.

 -- Overriding non default parameters and preferences for all tables in the
 system and to use dbms_stats for gathering statistics:
 begin
dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE');
 end;
 -- Overriding non default parameters and
preferences for 'SH.SALES':
 begin dbms_stats.set_table_prefs('SH','SALES',
 'PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end;

 Rationale:  Using default parameter values for statistics gathering operations
	     is more efficient.

----------------------------------------------------
 Rule Name:	    AvoidStaleStats
 Rule Description:  Avoid objects with stale or no statistics

 Finding:  There are 1 object(s) with no statistics.
 Schema:
 TEST
 Objects:
 T1

 Recommendation:  Gather Statistics on those objects with no statistics.

 Example:
 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec
dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Stale statistics or no statistics will result in bad plans.

----------------------------------------------------
-------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>

We can see the advisor has noticed the table with no statistics. We could now implement the recommendations as we have done previously.

Configure Advisor Tasks

There are a number of procedures and functions in the DBMS_STATS package that alter the way the advisor works by filtering out objects and advice. There are examples of their usage in the documentation, but I can't imagine myself using them very much. I would rather see the output for all the potentially problematic objects.

Considerations

The Optimizer Statistics Advisor findings and actions work fine for many databases, but like all automated features you must still engage your brain. We have one old 3rd party application that reacts very badly to statistics being gathered "properly". Instead we have to take a very "old fashioned" approach, since this is the only way the queries will give a consistent performance. For systems like this we would definitely ignore statistics advisor suggestions, or risk terrible performance.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.