2009/03/12 18:43

[펌] 10g 자동화 통계정보 수집에 관하여



1. 대량의 데이터 로드 작업 수행후에는 DBA가 작업 후 바로 통계작업을 실시해야 최신의 통계정보를 유지할 수 있다.

(바로 통계작업을 해도 바로 적용되지 않으니 안심해도 된다. http://blog.naver.com/ukja/120045329315 참조하삼)

 

2. 당연하겠지만 external table은 통계정보 대상이 아니다.

 

##### 통계 생성이 필요한 부분은 크게 4가지로 구분할 수 있다. #####

#####                    1.System Statistics                                #####

#####                    2.Fixed Objects Statistics                       #####

#####                    3.Dictionary Statistics                            #####

#####                    4.User Table Statistics                          #####

 

3. 시스템 통계는 자동화 되지 않는다. DBA가 직접 수행해야 한다.

 시스템 통계란 system hardware의 I/O, CPU의 특성을 분석하여 optimizer가 임의의 SQL에 대한 실행 계획을 수립 할 때

 이를 기반으로 계산하기 때문에 optimizer가 더욱더 똑똑해 질 수 있는 기반을 제공한다.

 예)  exec dbms_stats.gather_system_stats;

시스템 통계의 사용방법에 대해서는 다음 글을 참고하기 바랍니다. http://blog.naver.com/sungeunn/120050297742

 

4. fixed object에 대한 통계수집도 자동화되지 않는다.

   (v$view와 같은 dynamic performance view는 자동화 대상이 아니다.)

   Fixed Objects Statistics 무엇인가?

   fixed view (x$ tables) 에 대한 통계를 뜻하며 database의 activity를 기록하게 되기때문에 database가 일상적인 운영 상태일때

   fixed view (x$ tables) 에 대한 통계를 생성하는 것이 좋습니다. 보통 V$ view를 질의하는 사용자 query에  필요함

 

  * fixed object 통계 생성방법

  1) fixed object 통계정보를 저장 & 관리할 table을 생성한다.

  exec dbms_stats.create_stat_table(owname=>'SYSTEM',stattab=>'mystats');

 

  2) fixed objects 통계를 수집한다.

  exec dbms_stats.gather_fixed_objects_stats(stattab=>'mystats',statid=>'WEEK',statown=>'SYSTEM');

 

  3) fixed objects 통계정보를 load한다.

  exec dbms_stats.import_fixed_objects_stats(stattab=>'mystats',statid=>'WEEK');

 

5. Dictionary Statistics

   sys, system schema의 object들의 통계를 수집하는 것이다.

 

  * Dictionary Statistics 통계 생성방법

  1) Dictionary 통계정보를 저장 & 관리할 table을 생성한다.

  exec dbms_stats.create_stat_table(owname=>'SYSTEM',stattab=>'mystats');

 

  2) Dictionary 통계를 수집한다.

  exec dbms_stats.gather_dictionary_stats(stattab=>'mystats',statid=>'DAY',statown=>'SYSTEM');

 

  3) Dictionary 통계정보를 load한다.

  exec dbms_stats.import_dictionary_stats(stattab=>'mystats',statid=>'DAY');

 

6. User Table Statistics

   사용자의 Object에 대해 통계를 수집하는 것으로

   User Table Statistics는 10g의 GATHER_STATS_JOB을 통해 자동으로 통계 수집이 된다.

   요즘 현업의 추세는 자동화된 통계를 disable 시키고 있다.

 

7. 대량의 데이터를  insert하고 delete하는 등등 항상 full table scan이 필요한 테이블 등에 대해서는

   자동으로 table통계를 수집하는것이 오히려 악영향을 끼친다.

   때문에 이러한 테이블들은 exec dbms_stats.lock_table_stats('SCOTT','EMP'); 이런 프로시져로... locking을 하여

   자동 통계수집 대상 테이블에서 제외 할 수 있다.

 

   * 테이블 및 사용자별 자동 통계 중지하기

   EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');
   EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS ('owner name');

 

   * LOCK된 table확인하기

   SELECT owner, table_name, stattype_locked
   FROM dba_tab_statistics
   where stattype_locked is not null;

 

8. 10g부터 데이터 변동을 추정하는 table 모니터링은 자동화 되었다.

    alter table과 같은 명령어로 table 모니터링을 제어 할 수 있지만.. 이것은 명령만 유효할뿐..

    실제 내부적으로는 아무런 영향을 주지 않는다.

    Note:295249.1 <--- 참조하세요^^

 

9. 10g의 자동화된 통계수집 기능 disable 하는 방법

    gather_stats_job을 disable 시키고 statistics_level 파라메터 값을 basic으로 바꾸면 완벽하게 자동통계 기능을 끄는것이다.

 

    * 자동화 통계수집 기능 disable 하는 방법

    SQL> exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

   

    * 자동화 통계 수집 기능이 Disable되었는지 확인한다.

    col SCHEDULE_NAME for a40
    select job_name, enabled, SCHEDULE_NAME, STATE from dba_SCHEDULER_JOBs ;

 

    JOB_NAME                         ENABL   SCHEDULE_NAME                       STATE
------------------------------ ----- ------------------------------      -------------------
AUTO_SPACE_ADVISOR_JOB     TRUE   MAINTENANCE_WINDOW_GROUP     SCHEDULED
GATHER_STATS_JOB               FALSE  MAINTENANCE_WINDOW_GROUP      DISABLED
FGR$AUTOPURGE_JOB            FALSE                                                       DISABLED
PURGE_LOG                             TRUE  DAILY_PURGE_SCHEDULE               SCHEDULED

 

10. 통계정보를 수집할때 dbms_stats로 수집을 하면 오라클은 그 정보들의 미래의 사용 가능성을 염두해두고

   old_version으로 관리를 한다. 이런것들은 나중에 필요하면 dbms_stats.restore_*_stats로 과거 통계를 복원 할 수 있다.

   그러나 analyze command로 통계를 수집하면 old version으로 보존되지 않는다.

   통계정보 유지기간 확인및 설정은 다음과 같이 한다.

 

   유지기간 확인

   select dbms_stats.get_stats_history_retention from dual;

   유지기간 변경

   exec dbms_stats.alter_stats_history_retention(61);


Note:236935.1 <--- analyze와 dbms_stats의 차이점 참조하세요^^

 

11. 자동화된 통계정보는 AWR에 저장되며 AWR은 SYSAUX에 위치한다.

 

