14 Managing Extended Statistics

You can use DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to determine which column groups are required for a table based on a specified workload. This technique is useful when you do not know which extended statistics to create. This technique does not work for expression statistics.

Assumptions

This tutorial assumes the following:

• Cardinality estimates have been incorrect for queries of the sh.customers_test table (created from the customers table) that use predicates referencing the columns country_id and cust_state_province.

• You want the database to monitor your workload for 5 minutes (300 seconds).

• You want the database to determine which column groups are needed automatically.

To detect column groups:

1. Connect SQL*Plus to the database as user sh, and then create the customers_test table and gather statistics for it:

   CONNECT SH/SH
   DROP TABLE customers_test;
   CREATE TABLE customers_test AS SELECT * FROM customer;
   EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');
   

2. Enable workload monitoring.

   In a different SQL*Plus session, connect as SYS and run the following PL/SQL program to enable monitoring for 300 seconds:

   BEGIN
     DBMS_STATS.SEED_COL_USAGE(null,null,300);
   END;
   /
   

3. As user sh, run explain plans for two queries in the workload.

   The following examples show the explain plans for two queries on the customers_test table:

   EXPLAIN PLAN FOR
     SELECT *
     FROM   customers_test
     WHERE  cust_city = 'Los Angeles'
     AND    cust_state_province = 'CA'
     AND    country_id = 52790;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
    
   EXPLAIN PLAN FOR
     SELECT   country_id, cust_state_province, count(cust_city)
     FROM     customers_test
     GROUP BY country_id, cust_state_province;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
   

   Sample output appears below:

   PLAN_TABLE_OUTPUT
   ---------------------------------------------------------------------------
   Plan hash value: 4115398853
    
   ----------------------------------------------------
   | Id  | Operation         | Name           | Rows  |
   ----------------------------------------------------
   |   0 | SELECT STATEMENT  |                |     1 |
   |   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
   ----------------------------------------------------
    
   8 rows selected.
    
   PLAN_TABLE_OUTPUT
   ---------------------------------------------------------------------------
   Plan hash value: 3050654408
    
   -----------------------------------------------------
   | Id  | Operation          | Name           | Rows  |
   -----------------------------------------------------
   |   0 | SELECT STATEMENT   |                |  1949 |
   |   1 |  HASH GROUP BY     |                |  1949 |
   |   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
   -----------------------------------------------------
    
   9 rows selected.
   

   The first plan shows a cardinality of 1 row for a query that returns 932 rows. The second plan shows a cardinality of 1949 rows for a query that returns 145 rows.

4. Optionally, review the column usage information recorded for the table.

   Call the DBMS_STATS.REPORT_COL_USAGE function to generate a report:

   SET LONG 100000
   SET LINES 120
   SET PAGES 0
   SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
   FROM   DUAL;
   

   The report appears below:

   LEGEND:
   .......
    
   EQ         : Used in single table EQuality predicate
   RANGE      : Used in single table RANGE predicate
   LIKE       : Used in single table LIKE predicate
   NULL       : Used in single table is (not) NULL predicate
   EQ_JOIN    : Used in EQuality JOIN predicate
   NONEQ_JOIN : Used in NON EQuality JOIN predicate
   FILTER     : Used in single table FILTER predicate
   JOIN       : Used in JOIN predicate
   GROUP_BY   : Used in GROUP BY expression
   ...........................................................................
    
   ###########################################################################
    
   COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
   .........................................
    
   1. COUNTRY_ID                          : EQ
   2. CUST_CITY                           : EQ
   3. CUST_STATE_PROVINCE                 : EQ
   4. (CUST_CITY, CUST_STATE_PROVINCE,
       COUNTRY_ID)                        : FILTER
   5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
   ###########################################################################
   

   In the preceding report, the first three columns were used in equality predicates in the first monitored query:

   ...
   WHERE  cust_city = 'Los Angeles'
   AND    cust_state_province = 'CA'
   AND    country_id = 52790;
   

   All three columns appeared in the same WHERE clause, so the report shows them as a group filter. In the second query, two columns appeared in the GROUP BY clause, so the report labels them as GROUP_BY. The sets of columns in the FILTER and GROUP_BY report are candidates for column groups.

