Infrastructure at your Service

Yann Neuhaus

How to retrieve previous table statistics using Oracle dbms_stats

Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.

1. Statistics restore

This method uses the procedure dbms_stats.restore_%_stats, where % can be table, schema, database, and fixed_objects.
This is an example for a restoration of a table with definite timestamp. The starting situation is:

Statistics info:

SQL> select table_name, num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS')from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS     LAST_ANALYZED
------------------------------ -------------     -------------------
TAB1                                    5                     22.02.2011 10:20:07

 

Histograms info:

select table_name, column_name, count(*) 
  from dba_histograms  
 where owner='SCHEMA1'
   and table_name='TAB1'
 group by table_name, column_name;TABLE_NAME               COLUMN_NAME            COUNT(*)
-----------------           --------------------        ----------
TAB1                           ADRESS                        2
TAB1                           NAME                           2
TAB1                           NPA                              2
TAB1                           ID                                 2
TAB1                           LOCALITE                      2

 

Now, let’s remove the statistics:

exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCHMEMA1', TABNAME=>'TAB1');
Procedure PL/SQL terminate with success.

 

Checking information:

SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS   LAST_ANALYZED
------------------------------ -------------   -------------------
TAB1SQL> select table_name, column_name, count(*)
  2    from dba_histograms
  3   where owner='SCHEMA1'
  4     and table_name='TAB1'
  5   group by table_name, column_name;TABLE_NAME                     COLUMN_NAME            COUNT(*)
----------------                   --------------------       ----------
TAB1                                  ID                                2

 

Now, let’s have a look which statistics are available:

select count(*), stats_update_time
  from dba_tab_stats_history  
 where owner='SCHEMA1'
   and table_name='TAB1'
 group by stats_update_time;  COUNT(*)     STATS_UPDATE_TIME
-------------    ---------------------------------------------------------------------------
         1           11/02/11 21:00:47,054000 +01:00
         1           22/02/11 10:20:07,587000 +01:00
         1           22/02/11 10:25:12,524000 +01:00
  9 rows selected.

 

Let’s try to restore the second line:

SQL> exec dbms_stats.restore_table_stats(ownname=>'SCHEMA1',tabname=>'TAB1',AS_OF_TIMESTAMP=>'22/02/11 10:20:07,587000 +01:00');Procedure PL/SQL terminate with success.

 

Now, I could check if the previous statistics were retrieved correctly:

SQL> select table_name, column_name, count(*)
  2    from dba_histograms
  3   where owner='SCHEMA1'
  4     and table_name='TAB1'
  5   group by table_name, column_name;TABLE_NAME                COLUMN_NAME            COUNT(*)
----------------             --------------------         ----------
TAB1                           ADRESS                         2
TAB1                           NAME                            2
TAB1                           NPA                               2
TAB1                           ID                                  2
TAB1                           LOCALITE                       2
SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS   LAST_ANALYZED
------------------------------ -------------   -------------------
TAB1                                    5                  22.02.2011 10:20:07

 

The disadvantage with this method is knowing which timestamp should be restored. In the following case, the solution is allowed to put a tag. It is therefore easier to restore the desired statistics.

2. Statistics export

An alternative is to store the statistics in a table and associate it with a tag.
Here are the steps:

1) Create the statistics table

exec dbms_stats.create_stat_table('SCHEMA1','STATS');

2) Export the statistics

exec dbms_stats.export_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);This procedure could be also used to extract statistics from the production to feed that of the test.

3) Check statistics information

Statistics info:

SQL> select table_name, num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS')from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS     LAST_ANALYZED
------------------------------ -------------     -------------------
TAB1                                    5                     22.02.2011 10:20:07

 

Histograms info:

select table_name, column_name, count(*) 
  from dba_histograms  
 where owner='SCHEMA1'
   and table_name='TAB1'
 group by table_name, column_name;TABLE_NAME               COLUMN_NAME            COUNT(*)
-----------------           --------------------        ----------
TAB1                           ADRESS                        2
TAB1                           NAME                           2
TAB1                           NPA                              2
TAB1                           ID                                 2
TAB1                           LOCALITE                      2

4) Delete statistics

exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCHEMA1', TABNAME=>'TAB1');SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS   LAST_ANALYZED
------------------------------ -------------   -------------------
TAB1

5) Import statistics

exec dbms_stats.import_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);

The statid parameter is allowed to store several evolutions of statistics and thus the capacity to go through the time.
Here the name is ‘TAG1_TAB1’, thus I could have ‘TAG2_TAB1’, ‘TAG3_TAB1’,.. too.

6) Check if statistics are corrects

SQL> select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
TAB1                                    5 22.02.2011 10:20:07

I will come back on this topic in another posting.

4 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Yann Neuhaus
Yann Neuhaus

Chairman of the Board, Chief Sales Officer (CSO), Region Manager
Region Manager