꿈꾸는 개발자, DBA 커뮤니티 구루비

근래 오라클 9i 버전의 개발 지원 중단으로 인해 10g 또는 11g로 업그레이드를 진행하는 사이트가 늘어났다. 9i 버전의 오라클 Optimizer Mode 디폴트 값은 'CHOOSE'로 통계정보가 생성되어 있다면 CBO(Cost Based Optimizer) 방식으로 동작하고 통계정보가 생성되어 있지 않다면 RULE 기반으로 동작하는 방식이다.

오라클 10g 이상 버전의 Optimizer 디폴트 값은 ALL_ROWS이다. 이 방식은 통계정보 존재 유무와 상관없이 CBO 기반으로 동작한다.

CBO 방식으로 동작된다는 것은 통계정보를 이용해 비용을 계산한 후 SQL을 수행한다는 것이다. 9i 버전에서는 통계정보를 수동으로 돌리는 방식이라(오라클 10g부터는 자동으로 통계정보를 생성한다) 통계정보를 생성하지 않은 채 운영하는 사이트가 많다.

이때 10g로 업그레이드를 수행한다면 당연히 10g에서도 통계정보가 생성되어 있지 않을 것이다. 그럼 10g에서 통계정보가 존재하지 않을 경우 CBO 기반으로 어떤 방식으로 SQL을 수행할까?

오라클 Dynamic Sampling

통계정보가 존재하지 않을 경우에 오라클은 Dynamic Sampling Level에 따라 Dynamic Sampling 작업을 수행한다. 즉 Sampling Level에 따라 데이터블록들을 Sampling한 후 통계정보를 생성해 SQL 문을 수행한다.

10g에서도 Optimizer 모드를 CHOOSE 또는 RULE로 지정한다면 Dynamic Sampling을 수행하지는 않는다. 하지만 더 이상 오라클에서 개발을 지원하지않는 RULE로 돌아갈 필요는 없다.

Dynamic Sampling 적용 시점

- Dynamic Sampling으로 더 나은 실행 계획을 생성할 수 있을 때
- Query를 수행하는 전체 시간에 비해 Dynamic Sampling에 소요되는 시간이 아주 작을 때
- Query가 많이 실행될 때

Dynamic Sampling Level

- Level 0 : Dynamic Sampling 사용하지 않음
- Level 1 (9i Default) : 아래와 같은 상황에서 Analyze되지 않은 모든 테이블을 Dynamic Sampling 한다.
- .수행 Query에 최소 1개의 Analyze되지 않은 테이블이 있는 경우
- .Analyze되지 않은 테이블이 다른 테이블과 조인되었거나 서브 Query 또는 non-mergerable view에 있는 경우
- .Analyze되지 않는 테이블에 인덱스가 없는 경우
- .Dynamic Sampling 수행 시 사용되는 블록 수보다 Analyze되지 않는 테이블이 더 많은 블록을 가지고 있는 경우. Dynamic Sampling Block의 샘플링되는 기본 Block 수는 32이다.
- Level 2 (10g Default) : Analyze되지 않는 모든 테이블에 Dynamic Sampling을 수행한다. Dynamic Sampling Block의 샘플링되는 블록은 기본 블록 수(32)의 2배다.
- Level 3 : Level 2를 충족하는 모든 경우에 Dynamic Sampling을 수행한다. 추가적으로 표준 선택도 예측을 위해 모든 테이블은 WHERE절의 잠재적인 Dynamic Sampling이 몇몇 WHERE에 대한 추측을 이용한다. Dynamic Sampling Block의 샘플링되는 블록은 기본 블록수(32)의 2배다.
- Level 4 : Level 3의 사항을 만족하는 모든 테이블에 Dynamic Sampling을 적용한다. 추가적으로 단일 테이블의 WHERE 절에 2개 이상의 칼럼을 참조하는 모든 테이블들에 수행한다. Dynamic Sampling Block의 샘플링되는 블록은 기본 블록 수(32)의 2배다.
- Level 5, 6, 7, 8, 9 : Dynamic Sampling Block의 기본 블록 수에 각각 2, 4, 8, 32, 128을 사용해 이전 레벨 사항을 충족하는 모든 테이블에 Dynamic Sampling을 적용한다.
- Level 10 : 테이블의 모든 Block을 사용해 Level 9의 사항을 충족하는 모든 테이블에 Dynamic Sampling을 수행한다.

