Query Process 단계처리내용 Parse 단계 Syntax, Security, Semantics의체크및Simple transformation 을수행한다 < 표 2>. Query Rewrite 단계서브질의와뷰의병합을수행하고, OR Expansion 작업을수행한다.

Oracle Optimizer 의원리이해및 SQL & 애플리케이션의튜닝 ( 상 ) 옵티마이저의원리와특징 글 최세훈 ( 한국오라클 Tech Sales Consulting 본부 DB Tech 팀 ) sehoon.choi@oracle.com 다수의데이타베이스튜닝과 SQL / 애플리케이션튜닝을통해튜닝의효과를확신하는필자가유익한튜닝정보를제공한다. 여기에서필자는 SQL 문장개별단위의튜닝보다는우선옵티마이저의원리를이해하고, 전체구조적인문제, 유형문제또한옵티마이저관련파라미터의설정이먼저최적으로설정된상황하에서 SQL 문장의단위플랜에대한튜닝이이루어져야한다고강조한다. 그런취지에서이글에서는옵티마이저의원리에대한이해를기반으로 SQL 및애플리케이션튜닝에필요한기본지식을개발자와 DBA가쉽게이해할수있도록 2회에걸쳐소개할예정이다. 옵티마이저의질의처리단계에대한이해오라클에서사용하는옵티마이저 (Optimizer) 는크게RBO(Rule Base Optimizer) 와 CBO(Cost Base Optimizer) 2개로구분된다. 1992년 Oracle 7에서처음 CBO가지원된이래새로운기능들이적용되면서 CBO가계속향상되고있는데반해, RBO는오라클이더이상은추구하지않는옵티마이저로서, 현재의 Oracle Database 10g에서도명맥은남아있지만향후는더이상지원되지않을것이다. 옵티마이저의입장에서질의 (query) 처리는 5단계로나눌수있는데, 옵티마이저는서브질의와뷰의병합 (merge) 등을수행하는 Query Rewrite 단계와 Query Optimization 단계에참여한다. 여기서옵티마이저는 데이타를어떠한방법으로액세스할것이며 올바른결과를어떻게제공할것이며 데이타를얼마나효과적으로액세스할것인가를결정한다. QEP Generation 단계는 Query Optimization 단계에서제공된정보를이용해서질의에대한최적의실행계획 (execution plan) 을만들어내는단계이다. CBO에서는질의실행계획 (QEP) 을구하기위하여 RBO보다복잡한단계를거치게된다 < 그림 1>, < 표 1>. 소프트파싱과하드파싱 < 그림 1> 질의처리단계와옵티마이저의역할 SQL 문장이옵티마이저에의해처리되고그결과물로서, SQL 문장이어떻게실행될것인지의정보, 즉, QEP가생기게된다. 이들정보는한번쓰고버리는것이아니라오라클의캐쉬 (cache) 영역인 SGA의공유풀 (shared pool) 에이들모든정보를캐쉬화해관리한다. 다음번에같은 SQL 문장이사용자에의해서실행되면, 이를재활용하게된다. SQL 문장이실행되면, 우선 SQL 문장텍스트의스트링을해쉬함수을통과시켜결과값에해당되는버킷 ( 어레이형구조 ) 에매달린체인정보에 098 ORACLE KOREA MAGAZINE