As explained in Table 14-1, you can use the DBMS_STATS.CREATE_EXTENDED_STATS function to create column groups that were detected previously by executing DBMS_STATS.SEED_COL_USAGE.

Assumptions

This tutorial assumes that you have performed the steps in "Detecting Useful Column Groups for a Specific Workload".

To create column groups:

1. Create column groups for the customers_test table based on the usage information captured during the monitoring window.

   For example, run the following query:

   SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
   

   Sample output appears below:

   ###########################################################################
   EXTENSIONS FOR SH.CUSTOMERS_TEST
   ................................
   1. (CUST_CITY, CUST_STATE_PROVINCE,
       COUNTRY_ID)                     :SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
   2. (CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
   ###########################################################################
   

   The database created two column groups for customers_test: one column group for the filter predicate and one group for the GROUP BY operation.

2. Regather table statistics.

   Run GATHER_TABLE_STATS to regather the statistics for customers_test:

   EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');
   

3. As user sh, run explain plans for two queries in the workload.

   Check the USER_TAB_COL_STATISTICS view to determine which additional statistics were created by the database:

   SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   FROM   USER_TAB_COL_STATISTICS
   WHERE  TABLE_NAME = 'CUSTOMERS_TEST'
   ORDER BY 1;
   

   Partial sample output appears below:

   CUST_CITY                               620 HEIGHT BALANCED
   ...
   SYS_STU#S#WF25Z#QAHIHE#MOFFMM_          145 NONE
   SYS_STUMZ$C3AIHLPBROI#SKA58H_N          620 HEIGHT BALANCED
   

   This example shows the two column group names returned from the DBMS_STATS.CREATE_EXTENDED_STATS function. The column group created on CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID has a height-balanced histogram.

4. Explain the plans again.

   The following examples show the explain plans for two queries on the customers_test table:

   EXPLAIN PLAN FOR
     SELECT *
     FROM   customers_test
     WHERE  cust_city = 'Los Angeles'
     AND    cust_state_province = 'CA'
     AND    country_id = 52790;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
    
   EXPLAIN PLAN FOR
     SELECT   country_id, cust_state_province, count(cust_city)
     FROM     customers_test
     GROUP BY country_id, cust_state_province;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
   

   The new plans show more accurate cardinality estimates:

   ----------------------------------------------------
   | Id  | Operation         | Name           | Rows  |
   ----------------------------------------------------
   |   0 | SELECT STATEMENT  |                |  1093 |
   |   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |  1093 |
   ----------------------------------------------------
    
   8 rows selected.
    
   Plan hash value: 3050654408
    
   -----------------------------------------------------
   | Id  | Operation          | Name           | Rows  |
   -----------------------------------------------------
   |   0 | SELECT STATEMENT   |                |   145 |
   |   1 |  HASH GROUP BY     |                |   145 |
   |   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
   -----------------------------------------------------
   9 rows selected.
   

In some cases, you may know the column group that you want to create. The METHOD_OPT argument of the DBMS_STATS.GATHER_TABLE_STATS function can create and gather statistics on a column group automatically. You can create a new column group by specifying the group of columns using FOR COLUMNS.

Assumptions

This tutorial assumes the following:

• You want to create a column group for the cust_state_province and country_id columns in the customers table in sh schema.

• You want to gather statistics (including histograms) on the entire table and the new column group.

To create a column group and gather statistics for this group:

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

2. Create the column group and gather statistics.

   For example, execute the following PL/SQL program:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers',
     METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
                   'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
   END;
   /
   

To obtain the name of a column group, use the DBMS_STATS.SHOW_EXTENDED_STATS_NAME function or a database view. You can also use views to obtain information such as the number of distinct values, and whether the column group has a histogram.

Assumptions

This tutorial assumes the following:

• You created a column group for the cust_state_province and country_id columns in the customers table in sh schema.

• You want to determine the column group name, the number of distinct values, and whether a histogram has been created for a column group.

To monitor a column group:

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

2. To determine the column group name, do one of the following.

   • Execute the SHOW_EXTENDED_STATS_NAME function.

     For example, run the following PL/SQL program:

     SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 'sh','customers',
            '(cust_state_province,country_id)' ) col_group_name 
     FROM   DUAL;
     

     The output is similar to the following:

     COL_GROUP_NAME
     ----------------
     SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
     

   • Query the USER_STAT_EXTENSIONS view.

     For example, run the following query:

     SELECT EXTENSION_NAME, EXTENSION 
     FROM   USER_STAT_EXTENSIONS 
     WHERE  TABLE_NAME='CUSTOMERS';
     

     EXTENSION_NAME                     EXTENSION
     -----------------------------------------------------------------------
     SYS_STU#S#WF25Z#QAHIHE#MOFFMM_     ("CUST_STATE_PROVINCE","COUNTRY_ID")
     

