View Source

h1. 목차
[#I. 통계정보 비교하기]
[#II. 통계정보 Backup/Recovery]
[#III. Query Invalidation]

h1. 통계정보관리
h2. 통계정보 비교하기
h3.통계정보전과 후를 비교하는 방법
# 통계정보를 저장할 테이블 생성
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'));
{code:sql}
----------------------------------------------------------------------------------
새로운 통계정보를 생성하는 것은 기존 통계이후 새로운 데이터가 쌓여 있는것 이므로 데이터를 생성한다.
----------------------------------------------------------------------------------
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
{code}


h3. 통계정보 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);

h3. 자동 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);

{code:sql}
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
{code}

h3. 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된다.