그런데 왜 SQL 실행계획의 변경이 장애를 가지고 오는지에 대해 인지하는 관리자는 많지 않다. 흔히 모니터링을 하는 관리 자들은 여러 가지 툴을 이용해 이러한 증상들을 Wait Event로 많이 접하지만, 이러한 현상이 왜 일어나는지에 대해 알지 못하 는 사람이 대다수다. 그렇다면 이러한 문제점을 인지하고 미리 막을 수는 없는 것일까?

컨설팅 과정에서 필자는 SQL 실행계획이 변경되는 것에 대해 애플리케이션 프로그램에 힌트를 추가해 실행계획이 변경되지 못하도록 권고하고 있다. 그럼 이러한 문제점을 가지고 있는 오라클 환경을 계속 지켜만 봐야 하는 것일까?

그렇지 않다. 자신 이 운영하는 시스템에는 이러한 문제의 발생이 최소화되도록 예 방해야 하는 것이다. 안정적인 운영을 위해 SQL 실행계획이 변 경하는 것을 최소화하고, 빠른 대응으로써 장애를 최소화하는 방 법을 익히도록 하자.

적을 배우자, 옵티마이저

원인이 있으면 결과가 있는법. 문제는 오라클 내부에 있는 인공지능인 옵티마이저다. 그럼 도대체 옵티마이저가 무엇이기에 잘 운영되고 있는 시스템을 임의로 바꾸는 것인지 알아보자.

• - 옵티마이저 : DBMS에서 질의(SQL)를 분석해, 최적의 실행계획을 생성하는 인공지능 프로그램

옵티마이저는 실행계획을 생성하기 위한 방법들로 Table Access 방법, Table Join 방법 등과 참고자료로 통계정보, 시스 템의 환경, 환경설정 파일 등을 활용해 최적의 실행계획을 생성 한다.

하지만 최적의 길이라고 선택한 것이 여러분들이 접하고 있는 실행계획 변경으로 인한 문제점인 것이다. 그렇다면 옵티마이저 가 어떻게 실행계획을 설정하는지 간단히 살펴보도록 하자.

• [리스트 1] 옵티마이저의 실행계획 설정 예

• SELECT A.*
    FROM A, B
   WHERE A.ID=B.ID 
     AND B.ID='0001';
  
  -----------------------------------------------------------------------
  | Id | Operation        |  Name | Rows | Bytes | Cost(%CPU)| Time     |
  -----------------------------------------------------------------------
  |  0 | SELECT STATEMENT |       | 10   | 120   | 1 (0)     | 00:00:01 |
  |  1 | NESTED LOOPS     |       | 10   | 120   | 1 (0)     | 00:00:01 |
  |* 2 | INDEX RANGE SCAN | B_IDX | 10   | 60    | 1 (0)     | 00:00:01 |
  |* 3 | INDEX RANGE SCAN | A_IDX | 1    | 6     | 0 (0)     | 00:00:01 |
  

A 테이블과 B 테이블이 각각 100건과 10,000건 존재하고, B의 [ID] 컬럼에‘0001’은 10건이 매칭된다.

실행계획은 [리스트 1]의 내용과 같이 B 테이블을 선처리 테이블로 선정한 후 B 테 이블의 인덱스인 B_IDX를 액세스한 후 A 테이블을 후처리 테이 블로 선정해 A_IDX 인덱스를 활용해 Nested Loops 방식으로조인이 이뤄지도록 되어 있다.

이러한 실행계획들이 옵티마이저의 주관적인 생각이 아님을 10053 Event를 통해 알 수 있다. 오라클은 실행계획을 생성하기 위해 많은 일을 한다. 크게 두 가지 일을 진행하게 되는데, 첫 번 째는 오라클이 CBO로 넘어오면서 가장 큰 장점이자 단점이 된 Query Transformation이다.

이 부분은 SQL을 최적화시키기 위해 이뤄지는 단계로, 개발 자가 복잡하게 만들어 놓은 SQL을 옵티마이저가 인라인뷰에 통 합할 것인지, 아니면 서브쿼리 내용을 먼저 풀 것인지, 조건들을 인라인뷰에 삽입해 데이터 액세스를 줄일 것인지 등 많은 부분을 시도한다.

