Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

The Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1 is something I planned to write about for quite a while. Roy and I include this topic in the New Features section in our Upgrade / Migrate / Consolidate to Oracle Database 12.2 slide deck for some time. This blog post (in German only) by my former team mate Ulrike Schwinn inspired me to post something by myself.

Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

First of all you’ll find the feature documented in the Oracle Database 12.2 Database SQL Tuning Guide.

The advisor task (‘Statistics Advisor‘) runs automatically in the maintenance window. And of course you can also run it on demand. It inspects how optimizer statistics are gathered. The advisor does not gather any optimizer statistics by itself unless you’ll advice it with DBMS_STATS.IMPLEMENT_ADVISOR_TASK. First it generates a report of findings and recommendations helping you to gather statistics following best practices.

The Optimizer Statistics Advisor checks:

• How to gather statistics?
• When to gather statistics?
• How to improve stats gathering?

Rules for the Optimizer Statistics Advisor

Look up the current rule set it is based on in V$STATS_ADVISOR_RULES:

SQL> select RULE_ID, NAME, RULE_TYPE, DESCRIPTION from V$STATS_ADVISOR_RULES order by 1;

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

Once the Optimizer Statistics Advisor finds evidence for rules not followed it will give recommendations. You can trigger this via a Optimizer Statistics Advisor task. This task is running as a sub-task of the Automatic Statistics Gathering automatic maintenance job by default. It analyzes the statistics history stored in the data dictionary, the statistics operation log, and the current statistics footprint that exists in SYSAUX.

The result could be recommendations – and of course the actions to implement these.

Example

This is a very simple testcase with a DBA user in a PDB. I execute some random DML on tables TEST1 and TEST2:

SQL> select inserts, updates, deletes from dba_tab_modifications where table_name in ('TEST1','TEST2');

TABLE_NAME  INSERTS UPDATES DELETES
----------- ------- ------- -------
TEST1       333030  0       9999
TEST2       219075  317123  0

Quick check to see whether the stats on table TEST1 and TEST2 are STALE:

SQL> alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm';
Session altered

SQL> select sysdate from dual;

SYSDATE
-------------------------------
21-Aug-2017 10:42:09am

SQL> select table_name, last_analyzed, stale_stats from dba_tab_statistics where table_name in ('TEST1','TEST2');

TABLE_NAME LAST_ANALYZED		   STA
---------- ------------------------------- ---
TEST1      21-Aug-2017 10:27:32am          YES
TEST2      21-Aug-2017 10:28:09am          YES

Let me check if the Optimizer Statistics Advisor has something to recommend:

SET LONG 1000000
SET LONGCHUNKSIZE 100000 
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000

DECLARE
  v_tname   VARCHAR2(128) := 'TEST_TASK_MIKE';
  v_ename   VARCHAR2(128) := NULL;
  v_report  CLOB := NULL;
  v_script  CLOB := NULL;
BEGIN
  v_tname  := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  v_ename  := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

And this is the result:

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

Task Name : TEST_TASK_MIKE
Execution Name : EXEC_1
Created : 08-21-17 10:47:11
Last Modified : 08-21-17 10:47:19

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
For execution EXEC_1 of task TEST_TASK_MIKE, the Statistics Advisor has 2
finding(s). The findings are related to the following rules: AVOIDSTALESTATS,
UNLOCKNONVOLATILETABLE. 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 2 object(s) with stale statistics.
Schema:
HUGO
Objects:
TEST1
TEST2

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

Rule Name: UnlockNonVolatileTable
Rule Description: Statistics for objects with non-volatile should not be locked
Finding: Statistics are locked on 1 table(s) which are
not volatile.
Schema:
SYS
Objects:
AQ_SRVNTFN_TABLE_1

Recommendation: Unlock the statistics on non-volatile tables, and use gather statistics operations to gather statistics for these tables.
Example:

-- Unlocking statistics for 'SH.SALES':
dbms_stats.unlock_table_stats('SH', 'SALES');
Rationale: Statistics gathering operations will skip locked objects and may lead to stale or inaccurate statistics.

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

Implementing the advisor’s recommendations

Instead of transforming the examples from the report you could simply implement the advisor’s recommendations:

DECLARE
  v_tname   VARCHAR2(128) := 'TEST_TASK_MIKE';
  v_implementation_result CLOB;
BEGIN
  v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/

Let’s double check by creating another advisor task:

DECLARE
 v_tname VARCHAR2(128) := 'TEST_TASK_MIKE_RECHECK';
 v_ename VARCHAR2(128) := NULL;
 v_report CLOB := NULL;
 v_script CLOB := NULL;
BEGIN
 v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
 v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
 v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
 DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

