Blog José Navarro How to retrieve previous table statistics using Oracle dbms_stats
dbi services Blog
Welcome to the dbi services Blog! This blog focuses on database infrastructure and middleware topics. It covers technologies such as Oracle, Microsoft SQL Server, MySQL, Sybase, Linux, or Documentum (etc.). The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
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------------------------------ ------------- -------------------
TAB1
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 ID 2
Now, let's have a look which statistics are available:
select count(*), stats_update_timefrom 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.
Related Post
- How to handle mutex issues after migrating to Oracle 11.1 You have recently migrated to Oracle Database 11g Release 1 (11.1) and have had some issues with so called "mutexes"? Don't worry, these nasty sound...
- Oracle Basics (1): Oracle Database vs Oracle Instance While beginning to work with Oracle, one of the most common confusions is between Oracle Database and Oracle Instance. The term Oracle Database is m...
- Monitoring 11g databases with Grid Control 10.2.0.5 The Metalink note 949858.1 describes problems with 10.2.0.5 agents monitoring 11g databases - this might be the case if you have an "old" 10.2.0.5 Gri...
- Password Oracle : vos mots de passe sont-ils sécurisés ? Quelles sont les faiblesses de l'encryption des mots de passe Oracle ? Comment est-ce qu'Oracle crée les clés de hachage afin d'en améliorer la sécuri...
- Oracle Basics (2) - Data Storage What is finally the primary objective of a database? Storing Data The question is, how Oracle makes it. For many beginners, a confusing topic is to ...
About the author
- Florian Haas' blog
- Dirk Nachbar's blog on Oracle Fusion Middleware & Application Server
- Oracle Scratchpad - Jonathan Lewis' blog
- Martin Bach's blog
- Striving for optimal performance - Chris Antognini's blog
- The Tom Kyte Blog (Ask Tom)
- Blog of Adar-Consult
- Alex Gorbachev's blog
- Marcus Mönnig's Oracle & Mumbai Blog
HI, I need more information for Oracle