Oracle Magazine 2003 summer

Size: px

Start display at page:

Download "Oracle Magazine 2003 summer"

지섭 구
6 years ago
Views:

1 관계형 DBMS 옵티마이저의 한계와해결방안 글 이상원 < 성균관대학교교수 > 030 ORACLE KOREA MAGAZINE

:: TECHNOLOGY :: 관계형 DBMS의두뇌역할을담당하는비용기반옵티마이저 (Cost Based Optimizer : CBO) 의근본적인한계들을정확히아는것은필요한경우에 SQL의정확한튜닝을위해서반드시필요하다. 이한계들을극복하기위해관계형 DBMS 벤더들은꾸준한기능개선노력을하고있지만, 해결책이결코쉽지않다.

관계형 DBMS 옵티마이저는기본적으로통계정보, 선택도, 카디널리티, 비용순으로특정실행계획 (Execution Plan) 의수행비용을예측한다. 이글에서는이관계형 DBMS의옵티마이저가비용을계산하는과정에서발생할수있는오류의종류와원인을체계적으로알아보고, 각각의오류에대해일반적인해결방안을설명하겠다.

2 :: TECHNOLOGY :: 관계형 DBMS의두뇌역할을담당하는비용기반옵티마이저 (Cost Based Optimizer : CBO) 의근본적인한계들을정확히아는것은필요한경우에 SQL의정확한튜닝을위해서반드시필요하다. 이한계들을극복하기위해관계형 DBMS 벤더들은꾸준한기능개선노력을하고있지만, 해결책이결코쉽지않다. 이글은SQL 튜닝전문가나 SQL 개발자등사용자입장에서이문제의근본적인이유를제대로이해하고, 이를해결하기위해어떤기능들이개별 DBMS에서제공되는지를파악하며, 현재관계형 DBMS의옵티마이저의한계를해당기능들을이용해서어떻게극복할것인지대해체계적으로생각하는방법을제공할것이다. 관계형 DBMS 옵티마이저는기본적으로통계정보, 선택도, 카디널리티, 비용순으로특정실행계획 (Execution Plan) 의수행비용을예측한다. 이글에서는이관계형 DBMS의옵티마이저가비용을계산하는과정에서발생할수있는오류의종류와원인을체계적으로알아보고, 각각의오류에대해일반적인해결방안을설명하겠다. 이글은이한계에대한완전한해결책을제시할수는없지만, 사용자입장 ( 특히 SQL 튜닝전문가나 SQL 개발자 ) 에서1) 이문제의근본적인이유를제대로이해하고, 2) 이를해결하기위해어떤기능들이개별 DBMS에서제공되는지를파악하며, 3) 현재관계형 DBMS의옵티마이저의한계를해당기능들을이용해서어떻게극복할것인지대해체계적으로생각하는방법을제공할것이다. 또한이한계들을극복하기위해서최근대두되고있는 스스로학습하는옵티마이저 의기본적인프레임워크와이를구현하는방안들에대해오라클 DBMS를중심으로설명할것이다. 이글의이해를돕기위해 주1 : 이기사는관계형데이타베이스개념에일정정도익숙한독자들을대상으로작성되었기때문에초보자는이해하기가어려울수도있다. 주2 : 여기서소개하는관계형DBMS의한계는특정DBMS의문제가아니라, 상용 DBMS별로조금씩차이는있겠지만, 거의모든 DBMS 에공통적으로나타나는문제점이다. 주3 : 여기서주로관계형DBMS 옵티마이저기술의근본적인한계를논하겠지만, 그렇다고해서이글이관계형 DBMS 옵티마이저기술을폄하하는것은절대아니다. 또한특정 DBMS의경우규칙기반옵티마이저 (Rule Based Optimizer) 모드도제공하는데, 여기서비용기반옵티마이저 (Cost Based Optimizer) 의한계를언급한다고해서비용기반옵티마이저가규칙기반옵티마이저에비해나쁘다고단정하는것은아니다. 반대로, 규칙기반옵티마이저는앞으로는복잡한 SQL을제대로최적화하지못하고, 또한새로도입된관계형 DBMS의최신기술들을활용할수없기때문에머지않아사라지게될기술이라본다. 주 4 : 이기사는관계형 DBMS의아주근본적인문제점들의기본원인을설명한다. 그런데이기술은워낙광범위하고복잡한기술이라모든세부적인내용을다다룰수도없을뿐더러, 필자도관계형 DBMS 옵티마이저의모든사항을완전히이해하고있지않다. 미진하고잘못된부분은모두필자의책임임을밝혀둔다 SUMMER 031