12. undo와 관련하여 AWR에 저장된 통계정보를 통해 오라클 스스로 얼마나 많은 수의 undo segment를 온라인 할것인지

     바로 결정할 수 있기때문에 Fast Ramp-Up이 가능하다.

 

13. 오라클 10g가 취합한 통계들은 SGA에 저장되어 v$view로 접근할 수 있으며 이 통계들은 주기적으로

     10g의 새로운 background processes인 MMON에 의해 스냅샷 형태로 AWR에 저장된다.

     기본 주기는 60분마다 스냅샷이 생성된다.

 

14. AWR 리포트가 보고 싶으면 $ORACLE_HOME/rdbms/admin/awrrpt.sql 을 돌리면 된다.

 

dbms_stats에 대해 더 알기 원하시면 다음 글을 참고 하세요

http://blog.naver.com/sungeunn/120051614586

http://blog.naver.com/sungeunn/120051616622

 

요즘 현업에서의 일반적인 추세는 10g의 자동화된 통계수집 기능을 사용하지 않는다.

이런 편리한 기능을 쓰지 않는 가장 큰 이유는 자동화 통계수집 기능을 이용하면 통계들을 컨트롤 하기가 어려워지기 때문이다. 많은 고수들이 자동화된 통계 수집 기능을 disable 할것을 권고하고 있다.



Trackback 0 Comment 0
2009/03/12 15:50

[펌] dbms_stats.auto_invalidate의 작동 방식 - Oracle 10g




dbms_stats를 이용해 통계 정보를 수집한 후 갑자기 hard parse가 몰리면서 library cache pin 경합으로 인해 시스템 성능 저하 현상이 발생하는 경우가 많다.

다음과 같은 상황에서 이런 현상이 발생한다.
- 통계 정보 수집으로 통계 정보가 변경된다.
- 통계 정보가 변경되면 관련된 SQL Cursor들이 invalidate된다.
- SQL Cursor가 invalidate되면 다음 번 Access때 hard parse가 발생한다.
- hard parse시에는 LCO에 대해 library cache pin을 exclusive모드로 획득한다.
- hard parse가 진행 중인 LCO를 실행하려는 다른 session들은 library cache pin 이벤트를 대기한다.

즉, 통계 정보 갱신에 의한 Cursor invalidation으로 인해 hard parse storm이 발생하고 이로 인해 성능 문제가 발생한다.

9i부터는 통계 정보 수집시 no_invalidate 옵션으로 Cursor invalidation을 제어할수 있다.
- no_invalidate == false: 통계 정보 갱신 후 관련된 SQL Cursor들을 즉시 invalidate한다. 9i에서의 기본값이다.
- no_invalidate == true: 통계 정보 갱신 후 관련된 SQL Cursor들을 invalidate하지 않는다. SQL Cursor들이 Shared Pool에서 age-out 된 후 reaload될 때 갱신된 통계 정보가 사용된다.

위 의 내용을 보면 알겠지만, no_invalidate 옵션은 참으로 애매모호한 옵션이다. false로 지정하면 hard parse storm이 발생해서 문제가 생길 것이고, true로 지정하면 갱신된 통계 정보가 반영되지 않으므로 곤란하다.

이런 고민을 해결하기 위해 Oracle 10g에서 dbms_stats.auto_invalidate(실제 값은 Null) 옵션이 추가되었다.
- no_invalidate == dbms_stats.auto_invalidate: 통 계 정보 갱신 후, 통계 정보 반영을 일정 시간 지연해서 적용한다. 이 시간 값은 _optimizer_invalidation_period 파라미터로 결정된다. 기본값이 18000(초)로 5시간이다. 즉 통계 정보 갱신 후 5시간이 지난 시점에 Cursor가 Access되면 그 때 통계 정보를 반영한다. 10g에서는 auto_invalidate가 기본값이다.

따 라서 10g에서는 통계 정보를 수집해도 즉각 반영되지 않고, 자연스럽게 hard parse storm도 피할 수 있다. 대신 일정 시간 이후 반영이 되므로 일정 목적을 달성할 수 있다. 이런에  현상 때문에 Oracle 10g를 처음 운영하는 환경에서 오해들이 있었다. "왜 통계 정보를 수집했는데 반영이 안돼? 버그 아냐"... 다행히 버그가 아니고 원래 그렇게 설계가 된 것이다.

만일, auto_invalidate를 사용하면서 특정 SQL Cursor는 즉시 invalidation하고 싶다면 어떻게 해야할까?
가장 간단한 방법은 alter system flush shared_pool을 이용해서 모든 Cursor를 invalidation시키는 것이다.
더 좋은 방법은 alter session set "_optimizer_invalidation_period" = 1; 과 같은 값을 적용해서 가능한 빨리 invalidation을 시키는 것이다.

통계 정보 수집시 SQL Cursor의 invalidation 문제는 지금까지 문서화가 잘 안되어 있어서 많은 오해들이 있었다. 이 글이 이런 오해를 푸는데 도움이 되었으면 한다.

PS)
auto_invalidate 옵션으로 통계 정보 갱신 후 일정 시간이 지나 SQL Cursor를 새로 생성할 시점이 오면 Oracle은 어떻게 통계 정보를 반영할까?
기 존의 SQL Cursor를 invalidate시키는 것이 아니라 새로운 Child Cursor를 생성한다. 실제 로 테스트를 해보면 새로운 Child Cursor가 생기고 v$sql_shared_cursor.roll_invalid_mismatch가 발생한 것으로 관찰된다.

참조)
http://wiki.ex-em.com/index.php/OPTIMIZER_INVALIDATION_PERIOD

Trackback 0 Comment 0
2009/03/11 14:20

[펌] 통계 정보 생성 GATHER_TABLE_STATS


출처 : http://www.oracleclub.com/article/23928
        http://blog.naver.com/xsoft?Redirect=Log&logNo=150043500269
 
 
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_validate
의존적인 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 과는 무관하게 진행 된다.

Trackback 0 Comment 0
2009/03/11 14:19

[펌] 통계정보의 이해


출처 : http://www.oracleclub.com/article/23893

[ 출처 ]
1. 10g Optimizer개념 및 통계치 생성 방법,SQL Tuning 방법
http://www.dbguide.net/club/board/download.jsp?maskname=274&fileName=10g+Optimizer+to+public.pdf

2.  10g 자동화 통계정보 수집에 관하여... [2008/05/11 Update]
http://blog.naver.com/sungeunn/120051268815

3.  어떤 STATISTICS_LEVEL 을 사용할 것인가 ?
http://cafe.naver.com/prodba/9293

