Oracle Technologies Blog

By ASKM

Manageability – Pending Statistics

Posted by Srikrishna Murthy Annam on August 29, 2009

Pending Statistics :

Oracle Database 11g introduced a new concept of pending statistics. In prior releases , the statistics are available to optimizer immediately after gathering the stats. In 11g we have the option to keep the stats pending until we choose to publish them to the optimizer. We now have an opportunity to test the newly gathered statistics before they are published.

The default value in 11g is to publish the stats.

Important Views and Packages

==========================

dba_tables
dba_indexes
dba_tab_columns
user_tab_pending_stats
user_ind_pending_stats
user_col_pending_stats

dbms_stats.get_prefs
dbms_stats.set_table_prefs
dbms_stats.gather_table_stats
dbms_stats.publish_pending_stats
dbms_stats.delete_pending_stats

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> sho user
USER is “SCOTT”
SQL> desc STATS_TEST
Name                                      Null?    Type
—————————————– ——– —————————-
CUST_ID                                            NUMBER
CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
CUST_LAST_NAME                            NOT NULL VARCHAR2(40)
CUST_GENDER                                        CHAR(1)
CUST_YEAR_OF_BIRTH                                 NUMBER(4)
CUST_MARITAL_STATUS                                VARCHAR2(20)
CUST_STREET_ADDRESS                       NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE                          NOT NULL VARCHAR2(10)
CUST_CITY                                 NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE                                VARCHAR2(40)
COUNTRY_ID                                NOT NULL CHAR(2)
CUST_MAIN_PHONE_NUMBER                             VARCHAR2(25)
CUST_INCOME_LEVEL                                  VARCHAR2(30)
CUST_CREDIT_LIMIT                                  NUMBER
CUST_EMAIL                                         VARCHAR2(30)

SQL> select count(1) from STATS_TEST;

COUNT(1)
———-
630

2) Check the available public statistics for table “STATS_TEST”
SQL> select index_name,table_name from user_indexes where TABLE_NAME=’STATS_TEST’;

INDEX_NAME                     TABLE_NAME
—————————— ——————————
CUST_CRED_LMT_IND              STATS_TEST

SQL> alter session set nls_date_format=’mm/dd hh24:mi:ss’;

Session altered.

SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = ‘STATS_TEST’;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = ‘STATS_TEST’ order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = ‘STATS_TEST’ order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

SQL>

3) Check the pending statistics for table  “STATS_TEST”
SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null;

no rows selected

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by index_name;

no rows selected

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by column_name;

no rows selected

SQL>

4) Modify the table’s publish value to false and then gather stats
SQL> select dbms_stats.get_prefs(‘PUBLISH’) publish from dual;

PUBLISH
——————————————————————————–
TRUE

SQL> select dbms_stats.get_prefs(‘PUBLISH’, ‘SCOTT’, ‘STATS_TEST’) publish from dual;

PUBLISH
——————————————————————————–
TRUE

SQL> exec dbms_stats.set_table_prefs(‘SCOTT’, ‘STATS_TEST’, ‘PUBLISH’, ‘false’);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs(‘PUBLISH’, ‘SCOTT’, ‘STATS_TEST’) publish from dual;

PUBLISH
——————————————————————————–
FALSE

SQL> execute dbms_stats.gather_table_stats(‘SCOTT’, ‘STATS_TEST’);

PL/SQL procedure successfully completed.

5) Check the available public statistics for table “STATS_TEST” and you will find that the stats are not published
SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = ‘STATS_TEST’;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = ‘STATS_TEST’ order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = ‘STATS_TEST’ order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

