by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3.1. SQL과 옵티마이져




3.1. SQL과 옵티마이져

3.1.1 SQL과 옵티마이저

3.1.2 옵티마이저의 형태 

* 옵티마이저에는 다음의 두 가지 형태가 있음 
- 규칙기준 옵티마이저(RBO)
- 비용기준 옵티마이저(CBO)

3.1.2.1. 규칙기준 옵티마이져

- RBO는 다음의 우선 순위에 따라 연산을 수행한다.
1) ROWID로 1로우 엑세스
2) 클러스터 조인에 의한 1로우 엑세스
3) Unique HASH Cluster에 의한 1로우 엑세스
4) Unique INDEX에 의한 1로우 엑세스
5) CLUSTER 조인
6) Non Unique HASH Cluster Key
7) Non Unique Cluster Key
8) Non Unique 결합 인덱스
9) Non Unique 한 컬럼 인덱스
10) 인덱스에 의한 범위 처리
11) 인덱스에 의한 전체 범위 처리
12) Sort Merge Join
13) 인덱스 컬럼의 MIN, MAX 처리
14) 인덱스 컬럼의 ORDER BY
15) 전체 테이블 스캔

가) 규칙기준 옵티마이저의 단점

  • 오브젝트에 대한 통계정보를 가지고 있지 않기 때문에 판단의 오차가 발생할 수 있음
    예1) 테이블 조인 시 단순히 FROM 절 뒤에 나오는 테이블의 순서대로 조인이 된다.
    * 다음의 두 쿼리는 실행계획이 다르다.
     - Query 1
    SELECT * FROM EMP e, DEPT d WHERE e.deptno = d.deptno

     - Query 2

    SELECT * FROM DEPT d, EMP e WHERE d.deptno = e.deptno

    예2) 분포도를 고려하지 않으므로 인덱스 선정 시 판단 착오가 있을 수 있다.
    * RBO는 Equal(=) 연산을 Range연산보다 우위에 둔다. 분포도를 고려한다면 Range연산 쪽이 효율이 더 좋을 수도 있다. 그러나 RBO는 분포도를 알지 못한다.
    - Query

    SELECT * FROM emp WHERE A='10' and B LIKE '123%'

     B like '123%' 이 A='10' 보다 분포도가 더 좋을 수가 있다. 그러나 RBO는 알지 못하므로 무조건 후자의 방법인 A='10'을 택한다. 

나) 규칙기준 옵티마이저의 장점

  • 매우 규칙적이고 분명하여 사용자가 정확히 예측가능
  • 기본적으로 우선순위에 근거한 판단과 기타 파악이 가능한 요소(FROM 절 뒤에 나오는 테이블의 순서로 Join시 Driving 테이블 선정이나 WHERE 조건에서 어떤 인덱스가 사용될 지와 같은 요소 등)들을 이용하여 사용자는 실행 계획에 대해 정확히 예측이 가능하고 제어가 가능하다
  • 전략적인 인덱스를 구성할 수 있다면 이 규칙의 보편 타당성이 매우 높아짐

3.1.2.2. 비용기준 옵티마이저(CBO)

- CBO: 관계형 데이터베이스가 추구하는 이상형이며, 대부분의 관계형 데이터베이스 제품은 CBO만 보유하고 있음. 처리방법들에 대한 비용을 산정 후, 가장 적은 비용이 들어가는 처리방법을 선택함.
- 통계정보에는 다음과 같은 많은 정보가 있다
테이블의 로우 수, 블록 수, 블록 당 평균 로우 수, 로우의 평균 길이, 컬럼별 상수 값의 종류, 분포도, 컬럼 내 NULL 값 수, 클러스터링 팩터, 인덱스의 깊이, 최대-최소값, 리프 블록 수 등

가) 비용기준 옵티마이저의 장점

  • 현실을 감안한 최적화
    - 컬럼 내부 분포도를 고려한 실행 계획 수립. RBO에서 보았던 Equal(=) 연산이 Range 연산보다 우위에 있다는 부분이 CBO에서는 통하지 않음
    - Histogram을 이용한 더욱 정확한 분포도 고려
  • 통계정보 관리를 통한 제어
  • 최악의 상황이 발생할 확률이 감소
    - 실행 계획을 고려하지 않고 SQL문장을 짜더라도 어느 정도의 성능은 보장됨

나) 비용기준 옵티마이저의 단점

  • 실행계획 예측이 곤란
    - 테이블의 일반적인 통계 정보 뿐만 아니고 컬럼의 Histogram까지 알고 있다고 할 지라도 예측대로 실행 계획이 나오리라는 보장이 없음
    - Bind변수를 사용하느냐 Literal 변수를 사용하느냐에 따라서도 달라질 수 있고 시스템 상태에 따라서도 달라질 수 있음.
    - 통계 정보 갱신 전후로 다른 실행 계획을 가질 수 있으며,
  • 버전에 따른 변화
    - DBMS 버전에 따라 결과가 달라지기도 함. DBMS 업그레이드 등을 통해 바뀌어진 옵티마이저 특성 때문에 실행 계획이 바뀌어 질 수 있음. 이는 더 좋은 방향으로 갈 수 있으나, 부분범위 처리 쿼리가 전체범위 처리 쿼리로 바뀌는 등의 부작용을 일으키기도 함.
    예) 조인 방식의 변화(Nested Loop Join -> Hash Join): DBMS 상위 버전으로 갈수록 Hash Join이 안정화 되고 옵티마이저는 Hash Join으로 해결하려는 경향이 강해짐. 이전에 Nested Loop Join으로 해결되던 쿼리가 실행 계획이 바뀌는 경우가 있음
  • 실행계획 제어가 곤란: RBO때 처럼 제어하던 방법은 먹히지 않음. Hint와 같은 보조수단을 사용해야 제어 가능

라) 통계정보 관리를 위한 제언
 - 효과적인 통계정보의 수집과 관리를 위해 DBMS_STATS 패키지 이용

3.1.2.3. 옵티마이저 목표(Goal)의 선택
가) 옵티마이저 모드의 종류

  • RULE: RBO 옵티마이저를 사용 시 설정한다.
  • ALL_ROWS: '전체결과 최적화'모드. 조건을 만족하는 모든 행들을 가장 빠르게 검색할 수 있는 방법을 택한다. 처리 범위가 크다고 판단된다면, Hash Join 이나 Full Table Scan을 사용하는 경향이 있다..
  • CHOOSE: 통계정보가 있다면 ALL_ROWS 를 사용하며, 존재하지 않는다면 RULE 방식을 사용한다.
  • FIRST_ROWS: '초기결과 최적화' 모드. 조건을 만족하는 첫 번째 행을 가장 빠르게 검색할 수 있는 방식을 택한다. 빠른 응답 시간을 요구하므로 대부분의 연산으로 Nested Loop Join이나 Index Scan을 사용하는 경향이 있다. 이 모드는 CHOOSE처럼 통계정보를 포함하고 있지 않다면 내부적으로 RULE 방식으로 수행한다.
  • FIRST_ROWS_n: n행을 가장 빨리 뽑을 수 검색할 수 있는 방식을 택한다. FIRST_ROWS의 확장 버전이다.

나) 옵티마이저 모드의 결정 기준

  •  OLAP, DSS 환경: ALL_ROWS
  •  OLTP 환경: CHOOSE, FIRST_ROWS, FIRST_ROWS_n

