The following concerns Version 12.2 (Bundle Patch: 12.2.0.1.210420) and version 19.0 (Bundle Patch: 19.10.0.0.210119) Standard Edition on Microsoft Windows Server.
I didn‘t care about the “Oracle Optimizer Statistics Advisor Task” in the past. But recently I noticed, that the table WRI$_ADV_OBJECTS has grown a lot on some of our databases and that it contained old data since 2017 in one case.
I found MOS Doc ID 2660128.1 (How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards) and I deleted the old records in the table using this commands:
EXEC SYS.DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 700);
exec prvt_advisor.delete_expired_tasks;
I decreased EXECUTION_DAYS_TO_EXPIRE slowly in steps of 100 days as the “delete_expired_tasks” command created a huge amount of redo-information, and I waited for RMAN to backup and delete the archive logs before I started the next step.
For every single day the database made an “Index Fast Full Scan” trough WRI%_ADV_OBJECTS, which consisted of about 550.000 blocks on one database. So deleting 100 days of history needed nearly an hour and resulted in up to 160 Log-Switches per hour, where some 4 or 5 Log-Switches per hour are the normal workload of the database.
Finally I set EXECUTION_DAYS_TO_EXPIRE to 30 days, run prvt_advisor.delete_expired_tasks for the last time and shrinked the size of WRI$_ADV_OBJECTS and it’s associated indexes:
exec dbms_pdb.exec_as_oracle_script('alter table SYS.WRI$_ADV_OBJECTS move');
alter index wri$_adv_objects_idx_01 rebuild;
alter index wri$_adv_objects_pk rebuild;
But in the next days I found some strange things and I would like to get some advice:
-----------------------------------------------------------------------------------------
1. On the 12.2 database the parameter EXECUTION_DAYS_TO_EXPIRE doesn’t seem to work automatically. The history in DBA_ADVISOR_EXECUTIONS and WRI$_ADV_OBJECTS is increasing every day and longer than 30 days, even if the parameter is limited to 30 days. Is it right, that there is no job by default, that cleans up the expired tasks periodically?
I looked at the results of the “Oracle Optimizer Statistics Advisor” with DBMS_STATS.REPORT_ADVISOR_TASK and found some strange results, for example:
-----------------------------------------------------------------------------------------
2. In one database the finding is:
Rule Name: CompleteAutoJob
Rule Description: Auto Statistics Gather Job should complete successfully
Finding: There were maintenance windows that failed to close.
Recommendation: Please file a Service Request with Oracle.
Rationale: Maintenance window configuration appears to be correct. Requires more investigation by Oracle Support.
But in an other very similar database (same version, similar workload, same maintenance window) with the same finding the recommendation is:
Recommendation: Increase the length of the maintenance window.
Rationale: Increasing the length of the maintenance window gives the statistics gathering job more time to finish.
Is there any explanation for this difference?
-----------------------------------------------------------------------------------------
3. For several table on different databases I’ve got the recommendation:
Rule Name: UnlockNonVolatileTable
Rule Description: Statistics for objects with non-volatile should not be locked
Finding: Statistics are locked on 296 table(s) which are not volatile.
Schema: ….
Objects: ….
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.
But following this advice I’ve got the following recommendation on the next days:
Rule Name: UseDefaultParams
Rule Description: Use Default Parameters in Statistics Collection Procedures
Finding: There are 296 statistics operation(s) using nondefault parameters.
Operation: unlock_table_stats(ownname=>'…', tabname=>…, stattype=>'ALL');
unlock_table_stats(ownname=>'…', tabname=>…, stattype=>'ALL');
…
Recommendation: Use default parameters for statistics operations.
Example: …
Rationale: Using default parameter values for statistics gathering operations is more efficient.
Why does the Oracle recommendation lead to a new problem?
Will the new messages disappear one day or will it be reported every day?
-----------------------------------------------------------------------------------------
4. And finally the most strange finding in my opinion is this:
Rule Name: AvoidOutOfRange
Rule Description: Avoid Out of Range Histogram endpoints
Finding: Out-of-range scenarios are observed on 1701310 table(s).
Recommendation: Copy table statistics from old partitions to newly created partitions.
Rationale: Copying table statistics in such a way would avoid out-of-range errors, and would likely be more accurate for newly created partitions. Refer to: https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables.
There are similar reports for other databases 12.2 and 19.0 (Bundle Patch : 19.10.0.0.210119) with extremely high numbers of the affected tables for the Rule “AvoidOutOfRange”.
The finding is nonsense as the database has only entries 5348 in dba_tables and 277346 entries in dba_columns. The recommendation is nonsense as well, as we have a standard edition and don’t use partitioning.
But this finding creates about 1.7 Million entries in WRI$_ADV_OBJECTS every day, which makes this table huge, even if the history is limited to 30 days.
How can I get rid of these messages?