오픈 후니쓰의 열린세상

 SQL> SELECT NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
     , SAMPLE_SIZE
     , LAST_ANALYZED
  FROM USER_TABLES
 WHERE TABLE_NAME = 'CMS_CATEGORY';
 
 SQL> SELECT NUM_DISTINCT
     , DENSITY
     , LOW_VALUE
     , HIGH_VALUE
     , LAST_ANALYZED
     , COLUMN_NAME
  FROM USER_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'CMS_CATEGORY'; 

===========================================================================================================================
===========================================================================================================================

출처 : http://blog.naver.com/vxxv122?Redirect=Log&logNo=130128144052

정의

→ 비용기반 옵티마이저에서 통계정보를 모아주기 위한 튜닝 도구.

    테이블과 인덱스, 클러스터, 컬럼에 대한 통계정보를 수집.

권한

→ Analyze Any 시스템 권한

통계 수집

1 . 테이블

→ Analyze Table 테이블명 Compute Statistics;

* Row수, 사용된 Block수, 사용안된 Block수, 사용가능한 평균공간, 변경된 Row수, 컬럼당 distinct 값수

  컬럼당 두번째로 가장 작은 값, 컬럼당 두번째로 가장 큰 값

   (질문) 왜 두번째로 가장 작은값과 큰값을 ?

2 . 인덱스

→ Analyze Index 인덱스명 Estimate Statistics;

* 인덱스 레벨, 레벨 Block수, distinct Key수, Key당 Leaf와 Data Block수 평균, Clustering Factor, 최소 Key 값

  최대 Key값

3 . 클러스터

→ Analyze Cluster 클러스터명 Delete Statistics;

* Cluster Key당 길이의 평균

4 . 컬럼

→ Analyze Table 테이블명 Compute Statistics For Table;

→ Analyze Table 테이블명 Compute Statistics For Columns 컬럼명 Size 75;

→ Analyze Table 테이블명 Compute Statistics For All Indexed Columns Size 75;

* 디볼트 버켓 수는 75개

  Distinct 한 값의 수, 히스토그램 정보

유효성 검사

→ Analyze Table 테이블명 Validate Structure [Cascade];

* 검사하려는 테이블과 관련된 모든 테이블을 검사하려면 Cascade 옵션 사용.

옵션

1 . Compute

→ 테이블에 저장되어 있는 모든 행을 대상으로 통계정보 수집

2 . Estimate

→ 오라클 서버의 자동화 알고리즘에 의해 데이터를 추출하여 통계정보를 수집

3 . Delete

→ 수집되어 있는 통계정보를 삭제

* 20,000건 이하의 Row는 Compute 권장, 이상은 Estimate 권장.

확인

1 . User_Tables

2 . User_Tab_Columns

3 . User_Indexes, Index_Stats, Index_histogram

4 . User_Cluster

5 . DBA_Analyze_Objects

===========================================================================================================================
===========================================================================================================================

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

1. 개요
- TABLE, COLUMN, 그리고 INDEX 에 대한 통계 정보를 수집 하게 하는 PROCEDURE
 
2. SYNTAX 
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME          VARCHAR2, 
                               TABNAME          VARCHAR2, 
                               PARTNAME         VARCHAR2 DEFAULT NULL,
                               ESTIMATE_PERCENT NUMBER   DEFAULT TO_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 DEFAULT NULL,
                               NO_INVALIDATE    BOOLEAN  DEFAULT  TO_NO_INVALIDATE_TYPE (
                                                                 GET_PARAM('NO_INVALIDATE')),
                               FORCE            BOOLEAN DEFAULT FALSE);

3. PARAMETER 설명
- DBMS_STATS.SET_PARAM 에 의해서 디폴트 파라미터 설정 변경이 가능하다.
- 수동 통계정보 생성 시에 저정을 하지 않았을 때 적용되는 DEFAULT 값에 영향을 미치는 값
    => CASCADE
    => DEGREE
    => ESTIMATE_PERCENT
    => METHOD_OPT
    => NO_INVALIDATE
    => GRANULARITY
