This is just a little shout-out about the Stats Advisor – if you decide to give it a go, what sort of things is it likely to tell you. The answer is in a dynamic performance view called v$stats_advisor_rules – which I’ve list below from an instance running 18.3.0.0.
SQL> set linesize 180 SQL> set trimspool on SQL> set pagesize 40 SQL> column description format a75 SQL> column name format a32 SQL> break on rule_type duplicate skip 1 SQL> select * from v$stats_advisor_rules; RULE_ID NAME RULE_TYPE DESCRIPTION CON_ID ---------- -------------------------------- --------- --------------------------------------------------------------------------- ---------- 0 SYSTEM 0 1 UseAutoJob SYSTEM Use Auto Job for Statistics Collection 0 2 CompleteAutoJob SYSTEM Auto Statistics Gather Job should complete successfully 0 3 MaintainStatsHistory SYSTEM Maintain Statistics History 0 4 UseConcurrent SYSTEM Use Concurrent preference for Statistics Collection 0 5 UseDefaultPreference SYSTEM Use Default Preference for Stats Collection 0 6 TurnOnSQLPlanDirective SYSTEM SQL Plan Directives should not be disabled 0 7 AvoidSetProcedures OPERATION Avoid Set Statistics Procedures 0 8 UseDefaultParams OPERATION Use Default Parameters in Statistics Collection Procedures 0 9 UseGatherSchemaStats OPERATION Use gather_schema_stats procedure 0 10 AvoidInefficientStatsOprSeq OPERATION Avoid inefficient statistics operation sequences 0 11 AvoidUnnecessaryStatsCollection OBJECT Avoid unnecessary statistics collection 0 12 AvoidStaleStats OBJECT Avoid objects with stale or no statistics 0 13 GatherStatsAfterBulkDML OBJECT Do not gather statistics right before bulk DML 0 14 LockVolatileTable OBJECT Statistics for objects with volatile data should be locked 0 15 UnlockNonVolatileTable OBJECT Statistics for objects with non-volatile should not be locked 0 16 MaintainStatsConsistency OBJECT Statistics of dependent objects should be consistent 0 17 AvoidDropRecreate OBJECT Avoid drop and recreate object seqauences 0 18 UseIncremental OBJECT Statistics should be maintained incrementally when it is beneficial 0 19 NotUseIncremental OBJECT Statistics should not be maintained incrementally when it is not beneficial 0 20 AvoidOutOfRange OBJECT Avoid Out of Range Histogram endpoints 0 21 UseAutoDegree OBJECT Use Auto Degree for statistics collection 0 22 UseDefaultObjectPreference OBJECT Use Default Object Preference for statistics collection 0 23 AvoidAnalyzeTable OBJECT Avoid using analyze table commands for statistics collection 0 24 rows selected.
As you can see the rules fall into three groups: system, operation, and object – and you can’t help noticing at all three levels how commonly the theme is: “just stick with the defaults!”.
As so often happens when I start writing a catch-up or “remind myself” not I found that Tim Hall has already written all about it.
Comments and related questions are welcome.