Oracle 노하우 및 팁

통계 정보 생성 GATHER_TABLE_STATS 0 4 27,370

by 타락천사 [타락천사] [2009.02.27 13:58:45]

통계 정보 생성 GATHER_TABLE_STATS
 
 
A.GATHER_TABLE_STATS Procedure
 
1. 개요
: Table,Column, 그리고 index 에 대한 통계 정보를 수집 하게 하는Procedure
 
2. Syntax
:DBMS_STATS.GATHER_TABLE_STATS (<?xml:namespace prefix = o />
   Ownname VARCHAR2,
   Tabname  VARCHAR2,
   Partname  VARCHAR2 DEFAULT NULL,
   Estimate_percent NUMBER DEFAULTto_estimate_percent_type
                                              (get_param(‘ESTIMATE_PERCENT’)),
   block_sample BOOLEAN DEFAULT FALSE,
   method_opt  VARCHAR2 DEFAULT get_param(‘METHOD_OPT’),
   degree       NUMBER DEFAULT to_degree_type(get_param(‘DEGREE’)),
   granularity     VARCHAR2 DEFAULT GET_PARAM(‘GRANULARITY’),
   cascade      BOOLEAN DEFAULT to_cascade_type(get_param(‘CASCADE’))
   stattab         VARCHAR2 DEFAULT NULL,
   statid          VARCHAR2 DEFAULT NULL,
  statown        VARCHAR2 DEFAULTNULL,
   no_invalidate   BOOLEAN DEFAULT
to_no_invalidate_type(get_param(‘NO_INVALIDATE’)),
   force           BOOLEAN DEFAULT FALSE );
 
3. Parameter 설명
: dbms_stats.set_param 에 의해서 디폴트 파라미터 설정 변경이 가능하다.
  [ 가능한 값은
   CASCADE,  DEGREE,  ESTIMATE_PERCENT,  METHOD_OPT,  NO_INVALIDATE,   GRANULARITY,
   ==> 이상은 수동 통계정보 생성 시에 저정을 하지 않았을 때 적용되는 Default 값에 영향을 미치고
   AUTOSTATS_TARGET [ AUTO - Oracle이 자동으로 대상 Object 결정,
                                      ALL - 대상 시스템의 모든 Objects
                                      ORACLE - SYS/SYSTEM OBJECT 만 ]
   ==> 자동 통계정보(GATHER_STATS_JOB) 시에만 영향을 미친다.
 
Default 값 확인
SYS>select dbms_stats.get_param(’method_opt’) from dual ;
DBMS_STATS.GET_PARAM(’METHOD_OPT’)
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
 
Default 값 변경
SYS>execute dbms_stats.set_param(’method_opt’,’FOR ALL COLUMNS SIZE 1’) ;
PL/SQL procedure successfully completed.
 
변경된 Default 값 확인
SYS>select dbms_stats.get_param(’method_opt’) from dual ;
DBMS_STATS.GET_PARAM(’METHOD_OPT’)
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1
 
Parameter
Description
Ownname
분석할 테이블 소유자
tabname
테이블 이름
partname
파티션 이름, 지정 하지 않으면 NULL 값
Estimate_percent
분석할 Row의 Percentage, NULL 이면 Compute(Row 전체)
유효값은 1/1000000 ~ 100
디폴트로, DBMS_STATS.AUTO_SAMPLE_SIZE 에 의해서 최적의 값을 결정
Block_sample
random block sampling or random row sampling 결정
random block sampling 이 좀더 효과적이다.
데이터의 블록 별 분포도가 안좋을 시에는 부적절한 정보 생성
디폴트 값이 False로, random row sampling 을 수행한다.
Method_opt
Histogram 생성시 사용하는 옵션
l        FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ]
l        FOR COLUMN [ size clause ] column | attribute [size clause]
                  [, column|attribute [ size clause ]…]
 Size_clause := SIZE { integer | REPEAT | AUTO | SKEWONLY }
n        Integer : Histogram Bucket 수, Max 는 1,254
n        REPEAT : 이미 Histogram 이 있는 칼럼에 대해서만 생성
n         AUTO : 데이터 분산도와 칼럼 부하 정도에 따라서 생성 결정
n         SKEWONLY : 데이터 분산도에 따라서 생성 결정
디폴트 값은 FOR ALL COLUMNS SIZE AUTO 이다.
즉, Histogram 의 생성여부를 Oracle 이 알아서 판단하게 된다.
이 경우
EX) method_opt => FOR ALL COLUMNS SIZE 1
    모든 컬럼에 대해서 Histogram bucket 의 수를 1로 한다.
    즉, 컬럼 내에 존재하는 여러 가지 값들의 cardinality 는 모두 동일한
     값으로 간주한다. ( histogram 을 사용하지 않는다.)
     이것은 where 조건에 들어오는 특정 컬럼에 대한 값의 변화에 따라서
      PLAN 이 변경될 가능성을 없애고자 함이다.
FOR ALL COLUMNS SIZE 1 로 설정하여 Histogram 정보가 생성되지
않도록 조치 한다.
degree
병렬처리 정도
디폴트 값은 NULL 이고, CREATE TABLE, ALTER TABLE 시
설정된 DEGREE 값에 의해 정해진다.
AUTO_DEGREE 값은 병렬처리 정도를 자동으로 결정한다.
이것은 1 or DEFAULT_DEGREE [ Object Size 와 CPU Count 에 의해 결정 ]
granularity
Parition table 에 대한 분석시 사용
‘ALL’ – Global, Partition, Subpartition 통계정보 수집 – Parition Table 대상
‘AUTO’ – 디폴트 값으로 ,Partition Type 에 따라서 결정 – 일반 Table 대상
‘DEFAULT’ – Global, Partition 통계정보 수집, Old Version 과 호환을 위해 사용
‘GLOBAL’ - Global 통계정보 수집
‘GLOBAL AND PARTITION’ – SubPartition 에 대한 통계정보는 수집되지 않는다.
‘PARTITION’ – Partition 통계정보 수집
‘SUBPARTITION’ – SubPartition 통계정보 수집
cascade
대상 테이블의 인덱스에 대한 통계수집 여부
인덱스 통계정보는 병렬처리가 불가능하다.
TRUE – 대상 테이블에 관련된 index 에 대해서 통계정보 수집
stattab
통계수집을 통한 기존 통계정보 Update 전에,
기존에 존재하는 통계정보를 저장할 User Stat Table 을 지정
statid
Stattab 와 연관된 구분자 값
statown
Stattab 에 지정한 User Stat Table 의 소유자가 다를 경우 지정
no_invalidate
의존적인 Cursor를 Invalidate 할지 , 안할지 결정
True – 관련된 Cursor 를 invalidate 하지 않는다.
False – 디폴트 값으로, 관련된 Cursor 를 Invalidate 한다.
Default 로 DBMS_STATS.AUTO_INVALIDATE 값이고,
의미는 DBMS 가 의존적 Cursor 를  언제 invalidate 할지 자동으로 결정
이때 작용하는 Parameter는 _OPTIMIZER_INVALIDATION_PERIOD 이고,
Default 롤 18000 초(5시간) 이다.
 즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에  해당 Cursor가 실행될 때 invalidation이 발생한다.
이것을 Auto Invalidation이라고 부른다.
일정 시간에 걸쳐 랜덤하게 Cursor를 Invalidation함으로써 특정 시점에
Hard Parse가  한꺼번에 몰리는 현상을 피할 수 있다.
force
Lock 걸린 Table 에 대해서도 강제로 통계정보 생성
 
 
예제 ) 참조– 메타링크 (일반 테이블- 237537.1, 파티션 테이블 - 237538.1 )
Cascade => TRUE
è 인덱스에 대한 통계정보도수집하라.
Cascade => FALSE
è 인덱스에 대한 통계정보도수집하라.
method_opt =>’FOR ALL COLUMNS SIZE 1’
 è 칼럼(High and Low Column Value)에 대한 통계정보도 수집하라.
method_opt =>’FOR COLUMNS’
 è컬럼에 대한통계정보를 수집하지 마라
 
가 ) 일반 테이블
SQL> show user
USER is"SYS"
1.  SCOTT의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,
테이블, 칼럼(Highand Low Column Value), 연관 인덱스의
통계정보를 생성한다.( COMPUTE STATISTICS )
SQL> execdbms_stats.gather_table_stats(ownname => ’SCOTT’,
tabname => ’BIG_TABLE’, cascade =>TRUE,
method_opt => ’FOR ALL COLUMNS SIZE 1’);
PL/SQL proceduresuccessfully completed.
 
2.      SCOTT 의 BIG_TABLE 의15% Row 를 가지고,
테이블, 칼럼, 연관인덱스의
통계정보를 생성한다. ( SAMPLE 15 PERCENT )
 
