Oracle Technical Note 오라클옵티마이저의기본원리 현재모든관계형 DBMS 에서는사용자의 SQL 질의를효율적으로처리하기위해옵티마이저를사용하고있다. 개발자나관리자들이이옵티마이저의기본동작원리를이해한다면, 여러면에서도움이되리라는생각에이글을쓰게되었다. 먼저, 옵티마이저에대한기본적인이해를구한다음, 오라클옵티마이저에대해알아보도록한다. 본론에앞서, 이글은관계형데이타베이스개념에일정정도익숙한독자들을대상으로작성되었기때문에, 초보자는이해하기가난해할수도있다는점과오라클옵티마이저가워낙광범위한기술이라모든세부적인내용을다다룰수는없으며, 필자도오라클옵티마이저의모든내부동작원리를완전히이해하고있지는못하다는점에양해를바란다. 미진하고잘못된부분은모두필자의책임임을밝혀둔다. 글 : 이상원 ( 주 ) 엑셈연구소장겸성균관대학교교수 swlee@ex-em.com
관계형 DBMS와옵티마이저 21 세기에들어서도여전히관계형 DBMS 가데이타베이스시장을지배하고있다. 그리고, 관계형 DBMS 에서사용되는핵심언어는 SQL(Structured Query Language) 이다. 이 SQL 언어의가장큰특징은사용자가데이타베이스에서자신이원하는데이타 (What) 만지정하면, 그데이타를어떻게구하는가 (How) 는 DBMS가자동적으로결정해서처리해준다는점이다. 이런면에서 SQL 을선언적 (declarative) 언어 ( 주 1) 라고부르며, 사용자는데이타베이스의물리적구조의변경에상관없이항상원하는정확한결과데이타를구할수있다. 이러한물리적데이타독립성 (physical data independence) 이관계형 DBMS를상업적성공으로이끈가장큰이유중의하나이다. 아무리 SQL 이이와같은장점을갖고있더라도, DBMS가내부적으로질의를처리하는방식이비효율적이라면관계형 DBMS 는누구도사용하지않을것이다. 다행히도현재의모든관계형 DBMS는사용자의 SQL 질의를효율적으로수행하는방법을찾아내는옵티마이저 (Query Optimizer : 혹자는 질의최적화기 라고도부르는데이글에서는 옵티마이저 라부르겠다.) 를제공하고있다. 예를들어, 다음과같은간단한 SQL 문을보자. Q1 : select ename, sal from emp e, dept d where e.deptno = d.deptno and d.loc = SEOUL emp와 dept 는각각 deptno와 loc 칼럼에대해 B 트리인덱스가있다고가정한다. 이같은단순한질의 Q1 의경우에도질의결과를구하는방법, 즉실행계획 (execution plan) 은다양할수있다. < 그림 1 > 은두가지실행계획 P1과 P2를보여주고있다. P1은우선 loc = SEOUL 조건을만족하는 dept 레코드를인덱스를이용해서찾고, 각 dept 레코드에대해 deptno 값이일치하는 emp 의레코드를인덱스를이용해서찾아값을출력한다 (Nested Loop 조인방법이용 ). [ 그림 1 ] 질의 Q1 에대한두가지실행계획의예 오라클옵티마이저의기본원리 2
한편, P2는 emp/dept 테이블을 Full Table Scan해서이들을 Sort Merge 조인방식으로조인해서질의처리를수행한다. 주목할점은, 이두실행계획모두정확한질의결과를구하지만, 두방식의수행시간에는차이가많이날수도있다는점이다. 예를들어, P1 방식은 1초에원하는결과를구하는반면, P2 방식은 1시간이걸릴수도있다. P1, P2 이외에도질의 Q1 을수행할수있는많은실행계획이있을수있다. 실행계획이란, 여러개테이블들의조인에대해, 특정한 1) 조인순서 (join ordering), 2) 조인방법 (join method), 그리고 3) 테이블액세스방법 (access method) 을선택하는것이다. 옵티마이저는가능한실행계획들을모두검토하고, 이중에서가장효과적으로, 즉가장빨리, Q1의결과를구할수있는실행계획을결정한다. 이글에서는, 옵티마이저가최적의실행계획을찾는과정을 질의최적화 (Query Optimization) 또는단순히 최적화 라고부르겠다. 관계형 DBMS 옵티마이저의핵심기능 관계형 DBMS 옵티마이저의핵심기능은다음과같다. 실행계획탐색 (Search Space Enumeration) : 주어진 SQL 질의를처리할수있는실행계획들을나열 (P1,.., Pn)? 비용산정 (Cost Estimation) : 각실행계획의예상비용을계산 많은실행계획들중에서최종적으로가장비용이적게드는실행계획 Pi 를선택해서 SQL 을실행하고결과를사용자에게보여준다. 실행계획탐색예를들어, 3개의테이블, T1, T2, T3에대해조인을수행하는 SQL 문이있다고가정하자. 그럼이질의를수행할수있는가능한실행계획은몇가지일까? 우리는앞에서조인순서, 조인방법, 그리고테이블액세스방법에따라서로다른실행계획이만들어진다고했다. 그렇다면, 3개의테이블 T1, T2, T3에대한조인순서는 3!, 즉 6개의조인순서가있다. (T1 _T2) _T3, (T1 _T3) _T2, (T2 _T1) _T3, (T2 _T3) _T1, (T3 _T1) _T2, (T3 _T2) _T1 그리고, 하나의조인순서에는 2개의조인을포함하는데, 이용가능한조인방법이 Nested Loop, Sort Merge, Hash Join의세가지가있다면, 각조인순서에대해총 32, 즉 9개의조합이가능하다. 그리고, 이각각의경우테이블을접근하는액세스방법이 Full Table Scan과 Index Scan의두가지가있다면 23, 즉 8개의서로다른조합이가능하다. 따라서, 3! x 32 x 23 = 432가지의실행계획이가능하다. 그런데, 옵티마이저가고려해야할실행계획의개수는 SQL 에포함된테이블의개수가증가함에따라기하급수적으로늘어나게된다. 만일 from 절의테이블의개수가 5개인경우, 5! x 35 x 25 = 933,120개가가능해진다. < 주 1> 엄밀하게는 SQL 은절차적 (procedural) 언어의요소를포함하고있다. SQL 은이론적으로관계대수 (relational algebra) 와관계해석 (relational calculus) 의두요소를모두포함하고있고, 관계대수는절차적언어이다. 따라서, SQL 은 상대적으로 (relatively) 선언적언어라불러야한다고본다. 오라클옵티마이저의기본원리 3
그리고, 여기서각실행계획의예상비용을계산하는데걸리는시간이 0.01초라고가정했을때, 모든실행계획의예상비용을구하는데약 9,300초 ( 약 2시간 36 분 ) 이걸린다. 만일테이블의개수가 10 개라고가정하면, 아마도모든실행계획의예상비용을계산하는데만도몇년이걸릴지도모른다. 21 세기 IT 환경에서는하나의 SQL 문에 5 ~ 10개정도의테이블이포함되는경우가일반적이다. 그런데, 옵티마이저가실행계획을선정하는데걸리는시간이이와같다면, 옵티마이저는차라리없는것이더나을지도모른다. 따라서, 옵티마이저는모든가능한실행계획을다고려할수는없다. 즉, 질의최적화에걸리는시간을줄이기위해어떤실행계획들은아예비용계산에서제외해야할필요도있다. 옵티마이저는모든가능한실행계획조합들을탐색하는방법 - 즉어떤실행계획을먼저고려하고, 어떤순서로다음실행계획을찾고, 어떤실행계획은제외할것인가? - 을갖고있어야한다. 비용산정앞의실행계획탐색단계에서만들어내는각각의실행계획에대해, 그실행계획을실제로수행할때비용 - 단순하게는시간이얼마나걸릴지? - 을예측해서, 가장비용이적은실행계획을선택해야한다. 이를위해서옵티마이저는데이타베이스내의데이타들에대해갖고있는통계정보와비용을예측하는다양한모델을사용해서각실행계획의비용을계산할수있어야한다. 여기서주목할점은, 옵티마이저가실행계획들을비교할때사용하는기준은 예상비용 이라는점이다. 앞의예에서 P1 과 P2 방법을실제로수행해보고더좋은방법을결정하는것이아니라, 옵티마이저가갖고있는통계정보를활용해서 P1 과 P2 로수행했을때어느실행계획의예상비용이작은가를보고서이를실제로수행하게되는것이다. Selinger 스타일의옵티마이저필자가아는한에서, 현재의모든상용관계형 DBMS의옵티마이저는 IBM DB2의모태인 System-R 프로토타입시스템을개발할당시에고안된아키텍처에기반하고있다 < 참고자료 4>. 이아키텍처를주도적으로제안한 IBM 의여성전산학자 Pat. Selinger의이름을따서, Selinger 스타일옵티마이저 라부른다. 참고로 Pat. Selinger의논문은아직까지도데이타베이스분야에서가장많이인용되는논문중의하나이고, Pat. Selinger는이한편의논문으로데이타베이스연구분야에서슈퍼스타의반열에올라섰다. 이 Selinger 스타일옵티마이저아키텍처의가장큰두가지특징은 1) 동적프로그래밍기반에의한실행계획탐색 (Search Space Pruning based on Dynamic Programming) 과 2) 비용기반최적화 (Cost-Based Optimization) 인데, 각각위에서나열한옵티마이저핵심기능의첫째, 둘째기능에해당된다. 오라클옵티마이저의기본원리 4
옵티마이저의이상과현실가장이상적인옵티마이저는, 모든질의에대해옵티마이저가선택한실행계획이실제로수행될때도가장좋은수행속도를보장하는경우이다. 그러나, 현재의옵티마이저는비록대부분의경우에상대적으로아주좋은실행계획 < 주 2> 을선택하지만, 실제로는아주나쁜실행계획을선택하는경우도있다. 현재의옵티마이저의한계, 원인그리고앞으로의개선방향에대해서는뒤에서자세히설명하겠다. 옵티마이저는 30 년이상축적된기술을포함하고있는, 인간의지능이가장많이녹아있는복잡한소프트웨어이다. 이세상의어떤누구도상용관계형 DBMS 옵티마이저의복잡한내부동작원리를완전히이해하고있는사람은없다고단언할수있다. 실제로필자도이글에서주로설명할오라클옵티마이저에대해서어쩌면기본적인지식밖에없다고할것이다. 다만, 많은오라클개발자나관리자들이현대의옵티마이저의가장기본적인동작원리를쉽게이해했으면하는마음으로이글을썼다. 옵티마이저의아키텍쳐이제까지는현재보편적으로사용되고있는관계형 DBMS 옵티마이저의기능, 간단한역사적배경, 그리고동작원리에대해간략히알아보았다. 이제부터는이러한배경지식을바탕으로 Oracle DBMS의옵티마이저의기본구조와동작원리에대해알아보겠다. 오라클은 RBO(Rule-Based Optimization : 규칙기반최적화 ) 와 CBO(Cost- Based Optimization : 비용기반최적화 ) 를모두지원하고있다. CBO의경우, 1992년 Oracle 버전 7부터도입되었는데, 향후이글에서오라클옵티마이저라함은 CBO 를지칭하는것이다. RBO 는간단한규칙위주로최적화를수행하는방법으로, 앞으로는널리사용되지않을것이며, 오라클도공식적으로 CBO 사용을권장하고있다. 그림 2 는오라클옵티마이저의아키텍처를보여주고있는데, 사용자의 SQL 질의는크게다음 4단계를거쳐서수행된다. 1. 파싱 (Parser) 2. 옵티마이저 (Query Optimizer) 3. 로우소스생성 (Row Source Generator) 4. SQL 실행 (SQL Execution Engine) 파싱 (Parser) 단계는 SQL은구문 (syntax) 과의미 (semantics) 검사를수행한다. 예를들어, SQL 구문이정확한지를검사하고, 참조된테이블에대해사용자의접근권한등을검사한다. 이단계가끝나면, SQL 문은파싱트리 (parsed tree) 형태로변형되어옵티마이저에게넘겨진다. < 주 2> 어떤실험에서는 80% 정도의경우, 옵티마이저가선택하는실행계획이실제수행했을때도결과가가장좋았다. 나머지 20% 의경우에, 옵티마이저가선택한실행계획은평균적으로세번째로시간이많이걸렸다. 이실험전체에걸쳐서가장빠른실행계획과옵티마이저가선택한실행계획의수행시간은 1.33 배정도였다. 오라클옵티마이저의기본원리 5
옵티마이저 (Query Optimizer) 단계는앞에서넘겨받은파싱트리를이용해서최적의실행계획을고른다. 그림 2 에서점선형태의사각형으로표시된부분이옵티마이저의주요구성요소를보여주고있는데, 뒤에서각구성요소의역할에대해자세히설명하겠다. [ 그림 2 ] Oracle Query Optimizer 의아키텍처 로우소스생성 (Row Source Generator) 단계는옵티마이저에서넘겨받은실행계획을내부적으로처리하는자세한방법을생성하는단계이다. 로우소스 란실행계획을실제로구현하는인터페이스각각을지칭하는말로, 테이블액세스방법, 조인방법, 그리고정렬 (sorting) 등을위한다양한로우소스가제공된다. 따라서, 이단계에서는실행계획에해당하는트리구조의로우소스들이생성된다. 마지막으로, SQL 실행 (SQL Execution Engine) 단계는위에서생성된로우소스를 SQL 수행엔진에서수행해서결과를사용자에게돌려주는과정이다. 여기서한가지주목할점은, 소프트파싱 (soft parsing) 과하드파싱 (hard parsing) 은크게옵티마이저단계의포함여부에따른차이이다. 즉, 소프트파싱은이미최적화를한번수행한 SQL 질의에대해옵티마이저단계와로우소스생성단계를생략하는것이고, 하드파싱은이두단계를새로수행하는것이다. 따라서, 하드파싱은통계정보접근과실행계획탐색때문에시간이많이걸린다. 이차이가주로 SQL 튜닝전문가들이가급적이면하드파싱을피하라고권하는이유이다. 오라클옵티마이저의동작원리이제부터는오라클옵티마이저의각구성요소의기능에대해좀더자세히알아보자. 그림 2 에서보듯이, 오라클옵티마이저는크게다음 3가지모듈로구성된다. 질의변환 (Query Rewriter) 실행계획생성 (Plan Generator) 비용산정 (Estimator) 오라클옵티마이저의기본원리 6
질의변환모듈질의변환 (Query Rewriter 또는 Transformer) 단계는파싱트리 (parsed tree) 를받아들여서질의변환을수행한다. 이변환과정을통해서의미적으로같은결과를수행하지만, 더나은실행계획을찾을수있는 SQL 문으로변환함으로써질의의수행처리속도를높이는데그목적이있다. 오라클옵티마이저가수행하는질의변환은크게다음두종류로구분할수있다. 휴리스틱 (Heuristic based) 질의변환 : 이변환의종류로는크게 View Merging, Subquery Unnesting, Predicate Push Down, Partition Pruning 등이있는데, 이들변환은가능한경우에항상질의변환을수행한다. 왜냐하면, 이와같은변환은경험적으로거의항상원래질의보다더빠른수행속도를보장하기때문이다. 비용기반 (Cost based) 질의변환 : 이변환의예로는, MV Rewrite, Star Query Transformation, OR-expansion 등을들수있다. 그런데, 이방법을사용해서변환된 SQL 문이원래 SQL 문보다속도가더빠르다는보장이없다. 따라서, 변환전 / 후의두 SQL 문에대해각각최선의실행계획을구하고, 이들의비용을비교해서더효율적인실행계획을최종적으로선택한다. 오라클의질의변환모듈에서지원하는다양한변환의종류와내용에대해서는 < 참고자료 2, 3> 을참고하기바란다. 질의변환단계가끝나면, 오라클옵티마이저는실행계획생성과비용산정모듈을수행하기앞서, 질의에서사용된모든테이블들과각테이블에정의된인덱스들에관한기본적인통계정보들 ( 예를들어, 테이블의블록개수, 로우평균길이, 인덱스의높이, 인덱스리프블록의개수등 ) 과각테이블에대한다양한액세스경로 ( 예를들어, Full Table Scan, Index Scan 등 ) 에대한비용정보를미리구해둔다. 오라클옵티마이저의기본원리 7
옵티마이저의아키텍쳐실행계획생성모듈이모듈은옵티마이저가새로운실행계획을만드는것이다. 오라클옵티마이저는제일먼저각테이블의레코드수를기준으로오름차순으로결정한다. 예를들어, SQL 질의의 from 절에서 T1, T2, T3 순서로참조한경우, 각테이블의카디널러티 (cardinality : 테이블의튜플수 ) 가 T1 > T2 > T3 순이라면제일처음고려하는조인순서는 (T3 _T2) _T1 이된다. 이조인순서에대해서다음단계인비용산정모듈을호출해서이조인순서에따르는실행계획들과각실행계획의비용을구한다. 그리고, 더이상의새로운조인순서가없을때까지계속새로운조인순서를만들어서비용을계산한다. 이모듈은지금까지찾아낸가장좋은실행계획과그비용을저장하고있다. 이단계는최종적으로구해진최적의실행계획을 그림 2 의로우소스생성단계에넘겨준다. [ 그림 2 ] Oracle Query Optimizer 의아키텍처 오라클의실행계획생성모듈은테이블개수가정해진값 ( 디폴트 5) 보다작은경우에는모든조인순서에대해고려하지만, 테이블개수가이값을넘어서면 where 절에명시적으로조인조건이테이블들을앞에포함하는조인순서만고려한다. 예를들어, 6개의테이블 T1, T2,..., T6에대한조인을수행하는 SQL 문에서조인조건이 T1, T2, T5, T6에대해서만주어졌다면, T1, T2, T5, T6가먼저조인되고, T3, T4는항상나중에조인되는조인순서만고려한다. 그런데, 이런경우에도, 앞에서언급한것처럼, 테이블의개수가많으면가능한조인순서의조합이기하급수적으로늘어나게된다. 이렇게되면옵티마이저시간이너무많이걸리기때문에, 옵티마이저는일정한수 ( 디폴트로는최대 80,000) 의조인순서에대해서만비용을계산하고, 이중에서가장최선의실행계획을찾게된다. 즉, 모든가능한조인순서조합들중에서일부분만비용을계산하고, 나머지는고려하지않는것이다. 이를실행계획탐색에대한 가지치기 (pruning) 또는 컷오프 (cutoff) 라부른다. 그런데, 고려되지않은조인순서중에서실제로최선의실행계획이포함되어있을 오라클옵티마이저의기본원리 8
수있다. 옵티마이저가제일처음고려하는조인순서를테이블의레코드수의오름차순순서로정하는이유는경험적으로이순서근처에실제로최적의실행계획이존재하기때문이다. 이와같이초기조인순서를선택하는휴리스틱 (heuristic) 을사용함으로써임의로조인순서를시작했을때최적의좋은실행계획이컷오프되는것을막을수있다. 오라클옵티마이저실행계획생성모듈 (Oracle9i부터도입된 ) 의또다른특징은, 조인순서를바꾸어가면서지금까지구한최적의실행계획의예상비용이그리크지않은경우, 최적화단계를일찍끝내버린다. 예를들어, 어떤질의에대해 10 초동안최적화를수행해서찾은최적실행계획의예상수행시간이 1분이면, 남은조인순서가더있더라도옵티마이저단계를종료한다. 반면에, 지금까지구한최적예상수행시간이 2시간이면, 더나은실행계획을찾기위해새로운조인순서에대해계속탐색할필요가있다. 이를 적응적탐색전략 (adaptive search strategy) 이라부른다. 비용산정모듈자, 그럼다음으로비용산정모듈에대해알아보자. 실행계획생성모듈에서넘겨받은특정조인순서의각조인에대해 Nested Loop, Sort Merge, Hash Join 방식과각테이블의다양한액세스방법을반복적용하면서각단계별로비용을계산해서궁극적으로해당조인순서에서찾을수있는최선의실행계획과그예상비용을구해서실행계획생성모듈에게넘겨준다. 현재의조인순서에대해중간단계까지의수행비용이실행계획생성모듈에서지금까지구한최선의예상비용보다더크다면, 해당조인순서에대해서는더이상비용산정을수행하지않고끝낸다. 예를들어, T1, T2, T3에대해 (T1 _T2) _T3 순서에대해비용이 1000이었는데, (T _T3) _T2 순서의 (T1 _T3) 비용이 1200이었다면더이상비용을계산할필요가없다. 그림 2 에나와있는것처럼, 옵티마이저는실행계획의비용을계산하기위한비용모델 (Cost Model) 을갖고있고, 이비용모델은 Oracle Data Dictionary에서관리하는다양한통계정보를기반으로크게다음과같은세가지값 (measure) 의예상치를계산한다. [ 그림 2 ] Oracle Query Optimizer 의아키텍처 오라클옵티마이저의기본원리 9
선택도 (Selectivity) : Where 절에있는다양한조건들의선택도계산 카디널러티 (Cardinality) : 실행계획상의각각의연산의결과카디널러티수계산 비용 (Cost) : 실행계획상의각각의연산을수행하는데소요되는시간비용계산이비용산정을위한통계정보를저장하는 Data Dictionary 테이블들은 DBA_TABLES, DBA_INDEXES, DBA_TAB_COL_STATISTICS, DBA_ HISTOGRAMS 등이다. 이통계정보는앞에서언급한테이블의액세스경로의비용정보를결정하는데도사용된다. 이들테이블의정보는사용자가 ANALYZE 명령어나 DBMS_STATS 패키지를이용해서관리하게되는데, 이들테이블에서관리하는통계정보의종류와통계정보를수집 / 관리하고, 통계정보를확인하는자세한방법은이글에서는설명하지않겠다 < 참고자료 5>. 만일에테이블과인덱스에대한통계정보가존재하지않는경우, 옵티마이저는해당테이블과인덱스에대해디폴트로가정하는값들이있다 < 참고자료 5>. 선택도우선선택도 (selectivity) 의개념을예로들자. 앞에서예로든질의 Q1 에서 d.loc = SEOUL 이라는조건의선택도는 dept 테이블전체중에서 loc 의값이 SEOUL 인레코드의비율을일컫는다. 옵티마이저는선택도계산을통해서해당조건을만족하는레코드가몇건정도가되는지를예측하게된다. 옵티마이저는만일 DBA_TABLES에 dept 테이블의 loc 칼럼의 distinct column values가 10 이라면옵티마이저는선택도가 0.1 이라고판단하게된다. 이때선택도를이와같이정하는이유는 dept 테이블이 loc 칼럼들에골고루분포되어있다고가정할때성립한다. 그러나, 실제로 loc 칼럼의값들이 skew되어서분포할수도있다. 예를들어, 전체레코드의 50% 가 loc 값으로 SEOUL 을갖는다면잘못된선택도값을얻게된다. 이와같이데이타분포가 skew되어있는경우, 해당칼럼에대한히스토그램정보를 DBA_HISTOGRAM 테이블에만들어주어야정확한선택도값을계산할수있다 ( 이경우는 0.5). 오라클옵티마이저는다양한조건식의종류에대해선택도를통계정보에기반해서계산하는수식을내부적으로갖고있다. 그렇지만, 만일 dept 테이블이아직분석되지않아서통계정보가없는경우, 옵티마이저는내부적으로갖고있는디폴트값을선택도로지정한다 ( 예를들어, 0.01). 카디널러티앞의 dept 테이블의전체레코드건수가 1000일때, 앞에서설명한 loc = SEOUL 의선택도가 0.1 로계산되었을때, 조건을만족하는레코드건수는 1000 x 0.1, 즉 100 개로예상할수있다. 이와같이어떤연산을수행한결과로나오는레코드건수를 카디널러티 (cardinality) 라하는데, 정확한카디널러티를계산하는것은좋은실행계획을만드는데굉장히중요하다. 오라클옵티마이저의기본원리 10
예를들어, (T1 _T2) _T3 순서로테이블을조인할때 (T1 _T2) 의결과와 T3 를조인할때어떤조인방법을선택하는것이좋을지를결정하기위해서는 (T1 _T2) 의크기를정확하게알아야한다. 이를위해서는 (T1 _T2) 조인의결과레코드가몇개인지를예상할수있어야한다. 이를위해오라클옵티마이저는다양한연산의결과레코드의카디널러티를통계정보와수식에의해서계산한다. T1과 T2의조인조건이 T1.c1 = T2.c2( 이를 P 라표기 ) 라했을때, 앞에서설명한선택도계산공식에의해이조건식의선택도 Sel(P) 를먼저계산한후, 이조인의결과카디널러티는 Card(T1) x Card(T2) x Sel(P) 가된다. 예를들어, T1, T2의튜플수가각각 1000, 5000이고 Sel(P) 가 0.01이면, 조인의결과로생기는튜플수는 1000 x 5000 x 0.01 = 5000이된다. 그런데, Sel(P) 가조금이라도틀리면이후의전체적인비용산정이잘못되게된다. 오라클옵티마이저는다양한종류의연산에대해내부공식을사용해카디널러티를계산한다. 비용비용 (cost) 은테이블액세스, 조인등을수행하는데걸리는시간을의미하는데, 시간은주로디스크 I/O 수와 CPU 사용시간을고려한다. 비용은앞에서계산한통계정보와내부계산식에의해계산된다. 예를들어, T1 _T2를 Nested Loop 방식으로조인할경우조인비용은 (T1 의데이타블록수 ) + ((T1의레코드건수 )*(T2의액세스비용 )) 이된다. 이처럼오라클옵티마이저는모든연산에대해소요되는비용을계산하는수식을갖고있다. 오라클옵티마이저는이세가지예상값 (measure) 을기반으로, 현재의실행계획의예상비용을구한다. 오라클옵티마이저와관련한몇가지유용한기능이상에서오라클옵티마이저의내부동작원리를살펴보았다. 옵티마이저와관련해서오라클에서제공하는몇가지유용한기능들에대해서간단히알아보자 ( 이들기능에대한자세한설명은 < 참고자료 3> 을보기바란다 ). 이기능들은크게두가지 - 즉, 옵티마이저가사용할통계정보를수집 / 관리하는기능과옵티마이저의활동을자세히추적할수있는기능 - 로구분할수있다. 먼저, 통계정보수집 / 관리기능으로는 ANALYZE 명령과 DBMS_ STATS 패키지를들수있다. 비용계산이최적의실행계획을구하는데중요한역할을하기때문에, 이기능을사용해서 1) 어떤테이블이나칼럼에변경사항이많이발생해서새로통계정보를분석해야하는지, 2) 어떤칼럼에대해히스토그램을만들어야하는지에대한도움을받을수있다. 다음으로옵티마이저의활동추적과관련해서, 1) 주어진질의에대해서어떠한최적화과정을거쳤는지, 2) 최종적으로어떤실행계획을선택했는지, 그리고, 선택된실행계획대로수행했을때걸리는시간과자원이얼마나소요되었는지를확인하는기능들이제공된다. 우선옵티마이저의최적화과정을확인하려면, Event 10053 을이용하면된다. 이를위해서는 SQL*Plus에서다음 alter 명령을수행하면된다. 오라클옵티마이저의기본원리 11
SQL> alter session set events 10053 trace name context forever ; 이명령을수행하고나면, 현재세션에서수행하는모든질의에대해옵티마이저의최적화과정의모든정보가 $ora_home/admin/udump /xxx.trc 파일에기록된다. 이트레이스파일은오라클옵티마이저가고려한모든조인순서, 조인방법, 테이블액세스방법, 선택도, 카디널러티, 비용정보등을포함하고있다. 다음으로, 단순히옵티마이저가최종적으로선택한실행계획만확인하고싶으면, EXPLAIN 명령어나 SQL*Plus에서제공되는 Autotrace 기능을이용하면된다. 그리고, 옵티마이저가선택해서수행한실행계획의자세한성능정보를알고싶으면, SQL Trace, TKPROF 등의기능을이용하면된다. 오라클옵티마이저의한계와그원인오라클옵티마이저를포함한현재의관계형 DBMS 의옵티마이저는항상최적의실행계획을고르지는못한다. 옵티마이저의한계는실행계획생성모듈과비용산정모듈의동작원리에그원인이숨어있다. 실행계획생성모듈의제약우선, 실행계획생성모듈에서최적화를위해사용할수있는시간이제한적이라는점이다. 앞서살펴보았지만, 10개이상의테이블조인을포함하는질의의경우최적의실행계획을구하기위해옵티마이저가고려해야할탐색공간이너무많기때문에다양한형태의컷오프를수행한다. 이과정에서실제로최적의실행계획이고려되지않고잘려나갈수있다. 비용산정모듈의불완전성비용산정모듈에서는특정실행계획의비용을통계정보와내부적인비용산정모델을사용해서계산한다. 그런데, 옵티마이저에서사용하는통계정보와비용산정모델이불완전하다. 따라서, 옵티마이저는불완전한정보를바탕으로일종의추측 ( 또는가정 ) 을하는것이다. 예를들어, d.loc = SEOUL 의선택도를구하는과정을보자. d.loc에대한히스토그램통계정보가없으면, 옵티마이저는 모든값들이골고루분포되어있다 는가정하에 d.loc 칼럼의 distinct value 개수 ( 이를 NDV 라하자 ) 를기준으로해당조건의선택도를 1/NDV로계산한다. 그러나, 실제로는 d.loc에대해 skew된분포를보이면옵티마이저의실행계획비용산정이틀려지게된다. 비용산정모듈이한계를갖게되는또다른예로, SQL 에서바인드변수 (bind variables) 의사용을들수있다. 질의 Q1 에서 d.loc = SEOUL 대신에 d.loc = :loc_name 조건이사용되었으면, 데이타베이스에 loc 칼럼에아무리정확한통계치를갖고있어도선택도에대해서는일정한비율을가정할수밖에없다. 다른예로서, 다음질의를살펴보자. Q2: select * from emp where job_title = vice_president and salary < 40000 데이타베이스에는 job_title, salary 칼럼모두에대해정확한히스토그램정보를유 오라클옵티마이저의기본원리 12
지하고있고, job_title = vice_president 의선택도가 0.05이고, salary < 40000 의선택도는 0.4 였다. 이때옵티마이저는 emp 테이블에서 where 절의조건의전체선택도를 0.05 x 0.4, 즉 0.02로계산한다. 이는 각칼럼의값들의분포는서로독립적이다 는가정에기반하다. 그러나, 부사장이면서연봉이 40,000 이하인경우는거의없기때문에실제선택도는 0에가까울것이다. 결국앞의가정은이와같이서로밀접한상관관계가있는두칼럼에대한선택도를구할때문제가되는것이다. 마지막예로, 조인연산에대한비용을예측할때, 이조인을수행할수있는메모리공간을고정크기로가정하고, Nested Loop, Sort Merge, Hash Join의비용을산정한다. 그러나, 질의를수행할때실제비용은이용가능한메모리의양에따라크게차이가날수있다. 결론적으로, 옵티마이저의정확도는비용계산의정확도에따라좌우되는데, 참고하는통계정보가부족하거나계산과정의몇가지가정들이실제데이타분포와실행계획의런타임환경과차이가있기때문에정확도에문제가발생하는것이다. 힌트기능을이용한옵티마이저동작제어이와같은현재의옵티마이저의한계를보완하기위해, 오라클에서는 SQL 에힌트 (hint) 를추가해서사용자가옵티마이저가선택하는실행계획에영향을줄수있도록하고있다. 옵티마이저가최선이아니차선의실행계획을선택하는경우, 사용자가 SQL 에힌트를주어서실행계획을베스트플랜으로만들도록하는것이목적이다. 힌트를제공하는것이옵티마이저의기능이떨어지는것을의미하는것은아니다. 어떤 DBMS의옵티마이저도완전할수는없기때문에, 힌트기능의제공은반드시필요하다. 옵티마이저의실행계획은결국조인순서, 조인방법, 테이블액세스경로를결정하는것이기때문에, 힌트의종류도크게이세가지를제어하는것으로구분할수있다. 다음예는 Q1 에대해힌트를사용한예를보여주고있는데, 처음 ordered 는 from 절에나와있는순서대로조인순서를정하는것이고, use_nl 의경우dept 테이블을 inner table로사용할때 Nested Loop 방식만을사용하도록지정하고, full(e) 는 emp 테이블은항상 Full Table Scan을통해서액세스하도록지정하는것이다. 오라클에서제공하는다양한힌트의종류와자세한의미에대한설명은 < 참고자료 5> 를보기바란다. Q1 : select /*+ ordered use_nl(d) full(e) */ ename, sal : Oracle 힌트기능사용예 from emp e, dept d where e.deptno = d.deptno and d.loc = SEOUL 오라클 SQL 에서힌트를제공하는또다른목적은, 사용자가다양한실행계획을수행해봄으로써어떤데이타액세스경로가도움이되는지를테스트해볼수도있다. 힌트의사용은아주불가결한경우말고는사용을조심해야한다. 실제로이힌트기능이남용되는경우가많다. 오라클옵티마이저의기본원리 13
힌트를사용하게됨으로써데이타베이스환경의변화 ( 예를들어, 테이블의크기변화, 인덱스의추가 / 삭제 ) 가발생할때옵티마이저가더나은실행계획을선택하는것을방해할수도있다. 실제로 Oracle E-Business Suite 11i의경우포함된 27 만개의 SQL 중에서 0.3% 만이힌트를포함하고있다고한다. SQL 튜닝과옵티마이저의관계 SQL 튜닝은특정 SQL 질의의수행시간을단축하기위해사용자가취하는다양한방법을통칭한다. SQL 튜닝의범위는굉장히포괄적인데, 옵티마이저와관련한방법으로는 SQL 재작성, 힌트사용, 새로운인덱스추가, 통계데이타의추가 / 갱신등을통해서옵티마이저가더욱더효율적인실행계획을생성하도록하는것이다. SQL 재작성사용자가원하는데이타를질의하는방법은실제로매우다양할수있다. 극단적인예로, C. J. Date는한 SQL 문을 50 가지이상의다른 SQL 문으로표현이가능함을보여준다 (http://www.dbpd.com/vault/9807xtra.htm 참조 ). SQL 재작성을통한 SQL 튜닝은원래의 SQL 문을, 같은결과를내지만, 옵티마이저가더효과적인실행계획을생성할수있는 SQL 문으로바꾸는방법이다. 힌트사용앞에서언급한것처럼, 힌트기능을사용해서옵티마이저가선택하는실행계획을바꾸는방법이다. 새로운인덱스추가 SQL 문의효율적인처리를위해서는특정테이블의특정칼럼값을이용해서해당데이타를빨리찾아야하는데, 인덱스가없기때문에옵티마이저가어떤실행계획을선택하더라도그 SQL 문은느릴수밖에없는경우가있다. 이와같은상황에서는새로운인덱스생성을통해서옵티마이저가해당인덱스를이용하는새로운실행계획을선택하도록할수있다. 통계데이타의추가 / 갱신앞에서설명한것처럼, 오라클옵티마이저의비용산정모듈에서는테이블, 칼럼, 인덱스등에대한통계정보를이용해서선택도, 카디널러티등을구하고이를통해서궁극적으로실행계획의비용을계산한다. 그런데, 만일특정테이블 / 칼럼에대한통계정보가없거나, 오래전에만들어진경우는비용계산이부정확하게되고, 따라서옵티마이저가선택하는실행계획이실제로는안좋은실행계획일수가있다. 이를해결하기위해서는특정통계정보를추가하거나새로갱신해주어서옵티마이저가정확한비용산정을통해서더나은실행계획을선택하도록해주는방법이다. 옵티마이저기술의발달은궁극적으로 SQL 튜닝관련직종을없앨수도있다. 그러나, 다행인지불행인지몰라도, 향후 10년사이에이런일이벌어지지는않을것같다. 오라클옵티마이저의기본원리 14
향후옵티마이저의기술의발전방향비록옵티마이저기술은지난 30 년간꾸준히발전해오면서인간이만든가장지능적인소프트웨어이지만, 앞으로도끊임없이기술발전이필요한분야이다. 사용자가사용하는 SQL 질의가점점더복잡해지고, 데이타베이스에서다루는데이타양이엄청난속도로늘어나고, 새로운데이타의종류를데이타베이스에서다루어야하기때문에, 옵티마이저기술의중요성은더욱더커질것이다. 향후옵티마이저기술의주요발전방향은다음의분야가될것이다. 여기서나열한분야는주로학계에서연구가활발히진행중이거나많은진전이있는분야를중심으로판단한필자의개인적인의견이다. 질의변환오라클옵티마이저와관련해서간략히설명했지만, 현재의옵티마이저가처리하는질의변환의형태는상대적으로정형화되고단순한형태의질의변환만을주로수행한다. 단일 SQL 블록 (Select-From-Where) 에대한질의변환이외에, 복잡한중첩질의 (nested query) 를단일질의로변환하는방법, 중첩질의내의각 SQL 블록을결합한효과적인실행계획생성이가능해질것이다. 비용산정을위한정확한통계정보관리실행계획에대한정확한비용산정이옵티마이저기술의핵심이다. 따라서, 지금옵티마이저가참고하는통계정보보다더정교하고복잡한통계정보의관리 / 유지기법들이도입될것이다. 예를들어, 애트리뷰트값들의분포가서로독립적이라는가정대신에상호연관성이깊은칼럼들에대한히스토그램정보를효과적으로수집 / 활용하는기술이도입될수도있다. 런타임시동적질의최적화현재의옵티마이저기술은, 질의수행환경에상관없이옵티마이저가선택한실행계획을그대로실행한다는측면에서정적 (static) 질의최적화방법이다. 앞으로는고정된실행계획을그대로수행하는것이아니라, SQL 실행단계의상황에따라실행계획을융통성있게바꾸는기술이개발될것이다. 실제로 Oracle9i의경우초보적인형태의동적질의최적화기능을제공하고있다. 학습하는옵티마이저현재의옵티마이저기술은통계정보의변화나사용자의힌트가없다면, 같은 SQL 질의에대해서는항상똑같은실행계획을선택할것이다. 옵티마이저가특정질의에대해생성한실행계획을실제수행했을때, 예상과달리좋지않은성능을보이면, 옵티마이저가다음번에최적화를수행할때는이전의실행계획을제외한다른대안을찾게되는것이다. 오라클옵티마이저의기본원리 15
참고자료 1. Ken Jacobs, Query Optimization, Oracle Magazine 2002 July/August (http://www.oracle.com/oramag/oracle/02-jul /o42dba.html) 2. Oracle Corp., Query Optimization in Oracle9i, Technical White Paper (http://otn.oracle.com/products/bi/pdf/o9i_optimization_twp.pdf) 3. Oracle Corp., Oracle9i Performance Tuning Guide and Reference Release 2(9.2) (http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96533/toc.htm) 4. Patricia Selinger 외다수, Access Path Selection in a Relational Database System, ACM SIGMOD 1979 5. Surajit Chaudhuri, An Overview of Query Optimization in Relational Systems, AMM PODS Tutorial 1998v 6. Yannis E. Ioannidis, Query Optimization, Handbook for Computer Science(Chapter 45), CRC Press 7. Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom, Database Systems: The Complete Book, Prentice Hall, 2001 8. Raghu Ramakrishnan and Johannes Gehrke, Database Management Systems(2nd Edition), McGraw-Hill, 1999 9. Abraham Silberschatz, Henry F. Korth and S. Sudarshan, Database System Concepts(4th edition), McGraw-Hill, 2001 오라클옵티마이저의기본원리 16
한국오라클 ( 주 ) 서울특별시강남구삼성동 144-17 삼화빌딩대표전화 : 2194-8000 FAX : 2194-8001 한국오라클교육센타서울특별시영등포구여의도동 28-1 전경련회관 5 층, 7 층대표전화 : 3779-4242~4 FAX : 3779-4100~1 대전사무소대전광역시서구둔산동 929 번지대전둔산사학연금회관 18 층대표전화 : (042)483-4131~2 FAX : (042)483-4133 대구사무소대구광역시동구신천동 111 번지영남타워빌딩 9 층대표전화 : (053)741-4513~4 FAX : (053)741-4515 부산사무소부산광역시동구초량동 1211~7 정암빌딩 8 층대표전화 : (051)465-9996 FAX : (051)465-9958 울산사무소울산광역시남구달동 1319-15 번지정우빌딩 3 층대표전화 : (052)267-4262 FAX : (052)267-4267 광주사무소광주광역시서구양동 60-37 금호생명빌딩 8 층대표전화 : (062)350-0131 FAX : (062)350-0130 고객에게완전하고효과적인정보관리솔루션을제공하기위하여오라클사는전세계 145 개국에서제품, 기술지원, 교육및컨설팅서비스를제공하고있습니다. http://www.oracle.com/ http://www.oracle.com/kr