Optimizing Oracle Optimizer (2009년)
통계정보관리
통계정보 비교하기
h5.통계정보전과 후를 비교하는 방법
- 통계정보를 저장할 테이블 생성
exec dbms_stats.create_stat_table('woong', 't1_stat');
- 통계정보 export
exec dbms_stats.export_table_stats('woong', 't1',null,'t1_stat');
- 새로운 통계정보 생성
exec dbms_stats.gather_table_stats('woong', 't1', method_opt=>'for columns c1 size 254');
- 통계정보 비교
select * from table(dbms_stats.diff_table_stats_in_stattab('woong','t1','t1_stat'));
----------------------------------------------------------------------------------
새로운 통계정보를 생성하는 것은 기존 통계이후 새로운 데이터가 쌓여 있는것 이므로 데이터를 생성한다.
----------------------------------------------------------------------------------
woong:WOONG >
t1 insert into t1
2 select 1,1
3 from dual
4 connect by level <= 100000;
100000 개의 행이 만들어졌습니다.
경 과: 00:00:01.28
woong:WOONG >
t1 commit;
커밋이 완료되었습니다
경 과: 00:00:00.11
woong:WOONG >
t1 exec dbms_stats.gather_table_stats('woong', 't1', method_opt=>'for columns c1 size 254');
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.68
woong:WOONG >
t1 select * from table(dbms_stats.diff_table_stats_in_stattab('woong','t1','t1_stat'));
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
############################################################################### 1721.05263
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : T1
OWNER : WOONG
SOURCE A : User statistics table T1_STAT <- SOURCE A = T1_STAT 테이블과
: Statid :
: Owner : WOONG
SOURCE B : Current Statistics in dictionary <- SOURCE B = 현재 통계정보 딕셔너리와 비교한다.
PCTTHRESHOLD : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
T1 T A 10000 20 7 10000
B 110000 244 6 110000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
C1 A 10000 .0001 NO 0 4 C102 C302 10000
B 10000 .000009076 YES 0 4 C102 C302 5509
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................
INDEX: T1_N1
............
T1_N1 I A 10000 19 10000 1 1 18 1 10000
B 110000 346 10000 1 1 172 1 110000
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
INDEX: T1_N2
............
T1_N2 I A 10000 19 10000 1 1 18 1 10000
B 110000 346 10000 1 1 172 1 110000
###############################################################################
1 개의 행이 선택되었습니다.
경 과: 00:00:00.03
통계정보 Backup/Recovery
- 통계정보 Export
exec dbms_stats.export_table_stats(user_name,table_name);
- 통계정보 Import
exec dbms_stats.import_table_stats(user_name,table_name,stat_table_name);
자동 Backup/Recovery (10g)
- SYSAUX tablespace에 저장
- 통계정보를 생성 할 때마다 저장된다.
- DBA_TAB_STATS_HISTORY 뷰를 통해 조회
- 특정시점의 통계정보로 Recovery할 수 있다.
- dbms_stats.restore_table_stats(user_name,table_name,DBA_TAB_STATS_HISTORY.stats_update_time);
woong:WOONG >
t1 desc DBA_TAB_STATS_HISTORY
이름 널? 유형
----------------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
STATS_UPDATE_TIME TIMESTAMP(6) WITH TIME ZONE
woong:WOONG >
t1 select *
2 from (select rownum rn
3 , OWNER
4 , TABLE_NAME
5 , PARTITION_NAME
6 , SUBPARTITION_NAME
7 , STATS_UPDATE_TIME
8 from DBA_TAB_STATS_HISTORY
9 where owner = 'WOONG'
10 order by STATS_UPDATE_TIME desc)
11 where rn = 1;
RN OWNER TABLE_NAME PARTITION_NAME
---------- ------------------------------ ------------------------------ ------------------------------
SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
1 WOONG WOONG_T1
09/03/26 17:41:29.625000 +09:00
1 개의 행이 선택되었습니다.
경 과: 00:00:00.03
woong:WOONG >
t1 exec dbms_stats.restore_table_stats('woong','t1', '09/03/26 17:41:29.625000 +09:00');
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.15
woong:WOONG >
t1 select * from table(dbms_stats.diff_table_stats_in_stattab('woong','t1','t1_stat'));
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
############################################################################### 1721.05263
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : T1
OWNER : WOONG
SOURCE A : User statistics table T1_STAT
: Statid :
: Owner : WOONG
SOURCE B : Current Statistics in dictionary
PCTTHRESHOLD : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
T1 T A 10000 20 7 10000
B 110000 244 6 110000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
C1 A 10000 .0001 NO 0 4 C102 C302 10000
B 10000 .000009076 YES 0 4 C102 C302 5509
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................
INDEX: T1_N1
............
T1_N1 I A 10000 19 10000 1 1 18 1 10000
B 110000 346 10000 1 1 172 1 110000
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
INDEX: T1_N2
............
T1_N2 I A 10000 19 10000 1 1 18 1 10000
B 110000 346 10000 1 1 172 1 110000
###############################################################################
1 개의 행이 선택되었습니다.
경 과: 00:00:00.04
Query Invalidation
- 오라클 9i에서는 통계정보가 변경되면 관련된 모든 Query가 invalidation된다.
- 오라클10g에서는 이 문제를 해결하기 위해서 Query Invalidation을 일정시간에 걸쳐 골고루 배분한다.
dbms_stats에서 통계정보를 변경하는 모든 procedure는 No_Invalidation 파라미터를 갖는다.
- TRUE : Hard Parse가 발생하는 시점에 통계정보가 반영된다. 즉, shared pool에서 해당 쿼리가 age out되지 않는다면 통계정보가 반영되지 않는 것을 의미한다.
- FALSE : 통계정보가 변경되는 즉시 Invalidation이 이루어진다.
- AUTO_INVALIDATE : _OPTIMZER_INVALIDATION_PERIOD 파라미터(Default 5시간=18000초)에 결정된 시간에 걸쳐 관련된 쿼리들이 골고루 invalidation된다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3895
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.