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;

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

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';

---------- ---------

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';

---------- -------------------
     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';

---------- -------------------
     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;

------------------------------ ---------------------------------------------------------------------------
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'),


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


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.


  1. #1 von Kellyn Pot'Vin (@DBAKevlar) am 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 von Uwe Hesse am April 23, 2012 - 19:46

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

  3. #3 von Mahir M. Quluzade am April 24, 2012 - 11:06

    Thanks Uwe!

  4. #4 von Chandra Shekar Ramavath am 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 von Uwe Hesse am April 25, 2012 - 11:04

    Mahir, you’re welcome 🙂

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

    Klicke, um auf twp-bp-optimizer-stats-04042012-1577139.pdf zuzugreifen

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

  6. #6 von Amit Saraswat am September 5, 2012 - 18:35

    Thanks Uwe!!

    You always pick gems out of Oracle treasury.

  7. #7 von Subhash am Januar 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 von Uwe Hesse am Januar 9, 2014 - 14:55

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

  9. #9 von dadbm am Februar 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 von ursvenkat am Juli 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


Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:


Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..