새로쓴 대용량 데이터베이스 솔루션 - 제 1부 액세스 영향요소의 이해3-1

제 3장 SQL의 실행계획(Explain Plan)

 

3.1 SQL과 옵티마이저

 

3.1.1 옵티마이저와 우리의 역할

Optimizer : 다양한 사용자 요구에 따라 그 때마다 최적의 경로(처리 절차)를 찾아 주는 장치

 

 

3.1.2 옵티마이저의 형태

- 규칙기준 옵티마이저(RBO, Rule-Based Optimizer)

  * 인덱스 구조나 사용 연산자에 부여된 순위로써 최적 경로 결정

  * 통계정보를 전혀 가지지 않음

  * 경우에 따라 비현실적인 처릭경로 수림

  * 수립될 처리경로 예측 가능

  * 사용자가 원하는 처리경로로 유도하기가 용이

  * 일반적인 보편타당성 있음

 

- 비용기준 옵티마이저(CBO, Cost-Based Optimizer)

  * 통계정보로 실제 비용을 계산하여 최소 비용 선택

  * 데이터의 상태에 따른 현실적 처리 경로 수립

  * 이론적으로는 규칙기준에 비해 훨씬 진보된 형태

  * 전문지식이 부족해도 극단적인 악성 실행계획은 피할 수 있음

  * 수립될 처리경로 예측이 곤란

  * 원하는 처리경로로 유도가 곤란

  * 어쩔 수 없는 논리적 한계 존재

 

 

3.1.3 옵티마이저 목표(Goal)의 선택

가) 옵티마이저 모드의 종류

- 초기결과 최적화(First_rows) : 전체는 관심 없고 일부라도 먼저 통과하는 것을 목표로

- 전체결과 최적화(All_rows) : 전체가 모두 수행되는 것을 목표로

 

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

- 변화에 적응하기 위해서는 많은 준비가 필요하므로 기존에 운영중인 시스템에서 모드를 변경하는 것은 매우 위험

 

다) 옵티마이저 관련 파라메터

 

- 동적표본화(Dynamic Sampling)

  * SQL파싱마다 표본을 추출하므로 적은 양의 데이터 처리나 빈번하게 수행되는 경우는 적용 X

  * 동적표본만으로도 충분히 좋은 수행속도를 내거나 전체 수행시간에 비해 표본 추출시간이 적다거나 매우 오래 수행되는 배치처리인 경우 사용

  * 실행계획을 위한 최소한의 표본만 사용

  * 표본 양에 따라 적중률이 비례하는 것은 아니므로 너무 높은 레벨 지정할 필요는 X

  * 초기 값은 기본값을 그대로 적용, 필요시 특정 세션에만 지정

  * 레벨 증가할수록 표본으로 추출하는 블록 수 증가

  * 기본 블록 수는 32, 0은 이 기능을 사용하지 않음

  * 레벨에 따라 2의 제곱만큼 증가하므로 지나치게 높은 레벨을 지정할 때는 주의

 

 

3.1.3.1 실행계획의 고정화

- 아우트라인(Outline) : 과거에 수행되었던 실행계획의 요약본을 저장하고 있다가 이것을 참조하여 실행계획 수립

 

- 적용기준

  * 잘 정의된 옵티마이징 팩터와 적절한 SQL을 기반으로 대부분은 옵티마이저에게 맡겨 수행

  * 특별히 문제가 있는 경우에만 아우트라인으로 통제

 

- 적용방법

  * 범용적 관리(Public)

  * 개별적 관리(Private)

  * 필요에 따라 적용(Enable)시키거나 금지(Disable) 가능

  * 필요 시 강제로 편집(Editing)도 가능

  * Export 해 두었다가 원할 때 Import 가능

  * 그룹(Category)을 지정하여 선별적인 적용 가능

 

가) 아우트라인의 생성과 조정

- 생성과 조정을 도와주는 패키지

  * DBMS_OUTLN

  * DBMS_OUTLN_EDIT

 

- 패키지가 포함하는 다양한 프로시져

  * CREATE_OUTLINE : 지정된 건을 공유커서에서 찾아 아우트라인을 생성한다.

  * CLEAR_USED : 지정한 아우트라인을 제거한다

  * DROP_BY_CAT : 지정한 카테고리에 속한 아우트라인들을 제거한다

  * DROP_UNUSED : SQL파싱에 사용된 적이 없는 아우트라인을 제거한다

  * UPDATE_BY_CAT : 어떤 카테고리를 새로운 카테고리로 변경한다

  * GENERATE_SIGNATURE : 지정한 SQL문에 대한 식별자를 생성한다

 

- 생성(사용)

* ALTER SESSION SET CREATE_STORED_OUTLINES(USE_STORED_OUTLINES)=category_name : 지정카테고리생성(사용)

true : Default 카테고리 생성(사용)

false : 카테고리 생성(사용) 종료

 

- 개별 아우트라인 적용

➀ 기존의 아우트라인에서 새로운 개별 아우트라인으로 복제한다

  * CREATE PRIVATE OULINE prv_ol_1 FROM outln_1;

➁ 아우트라인을 수정할 수 있는 룰이나 DBMS_OULN_EDIT 패키지에 있는 여러 프로시저를 이용해 조인의 순서를 조정하는 등의 작업

  * 수작업으로 이들을 갱신하였다면 REFRESH_PRIVATE_OUTLINE 프로시저로 다시 리플래쉬 해야한다

➂ 생성된 개별 아우트라인을 검증하기 위해 USE_PRIVATE_OUTLINES을 TRUE로 지정하고 검증을 실시한다

➃ 충분한 검증이 끝나면 공식적인 적용을 위해 다음의 작업을 수행한다

  * CREATE OR REPLACE OUTLINE outln_1 FROM PRIVATE prv_ol_1;

➄ USE_PRIVATE_OUTLINES을 FALSE로 지정하여 개별 아우트라인의 수행을 종료한다

 

나) 아우트라인의 관찰

- 수행

 

- 컬럼별로 표시

NAME : SYS SYS_OUTLINE_050921102126522

CATEGORY : LHS001

USED : UNUSED

TIMESTAM : 05/09/16

VERSION : 9.2.0.1.0

SQL_TEXT : SELECT EMPNO, ENAME, JOB, LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7856 ...

SIGNATURE : 2E945C56F14A598F915PA15

 

다) 옵티마이저 업그레이드 시의 적용

  - 안정적인 실행 계획을 유지하면서 점차적으로 비용 기준에 맞추어 가도록 하는 전략

    ➀ 현재의 규칙 기준으로 수행되는 SQL에 대하여 다음과 같이 특정 카테골를 주고 아우트라인 생성을 시작한다

      -> ALTER SESSION SET CREATE_STORED_OULINES = category_name;

      -> 이때 선별적으로 생성하고자 하면 이와 같이 해당 세션에 대해 실시하고 전체적으로 하고자 한다면 ‘ALTER SYSTEM ... ’ 으로 수행

    ➁ 대부분 중요한 SQL에 대해 아우트라인이 생성되도록 가능하다면 오랜 기간 수집하는 것이 좋다. 월 단위 등 특정기간에만 수행되는 어플리케이션에 대해서는 별도의 처리를 한다

    ➂ 아우트라인 생성을 종료시키려면 CREATE_STORED_OUTLINES 파라메터를 ‘FALSE’로 지정한다

    ➃ DBMS_STATS 패키지를 이용하여 통계정보를 생성한다

    ➄ 옵티마이저 모드를 RULE에서 CHOOSE로 변경한다

    ➅ USE_STORED_OUTLINES 파라메터에 앞서 생성해 둔 카테고리를 지정하여 아우트라인을 적용시킨다

 

- 최적의 실행 계획을 제시하지 못하면서 과한 실행계획을 제시하여 문제를 종종 일으키는 옵티마이저에 대해 안정적 실행 계획 유지를 위한 전략

    ➀ 문제나 실행계획을 고정시킬 대상 SQL에 대해 원하는 실행계획을 아우트라인으로 생성한 후 그들만의 카테고리를 지정/생성한다

       -> ALTER OUTLINE outline_name CHANGE CATEGORY TO our_category_name;

    ➁ ALTER SESSION SET USE_STORED_OUTLINES = our_category_name을 통해 아우트라인을 적용시킨다

 

 

3.1.3 옵티마이저 최적화 절차

가) 질의 변환기(Query Transfomer)

 - 양호한 실행계획을 얻을 수 있도록 적절한 형태로 SQL 모양을 변환하는 것

   * 뷰 병합 : 뷰 정의 시 지정 뷰쿼리를 실제 액세스 수행하는 액세스쿼리에 병합해 넣는 방식

   * 조건절 진입 : 뷰병합 불가능 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식의 질의 변환

   * 서브쿼리 비내포화 : 내포관계 해제 방법을 사용하거나 조인형식으로 대체함으로 보다 양호한 수행속도를 얻음

   * 실체뷰의 쿼리 재생성 : 원래 테이블과 실체뷰는 밀접한 논리관계를 가진 물리 집합이므로 사용사자 수행시킨 쿼리를 옵티마이저가 가장 최적의 물리적 집합을 처리하도록 쿼리를 재생성 해줌으로써 수행속도를 크게 개선

   * OR 조건의 전개 : OR 조건연산자는 인덱스를 사용하면 각각을 여러 단위 쿼리로 분기하고 UNION ALL로 연결하는 질의 변환을 수행

   * 사용자 정의 바인드 변수 엿보기(Peeking) : 최초 수행될 때 적용된 값을 이용해 실행 계획을 수립하고 다음 수행부터는 공유하는 방법

 

나) 비용 산정기

- 선택도(Selectivity) : 처리할 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율

- 카디널리티(Cardinality) : 실행계획에서 ‘Card’로 표시된 부분으로 판정대상이 가진 결과건수 or 다음단계로 가는 중간결과 건수를 의미

( 선택도 ) * ( 전체 로우 수 )

- 비용(Cost) : 실행계획에서 ‘Cost’로 표시되는 부분으로 실행계획 상의 각 연산들을 수행할 때 소요되는 시간 비용을 상대적으로 계산한 예측치

 

다) 실행계획 생성기

- 쿼리를 처리할 수 있는 적용 가능한 실행계획을 선별하고, 비교 검토를 거쳐 가장 최소의 비용을 가진 것을 선택

- 특기사항

  * 후보로 등장한 여러개의 실행계획들은 다양한 처리단위(Query Block) 조합으로 구성

  * 어떤 실행계획을 선택하더라도 동일한 결과를 얻을 수는 있음

  * But, 실행계획에 따라 처리의 효율성은 크게 다를 수 있음

  * 논리적으로 적용가능한 실행계획은 경우에 따라서는 천문학적이 될 수도 있음

  * 그러나, 최적화란 쿼리가 실행되기 전에 아주 짧은 시간 내에 수행해야만 하는 작업

- ISSUE

  * 최적화에 많은 시간을 투자한다면 더 나은 실행계획을 얻을 수 있을지 모르지만 이로 인한 부하가 전체 수행시간에 너무 많은 비중을 차지하면 결코 적절하지 않다.

 

 

3.1.4 질의의 변환(Query Transforming)

- 수식연산

  * 가능한 모든 수식의 값을 미리 구한다

➀ sales_qty > 1200 / 20

➁ sales_qty > 100

➂ sales_qty * 20 > 1200 => 좌우를 이항하여 단순화 하지 않는다.

 

  * 조건절 단순화 ( 가변길이 타입만 )

➀ job like ‘SALESMAN’

➁ job = ‘SALESMAN’

 

  * 조건절 확장 ( 고정길이 타입 )

➀ job IN (‘CLERK’, ‘MANAGER’)

➁ job = ‘CLERK’ OR job = ‘MANAGER’

 

  * 조건절 확장 ( ANY, SOME )

➀ sales_qty > ANY (:in_qty1, :in_qty2)

➁ sales_qty > :in_qty1 OR sales_qty > in_qty2

 

  * ANY, SOME 뒤 서브쿼리를 EXISTS 연산자를 이용해 변환

➀ WHERE 100000 > ANY (SELECT sal

   FROM emp

   WHERE job = ‘CLERK’)

➁ WHERE EXISTS (SELECT sal

   FROM emp

   WHERE job = ‘CLERK’

   AND 100000 > sal )

 

  * ALL을 사용한 경우(‘=’, AND 연산자 사용)

➀ sales_qty > ALL (:in_qty1, :in_qty2)

➁ sales_qty > :in_qty1 AND sales_qty > in_qty2

 

  * BETWEEN 비교 연산자를 사용산 조건식은 ‘>=’와 ‘<=’ 비교연산자를 사용한 조건절로 변환

➀ sales_qty BETWEEN 100 AND 200

➁ sales_qty >= 100 AND sales_qty <= 200

 

  * NOT을 사용한 경우 논리 연산자를 제거할 수 있는 비교연산을 찾아 대체시키는 변환을 함 (➀을 ➁로 변환 후, 다시 ➂으로 변환)

➀ NOT (sal > 30000 OR comm IS NULL)

➁ NOT sal < 30000 AND comm IS NOT NULL

➂ sal >= 30000 AND comm IS NOT NULL

 

  * 서브쿼리에 사용된 NOT을 변환하는 경우

➀ NOT deptno = ( SELECT deptno FROM emp WHERE empno = 7689 )

➁ deptno <> ( SELECT deptno FROM emp WHERE empno = 7689 )

 

 

3.1.4.1 이행성 규칙(Transitivity principle)

- 3단 논법 규칙과 비슷

- SELECT *

  FROM emp e, dept d

  WHERE e.deptno = 20

  AND e.deptno = d.deptno;

이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 액세스 하는 실행계획이 가능

- 비용기준 옵티마이저를 사용할 때만 적용

- 비교수식 대상이 상수값이어야만 함

- OR 조건들의 UNION ALL 분기

  * OR을 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환

    : UNION ALL에 의해 각각의 인덱스를 경유하는 실행계획을 선택하고 나중에 결합

    : 실행계획에서는 ‘IN-LIST ITERATOR’나 ‘CONCATENATION’이라는 표시가 나타남

- 조건절에 인덱스를 사용할 수 없고 full table scan을 한다면 OR를 사용한 조건절은 변환을 수행하지 X (효율적이라고 판단할 때만 변환)

 

 

3.1.4.2 뷰병합(View Merge)

- 뷰쿼리 : 뷰를 생성할 때 사용한, 딕셔너리에 저장된 SELECT문

- 액세스쿼리 : 위 뷰를 수행하는 SQL문

 

- 뷰쿼리/액세스쿼리를 병합하는 방법

* 뷰병합(View Merge)

 ✓ 뷰 쿼리 내에서 사용되었다면 뷰쿼리 병합은 불가능

   ➀ 집합연산(UNION ALL, UNION, INTERSECT, MINUS)

   ➁ CONNECT BY

   ➂ ROWNUM을 사용한 경우

   ➃ SELECT-List의 그룹함수(AVG, COUNT, MAX, MIN, SUM)

   ➄ GROUP BY(단, Merge 힌트를 사용했거나 파라메터가 Enable이면 뷰 병합 가능)

   ➅ Select-List의 DISTINCT(단, Merge 힌트를 사용했거나 파라메터가 Enable이면 뷰 병합 가능)

 

* 조건절 진입(Predicate pushing)

 ✓ 집합연산의 경우

 ✓ GROUP BY를 사용한 뷰

 ✓ 복잡한 뷰 내부에 GROUP BY나 DISTINCT를 사용한 경우

 

 

3.1.4.3 사용자 정의 바인드 변수의 엿보기(Peeking)

- 바인드 변수를 사용한 쿼리가 처음 실행될 때 옵티마이저는 사용자가 지정한 바인드 변의 값을 peeking 함으로써 조건절 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화 수행

- 최초 실질적인 파싱이 일어날 때만 단 한번 변수의 값을 peeking

- peeking의 적용여부는 _OPTIM_PEEK_USER_BINDS parameter로 결정

- 현실적으로는 최초 단 한가지 경우의 선택도를 사용하는 것이나 전체를 평균적인 분포로 보는 것이나 논리적으로 오류일 확률은 다르지 않음

  : 11g에서는 Adaptive Cursor Sharing 기능으로 이러한 문제를 많이 해결함

 

 

3.1.5. 개발자의 역할

- 집합적 사고로의 전환

  : 과거 절차형 프로그래밍의 개념에서 SQL의 근간이 되는 집합적, 비절차형 처리에 적응해야함

- 수행 결과 뿐 아니라 성능을 고려한 SQL을 활용