SQL> execdbms_stats.gather_table_stats(ownname => ’SCOTT’,
 tabname=> ’BIG_TABLE’, cascade => TRUE, estimate_percent => 15) ;
 
PL/SQL proceduresuccessfully completed.
 
3. SCOTT 의 BIG_TABLE 의 의 전체 테이블과 모드 인덱스를 가지고,
테이블의통계정보를 수집하라. 인덱스와 칼럼에 대한 통계정보는 제외
 
SQL> execdbms_stats.gather_table_stats(ownname => ’SCOTT’,
 tabname=> ’BIG_TABLE’, cascade => FALSE, method_opt => ’FOR COLUMNS’);
 
PL/SQL proceduresuccessfully completed.
 
4. SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,
테이블과인덱스에 대한 통계정보를 수집하라. 칼럼에 대한 통계정보는 제외
 
SQL> execdbms_stats.gather_table_stats(ownname => ’SCOTT’,
tabname => ’BIG_TABLE’, cascade => TRUE, method_opt =>’FOR COLUMNS’);
 
PL/SQL proceduresuccessfully completed.
 
5.    SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,
테이블과칼럼(No Histogram) 그리고 인덱스에 대한 통계정보를 수집하라.
잠시 후에
인덱스 칼럼들의 Histogram 통계정보를 수집하라.
SQL> execdbms_stats.gather_table_stats(ownname => ’SCOTT’,
tabname => ’BIG_TABLE’, cascade => TRUE) ;
PL/SQL proceduresuccessfully completed.
잠시 후에..
SQL> exec dbms_stats.gather_table_stats(ownname =>’SCOTT’,
            tabname => ’BIG_TABLE’, cascade=> TRUE,
method_opt => ’FOR ALL INDEXED COLUMNSSIZE 1’);
PL/SQL proceduresuccessfully completed.
 
6. SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,
   테이블과 인덱스칼럼(Only High and Low )에 대한 통계정보를 수집하라
   인덱스에 대한 통계정보는수집하지 마라.
 
SQL> execdbms_stats.gather_table_stats(ownname => ’SCOTT’,
tabname => ’BIG_TABLE’, cascade =>FALSE,
method_opt => ’FOR ALL INDEXED COLUMNSSIZE 1’);
 
PL/SQL proceduresuccessfully completed.
 
 
나.) PartitionTable 의 경우
    추가적으로 granularity 정보를 ‘ALL’,’AUTO’,’PARITION’,
’GLOBAL AND PARTITION,’GLOBAL’,’SUBPARTITION’을 통해서
통계수집 대상 Table Segment 를 선정 가능하다.
 
 
참고 ] LOCK VS DBMS_STATS.GATHER_TABLE_STATS
        : DML 이 LOCK 이 발생 하여도 GATHER_TABLE_STATS 는 정상적으로 진행된다.
 SCOTT10> begin
    for i in 1001 .. 5000 loop
    insert into check_lock values ( i , i , ’lock’);
    end loop ;
    end ;
     /
 PL/SQL procedure successfully completed.

 SYS>@check_user_lock.sql
 Enter value for user_name: scott10
 old  46: and b.username =upper(’&USER_NAME’)
 new  46: and b.username =upper(’scott10’)

 USERNAME    SID LOCK_TYPE       MODE_HELD   MODE_REQUE LOCK_ID1 LOCK_ID2
 ---------- ---- --------------- ----------- ---------- -------- --------
 SCOTT10     151 DML             Row-X (SX)  None       51782    0
 SCOTT10     151 Transaction     Exclusive   None       131077   307


 SYS>execute dbms_stats.gather_table_stats(ownname =>’SCOTT10’,tabname => ’CHECK_LOCK’);
 PL/SQL procedure successfully completed.
 ==> DML LOCK 과는 무관하게 진행 된다.
 
 
 
 
    
by 강정식 [2009.02.27 16:12:54]
좋은정보 감사염 ^^ 제 블로그에 퍼가염
by 숨표나라 [2009.07.31 08:35:30]
타락천사님~~^^ ㅋㅋ 여기서 활동 시작하셨나봐요 ^^ㅋ
by 꼬챙이 [2009.07.31 08:43:52]
타락천사님 여기서 활동 오래 전부터 하셨답니다~
by 동크 [2012.11.24 01:09:05]
좋은 글 감사드립니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입