[조시형의 DB이야기] SQL 내비게이션 - 옵티마이저 2

[조시형의 DB이야기] SQL 내비게이션 - 옵티마이저 1 - 보기

통계정보

개인적으로 내비게이션 경로탐색 알고리즘을 연구해 본 적은 없지만, 자동차 내비게이션이 사용하는 가장 기본적인 정보는 GPS 위치정보, 지도, 주소정보, 도로정보 등일 것이다. 초기 내비게이션은 이들 정보를 바탕으로 가장 짧은 거리로만 안내했었다. 

교통량이 많지 않으면 이런 단순한 내비게이션으로도 충분하고, 지금도 시골 동네에선 훌륭하게 작동한다. 하지만 장거리 여행을 하거나 교통체증이 심한 서울 시내를 운전할 때는 통행요금, 구간별 평균/제한 속도, 실시간 교통정보를 활용해 시간과 비용까지 절약해 주는 내비게이션이 꼭 필요하다. 

공사 구간이나 시위, 도보행진, 기타 행사로 인한 임시 교통통제 구간에 대한 정보도 잘 활용하면 차량정체로 인한 운전자들의 불편을 최소화하는 데 큰 도움이 된다. 일례로, '어벤져스2' 한국 촬영 당시, 영화 제작진이 각 내비게이션 업체에 직접 연락을 취해 촬영일정을 알렸고, 업체들은 교통통제 시간에 우회경로로 안내하는 서비스를 제공했다고 한다. 

지난주에도 관련된 기사 하나를 접했는데, 8월 5일 국토부 발표에 의하면 기존에 도로전광판을 통해서만 제공되던 실시간 사고정보를 스마트폰과 내비게이션을 통해서도 제공하기로 했다고 하니 반가운 소식이 아닐 수 없다. 

SQL 옵티마이저도 내비게이션처럼 다양한 정보를 활용하는데, 오브젝트 통계와 시스템 통계가 그것이다. 

● 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 컬럼 통계

● 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등

옵티마이저 종류

사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용을 나타내는 하나를 선택하는 옵티마이저가 비용기반(Cost-Based) 옵티마이저(이하 CBO)다. 

통계정보를 활용하지 못하고 단순한 규칙에 의존해 실행계획을 만들던 시절도 있었다. 일명, '규칙기반(Rule-Based) 옵티마이저', 줄여서 RBO!

혹시 내비게이션도 처음 실험할 당시에는 이런 규칙을 사용하지 않았을까? 고속도로를 이용해 목적지까지 최대한 가까이 이동한 후, 자동차 전용도로, 시내 주요간선도로(왕복 6~10차선), 일반도로(왕복 2~4차선), 골목도로 순으로 찾아가는 규칙. 이런 단순한 방식도 어느 정도 보편타당성을 갖고 목적지까지 정확히 도착하는 데 지장은 없지만, 다양한 교통정보를 활용하는 방식에 비하면 결과가 좋지 않을 가능성이 높다. 

RBO는 데이터양, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등 데이터 특성을 전혀 고려하지 않기 때문에 대용량 데이터를 처리하는 데는 부적합하다. RBO가 가진 규칙도 어느 정도 보편타당성을 갖기 때문에 사용하기에 큰 무리가 없던 시절이 있었다. 하지만 지금과 같은 대용량 데이터베이스 환경에서는 전혀 대안이 될 수 없으며, 누구 말마따나, 칼집에 넣어 박물관에나 갖다 놓아야 할 옵티마이저다. 

예를 들어, RBO는 아래 고객유형코드에 인덱스가 있으면 무조건 인덱스를 사용한다. 이 조건을 만족하는 데이터가 전체 고객 중 90%에 해당하는 데도 말이다. 

  select  * 

  from    고객 

  where   고객유형코드 = 'CC0123';

또한, 아래 SQL에서도 고객명 컬럼에 인덱스가 있으면 무조건 인덱스를 사용한다. 인덱스를 이용하면 소트(Sort) 연산을 생략할 수 있기 때문이다. 소트 연산을 생략하는 건 좋지만, 전체 테이블 레코드를 인덱스를 거쳐 액세스할 때 과연 빠른 성능을 얻을 수 있을까?

  select  * 

  from    고객 

  order by 고객명;

RBO 규칙에 의하면, 부등호 조건의 인덱스보다 Between 조건의 인덱스의 우선순위가 높다(RBO가 가진 10~11번째 규칙 참조). 따라서 사원 테이블의 연령과 연봉 컬럼에 각각 인덱스가 있다면, 아래 SQL에 대해 무조건 연봉 컬럼 인덱스를 사용한다. 

  select  * 

    from  사원 

   where  연령 >= 60 

     and  연봉 between 3000 and 6000;

Between은 닫힌 범위 검색조건이고, 부등호는 열린 범위 검색조건이므로 일반적으로 Between이 더 유리하다는 규칙은 어느 정도 보편타당성을 가진다. 하지만 모두가 알다시피 60세 이상 사원보다는 3000~6000 수준의 연봉을 받는 사원이 훨씬 많다. 그런데 RBO는 그 사실을 모른다. 아니, 굳이 알려고 하지 않는다. 

RBO의 이런 한계점 때문에 CBO가 탄생하게 된 것이다. 따라서 대용량, 초대용량을 넘어 빅데이터 시대에 사는 우리에게 CBO의 사용은 필수다. 그럼에도 가끔 RBO 시대를 그리워하며 통계정보를 수집하지 않겠다는 DBA를 만나게 된다. 같은 SQL인데도 통계정보에 따라 실행계획이 달라지는 특성이 시스템을 불안정하게 만든다는 이유에서다. 같은 목적지인데도 최신형 내비게이션은 가끔 다른 길로 안내하는 게 불안하고 맘에 안 든다며 구형 제품을 고집하는 것과 무엇이 다른가.

