통계 export/import

Knowledge/Perfomance Tuning 2015. 12. 29. 19:48

■dbms_stats 프로시저 사용한다.

 

1. create_stat_table 로 통계테이블을 만든다.

PROCEDURE CREATE_STAT_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 STATTAB                        VARCHAR2                IN
 TBLSPACE                       VARCHAR2                IN     DEFAULT
 GLOBAL_TEMPORARY               BOOLEAN                 IN     DEFAULT

 

$ sqlplus hr/hr

 

orcl@HR> exec dbms_stats.create_stat_table('hr','stat');

PL/SQL procedure successfully completed.

 

2. export_*_stats 를 통해 통계 테이블로 저장

* 위치에 들어갈 수 있는 값
database, column, dictionary, fixed_object, index, pending, schema, system, table

 

3. 다른 db로 통계 테이블 이동(datapump등 사용)

 

4. import_*_stats 를 통해 통계를 딕셔너리에 입력

* 위치에 들어갈 수 있는 값
database, column, dictionary, fixed_object, index, pending, schema, system, table

 

■LAB

1. export_table_stats를 통해 통계를 테이블에 저장

 

PROCEDURE EXPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT

 

$ sqlplus hr/hr

 

orcl@HR> exec dbms_stats.export_table_stats('hr','employees',null,'stat');

PL/SQL procedure successfully completed.

 

※stat은 위 1단계에서 만든 통계 저장용 테이블이다.

 

orcl@SYS>  exec print_table('select * from hr.stat where rownum=1');
STATID                        :
TYPE                          : C
VERSION                       : 5
FLAGS                         : 2
C1                            : EMPLOYEES
C2                            :
C3                            :
C4                            : LAST_NAME
C5                            : HR
N1                            : 103
N2                            : .00462962962962963
N3                            : 103
N4                            : 108
N5                            : 0
N6                            : 339495007414717000000000000000000000
N7                            : 469506047861463000000000000000000000
N8                            : 8
N9                            : 1
N10                           : 21
N11                           : 355127267452184000000000000000000000
N12                           :
D1                            : 23-nov-2015 11:00:08
R1                            : 4162656C
R2                            : 5A6C6F746B6579
CH1                           :
CL1                           :

 

2. import_table_stats를 통해 통계를 dictionary에 저장. 만약 다른 DB라면 export/import or empdp/impdp를 통해 테이블을 넣었을 것이다.

 

PROCEDURE IMPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT

 

orcl@HR> exec dbms_stats.import_table_stats('hr', 'employees', null, 'stat');                                                

PL/SQL procedure successfully completed.

 

 

저작자표시 비영리 변경금지 (새창열림)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향  (0) 2016.08.08
Adaptive cursor simple test  (0) 2016.01.02
통계복원(restore_table_stats)  (0) 2015.12.29
statistics lock/unlock  (0) 2015.12.29
수동통계수집(gathering statistics manually)  (0) 2015.12.29
Posted by neo-orcl
,

통계복원(restore_table_stats)

Knowledge/Perfomance Tuning 2015. 12. 29. 19:32

■통계 복원

만약 실행이 정상적이던 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

 

저작자표시 비영리 변경금지 (새창열림)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Adaptive cursor simple test  (0) 2016.01.02
통계 export/import  (0) 2015.12.29
statistics lock/unlock  (0) 2015.12.29
수동통계수집(gathering statistics manually)  (0) 2015.12.29
histogram 고려사항  (0) 2015.12.29
Posted by neo-orcl
,

statistics lock/unlock

Knowledge/Perfomance Tuning 2015. 12. 29. 19:31

■통계 lock

    자동 수집 방지 목적

예: 매번 동적 샘플링하도록 설정
begin
 dbms_stats.delete_table_stats('oe','orders');
 dbms_stats.lock_table_stats('oe','orders');
end;
/

 

예: 대표 값에 대한 통계가 있는 테이블에 대한 통계 lock
begin
 dbms_stats.gather_table_stats('oe','orders');
 dbms_stats.lock_table_stats('oe','orders');
end;
/

 

■ lock 방법

lock_partition_stats
lock_schema_stats
lock_table_stats

 

■unlock 방법

unlock_partition_stats
unlock_schema_stats
unlock_table_stats

 

■통계 lock 되었는지 확인하는 방법

 

select table_name, stattype_locked from user_tab_statistics
where stattype_locked='ALL';

 

※stattype은 기본값이 ALL이며 아직까진 다른 옵션은 개발되지 않은 것으로 보인다. 매뉴얼에도 없다.

 

■통계가 lock되어있더라도 강제 갱신 방법

통계가 lock된 테이블이나 테이블의 종속(인덱스, 열, 히스토그램)된 객체에 통계 갱신이나 수집, import를 시도하면
아래처럼 ora-20005를 만난다.

 

SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX')
BEGIN dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1

 

이때 force 옵션을 사용하면 갱신 가능하다.

SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX', force=>true);
PL/SQL procedure successfully completed.

저작자표시 비영리 변경금지 (새창열림)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

통계 export/import  (0) 2015.12.29
통계복원(restore_table_stats)  (0) 2015.12.29
수동통계수집(gathering statistics manually)  (0) 2015.12.29
histogram 고려사항  (0) 2015.12.29
자동 통계 수집(automatic statistics gathering)  (0) 2015.12.29
Posted by neo-orcl
,

티스토리툴바