13 Managing Optimizer Statistics: Advanced Topics

When setting the level for dynamic statistics, the best practice is to use ALTER SESSION to set the value for the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter. Determining a systemwide setting that would be beneficial to all SQL statements can be difficult.

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.

In general, the best practice is not to incur the cost of dynamic statistics for queries whose compile times must be as fast as possible, for example, unrepeated OLTP queries. You can disable the feature by setting the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter.

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;
   

You can use the DBMS_STATS package to perform operations relating to publishing statistics. Table 13-2 lists the relevant program units.

The initialization parameter OPTIMIZER_USE_PENDING_STATISTICS determines whether the database uses pending statistics when they are available. The default value is false, which means that the optimizer uses only published statistics. Set to true to specify that the optimizer uses any existing pending statistics instead. The best practice is to set this parameter at the session level rather than at the database level.

You can use access information about published statistics from data dictionary views. Table 13-3 lists relevant views.

This section explains how to use DBMS_STATS program units to change the publishing behavior of optimizer statistics, and also to export and delete these statistics.

Assumptions

This tutorial assumes the following:

• You want to change the preferences for the sh.customers and sh.sales tables so that newly collected statistics have pending status.

• You want the current session to use pending statistics.

• You want to gather and publish pending statistics on the sh.customers table.

• You gather the pending statistics on the sh.sales table, but decide to delete them without publishing them.

• You want to change the preferences for the sh.customers and sh.sales tables so that newly collected statistics are published.

To manage published and pending statistics:

1. Start SQL*Plus and connect to the database as user sh.

2. Query the global optimizer statistics publishing setting.

   Run the following query (sample output included):

   sh@PROD> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;
   
   PUBLISH
   -------
   TRUE
   

   The value true indicates that the database publishes statistics as it gathers them. Every table uses this value unless a specific table preference has been set.

   When using GET_PREFS, you can also specify a schema and table name. The function returns a table preference if it is set. Otherwise, the function returns the global preference.

3. Query the pending statistics.

   For example, run the following query (sample output included):

   sh@PROD> SELECT * FROM USER_TAB_PENDING_STATS;
    
   no rows selected
   

   This example shows that the database currently stores no pending statistics for the sh schema.

4. Change the publishing preferences for the sh.customers table.

   For example, execute the following procedure so that statistics are marked as pending:

   BEGIN
     DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', 'false');
   END;
   /
   

   Subsequently, when you gather statistics on the customers table, the database does not automatically publish statistics when the gather job completes. Instead, the database stores the newly gathered statistics in the USER_TAB_PENDING_STATS table.

5. Gather statistics for sh.customers.

   For example, run the following program:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS('sh','customers');
   END;
   /
   

6. Query the pending statistics.

   For example, run the following query (sample output included):

   sh@PROD> SELECT TABLE_NAME, NUM_ROWS FROM USER_TAB_PENDING_STATS;
    
   TABLE_NAME                       NUM_ROWS
   ------------------------------ ----------
   CUSTOMERS                           55500
   

   This example shows that the database now stores pending statistics for the sh.customers table.

7. Instruct the optimizer to use the pending statistics in this session.

   Set the initialization parameter OPTIMIZER_USE_PENDING_STATISTICS to true as shown:

   ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = true;
   

8. Run a workload.

   The following example changes the email addresses of all customers named Bruce Chalmers:

   UPDATE  sh.customers 
     SET   cust_email='ChalmersB@company.com' 
     WHERE cust_first_name = 'Bruce' 
     AND   cust_last_name = 'Chalmers';
   COMMIT;
   

   The optimizer uses the pending statistics instead of the published statistics when compiling all SQL statements in this session.

9. Publish the pending statistics for sh.customers.

   For example, execute the following program:

   BEGIN
     DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');
   END;
   /
   

10. Change the publishing preferences for the sh.sales table.

    For example, execute the following program:

    BEGIN
      DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'publish', 'false');
    END;
    /
    

    Subsequently, when you gather statistics on the sh.sales table, the database does not automatically publish statistics when the gather job completes. Instead, the database stores the statistics in the USER_TAB_PENDING_STATS table.

11. Gather statistics for sh.sales.

    For example, run the following program:

    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS('sh','sales');
    END;
    /
    

12. Delete the pending statistics for sh.sales.

    Assume you change your mind and now want to delete pending statistics for sh.sales. Run the following program:

    BEGIN
      DBMS_STATS.DELETE_PENDING_STATS('sh','sales');
    END;
    /
    

13. Change the publishing preferences for the sh.customers and sh.sales tables back to their default setting.

    For example, execute the following program:

    BEGIN
      DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', null);
      DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'publish', null);
    END;
    /
    

The DBMS_STATS package provides two procedures for locking statistics: LOCK_SCHEMA_STATS and LOCK_TABLE_STATS.

Assumptions

This tutorial assumes the following:

• You gathered statistics on the oe.orders table and on the hr schema.

• You want to prevent the oe.orders table statistics and hr schema statistics from changing.