Query Process 단계처리내용 Parse 단계 Syntax, Security, Semantics의체크및Simple transformation 을수행한다 < 표 2>. Query Rewrite 단계서브질의와뷰의병합을수행하고, OR Expansion 작업을수행한다. 서브질의와뷰병합이란, 옵티마이저가더욱효과적인 QEP를찾기위하여더효과적인플랜이있는지그가능성을확인하는과정이다 < 표 3>. Optimization 단계질의에대한액세스경로를결정한다. QEP Generation 단계질의를실행하는데필요한상세한정보를만들며, 이를질의실행계획 (QEP : Query Execution Plan) 이라고한다. 질의실행계획 (QEP) QEP는시리얼플랜 (serial plan) 과패러렐플랜 (parallel plan) 이있다. 시리얼플랜이란질의에대해서병렬성이적용되지않은플랜이며, 패러렐플랜이란질의에대해서병렬로실행할정보를생성해내는것이다. 경우에따라서시리얼플랜만생성하거나, 시리얼과패러렐플랜을동시에생성하기도한다. 오라클의시리얼플랜은질의가병렬로수행할정보가없을경우, 즉테이블의 Degree이나힌트등이없는경우는시리얼플랜만만들게되며, 병렬성이적용될경우시리얼플랜과패러렐플랜을모두만들게된다. 오라클의시리얼플랜을 RSO(Row Source Operator) Tree라하며, 패러렐플랜을 DFO(Data Flow Operator) Tree라고한다. 이들 QEP로병렬로실행하려고하나실행시리소스의부족으로원하는 Degree으로병렬적으로실행할수없는경우RSO Tree를쓰기도한다. Query Exectution 단계 QEP에따라SQL 문장을실행한다. < 표 1> 질의처리단계별역할 Example Expression (from) Transformation (to) ename LIKE WARD ename= WARD ename IN ( KING, WARD ) ename= KING OR ename= WARD ename=any/some( KING, WARD ) ename= KING OR ename= WARD deptno!= ALL(10,20) deptno!= 10 AND deptno!= 20 sal BETWEEN 2000 and 3000 sal >= 2000 AND sal <= 3000 NOT(sal<1000 OR comm is null) sal >= 1000 and comm is not null < 표 2> 간단한변형 (simple transformations) 의예서같은SQL 문장이존재하는지찾는처리절차를수행하게된다. 또한같은 SQL 문장을찾았어도여러버전이존재할수있다. 여러버전이란, 같은 SQL 문장 ( 대 / 소문자, 화이트스페이스등이모두같아야함 ) 이지만서로다른스키마의테이블 ( 예, scott의 emp, sys의 emp) 이거나, 바인드변수를사용한경우는바인드변수의타입, 길이등에의해서도서로다른버전 뷰병합예 Example Expression (from) View Merging (to) create view emp_d10 as select empno from emp select * from emp where deptno=10; where deptno = 10 and empno > 11910; select empno from emp_d10 Where empno>11910 서브질의병합예 (Single Row Sub-Query) Example Expression (from) Sub-Query Merging (to) select... from dept where select... from dept deptno = (select deptno from emp where deptno = <evaluated_value>; where empno < 12501); < 표 3> 서브질의와뷰의병합예이된다는것이다. 이와같이같은 SQL 문장에같은버전을찾았다면이를 소프트파싱 (soft parsing) 이라고한다. 그렇지만, 체인을다찾았는데같은문장을발견하지못했다면, 해당 SQL 문장이 Parsing/Optimizing 단계를거친결과로나온정보를저장하기위해공유풀로부터메모리를확보받고, 기록한정보를체인에매달게된다. 이를 하드파싱 (hard parsing) 이라고한다. 당연히하드파싱의작업량이소프트파싱의작업량에비해월등히클것이다 < 그림 2>. 이와같은소프트파싱과하드파싱의과정을생각해볼때, 집중적인 SQL 문장이실행되는 OLTP( 초당수천 ~ 수만개이상 ) 에서하드파싱이많다면어떻게될까? 한정된메모리인캐쉬에새로운메모리를계속할당하고, LRU 알고리즘에의해제거하고, 체인에매달고끊는등의일들을반복해야할것이다. 또한하드파싱은복잡한처리과정을거치므로많은자원 (CPU) 을사용하게된다. 그러므로 OLTP 환경에서는이와같은하드파싱을가능한줄이도록해야한다. 특히 SQL 실행규모가큰 OLTP 업무는 1% 미만을권장한다. 애플리케이션을개발할때이러한하드파싱을줄이기위한방법으로거의대부분의데이타베이스접속방식 (JDBC, ODBC, ADO, PRO*C 등 ) 에서자주사용되는 SQL 문장들은바인드변수기법들을사용하여개발하는방법들을제공하고있다. 또한일부에서는소프트파싱자체도줄일수있는기법들을제공하고있다. 실제이러한기법을적용해서튜닝한결과, 시스템 CPU/ 메모리측면에서 40~50% 이상개선된사례가많이있다. 혹시현재운영중인시스템이사용자가많아지면서 CPU 리소스가급격히증가해, 라이브러리캐쉬, 공유풀경합현상이발생한다면, 이러한점을의심해볼수있다. < 표 4> 는 SQL 문장을바인드변수를사용한공유 SQL과, 상수를결합한형태로 SQL 문장을만들어실행시키는비공유 SQL을 9,999회실행시켜오라클의공유풀메모리사용현황과파싱시 CPU 사용시간을테스트한것이다 ( 단, 그결과치는실행서버별로차이가있다 ). 결론적으로보면, 비공유 SQL 방식의사용메모리와 CPU 사용률이 2005 WINTER 099