- 자동 통계정보(GATHER_STATS_JOB) 시에만 영향을 미친다.
    => AUTOSTATS_TARGET [ AUTO   : ORACLE이 자동으로 대상 OBJECT 결정, 
                          ALL    : 대상 시스템의 모든 OBJECTS
                          ORACLE : SYS/SYSTEM OBJECT 만 ]

1) DEFAULT 값 확인
    SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL ;

    DBMS_STATS.GET_PARAM('METHOD_OPT')
    ----------------------------------
    FOR ALL COLUMNS SIZE AUTO
 
2) DEFAULT 값 변경 
    EXECUTE DBMS_STATS.SET_PARAM('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1') ;

    PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
 
3) 변경된 DEFAULT 값 확인

    SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL ;

    DBMS_STATS.GET_PARAM('METHOD_OPT')
    ---------------------------------- 
    FOR ALL COLUMNS SIZE 1
    

4. Test
1) Mission
    CASCADE => TRUE
        E 인덱스에 대한 통계정보도수집하라.
    
    CASCADE => FALSE
        E 인덱스에 대한 통계정보도수집하라.
    
    METHOD_OPT =>'FOR ALL COLUMNS SIZE 1'
        E 칼럼(HIGH AND LOW COLUMN VALUE)에 대한 통계정보도 수집하라.
    
    METHOD_OPT =>'FOR COLUMNS'
        E컬럼에 대한통계정보를 수집하지 마라

2) Test 
2-1) 일반 테이블
    SHOW USER

    USER IS"SYS"

    ① SCOTT의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고, 테이블, 칼럼(HIGHAND LOW COLUMN VALUE), 
       연관 인덱스의 통계정보를 생성한다.( COMPUTE STATISTICS )

       EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT', 
                                         TABNAME    => 'BIG_TABLE', 
                                         CASCADE    => TRUE,
                                         METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
 
    ② SCOTT의 BIG_TABLE의 15% ROW를 가지고, 테이블, 칼럼, 연관인덱스의 통계정보를 생성한다.( SAMPLE 15 PERCENT )
       EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'SCOTT',
                                         TABNAME          => 'BIG_TABLE', 
                                         CASCADE          => TRUE, 
                                         ESTIMATE_PERCENT => 15);

    ③ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블의 통계정보를 수집하라. 
       인덱스와 칼럼에 대한 통계정보는 제외
       EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                         TABNAME    => 'BIG_TABLE', 
                                         CASCADE    => FALSE, 
                                         METHOD_OPT => 'FOR COLUMNS');
 
    ④ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블과인덱스에 대한 통계정보를 수집하라. 
       칼럼에 대한 통계정보는 제외
       EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                         TABNAME    => 'BIG_TABLE', 
                                         CASCADE    => TRUE, 
                                         METHOD_OPT => 'FOR COLUMNS');

    ⑤ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블과 컬럼(NO HISTOGRAM),

       그리고 인덱스에 대한 통계정보를 수집하라.
       잠시 후에 인덱스 칼럼들의 HISTOGRAM 통계정보를 수집하라.

       EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
                                         TABNAME => 'BIG_TABLE', 
                                         CASCADE => TRUE);

       잠시 후에..

       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    =>'SCOTT',
                                          TABNAME    => 'BIG_TABLE', 
                                          CASCADE    => TRUE,
                                          METHOD_OPT => 'FOR ALL INDEXED COLUMNSSIZE 1');

    ⑥ SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고, 테이블과 인덱스칼럼(ONLY HIGH AND LOW )에

       대한 통계정보를 수집하라. 인덱스에 대한 통계정보는수집하지 마라.
        EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                          TABNAME    => 'BIG_TABLE', 
                                          CASCADE    => FALSE,
                                          METHOD_OPT => 'FOR ALL INDEXED COLUMNSSIZE 1');
 
2-2) PARTITIONTABLE 의 경우 
    ① 추가적으로 GRANULARITY 정보를 'ALL', 'AUTO', 'PARITION', 'GLOBAL AND PARTITION', 'GLOBAL', 'SUBPARTITION'을

       통해서 통계수집 대상 TABLE SEGMENT를 선정 가능하다.
 
 
    ② 참고 
        - LOCK VS DBMS_STATS.GATHER_TABLE_STATS
            : DML 이 LOCK 이 발생 하여도 GATHER_TABLE_STATS 는 정상적으로 진행된다.

            BEGIN
                FOR I IN 1001 .. 5000 LOOP
                    INSERT INTO CHECK_LOCK VALUES ( I , I , 'LOCK');
                END LOOP ;
            END ;
            /

        - @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

        - EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCOTT10',TABNAME => 'CHECK_LOCK');
        
        ==> DML LOCK 과는 무관하게 진행 된다.

VI 명령어

VI 커맨드 라인 명령어

COMMAND LINE 명령어 내용

vi ${FILE_NAME} vi를 시작하여 지정한 파일 편집

vi -r ${FILE_NAME} 읽기 전용(read-only) 편집기로서 vi를 시작하여 지정한 파일 편집

view ${FILE_NAME} 읽기 전용(read-only) 편집기로서 vi를 시작하여 지정한 파일 편집

vi -r 되살릴 수 있는 모든 파일 이름 보여주기

vi -r ${FILE_NAME} vi를 시작하여 지정한 파일 되살리기

VI 편집 명령어

삽입

명령어 내용

i 입력 모드로 전환, 커서 위치 앞에서 삽입

I 입력 모드로 전환, 현재 줄의 앞에 삽입

a 입력모드로 전환, 커서 위치 뒤에서 삽입

A 입력모드로 전환, 현재 줄의 뒤에 삽입

o 입력모드로 전환, 현재 줄의 아래에서 시작

O 입력모드로 전환, 현재 줄의 위에서 시작

:set autoindent 자동으로 들여쓰기 적용

:set noautoindent 자동으로 들여쓰기 미적용

편집하고 있는 파일을 바꾸기

명령어 내용

:e ${FILE_NAME} 지정한 파일의 편집

:e! ${FILE_NAME} 지정한 파일의 편집, 자동 점검의 생략

내용 고치기

명령어 내용

r 단지 한 글자만 변경(입력 모드로 바뀌지 않음)

R 입력하는 대로 겹쳐 써서 변경

s 삽입에 의해 한 단어의 변경

S 전체 줄을 삽입에 의한 변경

C 커서의 위치로부터 줄 끝까지 삽입에 의한 변경

cc 전체 줄을 삽입에 의한 변경

cmove 커서부터 move까지 삽입에 의해 변경

~ 대,소문자 바꾸기

고치기의 취소 또는 반복

명령어 내용

u 편집 버퍼를 수정했던 마지막 명령을 취소

U 현재 줄을 저장

. 편집 버퍼를 수정했던 마지막 명령을 반복

문자 삭제

명령어 내용

x 커서가 있는 문자 삭제

X 커서의 왼쪽 문자 삭제

D 커서부터 줄의 끝까지 삭제

dd 현재 줄의 전체 삭제

dmove 커서부터 move까지 삭제

dG 커서부터 편집 버퍼의 끝까지 삭제

d1G 커서부터 편집 버퍼의 맨 앞까지 삭제

:<line>d 지정한 줄의 삭제

:<line>,<line>d 지정한 범위의 삭제

여러 줄의 복사와 이동

명령어 내용

:<line>co<target> 지정한 줄을 복사하여 target 줄 밑에 삽입

:<line>,<line>co<target> 지정한 범위를 복사하여 target 줄 밑에 삽입

:<line>m<target> 지정한 줄로 이동하여 target 줄 밑에 삽입

:<line>,<line>m<target> 지정한 범위로 이동하여 target 줄 밑에 삽입

데이터를 처리하기 위한 셀 명령의 사용

명령어 내용

<n>!!command n번 줄에서 command의 실행

!move command 커서부터 move까지 command 실행

!move fmt 커서부터 move까지 줄들을 형식 맞추기

데이타 저장

명령어 내용

:w 원래의 파일로 데이타를 저장

:w ${FILE_NAME} 지정한 파일로 데이타를 저장

:w>>${FILE_NAME} 지정한 파일에 데이타를 추가

:wq 데이타를 저장하고 종료

