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:
This is an example for a restoration of a table with definite timestamp. The starting situation is:
Statistics info:
1
2
3
| 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:
1
2
3
4
5
6
7
8
9
10
11
| 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 2TAB1 NAME 2TAB1 NPA 2TAB1 ID 2TAB1 LOCALITE 2 |
Now, let’s remove the statistics:
1
2
| exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCHMEMA1', TABNAME=>'TAB1');Procedure PL/SQL terminate with success. |
Checking information:
1
2
3
4
5
6
7
8
9
| 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:
1
2
3
4
5
6
7
8
9
10
| 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:
1
| 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 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 2TAB1 NAME 2TAB1 NPA 2TAB1 ID 2TAB1 LOCALITE 2SQL> 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:
Here are the steps:
1) Create the statistics table
1
| exec dbms_stats.create_stat_table('SCHEMA1','STATS'); |
2) Export the statistics
1
| 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:
1
2
3
| 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:
1
2
3
4
5
6
7
8
9
10
11
| 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 2TAB1 NAME 2TAB1 NPA 2TAB1 ID 2TAB1 LOCALITE 2 |
4) Delete statistics
1
2
3
| 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
1
| 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.
Here the name is ‘TAG1_TAB1′, thus I could have ‘TAG2_TAB1′, ‘TAG3_TAB1′,.. too.
6) Check if statistics are corrects
1
2
3
| 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 |
Comments
Post a Comment
Oracle DBA Information