Analyzing Statistics Using Optimizer Statistics Advisor

Are you collecting statistics and using Oracle Defined methods to gather statistics?
No -> then Optimizer Statistics Advisor is there to Advice you, what you are missing while gathering statistics.
Optimizer Statistics Advisor analyzes how optimizer statistics are gathered, and then makes recommendations.

What is Optimizer Statistics Advisor
1.    Optimizer Statistics Advisor is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks.
2.    The advisor task runs automatically in the maintenance window, but you can also run it on demand.
       You can then view the advisor report. If the advisor makes recommendations, then in some cases you can run system-generated scripts to implement them.
3.    Optimizer Statistics Advisor inspects how optimizer statistics are gathered.
4.    The advisor automatically diagnoses problems in the existing practices for gathering statistics.
5.    The advisor does not gather a new or alternative set of optimizer statistics.
6.    The output of the advisor is a report of findings and recommendations, which help you, follow best practices for gathering statistics.

The following figure provides a conceptual overview of Optimizer Statistics Advisor.



Why we need Optimizer Statistics Advisor
Because there is still practice to collect statistics using script based approach.
There are few problems with script based approach as below,
Problems with a Traditional Script-Based Approach

The advantage of the scripted approach is that the scripts are typically tested and reviewed.
However, the owner of suboptimal legacy scripts may not change them for fear of causing plan changes.

The traditional approach has the following problems:

Legacy scripts may not keep pace with new best practices, which can change from release to release.
Frequently, successive releases add enhancements to histograms, sampling, workload monitoring, concurrency, and other optimizer-related features.
For example, starting in Oracle Database 12c, Oracle recommends setting AUTO_SAMPLE_SIZE instead of a percentage.
However, legacy scripts typically specify a sampling percentage, which may lead to suboptimal execution plans.

Resources are wasted on unnecessary statistics gathering.
A script may gather statistics multiple times each day on the same table.

Automatic statistics gathering jobs do not guarantee accurate and up-to-date statistics.
For example, sometimes the automatic statistics gathering job is not running because an initialization parameter combination disables it, or the job is terminated.
Moreover, sometimes the automatic job maintenance window is insufficient because of resource constraints, or because too many objects require statistics collection.
Jobs that stop running before gathering all statistics cause either no statistics or stale statistics for some objects, which can in turn cause suboptimal plans.

Statistics can sometimes be missing, stale, or incorrect.
For example, statistics may be inconsistent between a table and its index, or between tables with a primary key-foreign key relationship.
Alternatively, a statistics gathering job may have been disabled by accident, or you may be unaware that a script has failed.

Lack of knowledge of the problem can be time-consuming and resource-intensive.
For example, a service request might seek a resolution to a problem, unaware that the problem is caused by suboptimal statistics.
The diagnosis might require a great deal of time emailing scripts of the problematic queries, enabling traces, and investigating traces.

Recommended fixes may not be feasible.
Performance engineers may recommend changing the application code that maintains statistics.
In some organizations, this requirement may be difficult or impossible to satisfy.

Advantages of Optimizer Statistics Advisor

Optimizer Statistics Advisor does not gather a new or alternative set of optimizer statistics, and so does not affect the workload.
Rather, Optimizer Statistics Advisor analyzes information stored in the data dictionary, and then stores the findings and recommendations in the database.
Components of Optimizer Statistics Advisor
The Optimizer Statistics Optimizer framework stores its metadata in data dictionary and dynamic performance views.
Below are mentioned component list for OPTIMIZER-STATS-ADVISOR

1=> Rules
2=> Findings
3=> Recommendations
4=> Actions

SQL> SET LINESIZE 208
SET PAGESIZE 100
COL ID FORMAT 99
COL NAME FORMAT a33
COL DESCRIPTION FORMAT a62

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION
FROM   V$STATS_ADVISOR_RULES
WHERE  RULE_ID BETWEEN 1 AND 12
ORDER BY RULE_ID;

 ID NAME			      RULE_TYPE DESCRIPTION
--- --------------------------------- --------- --------------------------------------------------------------
  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

12 rows selected.

We will be describing each of them one-by-one
1 => Rules for Optimizer Statistics Advisor
An Optimizer Statistics Advisor rule is an Oracle-supplied standard by which Optimizer Statistics Advisor performs its checks.
The advisor organizes rules into the following classes:
a-> Systems
This class checks the preferences for statistics collection, status of the automated statistics gathering job, use of SQL plan directives, and so on.
Rules in this class have the value SYSTEM in V$STATS_ADVISOR_RULES.RULE_TYPE.

b-> Objects
This class checks for the quality of the statistics, staleness of statistics, unnecessary collection of statistics, and so on.
Rules in this class have the value OBJECT in V$STATS_ADVISOR_RULES.RULE_TYPE.

