Table of contents
Preamble
Have you ever dreamed of computing statistics on an object to test if a query is performing better without impacting the whole database and to test those statistics and revert back in case it’s worst ? It’s now possible in 11g with deferred statistics publishing !
Test table
We will use the following usual test table:
DROP TABLE test1; CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users; DECLARE i NUMBER; nbrows NUMBER; BEGIN i:=1; nbrows:=50000; LOOP EXIT WHEN i>nbrows; IF (i=1) THEN INSERT INTO test1 VALUES(1,RPAD('A',49,'A')); ELSE INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A')); END IF; i:=i+1; END LOOP; COMMIT; END; / CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users; EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); |
As we have already seen the first statistics gathering in AUTO mode does not create any histograms and so you should see:
SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM DBA_TAB_COL_STATISTICS WHERE owner='YJAQUIER' AND table_name='TEST1'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------------------------ ------------------------------ ------------ ----------- --------------- TEST1 ID 2 1 NONE SQL> SELECT TO_CHAR(last_analyzed,'dd-mon-yyyy hh24:mi:ss') AS last_analyzed FROM user_tables WHERE table_name='TEST1'; LAST_ANALYZED ----------------------------- 04-feb-2011 12:42:47 |
Deferred statistics testing
So, on our test table, a full table scan as Oracle doesn’t know how distinct value for id column:
SQL> SET autotrace traceonly EXPLAIN STATISTICS SQL> SELECT /* Yannick */ * FROM test1 WHERE id=1; Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 4122059633 --------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25000 | 732K| 136 (0)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST1 | 25000 | 732K| 136 (0)| 00:00:02 | --------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - filter("ID"=1) STATISTICS ---------------------------------------------------------- 1 recursive calls 0 db block gets 443 consistent gets 0 physical reads 0 redo SIZE 525 bytes sent via SQL*Net TO client 419 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 1 ROWS processed |
Then by default statistics are published when gathered:
SQL> SELECT dbms_stats.get_prefs('publish','yjaquier','test1') FROM dual; DBMS_STATS.GET_PREFS('PUBLISH','YJAQUIER','TEST1') -------------------------------------------------------------------------------- TRUE SQL> SELECT dbms_stats.get_prefs('publish') FROM dual; DBMS_STATS.GET_PREFS('PUBLISH') -------------------------------------------------------------------------------- TRUE |
We change this with, please note that there is a bug because if you don’t change the publishing at global level then histograms will be updated and so histograms statistics will be published !!!:
SQL> EXEC dbms_stats.set_global_prefs('publish','false'); PL/SQL PROCEDURE successfully completed. SQL> EXEC dbms_stats.set_table_prefs('yjaquier','test1','publish','false'); PL/SQL PROCEDURE successfully completed. SQL> SELECT dbms_stats.get_prefs('publish','yjaquier','test1') FROM dual; DBMS_STATS.GET_PREFS('PUBLISH','YJAQUIER','TEST1') ---------------------------------------------------------------------------------------------------------------------------------- FALSE SQL> SELECT dbms_stats.get_prefs('publish') FROM dual; DBMS_STATS.GET_PREFS('PUBLISH') ---------------------------------------------------------------------------------------------------------------------------------- FALSE |
Now let’s update the statistics, create an histogram, and so using the index. But without publishing anything:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); PL/SQL PROCEDURE successfully completed. SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM DBA_TAB_COL_STATISTICS WHERE owner='YJAQUIER' AND table_name='TEST1' 2 3 4 5 6 7 8 9 ; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------------------------ ------------------------------ ------------ ----------- --------------- TEST1 ID 2 1 NONE SQL> SELECT TO_CHAR(last_analyzed,'dd-mon-yyyy hh24:mi:ss') AS last_analyzed FROM user_tables WHERE table_name='TEST1'; LAST_ANALYZED ----------------------------- 04-feb-2011 12:42:47 SQL> SELECT * FROM user_tab_pending_stats WHERE table_name='TEST1'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------- SAMPLE_SIZE LAST_ANAL ----------- --------- TEST1 50000 496 53 50000 04-FEB-11 |
Then test is simple as:
SQL> ALTER SESSION SET optimizer_use_pending_statistics=TRUE; SESSION altered. SQL> SELECT /* Yannick */ * FROM test1 WHERE id=1; Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 3254990620 -------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 30 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - ACCESS("ID"=1) STATISTICS ---------------------------------------------------------- 387 recursive calls 0 db block gets 84 consistent gets 1 physical reads 0 redo SIZE 529 bytes sent via SQL*Net TO client 419 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 3 sorts (memory) 0 sorts (disk) 1 ROWS processed SQL> ALTER SESSION SET optimizer_use_pending_statistics=FALSE; SESSION altered. SQL> SELECT /* Yannick */ * FROM test1 WHERE id=1; Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 4122059633 --------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25000 | 732K| 136 (0)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST1 | 25000 | 732K| 136 (0)| 00:00:02 | --------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - filter("ID"=1) STATISTICS ---------------------------------------------------------- 0 recursive calls 0 db block gets 443 consistent gets 0 physical reads 0 redo SIZE 525 bytes sent via SQL*Net TO client 419 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 1 ROWS processed |
To conclude you have now the flexibility to publish with:
SQL> EXEC dbms_stats.publish_pending_stats('yjaquier','test1'); PL/SQL PROCEDURE successfully completed. |
Or delete the pending statistics with:
SQL> EXEC dbms_stats.delete_pending_stats('yjaquier','test1'); PL/SQL PROCEDURE successfully completed. |