The stats for HUGO’s tables TEST1 and TEST2 have been refreshed. No further action is needed here.

But interesting enough, the implementation of the advisor task did not unlock the stats on:

GENERAL INFORMATION
-------------------------------------------------------------------------------
Task Name : TEST_TASK_MIKE_RECHECK
Execution Name : EXEC_2
Created : 08-21-17 10:57:20
Last Modified : 08-21-17 10:57:28

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
For execution EXEC_2 of task TEST_TASK_MIKE_RECHECK, the Statistics Advisor
has 1 finding(s). The findings are related to the following rules:
USEDEFAULTPARAMS. 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:
unlock_table_stats(ownname=>'SYS', tabname=>'AQ_SRVNTFN_TABLE_1', 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.

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

The implementation of recommendations does not seem to change attributes or stats when the statistics are locked. And there’s an “exec dbms_stats.” missing in the operation recommended above. But this is a minor thing.

Scripting the Adivor’s recommendations

Actually I’d rather script the advisor’s recommendations instead of blindly implement them. You can do this simply by:

SQL> spool fixit.sql
SQL>  select DBMS_STATS.SCRIPT_ADVISOR_TASK('TEST_TASK_MIKE') from dual;
SQL> spool off

Afterwards you’ll get this script which tells you all details about how to fix the advisor’s recommendations:

-- Script generated for the recommendations from execution EXEC_1
-- in the statistics advisor task TEST_TASK_MIKE
-- Script version 12.2

-- [I did cut off a lot of the comments here]

-- Scripts for rule UNLOCKNONVOLATILETABLE
-- Rule Description: Statistics for objects with non-volatile should not be locked
-- Unlock statistics for objects that are not volatile.
begin dbms_stats.unlock_table_stats('SYS', 'AQ_SRVNTFN_TABLE_1'); end;
/

-- 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 := 'HUGO';
  obj_filter.objtype := 'TABLE';
  obj_filter.objname := 'TEST1';
  obj_filter_list.extend();
  obj_cnt := obj_cnt + 1;
  obj_filter_list(obj_cnt) := obj_filter;
  obj_filter.ownname := 'HUGO';
  obj_filter.objtype := 'TABLE';
  obj_filter.objname := 'TEST2';
  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;
/

Advisor Runtime

You can easily check how long the advisor took to complete:

column ADVISOR_NAME format a28

select advisor_name,
  round(24 * (execution_end - execution_start),0) IN_HOURS,
  round(24 * 60 * (execution_end - execution_start),0) IN_MINS,
  round(24 * 60 * 60 * (execution_end - execution_start),0) IN_SECS
 from dba_advisor_tasks 
  where task_name='AUTO_STATS_ADVISOR_TASK';

ADVISOR_NAME		       IN_HOURS    IN_MINS    IN_SECS
---------------------------- ---------- ---------- ----------
Statistics Advisor		      0 	 0	    5

Can you disable it?

Well, not really – but there is a nice workaround:

BEGIN
    dbms_advisor.set_task_parameter ('AUTO_STATS_ADVISOR_TASK','_AUTO_MMON_INTERVAL',2147483647);
    dbms_advisor.set_task_parameter ('AUTO_STATS_ADVISOR_TASK','_AUTO_STATS_INTERVAL',2147483647);
END;
/

This should help as a temporary workaround until the bug mentioned in MOS Note:2305512.1 gets some attention.

Update

It looks now as if this fix has been released for all releases. Once you applied the fix for Unpublished Bug 26749785 – PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK you can simply disable it with:

exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

Please see explicitly my newer blog post in case you wonder about this error still happening in Oracle Database 19c:

• https://mikedietrichde.com/2023/02/28/oracle-optimizer-statistics-advisor-in-oracle-19c/

Summary

In summary for me the feature is very helpful in practical environments. Instead of trying to find out about any suboptimal stats anywhere the advisor will reveal them and give you recommedations. This will include incremental statistcs collection as well. In modern DBA environments where less DBAs take care on more and more databases you’ll have to automate such hings. And the Oracle Statistics Advisor is a tiny little helper allowing you to save a lot of time.

Final question: Is this extra license functionality?
As far as I see: No. As the DBMS_STATS package does not belong to any packs – and all functions are part of DBMS_STATS the Optimizer Statistics Advisor seems to be included in the regular Oracle database license – with no edition limitations.

Knwon Issues

Just learned the other day from Sergio (THANKS!) that customers may have trouble with the job tied into the Automatic Job Maintenance – and getting executed millions of times eating a lot of space in SYSAUS. Please check:

• MOS Note:2305512.1 (SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor)

–Mike