- 파싱타임을최소화하고 SQL 등이공유될수있도록바인드변수를사용해야한다. - 인덱스의사용률이높아야한다. - 정렬 (sorting) 을최소화해야한다. - Nested Loop Join(FIRST_ROWS_n) 방식으로많이유도한다. < 그림 2> SQL 문장의파싱된정보를찾기위한라이브러리캐쉬검색절차실행규모에비례해증가하고, 실행된 SQL 문장이기존에캐쉬화되어있는 SQL 문장들을밀어내는역할을한다는것을알수있다. 이와같은 SQL 문장을공유하기위해서오라클입장에서처리해주는 CURSOR_SHARING이라는파라미터를제공하기도한다. 그러나, CURSOR_SHARING은모든상수를다바인드변수로바꿔버리기때문에개발자가의도하지않은 Literal까지도바꾸게되므로, 애플리케이션을수정할수있다면가능한애플리케이션단에서바인드변수를사용하는것이효과적이다. 하드파싱을줄이기위해모든업무에바인드변수사용방법을적용하는것은잘못된생각이다. 옵티마이저의입장에서보면, 바인드변수기법보다는 Literal을사용한비공유 SQL 방식을좋아한다. Literal SQL 문장일경우는상수값에따라서범위를정확히알수있기때문에효과적인플랜을결정하는주요결정요소로작용하기때문이다. 즉바인드변수기법은옵티마이저의판단에는좋지않지만 SQL 문장이집중적으로실행되는 OLTP 환경에서하드파싱의비율을줄이기위한방법인것이다. 즉, 업무의특징에따라서다른적용방식이사용되어야한다. 다음은 OLTP와 DW 의특징에따라다르게고려되어야할사항이다. OLTP의특징 - 목표 : 신속한응답시간, 적은데이타처리량 DW의특징 - 목표 : 최고의처리량, 방대한데이타처리량 - 인덱스의참조는중요한사항이아니다. - 정렬또는 Aggregate함수등이중요한역할을한다. - Hash Join 등을많이사용하도록유도한다. - 파싱타임등은그리중요하지않으며, 바인드변수의사용이문제가될수있다. - 병렬질의등의사용률을높인다. Rule Base Optimizer 질의최적화 (query optimization) 에서 RBO(Rule Base Optimizer) 는정해진랭킹 (ranking) 에의해플랜을결정한다. 같은랭킹이라면 Where 절의뒤부터, From절뒤의객체가우선순위를갖는다. 한객체 ( 예 : 테이블 ) 에서같은랭킹의인덱스가있다면가장최근에만들어진인덱스를사용한다. 이는 CBO(Cost Base Optimizer) 에서도같이적용되는사항이다. 다분히 RBO는개발자들이프로그래밍단계에서 SQL 문장구조의인위적인조정등으로인덱스를사용못하게하는등개발자가코딩에신경을많이써야하는문제점이있다. 또한 RBO는해당질의에대한테이블의인덱스가존재한다면전체 90% 이상의대상이어도인덱스를선택한다는것이다. 즉, RBO는무조건다음과같은미리정해진룰을기준으로플랜을결정하게된다. 1992년 Oracle 7에서 CBO가지원되면서 CBO는계속적인신기능의적용으로발전해온반면, RBO는더이상의기능향상은없으며, 향후는 CBO만지원될계획이다. 그러므로 RBO에더이상의미련을갖지말기바라며, CBO의훌륭한기능들을적극활용하길바란다. 다음은 RBO의랭킹을정리한것이다. SQL 유형 공유풀의메모리사용 하드파싱수 실행수 파싱 CPU 사용률 공유 SQL select ename from emp where empno = :1 9,807 1 9,999 0.01sec 비공유 SQL select ename from emp where empno = 1 93,219,148(92MB) 9,999 9,999 14.33sec select ename from emp where empno = 2 select ename from emp where empno = 3...... < 표 4> 공유 SQL과비공유 SQL의비교 100 ORACLE KOREA MAGAZINE