4. 메타링크
  4.1 Two types of automatic statistics collected in 10g [ 559029.1  ]
  4.2 How to Disable Automatic Statistics Collection in 10G ? [ 311836.1 ]
5. 10G References [ STATISTICS_LEVEL ]
  http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#REFRN10214

 [ 결론 ]
1. System 통계정보는 따로 생성 하지 않는다.
    어느 시간이 최적인지 모른다. DBA 가 알수 있으면 정기적으로 생성해서 활용한다.
2. 자동 통계정보 수집(CBO) 는 disable 하고, 수동으로 통계정보를 생성한다.
   운영 중에 통계정보 생성으로 인해 업무의 성능 저하를 사전에 예방하자.
3. STATISTICS_LEVEL = TYPICAL 로 유지 하자

4. 자동 통계정보 수집 대상은 User Objects,  Sys/System Object 가 대상이다.

  
[ 요약 ]

  대상  설명   TATISTICS_LEVEL=BASIC  STATISTICS_LEVEL=TYPICAL
 System Statistics  System 성능 ( CPU, DISK )    
 Fixed Objects Statistics  DBMS 성능 ( X$, V$)    
 Dictionary Statistics  SYS/SYSTEM USER TABLE  자동 수집되지 않는다.  자동 수집된다.
 User Table Statistics
 일반 유저 TABLE  자동 수집되지 않는다  자동 수집된다.
 

통계정보의 종류
: 크게 4가지로 구분할 수 있다.
[ 1.System Statistics ]
: 개요 - System Statistics 는 System Hardware 의 I/O, CPU 의 특성을 분석하여
         Optimizer 가 CPU Costing 을 계산할 때 사용하는 정보로써,
         이를 기반으로 Optimizer 가 임의의 SQL에 대한 실행 계획을 수립할 때
         이를 기반으로 계산하게 된다.
  수행주기 - 초기 1회
             시스템 자원의 변경이 생겼을 경우 ( Memory, CPU, I/O 등 )
  9i - 처음 소개된 개념이고, DBA가 수동으로 수집 하지 않으면 기본적으로
       존재하지 않는 정보이다. 기본적으로 I/O Model로 비용산정
       System Statistics 정보가 있으면 Optimizer 가 비용 산정을 CPU Model 로 하고,
       System Statistics 정보가 없다면 Optimizer 가 비용 산정을 I/O Model 로 한다.
  10G - System Statistics 정보를 수집 하지 않는다면 Noworkload System Statistics 가
       사용된다. 10G 에서는 Optimizer 가 비용 산정을 CPU Model 로 한다. [ Default ]
       [ CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 로 구성된다. ]
       수동으로 System Statistics 수집 시에는 Workload System Statistics 라고 한다.
       결론적으로 운영 시스템의 최적의 System Statistics 를 생성하여, 적절히
       적용 하여 사용하는 것이 최선이나, 보통은 NoWorkload System Statistics 를
       그대로 사용한다.
 주의사항 :
RAC 에서 NODE 가 서로 같은 시스템 사양을 같지 않을 경우에는
       System Statistics 를 Node 별로 나누어 관리 되지 않으므로 전체 System 의
       대표성을 가지는 Node 에서 수행을 한다.
       특히 위와 같은 결정을 하기 위해서는, 각각의 Node 별로 통계치를 생성해
       보고 비교해 본 후에 결정할 수 있다.
       노드의 사양이 동일한 경우 가장 일반적인 Node 에서 수행한다.  

실습
: 시스템 통계정보는 Optimizer 가 실행 계획 세움에 있어서 지대한 영향을
  미치므로, 항상 기존의 시스템 통계자료를 백업 후 진행 하자.
  OLTP 와 OLAP 성 통계정보를 생성하고, IMPORT 해보자

 
-- 1. 시스템 통계정보 확인
SELECT * FROM SYS.AUX_STATS$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33
SYSSTATS_INFO                  FLAGS                        0
SYSSTATS_MAIN                  CPUSPEEDNW                1489
SYSSTATS_MAIN                  IOSEEKTIM                   10
SYSSTATS_MAIN                  IOTFRSPEED                4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

-- 2. 기존 통계정보 백업 받을 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- 기존 통계정보 백업
SQL> execute DBMS_STATS.EXPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS
PL/SQL procedure successfully completed.
-- 백업 받은 시스템 통계정보 데이타 확인
SQL> SELECT STATID, C1, C2, C3 FROM ORIGIN ;
--  C1 = COMPLETED     --> 수집 완료


-- 3. 시스템 통계정보 수집
-- 사전에 JOB PROCESS 확인 ( 1보다 커야 한다.)
SQL> show parameters job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL> alter system set job_queue_processes = 5 ;
System altered.

SQL> show parameters job_queue
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     5

 

-- 4.  OLTP 성 시스템 통계정보 수집하기
-- OTLP용 시스템 통계정보 생성을 위한 통계정보 테이블 생성
-- OWNER, TABLE 이름, TABLESPACE 이름 순
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLTP’,’USERS’);
PL/SQL procedure successfully completed.
--  지금 부터 INTERVAL 로 지정된 시간 동안 시스템 통계정보를 생성 하라..
--  2분동안 시스템 통계정보를 수집 하라
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLTP’, STATID => ’OLTP’);
PL/SQL procedure successfully completed.
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP                 AUTOGATHERING                  02-24-2009 08:54               02-24-2009 08:56
OLTP
--  C1 = AUTOGATHERING --> 수집 중
--  C1 = COMPLETED     --> 수집 완료 로 변경된다. [ 2분 경과 후 ]

--  10분동안 시스템 통계정보를 수집 하라 [앞서 수행한 2분동안 통계정보는 Update 된다.  ]
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID => ’OLTP’);
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP                 AUTOGATHERING                  02-24-2009 08:56               02-24-2009 08:58
OLTP
--  C1 = AUTOGATHERING --> 수집 중

-- 시스템 통계정보 수집 중지 하기 [ gathering_mode=>’STOP’ ]
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’STOP’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID => ’OLTP’);

-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
--  C1 = AUTOGATHERING --> 수집 중
--  C1 = COMPLETED     --> 수집 완료 [ 강제로 중지 하여도 COMPLETED 로 나온다. ]

 

--  5. OLAP 성 시스템 통계정보 수집하기
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLAP’,’USERS’);
PL/SQL procedure successfully completed.
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLAP’, STATID => ’OLAP’);
SQL> SELECT STATID, C1, C2, C3 FROM OLAP ;

STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLAP                 COMPLETED                      02-24-2009 09:04               02-24-2009 09:06
OLAP
-- C1 => AUTOGATHERING -- 수집 중
-- C1 => COMPLETED     -- 수집 완료 로 변경된다.  [ 2분 경과 후 ]

 

-- 6. 생성한 OLAP_STATS 시스템 통계정보 IMPORT 하기
-- 기존 통계정보 삭제
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- 기존 통계정보를 삭제 하면,
-- SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 값만 DELETE 시점으로 변경된다.
SQL> select  * from sys.aux_stats$;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-24-2009 09:08
SYSSTATS_INFO                  DSTOP                          02-24-2009 09:08

-- OLTP 시스템 통계정보 IMPORT 하기
execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’OLTP’, STATID =>’OLTP’,STATOWN =>’SYS’);
-- Import 된 시스템 통계정보 확인
-- OLTP 시스템 통계정보를 수행한 시간으로 SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP
-- 값이 변경되어 진다.
SQL> select * from sys.aux_stats$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-24-2009 08:56
SYSSTATS_INFO                  DSTOP                          02-24-2009 08:58


-- 7. 다시 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33
--  SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 로 ORIGIN 으로 변경됨


-- 8. 수동으로 시스템 통계정보 생성하기 [ 파라미터 개별 설정 ]
-- 기존 시스템 통계정보 삭제 하기
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- CPUSPEED 설정
-- CPUSPEED : Wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’CPUSPEED’, PVALUE=>400);
PL/SQL procedure successfully completed.
-- CPUSPEED 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’CPUSPEED’;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN                  CPUSPEED                   400
-- SREADTIM 설정
-- SREADTIM : wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’SREADTIM’, PVALUE=>100);
PL/SQL procedure successfully completed.
-- SREADTIM 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’SREADTIM’;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN                  SREADTIM                   100

 

-- 9. 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 원복 결과 확인
SQL> select * from sys.aux_stats$ ;

SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33

 

[ 2.Fixed Objects Statistics ]
  개요 - 
Dynamic Performance View와 같이 fixed view(x$ tables)에 대한 통계치는
          Manual 한 Gathering 이 필요하다. 이 Fixed Objects Statistics 는
          Database 의 Activity 를 기록하게 되므로 database 가 일반적인 운영 상태
          일때 gathering  하여야 한다.
          일반적으로 Fixed Object Statistics 통계치는 V$ view 를 조회하는
          사용자 Query에 필요하다.
  수행주기 - 초기 1회
             추가적인 Application 이나 변경으로 동시 사용자 등의 변경 발생시
  주의사항 - 자동화 대상이 아니다. [ 자동으로 Fixed Object의 통계정보가 생성되지 않는다. ]
             RAC 에서는 아직 Fixed Objects Statistics 를 Instance 별로 구분하지
             않기 때문에, 가장 부하가 많은 Node(Instance)에서 통계치를 조사한다.

 
실습
: Fixed Object 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
  항상 기존의 Fixed Object 통계정보를 백업 후 진행 하자.
  OLTP 와 OLAP 성 Fixed Object 통계정보를 생성하고, IMPORT 해보자

 

-- 1. 기존의 Fixed Object 통계정보 백업 테이블 생성   
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_ORIGIN’,’USERS’);           
-- EXPORT_FIXED_OBJECTS_STATS 를 통한 백업 수행
SQL> execute DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’,STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

 

-- 2. 신규로 Fixed Object 통계정보 테이블 생성  
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_NEW’,’USERS’); 
-- 신규 Fixed Object 통계정보 수집
SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’);
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 13578
ORA-06512: at "SYS.DBMS_STATS", line 13892
ORA-06512: at "SYS.DBMS_STATS", line 14420
ORA-06512: at line 1

SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’FIX_NEW’);
PL/SQL procedure successfully completed.

 

-- 3. 기존 Fixed Object 통계정보 확인 하기
SQL>  select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA                       
X$KQFVI                       
X$KQFVT                       
X$KQFDT                       


-- 4. 신규 Fixed Object 통계정보 IMPORT
--  FIXED TABLE 의 통계정보 삭제
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_NEW’, STATID =>’FIX_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.


-- 5. 신규 Fixed Object 통계정보 확인 하기
SQL>  select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA                        20090224 09:50:34 09:50:34
X$KQFVI                        20090224 09:50:34 09:50:34
X$KQFVT                        20090224 09:50:34 09:50:34
X$KQFDT                        20090224 09:50:34 09:50:34
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.

 

6. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’, STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 복원된 정보 확인 하기
SQL > select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

 
[ 3.Dictionary Statistics ]
  개요 - DBMS_STATS.GATHER_DICTIONARY_STATS 를 이용하여 Sys, System Schema 의
        Object를 Gathering 한다. 이 procedure 는 또한 DRSYS 나 CTX user Schema의
        Object 도 함께 Gathering 한다.
  수행주기 - 초기 1회
             Database Object(사용자 Table, PL/SQl, User생성) 의 변경이 있는 경우
            
실습
: Dictionary 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
  항상 기존의 Dictionary 통계정보를 백업 후 진행 하자.
  OLTP 와 OLAP 성 Dictionary 통계정보를 생성하고, IMPORT 해보자

 

-- 1. 기존의 Dictionary 통계정보 통계정보 백업 테이블 생성   
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- EXPORT_DICTIONARY_STATS 를 통한 백업 수행
SQL>  execute DBMS_STATS.EXPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’,STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.


-- 2. 신규로 Dictionary 통계정보 테이블 생성  
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_NEW’,’USERS’); 
PL/SQL procedure successfully completed.
-- 신규 Dictionary 통계정보 수집
SQL> execute DBMS_STATS.GATHER_DICTIONARY_STATS(’SYS_NEW’);
PL/SQL procedure successfully completed.

 

3. 신규 Dictionary 통계정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2
....
X$LOGMNR_OBJ$                  20090224 10:07:20 10:07:20
X$LOGMNR_TABCOMPART$           20090224 10:07:20 10:07:20
X$LOGMNR_USER$                 20090224 10:07:20 10:07:20
SUMDELTA$
SDO_TOPO_DATA$
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.


4. Dictionary 통계정보 IMPORT 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_NEW’, STATID =>’SYS_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- IMPORT 된  Dictionary 통계정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2;
SRS$                           20081229 22:00:05 22:00:05
X$LOGMNR_ATTRIBUTE$            20090224 10:07:19 10:07:19
X$LOGMNR_COLTYPE$              20090224 10:07:19 10:07:19
X$LOGMNR_IND$                  20090224 10:07:19 10:07:19
X$LOGMNR_COL$                  20090224 10:07:19 10:07:19
X$LOGMNR_DICT$                 20090224 10:07:19 10:07:19
==> 테스트 에서는 실제로 Dictionary 통계정보를 가진 Table Count 가 오히려 줄어들었다.
      
5. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’, STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 복원된 정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2;

 

[ 4.User Table Statistics ]
: 사용자의 Object 에 대한 통계치 수집은 10G 에서 기본적인 GATHER_STATS_JOB 을
이용한다. 이 JOB 은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가
변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행한다.
또한 분석하는 순서 역시 우선순위 순으로 수행한다.
만약 Object 에 많은 Data 가 Load 되거나 변경이 있는 경우에는  Manual 하게
수행한다.
- 10G 에서 말하는 자동 통계정보 수집 기능(Automatic Statistics) 의 대상은
  1. AWR(Automactic Workload Repository)
   - 분석, Self-Tuing 및 일반적인 Tuning 을 목적으로 하는 자료로
     특정 시간 동안 데이타 베이스에서 발생한 여러 가지 상황 정보를 이른다.
     Wait Events, Latces, Enqueues, Cpu Consumption, SGA Compoenent, PGA 등에
     대한 자료 이다.
      ’STATISTICS_LEVEL’ 에 의해서 수집되는 자료의 LEVEL 이 결정된다.
     AWR 정보는 디폴트로 7일간 보관한다. [ SYSAUX TABLESPACE 에 ]
    
  2. CBO(Cost-Based Optimizer)
   - Database 의 Object 즉,  Application 및 Oracle Internal (Sys/System) 유저의
     Table, Index 에 대한 통계정보 수집
     이 수집된 정보는 Optimizer 가 수행계획 수립 시에 의해 사용된다.
     GATHER_STATS_JOB 에 의해서 수집 된다. [DB 생성시 자동 생성됨]
     Optimizer historical 통계정보는 디폴트로 31일간 보관한다.
     STATISTICS_LEVEL=BASIC 이면 CBO 통계정보가 수집되지 않는다.
        - Automatic Optimizer Statistics Collection
        - Object level Statistics  
     [ SYSAUX TABLESPACE 에 ]  
    
-- SYSAUX 사용 현황 파악 하기
 SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;
 1. SM/AWR 
  - AWR 정보 수집
  - AWR 정보 수집 옵션 확인
    SQL>  SELECT DBID, RETENTION, TOPNSQL  FROM DBA_HIST_WR_CONTROL ;
          DBID RETENTION                      TOPNSQL
    ---------- ------------------------------ ----------
    2466823093 +00007 00:00:00.0              DEFAULT
    보존 변경은 dbms_workload_repository.modify_snapshot_settings 를 통해서 가능
   
    -- AWR 정보 한달 보관주기로 변경 [ 60*24*31 = 44640 분 ]
    SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 44640 );
    PL/SQL procedure successfully completed.
    -- 변경되 AWR 정보 확인
    SQL> SELECT DBID, RETENTION, TOPNSQL  FROM DBA_HIST_WR_CONTROL ;
   
          DBID RETENTION                      TOPNSQL
    ---------- ------------------------------ ----------
    2466823093 +00031 00:00:00.0              DEFAULT

 2. SM/ADVISOR
    SQL Tuning Advisor, SQL Access Advisor, ADDM 이 사용하는 정보 저장소

 3. SM/OPTSTAT    
    - 구버전(Old) Optimizer 통계정보 저장소
    - SM/OPTSTAT 저장 기간 확인
      SQL> select dbms_stats.get_stats_history_retention from dual;
      GET_STATS_HISTORY_RETENTION
      ---------------------------
                               31
      ==> Default 로 31일
    -- 10일로 조절
    SQL> exec dbms_stats.alter_stats_history_retention(10);
    PL/SQL procedure successfully completed.

    SQL> select dbms_stats.get_stats_history_retention from dual;
    GET_STATS_HISTORY_RETENTION
    ---------------------------
                         10
    -- 원복
    SQL> exec dbms_stats.alter_stats_history_retention(31);
    PL/SQL procedure successfully completed.

  4. SM/OTHER
     - Alert History 등의 저장소


--  STATISTICS_LEVEL PARAMETER
    : Database 와 OS 의 통계정보의 수집 Level 을 제어 하는 파라미터
    1.Typical
      - Default, 일반적인 환경에 가장 적합
    2. ALL
      - typical + Timed OS Statistics + Plan Execution Statistics
    3. BASIC
      - 아래 기능을에 필요한 중요한 통계정보를 수집 할 수 없다.
        - AWR
        - ADDM
        - All Server-Generated Alerts
        - Automatic SGA Memory Management
        - Automatic Optimizer Statistics Collection
        - Object level Statistics
        등...
           
 
실습 : 여기서 말하는 자동 통계수집 이란 CBO 에 대한 것을 이른다.
     - 1. 자동 통계수집일정을 확인하고
     - 2. 자동 통계수집을 Disable 해보자
     - 3. 통계정보 백업 / 복구 하기 
     - 4. 특정 테이블 통계수집 중지 하기
 
-- 1.1 자동통계정보 수집 확인 하기
SQL >  select job_name, job_type, program_name, schedule_name, job_class
      from dba_scheduler_jobs
       where job_name =’GATHER_STATS_JOB’;
JOB_NAME             JOB_TYPE         PROGRAM_NAME         SCHEDULE_NAME                  JOB_CLASS
-------------------- ---------------- -------------------- ------------------------------ ------------------------------
GATHER_STATS_JOB                      GATHER_STATS_PROG    MAINTENANCE_WINDOW_GROUP       AUTO_TASKS_JOB_CLASS

-- 1.2 자동통계정보 수집 시 실행 되는 Program 확인
SQL> select program_Action from dba_scheduler_programs where program_name =’GATHER_STATS_PROG’;

PROGRAM_ACTION
--------------------------------------------------
dbms_stats.gather_database_stats_job_proc


--  1.3 자동통계정보 수집 시 스케줄 확인
SQL> select * from dba_scheduler_wingroup_members where window_group_name =’MAINTENANCE_WINDOW_GROUP’;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

 

--  1.4 자동통계정보 수집 시 스케줄 상세 확인
SQL> select window_name, repeat_interval, duration
     from dba_scheduler_windows
     where window_name in (’WEEKNIGHT_WINDOW’,’WEEKEND_WINDOW’);

