Statistics Advisor task in Oracle

Oracle 12.2 introduced new feature of statistics advisor. It runs as auto task AUTO_STATS_ADVISOR_TASK.

Check the statistics advisory list

COL EXECUTION_NAME FORMAT a14
SELECT EXECUTION_NAME, EXECUTION_END, STATUS FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK'
ORDER BY 2;


Run the Stats Advisor Task Manually:

SET LINESIZE 3000
SET LONG 500000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000
DECLARE
task_name VARCHAR2(128) := 'stats_advisor_task';
exec_name VARCHAR2(128) := NULL;
report CLOB := NULL;
BEGIN
-- For create a task
task_name := dbms_stats.create_advisor_task(task_name);
-- For execute the task
exec_name := dbms_stats.execute_advisor_task(task_name);
-- For view the task report
report := dbms_stats.report_advisor_task(task_name);
dbms_output.put_line(report);
--For implement the recommendation from the task
--implementation_result := dbms_stats.implement_advisor_task(tname);
END;
/


Drop the stats advisory task

EXEC DBMS_STATS.DROP_ADVISOR_TASK(task_name);

Example:
EXEC DBMS_STATS.DROP_ADVISOR_TASK('stats_advisor_task');


REPORTS EXAMPLE

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.

FINDINGS
-------------------------------------------------------------------------------
Rule Name: UseDefaultPreference
Rule Description: Use Default Preference for Stats Collection
Finding: Global preference METHOD_OPT is set to a non-default value
'FOR ALL COLUMNS SIZE 1'.
Recommendation: Set the value of preference METHOD_OPT to 'FOR ALL COLUMNS SIZE AUTO'.
Example: Setting preference cascade to default value:
dbms_stats.set_global_prefs('CASCADE', NULL);
Rationale: METHOD_OPT controls the creation of histograms during statistics collection.
With the default value FOR ALL COLUMNS SIZE AUTO, Oracle determines which
columns require histograms and the number of buckets to use based on the usage
of columns in SQL statements and the number of distinct values. The default
value helps to create the necessary histograms with an adequate number of
buckets.


Statistics Advisor: V$STATS_ADVISOR_RULES
Find the rule detail of statistics.

SQL> select to_char(rule_id,99)||' '||name||' ('||description||')' from V$STATS_ADVISOR_RULES where rule_id>0 order by rule_id;

TO_CHAR(RULE_ID,99)||''||NAME||'('||DESCRIPTION||')'
------------------------------------------------------------------------------------------------------------------------
  1 UseAutoJob (Use Auto Job for Statistics Collection)
  2 CompleteAutoJob (Auto Statistics Gather Job should complete successfully)
  3 MaintainStatsHistory (Maintain Statistics History)
  4 UseConcurrent (Use Concurrent preference for Statistics Collection)
  5 UseDefaultPreference (Use Default Preference for Stats Collection)
  6 TurnOnSQLPlanDirective (SQL Plan Directives should not be disabled)
  7 AvoidSetProcedures (Avoid Set Statistics Procedures)
  8 UseDefaultParams (Use Default Parameters in Statistics Collection Procedures)
  9 UseGatherSchemaStats (Use gather_schema_stats procedure)
 10 AvoidInefficientStatsOprSeq (Avoid inefficient statistics operation sequences)
 11 AvoidUnnecessaryStatsCollection (Avoid unnecessary statistics collection)
 12 AvoidStaleStats (Avoid objects with stale or no statistics)
 13 GatherStatsAfterBulkDML (Do not gather statistics right before bulk DML)
 14 LockVolatileTable (Statistics for objects with volatile data should be locked)
 15 UnlockNonVolatileTable (Statistics for objects with non-volatile should not be locked)
 16 MaintainStatsConsistency (Statistics of dependent objects should be consistent)
 17 AvoidDropRecreate (Avoid drop and recreate object seqauences)
 18 UseIncremental (Statistics should be maintained incrementally when it is beneficial)
 19 NotUseIncremental (Statistics should not be maintained incrementally when it is not beneficial)
 20 AvoidOutOfRange (Avoid Out of Range Histogram endpoints)
 21 UseAutoDegree (Use Auto Degree for statistics collection)
 22 UseDefaultObjectPreference (Use Default Object Preference for statistics collection)
 23 AvoidAnalyzeTable (Avoid using analyze table commands for statistics collection)

23 rows selected.