3 관계형 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의경우에도질의결과를구하는방법, 즉실행계획은다양할수있다. < 그림1> 은두가지실행계획 P1과 P2를보여주고있다. P1은우선loc = SEOUL 조건을만족하는 dept 레코드를인덱스를이용해서찾고, 각 dept 레코드에대해 deptno 값이일치하는 emp의레코드를인덱스를이용해서찾아값을출력한다 ( 중첩루프 (Nested Loop) 조인방법이용 ). 한편, P2는emp/dept 테이블을전체테이블스캔해서 (Full Table Scan) 이들을정렬합병 (Sort Merge) 방식으로조인해서질의처리를수행한다. 주목할점은, 이두실행계획모두정확한질의결과를구하지만, 두방식의수행시간에는차이가많이날수도있다는점이다. 예를들어, P1 방식은1초에원하는결과를구하는반면, P2 방식은 1시간이걸릴수도있다. < 그림 2> 는관계형 DBMS 옵티마이저의내부아키텍처를나타낸것으로, SQL 질의는크게다음 4단계를거쳐서최적화된다 (< 그림 2> 에서번호가매겨진부분 ). 1. SQL : 사용자가입력한SQL 2. 실행계획생성 (Plan Generator) : 주어진 SQL을수행할수있는실행계획들 (P1, P2,..., Pn) 을생성 3. 비용산정 (Cost Estimator) : 각실행계획에대해옵티마이저의비용산정 (Cost Estimator) 모듈이예상비용을계산 4. 실행계획선택 : 각실행계획들의비용을비교해서가장좋은비용을선정이와같이, 다양한실행계획들에대해각실행계획의비용을예측해서최선의실행계획을선택하는비용기반최적화 (Cost Based Optimization) 기법이 Oracle, DB2, MS SQL Server를포함한모든상용 DBMS 옵티마이저의기본아키텍처이다. 이아키텍처는관계형 DBMS의옵티마이저는 IBM DB2의모태인 System-R 프로토타입시스템을개발할당시에처음으로고안되었고, 이아키텍처를주도적으로제안한 IBM의여성전산학자 Pat. Selinger의이름을따서, Selinger- 스타일옵티마이저 라부른다 ([ 참고문헌 2], 본격적으로관계형 DBMS의옵티마이저에대해깊이있게공부하고싶은독자들은이논문을반드시읽어야한다 ). 옵티마이저의생명 - 비용산정의정확성 < 그림 1> 질의 Q1에대한두가지실행계획예 P1, P2 이외에도질의 Q1을수행할수있는많은실행계획이있을수 있다. 실행계획이란, 여러개테이블들의조인에대해, 특정한조인순서 (Join Ordering), 조인방법 (Join Method), 그리고테이블액세스방법 (Access Method) 을선택하는것이다. 옵티마이저는가능한실행계획들을모두검토하고, 이중에서가장효과적으로, 즉가장빨리Q1의결과를구할수있는실행계획을결정한다. 옵티마이저가최적의실행계획을찾는과정을 질의최적화 (Query Optimization) 또는단순히 최적화 라고한다. 관계형 DBMS의옵티마이저의기본원리에대해서는본지의 2002년겨울호에본저자가기고한글 [ 참고문헌1] 과거기에언급된여러문헌들을참고하기바란다. 그런데여기서주목할점은, 옵티마이저가실행계획들을비교할때사용하는기준은 예상비용 이라는점이다. 앞의예에서 P1과P2 방법을실제로수행해보고더좋은방법을결정하는것이아니라, 옵티마이저가갖고있는통계정보를활용해서P1과P2로수행했을때어느실행계획의예상비용이적은가를보고서이를실제로수행하게되는것이다. 결국옵티마이저의생명은예상비용의정확성에달려있다. 즉, < 그림 2> 의비용산정 (Cost Estimator) 모듈이얼마나정확하게비용을산정하느냐에옵티마이저의정확성이결정된다. 많은경우에, 옵티마이저의비용산정모듈은아주정확하게비용을예상하기때문에, 현재 90% 이상의실제 SQL들에대해서옵티마이저는최선의실행계획을선택한다. 얼마나위대한기술인가! 그런데, 이비용산정모듈이항상정확한예상비용을계산하는것은아니다. 그렇다면왜그럴까? 비용산정의정확성에영향을미치는요소 < 그림2> 에서알수있듯이, 특정실행계획의비용을결정하는요소는크게다음3가지이다 ( 물론, 이들 3가지요소이외에도비용산정의정확성에영향을 032 ORACLE KOREA MAGAZINE

4 :: TECHNOLOGY :: < 그림 2> 관계형 DBMS 옵티마이저의내부아키텍처 미치는미묘한여러가지문제들 - Bind 변수, 리터럴변수, 특수한조건식 %xxx%, CPU와저장장치등의접근비용의가정 [ 참고문헌 8] 등- 이있지만, 이런변수들은여기서는다루지않겠다. 자세한내용은 [ 참고문헌 5] 를참고하기바란다 ). 대해서좀더자세히알아보고, 이들오류의일반적인해결책에대해서도간단히알아보도록하자. 지금부터는오라클 DBMS를중심으로설명하겠지만, DB2나MS SQL Server의경우에도유사하다고이해하면될것이다. 통계정보 : 데이타딕셔너리 (Data Dictionary) 비용산정모듈에서의기본가정들 : 선택도 (Selectivity) 와카디널리티 (Cardinality) 계산식 실제질의수행환경에대한가정 : 비용계산식의런타임수행환경 ( 예 : 메모리 ) 에대한가정 < 그림 2> 의비용산정모듈의동작순서를따르면 1) 통계정보를바탕으로, 2) where 절의조건식과조인조건에대해선택도와카디널리티를계산하고, 3) 이를바탕으로각테이블에대한액세스방법 ( 예 : 전체테이블스캔또는인덱스 ) 과조인방법 ( 예 : 중첩루프나정렬합병, 해시 ) 의비용을계산한다. 그런데, 이세가지과정모두에서잘못된비용을산정할수있는가능성이있다. 즉, 현재의통계정보가데이타베이스의실제데이타들의분포와다를수있고, 선택도와카디널리티계산공식의가정이실제데이타의분포와일치하지않고, 비용계산공식에서가정하고있는수행환경이실제질의수행환경과는많은차이가날수있는것이다. 그러면이제이세가지과정에서발생할수있는오류의원인과종류에 통계정보의수준 일반적으로관계형 DBMS의경우, 시스템카탈로그 또는 데이타딕셔너 리 라는이름으로테이블 / 인덱스 / 칼럼들에대한통계정보들을유지한다 < 표1>. 그러나이통계정보를아주정확하게관리하는것은비용이많이들기 때문에, 테이블에튜플들이삽입 / 삭제 / 갱신될때마다자동적으로유지, 관 리되지않고, 대신에사용자가주기적으로통계정보를수집하기위한명령 을수행한다. 구 분 필요한통계정보 테이블 NUM_ROWS, BLOCKS, AVG_ROW_LENGTH 등 인덱스 BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR 등 칼 럼 LOW_VALUE, HIGH_VALUE, DISTINCT_NUM, NULL_NUM, AVG_COL_LENGTH, HISTOGRAM 등 < 표1> 옵티마이저가필요로하는기본적인통계정보들 질의에서참조된특정테이블, 칼럼, 인덱스들에대한통계정보를딕셔 2003 SUMMER 033

