통계복원(restore_table_stats)

■통계 복원

만약 실행이 정상적이던 SQL이 특정 시점부터 느려졌다면 실행계획의 변경도 의심해보아야 한다. 만약 실행계획의 변경이 통계정보의 갱신에 의한 변경이고 최적화되지 않았다면 통계의 복원을 사용할 기회이다.

 

dbms_stats.restore_*_stats 프로시저로 이전 통계 복원 가능하다

예:
begin
 dbms_stats.restore_table_stats(ownname=>'oe'
  , tabname=>'inventories'
  , as_of_timestamp=>to_date('2015-12-28 10:00', 'yyyy-mm-dd hh24:mi'));
end;
/

 

or

 

begin
 dbms_stats.restore_table_stats('oe','inventories',to_date('2015-12-28 10:00', 'yyyy-mm-dd hh24:mi'));
end;
/

 

■테이블 통계 변경 내역 확인(테이블만 존재)

단, dbms_stats를 통해 수집한 내역만 확인 가능하다. analyze를 통한것은 기록이 남지 않는다.

 

select * from dba_tab_stats_history
where owner = 'HR'
and table_name='EMP'

orcl@SYS> exec print_table('select * from dba_tab_stats_history where owner = ''HR'' and table_name=''EMP''');
OWNER                         : HR
TABLE_NAME                    : EMP
PARTITION_NAME                :
SUBPARTITION_NAME             :
STATS_UPDATE_TIME             : 29-DEC-15 05.47.24.880458 PM +09:00
-----------------
OWNER                         : HR
TABLE_NAME                    : EMP
PARTITION_NAME                :
SUBPARTITION_NAME             :
STATS_UPDATE_TIME             : 29-DEC-15 06.07.28.041455 PM +09:00
-----------------
OWNER                         : HR
TABLE_NAME                    : EMP
PARTITION_NAME                :
SUBPARTITION_NAME             :
STATS_UPDATE_TIME             : 22-DEC-15 10.00.16.870032 PM +09:00
-----------------

PL/SQL procedure successfully completed.

 

 

■보관주기

기본값은 31일

변경은 dmbs_stats.alter_stats_history_retention 으로 설정한다.

 

15일로 변경 예시

 

orcl@SYS> exec dbms_stats.alter_stats_history_retention(15);

PL/SQL procedure successfully completed.

 

확인

 

orcl@HR> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         15

 

■ 프로시저 정보

PROCEDURE RESTORE_DATABASE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
 FORCE                          BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_DICTIONARY_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
 FORCE                          BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_FIXED_OBJECTS_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
 FORCE                          BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_SCHEMA_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
 FORCE                          BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_SYSTEM_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
PROCEDURE RESTORE_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
 RESTORE_CLUSTER_INDEX          BOOLEAN                 IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT