Analyzing Statistics Using Optimizer Statistics Advisor

Optimizer Statistics Advisor analyzes how optimizer statistics are gathered, and then makes recommendations.

18.1 About Optimizer Statistics Advisor

Optimizer Statistics Advisor is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks.
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.
The following figure provides a conceptual overview of Optimizer Statistics Advisor.
Figure 18-1 Optimizer Statistics Advisor

Description of "Figure 18-1 Optimizer Statistics Advisor"

18.1.1 Purpose of Optimizer Statistics Advisor

Optimizer Statistics Advisor inspects how optimizer statistics are gathered.
The advisor automatically diagnoses problems in the existing practices for gathering statistics. The advisor does not gather a new or alternative set of optimizer statistics. The output of the advisor is a report of findings and recommendations, which helps you follow best practices for gathering statistics.
Optimizer statistics play a significant part in determining the execution plan for queries. Therefore, it is critical for the optimizer to gather and maintain accurate and up-to-date statistics. The optimizer provides the `DBMS_STATS` package, which evolves from release to release, for this purpose. Typically, users develop their own strategies for gathering statistics based on specific workloads, and then use homegrown scripts to implement these strategies.

18.1.1.1 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.

18.1.1.2 Advantages of Optimizer Statistics Advisor

An advisor-based approach offers better scalability and maintainability than the traditional approach.
If best practices change in a new release, then Optimizer Statistics Advisor encodes these practices in its rules. In this way, the advisor always provides the most up-to-date recommendations.
The advisor analyzes how you are currently gathering statistics (using manual scripts, explicitly setting parameters, and so on), the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. 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.
Optimizer Statistics Advisor provides the following advantages over the traditional approach:
Provides easy-to-understand reports
The advisor applies rules to generate findings, recommendations, and actions.
Supplies scripts to implement necessary fixes without requiring changes to application code
When you implement a recommended action, benefit accrues to every execution of the improved statements. For example, if you set a global preference so that the sample size is `AUTO_SAMPLE_SIZE` rather than a suboptimal percentage, then every plan based on the improved statistics can benefit from this change.
Runs a predefined task named `AUTO_STATS_ADVISOR_TASK` once every day in the maintenance window
For the automated job to run, the `STATISTICS_LEVEL` initialization parameter must be set to `TYPICAL` or `ALL`.
Supplies an API in the `DBMS_STATS` package that enables you to create and run tasks manually, store findings and recommendations in data dictionary views, generate reports for the tasks, and implement corrections when necessary
Integrates with existing tools
The advisor integrates with SQL Tuning Advisor and AWR, which summarize the Optimizer Statistics Advisor results.

18.1.2 Optimizer Statistics Advisor Concepts

Optimizer Statistics Advisor uses the same advisor framework as Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors.

18.1.2.1 Components of Optimizer Statistics Advisor

The Optimizer Statistics Optimizer framework stores its metadata in data dictionary and dynamic performance views.
The following Venn diagram shows the relationships among rules, findings, recommendations, and actions for Optimizer Statistics Advisor. For example, all findings are derived from rules, but not all rules generate findings.
Figure 18-2 Optimizer Statistics Advisor Components

Description of "Figure 18-2 Optimizer Statistics Advisor Components"

18.1.2.1.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 rules embody Oracle best practices based on the current feature set. If the best practices change from release to release, then the Optimizer Statistics Advisor rules also change.

The advisor organizes rules into the following classes:

System
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.
Operation
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.
Object
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.

The rules check for the following problems:

How to gather statistics
For example, one rule might specify the recommended setting for an initialization parameter. Another rule might specify that statistics should be gathered at the schema level.
When to gather statistics
For example, the advisor may recommend that the maintenance window for the automatic statistics gathering job should be enabled, or that the window should be extended.
How to improve the efficiency of statistics gathering
For example, a rule might specify that default parameters should be used in DBMS_STATS, or that statistics should not be set manually.

In V$STATS_ADVISOR_RULES, each rule has a unique string ID that is usable in the DBMS_STATS procedures and reports. You can use a rule filter to specify rules that Optimizer Statistics Advisor should check. However, you cannot write new rules.

Example 18-1 Listing Rules in V$STATS_ADVISOR_RULES

The following query, with sample output, lists a subset of the rules in V$STATS_ADVISOR_RULES. The rules may change from release to release.

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 Proc.
 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.

See Also:

Oracle Database Reference to learn more about V$STATS_ADVISOR_RULES

18.1.2.1.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`. For example, the advisor queries `DBA_TAB_STATISTICS` and `DBA_IND_STATISTICS` to determine whether statistics are stale, or whether a discrepancy exists between the numbers of rows.
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. For example, a finding may show that `DBMS_STATS.GATHER_TABLE_STATS` has used `ESTIMATE_PERCENT=>0.01`, which violates the `ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZE` rule.
A finding corresponds to exactly one rule. However, a rule can generate many findings.
See Also:
Oracle Database PL/SQL Packages and Types Reference to learn more about `DBMS_STATS`
Oracle Database Reference to learn more about `ALL_TAB_STATISTICS`

18.1.2.1.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.
See Also:
"Guideline for Setting the Sample Size" to learn the guideline for the sample size
Oracle Database Reference to learn about `DBA_ADVISOR_RECOMMENDATIONS`

18.1.2.1.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`.
For example, Optimizer Statistics Advisor executes a task that performs the following steps:
Checks rules
The advisor checks conformity to the rule that stale statistics should be avoided.
Generates finding
The advisor discovers that a number of objects have no statistics.
Generates recommendation
The advisor recommends gathering statistics on the objects with no statistics.
Generates action
The advisor generates a PL/SQL script that executes `DBMS_STATS.GATHER_DATABASE_STATS`, supplying a list of objects that need to have statistics gathered.
See Also:
"Statistics Preference Overrides" to learn how to override statistics gathering preferences
"Guideline for Setting the Sample Size" to learn more about `AUTO_SAMPLE_SIZE`
Oracle Database Reference to learn about `DBA_ADVISOR_ACTIONS`

18.1.2.2 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.
As for any other task, you can configure the automated task, and generate reports. If the report recommends actions, then you can implement the actions manually.
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.
See Also:
"Configuring Automatic Optimizer Statistics Collection"
Oracle Database PL/SQL Packages and Types Reference to learn more about `DBMS_STATS.CREATE_ADVISOR_TASK`

18.1.3 Command-Line Interface to Optimizer Statistics Advisor

Perform Optimizer Statistics Advisor tasks using the `DBMS_STATS` PL/SQL package.
Table 18-1 DBMS_STATS APIs for Task Creation and Deletion
PL/SQL Procedure or Function Description
`CREATE_ADVISOR_TASK`
Creates an advisor task for Optimizer Statistics Advisor. If the task name is already specified, then the advisor uses the specified task name; otherwise, the advisor automatically generates a new task name.
`DROP_ADVISOR_TASK`
Deletes an Optimizer Statistics Advisor task and all its result data.
Table 18-2 DBMS_STATS APIs for Task Execution
PL/SQL Procedure or Function Description
`EXECUTE_ADVISOR_TASK`
Executes a previously created Optimizer Statistics Advisor task.
`INTERRUPT_ADVISOR_TASK`
Interrupts a currently executing Optimizer Statistics Advisor task. The task ends its operations as it would in a normal exit, enabling you to access intermediate results. You can resume the task later.
`CANCEL_ADVISOR_TASK`
Cancels an Optimizer Statistics Advisor task execution, and removes all intermediate results of the current execution.
`RESET_ADVISOR_TASK`
Resets an Optimizer Statistics Advisor task execution to its initial state. Call this procedure on a task that is not currently executing.
`RESUME_ADVISOR_TASK`
Resumes the Optimizer Statistics Advisor task execution that was most recently interrupted.
Table 18-3 DBMS_STATS APIs for Advisor Reports
PL/SQL Procedure or Function Description
`REPORT_STATS_ADVISOR_TASK`
Reports the results of an Optimizer Statistics Advisor task.
`GET_ADVISOR_RECS`
Generates a recommendation report on the given item.
Table 18-4 DBMS_STATS APIs for Task and Filter Configuration
PL/SQL Procedure or Function Description
`CONFIGURE_ADVISOR_TASK`
Configures the Optimizer Statistics Advisor lists for the execution, reporting, script generation, and implementation of an advisor task.
`GET_ADVISOR_OPR_FILTER`
Creates an operation filter for a statistics operation.
`CONFIGURE_ADVISOR_RULE_FILTER`
Configures the rule filter for an Optimizer Statistics Advisor task.
`CONFIGURE_ADVISOR_OPR_FILTER`
Configures the operation filter for an Optimizer Statistics Advisor task.
`CONFIGURE_ADVISOR_OBJ_FILTER`
Configures the object filter for an Optimizer Statistics Advisor task.
`SET_ADVISOR_TASK_PARAMETER`
Updates the value of an Optimizer Statistics Advisor task parameter. Valid parameters are `TIME_LIMIT` and `OP_START_TIME`.
Table 18-5 DBMS_STATS APIs for Implementation of Recommended Actions
PL/SQL Procedure or Function Description
`SCRIPT_ADVISOR_TASK`
Gets the script that implements the recommended actions for the problems found by the advisor. You can check this script, and then choose which actions to execute.
`IMPLEMENT_ADVISOR_TASK`
Implements the actions recommended by the advisor based on results from a specified Optimizer Statistics Advisor execution.
See Also:
Oracle Database PL/SQL Packages and Types Reference to learn about the `DBMS_STATS` package

PL/SQL Procedure or Function	Description
`CREATE_ADVISOR_TASK`	Creates an advisor task for Optimizer Statistics Advisor. If the task name is already specified, then the advisor uses the specified task name; otherwise, the advisor automatically generates a new task name.
`DROP_ADVISOR_TASK`	Deletes an Optimizer Statistics Advisor task and all its result data.

PL/SQL Procedure or Function	Description
`EXECUTE_ADVISOR_TASK`	Executes a previously created Optimizer Statistics Advisor task.
`INTERRUPT_ADVISOR_TASK`	Interrupts a currently executing Optimizer Statistics Advisor task. The task ends its operations as it would in a normal exit, enabling you to access intermediate results. You can resume the task later.
`CANCEL_ADVISOR_TASK`	Cancels an Optimizer Statistics Advisor task execution, and removes all intermediate results of the current execution.
`RESET_ADVISOR_TASK`	Resets an Optimizer Statistics Advisor task execution to its initial state. Call this procedure on a task that is not currently executing.
`RESUME_ADVISOR_TASK`	Resumes the Optimizer Statistics Advisor task execution that was most recently interrupted.

PL/SQL Procedure or Function	Description
`REPORT_STATS_ADVISOR_TASK`	Reports the results of an Optimizer Statistics Advisor task.
`GET_ADVISOR_RECS`	Generates a recommendation report on the given item.

PL/SQL Procedure or Function	Description
`CONFIGURE_ADVISOR_TASK`	Configures the Optimizer Statistics Advisor lists for the execution, reporting, script generation, and implementation of an advisor task.
`GET_ADVISOR_OPR_FILTER`	Creates an operation filter for a statistics operation.
`CONFIGURE_ADVISOR_RULE_FILTER`	Configures the rule filter for an Optimizer Statistics Advisor task.
`CONFIGURE_ADVISOR_OPR_FILTER`	Configures the operation filter for an Optimizer Statistics Advisor task.
`CONFIGURE_ADVISOR_OBJ_FILTER`	Configures the object filter for an Optimizer Statistics Advisor task.
`SET_ADVISOR_TASK_PARAMETER`	Updates the value of an Optimizer Statistics Advisor task parameter. Valid parameters are `TIME_LIMIT` and `OP_START_TIME`.

PL/SQL Procedure or Function	Description
`SCRIPT_ADVISOR_TASK`	Gets the script that implements the recommended actions for the problems found by the advisor. You can check this script, and then choose which actions to execute.
`IMPLEMENT_ADVISOR_TASK`	Implements the actions recommended by the advisor based on results from a specified Optimizer Statistics Advisor execution.

18.2 Basic Tasks for Optimizer Statistics Advisor

This section explains the basic workflow for using Optimizer Statistics Advisor. All procedures and functions are in the DBMS_STATS package.

The following figure shows the automatic and manual paths in the workflow. If AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window, then your workflow begins by querying the report. In the manual workflow, you must use PL/SQL to create and execute the tasks.

Figure 18-3 Basic Tasks for Optimizer Statistics Advisor

Description of "Figure 18-3 Basic Tasks for Optimizer Statistics Advisor"

Typically, you perform Optimizer Statistics Advisor steps in the sequence shown in the following table.

Table 18-6 Optimizer Statistics Advisor Workflow

Step	Description	To Learn More
1	Create an Optimizer Advisor task using `DBMS_STATS.CREATE_ADVISOR_TASK` (manual workflow only).	"Creating an Optimizer Statistics Advisor Task"
2	Optionally, list executions of advisor tasks by querying `DBA_ADVISOR_EXECUTIONS`.	"Listing Optimizer Statistics Advisor Tasks"
3	Optionally, configure a filter for the task using the `DBMS_STATS.CONFIGURE_ADVISOR_*_FILTER` procedures.	"Creating Filters for an Optimizer Advisor Task"
4	Execute the advisor task using `DBMS_STATS.EXECUTE_ADVISOR_TASK` (manual workflow only).	"Executing an Optimizer Statistics Advisor Task"
5	Generate an advisor report.	"Generating a Report for an Optimizer Statistics Advisor Task"
6	Implement the recommendations in either of following ways: Implement all recommendations automatically using `DBMS_STATS.IMPLEMENT_ADVISOR_TASK`. Generate a PL/SQL script that implements recommendations using `DBMS_STATS.SCRIPT_ADVISOR_TASK`, edit this script, and then run it manually.	"Implementing Actions Recommended by Optimizer Statistics Advisor" and "Generating a Script Using Optimizer Statistics Advisor"

Example 18-2 Basic Script for Optimizer Statistics Advisor in Manual Workflow

This script illustrates a basic Optimizer Statistics Advisor session. It creates a task, executes it, generates a report, and then implements the recommendations.

DECLARE
  v_tname   VARCHAR2(128) := 'my_task';
  v_ename   VARCHAR2(128) := NULL;
  v_report  CLOB := null;
  v_script  CLOB := null;
  v_implementation_result CLOB;
BEGIN
  -- create a task
  v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);

  -- execute the task
  v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);

  -- view the task report
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);

  -- implement all recommendations
  v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_STATS package

18.2.1 Creating an Optimizer Statistics Advisor Task

The DBMS_STATS.CREATE_ADVISOR_TASK function creates a task for Optimizer Statistics Advisor. If you do not specify a task name, then Optimizer Statistics Advisor generates one automatically.

Prerequisites

To execute this subprogram, you must have the ADVISOR privilege.

Note:

This subprogram executes using invoker's rights.

To create an Optimizer Statistics Advisor task:

In SQL*Plus, log in to the database as a user with the necessary privileges.
Execute the DBMS_STATS.CREATE_ADVISOR_TASK function in the following form, where tname is the name of the task and ret is the variable that contains the returned output:
```
EXECUTE ret := DBMS_STATS.CREATE_ADVISOR_TASK('tname');
```
For example, to create the task opt_adv_task1, use the following code:
```
DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
END;
/
```

Optionally, query USER_ADVISOR_TASKS:

SELECT TASK_NAME, ADVISOR_NAME, CREATED, STATUS FROM USER_ADVISOR_TASKS;

Sample output appears below:

TASK_NAME       ADVISOR_NAME         CREATED   STATUS
--------------- -------------------- --------- -----------
OPT_ADV_TASK1   Statistics Advisor   05-SEP-16 INITIAL

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about CREATE_ADVISOR_TASK

18.2.2 Listing Optimizer Statistics Advisor Tasks

The DBA_ADVISOR_EXECUTIONS view lists executions of Optimizer Statistics Advisor tasks.

To list Optimizer Statistics Advisor tasks:

In SQL*Plus, log in to the database as a user with administrator privileges.

Query DBA_ADVISOR_EXECUTIONS as follows:

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;

The following sample output shows 8 executions:

EXECUTION_NAME EXECUTION STATUS
-------------- --------- -----------
EXEC_1         27-AUG-16 COMPLETED
EXEC_17        28-AUG-16 COMPLETED
EXEC_42        29-AUG-16 COMPLETED
EXEC_67        30-AUG-16 COMPLETED
EXEC_92        01-SEP-16 COMPLETED
EXEC_117       02-SEP-16 COMPLETED
EXEC_142       03-SEP-16 COMPLETED
EXEC_167       04-SEP-16 COMPLETED

8 rows selected.

See Also:

Oracle Database Reference to learn more about DBA_ADVISOR_EXECUTIONS

18.2.3 Creating Filters for an Optimizer Advisor Task

Filters enable you to include or exclude objects, rules, and operations from Optimizer Statistics Advisor tasks.

18.2.3.1 About Filters for Optimizer Statistics Advisor

A filter is the use of `DBMS_STATS` to restrict an Optimizer Statistics Advisor task to a user-specified set of rules, schemas, or operations.
Filters are useful for including or excluding a specific set of results. For example, you can configure an advisor task to include only recommendations for the `sh` schema. Also, you can exclude all violations of the rule for stale statistics. The primary advantage of filters is the ability to ignore recommendations that you are not interested in, and reduce the overhead of the advisor task.
The simplest way to create filters is to use the following `DBMS_STATS` procedures either individually or in combination:
`CONFIGURE_ADVISOR_OBJ_FILTER`
Use this procedure to include or exclude the specified database schemas or objects. The object filter takes in an owner name and an object name, with wildcards (`%`) supported.
`CONFIGURE_ADVISOR_RULE_FILTER`
Use this procedure to include or exclude the specified rules. Obtain the names of rules by querying `V$STATS_ADVISOR_RULES`.
`CONFIGURE_ADVISOR_OPR_FILTER`
Use this procedure to include or exclude the specified `DBMS_STATS` operations. Obtain the IDs and names for operations by querying `DBA_OPTSTAT_OPERATIONS`.
For the preceding functions, you can specify the type of operation to which the filter applies: `EXECUTE`, `REPORT`, `SCRIPT`, and `IMPLEMENT`. You can also combine types, as in `EXECUTE + REPORT`. Null indicates that the filter applies to all types of advisor operations.
See Also:
Oracle Database Reference to learn more about `V$STATS_ADVISOR_RULES`
Oracle Database PL/SQL Packages and Types Reference to learn more about `DBMS_STATS`

18.2.3.2 Creating an Object Filter for an Optimizer Advisor Task

The DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER function creates a rule filter for a specified Optimizer Statistics Advisor task. The function returns a CLOB that contains the updated values of the filter.

You can use either of the following basic strategies:

Include findings for all objects (by default, all objects are considered), and then exclude findings for specified objects.
Exclude findings for all objects, and then include findings only for specified objects.

Prerequisites

To use the DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER function, you must meet the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
You must be the owner of the task.

Note:

This subprogram executes using invoker's rights.

To create an object filter:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
Either exclude or include objects for a specified task using the DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER function.
Invoke the function in the following form, where the placeholders are defined as follows:
- report is the CLOB variable that contains the returned XML.
- tname is the name of the task.
- opr_type is the type of operation to perform.
- rule is the name of the rule.
- owner is the schema for the objects.
- table is the name of the table.
- action is the name of the action: ENABLE, DISABLE, DELETE, or SHOW.
```
BEGIN
  report := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , ownname            => 'owner'
  , tabname            => 'table'
  , action             => 'action' );
END;
```

Example 18-3 Including Only Objects in a Single Schema

In this example, for the task named opt_adv_task1, your goal is to disable recommendations for all objects except those in the sh schema. User account sh has been granted ADVISOR and READ ANY TABLE privileges. You perform the following steps:

Drop any existing task named opt_adv_task1.

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

Create a procedure named sh_obj_filter that restricts a specified task to objects in the sh schema.

CREATE OR REPLACE PROCEDURE sh_obj_filter(p_tname IN VARCHAR2) IS
   v_retc CLOB;
BEGIN
   -- Filter out all objects that are not in the sh schema
   v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
               task_name          => p_tname
             , stats_adv_opr_type => 'EXECUTE'
             , rule_name          => NULL
             , ownname            => NULL
             , tabname            => NULL
             , action             => 'DISABLE' );

   v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
               task_name          => p_tname
             , stats_adv_opr_type => 'EXECUTE'
             , rule_name          => NULL
             , ownname            => 'SH'
             , tabname            => NULL
             , action             => 'ENABLE' );
END;
/
SHOW ERRORS

Create a task named opt_adv_task1, and then execute the sh_obj_filter procedure for this task.

DECLARE
  v_tname VARCHAR2(32767);
  v_ret VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  sh_obj_filter(v_tname);
END;
/

Execute the task opt_adv_task1.

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
begin
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER

18.2.3.3 Creating a Rule Filter for an Optimizer Advisor Task

The DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER function creates a rule filter for a specified Optimizer Statistics Advisor task. The function returns a CLOB that contains the updated values of the filter.

You can use either of the following basic strategies:

Enable all rules (by default, all rules are enabled), and then disable specified rules.
Disable all rules, and then enable only specified rules.

Prerequisites

To use the DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER function, you must meet the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
You must be the owner of the task.

Note:

This subprogram executes using invoker's rights.

To create a rule filter:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

Obtain the names of the advisor rules by querying V$STATS_ADVISOR_RULES.

For example, query the view as follows (partial sample output included):

SET LINESIZE 200
SET PAGESIZE 100
COL ID FORMAT 99
COL NAME FORMAT a27
COL DESCRIPTION FORMAT a54

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
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
...

Either exclude or include rules for a specified task using the DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER function.
Invoke the function in the following form, where the placeholders are defined as follows:
- tname is the name of the task.
- report is the CLOB variable that contains the returned XML.
- opr_type is the type of operation to perform.
- rule is the name of the rule.
- action is the name of the action: ENABLE, DISABLE, DELETE, or SHOW.
```
BEGIN
  report := DBMS_STATS.DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , action             => 'action' );
END;
```

Example 18-4 Excluding the Rule for Stale Statistics

In this example, you know that statistics are stale because the automated statistics job did not run. You want to generate a report for the task named opt_adv_task1, but do not want to clutter it with recommendations about stale statistics.

