10 Optimizer Statistics Concepts

Example 10-2 Index Statistics

This example queries some index statistics for the cust_lname_ix and customers_pk indexes on the sh.customers table (sample output included):

SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY",
       AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY",
       AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY"
FROM   DBA_IND_STATISTICS
WHERE  OWNER = 'SH'
AND    INDEX_NAME IN ('CUST_LNAME_IX','CUSTOMERS_PK');

INDEX_NAME     BLEVEL LEAFBLK DIST_KEY LEAFBLK_PER_KEY DATABLK_PER_KEY
-------------- ------ ------- -------- --------------- ---------------
CUSTOMERS_PK        1     115    55500               1               1
CUST_LNAME_IX       1     141      908               1              10

Example 10-3 Index Clustering Factor

This example shows how the optimizer uses the index clustering factor to determine whether using an index is more effective than a full table scan.

1. Start SQL*Plus and connect to a database as sh, and then query the number of rows and blocks in the sh.customers table (sample output included):

   SELECT  table_name, num_rows, blocks
   FROM    user_tables
   WHERE   table_name='CUSTOMERS';
    
   TABLE_NAME                       NUM_ROWS     BLOCKS
   ------------------------------ ---------- ----------
   CUSTOMERS                           55500       1486
   

2. Create an index on the customers.cust_last_name column.

   For example, execute the following statement:

   CREATE INDEX CUSTOMERS_LAST_NAME_IDX ON customers(cust_last_name);
    
   

3. Query the index clustering factor of the newly created index.

   The following query shows that the customers_last_name_idx index has a high clustering factor because the clustering factor is significantly more than the number of blocks in the table:

   SELECT index_name, blevel, leaf_blocks, clustering_factor
   FROM   user_indexes
   WHERE  table_name='CUSTOMERS'
   AND    index_name= 'CUSTOMERS_LAST_NAME_IDX';
    
   INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
   ------------------------------ ---------- ----------- -----------------
   CUSTOMERS_LAST_NAME_IDX                 1         141              9859
   

4. Create a new copy of the customers table, with rows ordered by cust_last_name.

   For example, execute the following statements:

   DROP TABLE customers3 PURGE;
   CREATE TABLE customers3 AS 
     SELECT * 
     FROM   customers 
     ORDER BY cust_last_name;
   

5. Gather statistics on the customers3 table.

   For example, execute the GATHER_TABLE_STATS procedure as follows:

   EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'CUSTOMERS3');
   

6. Query the number of rows and blocks in the customers3 table .

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

   SELECT    TABLE_NAME, NUM_ROWS, BLOCKS
   FROM      USER_TABLES
   WHERE     TABLE_NAME='CUSTOMERS3';
    
   TABLE_NAME                       NUM_ROWS     BLOCKS
   ------------------------------ ---------- ----------
   CUSTOMERS3                          55500       1485 
   

7. Create an index on the cust_last_name column of customers3.

   For example, execute the following statement:

   CREATE INDEX CUSTOMERS3_LAST_NAME_IDX ON customers3(cust_last_name);
    
   

8. Query the index clustering factor of the customers3_last_name_idx index.

   The following query shows that the customers3_last_name_idx index has a lower clustering factor:

   SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR
   FROM   USER_INDEXES
   WHERE  TABLE_NAME = 'CUSTOMERS3'
   AND    INDEX_NAME = 'CUSTOMERS3_LAST_NAME_IDX';
    
   INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
   ------------------------------ ---------- ----------- -----------------
   CUSTOMERS3_LAST_NAME_IDX                1         141              1455
   

   The table customers3 has the same data as the original customers table, but the index on customers3 has a much lower clustering factor because the data in the table is ordered by the cust_last_name. The clustering factor is now about 10 times the number of blocks instead of 70 times.

9. Query the customers table.

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

   SELECT cust_first_name, cust_last_name
   FROM   customers
   WHERE  cust_last_name BETWEEN 'Puleo' AND 'Quinn';
    
   CUST_FIRST_NAME      CUST_LAST_NAME
   -------------------- ----------------------------------------
   Vida                 Puleo
   Harriett             Quinlan
   Madeleine            Quinn
   Caresse              Puleo 
   

10. Display the cursor for the query.

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

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
    
    -------------------------------------------------------------------------------
    | Id | Operation                 | Name      | Rows |Bytes|Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------
    |   0| SELECT STATEMENT          |           |      |     | 405 (100)|        |
    |*  1|  TABLE ACCESS STORAGE FULL| CUSTOMERS |  2335|35025| 405   (1)|00:00:01|
    -------------------------------------------------------------------------------
    

    The preceding plan shows that the optimizer did not use the index on the original customers tables.

11. Query the customers3 table.

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

    SELECT cust_first_name, cust_last_name
    FROM   customers3
    WHERE  cust_last_name BETWEEN 'Puleo' AND 'Quinn';
     
    CUST_FIRST_NAME      CUST_LAST_NAME
    -------------------- ----------------------------------------
    Vida                 Puleo
    Harriett             Quinlan
    Madeleine            Quinn
    Caresse              Puleo 
    

