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.

To create an object filter:

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

2. 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;

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.

To create a rule filter:

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

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

3. 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;

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:

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

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

3. 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.%');

4. 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;

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.

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:

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

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

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

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.

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:

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

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

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