조건식상호독립 (Predicate Independence) 조인독립 (Join Independence) 그런데이가정들은많은경우에잘성립하지만, 모든경우에성립하지는않는불완전한가정이다. 따라서, 이불완전한가정으로인해서선택도와카디널리티, 비용을잘못산정하게될수도있는것이다. 그러면이들각가정의의미와문제점, 그리고해결방안에대해알아보자.

5 조건식상호독립 (Predicate Independence) 조인독립 (Join Independence) 그런데이가정들은많은경우에잘성립하지만, 모든경우에성립하지는않는불완전한가정이다. 따라서, 이불완전한가정으로인해서선택도와카디널리티, 비용을잘못산정하게될수도있는것이다. 그러면이들각가정의의미와문제점, 그리고해결방안에대해알아보자. 너리에서참조해야하는데, 이들에대한통계정보의수준은크게다음과같은단계로구분할수있다. 1. 디폴트 (No Statistics) 단계테이블과인덱스를생성한뒤, 사용자가전혀통계정보를수집하지않은경우인데, 이경우에옵티마이저는 < 표 1> 에서필요로하는정보들에대해기본적인가정을한다. 이경우는실제데이타분포와완전히틀릴수있다. 따라서사용자는다음단계인기본통계정보는유지할필요가있다. 균등분포가정균등분포가정은어떤테이블의특정한칼럼에대해튜플들이갖는 값이골고루분포되어있다 는가정이다. 예를들어, d.loc에대한히스토그램통계정보가없으면, 옵티마이저는이균등분포가정에기반해서 d.loc칼럼의기본단계의서로다른값 (Distinct Value) 개수 ( 이를 NDV라하자.) 를기준으로해당조건의선택도를 1/NDV로계산한다. 그러나, 실제데이타가 d.loc에대해치우친 (Skewed) 분포를보이면, 옵티마이저의선택도, 카디널리티, 비용이순차적으로틀리게되는것이다. 따라서, 이가정이일반적으로맞지않은칼럼에대해서는사용자가해당칼럼에대해히스토그램정보를유지해야한다. 2. 기본 (Base Statistics) 단계실제데이타를분석해서테이블, 인덱스, 칼럼들에대해기본적인요약된통계정보를유지하는것이다. 이를위해서사용자는 Analyze table/index 등의명령을사용해야한다. 3. 히스토그램 (Histogram) 단계특정칼럼에대해서데이타의분포를기본단계보다훨씬더정확하게유지하는것이다. 해당칼럼에대해값들의분포가고르게분포되지않고 (Non- Uniform), 치우친 (Skewed) 경우에해당칼럼에대해서는히스토그램정보를생성해야한다. 특정한시점에기본또는히스토그램단계의통계정보를수집한이후에, 실제테이블에데이타가아주많이변경된경우에는새로이통계정보를재생성해주어야한다. 그렇지않으면, 아무리옵티마이저의비용산정모듈이지능적이라도결국은틀린비용예측을하게되고, 궁극적으로좋지않은실행계획을선택하게된다. 비용산정모듈에서사용하는기본가정들 < 그림 2> 의비용산정모듈에서보듯이, 옵티마이저는앞의통계정보를기반으로선택도와카디널리티를계산하는내부공식을갖고있다. 이내부공식들은다음세가지가정에기반한다. 균등분포 (Uniform Distribution) 조건식상호독립가정조건식상호독립가정은하나의테이블에대해주어진 where절의각조건식이서로독립적이라는것이다. 예를들어, 질의 select * from emp where deptno = 10 and salary < 의경우, deptno와salary 각각에대해완전한통계정보를유지해서, deptno = 10의선택도가 0.1이고 salary < 40000의선택도는 0.4였다. 일반적으로조건식 P1과 P2의선택도를 Sel(P1), Sel(P2) 라할때, 각칼럼의값들의분포는서로독립적이다 는가정에기반해서,(P1 AND P2) 조건식의결합선택도 (Combined Selectivity) 는Sel(P1) * Sel(P2) 로계산하게된다. 이때옵티마이저는 emp 테이블에서 where 절조건의전체선택도를 0.1 x 0.4, 즉0.04로계산한다. 실제로 deptno와salary는별로상관관계가없기때문에, 이가정에따라계산된선택도 0.04는거의정확하다고볼수있다. 그러나질의 select * from emp where job_title = 부장 and salary < 의경우, job_title = 부장 과 salary < 40000에대해정확한선택도는 0.2이고 salary < 40000의선택도는 0.4를유지하고있지만, 부장이면서연봉이 40000이하인경우는거의없기때문에실제선택도는 0에가까울것이다. 즉, 이가정은실제데이타분포상서로밀접한상관관계가있는두칼럼에대한선택도를구할때아주잘못된선택도를산정하게되고, 결국잘못된카디널리티와예상비용을산정하게된다. 이에대한해결책은이러한조건식상호독립이성립하지않는칼럼들의조합에대해히스토그램을별도로유지하는것이다. 그렇지만, 이는유지해야할히스토그램의정보가기하급수적으로늘어나게되는문제가있기때문에현실적인대안이되기는힘들다. 오라클은 Oracle9i Database부터동적샘플링 (Dynamic Sampling) 을제공해서좀더정확한선택도계산을가능하게하고있다. 자세한내용은 [ 참고문헌 6] 을보기바란다. 034 ORACLE KOREA MAGAZINE