You query V$STATS_ADVISOR_RULES for rules that deal with stale statistics (sample output included):

COL NAME FORMAT a15
SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
WHERE  DESCRIPTION LIKE '%tale%'
ORDER BY RULE_ID;

 ID NAME            RULE_TYPE DESCRIPTION
--- --------------- --------- -----------------------------------------
 12 AvoidStaleStats OBJECT    Avoid objects with stale or no statistics

You configure a filter using CONFIGURE_ADVISOR_RULE_FILTER, specifying that task execution should exclude the rule AvoidStaleStats, but honor all other rules:

VARIABLE b_ret CLOB
BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => 'AvoidStaleStats'
,    action             => 'DISABLE' );
END;
/

Example 18-5 Including Only the Rule for Avoiding Stale Statistics

This example is the inverse of the preceding example. You want to generate a report for the task named opt_adv_task1, but want to see only recommendations about stale statistics.

Query V$STATS_ADVISOR_RULES for rules that deal with stale statistics (sample output included):

COL NAME FORMAT a15

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
WHERE  DESCRIPTION LIKE '%tale%'
ORDER BY RULE_ID;

 ID NAME            RULE_TYPE DESCRIPTION
--- --------------- --------- -----------------------------------------
 12 AvoidStaleStats OBJECT    Avoid objects with stale or no statistics

Configure a filter using CONFIGURE_ADVISOR_RULE_FILTER, specifying that task execution should exclude all rules:

VARIABLE b_ret CLOB
BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => null
,    action             => 'DISABLE' );
END;
/

Configure a filter that enables only the AvoidStaleStats rule:

BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => 'AvoidStaleStats'
,    action             => 'ENABLE' );
END;
/

See Also:

Oracle Database Reference to learn more about V$STATS_ADVISOR_RULES
Oracle Database PL/SQL Packages and Types Reference to learn more about CONFIGURE_ADVISOR_RULE_FILTER

18.2.3.4 Creating an Operation Filter for an Optimizer Advisor Task

The DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER function creates an operation filter for a specified Optimizer Statistics Advisor task. The function returns a CLOB that contains the updated values of the filter.

You can use either of the following basic strategies:

Disable all operations, and then enable only specified operations.
Enable all operations (by default, all operations are enabled), and then disable specified operations.

The DBA_OPTSTAT_OPERATIONS view contains the IDs of statistics-related operations.

Prerequisites

To use DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER function, you must meet the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
Note:
This subprogram executes using invoker's rights.
You must be the owner of the task.
To query the DBA_OPTSTAT_OPERATIONS view, you must have the SELECT ANY TABLE privilege.

To create an operation filter:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

Query the types of operations.

For example, list all distinct operations in DBA_OPTSTAT_OPERATIONS (sample output included):

SQL> SELECT DISTINCT(OPERATION) FROM DBA_OPTSTAT_OPERATIONS ORDER BY OPERATION;

OPERATION
-----------------------
gather_dictionary_stats
gather_index_stats
gather_schema_stats
gather_table_stats
purge_stats
set_system_stats

Obtain the IDs of the operations to be filtered by querying DBA_OPTSTAT_OPERATIONS.
For example, to obtain IDs for all statistics gathering operations for tables and indexes in the SYS and sh schemas, use the following query:
```
SELECT ID 
FROM   DBA_OPTSTAT_OPERATIONS
WHERE  (  OPERATION = 'gather_table_stats'
          OR OPERATION = 'gather_index_stats')
AND    (  TARGET LIKE 'SH.%'
          OR TARGET LIKE 'SYS.%');
```
Exclude or include rules for a specified task using the DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER function, specifying the IDs obtained in the previous step.
Invoke the function in the following form, where the placeholders are defined as follows:
- report is the CLOB variable that contains the returned XML.
- tname is the name of the task.
- opr_type is the type of operation to perform. This value cannot be null.
- rule is the name of the rule.
- opr_id is the ID (from DBA_OPTSTAT_OPERATIONS.ID) of the operation to perform. This value cannot be null.
- action is the name of the action: ENABLE, DISABLE, DELETE, or SHOW.
```
BEGIN
  report := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , operation_id       => 'op_id'
  , action             => 'action' );
END;
```

Example 18-6 Excluding Operations for Gathering Table Statistics

In this example, your goal is to exclude operations that gather table statistics in the hr schema. User account stats has been granted the DBA role, ADVISOR privilege, and SELECT ON DBA_OPTSTAT_OPERATIONS privilege. You perform the following steps:

Drop any existing task named opt_adv_task1.

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

Create a procedure named opr_filter that configures a task to advise on all operations except those that gather statistics for tables in the hr schema.

CREATE OR REPLACE PROCEDURE opr_filter(p_tname IN VARCHAR2) IS
   v_retc CLOB;
BEGIN
   -- For all rules, prevent the advisor from operating 
   -- on the operations selected in the following query
   FOR rec IN 
     (SELECT ID FROM DBA_OPTSTAT_OPERATIONS WHERE OPERATION = 'gather_table_stats' AND TARGET LIKE 'HR.%')
   LOOP
     v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER(
                 task_name          => p_tname
               , stats_adv_opr_type => NULL
               , rule_name          => NULL
               , operation_id       => rec.id
               , action             => 'DISABLE');
   END LOOP;
END;
/
SHOW ERRORS

Create a task named opt_adv_task1, and then execute the opr_filter procedure for this task.

DECLARE
  v_tname VARCHAR2(32767);
  v_ret VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  opr_filter(v_tname);
END;
/

Execute the task opt_adv_task1.

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
begin
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

Print the report.

SPOOL /tmp/rep.txt
SET LONG 1000000
COLUMN report FORMAT A200
SET LINESIZE 250
SET PAGESIZE 1000

SELECT DBMS_STATS.REPORT_ADVISOR_TASK(
         task_name      => 'opt_adv_task1'
       , execution_name => NULL
       , type           => 'TEXT'
       , section        => 'ALL' 
       ) AS report
FROM   DUAL;
SPOOL OFF

See Also:

Oracle Database Reference to learn more about DBA_OPTSTAT_OPERATIONS
Oracle Database PL/SQL Packages and Types Reference to learn more about CONFIGURE_ADVISOR_OPR_FILTER

18.2.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.

The results of performing this task depend on the privileges of the executing user:

SYSTEM level
Only users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task on system-level rules.
Operation level
The results depend on the following privileges:
- Users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task for all statistics operations.
- Users with the ANALYZE ANY privilege but not the ANALYZE ANY DICTIONARY privilege can perform this task for statistics operations related to any schema except SYS.
- Users with the ANALYZE ANY DICTIONARY privilege but not the ANALYZE ANY privilege can perform this task for statistics operations related to their own schema and the SYS schema.
- Users with neither the ANALYZE ANY nor the ANALYZE ANY DICTIONARY privilege can only perform this operation for statistics operations relating to their own schema.
Object level
Users can perform this task for any object for which they have statistics collection privileges.

Prerequisites

This task has the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
You must be the owner of the task.
If you specify an execution name, then this name must not conflict with an existing execution.

Note:

This subprogram executes using invoker's rights.

To execute an Optimizer Statistics Advisor task:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
Execute the DBMS_STATS.EXECUTE_ADVISOR_TASK function in the following form, where tname is the name of the task, execname is the optional name of the execution, and ret is the variable that contains the returned output:
```
EXECUTE ret := DBMS_STATS.EXECUTE_ADVISOR_TASK('tname','execname');
```
For example, to execute the task opt_adv_task1, use the following code:
```
DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/
```

