Deferred statistics publishing
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.