12. Display the cursor for the query.

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

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
    
    ---------------------------------------------------------------------------------------
    |Id| Operation                   | Name                   |Rows|Bytes|Cost(%CPU)| Time|
    ---------------------------------------------------------------------------------------
    | 0| SELECT STATEMENT            |                        |    |     |69(100)|        |
    | 1|  TABLE ACCESS BY INDEX ROWID|CUSTOMERS3              |2335|35025|69(0)  |00:00:01|
    |*2|   INDEX RANGE SCAN          |CUSTOMERS3_LAST_NAME_IDX|2335|     |7(0)   |00:00:01|
    ---------------------------------------------------------------------------------------
    

    The result set is the same, but the optimizer chooses the index. The plan cost is much less than the cost of the plan used on the original customers table.

13. Query customers with a hint that forces the optimizer to use the index.

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

    SELECT /*+ index (Customers CUSTOMERS_LAST_NAME_IDX) */ cust_first_name, 
           cust_last_name 
    FROM   customers 
    WHERE  cust_last_name BETWEEN 'Puleo' and 'Quinn';
     
    CUST_FIRST_NAME      CUST_LAST_NAME
    -------------------- ----------------------------------------
    Vida                 Puleo
    Caresse              Puleo
    Harriett             Quinlan
    Madeleine            Quinn 
    

14. Display the cursor for the query.

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

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                | Name                  |Rows|Bytes|Cost(%CPU)| Time   |
    -----------------------------------------------------------------------------------------
    | 0| SELECT STATEMENT            |                       |    |     | 422(100) |        |
    | 1|  TABLE ACCESS BY INDEX ROWID|CUSTOMERS              |335 |35025| 422(0)   |00:00:01|
    |*2|   INDEX RANGE SCAN          |CUSTOMERS_LAST_NAME_IDX|2335|     | 7(0)     |00:00:01|
    -----------------------------------------------------------------------------------------
    

    The preceding plan shows that the cost of using the index on customers is higher than the cost of a full table scan. Thus, using an index does not necessarily improve performance. The index clustering factor is a measure of whether an index scan is more effective than a full table scan.

Example 10-4 Collocated Data

Assume that the rows are stored in the data blocks as follows:

Block 1       Block 2        Block 3
-------       -------        -------
A  A  A       B  B  B        C  C  C

In this example, the index clustering factor for col1_idx is low. The rows that have the same indexed column values for col1 are in the same data blocks in the table. Thus, the cost of using an index range scan to return all rows with value A is low because only one block in the table must be read.

Example 10-5 Scattered Data

Assume that the same rows are scattered across the data blocks as follows:

Block 1       Block 2        Block 3
-------       -------        -------
A  B  C       A  C  B        B  A  C

In this example, the index clustering factor for col1_idx is higher. The database must read all three blocks in the table to retrieve all rows with the value A in col1.

Example 10-6 Display Directives for sh Schema

EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
 
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME, 
       o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;
 
DIR_ID              OWN OBJECT_NA COL_NAME   OBJECT  TYPE             STATE  REASON
------------------- --- --------- ---------- ------- ---------------- ------ ------------
1484026771529551585  SH CUSTOMERS COUNTRY_ID  COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE 
                                                                             CARDINALITY 
                                                                             MISESTIMATE
1484026771529551585  SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE 
                                  PROVINCE                                   CARDINALITY
                                                                             MISESTIMATE
1484026771529551585  SH CUSTOMERS              TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE 
                                                                             CARDINALITY
                                                                             MISESTIMATE

Example 10-7 Execution Plan

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b74nw722wjvy3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'
 
Plan hash value: 1683234692
 
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |     29 |00:00:00.01 |      16 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |     29 |     29 |00:00:00.01 |      16 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - 1 Sql Plan Directive used for this statement

Example 10-8 Display Directives for sh Schema

EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
 
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME, 
       o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;
 
DIR_ID              OWN OBJECT_NA  COL_NAME    OBJECT TYPE     STATE      REASON
------------------- --- ---------  ----------  ------ -------- ---------  ------------
1484026771529551585  SH CUSTOMERS  COUNTRY_ID  COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE  
                                                      SAMPLING            CARDINALITY 
                                                                          MISESTIMATE
1484026771529551585  SH CUSTOMERS  CUST_STATE_ COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE 
                                   PROVINCE           SAMPLING            CARDINALITY 
                                                                          MISESTIMATE
1484026771529551585  SH CUSTOMERS               TABLE DYNAMIC_ SUPERSEDED SINGLE TABLE
                                                      SAMPLING            CARDINALITY
                                                                          MISESTIMATE

Example 10-9 Execution Plan

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b74nw722wjvy3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'
 
Plan hash value: 1683234692
 
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |     29 |00:00:00.01 |      17 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |     29 |     29 |00:00:00.01 |      17 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
 
19 rows selected.