Path 1 : Single Row by Rowid Path 2 : Single Row by Cluster Join Path 3 : Single Row by Hash Cluster Key with Unique or Primary Key Path 4 : Single Row by Unique or Primary Key Path 5 : Clustered Join Path 6 : Hash Cluster Key Path 7 : Indexed Cluster Key Path 8 : Composite Index Path 9 : Single-Column Indexes Path 10 : Bounded Range Search on Indexed Columns Path 11 : Unbounded Range Search on Indexed Columns Path 12 : Sort-Merge Join Path 13 : MAX or MIN of Indexed Column Path 14 : ORDER BY on Indexed Column Path 15 : Full Table Scan 특히 Path 8, 9, 10에주의를해야한다. 예를들면, emp 테이블에 A 인덱스가 deptno 로구성되어있고, B 인덱스가 deptno + empno 로구성되어있다면, 다음과같은 SQL 문장은 A 인덱스를사용하게된다. 조건이 Bounded Range Search(Between) 로왔기때문에아래의 SQL 문장에서 (A) 와 (B) 의랭킹은 (A) ==> Rank 9, (B) ==> Rank 10 조건이되므로싱글칼럼인덱스를사용한다는것이다. select /*+ rule */ * from emp where deptno = 10 and empno between 7888 and 8888; A B 그러면, 이제 CBO에대해살펴보기전에, 참고로 RBO를 CBO로전환한사례를잠깐소개하겠다. 현재 RBO를사용하고있는상황에서마이그래이션시 CBO로전환하고싶으나, 막연히두려운부분도많을것이다. 실제 RBO에서 CBO로전환하고나서가장효과를보는부분은배치잡형태이다. 특히 Oracle9i Database 이상의 WORKAREA_SIZE_POLICY=AUTO로운영하는곳이라면더욱더그럴것이다. 그러나 OLTP의변화는조심해야한다. 아래의경우는, 이전하면서옵티마이저모드를 RBO에서 CBO로전환한것뿐만아니라, 블록사이즈와 CBO 옵티마이저에민감한 db_file_multi block_read_count 값도크게늘렸다. 특히 WORKAREA_SIZE_ POLICY=AUTO로필요한워킹메모리 (Sort, Hash, Bitmap 등 ) 를옵티마이저가판단하에가능한충분히사용하게하는방식을사용하였다. 그러다보니, CBO에영향을주는소트메모리와해쉬메모리가풍부하게되었고, 블록사이즈도커졌으며, 풀테이블스캔의정도를결정하는 db_file_multiblock_read_count 값도아주커진상태이다. 또한마이그래이션되면서데이타가재정리되어있는상태이므로, 풀테이블스캔과 Sort Merge Join, Hash Join의경향이커진상태이다. 그러므로배치잡의 경우는최적의조건이되었으나, 기존에주로 Nested Loop Join을선호하던 RBO 환경의 OLTP들은많은플랜의변화에직면하게된다. 그러면이러한부분을어떻게보정해줄것인가? optimizer_index_caching, optimizer_index_cost_adj의파라미터가그해답일것이다. 가능한 Nested Loop Join를선호하고, CBO의옵티마이저모드가인덱스에점수를더주어서인덱스의비중을키울수가있는것이다. 물론이러한전환형태말고옵티마이저모드를 FIRST_ROWS_n으로운영하거나, 아웃라인을이용하는방법등도있을것이다. 여러방법이있겠지만, 필자는아래와같은방법을선호한다. 다음은마이그래이션시 RBO에서 CBO로전환한사례이다. Oracle 7 --> Oracle 9 (RBO to CBO 전환사례 ) db_block_size : 2KB --> 8KB db_file_multiblock_read_count : 8 --> 32 optimizer_mode : RULE --> CHOOSE hash_join_enabled : FALSE --> TRUE workarea_size_policy : AUTO (New) optimizer_index_caching : 80 (New) optimizer_index_cost_adj : 20 (New) Cost Base Optimizer 질의최적화에서 CBO(Cost Base Optimizer) 는해당 SQL 문장이참조하고있는객체들 ( 테이블, 인덱스등 ) 에대한수집된통계정보 (statistics) 의값과데이타베이스파라미터 (init.ora) 설정값을기초로가장적은비용 (cost) 이발생되는플랜을결정하는옵티마이저방식이다. 여기서중요한사실은 RBO에서는전혀사용되지않았던통계정보를 CBO에서는이용한다는것이다. 이들통계정보는 DBA에의해서또는자동수집기능 (Oracle9i Database Release 2, Oracle Database10g) 에의해객체들의통계정보를관리하는시스템딕셔널리 (Dictionary) 에저장되고, 이정보를 CBO 옵티마이저가이용하는것이다. 이들정보는 SQL 문장을실행하는데얼마만큼의 I/O 횟수가발생할것인가를계산하기위한각종데이타를가지고있다. 여기서중요한사실은 I/O 크기는중요하지않으며 I/O 횟수가중요하다는것이다. 즉, CBO 옵티마이저는 SQL 문장에대한여러가지경우의수별로 I/O의횟수에비례한비용을산출해내고, 이들비용에서가장작은비용을갖는플랜을결정한다는것이다. 즉, 비용은 I/O 횟수에비례하는값이라고보면쉬울것이다. 그러나, Oracle Database 10g부터는비용의단위기준이 I/O에서처리시간으로바뀌었다 (time base). 또한 Oracle9i Database부터시스템통계정보 (CPU, 디스크액세스타임 ) 를이용해서 I/O로환산한방식을제공하였으나, 이것은단지옵션이었다. 그러나, Oracle Database 10g부터는 2005 WINTER 101

