Performance Tuning
Restore old optimizer statistics
User Rating: 
/ 0
- Details
-
Parent Category: Articles
-
Created on Wednesday, 27 March 2013 11:43
-
Last Updated on Wednesday, 27 March 2013 11:59
-
Published on Wednesday, 27 March 2013 11:54
-
Written by Guy Lambregts
-
Hits: 5204
Restore old optimizer statistics.
From Oracle 10G R1 onwards by default the history of overwritten optimizer statistics is kept in the sysaux tablespace for 31 days
If per accident the newer optimizer statistics would trigger worse explain plans and if our customer requires the old statistics to be reset
( to get back to the older better explain plans ) we can achieve this using dbms_stats.restore_schema_stats & dbms_stats.restore_table_stats
This info of old stats is kept in the SYSAUX tablespace, we can query the views below
SQL > select view_name from dba_views where view_name like '%STAT%HIST%';
VIEW_NAME
------------------------------
ALL_TAB_STATS_HISTORY
DBA_TAB_STATS_HISTORY
USER_TAB_STATS_HISTORY
The view dba_optstat_operations lists us info about the ... optimizer stats operations ( sounds logic )
SQL > column start_time format a35
SQL > column end_time format a35
SQL > column target format a30
SQL > column operation format a30
SQL > select * from dba_optstat_operations;
OPERATION TARGET START_TIME END_TIME
------------------------------ ------------------------------ ----------------------------------- -----------------------------------
gather_database_stats(auto) 21-MAR-13 10.00.03.868860 PM +01:00 21-MAR-13 10.02.12.865781 PM +01:00
gather_database_stats(auto) 22-MAR-13 10.00.03.911726 PM +01:00 22-MAR-13 10.02.40.560767 PM +01:00
gather_database_stats(auto) 25-MAR-13 10.00.00.982278 PM +01:00 25-MAR-13 10.05.06.405809 PM +01:00
gather_database_stats(auto) 12-MAR-13 10.00.02.661889 PM +01:00 12-MAR-13 10.03.06.343458 PM +01:00
gather_database_stats(auto) 13-MAR-13 10.00.03.399469 PM +01:00 13-MAR-13 10.02.10.024210 PM +01:00
gather_database_stats(auto) 23-MAR-13 06.00.03.907175 AM +01:00 23-MAR-13 06.01.33.470950 AM +01:00
gather_database_stats(auto) 20-MAR-13 10.00.03.360160 PM +01:00 20-MAR-13 10.03.07.109314 PM +01:00
gather_database_stats(auto) 26-MAR-13 10.00.03.412510 PM +01:00 26-MAR-13 10.03.41.139514 PM +01:00
8 rows selected.
v$sysaux_occupants lists us the below info
SQL > select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';
SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS
*** MOVE PROCEDURE NOT APPLICABLE *** 120320
It does not take an hero to restore old stats, this is a test with a test database
Table stats are old
SQL> select table_name,last_analyzed from user_tables;
TABLE_NAME LAST_ANALYZED
------------------------------ ----------------
T_DELIVERY_DL 13/02/2013 17:22
T_COUNTRY_CO 13/02/2013 17:22
T_ORDER_SORTED_OR 13/02/2013 17:25
T_SUPPLIER_SU 13/02/2013 17:25
T_ORDER_OR 13/02/2013 17:24
T_CURRENCY_CR 13/02/2013 17:22
T_INVOICE_IV 13/02/2013 17:22
7 rows selected.
Let us gather NEW optimizer stats
SQL> begin
2 dbms_stats.gather_schema_stats(user,cascade => true); end;
3 /
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed from user_tables;
TABLE_NAME LAST_ANALYZED
------------------------------ ----------------
T_DELIVERY_DL 27/03/2013 11:17
T_COUNTRY_CO 27/03/2013 11:17
T_ORDER_SORTED_OR 27/03/2013 11:17
T_SUPPLIER_SU 27/03/2013 11:17
T_ORDER_OR 27/03/2013 11:17
T_CURRENCY_CR 27/03/2013 11:17
T_INVOICE_IV 27/03/2013 11:17
7 rows selected.
Restore old stats
A performance decrease is claimed, our customer asks us to reset the get the same performance as it was before, so let' s restore the old schema stats
SQL> begin
2 dbms_stats.restore_schema_stats(user,'26-MAR-13 11.12.24.626021 AM +01:00');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed from user_tables;
TABLE_NAME LAST_ANALYZED
------------------------------ ----------------
T_DELIVERY_DL 13/02/2013 17:22
T_COUNTRY_CO 13/02/2013 17:22
T_ORDER_SORTED_OR 13/02/2013 17:25
T_SUPPLIER_SU 13/02/2013 17:25
T_ORDER_OR 13/02/2013 17:24
T_CURRENCY_CR 13/02/2013 17:22
T_INVOICE_IV 13/02/2013 17:22
7 rows selected.
SQL> column operation format a30
SQL> column start_time format a30
SQL> column end_time format a30
SQL> select * from dba_optstat_operations; -- layout to be improved
OPERATION TARGET START_TIME END_TIME
------------------------------ ---------------------------------------------------------------- ---------------------------------------- ----------------------------------------
gather_database_stats(auto) 08-MAR-13 02.50.16.592588 PM +01:00 08-MAR-13 02.54.56.235864 PM +01:00
copy_table_stats SYS.WRH$_FILESTATXS.WRH$_FILEST_2121303953_1208 11-MAR-13 02.38.41.033424 PM +01:00 11-MAR-13 02.38.41.986284 PM +01:00
copy_table_stats SYS.WRH$_SQLSTAT.WRH$_SQLSTA_2121303953_1208 11-MAR-13 02.38.42.397268 PM +01:00 11-MAR-13 02.38.42.626240 PM +01:00
copy_table_stats SYS.WRH$_SYSTEM_EVENT.WRH$_SYSTEM_2121303953_1208 11-MAR-13 02.38.42.789025 PM +01:00 11-MAR-13 02.38.42.801021 PM +01:00
copy_table_stats SYS.WRH$_WAITSTAT.WRH$_WAITST_2121303953_1208 11-MAR-13 02.38.43.006831 PM +01:00 11-MAR-13 02.38.43.016445 PM +01:00
copy_table_stats SYS.WRH$_LATCH.WRH$_LATCH_2121303953_1208 11-MAR-13 02.38.43.389863 PM +01:00 11-MAR-13 02.38.43.596667 PM +01:00
copy_table_stats SYS.WRH$_LATCH_MISSES_SUMMARY.WRH$_LATCH__2121303953_1208 11-MAR-13 02.38.43.777661 PM +01:00 11-MAR-13 02.38.43.787960 PM +01:00
copy_table_stats SYS.WRH$_DB_CACHE_ADVICE.WRH$_DB_CAC_2121303953_1208 11-MAR-13 02.38.43.871251 PM +01:00 11-MAR-13 02.38.43.944033 PM +01:00
copy_table_stats SYS.WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_2121303953_1208 11-MAR-13 02.38.44.087949 PM +01:00 11-MAR-13 02.38.44.171553 PM +01:00
copy_table_stats SYS.WRH$_SGASTAT.WRH$_SGASTA_2121303953_1208 11-MAR-13 02.38.44.231664 PM +01:00 11-MAR-13 02.38.44.248057 PM +01:00
copy_table_stats SYS.WRH$_SYSSTAT.WRH$_SYSSTA_2121303953_1208 11-MAR-13 02.38.44.386896 PM +01:00 11-MAR-13 02.38.44.395797 PM +01:00
copy_table_stats SYS.WRH$_PARAMETER.WRH$_PARAME_2121303953_1208 11-MAR-13 02.38.44.481089 PM +01:00 11-MAR-13 02.38.44.491346 PM +01:00
copy_table_stats SYS.WRH$_SEG_STAT.WRH$_SEG_ST_2121303953_1208 11-MAR-13 02.38.44.842999 PM +01:00 11-MAR-13 02.38.45.166086 PM +01:00
copy_table_stats SYS.WRH$_SERVICE_STAT.WRH$_SERVIC_2121303953_1208 11-MAR-13 02.38.45.260562 PM +01:00 11-MAR-13 02.38.45.294079 PM +01:00
copy_table_stats SYS.WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_2121303953_1208 11-MAR-13 02.38.45.663916 PM +01:00 11-MAR-13 02.38.46.558268 PM +01:00
copy_table_stats SYS.WRH$_TABLESPACE_STAT.WRH$_TABLES_2121303953_1208 11-MAR-13 02.38.46.695520 PM +01:00 11-MAR-13 02.38.46.719540 PM +01:00
copy_table_stats SYS.WRH$_OSSTAT.WRH$_OSSTAT_2121303953_1208 11-MAR-13 02.38.46.817516 PM +01:00 11-MAR-13 02.38.46.830713 PM +01:00
copy_table_stats SYS.WRH$_SYS_TIME_MODEL.WRH$_SYS_TI_2121303953_1208 11-MAR-13 02.38.46.952732 PM +01:00 11-MAR-13 02.38.46.977654 PM +01:00
copy_table_stats SYS.WRH$_SERVICE_WAIT_CLASS.WRH$_SERVIC_2121303953_1208 11-MAR-13 02.38.47.052823 PM +01:00 11-MAR-13 02.38.47.062994 PM +01:00
copy_table_stats SYS.WRH$_EVENT_HISTOGRAM.WRH$_EVENT__2121303953_1208 11-MAR-13 02.38.47.239814 PM +01:00 11-MAR-13 02.38.47.265572 PM +01:00
copy_table_stats SYS.WRH$_MVPARAMETER.WRH$_MVPARA_2121303953_1208 11-MAR-13 02.38.47.348433 PM +01:00 11-MAR-13 02.38.47.358969 PM +01:00
gather_schema_stats TUNING 27-MAR-13 11.17.33.781600 AM +01:00 27-MAR-13 11.17.52.789154 AM +01:00
restore_schema_stats TUNING 27-MAR-13 11.18.25.639399 AM +01:00 27-MAR-13 11.18.25.861862 AM +01:00