다) 옵티마이저 모드와 관련된 파라미터 지정

  • CURSOR_SHARING: 커서(SQL 문장)들의 공유 정책을 결정. Literal 변수로 쓰여진 SQL 문장들을 Bind 변수로 쓰여진 SQL 문장처럼 전환하여 공유하는 것이 가능
    - 효과: 불필요하게 Hard parsing을 유발하는 Literal 변수를 사용하는 SQL 문장들을 공유하여 Soft parsing 할 수 있도록 해줌
  • DB_FILE_MULTIBLOCK_READ_COUNT: 다중 블록 스캔을 하는 연산(Full Table Scan, Index Fast Full Scan)시 한번에 읽어들이는 블록수를 결정한다.  해당 값이 커질수록 다중 블록 스캔 연산 시 유리해진다.
  • OPTIMIZER_INDEX_CACHING: Nested Loop Join이나 IN-List 검색으로 수행되어 특정 인덱스가 반복해서 랜덤 엑세스를 하게 될 때 일정한 비율의 인덱스 블록들이 버퍼에 캐쉬되어 있다고 가정하고 실행 계획을 세운게 해주는 파라미터
  • OPTIMIZER_INDEX_COST_ADJ: 비용 계산을 할 때 인덱스의 엑세스 비중을 조정하는 역할을 담당하며 기본값은 100이다.
  • OPTIMIZER_DYNAMIC_SAMPLING: 통계 데이터를 보유하고 있지 않을 때, 소량의 표본을 동적으로 추출하여 통계 정보로 활용하기 위해 사용

 ----------------------------------------------------------------------------------------------------------------------------------------------------

  - 테스트 환경: Windows 2003, Oracle 10g R2(10.2.0.3)  
  * DB_FILE_MULTIBLOCK_READ_COUNT
  ALL_ROWS
  DB_FILE_MULTIBLOCK_READ_COUNT=16
  OPTIMIZER_INDEX_COST_ADJ=100
  쿼리: select * from test_tab2 a where col1 = :a1;

  * Ful Scan 쿼리: select /*+ FULL(a) */ * from test_tab2 a where col1 = :a1;
  -------------------------------------------------------------------------------
  | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |           |   520 |  4680 |   499  (12)| 00:00:06 |
  |*  1 |  TABLE ACCESS FULL| TEST_TAB2 |   520 |  4680 |   499  (12)| 00:00:06 |
  -------------------------------------------------------------------------------

  * Index Scan 쿼리: select /*+ INDEX(a IDX_TEST_TAB2) */ * from test_tab2 a where col1= :a1;  in (a1,a2)
  ---------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |               |   520 |  4680 |   466   (1)| 00:00:06 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB2     |   520 |  4680 |   466   (1)| 00:00:06 |
  |*  2 |   INDEX RANGE SCAN          | IDX_TEST_TAB2 |   527 |       |     4   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------


  - DB_FILE_MULTIBLOCK_READ_COUNT 갑에 따른 Cost 변화는?
파라미터 값 Full Scan 비용 Index Scan 비용 수행 방식
1 2067 466 Index Scan
2 1231 466 Index Scan
3 821 466 Index Scan
4 603 466 Index Scan
16 499 466 Index Scan
32 447 466 Full Scan
64 420 466 Full Scan
128 407 466 Full Scan
  - 해당 파라미터의 값에 따라서 Full Scan 비용이 달라짐. 이이 대한 결정 기준은?
    결정하는 요소는 테이블의 전체 블록 갯수(DBA_TABLE의 BLOCKS 컬럼
SQL> select blocks from USER_TABLES where table_name='TEST_TAB2';
    BLOCKS
----------
      2008

       파라미터 값이 1일 때, Full Table Scan의 비용과 통계 상의 Block 갯수와 거의 동일
    이 값을 기반으로 다른 값일 때 비용이 정해짐.

* OPTIMIZER_INDEX_COST_ADJ
( 가정: DB_FILE_MULTIBLOCK_READ_COUNT 값은 32)

파라미터 값 Full Scan 비용 Index Scan 비용 수행 방식
200 447 932 Full Scan
100 447 466 Full Scan
50 447 233 Index Scan
25 447 116 Index Scan  

N

 - 파라미터 값이 50인 경우 실행 게획
 ---------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |               |   520 |  4680 |   233   (0)| 00:00:03 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB2     |   520 |  4680 |   233   (0)| 00:00:03 |
 |*  2 |   INDEX RANGE SCAN          | IDX_TEST_TAB2 |   527 |       |     2   (0)| 00:00:01 |
 --------------------------------------------------------------------------------------------- 
 * OPTIMIZER_INDEX_CACHING
  - 예제: OPTIMIZER_INDEX_CACHING 을 50으로 설정
  - IN-List Iterator 연산
  1) 파라미터 적용 전
  -----------------------------------------------------------------------------------
  | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |               | 39216 |   114K|    81   (3)| 00:00:01 |
  |   1 |  INLIST ITERATOR  |               |       |       |            |          |
  |*  2 |   INDEX RANGE SCAN| IDX_TEST_TAB4 | 39216 |   114K|    81   (3)| 00:00:01 |
  -----------------------------------------------------------------------------------
  2) 파라미터 적용 후
  -----------------------------------------------------------------------------------
  | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |               | 39216 |   114K|    41   (3)| 00:00:01 |
  |   1 |  INLIST ITERATOR  |               |       |       |            |          |
  |*  2 |   INDEX RANGE SCAN| IDX_TEST_TAB4 | 39216 |   114K|    41   (3)| 00:00:01 |
  ----------------------------------------------------------------------------------- 
 

