DIFF_TABLE_STATS_IN_HISTORY Example

In 10g, we introduced the automatic retention of old Optimizer Statistics. We might want to compare the Optimizer Statistics, presently in use with the old stats. This posting gives an example that shows how to do that. The Online Documentation for the function has no example for that – probably it is considered too trivial :-)

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> connect scott/tiger
Connected.

SQL> alter session set nls_timestamp_tz_format='yyyy-mm-dd:hh24:mi:ss';

Session altered.

SQL> alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';

Session altered.
SQL> select num_rows,last_analyzed from user_tables where table_name='DEPT';

  NUM_ROWS LAST_ANAL
---------- ---------

There are no Optimizer Statistics yet for the DEPT table of SCOTT. We change that:

SQL> exec dbms_stats.gather_table_stats('SCOTT','DEPT')

PL/SQL procedure successfully completed.
SQL> select num_rows,last_analyzed from user_tables where table_name='DEPT';

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     4 2012-04-23:16:14:38

This is my demo setup. Now we have some DML on the table and gather statistics afterwards:

SQL> insert into dept values (50,'TEST','TEST');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','DEPT')

PL/SQL procedure successfully completed.

SQL> select num_rows,last_analyzed from user_tables where table_name='DEPT';

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     5 2012-04-23:16:17:42

The above stats are actually in use, while the old stats got recorded. They show up in dba_tab_stats_history with the time they got stored there:

SQL> select table_name,stats_update_time from user_tab_stats_history;

TABLE_NAME               STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
DEPT                   2012-04-23:16:14:38
DEPT                   2012-04-23:16:17:42

I got two rows above; the first represents the NULL stats before the first gather, the second row are the stats before the second gather. We can now compare the present stats with the old stats in this way:

SQL> select * from table(dbms_stats.diff_table_stats_in_history(
                    ownname => user,
                    tabname => upper('&tabname'),
                    time1 => systimestamp,
                    time2 => to_timestamp('&time2','yyyy-mm-dd:hh24:mi:ss'),
                    pctthreshold => 0));   
Enter value for tabname: dept
old   3:             tabname => upper('&tabname'),
new   3:             tabname => upper('dept'),
Enter value for time2: 2012-04-23:16:17:42
old   5:             time2 => to_timestamp('&time2','yyyy-mm-dd:hh24:mi:ss'),
new   5:             time2 => to_timestamp('2012-04-23:16:17:42','yyyy-mm-dd:hh24:mi:ss'),

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE          : DEPT
OWNER          : SCOTT
SOURCE A      : Statistics as of 2012-04-23:16:25:16
SOURCE B      : Statistics as of 2012-04-23:16:17:42
PCTTHRESHOLD  : 0
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME            TYP SRC ROWS       BLOCKS      ROWLEN     SAMPSIZE
...............................................................................

DEPT                   T    A   5           8          18         5
                            B   4           4          20         4

I had to shorten and adjust the above report to make it look nice on WordPress – there is also a section about Column Statistics in it that I left out here.
Conclusion: We can not only restore old Optimizer Statistics easy, we can also compare the old stats with the present stats beforehand.

About these ads

,

  1. #1 by Kellyn Pot'Vin (@DBAKevlar) on April 23, 2012 - 17:19

    Very helpful when you want to know what and how much statistics info has changed between one collection and another….
    Thanks, Uwe!

  2. #2 by Uwe Hesse on April 23, 2012 - 19:46

    Thank YOU for stopping by and leaving that nice comment, Kellyn!

  3. #3 by Mahir M. Quluzade on April 24, 2012 - 11:06

    Thanks Uwe!

  4. #4 by Chandra Shekar Ramavath on April 24, 2012 - 13:49

    Nice explanation Owe!! Can you please post a topic on best (Idle or optimal) parameters with DBMS_STATS to collect statistics? My mail id: chandu_0448@yahoo.com

  5. #5 by Uwe Hesse on April 25, 2012 - 11:04

    Mahir, you’re welcome :-)

    Chandra, Maria Colgan has an excellent Whitepaper published about that already:

    http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf

    And thanks for the nice comment (it’s Uwe, not Owe BTW) :-)

  6. #6 by Amit Saraswat on September 5, 2012 - 18:35

    Thanks Uwe!!

    You always pick gems out of Oracle treasury.

  7. #7 by Subhash on January 2, 2014 - 20:50

    Very useful and nice explanation.
    Thanks Uwe! Your way of teaching is amazing. You always make thing looks easy to understand

  8. #8 by Uwe Hesse on January 9, 2014 - 14:55

    Thank you Amit Sraswat and Subhash for taking the time to leave a nice feedback! Much appreciated :-)

  9. #9 by dadbm on February 20, 2014 - 14:50

    Thanks Uwe
    Very useful example.
    I would rather switch time1 and time2 in your example, since time2 by default is the current stats in dictionary.
    You can omit it also:
    SQL> select * from table(dbms_stats.diff_table_stats_in_history(
    ownname => user,
    tabname => upper(‘&tabname’),
    time1 => to_timestamp(‘&time1′,’yyyy-mm-dd:hh24:mi:ss’),
    pctthreshold => 0));

    For those who have issues with sql+ formating, try to use
    set long 100000

    — Kirill Loifman

  10. #10 by ursvenkat on July 13, 2014 - 19:57

    Thank you Uwe for sharing a wonderful work. I just have question is there any query to find the : how many rows inserted after last analyzed(table) and need to check what % had run last without giving any time frame

    Regards
    venkat

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,743 other followers

%d bloggers like this: