Pickleball spielen

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.