Optimizing Oracle Optimizer (2009년)
통계 정보 관리 0 0 52,221

by 구루비스터디 dbms_stats 통계정보 [2018.07.14]


  1. 통계정보관리
    1. 통계정보 비교하기


통계정보관리

통계정보 비교하기

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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입