by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

통계 정보 관리




목차

I. 통계정보 비교하기
II. 통계정보 Backup/Recovery
III. Query Invalidation

통계정보관리

통계정보 비교하기

통계정보전과 후를 비교하는 방법

  1. 통계정보를 저장할 테이블 생성
    exec dbms_stats.create_stat_table('woong', 't1_stat');
  2. 통계정보 export
    exec dbms_stats.export_table_stats('woong', 't1',null,'t1_stat');
  3. 새로운 통계정보 생성
    exec dbms_stats.gather_table_stats('woong', 't1', method_opt=>'for columns c1 size 254');
  4. 통계정보 비교
    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 파라미터를 갖는다.

  1. TRUE : Hard Parse가 발생하는 시점에 통계정보가 반영된다.
    즉, shared pool에서 해당 쿼리가 age out되지 않는다면 통계정보가 반영되지 않는 것을 의미한다.
  2. FALSE : 통계정보가 변경되는 즉시 Invalidation이 이루어진다.
  3. AUTO_INVALIDATE : _OPTIMZER_INVALIDATION_PERIOD 파라미터(Default 5시간=18000초)에 결정된 시간에 걸쳐 관련된 쿼리들이 골고루 invalidation된다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.