To lock statistics:

1. Start SQL*Plus and connect to the database as the oe user.

2. Lock the statistics on oe.orders.

   For example, execute the following PL/SQL program:

   BEGIN
     DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
   END;
   /
   

3. Connect to the database as the hr user.

4. Lock the statistics in the hr schema.

   For example, execute the following PL/SQL program:

   BEGIN
     DBMS_STATS.LOCK_SCHEMA_STATS('HR');
   END;
   /
   

The DBMS_STATS package provides two procedures for unlocking statistics: UNLOCK_SCHEMA_STATS and UNLOCK_TABLE_STATS.

Assumptions

This tutorial assumes the following:

• You locked statistics on the oe.orders table and on the hr schema.

• You want to unlock these statistics.

To unlock statistics:

1. Start SQL*Plus and connect to the database as the oe user.

2. Unlock the statistics on oe.orders.

   For example, execute the following PL/SQL program:

   BEGIN
     DBMS_STATS.UNLOCK_TABLE_STATS('OE','ORDERS');
   END;
   /
   

3. Connect to the database as the hr user.

4. Unlock the statistics in the hr schema.

   For example, execute the following PL/SQL program:

   BEGIN
     DBMS_STATS.UNLOCK_SCHEMA_STATS('HR');
   END;
   /
   

Restoring statistics is similar to importing and exporting statistics. In general, restore statistics instead of exporting them in the following situations:

• You want to recover older versions of the statistics. For example, you want to restore the optimizer behavior to an earlier date.

• You want the database to manage the retention and purging of statistics histories.

Export statistics rather than restoring them in the following situations:

• You want to experiment with multiple sets of statistics and change the values back and forth.

• You want to move the statistics from one database to another database. For example, moving statistics from a production system to a test system.

• You want to preserve a known set of statistics for a longer period than the desired retention date for restoring statistics.

When restoring previous versions of statistics, the following limitations apply:

• DBMS_STATS.RESTORE_*_STATS procedures cannot restore user-defined statistics.

• Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics.

• When you drop a table, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures is lost. Without this data, these features do not function properly. To remove all rows from a table, and to restore these statistics with DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table.

You can restore statistics using the DBMS_STATS.RESTORE_*_STATS procedures. The procedures listed in Table 13-5 accept a timestamp as an argument and restore statistics as of the specified time (as_of_timestamp).

Dictionary views display the time of statistics modifications. You can use the following views to determine the time stamp to be use for the restore operation:

• The DBA_OPTSTAT_OPERATIONS view contain history of statistics operations performed at schema and database level using DBMS_STATS.

• The DBA_TAB_STATS_HISTORY views contains a history of table statistics modifications.

Assumptions

This tutorial assumes the following:

• After the most recent statistics collection for the oe.orders table, the optimizer began choosing suboptimal plans for queries of this table.

• You want to restore the statistics from before the most recent statistics collection to see if the plans improve.

To restore optimizer statistics:

1. Start SQL*Plus and connect to the database with administrator privileges.

2. Query the statistics history for oe.orders.

   For example, run the following query:

   COL TABLE_NAME FORMAT a10
   SELECT TABLE_NAME,
          TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME
   FROM   DBA_TAB_STATS_HISTORY 
   WHERE  TABLE_NAME='ORDERS'
   AND    OWNER='OE'
   ORDER BY STATS_UPDATE_TIME DESC;
   

   Sample output is as follows:

   TABLE_NAME STATS_MOD_TIME
   ---------- -------------------
   ORDERS     2012-08-20:11:36:38
   ORDERS     2012-08-10:11:06:20
   

3. Restore the optimizer statistics to the previous modification time.

   For example, restore the oe.orders table statistics to August 10, 2012:

   BEGIN
     DBMS_STATS.RESTORE_TABLE_STATS( 'OE','ORDERS', 
                  TO_TIMESTAMP('2012-08-10:11:06:20','YYYY-MM-DD:HH24:MI:SS') );
   END;
   /
   

   You can specify any date between 8/10 and 8/20 because DBMS_STATS restores statistics as of the specified time.

You can use DBMS_STATS procedures to obtain historical information for optimizer statistics. This information is useful when you want to determine how long the database retains optimizer statistics, and how far back these statistics can be restored.

You can use the following procedure to obtain information about the optimizer statistics history:

• GET_STATS_HISTORY_RETENTION

  This function can retrieve the current statistics history retention value.

• GET_STATS_HISTORY_AVAILABILITY

  This function retrieves the oldest time stamp when statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.

To obtain optimizer statistics history information:

1. Start SQL*Plus and connect to the database with the necessary privileges.

2. Execute the following PL/SQL program:

   DECLARE
     v_stats_retn  NUMBER;
     v_stats_date  DATE;
   BEGIN
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('The retention setting is ' || v_stats_retn || '.');
     v_stats_date := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
     DBMS_OUTPUT.PUT_LINE('Earliest restore date is ' || v_stats_date || '.');
   END;
   /
   

