통계정보관리

통계정보 비교하기

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'));


----------------------------------------------------------------------------------
새로운 통계정보를 생성하는 것은 기존 통계이후 새로운 데이터가 쌓여 있는것 이므로 데이터를 생성한다.
----------------------------------------------------------------------------------
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된다.

통계 정보 관리 (by jongmali) [2009.04.30]

목차

통계정보관리

통계정보 비교하기

통계정보 Backup/Recovery

자동 Backup/Recovery (10g)

Query Invalidation