Oracle 튜닝 기본 가이드

한 때 DB튜너는 1억원을 상회하는 연봉을 받는 초 고급인력이었습니다.(아! 지금도 그런가요?) 프로그래밍을 하는 입장에서 정말 뛰어난 튜너의 도움을 받고나면 놀라움에 눈이 커지게 되더군요. 이와 같은 계기로 뭔가 튜닝을 교육/공부 등 다양한 경로를 통해서 공부하고자 했습니다!!! 1억~~!!
하단에 쓰여질 튜닝은 "Oracle 10g 10.2.0.1"를 기준으로 작업되었습니다.

[오라클이란?]

 

다들 아시다시피 Oracle은 MySQL, MSSQL 등과 더불어 전 세계적으로 가장 많이 쓰이는 DBMS 중 하나입니다.
Oracle은 버전별로 나누어서 구축에 반영될 정도로 광범위하게 사용되고 있고, 이에 따른 각 버전의 지원도 원활이 이뤄진다는 것이 큰 장점입니다. MySQL, MSSQL에 비하여 월등한 대용량 데이터베이스 처리가 가능하며, 높은 이식성과 함께 분산처리 등이 가능하다는 여러 장점이 있는 1위급 데이터베이스 입니다.

[쿼리 수행 절차]

튜닝의 기본 방법을 확인하기 전에, 우선 Oracle이 어떻게 쿼리를 수행하는지 부터 간략하게 확인해 보도록 하겠습니다.

1. Open 단계 : 해당 쿼리문을 Open합니다.
2. Parse 단계 : 해당 SQL문을 해석하고, 실행계획을 도출하는 단계로, 결과는 shared pool에 저장됩니다.
    1) syntax 체크 : 기계적인 요소들에 대한 체크 수행 (구문, 문법 등 확인)
    2) semantic 체크 : 의미상의 체크 (의미상, 권한 등) 
    3) library cache search : library cache에서 HASH(ASCII(sql_text)) 값을 이용하여 기존에 사용된 값을 이용하여 재사용 가능 여부를 결정한다.
    4-1) 만일 검색결과가 있는 경우에는 해당 실행계획을 바로 파싱한다. (소프트 파싱, soft parsing)
    4-2) 만일 아니라면, library cache에 필요한 공간을 할당하고 하드 파싱(hard parsing)을 수행한다.
           - library cache에 hash값을 저장
           - optimizing 단계를 수행하여 실행계획을 생성
           - 가장 많은 resource를 사용하는 단계임
3. Bind 단계 : 바인딩 변수를 확인하고, 각 값을 할당(혹은 재할당)한다.
4. Execute 단계 : 실질적으로 SQL이 수행되는 단계로, 실제 I/O가 발생한다.
5. Fetch 단계 : 결과값을 산출하는 단계로 필요에 의해서는 sort 작업도 수행된다.
6. Close 단계

[튜닝(쿼리작성)에 대한 기본적인 사항]

바로 윗 단계를 통해 Oracle은 실행계획(Execution Plan)에 의해 해당 쿼리가 실행된다는 것을 확인하실 수 있었을 껍니다. 따라서 이 실행계획이 어떻게 생성되는지를 이해하고, 이에 대한 부분들을 수정하거나, 기타 속도 향상과 안정성을 위해 작업하는 행위를 넓은 의미에서 모두 튜닝이라고 할 수 있겠습니다. 하지만 절대적인 것은 아닙니다. 같은 스타일의 쿼리문이라고 하더라도 데이터가 많고 적음, 바인딩 변수가 어떤 값이냐에 따라서 실행계획은 다르게 생성될 수 있기 때문입니다.

[실행계획(Execution Plan)의 생성원리]

Oracle의 실행계획은 바로 Optimizer에 의해서 결정됩니다. Optimizer의 입장에서 쿼리를 작성하거나 인덱스, 힌트 등의 DB 설정을 진행하시면, 바로 튜닝작업을 하게 되는 것입니다. Optimizer는 다음과 같은 순서로 실행계획을 생성합니다.

1. Query Transformer : 우선, 쿼리문을 해석하고
2. Estimator : 해당 쿼리문을 여러 대상의 실행계획을 통해 추정값들을 계산하게 됩니다.
3. Plan Generator : 가장 적은 cost의 실행계획을 기준으로 실행계획을 생성하게 됩니다. (10g는 기본적으로 cost-based) 

이와 같이 일반적으로 Estimator의 단계에서 DB의 다양한 정보들을 종합하고, (만일 필요한 정보가 없는 경우에는 직접 수행해서라도 통계값을 생성, Dynamic Sampling) 그 값들을 통해 실행계획을 생성하게 됩니다.

다음은 Optimizer가 cost를 추정할 때 고려되는 기준값을 중요한 것만 언급한 것입니다.
1. Hint : 각 쿼리에 /*+ */ 형태로 사용자가 직접 지정한 값을 토대로 실행계획이 생성됨
2. 각종 통계자료
   1) selectivity (선택도) : 통계정보로 저장되는 값으로 0에 가까울수록 해당 컬럼의 값들이 Unique하다는 의미
   2) cardinality : "전체 row값 * selectivity값"
   3) Histogram : selectivity 로는 중복 값을 지닌 row들에 대한 구분이 고려되지 않기 때문에 전체 row값 대비 각 value의 비중을 상대적으로 계산해 놓은 수치