By default, the database retains optimizer statistics for 31 days. You can configure the retention period using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure.

Prerequisites

To run this procedure, you must have either the SYSDBA privilege, or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

Assumptions

This tutorial assumes the following:

• The current retention period for optimizer statistics is 31 days.

• You run queries annually as part of an annual report. To keep the statistics history for more than 365 days so that you have access to last year's plan (in case a suboptimal plan occurs now), you set the retention period to 366 days.

• You want to create a PL/SQL procedure set_opt_stats_retention that you can use to change the optimizer statistics retention period.

To change the optimizer statistics retention period:

1. Start SQL*Plus and connect to the database with the necessary privileges.

2. Create a procedure that changes the retention period.

   For example, create the following procedure:

   CREATE OR REPLACE PROCEDURE set_opt_stats_retention
     ( p_stats_retn   IN NUMBER )
   IS
     v_stats_retn NUMBER;
   BEGIN
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('Old retention setting is ' ||v_stats_retn|| '.');
     DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(p_stats_retn);
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('New retention setting is ' ||v_stats_retn|| '.');
   END;
   /
   

3. Change the retention period to 366 days.

   For example, execute the procedure that you created in the previous step (sample output included):

   SQL> EXECUTE set_opt_stats_retention(366)
   
   The old retention setting is 31.
   The new retention setting is 366.
    
   PL/SQL procedure successfully completed.
   

Automatic purging is enabled when the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. The database purges all history older than the older of (current time - the ALTER_STATS_HISTORY_RETENTION setting) and (time of the most recent statistics gathering - 1).

You can purge old statistics manually using the PURGE_STATS procedure. If you do not specify an argument, then this procedure uses the automatic purging policy. If you specify the before_timestamp parameter, then the database purges statistics saved before the specified timestamp.

Prerequisites

To run this procedure, you must have either the SYSDBA privilege, or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.

Assumptions

This tutorial assumes that you want to purge statistics more than one week old.

To purge optimizer statistics:

1. Start SQL*Plus and connect to the database with the necessary privileges.

2. Execute the DBMS_STATS.PURGE_STATS procedure.

   For example, execute the procedure as follows:

   EXEC DBMS_STATS.PURGE_STATS( SYSDATE-7 );
   

This section explains how to transport schema statistics from a production database to a test database.

Prerequisites and Restrictions

When preparing to export optimizer statistics, note the following:

• Before exporting statistics, you must create a table to hold the statistics. The procedure DBMS_STATS.CREATE_STAT_TABLE creates the statistics table.

• The optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. To make the optimizer use statistics in user-defined tables, import these statistics into the data dictionary using the DBMS_STATS import procedure.

• The Data Pump Export and Import utilities export and import optimizer statistics from the database along with the table. When a column has system-generated names, Original Export (exp) does not export statistics with the data, but this restriction does not apply to Data Pump Export.

  Note:

  Exporting and importing statistics using DBMS_STATS is a distinct operation from using Data Pump Export and Import.

Assumptions

This tutorial assumes the following:

• You want to generate representative sh schema statistics on a production database and use DBMS_STATS to import them into a test database.

• Administrative user dba1 exists on both production and test databases.

• You intend to create table opt_stats to store the schema statistics.

• You intend to use Oracle Data Pump to export and import table opt_stats.

To generate schema statistics and import them into a separate database:

1. On the production host, start SQL*Plus and connect to the production database as administrator dba1.

2. Create a table to hold the production statistics.

   For example, execute the following PL/SQL program to create user statistics table opt_stats:

   BEGIN
     DBMS_STATS.CREATE_STAT_TABLE ( 
       ownname => 'dba1'
   ,   stattab => 'opt_stats'
   );
   END;
   /
   

3. Gather schema statistics.

   For example, manually gather schema statistics as follows:

   -- generate representative workload
   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');
   

4. Use DBMS_STATS to export the statistics.

   For example, retrieve schema statistics and store them in the opt_stats table created previously:

   BEGIN
     DBMS_STATS.EXPORT_SCHEMA_STATS (
       ownname => 'dba1'
   ,   stattab => 'opt_stats'
   );
   END;
   /
   

5. Use Oracle Data Pump to export the contents of the statistics table.

   For example, run the expdp command at the operating schema prompt:

   expdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats
   

6. Transfer the dump file to the test database host.

7. Log in to the test host, and then use Oracle Data Pump to import the contents of the statistics table.

   For example, run the impdp command at the operating schema prompt:

   impdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats 
   

8. On the test host, start SQL*Plus and connect to the test database as administrator dba1.

9. Use DBMS_STATS to import statistics from the user statistics table and store them in the data dictionary.

   The following PL/SQL program imports schema statistics from table opt_stats into the data dictionary:

   BEGIN
     DBMS_STATS.IMPORT_SCHEMA_STATS( 
       ownname => 'dba1'
   ,   stattab => 'opt_stats' 
   );
   END;
   /