시스템통계정보 (CPU, 디스크액세스타임 ) 를이용해서처리시간으로환산한방식을디폴트로사용하므로상당히정확한플랜을만들어내며, 실행예측시간도상당히정확하다. 그러면, 여기서잠깐 CBO에서사용되는통계정보가저장된 Dictinary 정보예를참고로살펴보자. [USER ALL DBA]_TABLES : Table의통계정보 NUM_ROWS,BLOCKS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED [USER ALL DBA]_INDEXES : Index의통계정보 BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS,SAMPLE_SIZE, LAST_ANALYZED [USER ALL DBA]_TAB_COLUMNS : Column의통계정보 NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,AVG_COL_LEN [USER ALL DBA]_TAB_HISTOGRAMS : Column의Data 분포도정보 TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE 기타파티션 / 클러스터등에대한통계정보그러면 select * from dept where deptno = 10 과같은SQL 문장을실행해야한다고가정하자. 여기서 dept Table은 deptno에대한인덱스가있고, 테이블은전체 10 블록으로구성되어있으며, 풀테이블스캔일경우 I/O 단위를결정하는파라미터는 DBA가 db_file_multiblock_ read_count=8로지정하여운영하고있다고가정하자. 여기서 RBO라면무조건인덱스를타는플랜을결정하였을것이다. 그러나, CBO의경우는 deptno의인덱스를이용해실행하면, 3회 ( 인덱스는싱글블록단위 I/O) 의 I/O가발생한다고가정하고, 풀테이블스캔의경우는 2회 (8블록 + 2블록 ) 의 I/O가발생한다고가정하면, CBO에서는인덱스가있음에도 I/O 횟수측면에서더효과적인풀테이블스캔을선택한다는것이다. 그러므로 CBO는이와같이가능한정확한 ( 현실데이타와맞는 ) 통계정보와적절한데이타베이스의파라미터인 init.ora에의해플랜이결정되는것이다. CBO에서만가능한기능들 CBO는오라클의신기능을지원하도록지속적으로발전하고있으며, 다음의경우는반드시 CBO에서만플랜결정시검토되거나무조건 CBO로동작되는경우이다. 예를들어, 파티션테이블을사용한다면통계정보가없더라도무조건 CBO로동작된다는것이다. Partitioned tables (*) Index-organized tables Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement (*) Parallel execution and parallel DML Star transformations Star joins Extensible optimizer Query rewrite (materialized views) Progress meter Hash joins Bitmap indexes Partition views (release 7.3) Hint (*) Parallel DEGREE & INSTANCES - DEFAULT 도해당 (*) CBO의옵티마이저에영향을줄수있는파라미터예옵티마이저가플랜을수립하는데영향을줄수있는파라미터값이무엇인지를알고있는것이무엇보다중요하다. 실제옵티마이저가참조하는파라미터는 Oracle9i Database 기준으로보더라도 60여개에이른다. 특히 DBA는이들옵티마이저의파라미터설정에신중해야한다. 또한이들파라미터의효과적인설정은개발중이거나, 마이그래이션중에업무의특징을판단한다음, 해당업무에가장효과적인것을설정해야한다. 기준이잘못되면개발자들은 SQL 문장마다힌트를넣기바쁠것이고, 많은인적자원을튜닝에소모해야할것이다. 그러므로대부분의업무들이최적화되어잘운영될수있는형태로이들파라미터를바꿔가면서기준을정하는것이중요하다. 물론이들값보다도 CBO에서사용되는통계정보가중요하다는것은당연한사실이다. 오라클에서는개발장비에도운영장비에있는통계정보와같게운영할수있도록 DBMS_STATS 패키지를제공한다. 다음은질의수행시옵티마이저가플랜을수립하기위해참조한파라미터중일부이다 ( 버전마다다르다 ). OPTIMIZER_PERCENT_PARALLEL (Default = 0) Optimizer_Percent_Parallel의 Parameter는 CBO가비용을계산하는데영향을주는파라미터이다. 즉수치가높을수록병렬성을이용하여풀테이블스캔으로테이블을액세스하려고한다. 이값이0인경우는최적의시리얼플랜이나패러렐플랜을사용하며, 1~100일경우는비용계산에서객체의등급을사용한다. OPTIMIZER_MODE (Default=Choose(Oracle7 ~ Oracle9i Database),ALL_ROWS) {Choose(<=9i) Rule(<=9i) First_rows First_rows_n(> 102 ORACLE KOREA MAGAZINE

=Oracle9i) All_rows} 기본적인옵티마이저모드를결정한다 ( 왼쪽상자기사 옵티마이저모드의종류및특징 참조 ). 옵티마이저모드의종류및특징 HASH_AREA_SIZE, HASH_JOIN_ENABLED (Oracle Database 10g : _ hash_join_enabled=true) 위의파라미터값에따라서 Hash Join으로유도할수있다. Hash Join이가능하고해쉬메모리가충분하다면, 플랜에 Hash Join의경향이커진다. OPTIMIZER_SEARCH_LIMIT (Default = 5) 옵티마이저에게조인비용을계산할경우, From절에나오는테이블의개수에따라서조인의경우의수가있을수있으며, 옵티마이저는이들각각의경우의수에대한조인비용을계산하게된다. 물론일부예외사항은있다. 예를들어, Cartesian Production Join 등은우선순위가낮으므로뒤로미뤄질것이다. 이파라미터의값이 5일경우From절에 5개의테이블에대해서모든조인의경우의수를가지고비용을계산하게되며, 그개수는 5!=120개의경우의수에대한조인비용을계산하게되므로옵티마이저가많은시간을소모하게되므로성능에영향을미칠수도있다. SORT_AREA_SIZE, SORT_MULTIBLOCK_READ_COUNT 위의파라미터의값에따라서 Sort Merge Join으로유도할수있다. 소트메모리가충분하다면, 플랜에 Sort Merge Join의경향이커진다. DB_FILE_MULTIBLOCK_READ_COUNT 이파라미터의수치가클수록인덱스스캔보다는풀테이블스캔의비중이높아진다. 이파라미터는옵티마이저의플랜결정에민감하게영향을주는값이다. 즉, 이값이커지면풀테이블스캔과병행해서 Sort Merge Join 또는 Hash Join의경향이커진다. 인스턴스레벨 : optimizer_mode = {Choose Rule First_rows First_rows_n All_rows} 세션레벨 : 인스턴스레벨에우선 ALTER SESSION SET optimizer_mode = {Choose Rule First_rows First_rows_n All_rows} 스테이트먼트레벨 : 힌트를사용하며, 인스턴스, 세션레벨에우선 Oracle9i Database에서 FIRST_ROWS_n 옵티마이저모드가추가되었음 (N : 1, 10, 100, 1000). Oracle Database 10g에서는 CHOOSE, RULE 모드는더이상지원되지않으나, 기능은남아있다. OPTIMIZER_MODE=CHOOSE 일경우통계정보가없다면기본적으로 RBO로플랜이결정된다. 그러나, RULE, DRIVING_SITE 힌트이외의힌트가왔다면 CBO로결정된다 ( 힌트는룰의규정을깨므로 CBO로동작됨 ). Parallel Degree, Partition Table, SAMPLE절등이있으면무조건 CBO OPTIMIZER_MODE=First_rows First_rows_n All_rows일경우통계정보의존재여부와관계없이무조건 CBO로처리하려고함. 통계정보가없다면 Heuristics Value를이용하거나, Oracle9i Database 이상일경우는다이나믹샘플링의레벨에따라테이블의데이타를샘플링해서 CBO로플랜이결정된다. 그러나, 플랜이비효율적일수있다. 통계정보가있으나옵티마이저모드가 RULE일경우, 다른힌트가오지않은경우와 Parallel Degree, Partition Table, SAMPLE절등이나오지않은경우는 RBO로처리된다. OPTIMIZER_INDEX_CACHING (Default = 0) CBO가 Nested Loop Join을선호하도록조절하는파라미터, Nested Loop Join시버퍼캐쉬내에이너테이블의인덱스를캐쉬화하는비율 (%) 을지정하므로 Nested Loop Join시성능이향상되며, 옵티마이저는비용계산시이비율을반영하여 Nested Loop Join을선호하도록플랜이선택된다 (0~100). 100에근접할수록인덱스액세스경로가결정될가능성이높다. 기존의 RBO 를 CBO로전환시옵티마이저를 RBO 성향으로보정하는데효과적이다. OPTIMIZER_INDEX_COST_ADJ (Default = 100) 옵티마이저가인덱스를사용하는위주의플랜으로풀릴것인지또는가능한사용하지않을쪽으로풀릴것인지의비중을지정한다. CBO는 RBO처럼인덱스를사용하도록플랜이주로만들어지게되나, 인덱스가있다고해서 RBO처럼인덱스를이용한플랜으로처리되는것은아니다. 인덱스를이용하는플랜위주로하고자한다면 100(%) 이하를, 가능한인덱스를사용하지않고자한다면 100 이상을지정한다 (1 ~ 10000). 이파라미터는기존의 RBO를 CBO로전환시옵티마이저를 RBO의인덱스위주성향으로보정하는데효과적이다. WORKAREA_SIZE_POLICY (AUTO MANUAL) 옵티마이저가 [HASH SORT BITMAP_MERGE CREATE_ BITMAP] *_AREA_SIZE를자동으로결정하는 PGA 자동관리방식으로, 인스턴스에속한모든 PGA의메모리의합이 PGA_AGGREGATE_TARGET에서설정된메모리를가능한넘지않는범위내에서 Workarea(Sort, Hash, Bitmap 등 ) 를충분히사용하고자하는방식이다. 플랜은할당된 Workarea를가지고플랜을결정하게되므로풍부한메모리에의해 Hash Join, Sort Merge Join등을선호하는경향이높다. 내부적으로히든파라미터로 *_AREA_SIZE의값을가지고플랜을결정할수도있으나인위적인설정없이는자동할당된메모리로플랜이결정된다. 2005 WINTER 103