:q! 데이타를 저장하지 않고 종료

디스플레이 제어

명령어 내용

:set number 내부 줄 번호 보여주기

:set nonumber 내부 줄 번호 숨기기

마지막으로 지운 것 복사하기

명령어 내용

p 마지막으로 지워진 것을 커서의 뒤/아래에 삽입

P 마지막으로 지워진 것을 커서의 앞/위에 삽입

xp 두 문자를 바꿈

deep 두 단어를 바꿈

ddp 두 줄을 바꿈

패턴 검색

명령어 내용

/rexp 지정된 정규 표현식에 대해 앞으로 검색

/ 이전의 패턴에 대해 앞으로 검색을 반복

?rexp 지정된 정규 표현식에 대해 뒤로 검색

? 이전의 패턴에 대해 뒤로 검색을 반복

n / 또는 ? 명령에 대해 같은 방향으로 반복

N / 또는 ? 명령에 대해 반대 반향으로 반복

약어의 사용

명령어 내용

:ab short long short를 long에 대한 약어로 변경

:ab 현재 약어 목록을 표시

:una short 약어 short를 표시

줄 길이의 조정

명령어 내용

r<Return> 문자를 뉴라인으로 변경

J 아래 라인을 현재 라인과 합치기

:set wm=n 오른쪽으로 n문자 위치에서 자동적으로 줄 나눔

커서 이동

명령어 내용

h or <Left Key> 커서를 한 칸 왼쪽으로 이동

j or <Down Key> 커서를 한 칸 아래로 이동

k or <Up Key> 커서를 한 칸 위로 이동

l or <Right Key> 커서를 한 칸 오른쪽으로 이동

<Backspace> 커서를 한 칸 왼쪽으로 이동

<Space> 커서를 한 칸 오른쪽으로 이동

- 커서를 이전 줄의 처음으로 이동

+ 커서를 다음 줄의 처음으로 이동

<Return> 커서를 다음 줄의 처음으로 이동

0 커서를 현재 줄의 맨 앞으로 이동

$ 커서를 현재 줄의 맨 끝으로 이동

^ 커서를 현재 줄의 첫글자(공백이나 탭이 아닌)로 이동

w 커서를 다음 단어의 첫 글자로 이동

e 커서를 다음 단어의 끝 글자로 이동

b 커서를 이전 단어의 첫 글자로 이동

W w와 같은(문장 부호 무시)

E e와 같음(문장 부호 무시)

B b와 같음(문장 부호 무시)

( 다음 문장의 처음으로 이동

) 이전 문장의 처음으로 이동

{ 다음 문단의 처음으로 이동

} 이전 문단의 처음으로 이동

H 커서를 화면 맨 위로 이동

M 커서를 중간으로 이동

L 커서를 맨 아래로 이동

^ f 한 화면 아래로 이동

^b 한 화면 위로 이동

^d 반 화면 아래로 이동

^u 반 화면 위로 이동

n^d n줄만큼 아래로 이동

n^u n줄만큼 위로 이동

편집 버퍼를 통한 이동

명령어 내용

:!command vi를 중단하고 지정한 셸 명령을 실행

:!! vi를 중단하고 이전의 셸 명령을 실행

:!sh vi를 중단하고 셸을 실행

:!csh vi를 중단하고 새로운 C-셸을 실행

패턴에 의한 치환

명령어 내용

:s/<pattern>/<replace>/ 현재 줄의 치환

:<line>s/<pattern>/<replace> 지정한 줄의 치환

:<line>,<line>s/<pattern>/<replace>/ 정한 범위의 치환

:%s/<pattern>/<replace>/ 모든 줄의 치환

데이터 읽기

명령어 내용

:<line>r ${FILE_NAME} 파일의 내용을 지정한 줄 다음에 삽입

:r ${FILE_NAME} 파일의 내용을 현재의 줄 다음에 삽입

:<line>r !command command의 결과를 지정한 줄 다음에 삽입

:r !command command의 결과를 현재의 줄 다음에 삽입

:r !look <pattern> 지정한 pattern으로 시작된 단어 삽입

정규식에서 사용하는 패턴 기호

명령어 내용

