12 Configuring Options for Optimizer Statistics Gathering

To set global optimizer statistics preferences using Cloud Control:

1. Go to the Optimizer Statistics Console page, as explained in "Accessing the Optimizer Statistics Console".

2. Click Global Statistics Gathering Options.

   The Global Statistics Gathering Options page appears.

3. Make your desired changes, and click Apply.

To set object-level optimizer statistics preferences using Cloud Control:

1. Go to the Optimizer Statistics Console, as explained in "Accessing the Optimizer Statistics Console".

2. Click Object Level Statistics Gathering Preferences.

   The Object Level Statistics Gathering Preferences page appears.

3. To modify table preferences for a table that has preferences set at the table level, do the following (otherwise, skip to the next step):

   1. Enter values in Schema and Table Name. Leave Table Name blank to see all tables in the schema.

      The page refreshes with the table names.

   2. Select the desired tables and click Edit Preferences.

      The General subpage of the Edit Preferences page appears.

   3. Change preferences as needed and click Apply.

4. To set preferences for a table that does not have preferences set at the table level, do the following (otherwise, skip to the next step):

   1. Click Add Table Preferences.

      The General subpage of the Add Table Preferences page appears.

   2. In Table Name, enter the schema and table name.

   3. Change preferences as needed and click OK.

5. To set preferences for a schema, do the following:

   1. Click Set Schema Tables Preferences.

      The General subpage of the Edit Schema Preferences page appears.

   2. In Schema, enter the schema name.

   3. Change preferences as needed and click OK.

If you do not use Cloud Control to set optimizer statistics preferences, then you can invoke the DBMS_STATS procedures described in Table 12-1.

Prerequisites

This task has the following prerequisites:

• To set the global or database preferences, you must have SYSDBA privileges, or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

• To set schema preferences, you must connect as owner, or have SYSDBA privileges, or have the ANALYZE ANY system privilege.

• To set table preferences, you must connect as owner of the table or have the ANALYZE ANY system privilege.

To set optimizer statistics preferences from the command line:

1. Connect SQL*Plus to the database with the necessary privileges.

2. Optionally, call the DBMS_STATS.GET_PREFS procedure to see preferences set at the object level, or at the global level if a specific table is not set.

   For example, obtain the STALE_PERCENT parameter setting for the sh.sales table as follows:

   SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'SH', 'SALES') 
   FROM   DUAL;
   

3. Execute the appropriate procedure from Table 12-1, specifying the following parameters:

   • ownname - Set schema name (SET_TAB_PREFS and SET_SCHEMA_PREFS only)

   • tabname - Set table name (SET_TAB_PREFS only)

   • pname - Set parameter name

   • pvalue - Set parameter value

   • add_sys - Include system tables (optional, SET_DATABASE_PREFS only)

   The following example specifies that 13% of rows in sh.sales must change before the statistics on that table are considered stale:

   EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'STALE_PERCENT', '13');
   

4. Optionally, query the *_TAB_STAT_PREFS view to confirm the change.

   For example, query DBA_TAB_STAT_PREFS as follows:

   COL OWNER FORMAT a5
   COL TABLE_NAME FORMAT a15
   COL PREFERENCE_NAME FORMAT a20
   COL PREFERENCE_VALUE FORMAT a30
   SELECT * FROM DBA_TAB_STAT_PREFS;
   

   Sample output appears as follows:

   OWNER TABLE_NAME      PREFERENCE_NAME      PREFERENCE_VALUE
   ----- --------------- -------------------- ------------------------------
   OE    CUSTOMERS       NO_INVALIDATE        DBMS_STATS.AUTO_INVALIDATE
   SH    SALES           STALE_PERCENT        13
   

Assumptions

This tutorial assumes the following:

• You want correct selectivity estimates for the following query, which has WHERE clause predicates on two correlated columns:

    SELECT *
    FROM   sh.customers
    WHERE  cust_city='Los Angeles'
    AND    cust_state_province='CA';
  

• The preceding query uses serial processing.

• The sh.customers table contains 932 rows that meet the conditions in the query.

• You have gathered statistics on the sh.customers table.

• You created an index on the cust_city and cust_state_province columns.

• The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the default level of 2.

To set the dynamic statistics level manually:

1. Connect SQL*Plus to the database with the appropriate privileges, and then explain the execution plan as follows:

   EXPLAIN PLAN FOR
     SELECT *
     FROM   sh.customers
     WHERE  cust_city='Los Angeles'
     AND    cust_state_province='CA';
   

2. Query the plan as follows:

   SET LINESIZE 130
   SET PAGESIZE 0
   SELECT * 
   FROM   TABLE(DBMS_XPLAN.DISPLAY);
   

   The output appears below (the example has been reformatted to fit on the page):

   -------------------------------------------------------------------------------
   |Id| Operation                   | Name             |Rows|Bytes|Cost | Time   |
   -------------------------------------------------------------------------------
   | 0| SELECT STATEMENT            |                   | 53| 9593|53(0)|00:00:01|
   | 1|  TABLE ACCESS BY INDEX ROWID|CUSTOMERS          | 53| 9593|53(0)|00:00:01|
   |*2|   INDEX RANGE SCAN          |CUST_CITY_STATE_IND| 53| 9593| 3(0)|00:00:01|
   -------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      2 - access("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
   

   The columns in the WHERE clause have a real-world correlation, but the optimizer is not aware that Los Angeles is in California and assumes both predicates reduce the number of rows returned. Thus, the table contains 932 rows that meet the conditions, but the optimizer estimates 53, as shown in bold.

   If the database had used dynamic statistics for this plan, then the Note section of the plan output would have indicated this fact. The optimizer did not use dynamic statistics because the statement executed serially, standard statistics exist, and the parameter OPTIMIZER_DYNAMIC_SAMPLING is set to the default of 2.

3. Set the dynamic statistics level to 4 in the session using the following statement:

   ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=4;
   

4. Explain the plan again:

   EXPLAIN PLAN FOR
     SELECT *
     FROM   sh.customers
     WHERE  cust_city='Los Angeles'
     AND    cust_state_province='CA';
   

   The new plan shows a more accurate estimate of the number of rows, as shown by the value 932 in bold:

   PLAN_TABLE_OUTPUT
   ---------------------------------------------------------------------------
   Plan hash value: 2008213504
    
   ---------------------------------------------------------------------------
   | Id  | Operation         | Name      |Rows | Bytes |Cost (%CPU)|Time     |
   ---------------------------------------------------------------------------
   |   0 | SELECT STATEMENT  |           | 932 |   271K|   406 (1)| 00:00:05 |
   |*  1 |  TABLE ACCESS FULL| CUSTOMERS | 932 |   271K|   406 (1)| 00:00:05 |
   ---------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      1 - filter("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
    
   Note
   -----
      - dynamic statistics used for this statement (level=4)
   

   The note at the bottom of the plan indicates that the sampling level is 4. The additional dynamic statistics made the optimizer aware of the real-world relationship between the cust_city and cust_state_province columns, thereby enabling it to produce a more accurate estimate for the number of rows: 932 rather than 53.

To disable dynamic statistics at the session level:

1. Connect SQL*Plus to the database with the appropriate privileges.

2. Set the dynamic statistics level to 0.

   For example, run the following statement:

   ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=0;