Deferred statistics publishing

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.

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>