. 뉴라인을 제외한 모든 단일 문자와 대응

* 영 또는 그 이상의 선행 문자와 대응

^ 줄의 시작과 대응

$ 줄의 끝과 대응

\< 단어의 시작과 대응

\> 단어의 끝과 대응

[ ] 묶여진 문자중의 하나와 대응

[^ ] 묶여진 문자를 제외한 아무것하고나 대응

\ 이어지는 기호를 문자 그대로 해석

줄 번호

명령어 내용

[n]G 줄번호 n으로 건너뛰기

1G 편집 버퍼의 첫 줄로 건너뛰기

G 편집 버퍼의 마지막 줄로 건너뛰기

:map g lG g가 lG와 같도록 매크로 정의

■ CBO 내부 구조

  ○ Estimator는 Cardinality, Selectivity, Cost를 계측
  ○ Plan의 조사는 정교한 내부 알고리즘에 의하여 일정 시간 내에 완료
  ○ 최종적으로 가장 Cost가 작은 Plan이 선택


◆ Cardinality
  - 어떤 Row set에 속하는 Row들의 갯수
◆ Selectivity
  - 어떤 Row set에 특정한 조건을 적용하는 경우 그 조건을 만족하는 Row들의 비율
  - 쿼리의 술어(Predicate)나 컬럼값들의 분포도와 밀접한 관련이 있음.
◆ Cost
  - 하나의 SQL문장을 수행하기 위해 필요한 리소스의 소요량(I/O횟수, CPU사용량, 메모리 사용량, 네트워크 전송량)
  - Cardinality 및 Selectivity를 기반으로 계산

■ CBO와 통계정보
  ○ Cardinality, Selectivity, 그리고 궁극적으로 Cost 계산을 위해서는 데이터의 정확한 통계 정보 필요
  ○ 통계정보는 Static Dictionary에 저장
    - 자동으로 바뀌지 않아, 사용자에 의한 수집이 필요. 바꾸려면 DDL을 날려줘야 함.
    - 두가지 방법 : Analyze명령 또는 DBMS_STATS 패키지 이용


◆ Analyze 명령

통계정보를 새로 만들 때는 compute.
이전에 만들어져 있는 통계정보를 검증할 때는 estimate.

◆ DBMS_STATS 패키지

◆ 히스토그램

히스토그램은 특별한 통계정보로써, 데이터가 한쪽으로 쏠리면 기존의 통계정보는 먹지 않는데, 이때 사용할 수 있는 것이 히스토그램임.
단점은 히스토그램 만드는데 오랜 시간이 걸리고, 저장공간 또한 많이 차지하기 때문에 일반 통계정보를 만들어서 잘 작동하면 그거 쓰면 되고, 그게 만족스럽지 못할 때 그때 히스토그램을 쓰면 된다.


□ 히스토그램 가이드라인
  - 데이터 분포의 불균형이 심한 컬럼에만 사용
  - 추가적 저장공간이 필요하므로 상당한 성능 개선이 있는 경우에만 사용
  - 바인드 변수를 사용하는 경우 Histogram은 사용되지 않음.


■ 정리
  - 어떤 테이블에 1억건의 데이터가 들어있다는 통계정보가 있는데, 9천 건을 삭제해도 통계정보는 그대로이다. 이유는 DML이기 때문이다. 그러나 truncate같은 DDL을 날린다면 통계정보가 바뀐다. 그래서 통계정보만 바꿔주는 dbms_stats 패키지나 analyze명령을 날려주면 통계정보가 변경된다. 그러나 이거 한번 날려줄 때마다 서버에 부하가 심하다. 그리하여 업무시간에는 절대 통계정보를 업데이트 하면 안된다.
  - 10g는 위 명령이 자동으로 1시간에 한번씩 업데이트 된다. 9i는 관리자가 수동으로 날려줘야 한다. 그러나 우리가 배웠던 cron을 이용하여 자동으로 업무 이외 시간에 돌려주게끔 하면 된다.
  - 테이블이나 인덱스가 급격히 바뀌었을 때 통계정보를 변경해서 분석해주면 된다.

출처:겨울섬님 블로그