3. WHERE 조건
   1) binding value의 유무 : 옵션에 따라서 쿼리 수행시 해당 파라미터 값을 binding하고 실행계획을 만들 수 있으나 (Bind Peeking), 아직은 불안한 점이 많아 selectivity 값을 기준으로 실행계획을 생성한다.

[쿼리작성/튜닝 CASE]

[쿼리 작성시(개발자 입장)]
   
1. 비교 대상 컬럼과 비교값의 데이터 형태가 인덱스와 같아야 인덱스 스캔을 이용할 수 있다. 이는 바인딩 변수(파라미터)에도 공통적으로 적용되는 부분!
   예) salay 라는 컬럼이 number형임에도 '1000'(char 형)으로 조건을 걸게 되면 salay라는 컬럼이 인덱스 설정되어있더라도 인덱스를 수행하지 않게 된다.

2. IS NULL 연산은 인덱스 스캔을 사용할 수 없다. 하지만 IS NOT NULL 연산은 인덱스 스캔이 가능
   : 일반적인 인덱스 형태에서는 null값을 포함하지 않기 때문이다. 따라서 기본적으로 인덱스를 생성하는 경우에는 null값을 대체할 만한 기본값을 정해놓고, 기본값을 '=' 연산으로 조회하는 것이 훨씬 효율적이다.
   예) salay IS NULL   =>   salay = '00000000'

3. WHERE문은 기본적으로 ['='], ['BETWEEN', '<', '>'], [like] 순서로 쿼리수행 효율성이 높다.

4. 조건 컬럼을 선택할 때에는 반드시 인덱스에 포함되는 컬럼을 위주로 작성하는 것이 좋다.
   : salay라는 컬럼을 [substr(salay, 1, 3) = '213'] 과 같은 형태로 조건을 걸어주는 경우, 아무리 salay 컬럼이 인덱스에 포함되어 있더라도 인덱스 스캔을 하지 않게 된다.

5. BETWEEN과 IN의 경우, 물론 Case by Case이지만 BETWEEN연산보다는 IN연산이 일반적으로 효율적이다.

6. UNION은 각 값의 중복값은 허용하지 않는 특징이 있는 반면, UNION ALL의 경우에는 상위 결과를 Append한다는 특징이 있다. 따라서 속도적인 측면에서 UNION보다는 UNION ALL이 효율적이다. 따라서 UNION 상/하단의 쿼리가 중복된 값이 나올 수 없도록 수정한 후, UNION보다는 UNION ALL을 사용하는 것이 더욱 효율적이다.

7. 만일 존재여부만을 확인하는 경우에는 IN보다는 EXISTS가 효율적이다.
   : EXISTS 조건은 해당 컬럼의 값이 리스트에 하나만 존재하더라도 바로 인식하고 다음 조건으로 넘어가기 때문

8. ORDER BY 절도 인덱스에 따라 인덱스 스캔이 사용된다.
   : 따라서 생성된 인덱스를 고려하여 작성하는 것이 효율적

9. 단순한 MAX값을 조회할 때는 역순 인덱스를 생성하여 "rownum = 1"의 조건을 활용할 수 있다.
   예) select /*+ index_desc(ix_aa) */ from table where aa = 12 and rownum = 1

---

[Hint 작성의 경우]

- /*+ index(인덱스 명) */
  : 기본적으로 인덱스를 생성했음에도 Optimizer가 인덱스 스캔을 수행하지 않고 Full Table 스캔을 수행하는 경우가 있다. 이러한 경우에는 해당 인덱스를 반드시 확인한 후 힌트를 사용하여 인덱스 스캔을 유도한다.

- /*+ use_nl(테이블1, 테이블2) */
  :  대량의 데이터를 조회하는 경우가 아니라면, 그리고 후행 테이블의 조회 조건이 인덱스가 생성되어 있다면 Nested-Loop Join 방법이 일반적으로 가장 효율적이다. 하지만 Optimizer가 Full Table 스캔 등의 비효율적인 경로를 선택하는 경우에는 use_nl 힌트를 통해서 Nested-Loop Join을 수행하도록 설정할 수 있다.

---

[Index 생성시]

1. IS NULL 연산이 필요한 경우 BITMAP형태의 인덱스를 생성하여 사용한다.
  : B*TREE를 사용하는 일반적인 인덱스가 null값을 포함하지 않는 반면 BITMAP 인덱스는 NULL값도 포함된다.

2. 가능하다면 Selectivity(선택성)이 가장 좋은(값이 적은) 컬럼을 선행컬럼으로 지정하여 인덱스를 생성하는 것이 효율적이다.

3. 쿼리의 조회 결과에 해당하는 부분과 WHERE 조건에 해당하는 컬럼은 인덱스에 포함되어 있는지의 유무에 따라서 인덱스 스캔이 결정된다. 따라서 적은 수의 컬럼을 기준으로 조회하는 경우에는 이를 인덱스에 포함하여 수행할 수도 있다.
   예) "select d from xxx where a=a1 and b=b1 and c=c1" 과 같은 쿼리의 경우 index(a, b, c) 의 인덱스가 생성되어 있는 경우, d 컬럼도 인덱스에 함께 포함하여 인덱스 스캔만으로 결과를 얻을 수도 있다.