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

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

The following table 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 15-2 lists relevant views.

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.example.icom' 
     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;
    /