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
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.