3.1.2.4. 실행계획의 고정화
- 아우트 라인 이란? 요약본이라는 의미를 가지며, 쉽게 변할 수 있는 쿼리의 실행 계획을 아우트 라인을 참조하게 만들어 변하지 않게 한다.
  아우트 라인은 통계 자료의 갱신이나 DBMS Upgrade 등으로 쉽게 변할 수 있다. 이 경우 세션 레벨 또는 시스템 전체적으로 이미 만들어진 아우트 라인을 참조하여 변경을 막을수 있다.

가) 아우트 라인 생성과 조정
- DBMS_OUTLN, DBMS_OUTLN_EDIT 패키지에는 다음과 같은 프로시저로 OUTLINE 제어
CREATE_OUTLINEL 지정된 건을 공유캐시에서 찾아 아우트 라인을 생성한다.
CLEAR_USED: 지정된 아우트 라인을 제거한다.
DROP_BY_CAT: 지정한 카테고리에 속한 아우트 라인을 제거한다.
DROP_UNUSED: SQL 파싱에 사용된 적이 없는 아우트 라인을 제거한다.
UPDATE_BY_CAT: 어떤 카테고리를 새로운 카테고리로 변경한다.
GENERATE_SIGNATURE: 지정한 SQL 문에 대한 식별자를 생성한다. 

나) 아우트 라인의 관찰
- USER_OUTLINES: 생성되어 있는 아우트 라인에 대한 정보를 가지고 있다.
- USER_OUTLINE_HINTS: 생성되어 있는 아우트 라인에 대한 힌트 정보를 가지고 있다.

예제) 수행 쿼리: select * from test_tab2 where col1 = 11
 - USER_OUTLINE 정보 

NAME                           CATEGORY                       USED   TIMESTAM
----------------------- ---------------------- ---- -------
VERSION
----------------------------------------------------------------
SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE
------------------------------------------------------------------------- ------------------
ENABLED  FORMAT
----- -----
SYS_OUTLINE_08030809063457720  DEFAULT01                      UNUSED 08/03/08
10.2.0.3.0
select * from test_tab2 where col1 = :"SYS_B_0"                                  300EF8AD9364D515D939DFA4952CA69D COMPATIBLE
ENABLED  NORMAL

  - USER_OUTLINE_HINTS 정보

