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.
Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.
This entry was posted in Oracle and tagged , . Bookmark the permalink.

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>