In some cases we may find that gathering fresh statistics has led to the optimizer executing sub-optimal execution plans and we would like to restore the statistics as of a date when we had good performance.
I have found out, that it should be table called DM_SYSOBJECT_S.
So I have checked the available statistics:
SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='DM_SYSOBJECT_S' and owner='EQBP01';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
DM_SYSOBJECT_S 15-NOV-14 04.29.49.329957 AM +01:00
DM_SYSOBJECT_S 15-NOV-14 11.51.13.874860 AM +01:00
DM_SYSOBJECT_S 22-NOV-14 04.47.20.112931 AM +01:00
DM_SYSOBJECT_S 24-NOV-14 02.23.28.306822 PM +01:00
DM_SYSOBJECT_S 24-NOV-14 05.30.47.253816 PM +01:00
I knew, that betwwen the 15.11 and 22.11 everything was working fine, so I have restored the statistics from 15.11:
SQL> execute dbms_stats.restore_table_stats('EQBP01','DM_SYSOBJECT_S','15-NOV-14 11.51.13.874860 AM +01:00');
PL/SQL procedure successfully completed.
After that, the problem was solved and queries were running fine.
Now, I have to modify the automatic statistics gathering job to use the histograms on this table/schema.
Check, what is set:
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
Set the parameter you want:
SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('EQBP01','method_opt','FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed.
Check again:
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
I can see, that there is the same value as before setting we have made, but when checked the tables in that schema, the parameter value is changed:
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01','DM_SYSOBJECT_S') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT
So we will see what will happen after the statistics will be calcualted during the weekend.
I have found out, that it should be table called DM_SYSOBJECT_S.
So I have checked the available statistics:
SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='DM_SYSOBJECT_S' and owner='EQBP01';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
DM_SYSOBJECT_S 15-NOV-14 04.29.49.329957 AM +01:00
DM_SYSOBJECT_S 15-NOV-14 11.51.13.874860 AM +01:00
DM_SYSOBJECT_S 22-NOV-14 04.47.20.112931 AM +01:00
DM_SYSOBJECT_S 24-NOV-14 02.23.28.306822 PM +01:00
DM_SYSOBJECT_S 24-NOV-14 05.30.47.253816 PM +01:00
I knew, that betwwen the 15.11 and 22.11 everything was working fine, so I have restored the statistics from 15.11:
SQL> execute dbms_stats.restore_table_stats('EQBP01','DM_SYSOBJECT_S','15-NOV-14 11.51.13.874860 AM +01:00');
PL/SQL procedure successfully completed.
After that, the problem was solved and queries were running fine.
Now, I have to modify the automatic statistics gathering job to use the histograms on this table/schema.
Check, what is set:
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
Set the parameter you want:
SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('EQBP01','method_opt','FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed.
Check again:
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
I can see, that there is the same value as before setting we have made, but when checked the tables in that schema, the parameter value is changed:
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> select DBMS_STATS.GET_PREFS('method_opt','EQBP01','DM_SYSOBJECT_S') "what" from dual;
what
--------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT
So we will see what will happen after the statistics will be calcualted during the weekend.