OPTIMIZER_DYNAMIC_SAMPLING (Default = 1(Oracle9i Database), 2(Oracle Database 10g)) 더나은플랜을결정하기위한목적으로더정확한 Selectivity & Cardinality 를구하기위한방법으로 0 ~ 10 레벨이있으며, 레벨이높을수록 SQL 문장의실행시점에통계정보를만들기위해테이블의데이타를샘플링하기위한추가적인 Recursive SQL이발생된다. DYNAMIC_SAMPLING(0 ~ 10) 힌트를통해서도같은기능을할수있다. 그러나내부적으로추가적인테이블액세스의비용이발생하므로 OLTP에서는주로사용하지않는다. 특히 OLTP 환경에서레벨을디폴트값이상높여놓지않도록한다. Oracle Database 10g의경우통계정보가없다면 다이나믹샘플링 이적용된다. 다음은 Oracle Database 10g의플랜및다이나믹샘플링의예이다. SQL> analyze table dept delete statistics; 통계정보가없을경우, 다이나믹샘플링기능을확인하기위해서통계정보삭제 Table analyzed. SQL> analyze table bigemp delete statistics; Table analyzed. Explain Plan으로 SQL> explain plan for 파싱처리 select * from bigemp e, dept d where e.deptno = d.deptno and d.deptno = 10; Explained. 플랜을보기위한 SQL SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT Id Operation Name Rows Bytes Cost(%CPU) Time 0 SELECT STATEMENT 11833 1352K 176 (6) 00:00:02 1 MERGE JOIN CARTESIAN 11833 1352K 176 (6) 00:00:02 * 2 TABLE ACCESS FULL DEPT 1 30 5 (0) 00:00:01 3 BUFFER SORT 11833 1005K 171 (6) 00:00:02 * 4 TABLE ACCESS FULL BIGEMP 11833 1005K 171 (6) 00:00:02 Oracle Database 10g부터는비용중 CPU 비중및 Predicate Information (identified by operation id): 예측실행시간을확인할수있다. 2- filter( D. DEPTNO =10) 4 - filter( E. DEPTNO =10) Note 다이나믹샘플링이사용된것을 - dynamic sampling used for this statement 확인할수있다. 20 rows selected. < Dynamic Sampling에의한Recursive SQL문장예 > SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL( E ) FULL( E ) NO_PARALLEL_INDEX( E ) */ 1 AS C1, CASE WHEN E. DEPTNO =10 THEN 1 ELSE 0 END AS C2 FROM BIGEMP SAMPLE BLOCK (4.225352, 1) SEED (1) E ) SAMPLESUB CBO를위한통계정보운영방법통계정보는 CBO의플랜결정에사용되는객체들의물리적인구성정보를나타낸다. 즉, 테이블이몇블록으로구성되어있으며, 몇건의로우들을가지고있으며, 평균로우길이는어느정도이며, 칼럼의 Min/Max 값의분포, Distinct 값, 인덱스의레벨, 키 (key) 당 Leaf Block 수등의정보들을나타낸다. 이들정보는 CBO의플랜결정의기초자료로사용된다. 이들통계정보를생성하기위해서는 ANALYZE 명령어를이용하거나 DBMS_STATS 패키지를이용하면된다. 그러나 2개의차이점에주의해야하며, DBMS_STATS를지원하는 Oracle8i Database 이상부터는 DBMS_STATS를사용하기를권장하고있다. Analyze 명령어와 DBMS_STATS의차이점 Analyze는 Serial Statistics Gathering 기능만있는반면, DBMS_STATS은 Parallel Gathering 기능이있다. Analyze는파티션의통계정보를각파티션테이블과인덱스에대해서수집하고, Global Statistics는파티션정보를가지고계산하므로, 비정확할수있다. 그러므로파티션또는서브파티션이있는객체에는 DBMS_STATS을사용하여야한다. DBMS_STATS은전체클러스터에대해서는통계정보를수집하지않는다. 그러므로 Analyze를사용한다. DBMS_STATS은 CBO와관련된통계정보만을수집한다. 즉, 테이블의 EMPTY_BLOCKS, AVG_SPACE,CHAIN_CNT 등은수집되지않는다. DBMS_STATS은사용자가지정한통계정보테이블에수집된통계정보를저장할수있고, 딕셔너리로각칼럼, 테이블, 인덱스, 스키마등을반영할수있다. DBMS_STATS은 IMPORT/EXPORT 기능및추가적인기능이많다. 이기능을이용하여운영 DB의통계정보를개발장비의통계정보로복사할수있으므 104 ORACLE KOREA MAGAZINE