:: TECHNOLOGY :: 또다른해결책으로두조건식칼럼의함수적종속성 (Functional Dependency) 이나다중값종속성 (Multivalued Dependency) 을이용해서의미적질의최적화를수행할수도있지만, 이또한현단계에서는현실적인대안은되지못하고이론적인해결책일뿐이다.

deptno 의결과카디널리티는 Card(emp) x Card(dept) x 1/MAX(emp.deptno의NDV, deptno.deptno의 NDV) 가된다. 이가정은조인되는두칼럼이주키 (primary key)/ 외래키 (foreign key) 관계 (N:1의관계) 에있다고볼때가장잘들어맞는다. 만일에, emp.deptno와 dept.

6 :: TECHNOLOGY :: 또다른해결책으로두조건식칼럼의함수적종속성 (Functional Dependency) 이나다중값종속성 (Multivalued Dependency) 을이용해서의미적질의최적화를수행할수도있지만, 이또한현단계에서는현실적인대안은되지못하고이론적인해결책일뿐이다. 중첩루프조인의가정중첩루프의경우조인비용 ( 즉, 필요한디스크 I/O 수 ) 을다음과같이산정한다. 조인독립가정조인독립은조인되는두테이블 T1과 T2에대해, T1의한튜플이 T2의모든튜플들과똑같은확률로조인된다고가정 ( 반대의경우도성립 ) 하는것이다. 예를들어, 질의 select * from emp, dept where emp.deptno = dept.deptno 의결과카디널리티는 Card(emp) x Card(dept) x 1/MAX(emp.deptno의NDV, deptno.deptno의 NDV) 가된다. 이가정은조인되는두칼럼이주키 (primary key)/ 외래키 (foreign key) 관계 (N:1의관계) 에있다고볼때가장잘들어맞는다. 만일에, emp.deptno와 dept.deptno가거의겹치는값이없다면, 조인결과튜플의수도거의 0에가까울것이다. 또다른극단적인경우는, 아래예처럼, 양쪽칼럼모두특정값이많이나타나는경우로, 이들이조인될때는조인조건의선택도는 1에가까울것이다. 즉, 조건식의선택도는 1/3이아니라, 실제 18/36, 즉1/2이된다. 이조인독립가정에위배되는경우에찾아내기힘들고, 이는실제데이타분포를확인한후수동으로찾아야하고, 이를바탕으로사용자가힌트 (Hint) 기능을사용함으로써 SQL 튜닝을수행할수밖에없다. Nested Loop Join Cost = cost of accessing outer table + (cardinality of outer table * cost of accessing inner table ) 이수식에따르면, 중첩루프의안쪽테이블 (inner table) 의데이타는매번디스크에서읽어와야한다는가정이숨어있는것이다. 그러나실제일정정도이상의버퍼캐시를갖고있고인덱스를통해서안쪽테이블을액세스하는경우, 항상디스크 I/O가발생하는것은아니고인덱스블록들이버퍼캐시에존재할확률이높기때문에예상치보다훨씬적은물리적인디스크I/O가발생하게된다. 따라서, 위수식은질의수행환경에있어서이용가능한메모리자원에대해최악의가정을하고있는것이다. 결국은중첩루프조인방법의비용을... deptno deptno emp dept 메모리실행환경에대한가정 현재의관계형 DBMS들은대부분중첩루프 (Nested Loop), 정렬합병 (Sort Merge), 해시 (Hash) 의세가지조인방법을지원하고있다. 그런데, 이들각각의조인방법에대한비용계산식에서는실제수행시에사용가능한메모리자원에대해특정한가정을하게된다. 다음두가지가대표적인경우이다. 중첩루프조인시해당데이타및인덱스블록은디스크로부터읽어야한다는가정 정렬합병또는해시조인에필요한메모리크기에대한가정 2003 SUMMER 035

