Tip
2010.04.11 19:53

Analyze 통계정보

조회 수 13296 추천 수 0 댓글 0

ANALYZE는 인덱스, 테이블, 클러스터의 통계정보를 생성.

 

Syntax
ANALYZE object-clause operation STATISTICS
 [VALIDATE STRUCTURE [CASCADE]]
 [LIST CHAINED ROWS [INTO tables]]

 

object-clause
   TABLE, INDEX, CLUSTER중에서 해당하는 오브젝트를 기술하고 처리할 오브젝트 명을 기술.


operation
   operation 옵션에는 다음 3가지중 한가지 기능을 선택할 수 있습니다.
  COMPUTE
       - 각각의 값들을 정확하게 계산 합니다.
       - 가장 정확한 통계를 얻을 수 있지만 처리 속도가 가장 느립니다.
  ESTIMATE
       - 자료사전의 값과 데이터 견본을 가지고 검사해서 통계를 예상합니다.
       - COMPUTE보다 덜 정확하지만 처리속도가 훨씬 빠릅니다.
  DELETE
       - 테이블의 모든 통계 정보를 삭제 합니다.

 

정보수집
    - 주기적인 ANALYZE 작업을 수행 시켜 주어야 합니다.
    - 테이블을 재생성 하거나, 새로 클러스터링을 한 경우, 인덱스를 추가하거나 재생성한 경우,
      다량의 데이터를 SQL이나 배치 애플리케이션을 통해 작업한 경우 ANALYZE를 수행 시켜
 주는 것이 좋습니다.
    - 사용자는 USER_TABLES, USER_COLUMNS, USER_INDEXS, USER_CLUSTER 등의 자료사전
뷰를 통해 정보를 확인할 수 있습니다 
    - 테이블을 ANALYZE 시킨다면 거기에 따르는 인덱스들도 같이 실시하는 것이 좋습니다.

ANALYZE는 다음과 같은 통계정보를 생성하여 데이터 사전에 저장 합니다.
 - 테이블 :  총 로우의수,  총 블럭의 수, 비어있는 블럭에 쓰여질 수 있는 빈 공간의 평군,
                체인이 발생된 로우의 수, 로우의 평균 길이
 - 인덱스 : 인덱스의 깊이(Depth), Leaf block의 개수, Distinct Key의 수,
               Leaf Blocks/Key의 평균, Data blocks/key의 평균,
               Clustering Factor, 가장 큰 key 값, 가장 작은 key 값
 - 컬럼 : Distinct한 값의 수, 히스토그램 정보
 - 클러스터 : Cluster Key당 길이의 평균


 테이블 정보수집 예제

SQL> ANALYZE TABLE emp COMPUTE STATISTICS;
→ 일반적으로 사용하며 가장 정확한 통계정보를 생성.

SQL> ANALYZE TABLE emp DELETE STATISTICS;
→ 새로운 정보를 구하기 전에 기존 정보를 삭제할 때 이용.

 SQL>ANALYZE TABLE emp COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
→ 특정 column에 대한 data 분포.

 

통계 정보의 확인

SQL>SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
last_analyzed
     FROM USER_TABLES
     WHERE table_name=’TABLE명’;

SQL>SELECT num_distinct, density, low_value, high_value, last_analyzed, column_name
     FROM USER_TAB_COL_STATISTICS
     WHERE table_name=’TABLE명’;


DBMS_STATS.GATHER_SYSTEM_STATS 함수

Step
$ sqlplus
Enter user-name: /as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> EXECUTE DBMS_STATS.CREATE_STAT_TABLE( ownname => 'SYSTEM', stattab => 'mystats');
→ system user에 mystats table 생성
PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;
→ JOB_QUEUE_PROCESSES parameter 변경 : 1이상
System altered.

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( interval => 20, stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');                 통계정보생성간격(분)  저장 테이블


< 참고 > statid 로 DAY 와 NIGHT 이 있으므로, DBMS_STATS.GATHER_SYSTEM_STATS
         함수의 파라미터로 선택하여 사용 가능함.


PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 20);

PL/SQL procedure successfully completed.

SQL> select * from system.mystats;

no rows selected

SQL> select count(*) from system.mystats;

  COUNT(*)
         0
위의 select 수행 결과 no rows selected로 나오면 아래와 같이 수행한다.

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START', stattab => 'mystats',  statid => 'DAY',  statown => 'SYSTEM');

PL/SQL procedure successfully completed.

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

PNAME    PVAL2
---------------------------------
STATUS    AUTOGATHERING

 

설정 후 진행 사항 확인
SQL> alter session set nls_date_format='mm-dd-YYYY HH24:MI';

Session altered.

SQL> select c1, to_date(c2), to_date(c3) from system.mystats;

C1                       TO_DATE(C2)      TO_DATE(C3)
------------------------------ -------------------- --------------------
MANUALGATHERING      01-30-2008 08:47  01-30-2008 08:47

정지시
SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP', stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

PL/SQL procedure successfully completed.
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

PNAME      PVAL2
------------------------------
STATUS      COMPLETED

 

<주의> 만약, DBMS_STATS.GATHER_SYSTEM_STATS 함수를 이용하여 STOP을 실행한 이후에 아래 와 같은 명령을 수행 시 PVAL2가 badstats로 나온다면 이것은 interval 이 충분하지 않아 그 기간 동안에 작업 WORKLOAD가 부족하기 때문입니다.
select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
SNAME                   PNAME                  PVAL1    PVAL2
------------------------------ ------------------------------ ---------- -------------------------
SYSSTATS_INFO            STATUS                           BADSTATS
따라서, interval 동안 SINGLE BLOCK I/O와 multiblock I/O에 대한 workload가 오라클에서 통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 없으면 정보가 부족하여 발생하는 현상으로 보입니다. 즉, 지속적인 i/o에 대한 통계치를 제대로 수집하지 못해서 발생하는 것입니다.
시스템에 대한 통계 정보 수집 시 interval은 최소한 60분, default로 120분입니다.

통계정보를 보관하기 위해 생성한 'mystats' 라는 stats table을 drop하려면

SQL> EXECUTE DBMS_STATS.DROP_STAT_TABLE( ownname => 'SYSTEM', stattab =>'mystats');
PL/SQL procedure successfully completed.

 

< 권장 사항 >
dbms_stats.gather_system_stats package 사용 시 이 두 개의 파라미터를 Tuning 후 parameter file에 설정하면 보다 나은 CBO의 효과를 얻을 수 있다.
optimizer_index_caching  (range of value : 0 to 100 default : 0)
optimizer_index_cost_adj (range : 1 to 10000 default : 100)


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84902
76 Tip 오라클 null값 정리 유주환 2010.04.18 29867
75 Tip sysaux resize 유주환 2010.04.18 25290
» Tip Analyze 통계정보 dbkill 2010.04.11 13296
73 Tip Expdp/Impdp시 ORA-31633 에러 해결 방법 2 김준호 2010.04.20 22479
72 Tip DATAFILE SIZE 줄이는 계산 1 고구마 2010.04.19 13475
71 Tip 세마포어에 대하여 고구마 2010.04.19 13230
70 Tip DML_DDL_로그기록_체크 유주환 2010.04.19 13417
69 Tip 통계 백업 및 생성 유주환 2010.04.18 16183
68 Tip shared pool / library cache 고구마 2010.04.28 25066
67 Tip DML 문의 처리과정 고구마 2010.04.28 17306
66 Tip Buffer Cahe 관련 대기 이벤트들 고구마 2010.04.28 25635
65 Tip Buffer busy waits 고구마 2010.04.28 19597
64 Tip 버퍼캐시와 OWI 1 고구마 2010.04.28 19873
63 Tip IDLE EVENT 유주환 2010.04.23 21499
62 Tip 권한주기 고구마 2010.04.23 15733
61 Tip 핫백업 디비올리기 유주환 2010.04.09 34980
60 Tip partiton table truncate 및 rebuild 작업 유주환 2010.04.09 10545
59 Tip How to Use DBMS_STATS to Move Statistics to a Different Database 고구마 2010.04.09 17064
58 Tip 10G FGA AUDIT 1 고구마 2010.04.09 23607
57 Tip EM 재구성 고구마 2010.04.09 26261
Board Pagination Prev 1 2 3 4 Next
/ 4