Oracle 12cR2: Statistics Advisor

By Franck Pachot

.
Today at #ukoug_tech16 12:30 in hall 11A I’ll talk about Statistics Gathering Best Practice & 12cR2 Statistics Advisor
Rather than taking the Optimizer Statistics Advisor rules one by one I’ll show the things to take care (some people may call it best practices) when gathering statistics and they mention the Statistics Advisor Rule.
If you need a reference about all rules, you can get it from V$STATS_ADVISOR_RULES

09:41:19 SQL> select to_char(rule_id,99)||' '||description||' ('||name||')' from V$STATS_ADVISOR_RULES where rule_id>0 order by rule_id;
&nbsp
TO_CHAR(RULE_ID,99)||''||DESCRIPTION||'('||NAME||')'
----------------------------------------------------------------------------------------------------

I’ll explain them briefly here.

You should always enable to automatic statistic gathering job. You may manage special cases manually, but do not disable it.

  1 Use Auto Job for Statistics Collection (UseAutoJob)

For sure if the job does not complete successfully, the advisor detects the probable cause.

  2 Auto Statistics Gather Job should complete successfully (CompleteAutoJob)

Statistics history may save your life in case of a regression. But be sure the retention is not too large and purge occurs or SYSAUX will grow

  3 Maintain Statistics History (MaintainStatsHistory)

The faster the statistics gathering run, the more statistics you can gather. Use all your server resources for it.

  4 Use Concurrent preference for Statistics Collection (UseConcurrent)

Default options for global preferences are what the optimizer developers think are the best for most cases.

  5 Use Default Preference for Stats Collection (UseDefaultPreference)

Humm.. this one was introduced before the decision not to activate SPD by default
(see http://dbi-services.com/blog/oracle-12cr2-optimizer-adaptive-statistics/)

  6 SQL Plan Directives should not be disabled (TurnOnSQLPlanDirective)

Setting statistics manually may be used as a workaround but not the general case

  7 Avoid Set Statistics Procedures (AvoidSetProcedures)

When you run dbms_stats.gather_…_stats manually, default options are what the optimizer developers think are the best for most cases.

  8 Use Default Parameters in Statistics Collection Procedures (UseDefaultParams)

And in those cases, better to run it for a schema so that you are sure to include newly created tables

  9 Use gather_schema_stats procedure (UseGatherSchemaStats)

You waste time and ressources if you gather statistics in a addition to what is done with online statistics gathering

 10 Avoid inefficient statistics operation sequences (AvoidInefficientStatsOprSeq)

You waste time and ressources if you gather statistics when nothing has changed

 11 Avoid unnecessary statistics collection (AvoidUnnecessaryStatsCollection)

You need statistics for all tables

 12 Avoid objects with stale or no statistics (AvoidStaleStats)

Statistics gathered before bulk inserts will be immediately stale

 13 Do not gather statistics right before bulk DML (GatherStatsAfterBulkDML)

You don’t want the automatic statistics gathering run on a table between a truncate and an insert

 14 Statistics for objects with volatile data should be locked (LockVolatileTable)

But let it run for tables with no massive change

 15 Statistics for objects with non-volatile should not be locked (UnlockNonVolatileTable)
 16 Statistics of dependent objects should be consistent (MaintainStatsConsistency)

Better truncate, make indexes unusable, and insert /*+ append */ than drop and recreate the table (which removes statistics).

 17 Avoid drop and recreate object seqauences (AvoidDropRecreate)

Statistics advisor may detect when incremental statistics gathering is me efficient for partitioned tables

 18 Statistics should be maintained incrementally when it is beneficial (UseIncremental)
 19 Statistics should not be maintained incrementally when it is not beneficial (NotUseIncremental)

Stale statistics may lead to under-estimation because of linear decay

 20 Avoid Out of Range Histogram endpoints (AvoidOutOfRange)

Large tables can be scanned in parallel, recommendation is default degree

 21 Use Auto Degree for statistics collection (UseAutoDegree)

As we have seen about global preferences, table preference should be default for most cases (rolling invalidation, auto sample size, auto histogram size)

 22 Use Default Object Preference for statistics collection (UseDefaultObjectPreference)

And for sure dbms_stats is the way to gather statistics for the optimizer. ANALYZE is deprecated for that since 91

 23 Avoid using analyze table commands for statistics collection (AvoidAnalyzeTable)

Those are only my interpretation. 12.2 is new (and cloud first) and I’ve not observed all those recommandations yet. But there are properly described by the advisor.
This is the kind of output we can get:

----------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 
 Task Name       : MY_TASK
 Execution Name  : EXEC_52
 Created         : 12-07-16 11:31:40
 Last Modified   : 12-07-16 11:32:37
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_52 of task MY_TASK, the Statistics Advisor has 6
 finding(s). The findings are related to the following rules: USECONCURRENT,
 AVOIDSETPROCEDURES, USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, AVOIDSTALESTATS,
 UNLOCKNONVOLATILETABLE. 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.
----------------------------------------------------
...