통계정보를 수집하지 않으면 과연 RBO 모드로 작동할까? 아래 SQL 실행계획을 보자.

SQL> exec dbms_stats.delete_table_stats(user, 'big_table');  -- 통계정보 삭제 

SQL> set autotrace traceonly exp

SQL> select * from big_table;

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT  |           |  1046K|   140M| 12188   (1)| 00:02:27  |

|   1 |  TABLE ACCESS FULL| BIG_TABLE |  1046K|   140M| 12188   (1)| 00:02:27  |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

Dynamic Sampling 기능이 작동(위 소스코드 맨 하단 굵은 글씨체 참조)하고 그 때문에 SQL 최적화 비용이 증가할 수 있음을 설명해 주면, 이 기능을 비활성화하겠단다. 

SQL> alter system set optimizer_dynamic_sampling = 0;

SQL> select * from big_table;

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT  |           |  3659K|   492M| 12263   (2)| 00:02:28  |

|   1 |  TABLE ACCESS FULL| BIG_TABLE |  3659K|   492M| 12263   (2)| 00:02:28  |

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

원하는 대로 dynamic sampling을 비활성화하고 실행계획을 다시 확인해 봤다. 그런데 SQL 실행계획에 표시된 Rows(=Cardinality) = 3659K, Bytes = 492M, Cost = 12263은 뭘까? 여전히 CBO 모드라는 얘기다. 

RBO도 데이터양을 어느 정도 참조해서 Cost를 계산하지 않나요? 천만의 말씀이다. (가끔, CBO도 RBO가 가진 15가지 규칙을 일부 활용하지 않느냐는 질문을 받기도 받는데, 이 역시 천만의 말씀이다.) 통계정보가 없을 때 CBO는 정해진 상수값과 공식을 이용해 Rows를 추정하고 비용(Cost)을 계산한다. 좀 더 구체적으로 말하면, 일단 평균행 길이는 100Byte로 추정한다. 그리고 Rows는 아래와 같이 구한다. 

  Rows = 세그먼트에 할당된 블록 개수 x (블록 크기 - 오버헤드) / 평균행 길이

그리고 이 Rows를 추출하는 데 필요한 I/O 양으로 Cost를 계산한 것이니, 통계정보 없이 내달리는 CBO 모드라고 표현하는 게 맞을 거다. 마치 70~80년대 도로정보를 기준으로 길을 안내하는 내비게이션이라고나 할까.

결론적으로, 대용량 데이터베이스 환경에서는 반드시 CBO를 선택해야 한다. 그리고 CBO를 선택했다면 반드시 통계정보를 잘 수집해 줘야 하는데, 통계정보의 중요성에 대해서는 뒤에서 다시 강조해서 설명하려고 한다. 

옵티마이저 모드 

CBO 내에서도 ALL_ROWS, FIRST_ROWS, FIRST_ROWS_N 같은 여러 가지 옵티마이저 모드가 존재하며, 애플리케이션 특성에 따라 이를 잘 선택해 줘야 한다. 내비게이션에서 경로검색 전에 검색모드를 먼저 선택하는 것처럼 말이다. 

● ALL_ROWS : 전체 처리속도 최적화 

● FIRST_ROWS : 최초 응답속도 최적화 

● FIRST_ROWS_N : 최초 N건 응답속도 최적화 

내비게이션은 보통 아래와 같은 검색모드를 제공한다. 

● 고속도로 우선 : 이동시간 단축

● 일반도로 우선 : 통행료 절감

● 최단거리 : 유류비 절감

필자는 인천 처가를 방문할 때 대개 제1경인고속도로를 이용하지만, 시간 여유가 있을 때는 고속도로와 나란히 뻗은 국도를 이용하기도 한다. 통행료 900원을 아끼거나, 논길 옆으로 여유 있는 드라이브를 즐기기 위해서다. 이처럼 같은 장소를 방문하더라도 상황이나 기분에 따라 선호하는 경로가 다를 수 있다. 

운전자가 검색모드를 선택하지 않으면 내비게이션이 선택한 추천경로로 안내를 시작하는데, 본인이 잘 모르는 좁은 골목길로 자꾸 안내한다거나 통행료가 비싼 고속도로로 안내한다고 내비게이션을 탓할 일이 아니다. 내비게이션이 운전자 마음을 어찌 알겠는가. 통행료를 절감하고 싶다면, '일반도로 우선' 모드를 명확히 선택해 줘야 한다. (지난주에 여름휴가를 맞아 제주도를 다녀왔는데, 내비게이션 검색모드로 '해안도로 우선', '산악도로 우선'을 제공해 주면 좋겠다는 생각을 했다. 제주도를 여행해 본 독자는 모두 공감할 것이다.)

SQL 옵티마이저도 최적화 목표, 즉 옵티마이저 모드를 제대로 선택해 주지 않으면 당연히 애플리케이션 특성에 맞지 않는 결과물을 낸다. 예를 들어, 100만 건의 결과집합 전체를 내려받아 파일로 저장하려는데, 인덱스를 이용해 소트 연산을 생략하는 실행계획을 만든다. 앞서 설명한, Order by 컬럼에 인덱스가 있으면 무조건 인덱스를 이용하는 RBO 규칙을 상기하기 바란다.

지금까지 통계정보란 무엇인지, 옵티마이저 종류 및 모드(Mode)에는 어떤 것들이 있는지 살펴봤다. 다음 글에서는 통계정보의 중요성에 대해 설명한다.