c-> Operations
This class checks whether statistics collection uses the defaults, test statistics are created using the SET_*_STATS procedures, and so on.
Rules in this class have the value OPERATION in V$STATS_ADVISOR_RULES.RULE_TYPE.

2 => Findings for Optimizer Statistics Advisor

A finding results when Optimizer Statistics Advisor examines the evidence stored in the database and concludes that the rules were not followed.

To generate findings, Optimizer Statistics Advisor executes a task, which is invoked either automatically or manually.
This task analyzes the statistics history stored in the data dictionary, the statistics operation log, and the current statistics footprint that exists in SYSAUX.

Typically, Optimizer Statistics Advisor generates a finding when a specific rule is not followed or is violated, although some findings—such as object staleness—provide only information.

A finding corresponds to exactly one rule. However, a rule can generate many findings.
3=> Recommendations for Optimizer Statistics Advisor
Based on each finding, Optimizer Statistics Advisor makes recommendations on how to achieve better statistics.

For example, the advisor might discover a violation to the rule of not using sampling when gathering statistics, and recommend specifying AUTO_SAMPLE_SIZE instead.
The advisor stores the recommendations in DBA_ADVISOR_RECOMMENDATIONS.
Multiple recommendations may exist for a single finding. In this case, you must investigate to determine which recommendation to follow.
Each recommendation includes one or more rationales that explain why Optimizer Statistics Advisor makes its recommendation. In some cases, findings may not generate recommendations.

4=> Actions for Optimizer Statistics Advisor

An Optimizer Statistics Advisor action is a SQL or PL/SQL script that implements recommendations. When feasible, recommendations have corresponding actions.
The advisor stores actions in DBA_ADVISOR_ACTIONS.

Operational Modes for Optimizer Statistics Advisor

Optimizer Statistics Advisor supports both an automated and manual mode.

Automated
The predefined task AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window once per day.
The task runs as part of the automatic optimizer statistics collection client.
The automated task generates findings and recommendations, but does not implement actions automatically.

Manual
You can create your own task using the DBMS_STATS.CREATE_ADVISOR_TASK function, and then run it at any time using the EXECUTE_ADVISOR_TASK procedure.
Unlike the automated task, the manual task can implement actions automatically. Alternatively, you can configure the task to generate a PL/SQL script, which you can then run manually.

Basic Tasks for Optimizer Statistics Advisor 

Below mentioned diagram depict basic task for AUTO and MANUAL MODE run



Viewing Reports When AUTO_STATS_ADVISOR_TASK run in AUTOMATIC MODE

If AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window, then your workflow begins by querying the report.

We can Query and make reports for AUTO_STATS_ADVISOR_TASK output by reporting their output.

Below are steps to perform the same.

Step 1=>Find latest job execution as below.

SQL> col advisor_name for a40
SQL>  select TASK_NAME,EXECUTION_NAME,ADVISOR_NAME ,EXECUTION_END from DBA_ADVISOR_EXECUTIONS WHERE ADVISOR_NAME='Statistics Advisor';

TASK_NAME         EXECUTION_NAME        ADVISOR_NAME          EXECUTION_END
------------------------------ ------------------------------ ---------------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK        EXEC_923         Statistics Advisor         28-MAY-2022 23:16:48
AUTO_STATS_ADVISOR_TASK        EXEC_909         Statistics Advisor         27-MAY-2022 22:02:47
AUTO_STATS_ADVISOR_TASK        EXEC_942         Statistics Advisor         29-MAY-2022 17:44:47

Step 2=>Find ADVISOR_NAME job execution as below.

select TASK_NAME,LAST_EXECUTION,ADVISOR_NAME,STATUS from dba_advisor_tasks  ORDER BY ADVISOR_NAME DESC;

TASK_NAME         LAST_EXECUTION     ADVISOR_NAME        STATUS
------------------------------ -------------------- ---------------------------------------- -----------
INDIVIDUAL_STATS_ADVISOR_TASK       Statistics Advisor        INITIAL
AUTO_STATS_ADVISOR_TASK        EXEC_942      Statistics Advisor        COMPLETED
SYS_AUTO_SPCADV112002227052022 EXEC_907      Segment Advisor        COMPLETED
SYS_AUTO_SPCADV515441729052022 EXEC_941      Segment Advisor        COMPLETED
SYS_AUTO_SPCADV336152328052022 EXEC_922      Segment Advisor        COMPLETED
SYS_AUTO_SPCADV610452129052022 EXEC_947      Segment Advisor        COMPLETED
SYS_AUTO_SPCADV456341329052022 EXEC_935      Segment Advisor        COMPLETED
SYS_AUTO_SPCADV228301528052022 EXEC_915      Segment Advisor        COMPLETED
SYS_AUTO_SQL_TUNING_TASK       EXEC_934      SQL Tuning Advisor        COMPLETED
SYS_AI_VERIFY_TASK        SQL Performance Analyzer       INITIAL
SYS_AUTO_INDEX_TASK        SQL Access Advisor        INITIAL
SYS_AI_SPM_EVOLVE_TASK        SPM Evolve Advisor        INITIAL
SYS_AUTO_SPM_EVOLVE_TASK       EXEC_936      SPM Evolve Advisor        COMPLETED
ADDM:3627898686_1_303        EXEC_946      ADDM         COMPLETED
ADDM:3627898686_1_294        EXEC_924      ADDM         COMPLETED
ADDM:3627898686_1_293        EXEC_921      ADDM         COMPLETED
ADDM:3627898686_1_291        EXEC_919      ADDM         COMPLETED
ADDM:3627898686_1_292        EXEC_920      ADDM         COMPLETED
ADDM:3627898686_1_290        EXEC_918      ADDM         COMPLETED
ADDM:3627898686_1_301        EXEC_944      ADDM         COMPLETED
ADDM:3627898686_1_285        EXEC_904      ADDM         COMPLETED
ADDM:3627898686_1_287        EXEC_910      ADDM         COMPLETED
ADDM:3627898686_1_289        EXEC_917      ADDM         COMPLETED
ADDM:3627898686_1_300        EXEC_943      ADDM         COMPLETED
ADDM:3627898686_1_296        EXEC_937      ADDM         COMPLETED
ADDM:3627898686_1_284        EXEC_894      ADDM         COMPLETED
ADDM:3627898686_1_302        EXEC_945      ADDM         COMPLETED
ADDM:3627898686_1_286        EXEC_905      ADDM         COMPLETED
ADDM:3627898686_1_297        EXEC_938      ADDM         COMPLETED
ADDM:3627898686_1_298        EXEC_939      ADDM         COMPLETED
ADDM:3627898686_1_299        EXEC_940      ADDM         COMPLETED
31 rows selected.

Step 3=> Generate report as below 
SET LONG 1000000
SET LONGCHUNKSIZE 100000
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000
select dbms_stats.report_advisor_task('AUTO_STATS_ADVISOR_TASK') as report from dual;

REPORT
----------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name  : AUTO_STATS_ADVISOR_TASK
Execution Name  : EXEC_942
Created  : 04-17-19 01:14:34
Last Modified  : 05-29-22 05:44:47
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
For execution EXEC_942 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
has 6 finding(s). The findings are related to the following rules:
USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, AVOIDUNNECESSARYSTATSCOLLECTION,
AVOIDSTALESTATS, UNLOCKNONVOLATILETABLE, USEINCREMENTAL. Please refer to the
finding section for detailed information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
Rule Name:     UseDefaultParams
Rule Description:  Use Default Parameters in Statistics Collection Procedures
Finding:  There are 54 statistics operation(s) using nondefault parameters.
Operation:
gather_table_stats(tabname=>'sales', no_invalidate=>FALSE);
gather_table_stats(ownname=>'ABHI_TEST', tabname=>'SALES', no_invalidate=>FALSE);
gather_table_stats(ownname=>'ABHI_TEST', tabname=>'SALES', no_invalidate=>FALSE);
gather_table_stats(ownname=>'ABHI_TEST', tabname=>'SALES', no_invalidate=>FALSE);
gather_table_stats(ownname=>'ABHI_TEST', tabname=>'SALES', estimate_percent=>2, method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
delete_table_stats(ownname=>'ABHI_tEST', tabname=>'SALES', cascade_parts=>TRUE, cascade_columns=>TRUE, cascade_indexes=>TRUE, stattype=>'ALL');
gather_table_stats(ownname=>'ABHI_TEST', tabname=>'SALES', estimate_percent=>2);
gather_table_stats(ownname=>'ABHI_TEST', tabname=>'SALES', estimate_percent=>2);

Manual Steps for Optimizer Statistics Advisor Executions -Manual Mode

Steps for MANUL MODE can be categories as per below.

1=> Creating an Optimizer Statistics Advisor Task
2=> Listing Optimizer Statistics Advisor Tasks
3=> Creating Filters for an Optimizer Advisor Task
4=> Executing an Optimizer Statistics Advisor Task
5=> Generating a Report for an Optimizer Statistics Advisor Task
6=> Implementing Optimizer Statistics Advisor Recommendations

1=> Creating an Optimizer Statistics Advisor Task

This function creates an advisor task for the Optimizer Statistics Advisor.
If you do not specify a task name, then Optimizer Statistics Advisor generates one automatically.

SQL> DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  v_tname := 'my_opti_stats_advisor';
  v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
END;
/
PL/SQL procedure successfully completed.

2=> Listing Optimizer Statistics Advisor Tasks

SQL> SELECT TASK_ID,TASK_NAME, ADVISOR_NAME, CREATED, STATUS FROM USER_ADVISOR_TASKS;

   TASK_ID TASK_NAME					      ADVISOR_NAME		     CREATED   STATUS
---------- -------------------------------------------------- ------------------------------ --------- ----------
       862 MY_OPTI_STATS_ADVISOR			      Statistics Advisor	     30-MAY-22 INITIAL

SQL> SELECT DISTINCT ADVISOR_NAME FROM DBA_ADVISOR_TASKS;

ADVISOR_NAME
------------------------------
SQL Tuning Advisor
ADDM
SQL Performance Analyzer
SQL Access Advisor
Segment Advisor
Statistics Advisor
SPM Evolve Advisor

7 rows selected.

4=> Executing an Optimizer Statistics Advisor Task
The DBMS_STATS.EXECUTE_ADVISOR_TASK function executes a task for Optimizer Statistics Advisor.
If you do not specify an execution name, then Optimizer Statistics Advisor generates one automatically.

SQL> DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  v_tname := 'my_opti_stats_advisor';
  v_ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/
PL/SQL procedure successfully completed.

SQL> SELECT TASK_NAME, EXECUTION_NAME,EXECUTION_END, EXECUTION_TYPE AS TYPE, STATUS 
         FROM   USER_ADVISOR_EXECUTIONS;  

TASK_NAME			    EXECUTION_NAME EXECUTION   TYPE		     STATUS
-------------------------------------------------- -------------- --------
MY_OPTI_STATS_ADVISOR   EXEC_964	   30-MAY-22   STATISTICS	 COMPLETED

SQL> 

5=> Generating a Report for an Optimizer Statistics Advisor Task
The DBMS_STATS.REPORT_ADVISOR_TASK function generates a report for an Optimizer Statistics Advisor task.

SET SERVEROUTPUT ON
DECLARE
  l_report     CLOB;
BEGIN
    l_report    := DBMS_STATS.report_advisor_task(
                   task_name      => 'MY_OPTI_STATS_ADVISOR',
                   execution_name => 'EXEC_964');
  DBMS_OUTPUT.put_line(l_report);
END;
/

SQL> SQL>   2    3    4    5    6    7    8    9  GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name	 : MY_OPTI_STATS_ADVISOR 
 Execution Name  : EXEC_964		 
 Created	 : 05-30-22 05:35:40	 
 Last Modified	 : 05-30-22 05:48:28

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_964 of task MY_OPTI_STATS_ADVISOR, the Statistics Advisor	
 has 6 finding(s). The findings are related to the
following rules:	     
 USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, AVOIDUNNECESSARYSTATSCOLLECTION,	
 AVOIDSTALESTATS, UNLOCKNONVOLATILETABLE, USEINCREMENTAL. Please refer to the	
 finding section for detailed information.

-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:	    UseDefaultParams						
 Rule Description:  Use Default Parameters in


6=> Implementing Optimizer Statistics Advisor Recommendations
The DBMS_STATS.IMPLEMENT_ADVISOR_TASK function implements the recommendations for a specified Optimizer Statistics Advisor task.
If you do not specify an execution name, then Optimizer Statistics Advisor uses the most recent execution.

SQL> 
VARIABLE b_ret CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'MY_OPTI_STATS_ADVISOR';
  :b_ret := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/
PL/SQL procedure successfully completed.
SQL> 

Print RESULT action which performed above.

SQL> 


Creating SCRIPTS for recommendations. 
The DBMS_STATS.SCRIPT_ADVISOR_TASK function generates an editable script with recommendations for a specified Optimizer Statistics Advisor task. 

SQL> VARIABLE b_script CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'MY_OPTI_STATS_ADVISOR';
  :b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
END;
/SQL>   2    3    4    5    6    7  

PL/SQL procedure successfully completed.
 
SQL> DECLARE
  v_len    NUMBER(10);
  v_offset NUMBER(10) :=1;
  v_amount NUMBER(10) :=10000;
BEGIN
  v_len := DBMS_LOB.getlength(:b_script);
  WHILE (v_offset < v_len)
  LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:b_script,v_amount,v_offset));
    v_offset := v_offset + v_amount;
  END LOOP;
END;
/  2    3    4    5    6    7    8    9   10   11   12   13  
-- Script generated for the recommendations from execution EXEC_964
-- in the statistics advisor task MY_OPTI_STATS_ADVISOR
-- Script version 12.2
...
...