Optionally, obtain details about the execution by querying USER_ADVISOR_EXECUTIONS:

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

Sample output appears below:

TASK_NAME       EXECUTION_NAME       EXECUTION TYPE       STATUS
--------------- -------------------- --------- ---------- -----------
OPT_ADV_TASK1   EXEC_136             23-NOV-15 STATISTICS COMPLETED

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about EXECUTE_ADVISOR_TASK

18.2.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.

The report contains the following sections:

General information
This section describes the task name, execution name, creation date, and modification date.
Summary
This section summarizes the findings and rules violated by the findings.
Findings
Each finding section lists the relevant rule and findings. If the advisor has a recommendation, then the recommendation is described. In some cases, a recommendation also has a rationale.

The name of the automated Optimizer Statistics Advisor task is AUTO_STATS_ADVISOR_TASK. If you follow the automated workflow, then you only need to query the automatically generated report.

Prerequisites

To generate a report with the DBMS_STATS.REPORT_ADVISOR_TASK function, you must meet the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
You must be the owner of the task.

Note:

This subprogram executes using invoker's rights.

The results of performing this task depend on the privileges of the executing user:

SYSTEM level
Only users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task on system-level rules.
Operation level
The results depend on the following privileges:
- Users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task for all statistics operations.
- Users with the ANALYZE ANY privilege but not the ANALYZE ANY DICTIONARY privilege can perform this task for statistics operations related to any schema except SYS.
- Users with the ANALYZE ANY DICTIONARY privilege but not the ANALYZE ANY privilege can perform this task for statistics operations related to their own schema and the SYS schema.
- Users with neither the ANALYZE ANY nor the ANALYZE ANY DICTIONARY privilege can only perform this operation for statistics operations relating to their own schema.
Object level
Users can perform this task for any object for which they have statistics collection privileges.

To generate an Optimizer Statistics Advisor report:

In SQL*Plus, log in to the database as a user with ADVISOR privileges.

Query the DBMS_STATS.REPORT_ADVISOR_TASK function output.

Use the following query, where the placeholders have the following definitions:

tname is the name of the task.
exec is the name of the execution.
type is the type of output: TEXT, HTML, or XML.
sect is the section of the report: SUMMARY, FINDINGS, ERRORS, and ALL.
lvl is the format of the report: BASIC, TYPICAL, ALL, or SHOW_HIDDEN.

SET LINESIZE 3000
SET LONG 500000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.REPORT_ADVISOR_TASK('tname', 'exec', 'type', 
       'sect', 'lvl') AS REPORT
FROM   DUAL;

For example, to print a report for AUTO_STATS_ADVISOR_TASK, use the following query:

SELECT DBMS_STATS.REPORT_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK', NULL,
       'TEXT', 'ALL', 'ALL') AS REPORT 
FROM   DUAL;

The following sample report shows four findings:

GENERAL INFORMATION
-------------------------------------------------------------------------------
 Task Name        : AUTO_STATS_ADVISOR_TASK
 Execution Name   : EXEC_136	
 Created          : 09-05-16 02:52:34 
 Last Modified    : 09-05-16 12:31:24 
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_136 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
 has 4 findings. The findings are related to the following rules: 
 AVOIDSETPROCEDURES, USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, NOTUSEINCREMENTAL. 
Please refer to the finding section for detailed information.

-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:	    AvoidSetProcedures
 Rule Description:  Avoid Set Statistics Procedures				
 Finding:  There are 5 SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures being	
           used for statistics gathering.
 Recommendation:  Do not use SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures.
                  Gather statistics instead of setting them.			
 Rationale:  SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS will cause bad plans due to
             wrong or inconsistent statistics.				   
----------------------------------------------------
 Rule Name:	    UseDefaultParams						
 Rule Description:  Use Default Parameters in Statistics Collection Procedures
 Finding:  There are 367 statistics operations using nondefault parameters.
 Recommendation:  Use default parameters for statistics operations.
 Example:

 -- Gathering statistics for 'SH' schema using all default parameter values:
 BEGIN dbms_stats.gather_schema_stats('SH'); END;
 Rationale:  Using default parameter values for statistics gathering operations
             is more efficient.	
----------------------------------------------------
 Rule Name:	    UseGatherSchemaStats
 Rule Description:  Use gather_schema_stats procedure
 Finding:  There are 318 uses of GATHER_TABLE_STATS.
 Recommendation:  Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
 Example:

 -- Gather statistics for 'SH' schema:
 BEGIN dbms_stats.gather_schema_stats('SH'); END;
 Rationale:  GATHER_SCHEMA_STATS has more options available, including checking
             for staleness and gathering statistics concurrently. Also it is
             more maintainable for new tables added to the schema. If you only
             want to gather statistics for certain tables in the schema, specify
             them in the obj_filter_list parameter of GATHER_SCHEMA_STATS.						
----------------------------------------------------
 Rule Name:	    NotUseIncremental

 Rule Description:  Statistics should not be maintained incrementally when it is not
 Finding:  Incremental option has been turned on for 10 tables, which will not benefit
           from using the incremental option.
 Schema:
 SH
 Objects:
 CAL_MONTH_SALES_MV    
 CAL_MONTH_SALES_MV    
 CHANNELS
 COUNTRIES   
 CUSTOMERS    
 DIMENSION_EXCEPTIONS    
 FWEEK_PSCAT_SALES_MV    
 FWEEK_PSCAT_SALES_MV    
 PRODUCTS
 PROMOTIONS
 SUPPLEMENTARY_DEMOGRAPHICS 
 TIMES

 Recommendation:  Do not use the incremental option for statistics gathering on these 
                  objects.						
 Example:									
 --
 Turn off the incremental option for 'SH.SALES':
 dbms_stats.set_table_prefs('SH', 'SALES', 'INCREMENTAL', 'FALSE');
 Rationale:  The overhead of using the incremental option on these tables
             outweighs the benefit of using the incremental option.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about REPORT_ADVISOR_TASK

18.2.6 Implementing Optimizer Statistics Advisor Recommendations

You can either implement all recommendations automatically using `DBMS_STATS.IMPLEMENT_ADVISOR_TASK`, or generate an editable script using `DBMS_STATS.SCRIPT_ADVISOR_TASK`.

18.2.6.1 Implementing Actions Recommended by Optimizer Statistics Advisor

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.

The simplest means of implementing recommendations is using DBMS_STATS.IMPLEMENT_ADVISOR_TASK. In this case, no generation of a script is necessary. You can specify that the advisor ignore the existing filters (level=>'ALL') or use the default, which honors the existing filters (level=>'TYPICAL').

Prerequisites

To use DBMS_STATS.IMPLEMENT_ADVISOR_TASK, you must meet the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
You must be the owner of the task.

Note:

This subprogram executes using invoker's rights.

The results of performing this task depend on the privileges of the executing user:

SYSTEM level
Only users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task on system-level rules.
Operation level
The results depend on the following privileges:
- Users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task for all statistics operations.
- Users with the ANALYZE ANY privilege but not the ANALYZE ANY DICTIONARY privilege can perform this task for statistics operations related to any schema except SYS.
- Users with the ANALYZE ANY DICTIONARY privilege but not the ANALYZE ANY privilege can perform this task for statistics operations related to their own schema and the SYS schema.
- Users with neither the ANALYZE ANY nor the ANALYZE ANY DICTIONARY privilege can only perform this operation for statistics operations relating to their own schema.
Object level
Users can perform this task for any object for which they have statistics collection privileges.

To implement advisor actions:

In SQL*Plus, log in to the database as a user with the necessary privileges.
Execute the DBMS_STATS.IMPLEMENT_ADVISOR_TASK function in the following form, where the placeholders have the following definitions:
- tname is the name of the task.
- result is the CLOB variable that contains a list of the recommendations that have been successfully implemented.
- fltr_lvl is the level of implementation: TYPICAL (existing filters honored) or ALL (filters ignored).
```
BEGIN
  result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK('tname', level => fltr_lvl);
END;
```
For example, to implement all recommendations for the task opt_adv_task1, use the following code:
```
VARIABLE b_ret CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  :b_ret := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/
```

Optionally, print the XML output to confirm the implemented actions.

For example, to print the XML returned in the previous step, use the following code (sample output included):

SET LONG 10000
SELECT XMLType(:b_ret) AS imp_results FROM DUAL;

IMP_RESULTS
------------------------------------
<implementation_results>
  <rule NAME="AVOIDSETPROCEDURES">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEGATHERSCHEMASTATS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="AVOIDSETPROCEDURES">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEGATHERSCHEMASTATS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEDEFAULTPARAMS">
    <implemented>no</implemented>
  </rule>
  <rule NAME="USEDEFAULTPARAMS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="NOTUSEINCREMENTAL">
    <implemented>yes</implemented>
  </rule>
</implementation_results>

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_STATS.IMPLEMENT_ADVISOR_TASK

18.2.6.2 Generating a Script Using Optimizer Statistics Advisor

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

Unlike IMPLEMENT_ADVISOR_TASK, the SCRIPT_ADVISOR_TASK generates a script that you can edit before execution. The output script contains both comments and executable code. As with IMPLEMENT_ADVISOR_TASK, you can specify that the advisor ignore the existing filters (level=>'ALL') or use the default, which honors the existing filters (level=>'TYPICAL'). You can specify that the function returns the script as a CLOB and file, or only a CLOB.

Prerequisites

To use the DBMS_STATS.SCRIPT_ADVISOR_TASK function, you must meet the following prerequisites:

To execute this subprogram, you must have the ADVISOR privilege.
You must be the owner of the task.

Note:

This subprogram executes using invoker's rights.

The results of performing this task depend on the privileges of the executing user:

SYSTEM level
Only users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task on system-level rules.
Operation level
The results depend on the following privileges:
- Users with both the ANALYZE ANY and ANALYZE ANY DICTIONARY privileges can perform this task for all statistics operations.
- Users with the ANALYZE ANY privilege but not the ANALYZE ANY DICTIONARY privilege can perform this task for statistics operations related to any schema except SYS.
- Users with the ANALYZE ANY DICTIONARY privilege but not the ANALYZE ANY privilege can perform this task for statistics operations related to their own schema and the SYS schema.
- Users with neither the ANALYZE ANY nor the ANALYZE ANY DICTIONARY privilege can only perform this operation for statistics operations relating to their own schema.
Object level
Users can perform this task for any object for which they have statistics collection privileges.

To generate an advisor script:

In SQL*Plus, log in to the database as a user with ADVISOR privileges.
Execute the DBMS_STATS.SCRIPT_ADVISOR_TASK function in the following form, where the placeholders have the following definitions:
- tname is the name of the task.
- exec is the name of the execution (default is null).
- dir is the name of the directory (default is null).
- result is the CLOB variable that contains a list of the recommendations that have been successfully implemented.
- filter_lvl is the level of implementation: TYPICAL (existing filters honored) or ALL (filters ignored).
```
BEGIN
  result := DBMS_STATS.SCRIPT_ADVISOR_TASK('tname', 
            execution_name => 'exec', dir_name => 'dir', 
            level => 'filter_lvl');
END;
```
For example, to generate a script that contains recommendations for the task opt_adv_task1, use the following code:
```
VARIABLE b_script CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  :b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
END;
/
```
Note:
If you do not specify an execution name, then Optimizer Statistics Advisor uses the most recent execution.

Print the script.

For example, to print the script returned in the previous step, use the following code (sample output included):

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;
/

The following example shows a sample script:

-- Script generated for the recommendations from execution EXEC_23
-- in the statistics advisor task OPT_ADV_TASK1
-- Script version 12.2

-- No scripts will be provided for the rule AVOIDSETPROCEDURES.  Please check the 
-- report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please check 
-- the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please
-- check the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES. Please check the 
-- report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please 
-- check the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please check 
-- the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report 
-- for more details.

-- Scripts for rule USEDEFAULTPARAMS
-- Rule Description: Use Default Parameters in Statistics Collection Procedures
-- Use the default preference value for parameters

begin dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end;
/

-- Scripts for rule USEDEFAULTOBJECTPREFERENCE
-- Rule Description: Use Default Object Preference for statistics collection
-- Setting object-level preferences to default values
-- setting CASCADE to default value for object level preference
-- setting ESTIMATE_PERCENT to default value for object level preference
-- setting METHOD_OPT to default value for object level preference
-- setting GRANULARITY to default value for object level preference
-- setting NO_INVALIDATE to default value for object levelpreference

-- Scripts for rule USEINCREMENTAL
-- Rule Description: Statistics should be maintained incrementally when it is
-- beneficial.
-- Turn on the incremental option for those objects for which using incremental is
-- helpful.

-- Scripts for rule UNLOCKNONVOLATILETABLE
-- Rule Description: Statistics for objects with non-volatile should not be locked
-- Unlock statistics for objects that are not volatile.

-- Scripts for rule LOCKVOLATILETABLE
-- Rule Description: Statistics for objects with volatile data should be locked
-- Lock statistics for volatile objects.

-- Scripts for rule NOTUSEINCREMENTAL
-- Rule Description: Statistics should not be maintained incrementally when it is not
   beneficial
-- Turn off incremental option for those objects for which using incremental is not
-- helpful.

begin dbms_stats.set_table_prefs('SH', 'CAL_MONTH_SALES_MV', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'CHANNELS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'COUNTRIES', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'CUSTOMERS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'DIMENSION_EXCEPTIONS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'FWEEK_PSCAT_SALES_MV', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'PRODUCTS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'PROMOTIONS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'SUPPLEMENTARY_DEMOGRAPHICS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'TIMES', 'INCREMENTAL', 'FALSE'); end;
/

-- Scripts for rule USEAUTODEGREE
-- Rule Description: Use Auto Degree for statistics collection
-- Turn on auto degree for those objects for which using auto degree is helpful.

-- Scripts for rule AVOIDSTALESTATS
-- Rule Description: Avoid objects with stale or no statistics
-- Gather statistics for those objcts that are missing or have no statistics.

-- Scripts for rule MAINTAINSTATSCONSISTENCY
-- Rule Description: Statistics of dependent objects should be consistent
-- Gather statistics for those objcts that are missing or have no statistics.

The Optimizer Statistics Advisor was introduced in Oracle Database 12c Release 2 (12.2) to help validate the quality of the optimizer statistics and provide corrective actions, should they be necessary. The DBMS_STATS package has been extended to include routines to interact with the advisor.

Optimizer Statistics Advisor Rules
AUTO_STATS_ADVISOR_TASK
Generate a Report
Implement Advisor Recommendations
Manually Execute the Advisor
Configure Advisor Tasks
Considerations
Optimizer Statistics Advisor Rules

The Optimizer Statistics Advisor works through a list of rules provided by Oracle, which represent "best practices" for optimizer statistics. It checks each rule and where necessary generates findings, recommendations and actions involving calls to the DBMS_STATS package to take corrective measures. The list of rules can be displayed using the V$STATS_ADVISOR_RULES view. They may change over time with subsequent patches and releases.