로개발장비의플랜을운영장비와같게만들수있다 ( 매뉴얼참조 ). 다음은 Analyze 명령어에만있는기능이다. SQL> EXECUTE dbms_stats.gather_system_stats(gathering_mode => START ); PL/SQL procedure successfully completed. >>>> 이시기동안발생된워크로드를분석해서 aux_stats$ 에반영시킴. Structural Integrity Check 기능 analyze { index/table/cluster } (schema.){ index/table/cluster } validate structure (cascade) (into schema.table); SQL> EXECUTE dbms_stats.gather_system_stats(gathering_mode => STOP ); 시스템통계정보수집의시작과종료. 시스템이사용되는시기에일정시간수집한다. PL/SQL procedure successfully completed. Chained Rows 수집기능 ANALYZE TABLE order_hist LIST CHAINED ROWS INTO <user_tab>; 시스템통계정보 (>= Oracle9i Database) 시스템통계정보는객체의통계정보와같이사용되는정보로서, 기존의 Oracle8i Database까지의 I/O 중심의플랜방식에 CPU와디스크 I/O 속 도와같은시스템자원의효율을반영하여보다효율적인플랜을결정하기 위한방법으로, Oracle9i Database에서처음소개되었으며, 옵션기능으로 DBA에의해사용될수도있고사용하지않을수도있었다. Oracle Database 10g에서는시스템통계정보가기본적으로수집되고사용된다. 그 러므로기존의 I/O 횟수만가지고플랜을결정하던부분이 Oracle Database 10g에서는시스템의자원의성능도고려되어보다정확한플랜을 결정할수있게되었다. 이들시스템통계정보는 DBMS_STATS 패키지를이용해서수집된다. Oracle Database 10g에서는기본적으로수집되는값들이있으며, 또한 사용자가수집해야하는항목도있다. 다음은 Oracle Database 10g의시 스템통계정보의수집형태를보여주고있다. SQL> select * from aux_stats$; -- System통계정보확인 SNAME PNAME PVAL1 PVAL2 SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 09-30-2004 16:23 SYSSTATS_INFO DSTOP 09-30-2004 16:23 SYSSTATS_INFO FLAGS 0 CPUSPEEDNW 230.853 <<< Default (NOWORKLOAD상태값 ) IOSEEKTIM 10 <<< Default (NOWORKLOAD상태값 ) IOTFRSPEED 4096 <<< Default (NOWORKLOAD상태값 ) SREADTIM MREADTIM 디폴트로수집된시스템통계정보. CPU Clock Speed, I/O Seek Time, I/O Transfer Time 등을확인할수있다. CPUSPEED MBRC MAXTHR SLAVETHR SQL> select * from aux_stats$; SNAME PNAME PVAL1 PVAL2 SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 09-30-2004 17:02 SYSSTATS_INFO DSTOP 09-30-2004 17:04 SYSSTATS_INFO FLAGS 1 CPUSPEEDNW 243.637 IOSEEKTIM 13.776 IOTFRSPEED 4096 SREADTIM MREADTIM CPUSPEED 9.167 9.091 238 Dbms_stats의 gather_system_stats를통해수집된시스템통계정보가반영되었다. MBRC 15 MAXTHR SLAVETHR SQL> EXECUTE dbms_stats.delete_system_stats(); --- 수집된시스템통계정보삭제 PL/SQL procedure successfully completed. 옵티마이저원리에바탕한 SQL 튜닝 지금까지설명한바와같이개략적으로나마옵티마이저의원리를이해하 기위한부분에초점을맞춰설명하였다. 더자세한부분이필요하면오라 클매뉴얼인 [Database Performance Tuning Guide and Reference] 를 권장하고싶다. 다음호엔오라클이사용하는조인방법들와오라클이제 공하는 SQL 튜닝방법에대해알아보도록하자. 2005 WINTER 105