6) Check the pending statistics for table  “STATS_TEST” and you will find that the stats are gathered and are pending
SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST                     08/29 03:25:34        630  137.646032

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND    08/29 03:25:35        630           2             8

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID                     08/29 03:25:33           19          0 .052631579
CUST_CITY                      08/29 03:25:33          300          0 .003333333
CUST_CREDIT_LIMIT              08/29 03:25:33            8          0       .125
CUST_EMAIL                     08/29 03:25:33          400          0      .0025
CUST_FIRST_NAME                08/29 03:25:33          450          0 .002222222
CUST_GENDER                    08/29 03:25:33            2          0         .5
CUST_ID                        08/29 03:25:33          630          0 .001587302
CUST_INCOME_LEVEL              08/29 03:25:33           12          0 .083333333
CUST_LAST_NAME                 08/29 03:25:33          400          0      .0025
CUST_MAIN_PHONE_NUMBER         08/29 03:25:33          630          0 .001587302
CUST_MARITAL_STATUS            08/29 03:25:33            2        234         .5
CUST_POSTAL_CODE               08/29 03:25:33          301          0 .003322259
CUST_STATE_PROVINCE            08/29 03:25:33          120          0 .008333333
CUST_STREET_ADDRESS            08/29 03:25:33          630          0 .001587302
CUST_YEAR_OF_BIRTH             08/29 03:25:33           66          0 .015151515

15 rows selected.

SQL>

Testing the pending stats

7) Turn off using the pending stats by setting init.ora parameter
SQL> alter session set optimizer_use_pending_statistics = false;

Session altered.

SQL> alter session set optimizer_dynamic_sampling = 0;

Session altered.

SQL> select count(1) from STATS_TEST;

COUNT(1)
———-
630

SQL> select count(1) from STATS_TEST where CUST_CREDIT_LIMIT=1500;

COUNT(1)
———-
129

SQL> explain plan for select * from STATS_TEST where CUST_CREDIT_LIMIT=1500;

Explained.

— Trying to query the 20% of table data , and it should have full table scan.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2806615597

——————————————————————————–
—————–

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (
%CPU)| Time     |

——————————————————————————–
—————–

|   0 | SELECT STATEMENT            |                   |    13 |  2704 |     5
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STATS_TEST        |    13 |  2704 |     5
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | CUST_CRED_LMT_IND |     5 |       |     1
(0)| 00:00:01 |

——————————————————————————–
—————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“CUST_CREDIT_LIMIT”=1500)

14 rows selected.
— As the number of rows matching query increases , the query still gets slower and slower.

8 ) To see if the optimizer does better when it uses the statistics in the pending statistic tables.
SQL> alter session set optimizer_use_pending_statistics = true;

Session altered.

SQL> set linesize 120
SQL> explain plan for select * from STATS_TEST where CUST_CREDIT_LIMIT=1500;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
————————————————————————————————————————
Plan hash value: 3505017564

——————————————————————————–
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————–
|   0 | SELECT STATEMENT  |            |    79 | 10823 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| STATS_TEST |    79 | 10823 |     6   (0)| 00:00:01 |
——————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“CUST_CREDIT_LIMIT”=1500)

13 rows selected.

SQL>

9) If the statistics prove to be acceptable, you can make them public
SQL> exec dbms_stats.publish_pending_stats(‘SCOTT’,’STATS_TEST’)

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = ‘STATS_TEST’;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST                     08/29 03:25:34        630         137

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = ‘STATS_TEST’ order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND    08/29 03:25:35        630           2             8

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = ‘STATS_TEST’ order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID                     08/29 03:25:33           19          0 .052631579
CUST_CITY                      08/29 03:25:33          300          0 .003333333
CUST_CREDIT_LIMIT              08/29 03:25:33            8          0       .125
CUST_EMAIL                     08/29 03:25:33          400          0      .0025
CUST_FIRST_NAME                08/29 03:25:33          450          0 .002222222
CUST_GENDER                    08/29 03:25:33            2          0         .5
CUST_ID                        08/29 03:25:33          630          0 .001587302
CUST_INCOME_LEVEL              08/29 03:25:33           12          0 .083333333
CUST_LAST_NAME                 08/29 03:25:33          400          0      .0025
CUST_MAIN_PHONE_NUMBER         08/29 03:25:33          630          0 .001587302
CUST_MARITAL_STATUS            08/29 03:25:33            2        234         .5
CUST_POSTAL_CODE               08/29 03:25:33          301          0 .003322259
CUST_STATE_PROVINCE            08/29 03:25:33          120          0 .008333333
CUST_STREET_ADDRESS            08/29 03:25:33          630          0 .001587302
CUST_YEAR_OF_BIRTH             08/29 03:25:33           66          0 .015151515

15 rows selected.

SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null;

no rows selected

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by index_name;

no rows selected

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by column_name;

no rows selected

SQL>
Pending Statistics Demo

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: