04.라이브러리 캐시 최적화 원리 - 01.SQL과 옵티마이저, 02.SQL처리과정

01.SQL과 옵티마이저

옵티마지어가 내장된 DBMS를 사용한다면 개발자가 프로시저를 직접 코딩할 필요없이 옵티마이저가 대신해서 프로그래밍해주고 프로시저를 생성해 줍니다.

예를들어, 아래와 같은 쿼리를 작성해서 결과값을 리턴받았다고 가정할때 예전에는 두 개 테이블을 조인해 사용자가 원하는 결과집합을 얻으려면, 기준 테이블을 select하고 한 건씩 fetch하면서 반대편 테이블로부터 조인 레코드를 seek하는 과정을 루핑을 통해 반복 수행하는 프로시저를 직접 개발했었어야 했습니다. 하지만 옵티마이저가 우리를 대신해 프로그래밍 해주게되고 프로시저가 자동으로 생성되는 것입니다.

select e.ename,e.job,d.dname from emp e, dept d

where d.deptno=e.deptno

and d.loc='NEW YORK'

 

위의 그림과 같은 과정을 거쳐 옵티마이저에 의해 생성된 처리절차를 사용자가 확인할 수 있도록 트리구조로 표현한 것이 실행계획(Explain Plan)입니다.

 

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |   136 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     4 |   136 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    12 |   168 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    12 |   168 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

 

SQL옵티마이저는 최소비용,최적의 경로를 선택해서 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 프로시저를 자동으로 생성해주는 DBMS의 핵심기능이라고 할 수 있습니다. 옵티마이저의 최적화 수행단계를 요약하면 아래와 같습니다.

1.사용자가 던진 쿼리수행을 위해 후보군이 될만한 실행계획들을 찾아냄

2.데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정

3.각 실행계획의 비용을 비교해서 최소비용을 갖는 하나를 선택

참고로, SQL파싱과 최적화를 담당하는 별도의 백그라운드 프로세스가 존해하지는 않고 서버프로세스가 처리해줍니다.

 

02.SQL 처리 과정

위의 그림은 SQL처리과정을 요약한것입니다. 사용자가 SQL문을 던지면 오라클은 SQL 파싱을 거친 후 해당 SQL이 메모리에 캐싱 돼 있는지 먼저 확인합니다. 메모리에서 찾으면 곧바로 실행할 수 있지만 찾지 못했을 때는 최적화단계를 거치게 됩니다. 메모리에서 SQL커서를 바로 찾아 곧바로 실행단계로 넘어가는 것을 소프트 파싱(Soft Parsing)이라 하고, 메모리에서 찾는데 실패해 최적화 및 Row-Source 생성 단계를 거치는 것을 하드 파싱(Hard Parsing)이라고 합니다.

 

같은 SQL을 5000번 반복하는 동안 수집한 SQL 트레이스 결과입니다.

맨아래쪽 Misses in library cache during parse가 1이고 Parse call이 5000이므로 5,000번 수행하는 동안 첫번째 시점에는 SQL커서를 찾지 못해 하드 파싱했고 나머지 4,999번은 라이브러리 캐시에 공유된 커서를 반복 재사용했음을 알 수 있습니다. 해당 값이 0 이었다면 최초 수행할 떄부터 이미 다른 세션에 의해 커서가 캐싱돼 있었다고 할 수 있습니다.

 

(1)SQL 파싱

사용자가 던진 SQL을 가장 먼저 받아서 처리하는 엔진이 SQL 파서입니다. SQL 파서는 우선 SQL 문장이 이루는 개별 구성요소를 분석하고 파싱해서 파싱트리를 만듭니다. 그 과정에서 사용자가 던진 SQL에 문법적 오류가 없는지 Syntax체크가 이루어 집니다. 예를들어 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 등을 체크하는 것입니다.

파싱트리가 만들어지고 나면 Semantic체크를 통해 의미상 오류가 없는지 확인합니다. 존재하지 않거나 권한 없는 오브젝트를 사용했을 때, 또는 존재하지 않는 컬럼을 참조했다면 이 단계에서 에러를 발생시킵니다.

문법적으로 완전하고 의미상 오류가 없다면 해싱 알고리즘을 이용해 해당 SQL 커서가 Shared Pool에 캐싱돼 있는지를 확인합니다. 그러기 위해 먼저 SQL ASCII 텍스트에 대한 숫자 값을 계산하고 이를 다시 해시 값으로 변환합니다.

Shared Pool에서 찾은 SQL문장이 현재 수행하려는 SQL문장과 100%일치하더라도 파싱을 요청한 사용자가 다르거나 옵티마이저 관련 파라미터 설정이 다르다면 새로운 SQL커서를 생성해야 합니다.

예를들어 EMP테이블이 scott에도 있고 hr에도 있다면 select count(*) from emp라는 쿼리를 수행함에 있어 scott으로 로그인 했을때와 hr로 로그인 했을 때 각각 다른 테이블을 액세스해야하고 실행계획도 달라져야 합니다.

 

(2)SQL 최적화

옵티마이저는 앞에서도 설명했듯이 시스템 통계 및 오브젝트 통계정보를 판단기준으로 삼아 다양한 액세스 경로를 비교하고 그 중 가장 효율적인 실행계획을 선택해주는 DBMS의 핵심엔진입니다. 옵티마이저가 최적화를  수행할 때 3개의 서브 엔진을 사용합니다.

Query Transformer : 사용자가 던진 SQL을 그대로 둔 채 최적화하는게 아니라 우선 최적화하기 쉬운 형태로 변환을 시도합니다. 물론 쿼리 변환 전후 결과가 동일함이 보장될 때만 그렇게 합니다.

Plan Generator : 하나의 쿼리를 수행하는데 있어, 후보군이 될만한 실행계획들을 생성해 내는 역할을 합니다.

Estimator :쿼리 오퍼레이션 각 단계의 선택도(selectivity),카디널리티(cardinality),비용(cost)를 계산하고, 궁극적으로는 실행계획 전체에 대한 총 비용을 계산해냅니다. 이는 어디까지나 예상치고 각 단계를 수행하는데 필요한 I/O,CPU,메모리 사용량 등을 예측하기 위해 데이터베이스 오브젝트 통계정보와 하드웨어적인 시스템 성능 통계정보를 이용합니다. 이들 통계정보들은 오라클이 자동으로 수집하거나 DB관리자의 정책에 따라 주기적으로 수집됩니다.

최적의 실행계획을 선택하려면 그만큼 선택의 폭이 넓어야 합니다. 그러려면 옵티마이저가 많은 실행계획들을 고려대상으로 삼을 수 있도록 짧은 시간 내에 실행계획들을 만들고 이에 대한 비용을 평가할 수 있어야합니다. 그런데 하나의 쿼리를 수행하는 데 있어 너무 다양한 액세스 경로와 조인방식,조인순서가 존재하므로 쉽지 않은 문제입니다.

현실적으로 모든 실행계획을 평가하는 것은 불가능하므로 최적화에 걸리는 총 소요시간을 단축시킬 목적으로 오라클은 몇 가지 지능적인 테크닉을 사용합니다.

우선 쿼리 수행 시 예상되는 총 수행시간에 비해 쿼리 최적화에 걸리는 시간이 일정비율을 넘지 않도록 적응적 탐색 전략을 사용하는데 'Adaptive search strategy'라고 합니다. 

두번재는 'Multiple Initial orderings heuristic'인데 이것은 조인 순서를 무순위로 평가하는게 아니라 최적의 실행계획을 발견할 가능성이 높은 순서대로 비용을 평가하는것을 말합니다. 그렇게 함으로써 실행계획을 평가하는 도중에 멈추더라도 그 중에서 선택된 실행계획이 거의 최적이거나, 최적은 아니더라도 최소한 만족할 만한 수준의 좋은 실행계획에 속할 가능성을 높입니다.

 

(3)Row-Source Generation

옵티마이저의 최적화 과정을 거치면 아래와 같이 SQL 처리과정을 표현한 실행계획이 만들어집니다. (물론 이것은 옵티마이저에 의해 생성된 실행계획을 사용자가 볼 수 있는 형태로 포맷팅한 것일 뿐 실제 모습은 아닙니다.)

 

이것은 개념 수준의 실행계획이지 실제 실행 가능한 형태는 아닙니다. 따라서 이것을 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 작업이 필요하며, 이 역할을 Row-Source Generator가 당당합니다. Row-Source는 레코드 집합을 반복 처리하면서 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어구조를 말합니다.

이를테면 위와 같은 모습일 것입니다.

 

하드파싱은 CPU를 많이 소비하는 몇 안되는 작업 중 하나이고 한번 수행할 때마다 내부적으로 많은 데이터 딕셔너리 조회를 수반하므로 하드 파싱은 우리가 생각하는 것 이상으로 비용이 큽니다. 그래서 낭비를 줄이기 이해 파싱 과정을 거친 SQL 커서를 재사용할 수 있도록 캐싱해두는 메모리 공간이 바로 라이브러리 캐시입니다,