ORACLE 과거 통계정보 restore 하기.

주말에 통계정보수집하고 월요일 아침 출근했더니 

SQL의 PLAN이 바뀌어 성능이 제대로 나오지 않아 문제 발생하는 경험 ..

아마 누구나 있을 듯 한데요.

10g 이후 통계정보가 수집되면 과거 통계정보를 특정 retention 기간동안 보관하게 되어 있어

통계정보 이상 시점이 확인이 된다면 과거의 잘돌던 통계정보를 다시 restore 해서 문제를 일단 진정시킬 수 있습니다. 

다음은 자동으로 저장되는 과거 통계정보 확인 방법 및 통계정보 restore 하는 방법입니다. 

1. retention 기간 확인 

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

2. restore가 가능한 가장 오래된 날짜 확인. 

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

3. 특정 table의 statistics history 확인 

select OWNER,TABLE_NAME,PARTITION_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name = '&TABLE_NAME' order by STATS_UPDATE_TIME;

4. 현재 통계정보 backup 

exec dbms_stats.create_stat_table(ownname => 'sys', stattab => 'old_stats3');

exec dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMP', stattab=>'old_stats3',statown  => 'SYS');

5. 통계정보 restore. 

-- execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)

-- execute DBMS_STATS.RESTORE_DATABASE_STATS(date)

-- execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)

-- execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)

-- execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)

-- execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)

예: 

execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');

참고: Restoring table statistics in 10G onwards (Doc ID 452011.1)