NAME                                 NODE      STAGE   JOIN_POS HINT
----------------------- ------- ------ ------ ----------------------------------
SYS_OUTLINE_08030809063457720           1          1          1 INDEX_RS_ASC(@"SEL$1" "TEST_TAB2"@"SEL$1" ("TEST_TAB2""))
SYS_OUTLINE_08030809063457720           1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_08030809063457720           1          1          0 ALL_ROWS
SYS_OUTLINE_08030809063457720           1          1          0 OPT_PARAM('optimizer_index_caching' 50)
SYS_OUTLINE_08030809063457720           1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
SYS_OUTLINE_08030809063457720           1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

다) 오라클 업그레이드 시에 적용

- Outline 수집 방식
 (1) 현재의 규칙기준으로 수행되는 SQL에 대하여 다음과 같이 특정 카테고리를 주고 아우트 라인을 생성한다.
      ALTER SESSION SET CREATE_STORED_OUTLINES= OUTLN_TEST
      이 때, ALTER SYSTEM 구문으로 하면 시스템 전역으로 적용이 가능하다.

 (2) 대부분의 중요한 SQL에 대해 아우트 라인이 생성되도록 가능하다면 오랜기간 동안 수집하는 것이 좋다.
      또는 월 단위 이상이나 특정한 기간에만 수행되는 어플리케이션은 별도의 처리를 해주는 것이 좋다.

 (3) 아우트 라인 생성을 종료 시키려면 CREATE_STORED_OUTLINES 파라미터를 FALSE로 지정한다.
      ALTER SESSION SET CREATE_STORED_OUTLINES= FALSE  

 * RULE 방식에서 CBO 전환 적용 시, 이렇게 아우트 라인을 생성한 후, DBMS_STATS를 이용해 통계를 생성
    UPGRADE 시 또한 생성 후 어플리케이션 테스트 수행

 * 이상 발생 시, USR_STORED_OUTLINES 를 이용해 복원
    ALTER SESSION SET USE_STORED_OUTLINES = OUTLN_TEST
 

3.1.2.5. 옵티마이저의 한계규칙기준 옵티마이저(RBO)의 한계: 내부 통계 값을 고려하지 않고 예측하므로 효율적인 실행 계획이 만들어질 수 없음

  • 비용기준 옵티마이저(CBO)의 한계: 완벽할 수 없는 통계 정보를 바탕으로 정확한 처리 범위를 예측할 수 있느냐에 대한 한계
    (1) 컬럼내의 모든 값이 분포도가 같다고 가정. 이것은 큰 오차를 발생 시킬 수 있음
    (2) Equal(=)연산이 아닌 'LIKE, <,>,BETWEEN'등의 범위 연산 사용 시 정확한 분포도 계산이 어려움.
    ※ 위 한계를 해결하기 위해 Histogram을 사용하면 모든 것이 해결될까?
    실제 쿼리 내부 컬럼에 대한 값이 Bind 변수의 형태로 들어온다면 실행 시점에서 변수의 값과 Binding 되므로 해당 변수 값을 Histogram에 적용 불가능.
    (3) 결합된 컬럼들에 대해 일일이 분포도를 보유할 수 없음. 너무나도 많은 경우가 발생하기 때문이다. 각각의 컬럼들의 종류의 곱만큼 Histogram 정보를 생성해야 하기 때문에 이것은 실제로 불가능. 각각 컬럼 자체에 대한 Histogram을 추출 후, 통계학적으로 연산 후 결합된 분포도를 산정할 수 밖에 없음. 이것은 실제와 큰 오차를 발생시킬 수 있음.

3.1.3. 옵티마이져의 최적화 절차

  • 위 링크를 참고해 주세요

3.1.4. 질의의 변환(Query Transforming)

  • 위 링크를 참고해 주세요

3.1.5 개발자의 역할

  • 개발자는 절차형 사고 방식에서 벗어나야만 한다.
  • 목적과 원하는 시간을 잘 감안해서 관계형 데이터베이스를 이용한다면 보다 많은 데이터를 절차형 처리 보다 빠른 시간에 수행할수가 있다.

문서에 대하여

  • 최초작성자 : 김병두, 김도희
  • 최초작성일 : 2008년 2월 22일
  • 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.
  • 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.