서영이네집

※ 이 글은 "실전 SQL 튜닝" 강의를 듣고 개인적인 의견과 함께 정리한 내용입니다.

OPTIMIZER MODE란 간단하게 말하면 자동차의 기어와 같습니다.
짐을 가득 싣고 오르막길을 간다면 1단이나 2단으로 가야하고 고속도로에서 100km이상 달리고자 한다면
3,4단으로 가야 할 것입니다.
OPTIMIZER 또한 자동차의 기어처럼 상황에 맞는 모드의 선택이 필요합니다.
아... 그리고 자동차 기어에 비유했다고 해서 자동차처럼 수시로 바꾸시면 안됩니다. 운영중인 디비에서 그랬다간 시말서 써야 할지도 모릅니다.^^;;

우선 OPTIMIZER MODE의 종류에 대해서 알아볼까요?

OPTIMIZER MODE 종류

1. CHOOSE
   - 오라클의 9i의 디폴트 설정입니다. 하지만 10g 부터는 ALL_ROWS가 디폴트 설정이죠. 간단하게 설명해서 CBO나 RBO를 사용할지 오라클이 알아서 선택합니다. /*+ RULL*/ 힌트를 사용하거나 통계정보가 하나도 없다면 RBO를 그 외의 경우에는 CBO를 사용하게 됩니다.
2. RULE
   - 무조건 RBO를 사용합니다.
3. FIRST_ROWS (FIRST_ROWS_N)
   - CBO의 일종으로 일부 데이터를 보여주는데 최적화된 모드입니다. 테이블에 1000건이 있어도 '최근 데이터 20건만 보여주겠다.'라면 FIRST_ROWS를 사용하시면 됩니다. 주로 OLTP(Online Transaction Processing)에 적당합니다.
4. ALL_ROWS
   - CBO의 일종으로 전체 데이터를 사용하는 환경에 적당합니다. 예를들어 '테이블안에 있는 전체 데이터를 검색해서 전체의 합이나 평균을 구하겠다.'라면 ALL_ROWS를 사용하시면 됩니다. 주로 OLAP(Online Analytical Processing)에 적당합니다.

지금까지 OPTIMIZER MODE의 종류에 대해서 알아봤으니 지금 사용하고 있는 DB의 OPTIMIZER MODE가 궁금하시죠? 이번에는 OPTIMIZER MODE를 확인하는 방법을 알아보겠습니다.
[code sql]
SELECT NAME, VALUE, ISDEFAULT, ISMODIFIED, DESCRIPTION
FROM V$PARAMETER
WHERE NAME LIKE '%optimizer_mode%';
[/code]

제 DB에서는 이렇게 나오는 군요. 참고로 oracle10g에 기본 설정입니다.
    NAME    VALUE    ISDEFAULT    ISMODIFIED    DESCRIPTION
1    optimizer_mode    ALL_ROWS    TRUE    FALSE    optimizer mode

OPTIMIZER MODE는 Instance, Session, Statement  세 가지 레벨에서 설정할 수 있습니다.

OPTIMIZER MODE의 레벨별 설정

1. Instance Level
   - initSID.ora에서 설정할 수 있으며 변경시에는 DB를 재시작해야 합니다.
   OPTIMIZER MODE = { RULL / CHOOSE / FIRST_ROWS / ALL_ROWS }
2. Session Level
   [code sql]
   ALTER SESSION SET OPTIMIZER MODE = { RULL / CHOOSE / FIRST_ROWS / ALL_ROWS }
   [/code]
3. Statement Level
   - SQL문에 Hint를 추가
   [code sql]
   SELECT /*+ FIRST_ROWS */ ename = from emp;
   [/code]

※ 각 설정의 우선순위는 Statement > Session > Instance 순입니다.

각 모드를 선택하는 기준은 환경에 따라 다르지만 "실전 SQL 튜닝"강의에서는 OLTP에서는 과거에 CHOOSE를 사용했다면 FIRST_ROWS, 9i 이상에서 신규개발이거나 과거에 CHOOSE를 사용하지 않았다면 FIRST_ROWS_N 사용을, OLAP에서는 ALL_ROWS를 추천~!

OPTIMIZER 관련 파라미터

1. CURSOR_SHARING (FORCE, SIMILAR, EXACT)
   - SQL 조건절에 있는 상수값들을 변수로 전환시켜 파싱하는 옵션입니다.
   FORCE, SIMILAR는 조건절에 상수값을 변수로 인정하여 실행계획을 수립하므로 실행계획의 공유비율이 높아집니다. 반면 EXACT는 대소문자, 공백, 비교 상수값이 조금만 달라도 공유할 수 없습니다.