3. Query the number of distinct values and find whether a histogram has been created for a column group.

   For example, run the following query:

   SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
   FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
   WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
   AND    e.TABLE_NAME=t.TABLE_NAME
   AND    t.TABLE_NAME='CUSTOMERS';
   

   COL_GROUP                             NUM_DISTINCT        HISTOGRAM
   -------------------------------------------------------------------
   ("COUNTRY_ID","CUST_STATE_PROVINCE")  145                 FREQUENCY
   

Use the DBMS_STATS.DROP_EXTENDED_STATS function to delete a column group from a table.

Assumptions

This tutorial assumes the following:

• You created a column group for the cust_state_province and country_id columns in the customers table in sh schema.

• You want to drop the column group.

To drop a column group:

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

2. Drop the column group.

   For example, the following PL/SQL program deletes a column group from the customers table:

   BEGIN
     DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers', 
                                     '(cust_state_province, country_id)' );
   END;
   /
   

You can use either of the following program units:

• GATHER_TABLE_STATS procedure

• CREATE_EXTENDED_STATISTICS function followed by the GATHER_TABLE_STATS procedure

Assumptions

This tutorial assumes the following:

• Selectivity estimates are inaccurate for queries of sh.customers that use the UPPER(cust_state_province) function.

• You want to gather statistics on the UPPER(cust_state_province) expression.

To create expression statistics:

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

2. Gather table statistics.

   For example, run the following command, specifying the function in the method_opt argument:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS( 
       'sh'
   ,   'customers'
   ,   method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 
                     'FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY' 
   );
   END;
   

You can use the database view DBA_STAT_EXTENSIONS and the DBMS_STATS.SHOW_EXTENDED_STATS_NAME function to obtain information about expression statistics. You can also use views to obtain information such as the number of distinct values, and whether the column group has a histogram.

Assumptions

This tutorial assumes the following:

• You created extended statistics for the LOWER(cust_state_province) expression.

• You want to determine the column group name, the number of distinct values, and whether a histogram has been created for a column group.

To monitor expression statistics:

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

2. Query the name and definition of the statistics extension.

   For example, run the following query:

   COL EXTENSION_NAME FORMAT a30
   COL EXTENSION FORMAT a35
   
   SELECT EXTENSION_NAME, EXTENSION
   FROM   USER_STAT_EXTENSIONS
   WHERE  TABLE_NAME='CUSTOMERS';
   

   Sample output appears as follows:

   EXTENSION_NAME                 EXTENSION
   ------------------------------ ------------------------------
   SYS_STUBPHJSBRKOIK9O2YV3W8HOUE (LOWER("CUST_STATE_PROVINCE"))
   

3. Query the number of distinct values and find whether a histogram has been created for the expression.

   For example, run the following query:

   SELECT e.EXTENSION expression, t.NUM_DISTINCT, t.HISTOGRAM
   FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
   WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
   AND    e.TABLE_NAME=t.TABLE_NAME
   AND    t.TABLE_NAME='CUSTOMERS';
   

   EXPRESSION                            NUM_DISTINCT        HISTOGRAM
   -------------------------------------------------------------------
   (LOWER("CUST_STATE_PROVINCE"))        145                 FREQUENCY
   

Use the DBMS_STATS.DROP_EXTENDED_STATS function to delete a column group from a table.

Assumptions

This tutorial assumes the following:

• You created extended statistics for the LOWER(cust_state_province) expression.

• You want to drop the expression statistics.

To drop expression statistics:

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

2. Drop the column group.

   For example, the following PL/SQL program deletes a column group from the customers table:

   BEGIN
     DBMS_STATS.DROP_EXTENDED_STATS(
       'sh'
   ,   'customers'
   ,   '(LOWER(cust_state_province))'
   );
   END;
   /