테이블 생성 (통계정보 생성되지 않음)

-- 10g부터 인덱스 생성 및 Rebuild 시에 자동으로 통계정보가 생성되므로 
-- False로 변경 (Default 값은 TRUE)
ALTER SESSION SET "_OPTIMIZER_COMPUTE_INDEX_STATS" = FALSE;

DROP TABLE T1 PURGE;

CREATE TABLE T1
    AS
SELECT LEVEL COL1, MOD(LEVEL, 10) COL2
  FROM DUAL
CONNECT BY LEVEL <=1000000;

CREATE INDEX t1_idx ON t1(col2);

Query 수행

SELECT * FROM T2 WHERE COL2 = 1;

Call     Count CPU Time Elapsed Time Disk Query  Current  Rows
------- ------ -------- ------------ ---- ------ ------- ------
Parse        1    0.031       0.037     0     90       0      0
Execute      1    0.000       0.000     0      0       0      0
Fetch    10001    0.156       0.243   196  21781       0 100000
------- ------ -------- ------------ ---- ------ ------- ------
Total    10003    0.188       0.280   196  21871       0 100000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)
Rows    Row Source Operation
------- -----------------------------------------------
0       STATEMENT
100000    TABLE ACCESS BY INDEX ROWID T1 (cr=21781 pr=196 pw=0 time=200040 us)
100000    INDEX RANGE SCAN T1_IDX (cr=10178 pr=196 pw=0 time=27 us)

통계정보 수집 후 쿼리 수행

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'T1');


-- 통계정보 생성 후에는 Full Table Scan이 
-- 더 좋은 성능이 나타나므로 Hint로 제어
SELECT /*+ INDEX(T1 T1_IDX) */ * 
  FROM T1
 WHERE COL2 = 1;


Call     Count CPU Time Elapsed Time Disk Query  Current  Rows
------- ------ -------- ------------ ---- ------ ------- ------
Parse        1    0.000        0.000    0      0       0      0
Execute      1    0.000        0.000    0      0       0      0
Fetch    10001    0.234        0.197    0  21781       0 100000
------- ------ -------- ------------ ---- ------ ------- ------
Total    10003    0.234        0.197    0  21781       0 100000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)

Rows   Row Source Operation
----   ----------------------------------------------
0      STATEMENT
100000   TABLE ACCESS BY INDEX ROWID T1 (cr=21781 pr=0 pw=0 time=100045 us)
100000   INDEX RANGE SCAN T1_IDX (cr=10178 pr=0 pw=0 time=30 us)(Object ID 55973)

위의 예제를 살펴보면 해당 결과 건수는 똑같이 100,000개이고 Table에서 읽은 Block 수도 똑같이 21,781이며 실행계획도 똑같다.

하지만 Parsing 단계를 보면 통계정보를 수행하기 전에는 90 Block을 읽었고, 통계정보 수행 후에는 0 Block을 읽었다.

즉, Dynamic Sampling 작업은 Hard Parsing 시에 Sampling 한 후 Query를 수행하는 작업이다.

오라클 9i에서 10g 버전 이상으로 업그레이드 작업 수행 이후 통계정보를 생성하지 않는다면 위의 예제처럼 오라클이 통계정보를 이용하기 위해 Hard Parsing 시에 Dynamic Sampling 작업을 수행하므로 DB에 많은 부하를 줄 수 있다.

또한 통계정보를 생성하게 되면 Hard Parsing이 필요한 경우 오라클 Optimizer가 통계정보를 바탕으로 더 효율적인 실행계획을 이용해 수행되기도 한다.

종종 이 변경된 Query가 악성일 경우도 있다. 이는 테이블의 모든 데이터를 이용해 Query의 실행계획을 변경하는 것이 아니라 Default로 테이블 데이터의 10% 정도만을 이용해 실행계획을 생성하기 때문이다.

데이터를 100% 참조해 통계정보를 생성한다면 시간은 오래 걸리더라도 좀 더 정확한 실행계획을 생성할 수 있다.

그러므로 9i에서 10g로 업그레이드할 경우에는 통계정보를 생성하고 업그레이드 이후 Optimizer에 의해 변경된 Query들이 존재하는지와 이 Query들이 가장 효율적인 실행계획으로 수행되는지를 반드시 확인해야 한다.