비용산정에영향을미치는요소 세부요소 해결방안 통계정보의정확성 통계정보와실제데이타의불일치 적절한통계정보의생성및주기적인갱신 비용산정모듈의가정과 균등분포가정 히스토그램 (Histogram) 생성 실제데이타분포의불일치 조건식상호독립가정 동적샘플링 (Dynamic Sampling) 조인독립가정 자동적인해결책없음 ; 정확한데이타분석후, 힌트기능을이용한 SQL 튜닝

7 비용산정에영향을미치는요소 세부요소 해결방안 통계정보의정확성 통계정보와실제데이타의불일치 적절한통계정보의생성및주기적인갱신 비용산정모듈의가정과 균등분포가정 히스토그램 (Histogram) 생성 실제데이타분포의불일치 조건식상호독립가정 동적샘플링 (Dynamic Sampling) 조인독립가정 자동적인해결책없음 ; 정확한데이타분석후, 힌트기능을이용한 SQL 튜닝 조인비용계산식과중첩루프조인버퍼캐시가정 OPTIMIZER_INDEX_CACHING,OPTIMIZER_INDEX_COST_ADJ 조정 실제수행환경의차이 Sort/Hash 메모리가정자동적인해결책없음, PGA_TARGET_SIZE 조정 < 표 2> 옵티마이저의기본적인문제점및해결책 실제보다너무높이책정하기때문에정렬합병이나해시조인방법이선택될가능성이높은것이다. 이에대한해결책으로 init.ora 파일에서 OPTIMIZER_INDEX_CHACHING ( 디폴트로 0) 값과 OPTMIZER_INDEX_COST_ADJ( 디폴트로 100) 의값을조정해서인덱스를이용한중첩루프조인의비용을보정하는방법이있다. 물론, 정확한값을시스템전체적으로얼마로조정할것인가의문제는또다른어려운문제이다. 정렬합병또는해시조인의가정 정렬합병또는해시조인의경우, Oracle9i Database 이전에는각오라클인스턴스마다고정된 SORT_AREA_SIZE 또는 HASH_AREA_SIZE가실제할당되기때문에이두조인에대한비용은상대적으로정확했다고볼수있다. 그러나, Oracle9i Database부터는시스템전체적으로 PGA_TARGET_SIZE 라는목표치를정해놓고, 동적으로각사용자마다필요에의해정렬이나해싱을위한공간을동적으로할당하기때문에, 이들비용을계산할때특정한메모리공간을수식에의해가정해야만한다. 필자도이경우에옵티마이저가메모리공간에어떤가정을하고, 동적으로어떻게할당되는지에대해서는자세히알방법이없다. 따라서, 이경우에가정한메모리공간보다실제수행시메모리가훨씬많거나적을경우, 옵티마이저가예측한공간보다작을경우예상비용이틀리게된다는점은분명하다. 이상의내용을정리하면, 관계형DBMS의옵티마이저는기본적으로통계정보, 선택도, 카디널리티, 비용순으로특정실행계획의수행비용을예측한다. 앞에서이관계형 DBMS의옵티마이저가비용을계산하는과정에서발생할수있는오류를체계적으로알아보았고, 각각의오류에대해일반적으로어떤식으로해결할것인지에대해알아보았다. < 표2> 는이상에서살펴본관계형 DBMS 옵티마이저의비용산정에영향을미치는요소와해결방안을정리한것이다. 자동적인해결책 - 스스로학습하는옵티마이저를향해 지금까지는관계형 DBMS 옵티마이저의문제점과이에대해개발자, DBA, 또는 SQL 튜닝전문가가취할수있는해결방안에대해알아보았다. 그렇지만, 관계형 DBMS는궁극적으로는사용자의개입없이도항상최선의실행계획을생성할수있어야할것이다. 현재많은관계형 DBMS 벤더들은이목표를위해새로운기술개발을경주하고있다. 여기서는이러한노력들중에서머지않아실용적으로사용될 스스로학습을통해더효율적인실행계획을생성하는옵티마이저 (Self- Learning Optimizer) 에대해알아보고자한다. 이내용은필자가대학 036 ORACLE KOREA MAGAZINE