2. DB_FILE_MULTIBLOCK_READ_COUNT
   - Full Table Scan, Index Fast Full Scan을 할때  한번 I/O에 읽을 블록 수를 지정할 수 있습니다. 기본값은 8이며 대용량의 배치처리가 많은 경우 좀더 높은 값을 주면 I/O 타임을 줄일 수 있습니다. 이 값이 커지면 풀 테이블 스캔과 병행해서 Sort Merge Join 또는 Hash Join의 경향이 커집니다.
3. OPTIMIZER_INDEX_CACHING
   - Nested Loop Join시 버퍼 캐쉬내에 Inner Table의 인덱스를 캐쉬화 하는 비율(%)을 지정하므로 Nested Loop Join시 성능이 향상되며 이 수치가 높을 수록 CBO가 Nested Loop Join을 선호하게됩니다. 기존의 RBO를 CBO로 전환시 옵티마이저를 RBO 성향으로 보정하는데 효과적입니다. 기본값은 0으로 지정되어 있으며 주로 현업에서는 80~100을 사용한다고 합니다.
4. OPTIMIZER_INDEX_COST_ADJ
   - 비용계산을 할 때 인덱스 엑세스 비중을 조정하는 역할을 담당하는 변수로써 기본값 100은 계산된 비용을 그대로 적용한다는 의미('계산된비용 값' * 100 )이며 10을 주었다면 '계산된비용 값' * 10으로 비중을 주겠다는 뜻입니다. 인덱스를 이용하는 플랜 위조로 하고자 한다면 100(%)이하를, 가능한 인덱스를 사용하지 않고자 한다면 100이상(100~10000)을 지정하면 됩니다. 기존의 RBO를 CBO로 전환시 옵티마이저를 RBO 성향으로 보정하는데 효과적입니다. 주로 현업에서는 10 또는 25정도를 사용한다고 합니다.
5. OPTIMIZER_PERCENT_PARALLEL
   - CBO가 비용계산시 이 옵션의 수치가 높을 수록 병렬성을 이용하여 풀 테이블 스캔으로 테이블을 엑세스 하려고 합니다. 기본값은 0입니다.
6. HASH_AREA_SIZE, HASH_JOIN_ENABLED
   - 이 파라미터의 값에 따라서 Hash Join으로 유도 할 수 있습니다. Hash Join이 가능하고 해쉬  메모리가 충분하다면 플랜에서 Hash Join의 경향이 커집니다.(항상 Hash Join으로 풀리는게 아니라 CBO가 자원이 충분하고 Hash Join을 사용하는게 더 좋다고 판단하면 Hash Join으로 풀립니다.)
7. OPTIMIZER_SEARCH_LIMIT
   - 옵티마이저가 조인 비용을 계산할 경우 이 파라미터의 값을 가지고 조인의 경우의 수를 바탕으로 비용을 계산하게되므로 이 파라미터의 값이 기본값인 5일 경우 FROM 절에서  3개의 테이블이 있다면 3!=6, 5개의  테이블이 있다면 5!=120개의 경우의 수를 가지고 비용계산을 하게 됩니다. 이때 너무 많은 경우의 수를 바탕으로 비용을 계산하게 되면 옵티마이저가 많은 시간을 소비하게 됩니다.
8. OPTIMIZER_SIZE_POLICY (AUTO | MANUAL)
   - 옵티마이저가 [Hash | SORT | BITMAP_MERGE | CREATE_BITMAP]*_AREA_SIZE를 자동으로 결정하는 PGA 자동관리 방식으로 인위적인 설정없이는 자동 할당된 메모리로 플랜이 결정된다.
9. OPTIMIZER_DYNAMIC_SAMPLING
   - Oracle 9i일 경우는 기본값이 1, 10g일 경우는 2로서 0~10 레벨이 있습니다. 레벨이 높을 수록 SQL 문장의 실행시점에 통계정보를 만들기 위해 테이블의 데이터를 샘플링하기 위한 추가적인 Recurcive SQL이 발생된다. Oracle 10g의 경우 통계정보가 없는 경우 자동으로 '다이나믹 샘플링'이 적용된다. 이때 그만큼의 부하가 발생되고 또한 통계정보가 있는 것보다는 부정확하므로 CBO를 사용한다면 되도록이면 꼭 통계정보를 생성하도록 하는것이 좋습니다.

옵티마이저 관련 파라미터 [권장예시]

이 값은 예시일 뿐입니다. 현업에서 많이 사용되어진다고 해서 모든 환경에서 절대적인 수치가 아닙니다. 이 파라미터들을 적용하기전에 반드시 해당환경에서 여러 수치로 테스트를 거치고 가장 적합한 수치를 적용해야 합니다.
이밖의 다른 파라미터 들은 전문 DBA가 아닌이상 테스트 해보고 적용하는데 있어서 들인 노력만큼의 성과를 얻기가 힘드리라 예상됩니다. 제가 DBA가 아니라 개발자라서 아무래도 개발자 입장에서 보게 되네요.^^;;

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.