SET LINESIZE 200

COLUMN name FORMAT A35
COLUMN description FORMAT A75

SELECT *
FROM   v$stats_advisor_rules
ORDER BY rule_id;

   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.

SQL>

AUTO_STATS_ADVISOR_TASK

The Optimizer Statistics Advisor runs a task called AUTO_STATS_ADVISOR_TASK during the maintenance window, so by default you don't need to do anything to start seeing advice about your database statistics. Like other advisors, we use the DBA_ADVISOR_EXECUTIONS view to identify the executions of interest.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER= pdb1;

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET LINESIZE 150
COLUMN task_name FORMAT A25
COLUMN execution_name FORMAT A20
COLUMN execution_end FORMAT A20
COLUMN execution_type FORMAT A20

SELECT task_name,
       execution_name,
       execution_start,
       execution_end,
       execution_type,
       status
FROM   dba_advisor_executions
WHERE  task_name = 'AUTO_STATS_ADVISOR_TASK'
AND    execution_end >= SYSDATE-2
ORDER BY 3;

TASK_NAME		  EXECUTION_NAME       EXECUTION_START	    EXECUTION_END	 EXECUTION_TYPE       STATUS
------------------------- -------------------- -------------------- -------------------- -------------------- -----------
AUTO_STATS_ADVISOR_TASK   EXEC_42	       23-AUG-2017 23:00:15 23-AUG-2017 23:00:20 STATISTICS	      COMPLETED
AUTO_STATS_ADVISOR_TASK   EXEC_52	       24-AUG-2017 23:00:27 24-AUG-2017 23:00:40 STATISTICS	      COMPLETED

SQL>

We can use this information to view the advice from the automatic runs, as shown below.

Generate a Report

Since the Optimizer Statistics Advisor uses the normal advisor framework, the results of the advisor executions can be viewed in the {CDB|DBA|ALL|USER}_ADVISOR_% views, but there is an easier way to get the results. An Optimizer Statistics Advisor report is generated using the REPORT_ADVISOR_TASK function in the DBMS_STATS package. It accepts the following parameters.

TASK_NAME : Name of the task.
EXECUTION_NAME : Name of the execution. When not specified the latest execution for the task is used. Default NULL.
TYPE : Allowable values are TEXT, HTML or XML. Default TEXT.
SECTION : Allowable values are SUMMARY, FINDINGS, ERRORS or ALL. Combinations are possible using "+" or "-" qualifiers. Default ALL.
LEVEL : Allowable values are BASIC, TYPICAL, ALL or SHOW_HIDDEN. Combinations are possible using "+" or "-" qualifiers. Default TYPICAL.

The following query displays the default report for the latest execution for the AUTO_STATS_ADVISOR_TASK task.

SET LINESIZE 200
SET LONG 1000000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.report_advisor_task('AUTO_STATS_ADVISOR_TASK') AS REPORT 
FROM   dual;

GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name	 : AUTO_STATS_ADVISOR_TASK
 Execution Name  : EXEC_52
 Created	 : 01-26-17 02:43:30
 Last Modified	 : 08-24-17 11:00:40
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_52 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
 has 1 finding(s). The findings are related to the following rules:
 AVOIDSTALESTATS. Please refer to the finding section for detailed information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:	    AvoidStaleStats
 Rule Description:  Avoid objects with stale or no statistics
 Finding:  There are 1 object(s) with stale statistics.
 Schema:
 CTXSYS
 Objects:
 DR$DBO

 Recommendation:  Regather statistics on objects with stale statistics.
 Example:
 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Stale statistics or no statistics will result in bad plans.
----------------------------------------------------
-------------------------------------------------------------------------------

SQL>

Implement Advisor Recommendations

The Optimizer Statistics Advisor recommendations can be implemented directly using the IMPLEMENT_ADVISOR_TASK function in the DBMS_STATS package. It accepts the following parameters.

TASK_NAME : Name of the task.
EXECUTION_NAME : Name of the execution. When not specified the latest execution for the task is used. Default NULL.
LEVEL : Allowable values are ALL or TYPICAL. Default TYPICAL.

The following code implements the recommendations and displays the result.

VARIABLE v_result CLOB

-- Implement recommendations.
DECLARE
  l_task_name  VARCHAR2(32767);
BEGIN
  l_task_name := 'AUTO_STATS_ADVISOR_TASK';
  :v_result := DBMS_STATS.implement_advisor_task(l_task_name);
END;
/


-- Display results.
SET LONG 10000

SELECT XMLTYPE(:v_result) AS results
FROM   dual;

RESULTS
--------------------------------------------------------------------------------
<implementation_results>
  <rule NAME="AVOIDSTALESTATS">
    <implemented>yes</implemented>
  </rule>
</implementation_results>

SQL>

We can see the statistics have just been analyzed.

COLUMN last_analyzed FORMAT A20
COLUMN stale_stats FORMAT A15

SELECT last_analyzed,
       stale_stats
FROM   dba_tab_statistics
WHERE  owner      = 'CTXSYS'
AND    table_name = 'DR$DBO';

LAST_ANALYZED	     STALE_STATS
-------------------- ---------------
25-AUG-2017 16:11:31 NO

SQL>

Alternatively the Optimizer Statistics Advisor recommendations can be turned into a script using the SCRIPT_ADVISOR_TASK function. It accepts the following parameters.

TASK_NAME : Name of the task.
EXECUTION_NAME : Name of the execution. When not specified the latest execution for the task is used. Default NULL.
DIR_NAME : If the directory name is specified, the function returns the script as a CLOB and writes it to a file in the specified directory. If not specified, the script is only present in the return value of the function. Default NULL.
LEVEL : Allowable values are ALL or TYPICAL. Default TYPICAL.

The following query displays the default script for the latest execution for the AUTO_STATS_ADVISOR_TASK task.

SET LINESIZE 200
SET LONG 1000000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.script_advisor_task('AUTO_STATS_ADVISOR_TASK')
FROM dual;
-- Script generated for the recommendations from execution EXEC_52
-- in the statistics advisor task AUTO_STATS_ADVISOR_TASK
-- Script version 12.2

-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.

-- Scripts for rule USECONCURRENT
-- Rule Description: Use Concurrent preference for Statistics Collection

-- Scripts for rule USEDEFAULTPREFERENCE
-- Rule Description: Use Default Preference for Stats Collection
-- Set global preferenes to default values.

-- Scripts for rule USEDEFAULTOBJECTPREFERENCE
-- Rule Description: Use Default Object Preference for statistics collection
-- Setting object-level preferences to default values
-- setting CASCADE to default value for object level preference
-- setting ESTIMATE_PERCENT to default value for object level preference
-- setting METHOD_OPT to default value for object level preference
-- setting GRANULARITY to default value for object level preference
-- setting NO_INVALIDATE to default value for object level preference

-- Scripts for rule USEINCREMENTAL
-- Rule Description: Statistics should be maintained incrementally when it is beneficial
-- Turn on the incremental option for those objects for which using incremental is helpful.

-- Scripts for rule UNLOCKNONVOLATILETABLE
-- Rule Description: Statistics for objects with non-volatile should not be locked
-- Unlock statistics for objects that are not volatile.

-- Scripts for rule LOCKVOLATILETABLE
-- Rule Description: Statistics for objects with volatile data should be locked
-- Lock statistics for volatile objects.

-- Scripts for rule NOTUSEINCREMENTAL
-- Rule Description: Statistics should not be maintained incrementally when it is not beneficial
-- Turn off incremental option for those objects for which using incremental is not helpful.