8 :: TECHNOLOGY :: SQL SQL re-written with Hint 4. Improvement Query Optimizer Statistics Learned Statistics 3. Feedback Cost model Adjustment Execution Plan SQL Execution Engine Query Results Estimated Cardinality Actual Cardinality 1. Monitoring Estimation Error Analyzer 2. Analyzing < 그림 3> 스스로학습하는옵티마이저의프레임워크 원에재학중생각한아이디어이기도하지만, 현재학계나산업계에서활발히연구가진행중이다. < 그림 3> 은스스로학습하는옵티마이저의일반적인프레임워크이다. 그림왼편은지금까지의일반적인옵티마이저의동작과정이다. 앞에서설명한옵티마이저의여러가지한계점으로인해특정SQL에대해좋지않은실행계획을생성하는경우, 사용자가원인을분석해서 < 표2> 와같은조처를취하지않는경우해당질의에대해서는항상똑같은실행계획을생성하고수행하게된다. < 그림 3> 의오른편은스스로학습하는옵티마이저의개념적인동작과정을보여주는데, 크게다음4단계로이루어진다. 모니터링 (Monitoring) 분석 (Analyzing) 피드백 (Feedback) 개선 (Improvement) 분석단계분석단계는옵티마이저의예상치 ( 예를들어, 로우소스별결과카디널리티 ) 와모니터링단계의실제값과비교를해서, 특정로우소스에서예상치대비실제값사이에심각한차이가발생한경우최적화과정에서어떤이유로예상치가틀렸는지 ( 예를들어균등분포의오류, 조인독립가정의위배 ) 를분석하는단계이다. 그런데, 다양한옵티마이저오류의원인들과이원인들이어떤식으로실행계획의선택에나쁜영향을미쳤는지를자동적으로정확하게분석하기란쉽지않다. 피드백단계피드백단계는앞에서분석된오류의원인을제거또는보정하기위해통계치, SQL 문, 옵티마이저의내부비용모델등을조정하는단계를일컫는다. 이단계또한앞에서분석된원인을어떤식으로보정하는것이완벽한보정인지를결정하기란쉽지않다. 모니터링단계이단계는옵티마이저가선택한실행계획을수행했을때, 실행계획상의각로우소스 (row source) 의실행정보 ( 예를들어실제조건을만족하는튜플수, 중첩루프조인의결과튜플수및논리적 / 물리적버퍼읽기등 ) 를정확하게산출한다. 이단계는 SQL 실행엔진모듈에서담당해야하며, 이모니터링을위해서약간의 ( 일반적으로 5% 미만 ) 오버헤드가발생한다. 개선단계개선단계는피드백단계에서새로이반영된통계정보, 옵티마이저내부비용모델등을이용해서최적화를수행해서, 주어진 SQL 문에대해더나은실행계획을생성하는단계이다. 그러나, 스스로학습하는옵티마이저의개념과프레임워크는아주초보 2003 SUMMER 037

