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
“And finally check”: missing query
SELECT client_name, operation_name, attributes, status FROM dba_autotask_operation;
Thanks Jan!
I added it – cheers
Mike
For the final query, it looks like you omitted the query itself. Setting the headings and the result is there, but no query.
Yes, thanks – I added it now 🙂
Thanks for the hint!
Cheers
Mike
Thanks Mike. Can you provide the query for the output in the “And finally check:” section of your note above please?
Fair point – and good catch 🙂
Thanks for the hint, I added it!
Cheers
Mike
From what I remember, didn’t DBMS_ADVISOR.SET_TASK_PARAMETER work to set the EXECUTION_DAYS_TO_EXPIRE on container databases? You just needed to drop the task for the pluggable DBs. Either way, double-check that AUTO_STATS_ADVISOR_TASK is corrected on both the container and pluggable DBs.
Hi Mike,
I guess you missed this query:
SELECT client_name, operation_name, attributes, status FROM dba_autotask_operation;
😉
Kind regards
Axel D.
Sorry, somehow I did not see the former comments for the same …
No worries, Alex – it is cool to see that people actually read the blog post 🙂
Cheers
Mike
For me, this was mostly an issue in pluggable databases, where directly running “alter table WRI$_ADV_OBJECTS move;” gives ORA-65040: operation not allowed from within a pluggable database.
So that command needed to be run as:
exec dbms_pdb.exec_as_oracle_script(‘alter table WRI$_ADV_OBJECTS move’)