UPDATE 230306 this post was originally written in December 2009. The examples have been verified & updated for Oracle 21.7.0, however there are most likely additional changes that have happened in the meantime that aren’t properly reflected here.
Introduction
The need to restore table statistics can be a common task for production DBAs. Let’s assume that query performance has deteriorated after gathering of statistics. You have 2 options:
- Create another set of statistics (risky! Don’t do that)
- Restore the previous statistics
Thankfully stats are automatically preserved and can be restored since Oracle 10g. The first option is only a theoretical option as there is no guarantee that new statistics improve the situation. It’s likely, but not certain. The old statistics on the other hand are proven to work, so let’s restore them.
By the way, statistics retention time is configurable. The current retention time can be determined using the following query:
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
______________________________
31
In 11.1 and later, it got even better: you can defer statistics publishing (see this post for more information).
Setup
Assume the following, very very basic setup of a non-partitioned table:
SQL> create table s (id number); Table created.
Let’s gather statistics explicitly for the empty table:
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.
The stats gathering operation is reflected in the aforementioned view:
select id, operation, target, start_time, end_time from dba_optstat_operations
where start_time > systimestamp - interval '5' minute;
ID OPERATION TARGET START_TIME END_TIME
______ _____________________ __________________ ______________________________________ ______________________________________
405 gather_table_stats "MARTINDBA"."S" 06-MAR-23 08.14.03.752519000 PM GMT 06-MAR-23 08.14.03.778287000 PM GMT
You find statistic information in many other places as well. USER_TAB_STATS_HISTORY for example keeps the history of all statistics updates:
select table_name, stats_update_time from user_tab_stats_history where table_name = 'S'; TABLE_NAME STATS_UPDATE_TIME _____________ ______________________________________ S 06-MAR-23 08.14.03.769967000 PM GMT
Last but not least DBA_TABLES keeps information about the segment statistics as well:
select num_rows, blocks,empty_blocks,avg_space, avg_row_len, sample_size,
to_char(last_analyzed, 'dd.mm.yyyy hh24:mi') last_analysed
from USER_TABLES
where table_name = 'S';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYSED
___________ _________ _______________ ____________ ______________ ______________ ___________________
0 0 0 0 0 0 06.03.2023 20:14
Statistics Update
Let’s generate a change in data and gather statistics:
begin for i in 1..1000 loop insert into s values (i); end loop; end; / commit; begin dbms_stats.gather_table_stats(user,'s'); end; /
By default new statistics are immediately published to the dictionary. If that’s not what you want, remember that you can defer statistics publishing in 11.1 and later.
select num_rows, blocks,empty_blocks,avg_space, avg_row_len, sample_size,
to_char(last_analyzed, 'dd.mm.yyyy hh24:mi') last_analysed
from USER_TABLES
where table_name = 'S';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYSED
___________ _________ _______________ ____________ ______________ ______________ ___________________
1000 5 0 0 4 1000 06.03.2023 20:18
The second statistic gathering operation is now reflected in the DBA_OPTSTAT_OPERATIONS view as expected:
select id, operation, target, start_time, end_time from dba_optstat_operations
where target = '"MARTINDBA"."S"' and start_time > systimestamp - interval '15' minute;
ID OPERATION TARGET START_TIME END_TIME
______ _____________________ __________________ ______________________________________ ______________________________________
405 gather_table_stats "MARTINDBA"."S" 06-MAR-23 08.14.03.752519000 PM GMT 06-MAR-23 08.14.03.778287000 PM GMT
406 gather_table_stats "MARTINDBA"."S" 06-MAR-23 08.18.25.295581000 PM GMT 06-MAR-23 08.18.25.340237000 PM GMT
Problems! We need to revert!
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:
begin dbms_stats.restore_table_stats(user,'S','06-MAR-23 08.15.03.752519000 PM GMT'); end; /
I simply picked the timestamp a minute past the completion of the initial statistics gathering when the table was empty. The statistics restore operation is reflected in the stats history for the table so you can’t sneak that one in silently :)
SQL> select * from user_tab_stats_history; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME _____________ _________________ ____________________ ______________________________________ S 06-MAR-23 08.14.03.769967000 PM GMT S 06-MAR-23 08.18.25.335210000 PM GMT S 06-MAR-23 08.27.16.956775000 PM GMT
But at least the stats are back to what they were:
select num_rows, blocks,empty_blocks,avg_space, avg_row_len, sample_size,
to_char(last_analyzed, 'dd.mm.yyyy hh24:mi') last_analysed
from USER_TABLES
where table_name = 'S';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYSED
___________ _________ _______________ ____________ ______________ ______________ ___________________
0 0 0 0 0 0 06.03.2023 20:14
Great stuff!
Martins Blog 