9 적인수준에머물러있으며실용화되기위해서는많은연구와기술개발및필드테스트를통한검증이있어야할것이다. 미터로이용해서변경할수있어야한다. 스스로학습하는옵티마이저의구현방안들스스로학습하는옵티마이저프레임워크의구현방안은여러가지가있을수있다. 첫째, 이프레임워크를 DBMS내에서흡수해서기존의옵티마이저를확장 / 구현하는것이다. 하지만, 이기술이완전하게검증된것이아니라는문제가있다. 오라클DBMS의경우도이학습하는옵티마이저기능을현재단계에서제공하는것은힘들지않겠지만, 현재기술로는완전한자동화가거의불가능하기때문에이기능을제공하지않는것으로판단된다. 둘째, 써드파티도구로서분석 / 피드백 / 개선단계를지원하는방안이다. DBMS엔진에서는모니터링단계의정보만생산하고, 도구에서자동적으로주어진 SQL에대해옵티마이저의오류원인을분석하고, 피드백정보를어떻게저장하고, 주어진 SQL을어떻게개선할것인지에대한기능을제공하는것이다. 마지막으로, SQL 튜닝전문가가수동으로분석 / 피드백 / 개선을수행하는것이다. 현재 ( 주 ) 엑셈과성균관대 VLDB 연구실에서는두번째방안을지원하는도구를개발중이다. 이를위해 Oracle9i Database부터제공하는 V$SQL_PLAN_STATISTICS_ALL 등의뷰를이용하고있는데, 이뷰는스스로학습하는옵티마이저를위한모니터링단계에서필요한다양한정보를제공하고있다. 개발중인도구의일차목표는1) 이뷰에서제공하는실행계획상의예상카디널리티와실제카디널리티의값을비교해서심각한차이가발생하는경우,2) 앞서의다양한옵티마이저의오류의근본원인과실행계획생성에미치는영향을분석하고, 이를보정하기위해3) 힌트기능을이용한SQL 재작성, 통계정보생성권고, 옵티마이저관련파라미터값조정권고등의기능을제공할것이다. 좀더나아가서는 V$SQL_PLAN_STATISTICS_ALL 뷰에서제공하는카디널리티이외에 I/O 비용, 버퍼캐시적중률등의정보를기반으로학습하는기능도추가할수있다. 써드파티도구방식이기능적으로완전하기위해서는향후오라클 DBMS에서두가지인터페이스를제공해야할것이다. 우선 < 그림3> 의분석단계에서학습한새로운통계정보를저장하고, 옵티마이저가새로최적화하는과정에서이를활용할수있도록한다. 예를들면, 균등분포를가정했는데, 특정칼럼이특정한값에대해치우친 (skewed) 경우이값에대한통계치를저장하고, 나중에카디널리티를계산할때이새로운값을기반으로카디널리티예상치를보정할수있어야한다. 둘째로, 옵티마이저내부의비용모델을변경할수있는인터페이스를제공해야한다. 예를들어두테이블에서조인되는칼럼들사이에조인독립성조건이만족되지않는경우, 조인카디널리티계산공식에가중치를파라 학습하는옵티마이저에대한관심을기대하며이글에서는관계형 DBMS의옵티마이저의근본적인한계점에대해살펴보았다. 이문제점에대해단시일내에관계형DBMS 벤더들이완벽한해결책을내놓기는힘들것이다. 따라서, 사용자입장에서 1) 이문제의근본적인이유를제대로이해하고 2) 이를해결하기위해어떤기능들이개별 DBMS 에서제공되는지를파악하고3) 현재관계형DBMS의옵티마이저의한계를해당기능들을이용해서어떻게극복할것인지를고민하는수밖에없다. 또한, 옵티마이저의근본적인한계를극복하기위한다양한노력들중에서, 필자가판단하기에앞으로주요한방향이될학습하는옵티마이저의프레임워크와구현방안에대해간단히언급하였다. 사실이분야는아직까지미개척분야이고, 각단계별로데이타베이스튜닝전문가들이나름대로기여할여지가엄청나게많은부분이다. 이글이데이타베이스관련종사자들에게관계형옵티마이저의근본적인한계를이해하는데큰맥락에서도움이되기를바라고, 조만간오라클 DBMS 옵티마이저에관한좀더완벽하고포괄적인내용을포함하는자료 [ 참고문헌 5] 를제공할수있기를희망한다. 또한, 현재필드에종사하는여러전문가들과의교류를통해서스스로학습하는옵티마이저의요소기술들에대해깊이있는논의를할수있는기본토대가되었으면하는바람이다. 참고문헌 1. 이상원, 오라클옵티마이저의기본원리, 오라클매거진코리아 2002년겨울호 2. Pat. Selinger 외다수, Access Path Selection in a Relational Database System, ACM SIGMOD Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom, Database Systems: The Complete Book(Chapter 16), Prentice Hall, Wolfgang Breitling, "Fallacies of Cost Based Optimizer," 2003 Hotsos Symposium on Oracle System Performance 5. 이상원, 오라클옵티마이저내부동작원리의모든것, In Preparation 6. Oracle Corp. Query Optimization in Oracle9i, 2002 Technical White Paper 7. Benoit Dageville, Mohamed Zait, SQL Memory Management in Oracle9i, VLDB Frederick R. Reiss, Tapas Kanungo, A Characterization of the Sensitivity of Query Optimization to Storage Access Cost Parameters, SIGMOD Oracle Corp., Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2), release2/server.920/a96533/toc.htm 038 ORACLE KOREA MAGAZINE

오라클옵티마이저의기본원리

Oracle Technical Note 오라클옵티마이저의기본원리 현재모든관계형 DBMS 에서는사용자의 SQL 질의를효율적으로처리하기위해옵티마이저를사용하고있다. 개발자나관리자들이이옵티마이저의기본동작원리를이해한다면, 여러면에서도움이되리라는생각에이글을쓰게되었다. 먼저, 옵티마이저에대한기본적인이해를구한다음, 오라클옵티마이저에대해알아보도록한다. 본론에앞서, 이글은관계형데이타베이스개념에일정정도익숙한독자들을대상으로작성되었기때문에,