-- Scripts for rule USEAUTODEGREE
-- Rule Description: Use Auto Degree for statistics collection
-- Turn on auto degree for those objects for which using auto degree is helpful.

-- Scripts for rule AVOIDSTALESTATS
-- Rule Description: Avoid objects with stale or no statistics
-- Gather statistics for those objcts that are missing or have no statistics.
-- Scripts for rule MAINTAINSTATSCONSISTENCY
-- Rule Description: Statistics of dependent objects should be consistent
-- Gather statistics for those objcts that are missing or have no statistics.
declare
obj_filter_list dbms_stats.ObjectTab;
obj_filter dbms_stats.ObjectElem;
obj_cnt number := 0;
begin
obj_filter_list := dbms_stats.ObjectTab();
obj_filter.ownname := 'CTXSYS';
obj_filter.objtype := 'TABLE';
obj_filter.objname := 'DR$DBO';
obj_filter_list.extend();
obj_cnt := obj_cnt + 1;
obj_filter_list(obj_cnt) := obj_filter;
dbms_stats.gather_database_stats(
obj_filter_list=>obj_filter_list);
end;
/

SQL>

Manually Execute the Advisor

There are a number of procedures and functions in the DBMS_STATS package that allow the advisor to be run manually, some of which are demonstrated below.

We create and populate a table, making sure there are no statistics. This will give us something for the advisor to find.

CONN test/test@pdb1

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE INDEX t1_desc ON t1(description);

INSERT INTO t1
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

EXEC DBMS_STATS.delete_table_stats('TEST', 'T1');

We create a new task and execute it using the CREATE_ADVISOR_TASK and EXECUTE_ADVISOR_TASK functions respectively. These optionally accept TASK_NAME and EXECUTION_NAME parameters. If they aren't specified system-generated names are created. We then generate the report associated with this task.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

SET SERVEROUTPUT ON
DECLARE
  l_task_name  VARCHAR2(32767);
  l_exec_name  VARCHAR2(32767);
  l_report     CLOB;
BEGIN
  l_task_name := DBMS_STATS.create_advisor_task;
  l_exec_name := DBMS_STATS.execute_advisor_task(task_name => l_task_name);
  l_report    := DBMS_STATS.report_advisor_task(
                   task_name      => l_task_name,
                   execution_name => l_exec_name);
  DBMS_OUTPUT.put_line(l_report);
END;
/
GENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name	 : TASK_16
 Execution Name  : EXEC_75
 Created	 : 08-26-17 09:42:24
 Last Modified	 : 08-26-17 09:42:35

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_75 of task
TASK_16, the Statistics Advisor has 2
 finding(s). The findings are related to the following rules: USEDEFAULTPARAMS,
 AVOIDSTALESTATS. 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 1 statistics operation(s) using
nondefault parameters.
 Operation:

delete_table_stats(ownname=>'TEST', tabname=>'T1', cascade_parts=>TRUE, cascade_columns=>TRUE, cascade_indexes=>TRUE, stattype=>'ALL');

 Recommendation:  Use default parameters for statistics
operations.
 Example:
 -- Gathering statistics for 'SH' schema using all default parameter values:
 BEGIN
dbms_stats.gather_schema_stats('SH'); END;
 -- Also the non default parameters can be overriden by setting
 'PREFERENCE_OVERRIDES_PARAMETER' preference.

 -- Overriding non default parameters and preferences for all tables in the
 system and to use dbms_stats for gathering statistics:
 begin
dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE');
 end;
 -- Overriding non default parameters and
preferences for 'SH.SALES':
 begin dbms_stats.set_table_prefs('SH','SALES',
 'PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end;

 Rationale:  Using default parameter values for statistics gathering operations
	     is more efficient.

----------------------------------------------------
 Rule Name:	    AvoidStaleStats
 Rule Description:  Avoid objects with stale or no statistics

 Finding:  There are 1 object(s) with no statistics.
 Schema:
 TEST
 Objects:
 T1

 Recommendation:  Gather Statistics on those objects with no statistics.

 Example:
 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec
dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Stale statistics or no statistics will result in bad plans.

----------------------------------------------------
-------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>

We can see the advisor has noticed the table with no statistics. We could now implement the recommendations as we have done previously.

Configure Advisor Tasks

There are a number of procedures and functions in the DBMS_STATS package that alter the way the advisor works by filtering out objects and advice. There are examples of their usage in the documentation, but I can't imagine myself using them very much. I would rather see the output for all the potentially problematic objects.

Considerations

The Optimizer Statistics Advisor findings and actions work fine for many databases, but like all automated features you must still engage your brain. We have one old 3rd party application that reacts very badly to statistics being gathered "properly". Instead we have to take a very "old fashioned" approach, since this is the only way the queries will give a consistent performance. For systems like this we would definitely ignore statistics advisor suggestions, or risk terrible performance.

Thursday, 18 March 2021

Optimizer Statistics Advisor in Oracle Database 12c Release 2 (12.2)

Analyzing Statistics Using Optimizer Statistics Advisor

Optimizer Statistics Advisor analyzes how optimizer statistics are gathered, and then makes recommendations.

18.1 About Optimizer Statistics Advisor

18.1.1 Purpose of Optimizer Statistics Advisor

18.1.1.1 Problems with a Traditional Script-Based Approach

18.1.1.2 Advantages of Optimizer Statistics Advisor

18.1.2 Optimizer Statistics Advisor Concepts

Optimizer Statistics Advisor uses the same advisor framework as Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors.

18.1.2.1 Components of Optimizer Statistics Advisor

18.1.2.1.1 Rules for Optimizer Statistics Advisor

18.1.2.1.2 Findings for Optimizer Statistics Advisor

18.1.2.1.3 Recommendations for Optimizer Statistics Advisor

18.1.2.1.4 Actions for Optimizer Statistics Advisor

18.1.2.2 Operational Modes for Optimizer Statistics Advisor

18.1.3 Command-Line Interface to Optimizer Statistics Advisor

18.2 Basic Tasks for Optimizer Statistics Advisor

18.2.1 Creating an Optimizer Statistics Advisor Task

18.2.2 Listing Optimizer Statistics Advisor Tasks

18.2.3 Creating Filters for an Optimizer Advisor Task

Filters enable you to include or exclude objects, rules, and operations from Optimizer Statistics Advisor tasks.

18.2.3.1 About Filters for Optimizer Statistics Advisor

18.2.3.2 Creating an Object Filter for an Optimizer Advisor Task

18.2.3.3 Creating a Rule Filter for an Optimizer Advisor Task

18.2.3.4 Creating an Operation Filter for an Optimizer Advisor Task

18.2.4 Executing an Optimizer Statistics Advisor Task

18.2.5 Generating a Report for an Optimizer Statistics Advisor Task

18.2.6 Implementing Optimizer Statistics Advisor Recommendations

You can either implement all recommendations automatically using DBMS_STATS.IMPLEMENT_ADVISOR_TASK, or generate an editable script using DBMS_STATS.SCRIPT_ADVISOR_TASK.

18.2.6.1 Implementing Actions Recommended by Optimizer Statistics Advisor

18.2.6.2 Generating a Script Using Optimizer Statistics Advisor

AUTO_STATS_ADVISOR_TASK

Generate a Report

Implement Advisor Recommendations

Manually Execute the Advisor

Configure Advisor Tasks

Considerations

No comments:

Post a Comment

You can either implement all recommendations automatically using `DBMS_STATS.IMPLEMENT_ADVISOR_TASK`, or generate an editable script using `DBMS_STATS.SCRIPT_ADVISOR_TASK`.