Martins Blog

Trying to explain complex things in simple terms

Restore table statistics

Posted by Martin Bach on December 9, 2009

This can be a common problem for production DBAs. Let’s assume that query performance has deteriorated after gathering of statistics. Since Oracle 10g the stats are automatically preserved (check select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual to find out for how long) and can be restored. In 11.1 and later, it’s even better in the way that you can defer statistics publishing (see this post for more information).

This is actually dead easy! Assume the following, very very basic setup:

SQL> create table s (id number);

Table created.

Let’s gather statistics for it, the table is empty.

SQL> exec dbms_stats.gather_table_stats(user,'s')

PL/SQL procedure successfully completed.

The view dba_optstat_operations lists the stats gathering operations against targets. As an interesting side effect, if you specify the table/index name in dbms_stats in lower case, the target column will list it in lower case as well.

In our testcase:

SQL> select * from  dba_OPTSTAT_OPERATIONS
 2  where target = 'MARTIN.s';

OPERATION
----------------------------------------------------------------
TARGET
----------------------------------------------------------------
START_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
gather_table_stats
MARTIN.s
03-DEC-09 01.34.00.958993 PM +00:00
03-DEC-09 01.34.01.162917 PM +00:00

USER_TAB_STATS_HISTORY has the history of all the times the statistics were updated:

SQL> select * from USER_TAB_STATS_HISTORY
 2  where table_name = 'S';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME
------------------------------
STATS_UPDATE_TIME
---------------------------------------------------------------------------
S

03-DEC-09 01.34.01.018135 PM +00:00

The dictionary contains the statistics as always:

13:37:45 SQL> r
 1  select num_rows, blocks,empty_blocks,avg_space, avg_row_len, sample_size,
 2         to_char(last_analyzed, 'dd.mm.yyyy hh24:mi') last_analysed
 3  from user_TABles
 4* where table_name = 'S'

 NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYSED
---------- ---------- ------------ ---------- ----------- ----------- ----------------
 0          0            0          0           0           0 03.12.2009 13:34

Let’s generate a change in data and gather statistics

13:37:45 SQL> begin
13:37:55   2  for i in 1..1000 loop
13:37:57   3    insert into s values (i);
13:38:01   4  end loop;
13:38:03   5  end;
13:38:04   6  /

PL/SQL procedure successfully completed.

13:38:04 SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'s')

PL/SQL procedure successfully completed.

This is immediately reflected in the dictionary (this is 10.2 – in 11.1 and later you could actually defer the publication of the statistics!)

13:38:22 SQL> l
 1  select num_rows, blocks,empty_blocks,avg_space, avg_row_len, sample_size,
 2         to_char(last_analyzed, 'dd.mm.yyyy hh24:mi') last_analysed
 3  from user_TABles
 4* where table_name = 'S'
13:38:24 SQL> /

 NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYSED
---------- ---------- ------------ ---------- ----------- ----------- ----------------
 1000          5            0          0           4        1000 03.12.2009 13:38

The second statistic gathering is now reflected in the DBA_OPTSTAT_OPERATIONS view as expected:

13:39:39 SQL> l
 1  select * from  DBA_OPTSTAT_OPERATIONS
 2* where target = 'MARTIN.s'
13:39:41 SQL> /

OPERATION                      TARGET     START_TIME                                         END_TIME
------------------------------ ---------- -------------------------------------------------- --------------------------------------------------
gather_table_stats             MARTIN.s   03-DEC-09 01.34.00.958993 PM +00:00                03-DEC-09 01.34.01.162917 PM +00:00
gather_table_stats             MARTIN.s   03-DEC-09 01.38.15.866596 PM +00:00                03-DEC-09 01.38.16.280150 PM +00:00

And here’s the point where we assume it all went wrong! Users calling, CPU load going through the roof because of bad execution plans etc. We need to revert back! This is easier to implement than getting approvals to do so:

13:39:43 SQL> begin
13:39:58   2   dbms_stats.restore_table_stats('MARTIN','S','03-DEC-09 01.34.01.162917 PM +00:00');
13:40:16   3  end;
13:40:17   4  /

PL/SQL procedure successfully completed.

I simply picked the timestamp from the completion of the initial statistics gathering.This is also reflected in the stats history for the table (so you can’t sneak that one in silently)

13:41:33 SQL> r
 1  select * from user_tab_stats_history
 2* where table_name = 'S'

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
S                                                                                            03-DEC-09 01.34.01.018135 PM +00:00
S                                                                                            03-DEC-09 01.38.16.256738 PM +00:00
S                                                                                            03-DEC-09 01.40.51.896131 PM +00:00

But at least the stats are back to what they were:

13:41:54 SQL> l
 1  select num_rows, blocks,empty_blocks,avg_space, avg_row_len, sample_size,
 2         to_char(last_analyzed, 'dd.mm.yyyy hh24:mi') last_analysed
 3  from user_TABles
 4* where table_name = 'S'
13:41:56 SQL> /

 NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYSED
---------- ---------- ------------ ---------- ----------- ----------- ----------------
 0          0            0          0           0           0 03.12.2009 13:34

Great stuff!

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

 
%d bloggers like this: