Oracle Optimizer Statistics Advisor in Oracle 19c

Christian Pfundtner pointed me to the fact that my old blog post about the Statistics Advisor from 2017 may not be up to date anymore. And he further pointed out that some of his clients still suffer from issues with the tool. Hence, let me refresh the topic with Oracle Optimizer Statistics Advisor in Oracle 19c. And thanks to Christian for the pointers.

Photo by Tobias Keller on Unsplash

Overview

At first, I won’t rewrite the entire blog post but instead focus on recent findings. Therefore, you may please want to read my previous blog post about the feature and tool itself in case you’d like to get an overview about the Oracle Optimizer Statistics Advisor.

In addition, I am sharing the link to the Oracle 21c documentation here which has the most recent information at the time I am writing this blog post.

The rules seem to be exactly the same as in Oracle 12.2.0.1. I don’t see a change comparing it to 21c.

set long 3000
set line 500
set pages 4000

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.

Start an Advisor Task

The procedure to kick off an advisor task works fine as well, too.

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;
/

In my environment this gives a nice list of recommendations.

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

 Task Name	 : TEST_TASK_MIKE    
 Execution Name  : EXEC_8	     
 Created	 : 02-27-23 11:59:43 
 Last Modified	 : 02-27-23 11:59:46

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_8 of task TEST_TASK_MIKE, the Statistics Advisor has 2	
 finding(s). The findings are related to the following rules: USECONCURRENT,    
 AVOIDSTALESTATS. Please refer to the finding section for detailed information. 
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name: UseConcurrent						    
 Rule Description:  Use Concurrent preference for Statistics Collection 	
 Finding:  The CONCURRENT preference is not used.				

 Recommendation:  Set the CONCURRENT preference.

 Example:									
 dbms_stats.set_global_prefs('CONCURRENT', 'ALL');				
 Rationale:  The system's condition satisfies the use of concurrent statistics	
	     gathering. Using CONCURRENT increases the efficiency of statistics 
	     gathering. 							
----------------------------------------------------
 Rule Name:	    AvoidStaleStats						
 Rule Description:  Avoid objects with stale or no statistics		     
 Finding:  There are 22 object(s) with stale statistics.	
		
 Schema:			
 AUDSYS 			
 Objects:			
 AUD$UNIFIED	
		
 Schema:			
 DBSNMP
 Objects:			
 BSLN_BASELINES 		
 BSLN_METRIC_DEFAULTS		
 BSLN_TIMEGROUPS	
	
 Schema:			
 LBACSYS			
 Objects:			
 OLS$INSTALLATIONS		
 OLS$PROPS	
 
 Schema: 		       
 WMSYS				
 Objects:			
 WM$ENV_VARS$		
	
 Schema:			
 XDB				
 Objects:			
 X$NM69LEG270EHE2WV0L8000000001 
 X$QN69LEG270EHE2WV0L8000000001 
 XDB$ALL_MODEL		       

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

Nothing to worry here in my case.

The differences

Now let me focus on the difference between 19c and earlier or later releases – the reason why Christian sent me the email I mentioned above.

Executing a call to disable the task works fine in Oracle Database 12.2.0.1 and 21c.

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

PL/SQL procedure successfully completed.

But it doesn’t in Oracle Database 19c. I ran this specifically in Oracle 19.18.0 but Christian confirmed that this happens in other 19c RUs as well.

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

BEGIN dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); END;
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at "SYS.DBMS_STATS", line 52546
ORA-06512: at "SYS.DBMS_STATS", line 52794
ORA-06512: at line 1

Now, when you revisit my 2017 blog post, I added a note in red later on:

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');

The official solution

Since the fix for Bug 26749785 is not included in any of the 19c RUs, you may need to apply the one off patch on top of your RU in order to have this call available to disable the task in your environment.

Simply download and apply the one-off patch for bug 26749785 – it is almost certainly available for your environment already.

From Oracle Database 21c onward this issue is fixed in the code. The fix introduces a new version of prvtstai.sql.

See also MOS Note: 2750857.1 – Enabling/Disabling AUTO_STATS_ADVISOR_TASK Throwing ORA-20001: Invalid input values for pname with a summary of the above findings.

Christian’s solution

Christian sent me his workaround which I will display here for those struggling as well with a huge amount of storage consumed. The below approach is will wipe out the consumed storage in SYSAUX as well. This was the main purpose of his exercise – free up the space again.

At first, you can check the space consumption:

select OCCUPANT_NAME,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS where OCCUPANT_NAME='SM/ADVISOR';

OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
SM/ADVISOR                                                                    10816

So in my case it is really low with 11MB. Which is the default allocated.

In addition, you can check the details with:

column segment_name format a30
select segment_name, bytes, tablespace_name from dba_segments where segment_name like 'WRI$_ADV_OBJECTS%'

SEGMENT_NAME                        BYTES TABLESPACE_NAME
------------------------------ ---------- ------------------------------
WRI$_ADV_OBJECTS                    65536 SYSAUX
WRI$_ADV_OBJECTS_PK                 65536 SYSAUX
WRI$_ADV_OBJECTS_IDX_01             65536 SYSAUX
WRI$_ADV_OBJECTS_IDX_02             65536 SYSAUX

Then Christian drops the advisor task.

exec DBMS_STATS.DROP_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK');

And a quick reorg of the indexes and the table will free the additional storage. It will just allocate the default value of a bit over 10MB again.

ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;

In case you’d like to get the task back again, use this call:

EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

And finally check:

column OPERATION_NAME format a26
column CLIENT_NAME format a36
column ATTRIBUTES format a44

SELECT client_name, operation_name, attributes, status FROM dba_autotask_operation;

CLIENT_NAME                          OPERATION_NAME             ATTRIBUTES                                   STATUS
------------------------------------ -------------------------- -------------------------------------------- --------
auto optimizer stats collection      auto optimizer stats job   ON BY DEFAULT, VOLATILE, SAFE TO KILL        ENABLED
auto space advisor                   auto space advisor job     ON BY DEFAULT, VOLATILE, SAFE TO KILL        ENABLED
sql tuning advisor                   automatic sql tuning task  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SA ENABLED
                                                                FE TO KILL

Be aware that the latter procedure may be helpful in order to free up the space. But generally, you may want to add the one-off patch from the beginning.

Further Links and Information

• Oracle Optimizer Statistics Advisor in Oracle 12.2.0.1 (August 22, 2017)
• Oracle 21c documentation – Optimizer Statistics Advisor
• Bug 26749785 – PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK
• One-off patch for bug 26749785
• MOS Note: 2750857.1 – Enabling/Disabling AUTO_STATS_ADVISOR_TASK Throwing ORA-20001: Invalid input values for pname
• Christian Pfundtner’s Blog – DB Masters (in German only)

–Mike