WINDOW_NAME          REPEAT_INTERVAL                                                                  DURATION
-------------------- -------------------------------------------------------------------------------- --------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0            +000 08:00:00
WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                              +002 00:00:00

-- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행 된다.
-- 토요일 0시에 수행되어 이틀 동안 수행된다.

 

-- 2.1 자동통계정보 수집 중지
-- STATISTICS_LEVEL=BASIC 이면 자동통계정보 수집(CBO)
SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     SCHEDULED

SQL> exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     DISABLED

 

-- 2.2 자동통계정보 수집 재설정
SQL> exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     SCHEDULED

 

-- 3.1 통계정보 백업 / 복구 하기
-- 유저 테이블 통계정보 백업 받을 테이블 생성하기
SQL> execute dbms_stats.create_stat_table(’SYS’,’USER_STATS’,’USERS’);
PL/SQL procedure successfully completed.
-- SCOTT 유저 테이블 통계정보 백업 받기
SQL> execute dbms_stats.export_schema_stats(’SCOTT’,’USER_STATS’,’SCOTT’,’SYS’);
PL/SQL procedure successfully completed.
-- 백업된 SCOTT 유저의 통계정보 확인
SQL> select STATID, C1, C2, C4, D1 from  USER_STATS ;


-- 3.2 신규로 유저 테이블 통계정보 생성
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
     GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
     CASCADE => TRUE );
-- 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

 

-- 3.3 유저 테이블 통계정보 원복하기
SQL> exec dbms_stats.delete_schema_stats(’SCOTT’);
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.import_schema_stats(’SCOTT’,’USER_STATS’,’USER_STATS’,’SYS’);
PL/SQL procedure successfully completed.
 
-- 3.4 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

 

-- 4.1 특정 테이블 통계수집 중지 하기
-- 수동으로 통계정보 수집 하여 LAST_ANALYZED  Update 하기
SQL>  execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
     GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
      CASCADE => TRUE );
PL/SQL procedure successfully completed.
-- 4.2 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

-- 특정 테이블 통계정보 수집 막기
SQL> execute dbms_stats.lock_table_stats(’SCOTT’,’T1’);
PL/SQL procedure successfully completed.

-- 특정 테이블 통계정보 수집 막음 확인
SQL> SELECT owner, table_name, stattype_locked
     FROM dba_tab_statistics
     WHERE OWNER=’SCOTT’
     and stattype_locked is not null;
    
OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SCOTT                          T1                             ALL


-- 확인을 위해서 수동으로 통계정보 수집 하여 LAST_ANALYZED  Update 하기
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
> GRANULARITY =>’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
> CASCADE => TRUE );
PL/SQL procedure successfully completed.

SQL>  select OWNER,TABLE_NAME, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from  dba_tab_statistics
      WHERE OWNER=’SCOTT’
     and table_name in (’T1’,’EMP’,’DEPT’);

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ ------------------------------ --------------------------
SCOTT                          DEPT                           20090224 11:37:57 11:37:57
SCOTT                          EMP                            20090224 11:37:57 11:37:57
SCOTT                          T1                             20090224 11:32:53 11:32:53

==> Lock 되어진 T1 에 대해서는 테이블 통계정보가 생성되지 않았음을 확인


Trackback 0 Comment 0
2009/03/06 18:27

2008년 한국의 사회지표

통계청에서 제공하는 사회지표를 보니 재미있는 내용이 많은거 같다.

- 외국인과의 혼인비율이 11.1%나 차지한다. 2005년에는 13.6%까지 올라갔었다.

- 평균 초혼연령은 남성 31.1세, 여성 28.1세로 계속 증가추세

- 사망률 순위 : 자살로 인한 사망률이 1997년에 비해 거의 1.7배정도 는데 비해 운수사고로 인한 사망률이 반으로 떨어졌다. 사망원인 순위는 암-> 뇌혈관 질환 -> 심장질환 -> 자살 -> 당뇨병 순

 - 한국영화 관객점유율은 2006년 63.8을 피크로 감소추세. 2008년에는 42.1%

 - 범죄발생건수는 2008년 196만 6천건으로 전년에 비해 7.5%증가(절도, 상해의 증가가 큼)


Trackback 0 Comment 0
2009/03/05 11:56

[책] Optimizing Oracle Optimizer


http://wiki.oracleclub.com/pages/viewpage.action?pageId=4325453

*  DBMS_XPLAN

1) dbms_xplan.display
 - explain plan for    select * from t1;

 -  select * from table(dbms_xplan.display('plan_table', null, 'typical', null ));   
          -- 디폴트(dbms_xplan.display) 와 동일한 결과
 -  select * from table(dbms_xplan.display('plan_table', null, 'all'));  
          -- Query Block 정보, 추출하는 컬럼정보, /*+ qb_name(x) */ 힌트로 query block 명 조작가능
 -  select * from table(dbms_xplan.display('plan_table', null, 'outline'));   -- 필요한(사용된) 힌트를 나열
 -  select * from table(dbms_xplan.display('plan_table', null, 'advanced'));   -- all, outline 정보 같이 보여줌

2) dbms_xplan.display_cursor

 - 통계정보 생성(t1테이블)
    exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, no_invalidate=>false);

   : 인덱스가 있는 컬럼에 대해 histogram 생성하면서 통계생성
    exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all indexed columns size skewonly');

 - gather_plan_statistics 힌트 : 쿼리 수행시 예측 Row수와 실제 Row수를 기록한다.
  select /*+ gather _plan_statistics */ count(*) from t1 where c1 = 'Many2';

  - select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));   
     -- 현재 세션에 대한 최근 쿼리 결과조회 ( SQL ID, Child number, 옵션)
 
  - 테이블 정보조회
     select     a.table_name, a.num_rows, a.blocks, a.sample_size, a.last_analyzed,
       s.column_name, s.num_distinct, s.num_nulls, s.density, s.low_value, s.high_value, s.histogram
     from user_tab_statistics a, user_tab_cols s
     where a.table_name = 'T1'
     and a.table_name = s.table_name;

     select * from user_histograms
     where table_name = 'T1'
     order by column_name, endpoint_value;


* Index and CBO
 1) Clustering Factor
    - Index Scan의 Cost 계산
      Cost = Blevel + Leaf Blocks * index Selectivity + Clustering Factor * Table Selectivity + Adjusted CPU

   - index 생성시 sequence key 값을 사용하거나, 시간순으로 date형을 사용하면 clustering factor가 좋게(낮게) 나온다. clustering factor를 개선시키려면 테이블을 다시 생성(order by 로 reinsert)해야 함.




Trackback 0 Comment 0
2009/02/19 21:29

global temporary table

oracle 에 global temporary table이라는게 있다.
PLAN TABLE같은데 적용해놓으면 편할 듯 하다.

CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  OTHER_XML          CLOB
)
ON COMMIT PRESERVE ROWS
NOCACHE;

GRANT DELETE, INSERT, SELECT, UPDATE ON  PLAN_TABLE TO PUBLIC;


Oracle8.1에서는 session내에서 임시로 사용할 data들을 영구적인 segment형태가
아닌 temporary structure에서 관리할 수 있다.
이러한 Temporary Table들은 그 생성문장에 의해서 definition이 dictionary에
저장되고, 각 session에서 해당 table을 사용할 때마다 definition을 이용하여
memory에 table 구조를 생성하게 된다.


CHARACTERISTICS
---------------
1. data는 session private하다. (특정 session에서 사용하는 temporary table
data는 다른 session에서 access할 수 없다.)
2. CREATE GLOBAL TEMPORARY TABLE ...
ON COMMIT [DELETE|PRESERVE] ROWS ; 문을 이용하여 생성한다.
3. data의 유지기간은 transaction단위 또는 session단위이다.
'ON COMMIT DELETE ROWS'로 생성되었다면 transaction단위이며 이때 data는
commit이 되는 시점에 자동으로 제거된다.
'ON COMMIT PRESERVE ROWS'로 생성되었다면 session단위이며 이때 data는
해당 session이 종료되면서 사라진다.
default는 'ON COMMIT DELETE ROWS'이다.
4. table의 definition은 dictionary에 permanently 저장된다.
*_tables의 TEMPORARY, DURATION column이 temporary table과 관련이 있다.
TEMPORARY - 'Y' : temporary type table
'N' : permanent type table
DURATION - 'SYS$SESSION' : data의 유지기간이 session단위
'SYS$TRANSACTION' : data의 유지기간이 transaction단위
NULL : 해당 table은 temporary type이 아님
5. session간에 data에 대한 contention이 발생되지 않기 때문에 DML문에 대한
lock이 필요하지 않다.
6. 임시적으로만 관리되는 data이므로 DML문에 대해서 redo log를 발생시키지
않는다.
7. index, view, trigger를 생성하여 사용할 수 있다. Temporary Table의
column에 생성되는 index도 temporary type이다.
8. table의 definition은 export utility를 이용하여 export할 수 있다.
그러나 그 row들은 export의 대상이 될 수 없다.


RESTRICTIONS
------------
1. partitioned, index-organized, clustered table로 생성할 수 없다.
2. foreign key constraint를 설정할 수 없다.
3. nested table이나 varray type의 column은 포함할 수 없다.
4. 다음과 같은 LOB_storage_clause들은 지정할 수 없다. : TABLESPACE,
storage_clause, LOGGING 또는 NOLOGGING, MONITORING 또는 NOMONITORING,
또는 LOB_index_clause.
5. parallel DML이나 parallel query는 지원되지 않는다. (parallel hint는
무시될 것이며, table생성시 parallel clause를 지정하면 error를
return한다.)
6. storage나 tablespace는 지정할 수 없다.
7. 분산 transaction은 지원되지 않는다.


SAMPLE
------

----------------------------------------------------------------------
-- temporary type table을 생성하되 duration은 transaction단위로 한다.
----------------------------------------------------------------------
SQL> create global temporary table temp_tab
2 (col1 number, col2 char(10))
3 on commit delete rows ;


-----------------------------------------------------------------------
-- 생성된 table에 대한 정보를 조회한다. *_tables의 TEMPORARY, DURATION
-- column이 temporary type table과 관련이 있다.
-----------------------------------------------------------------------
SQL> select temporary, duration
2 from user_tables
3 where table_name = 'TEMP_TAB' ;

T DURATION
- ---------------
Y SYS$TRANSACTION


-----------------------------------------------------------------------
-- temporary table에 row insert
-----------------------------------------------------------------------
SQL> insert into temp_tab values (1, 'wookpark') ;

SQL> select * from temp_tab ;

COL1 COL2
--------- ----------
1 wookpark


-----------------------------------------------------------------------
-- transction을 commit한다.
-----------------------------------------------------------------------
SQL> commit ;
Commit complete.


-----------------------------------------------------------------------
-- duration이 transaction단위이기 때문에 commit을 수행하면 모든 data가
-- 사라진다.
-----------------------------------------------------------------------
SQL> select * from temp_tab ;
no rows selected

from oracle
Trackback 0 Comment 0
2009/02/18 11:41

[책] Practical OWI in Oracle 10g



 ppt형식으로 되어있는데, 생각보다 내용설명이 간결하면서 이해하기 쉽게 되어있다.

 - RAC환경에서 주로 발생하는 gc cr/current 류의 wait event 는 주로 Global Cache 동기화(Cache Fusion)와 관련하여 발생
   gc 는 read시, current는 변경시 발생


 - Sequence 의 nocache : nextval 호출시마다 Dictionary 변경필요함.
   cf) RAC의 경우 cache를 주면 cache를 소진할 동안은 상호 통신이 필요없다. 단 ordered 속성을 주면 SV락을 통한 동기화가 필요하다. 특별한 필요가 없으면 cache와 noorder 속성으로 만들자.

 - db file scattered read 이벤트관련(full scan)
   : Insert, Delete 가 빈번한 interface성, temp성 테이블의 경우 delete 이후 통계정보가 생성되었다면 full scan이 발생할 수 있다.(주의)

 - latch: cache buffers chains 이벤트
  : 주로 hot block이나 bad SQL에 의해 동일 블록에 대한 접근이 동시에 일어날때 발생
   cf) Bad SQL의 문제일 경우 parallel query 로 변경하는것도 검토필요하다. parallel query의 경우 SGA를 거치지 않기 때문에 버퍼캐쉬 경합 자체가 없다.(parallel query실행시 더티버퍼를 디스크에 쓰도록 Segement 체크포인트가 수행됨으로 성능문제 고려)

 - 오라클의 physical I/O
   : OS상에서의 캐쉬, 스토리지 캐쉬가 있기 때문에 모두가 디스크 I/O 라고 보기는 힘들다.

 -  Keep 버퍼풀 사용하기
     1) DB_KEEP_CACHE_SIZE = 352321536 또는 alter system set DB_KEEP_CACHE_SIZE = 300M;
     2) 인덱스를 keep 버퍼에 올리기
         alter index schema.index_name storage(buffer_pool keep);

  - 다이렉트 로그 인서트 ( insert /*+ append */ into t1 select * from t2)
    : high water 마크 이후부터 insert 작업을 함에 따라 테이블에 X모드의 TM락을 획득하고 작업함.
      (insert, update, delete 동시 실행 못함)

        



 






Trackback 0 Comment 0
2009/01/06 20:38

[책] 마지막 강의


카네기 멜론의 랜디 포시 교수의 마지막 강의..

- 아이들의 상상력, 자신감, 성취감을 위해 방안을 마음대로 그리게 하고, 자신의 새차 뒷자석에 콜라를 쏟어버리는 그의 행동
- 어릴적 꿈을 향해 끊임 없이 노력하고, 다른 사람들에게 베풀고자 함

- 헤드 페이크 :  쉬는 시간에 물을 향해 뛰는 아이들을 마구 야단치던 축구코치, 힘들때마다 생각하며 노력
     과정에 푹 빠져들때까지 배우는 사람으로 하여금 자신이 진정 배우는 것이 무엇인지 모르게 하는 속임수

- 장벽은 나를 막기 위해 있는 것이 아니라, 나보다 그것을 덜 갈망하는 사람들을 막기위해 있는 것이라고..








Trackback 0 Comment 0
2008/12/12 17:28

[펌] 오라클 데이터형 별 저장사이즈


사이즈 추정하기

작성: 김도근

물리 모델 단계에서 로우 사이즈 예측하기가 힘들때가 많다. variable 사이즈를 많이 쓴다면 그에 대한 최대값과 최소값의 편차가 심할 가능성이 크다. 예전에 자료를 만든 'Oracle 9i 의 물리 설계' 를 토대로 예측을 해보자.

 

데이터 형

고정/가변

데이터 저장 시의 길이

CHAR

고정/가변

·         바이트수 지정시는 테이블이 정의한 길이의 고정 길이

·         문자 수지정시는 문자 수에 대해서 고정이지만 실점유 바이트 수는 캐릭터 셋 및 실제의 데이터에 의해 다르다. 예를 들면KO16KSC5601 DB로 「char(6 char)」의 컬럼에 「AAA」라고하는 데이터를 넣으면 3바이트 소비되지만 「하하하」라고 하는 2바이트 한글 데이터를 넣으면 6바이트를 소모한다.

VARCHAR2

가변

실제로 저장되고 있는 데이터의 길이(바이트 수 지정시  문자수지정시 )

NCHAR

고정

테이블 정의 지정 문자수의 두배(AL32UTF8 지정시)

NVARCHAR2

가변

저장 문자수의 2(AL32UTF8 지정시)

NUMBER

가변

길이 = 1 + CEIL (n / 2)

·         CEIL는 올림 값 (: ceil(1.2) = 2 )

·         n는 저장된 수치의 정수부 및 소수부를 합한 총자리수. n>38의 경우에는38 이다

·         유효 자리수 38자리수 미만의 음수의 경우는 1바이트 더한다.

DATE

고정

7 바이트

TIMESTAMP

가변

초의 소수부분에 데이터가 있는 경우:11바이트 고정

초의 소수부분에 데이터가 없는 경우:7바이트 고정

TIMESTAMP WITH TIME ZONE

고정

13바이트

TIMESTAMP WITH LOCAL TIME ZONE

고정

11바이트

INTERVAL YEAR TO MONTH

고정

5바이트

INTERVAL DAY TO SECOND

고정

11바이트

RAW

가변

실제로 저장되어 있는 데이터의 길이

LONG

가변

실제로 저장되어 있는 데이터의 길이

LONG RAW

가변

실제로 저장되어 있는 데이터의 길이

BLOB/CLOB/NCLOB

지정에 따라

  • DISABLE IN ROW 지정시:20바이트
  • ENABLE IN ROW 지정해서 저장시:실 데이터 길이+36바이트
  • ENABLE IN ROW지정해서 행 이외(LOB 테이블)에 저장시:실 데이터 길이에 따라 3686 바이트. 저장시에는 여유 있게 86 바이트로 고정해서 계산한다.
  • LOB테이블의 산출에 대해서는 뒷부분을 참조.
  • EMPTY의 경우는 데이터 길이의 부분은 0으로 계산

BFILE

고정

530 바이트

ROWID

고정

6 바이트(Oracle7), 10바이트(Oracle8)

위의 수치를 토대로 예측단계로 넘어가보자. 일반적으로 varchar 타입의 경우에는 약 70% 정도의 값이 들어간다고 가정한다.

 

SQL> desc emp

 

 컬럼명        NULL유무      데이터타입

 ----------------------------------------- -------- ------------------------

 EMPNO        NOT NULL       NUMBER(4)

 ENAME                       VARCHAR2(10)

 JOB                         VARCHAR2(9)

 MGR                         NUMBER(4)

 HIREDATE                    DATE

 SAL                         NUMBER(7,2)

 COMM                        NUMBER(7,2)

 DEPTNO                      NUMBER(2)

 

 

평균 레코드길이를 구하자

 

3:각 컬럼 길이의 예제

컬럼명

데이터 형

컬럼 데이터의 길이

컬럼 헤더의 길이

합계 길이

EMPNO

NUMBER(4)

1 + CEIL (4 / 2) = 3 바이트

1 바이트

4 바이트

ENAME

VARCHAR2(10)

CEIL (10 * 0.7) = 7 바이트

1 바이트

8 바이트

JOB

VARCHAR2(9)

CEIL (9 * 0.7) = 7 바이트

1 바이트

8 바이트

MGR

NUMBER(4)

1 + CEIL (4 / 2) = 3 바이트

1 바이트

4 바이트

HIREDATE

DATE

7 바이트

1 바이트

8 바이트

SAL

NUMBER(7,2)

1 + CEIL (7 / 2) = 5 바이트

1 바이트

6 바이트

COMM

NUMBER(7,2)

1 + CEIL (7 / 2) = 5 바이트

1 바이트

6 바이트

DEPTNO

NUMBER(2)

1 + CEIL (2 / 2) = 3 바이트

1 바이트

3 바이트

중간 합계 레코드 길이

47 바이트

레코드 길이(컬럼 헤더 3바이트를 가산)

50 바이트


                                           [출처] 로우의 사이즈를 추정해보자. |작성자 오라킬



Trackback 0 Comment 0