또한 10053 Event를 통해 Query Transformation에 대한 성공, 실패 여부를 볼 수도 있다. [리스트 2]의 내용은 10053 Event에서 Query Optimization에 관련된 내용을 첨부한 것이다 (지면상 해당 트레이스 파일을 간략화했음을 미리 알려 둔다).

• [리스트 2] 10053 Event에서 Query Optimization 관련 내용 첨부

• SINGLE TABLE ACCESS PATH
  Table: A Alias: A
  Card: Original: 100 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
  Best:: AccessPath: IndexRange Index: A_IDX
  Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0 Table: B Alias: B
  Card: Original: 10000 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00
  Best:: AccessPath: IndexRange Index: B_IDX
  Cost: 1.00 Degree: 1 Resp: 1.00 Card: 10.00 Bytes: 0
  

SINGLE TABLE ACCESS PATH 항목은 각 테이블을 접근 할 때, 최적의 데이터 액세스를 위해 해당 테이블의 인덱스 사용 유무를 결정하는 부분이다. 테이블 액세스 방법으로는 Table Scan, index Fast Full Scan, index Scan 등이 있으며 각 수행 비용(Cost)을 계산해 제일 빠른 액세스 방식을 선정하게 된다.

[리스트 2]에서는 테이블 액세스 방식을 A 테이블과 B 테이블 모두 Index Range Scan을 사용하는 것이 최적의 비용(Cost)으 로 선정됐으며, 최적의 실행계획은 Single Table Access Path에 서 선택된 액세스 방식과 [리스트 3]에 나오는 조인방식, 조인순 서를 조합해 최적의 경로를 계산하게 된다. 다음으로는 각 테이 블의 조인 순서와 조인 방법에 따른 내용을 살펴보도록 하자.

테이블은 A와 B 2개이며, 순서는 A 테이블이 먼저 선처리되 는 것과 B 테이블이 먼저 선처리되는 두 가지가 존재한다. 그리 고 조인 방법은 NestedLoop Join, Hash Join, Sort Merge Join 이 존재한다.

• [리스트 3] 조인방식, 조인순서를 조합해 최적의 경로 계산

• Join order[1]: A[A]#0 B[B]#1
  NL Join
  Best NL cost: 2.00
  SM Join
  SM cost: 3.00
  HA Join
  HA cost: 2.50
  ***************************************
  Join order[2]: B[B]#1 A[A]#0
  NL Join
  Best NL cost: 1.00
  SM Join
  SM cost: 3.00
  HA Join
  HA cost: 2.50
  Best:: JoinMethod: NestedLoop
  Cost: 1.00 Degree: 1 Resp: 1.00 Card: 10.00 Bytes: 12
  

경우의 수는 조인 순서(두 가지), 조인 방법(세 가지)이므로 총 여섯 가지의 방식이 존재하며, 각각의 조인 순서와 조인방식에 대해 내부 계산법을 활용해 결과를 도출하게 된다.

여기에서 Best 모델은 B 테이블을 선처리 테이블로 선정하고 A 테이블을 후처리 테이블로 선정해 Nested Loops 조인을 하는 것이 최적의 수행비용(Cost)을 쓰는 것으로 나타났다. 이렇듯 옵 티마이저는 테이블의 액세스 방법, 조인 순서, 조인 방법 등을 이 용해 많은 경우의 수 중에 최적의 비용으로 결과를 도출할 수 있 도록 비교한다.

그리고 우리가 사용하는 오라클 힌트(Leading, Use_hash, Index …) 등이 이러한 실행계획을 추출하는 데 있어서, 옵티마 이저가 혼동하지 않도록 수많은 경우의 수를 줄여주고 Parsing 단계에서의 수행속도를 향상시키는 부분일 것이다.

오라클의 옵티마이저는 실행계획을 생성할 때 많은 일들을 수 행하며 최적의 실행계획을 수립하기 위해 비교 분석한다. 그리고 이러한 실행계획을 생성할 수 있도록 참고하는 부분들이 많이 존 재한다. 참고하는 부분은 통계정보, 초기화 파라미터 등이며 해 당 내용에 관해서는 다음 기회에 설명하도록 한다.