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’)
I am am dealing with this issue since 12c and a solution will come in 21c :-((
This is not what I expect from expensive software!
The ‘AUTO_STATS_ADVISOR_TASK’ parameter=> ‘EXECUTION_DAYS_TO_EXPIRE’ was set to UNLIMITED when introduced (now it is 30 days) and still it is not purging out old rows automatically!
Who has ever read the recommendations of the stats-advisor? Who really needs these advises? As I could read in the docu it is only for administrators that rely on their own, old, homegrown scripts for statistics.
What a waste of resources!
Why is it switched on by default? When we switch it off, it produces ORA-12012 errors.
We need to apply patches and modify parameters instead of getting a ‘clean’ software. This is not the right way!
The ‘new features’ course does not even mention this funny ‘feature’. This may be the cause why most DBAs do not even note the problem it is causing unless the SYSAUX tablespace is the biggest in the entire database (The biggest I recently saw was 127 GB).
Dear Mike, please ask the developers to provide a proper solution finally in a helpful time.
Ether switch it off this aggravating ‘feature’ by default or make the auto-cleanup work (a retention time of 1 week should be the default)
regards
Dieter
Hi Dieter,
while I fully understand your anger, there is a one-off patch available on top of each of the 19c RUs. If you refer to the underlying issues causing trouble, I highly recommend that you follow up with support via an SR to have this tracked. We have an SR counter in each of the bugs, and it is important and helpful to signal when customers hit issues since this shows us internally the impact of such a misbehavior, and also the importance to our customers.
Cheers
Mike
for me, the best (currently working) workaround, without applying internims patches is described in Doc Id: 2305512.1 (19c)
declare
filter1 clob;
begin
filter1 := dbms_stats.configure_advisor_rule_filter(‘AUTO_STATS_ADVISOR_TASK’, null, null,’DISABLE’);
END;
/
Searching for information about uncontrolled growth of the WRI$_ADV_OBJECTS table in a PDB I found this blog entry. But regarding the following details I would like to discuss some more aspects of this problem. In several of our 19c Databases it is especially only one of all the rules listed in V$STATS_ADVISOR_RULES that causes trouble and leads to extensive growth of WRI$_ADV_OBJECTS: Rule 20 “Avoid Out of Range Histogram endpoints”.
The following query shows e.g., that in one of our databases only 13 tables are affected by this rule but the problem is reported several thousand times for each of this tables:
select
t.task_name,
r.rule_id,
r.name,
r.description,
count(distinct o.attr3||’ ‘||o.attr1||’.’||o.attr2) diff_objects,
count(o.attr9) how_often,
min(e.execution_start) first_date,
max(e.execution_start) last_date,
round(case when max(e.execution_start) min(e.execution_start) then
count(o.attr9)/(max(e.execution_start) – min(e.execution_start)) else null end,1) findings_per_day
from sys.wri$_adv_objects o
join sys.dba_advisor_executions e on e.execution_name = o.exec_name
join sys.v_$stats_advisor_rules r on r.rule_id = o.attr9
join sys.dba_advisor_tasks t on t.task_id = o.task_id
group by
t.task_name,
r.rule_id,
r.name,
r.description
order by
how_often desc,
r.name;
TASK_NAME RULE_ID NAME DESCRIPTION DIFF_OBJECTS HOW_OFTEN FIRST_DATE LAST_DATE FINDINGS_PER_DAY
———————– ——- ——————————- ————————————————————————— ———— ———- —————- —————- —————-
AUTO_STATS_ADVISOR_TASK 20 AvoidOutOfRange Avoid Out of Range Histogram endpoints 13 16917213 03.12.2022 22:07 27.04.2023 23:04 116639
AUTO_STATS_ADVISOR_TASK 16 MaintainStatsConsistency Statistics of dependent objects should be consistent 119 118072 03.12.2022 22:07 27.04.2023 23:04 814
AUTO_STATS_ADVISOR_TASK 12 AvoidStaleStats Avoid objects with stale or no statistics 15 508 03.12.2022 22:07 27.04.2023 23:04 4
AUTO_STATS_ADVISOR_TASK 11 AvoidUnnecessaryStatsCollection Avoid unnecessary statistics collection 21 313 03.12.2022 22:07 27.04.2023 23:04 2
AUTO_STATS_ADVISOR_TASK 15 UnlockNonVolatileTable Statistics for objects with non-volatile should not be locked 2 176 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 23 AvoidAnalyzeTable Avoid using analyze table commands for statistics collection 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 17 AvoidDropRecreate Avoid drop and recreate object sequences 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 10 AvoidInefficientStatsOprSeq Avoid inefficient statistics operation sequences 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 7 AvoidSetProcedures Avoid Set Statistics Procedures 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 2 CompleteAutoJob Auto Statistics Gather Job should complete successfully 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 13 GatherStatsAfterBulkDML Do not gather statistics right before bulk DML 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 14 LockVolatileTable Statistics for objects with volatile data should be locked 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 3 MaintainStatsHistory Maintain Statistics History 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 19 NotUseIncremental Statistics should not be maintained incrementally when it is not beneficial 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 6 TurnOnSQLPlanDirective SQL Plan Directives should not be disabled 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 21 UseAutoDegree Use Auto Degree for statistics collection 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 1 UseAutoJob Use Auto Job for Statistics Collection 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 4 UseConcurrent Use Concurrent preference for Statistics Collection 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 22 UseDefaultObjectPreference Use Default Object Preference for statistics collection 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 8 UseDefaultParams Use Default Parameters in Statistics Collection Procedures 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 5 UseDefaultPreference Use Default Preference for Stats Collection 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 9 UseGatherSchemaStats Use gather_schema_stats procedure 1 88 03.12.2022 22:07 27.04.2023 23:04 1
AUTO_STATS_ADVISOR_TASK 18 UseIncremental Statistics should be maintained incrementally when it is beneficial 1 88 03.12.2022 22:07 27.04.2023 23:04 1
In my understanding it should be possible to disable only certain rules of the AUTO_STATS_ADVISOR_TASK instead of disabling the whole task, but in our environment the following command has no effect.
declare
report varchar2(4000);
begin
report := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
task_name => ‘AUTO_STATS_ADVISOR_TASK’
, stats_adv_opr_type => ‘EXECUTE’
, rule_name => ‘AvoidOutOfRange’
, action =>’DISABLE’);
end;
/
So my questions are:
– Can you confirm, that it especially rule_id 20 of AUTO_STATS_ADVISOR_TASK, that makes the SYSAUX tablespace grow?
– Is there an explanation, why “AvoidOutOfRange” is reported so often for a single table or is this just a bug?
– Should it be possible to disable only single rules of the AUTO_STATS_ADVISOR_TASK as described above?
– And if so: Is there a query that shows, which rules of the AUTO_STATS_ADVISOR_TASK are disabled and which are enabled?
Hi Ulrich,
thanks a lot for the detailed description. Unfortunately, our team does not own the Stats Advisor. It is owned by the Optimizer team.
I happily forward your into to the people in charge but I know what the first question will be: “Do you have an SR for this?”
Can you please share the SR number with me – and in case you don’t have any yet, would you please mind opening an SR and passing the SR number along?
Then I can send this to the people in charge.
But regarding your questions, I found this blog post from our friends from dbi-services:
https://www.dbi-services.com/blog/oracle-12cr2-optimizer-statistics-advisor/
This shows you how to “disable” specific rules.
But you need to try please whether this stops actually the RULE or whether this only surpresses the output.
Thanks!!
Mike
Thank you Mike for your reply and for the link. I will check it out.
I created SR 3-26251990671 “Optimizer Statistics Advisor reports “Avoid Out of Range Histogram endpoints” a Million times” more than a year ago. It is closed alread and the result was: “You are the second customer to report this problem. It could be a bug but without being able to reproduce the problem here the only thing I can suggest is the workaround to disable the rule.”
Strange enough we see the problem on several databases, hosting different applications and we have it on 19c and we had it on 12c in the past. I had lost the focus on this topic but recently I noticed, that on some new 19c databases, that were created as a replacement for the 12c databases in the last months, the SYSAUX tablespace was grown quite big, because I didn’t remember to check the Optimizer Statistics Advisor when I created these databases. And it seems to me, that the behaviour of DBMS_STATS.DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER changed from 12c to 19c, but I’m not sure about this.
Sorry for the bad formatting of my previous message. I copied the whole text from an editor to your comment box and it looked better before. Is there a way to mark code in a comment?
Hi Ulrich,
I see that SR 3-26251990671 is from 2 years ago. So you seem to be aware of the filtering option already.
May I recommend that you open another SR please, and have Support check this for your current RU version?
I doubt that you are the only one struggling with this issue.
Cheers,
Mike