Oracle Optimizer 의원리이해및 SQL & 애플리케이션의튜닝 ( 하 ) 오라클튜닝기법의 100% 활용 글 최세훈 ( 한국오라클 DB Tech 팀 ) sehoon.choi@oracle.com 지난회에서는튜닝에들어가기위해먼저 Oracle Optimizer 의원리와특징에대해서설명했다. 이번회에서는조인메소드별특징과플랜보는법을이해하고, 실제오라클에서제공하는튜닝기법들을활용해보도록하자. 숲을보는튜닝 조인메소드별특징 튜닝에는정답이없다. 즉튜닝은시스템의특징이나업무의특징들을정확히이해하고, 그상황에맞게문제의원인을확인하고, 문제의원인을해결하기위한최적의튜닝방법을찾아야한다는것이다. 튜닝의기본목표는자원을상황에맞게효율적으로사용해서원하는결과값을원하는시간내에받아보는것이다. 병렬기능을많이사용한다고해서항상좋은결과가나오는것은아니다. 또한시스템의자원은한정되어있다는것을항상명심해야한다. 하나의애플리케이션이한정된시스템자원을병렬기능을사용해서독점한다면, 다른애플리케이션들은상대적으로피해를보게되는것이다. 즉튜닝은하나의애플리케이션을위한것이아니라모든애플리케이션이조화롭게운영될수있도록하여야한다는것이다. 이런입장에서필자는나무를보지말고숲을보라고항상강조한다. 즉단위 SQL 문장의플랜 (plan) 튜닝도중요하지만, 전체적인조화를이루는 SQL 문장의유형및구조적인문제등이더중요하다는것이다. 업무시작이후에구조적인문제를해결하려면상당한인적, 시간적자원을소모해야하지만, SQL 문장의플랜적인튜닝은해당 SQL 문장의튜닝결과를적용하는것으로, SQL 문장의구조적, 유형적튜닝에비해상대적으로적은비용이들것이다. 그래서튜닝작업을이런측면에서바라보도록항상노력하여야할것이다. 오라클의조인메소드는 Nested Loop Join(NLJ), Sort Merge Join(SMJ), Hash Join(HJ) 의 3가지가있다. 이들 3가지조인메소드로여러조인타입 (Basic(natural) Join, Outer Join, Semi Join, Anti Join 등 ) 을지원하게된다. Oracle Optimizer는최적화단계에서이들조인메소드들중조인에대한Selectivity와Cardinality의계산에의해가장효율적인것을선택하게된다. 단 RBO(Rule Base Optimizer) 에서는인위적인힌트 (USE_HASH) 를주지않고서는 Hash Join은전혀고려하지않는다. 힌트를준것자체가이미 RBO가아니라 CBO(Cost Base Optimizer) 로동작되는것이다. 조인을확인할때조인메소드뿐만아니라조인순서또한중요하다. 먼저액세스되는쪽을 드라이빙테이블 (Driving Table) 이라고하며, 나중에액세스되는테이블을 이너테이블 (Inner Table) 이라고한다. Hash Join 에서는 Build Table 과 Probing Table 이라는용어로사용된다. SQL 문장에서튜닝을잘하기위해서는조인메소드별특징을정확히이해하고플랜을통해처리되는과정을그려볼수있어야한다. Nested Loop Join(NLJ) NLJ는순차적인처리로 Fetch의운반단위 (Array Size, Prefetch Size) 마다결과로우 (row) 를리턴받을수있다. 첫번째로우를받는시간은빠르나, 전체결과로우를받는데까지걸리는시 2005 SPRING 095
< 그림 1> Nested Loop Join - 이너테이블에인덱스가있을경우 < 그림 2> Nested Loop Join - 이너테이블에인덱스가없는경우 간은느리다. 즉첫번째로우를받을준비가되어있는단계까지를실행시간으로볼때실행시간은빠르나 Fetch 시간은느리다. NLJ는메모리가필요없는조인이다. 그러므로추가적인메모리비용이필요없다. NLJ는드라이빙테이블에서많은로우들이필터링되어이너테이블로찾아들어가는부분을줄여야하므로드라이빙순서가중요하다. 이너테이블은드라이빙테이블의리턴되는모든로우들에대해서반복실행하므로액세스효율이좋아야한다. 즉대부분의경우이너테이블은인덱스가있어야한다. 또한인덱스의효율이좋아야한다. 이너테이블이작더라도액세스횟수가많다면인덱스가있어야한다. 인덱스의효율이좋지않아전체의 Index Range Scan과같은경우는최악의조건이다. NLJ는주로인덱스위주의싱글블록 I/O의랜덤I/O 위주이므로 OLTP에서적은데이타범위처리에주로사용된다. 즉전체의 15% 이상의경우는 Full Table Scan을이용한 Sort Merge 또는 Hash Join을이용한다. NLJ도드라이빙테이블이 Full Table Scan에병렬로처리되면이너테이블도병렬로종속적으로처리된다. Sort Merge Join(SMJ) 전체로우를리턴받는시간이빠르다. 즉첫번째로우를리턴받을준비까지의시간은느리지만, 준비가된상태에서의 Fetch 시간은빠르다 ( 메모리에서리턴하므로 ). 이는대상로우들 (Where 조건에의해필터된로우들만정렬 ) 을가지고정렬작업 ( 모든로우들을조인키로정렬 ) 을하기전까지는어떠한로우도리턴할수없기때문이다. NLJ와같이드라이빙테이블의리턴되는로우수와이너테이블의액세스패턴에의에액세스효율이좌우되지않으며, 조인테이블간에자신의처리범위로만처리량을결정하므로독립적이다. 추가적인정렬메모리 (SORT_AREA_SIZE) 비용이필요하다. 메모리가부족하면 TEMP 테이블스페이스에정렬중간단계 (Sort Runs) 를기록하게되므로추가적인디스크 I/O비용이발생할수있다. 정렬메모리에위치하는대상은조인키뿐만아니라 Select List도포함하므 로불필요한 Select List는제거해야한다. 정렬작업의 CPU 사용에대한오버헤드가있다. 그러므로많은로우들과전체적으로 Select List의사이즈의합이큰테이블의조인에는문제가있다. 즉디스크정렬을피할수가없으며, 정렬에 CPU 비용이많이든다. 디스크정렬만발생하지않는다면넓은범위처리에유리하다. 디스크정렬을피할수없는경우라면 (Batch Job, Create index,...) SORT_AREA_SIZE, SORT_MULTIBLOCK_READ_COUNT를SQL마다세션레벨에할당해서사용하도록한다 (WORKAREA_SIZE_POLICY가 Manual일경우나 Oracle9i Database 이전버전에서 ). 또한 TEMP 테이블스페이스의 Extent Size도충분히크게주도록한다. ALTER SESSION SET SORT_AREA_SIZE= 104857600; ALTER SESSION SET SORT_AREA_RETAINED_SIZE= 104857600; ( 같이준다 ) ALTER SESSION SET SORT_MULTIBLOCK_READ_COUNT=128; 정렬메모리의크기는 (= Target rows (total selected column s bytes) 2) 이상설정하되, PGA의메모리한계로인해테스트를통해 PGA Memory Allocation Error가발생하지않는범위내에서설정하도록한다. 필요시 10032 Trace를이용해점검한다. ALTER SESSION SET EVENTS 10032 TRACE NAME CONTEXT FOREVER ; Hash Join(HJ) Hash Join은두개의조인테이블중 Small Rowset(Where 조건에의해필터링된로우수가작은테이블 ) 을가지고 HASH_AREA_SIZE에지정된메모리내에해시테이블을만든다. 해시테이블을만든이후부터는 NLJ의장점인순차적인처리형태이다. 그러므로 NLJ과 SMJ의장점을가지고있다. Hash Join은 Basic Join( = ) 만가능하다. NLJ와같이드라이빙테이블의리턴되는로우수와이너테이블의액세스패 096 ORACLE KOREA MAGAZINE
< 그림 3> Sort Merge Join < 그림 4> Hash Join 턴에의에액세스의효율이좌우되지않으며, 조인테이블간에자신의처리범위로만처리량을결정하므로독립적이다. SMJ의단점인많은로우들의처리또는전체적으로 Select List의사이즈의합이큰테이블의조인시정렬작업의 CPU 사용에대한오버헤드및디스크정렬과같은문제점은없다. 그러므로최소한 SMJ보다는우수하다. 한테이블은작은 Rowset 사이즈 ( 리턴되는로우수와 Select List 기준 ), 다른한테이블은아주큰사이즈의조인에유리하다. 이러한경우는반드시작은사이즈를가지고해시테이블을만들어야한다. 단, Hash Join은순서가매우중요하다는점에주의하는데, 작은 Rowset으로해시테이블을만들어야하기때문이다. 힌트를잘못주어서 Big Rowset이리턴되는테이블부터드라이빙된다면 (Build Table), HASH_AREA_SIZE의메모리부족으로 TEMP 디스크 I/O 가발생한다. 그러므로힌트를줄경우반드시드라이빙순서를정확히주어 야한다. 디스크 I/O를피할수없는경우라면, HASH_AREA_SIZE(default : =SORT_AREA_SIZE 2) 를 SQL마다세션레벨에할당해서사용하도록한다 (WORKAREA_SIZE_POLICY가 Manual일경우이거나 Oracle9i Database 이전버전에서 ). 또한 TEMP 테이블스페이스의 Extent Size도충분히크게주도록한다. HASH_MULTIBLOCK_IO_COUNT는옵티마이저에게자동조정하도록설정하지않는다. ALTER SESSION SET HASH_AREA_SIZE= 104857600; 해시메모리의사이즈는 (= Small Table의 Target rows (total selected column s bytes) 1.5) 이상설정하되, PGA의메모리의한계로인해테스트를통해 PGA Memory Allocation Error가발생하지않는범위내에서설 < 그림 5> Hash Join Detail 2005 SPRING 097
정하도록한다. 필요시 10104 Trace 를이용해점검한다. 튜닝시플랜적인튜닝뿐만아니라구조적인튜닝에도집중한다. ALTER SESSION SET EVENTS 10104 TRACE NAME CONTEXT FOREVER ; SQL 튜닝시고려사항 SQL 튜닝시에고려해야할점을정리하면, 다음과같다. 가능한힌트는사용하지않는다. 힌트를많이구사한애플리케이션들은지난호에서설명했던것과같이기준 ( 파라미터및통계정보 ) 이잘못설정된경우가많다. 힌트는최후에어쩔수없는경우에사용하도록한다. - 1차적으로플랜이원하는경우가아닐경우통계정보및 Init.ora의파라미터값들을확인해본다. [USER ALL DBA]_TABLES,[USER ALL DBA]_INDEXES,[USER ALL DBA]_TAB_COLUMNS 등의딕셔너리정보확인, 최종분석시간, 블록수, 로우수, 칼럼의 Distinct 값, 인덱스의 Clustering Factor, Sample Size 등을확인하다. 이들값들이현실데이타와비슷한지확인한다. 통계정보가없거나너무오래됐거나샘플링사이즈가너무작은경우, 현실데이타와다를수있 - Execution이높은것은과다한Loop Query가아닌지검토한다. Loop Query의보완, 최적화가필요하다. - 일회성 ( 상수를사용 ) 비공유 SQL, 특히집중적으로실행되는 Literal SQL은바인드변수기법을사용한다. - 파싱 (parsing) 을줄이는방법으로 Java의 Statement Cache, PRO*C의 RELEASE_CURSOR=NO 등프그래밍언어의효과적인기법들을사용한다. - Array Processing 기능을사용한다. 기본적으로거의대부분의 DB 접속방식에서이들기법들을제공하고있으며, 코딩상의특별한처리없이 PREFETCH 기능이이와같은기능이며, 애플리케이션개발시특별한구현작업없이옵션설정만으로가능하다 (ODBC, JDBC, OO4O, ADO, PRO*C 등 ). - PL/SQL의 Bulk Binding/Bulk Collecting 기능을이용한다. - Aggregate Function 등향상되고효과적인여러질의기능을활용한다. 튜닝시플랜은상수로테스트하지만, 실제로바인드변수로운영되는경우플랜이다를수있다. 프로그램에바인드변수로되어있다면바인드변수로플랜을확인해봐야한다. 다. 이러한경우는통계정보를다시생성한다. - 칼럼에대한통계정보 ( 히스토그램 ) 는안돌리는것을원칙으로한다. 그러나편향된데이타분포도를가지고있다면히스토그램을운영한다. 또한이들칼럼에대한 Where 절의사용되는값들은바인드변수를사용하지않도록한다. 히스토그램을사용하는곳에는상수 (literal) 값을사용하여야플랜이효과적으로풀린다. - 힌트를지정할경우는가능한타이트하게주도록한다. 그렇지않을경우향후플랜이변경될가능성이많기때문이다. 예 > /*+ USE_NL(a b) */ ==> /*+ ORDERED USE_NL(a b)... */ 기타다음사항도고려한다. - Hash Join을사용할경우드라이빙순서, Rowset을고려하여사용한다. - SQL 문장에서반드시필요한칼럼만선택한다. 불필요한칼럼들은정렬, 해시작업에서메모리에로딩해야하므로 TEMP 디스크 I/O의원인이되기도한다. - Chaining % 비율을항상검토하고 Row Chaining 비율이높은테이블에대해서는칼럼의데이타타입조정및블록의 PCTFREE 등을늘리도록한다. 테이블의구조적인문제또는업무적인형태를고려하여 Reorg를하도록한다 (CTAS, MOVE, Exp/Imp 등이용 ). - 힌트는힌트의의미를정확히이해하고합당한힌트를주도록한다. 어설픈힌트는오히려역효과가발생되는경우가많다. 통계정보는운영중에직접돌리지않는다. - 집중적인운영시기에통계정보수집을위한실행은 Library Cache Contention을유발하며, 관련 SQL 및 PL/SQL들을 Invalid시켜성능저하및문제의원인이되기도한다. - 저녁시간의한가한시간을이용해서돌린다 ( 특히시스템의집중사용시기등에유의 ). WORKAREA_SIZE_POLICY=AUTO이면 *_AREA_SIZE는이용하지않으며, 설정해봐야의미가없다. 즉옵티마이저는 *_AREA_SIZE에의해플랜을결정하지않는다. 평균로우길이와블록당로우수도항상주의깊게관찰하여문제점이없는지검토한다 ( 통계정보이용 DBA_TABLES.NUM_ROWS/DBA _TABLES.BLOCKS). 블록당로우가적은경우는 DELETE가많이된경우이므로, Full Table Scan이자주발생된다면 Reorg 대상이될수있다. 그러나 RAC 환경에서는블록경합을줄이기위해인위적으로 PCTFREE를키워블록당로우수를적게가져가는경우도있다. Hash Join과 Sort Merge Join시 TEMP쪽에 I/O가발생하지않도록한다. PRO*C 애플리케이션일경우, 바인드변수의사용여부, RELEASE_ CURSOR=NO, PREFETCH=1000(batch), PREFETCH=100(OLTP) 를권장한다. 098 ORACLE KOREA MAGAZINE
Execution Plan 보기 Execution Plan이란옵티마이저가질의최적화단계에서 RBO 또는 CBO 에의해결정해낸최적의액세스경로정보를가지고 QEP Generator가만 들어낸실행계획이다. 이Execution Plan은 SQL 문장이실행될때필요한 모든정보를포함하고있다. 액세스경로 : 어떠한방법으로데이타에접근할것인가? (Index Scan, Index Fast Full Scan, Full Table Scan 등 ) 조인메소드 : 어떤조인메소드를사용할것인가? 조인순서 : 어떠한조인의순서로풀릴것인가? 다음과같은 Execution Plan을가정하자. ID PID Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=5 Bytes=250) 1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP (TABLE) (Cost=1 Card=5 Bytes=160) 2 1 NESTED LOOPS (Cost=3 Card=5 Bytes=250) 3 2 TABLE ACCESS (BY INDEX ROWID) OF DEPT (TABLE) (Cost=2 Card=1 Bytes=18) 4 3 INDEX (RANGE SCAN) OF DEPT_DEPTNO (INDEX) (Cost=1 Card=1) 5 2 INDEX (RANGE SCAN) OF EMP_DEPTNO (INDEX) (Cost=0 Card=5) 정도의리턴로우가발생할것이라는것을예측할수있다. 통계정보만정확하다면이들값도상당히정확하다고보면된다. Execution Plan을제대로보기위해서는, 각데이타베이스사용자마다 PLAN_TABLE이있어야하는데, PLAN_ TABLE은오라클버전마다다르다. 해당오라클버전의 $ORACLE_ HOME/rdbms/admin/utlxplan.sql을실행하면만들어진다. SQL을실행하지않고 Trace만보는방법도있다. EXPLAIN PLAN < 리스트 1>, SQL*Plus의 SET AUTOTRACE TRACEONLY EXPLAIN < 리스트 2>. 플랜을 PLAN_TABLE에서확인할수도있다. Oracle8i Database 이전까진 Plan_Table에서직접선택하고, Oracle8i Database 이상부터는 Plan_Table에서직접선택하거나 $ORACLE_ HOME/rdbms/admin 위치에서 utxpls.sql(serial Plan) 또는 utlxplp.sql (Parallel Plan) 스크립트를실행하면된다. Oracle9i Database 이전에서는 utxpls.sql, utlxplp.sql 외에 select * from table(dbms_xplan.display); < 리스트 1> Execution Plan 보기 (EXPLAIN PLAN) 실행예 (Oracle9i Database, Oracle10g Database) -- Oracle9i Database R2 Sample (9.2.0.4) 플랜에서나오는각라인을 로우소스 (Row Source) 라고한다. 플랜을보면서처리순서를판단하는것은간단하다. 플랜은트리형태로되어있으며, 자신보다하위레벨이있으면하위레벨부터, 같은레벨이라면위 ( 상 ) 의로우소스부터실행된다. 위플랜의 Optimizer=CHOOSE 에서알수있듯이해당 SQL 문장은옵티마이저모드가 CHOOSE에서플랜이만들어진것이다. 또한플랜에서 Cost= 의항목이나오면 CBO로풀렸다는것이다. RBO인지 CBO인지의판단은옵티마이저모드의항목으로판단하는것이아니라 Cost= 로판단한다는것에주의하자. 위플랜에서 2개의테이블 DEPT와EMP 테이블각각의액세스경로를확인할수있다. 모두인덱스를사용하고있는것이다. 또한조인메소드로는 Nested Loop Join이사용되었다. 조인순서는자신보다하위레벨이있으면하위레벨부터, 같은레벨이라면위의로우소스부터실행된다는법칙을적용해보면, ID를기준으로4 -> 3 -> 5 -> 2 -> 1 -> 0의순서로처리된다. 단조인메소드가 Nested Loop이기때문에 3에서리턴되는로우수만큼다음단계가반복된다. 그러므로조인순서는 DEPT -> EMP 순으로 Nested Loop로처리될것이라는것을알수있다. 또한 Card=5 는Computed Cardinality를나타내며, 몇건의로우가리턴될것인지를 CBO가통계정보를이용해서계산해낸값이다. Bytes=250 은리턴될로우들의바이트를나타내므로 5 로우에 250바이트 SQL> SET LINESIZE 130 SQL> SET PAGESIZE 0 SQL> SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = TEST_MYSQL 3 FOR SELECT ename, job, sal, dname 4 FROM emp, dept 5 WHERE emp.deptno = dept.deptno 6 AND NOT EXISTS 7 (SELECT * 8 FROM salgrade 9 WHERE emp.sal BETWEEN losal AND hisal); Explained. SQL> EXPLAIN PLAN...FOR <SQL> 로플랜을작성한다. SQL> -- 오른쪽의 Script로실행해도됨. @?/rdbms/admin/utlxpls.sql SQL> select * from table(dbms_xplan.display); 2005 SPRING 099
----------------------------------------------------- Id Operation Name Rows Bytes Cost ----------------------------------------------------- 0 SELECT STATEMENT 14 476 12 단위스텝별예상 로우및바이트를 1 MERGE JOIN ANTI 14 476 12 예측할수있다. 2 SORT JOIN 14 392 8 상위단은하위단 을포함한다. *3 HASH JOIN 14 392 5 4 TABLE ACCESS FULL EMP 14 238 2 5 TABLE ACCESS FULL DEPT 4 44 2 *6 FILTER *7 SORT JOIN 8 TABLE ACCESS FULL SALGRADE 5 30 2 ----------------------------------------------------- Oracle9i Database부터 Predicate Information (identified by operation id): --------------------------------------- Access & Filter Predicate 정보가추가되었다. 이정보를이용해서필터조건및 조인조건을확인할수있다. 3 - access( EMP. DEPTNO = DEPT. DEPTNO ) 6 - filter( EMP. SAL <= SALGRADE. HISAL ) 7 - access( EMP. SAL >= SALGRADE. LOSAL ) filter( EMP. SAL >= SALGRADE. LOSAL ) Note: cpu costing is off 24 rows selected. -- 10g Sample (10.1.0.2) SQL> -- @?/rdbms/admin/utlxpls.sql SQL> select * from table(dbms_xplan.display); Oracle10g Database부터디폴트로 CPU 반영비중과예측시간을확인할수있다. Oracle10g Database부터는비 용기준이시간이므로가능하다. -------------------------------------------------------------------- Id Operation Name Rows Bytes Cost(%CPU) Time -------------------------------------------------------------------- 0 SELECT STATEMENT 14 532 18 (17) 00:00:01 1 MERGE JOIN ANTI 14 532 18 (17) 00:00:01 2 SORT JOIN 14 420 12 (17) 00:00:01 * 3 HASH JOIN 14 420 11 (10) 00:00:01 4 TABLE ACCESS FULL DEPT 4 52 5 (0) 00:00:01 5 TABLE ACCESS FULL EMP 14 238 5 (0) 00:00:01 * 6 FILTER * 7 SORT JOIN 5 40 6 (17) 00:00:01 8 TABLE ACCESS FULL SALGRADE 5 40 5 (0) 00:00:01 -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------- 3 - access( EMP. DEPTNO = DEPT. DEPTNO ) 6 - filter( EMP. SAL <= HISAL ) 7 - access( EMP. SAL >= LOSAL ) filter( EMP. SAL >= LOSAL ) < 리스트 2> Execution Plan 보기 (SQL*Plus의 SET AUTOTRACE) 실행예 (Oracle9i Database) 9iR2 Sample (9.2.0.4) ======================> SQL> SET AUTOTRACE ON SQL> SQL> SELECT ename, job, sal, dname 2 FROM emp, dept 3 WHERE emp.deptno = dept.deptno 4 AND NOT EXISTS 5 (SELECT * 6 FROM salgrade 7 WHERE emp.sal BETWEEN losal AND hisal); no rows selected (1) SQL 문장이실행되었다. 실행은하지않고플랜만확인하려면 SET AUTOT TRACEONLY EXPLAIN 으로처리한다. Execution Plan ------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=14 Bytes=756) 1 0 MERGE JOIN (ANTI) (Cost=13 Card=14 Bytes=756) 2 1 SORT (JOIN) (Cost=8 Card=14 Bytes=392) 3 2 HASH JOIN (Cost=5 Card=14 Bytes=392) 4 3 TABLE ACCESS (FULL) OF EMP (Cost=2 Card=14 Bytes=238) 5 3 TABLE ACCESS (FULL) OF DEPT (Cost=2 Card=4 Bytes=44) 6 1 FILTER 7 6 SORT (JOIN) 8 7 TABLE ACCESS (FULL) OF SALGRADE (Cost=2 Card=409 Bytes=10634) (2) 내부적으로 EXPLAIN PLAN... 으로처리된플랜정보를보여준다. 실제 Runtime Plan은아니다. 100 ORACLE KOREA MAGAZINE
ACCESS_PREDICATES, FILTER_PREDICATES FROM v$sql_plan p Statistics --------------------------------------- 0 recursive calls (3) 실제실행된 Execution Statistics를보여준다. 전체읽어들인 0 db block gets 블록수는 db block gets + consistent gets. 21 consistent gets 0 physical reads 0 redo size 376 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed Cached Execution Plan(V$SQL_PLAN) Explain Plan으로보는플랜과실제실행시플랜이다를수있다. 이는 Explain Plan은단지SQL 문장에대한구조적인분석하에예상플랜을만 들어내기때문이다. 예를들어 select, * from emp where empno = :B1 의SQL 문장을실행한다고생각해보자. Empno가인덱스가설정되어있고 Character 타입으로되어있으며, 인덱스를이용하는것이효과적이라고가정하면 Explain plan은 Empno 의인덱스를이용해서풀릴것이다. 그러나실제실행시바인드변수인 :B1 에Character 타입이아니라 Number 타입으로바인드되었다면인덱 스를이용할수없는것이다. 또한 Oracle9i Database의 Bind Peeking과 같은기능은처음바인딩되는상수에의해플랜을결정하는데, 이방식에의 해서도그러한상황이있을수있다. 이와같이Explain Plan을보는것과실제예측실행시간이너무차이가 난다면 Runtime Plan을확인해볼필요가있다. Oracle9i Database부터 V$SQL_PLAN의성능뷰를제공하며, 현재캐쉬화되어있는 SQL 문장들에 대한 Runtime Plan을확인해볼수있다. V$SQL_PLAN은 PLAN_TABLE 과칼럼항목이거의같다. SELECT hash_value, (select sql_text from v$sql s where s.hash_value = p.hash_value and s.address = p.address and rownum <= 1), child_number,id,parent_id, LPAD(,2*(depth)) OPERATION DECODE(OTHER_TAG,NULL,, * ) DECODE(OPTIONS,NULL,, ( OPTIONS ) ) DECODE(OBJECT_NAME,NULL,, OF OBJECT_NAME ) DECODE(OBJECT#,NULL,, (Obj# TO_CHAR(OBJECT#) ) ) DECODE(ID,0,DECODE(OPTIMI- ZER,NULL,, Optimizer= OPTIMIZER)) DECODE(COST,NULL,, (Cost= COST DECODE(CARDINALITY,NULL,, Card= CARDINALITY) DECODE(BYTES,NULL,, Bytes= BYTES) ) ) SQLPLAN,OBJECT_NODE, PARTITION_START,PARTITION_STOP, PARTITION_ID, CPU_COST, IO_COST, TEMP_SPACE, DISTRIBUTION, OTHER, START WITH ID=0 and hash_value = [XXXXXXXXXX] CONNECT BY PRIOR ID=PARENT_ID AND PRIOR hash_value=hash_value AND PRIOR child_number=child_number ORDER BY hash_value,child_number,id,position SQL_TRACE와 TKPROF를이용한 SQL 튜닝 SQL_TRACE 또는 10046 Trace Enable/Disable SQL_TRACE는애플리케이션이 SQL 문장들을처리하는과정을 Trace로남기게하는기능이다. 10046 Trace 기능은 SQL_TRACE의기능에추가적인정보를기록한다. 레벨1은 SQL_TRACE 기능과같으며, 레벨4는바인드변수정보, 레벨8은 Wait Event 정보, 레벨12는바인드변수정보와 Wait Event 정보를같이보여준다. 주의할점은 Trace를 On 했으면반드시모니터링후 Off 해야한다는것이다. 그렇지않을경우 Disk Full이발생할수있으므로반드시주의해야한다. Trace는init.ora의user_dump_dest에서지정된곳에생성된다. 인스턴스레벨 : init.ora 파라미터이용 sql_trace = {TRUE FALSE} 또는 event = 10046 trace name context forever, level {1 4 8 12} 세션레벨 : SQL*Plus 또는애플리케이션루틴내 ALTER SESSION SET SQL_TRACE = {True False}; 또는 10046 Trace On alter session set events 10046 trace name context forever, level {1 4 8 12} ; 10046 Trace Off alter session set events 10046 trace name context off ; 또는 EXECUTE dbms_session.set_sql_trace({true False}); 또는 EXECUTE dbms_system.set_sql_trace_in_session(session_id, serial_id, {True False}); Execution Plan 보기 (SQL_TRACE,10046 Trace와 TKPROF) 실행예 (Oracle9i Database) Oracle9i Database Release 2(9.2.x) 부터는튜닝대상이어느곳인지 ( 플랜상의스텝 ) 판단하기쉽게획기적으로개선되어, 초보자도튜닝대상을쉽게찾을수있다. 또한사용자가 SQL 문장을실행해서결과값을받는서비스 2005 SPRING 101
타임은 DB 실행시간 + 대기시간 이다. Oracle9i Database부터는 SQL TRACE의레벨에따라Wait 정보의요약정보도같이보여주므로어느곳에병목현상이있는지판단하기쉬워졌다. 다음은Oracle9i Database Release 2(9.2.x) 부터개선된사항이다 < 리스트 3>. AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal) 10046 Trace 레벨에따라 Wait( 레벨 8, 레벨 12일경우 ) 정보도표시 각로우소스 ( 플랜상의스텝 ) 마다 Statistics 표시 Oracle9i Database에서는 time=xxxxxxxxxx 정보가 1/1000000초단위로나타난다. Oracle8i Database까지는 1/100초였다. Runtime Plan & TKPROF 실행시플랜주의 TKPROF. EXPLAIN=xxxx/yyyy일경우플랜이 2개 (Runtime Plan & Tkprof 실행시점의플랜 ) (1) call count cpu elapsed disk query current rows ------- ------ ------- -------- ------- ------- ------- ------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.06 0.09 0 21 0 0 ------- ------ ------- -------- ------- ------- ------- ------- total 3 0.06 0.09 0 21 0 0 실행수 Sec 처리된 처리된 블록수 로우수 < 리스트 3> Oracle9i Database R2(9.2.0.4) 샘플 -- alter session set sql_trace=true; alter session set events 10046 trace name context forever, level 12 ; SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal); ORA9iR2L@oracle> tkprof ora9ir2l_ora_2137.trc ora9ir2l_ora_2137.prf explain=scott/tiger width=132 TKPROF: Release 9.2.0.4.0 - Production on Mon Nov 22 16:30:54 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. ORA9iR2L@oracle> vi ora9ir2l_ora_2137.prf SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno (2) (3) (4) Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 59 (SCOTT) Rows Row Source Operation ----- --------------------------------------------------- 0 MERGE JOIN ANTI (cr=21 r=0 w=0 time=94397 us) 14 SORT JOIN (cr=14 r=0 w=0 time=92823 us) 14 HASH JOIN (cr=14 r=0 w=0 time=92237 us) 14 TABLE ACCESS FULL OBJ#(30627) (cr=7 r=0 w=0 time=860 us) 4 TABLE ACCESS FULL OBJ#(30628) (cr=7 r=0 w=0 time=275 us) 14 FILTER (cr=7 r=0 w=0 time=1238 us) 40 SORT JOIN (cr=7 r=0 w=0 time=856 us) 5 TABLE ACCESS FULL OBJ#(30630) (cr=7 r=0 w=0 time=383 us) Oracle9i Database Release 2부터는각로우소스 ( 스텝 ) 별일량을확인할수있는통계정보를보여준다. Rows Execution Plan ----- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 MERGE JOIN (ANTI) 14 SORT (JOIN) 14 HASH JOIN 14 TABLE ACCESS GOAL: ANALYZED (FULL) OF EMP 4 TABLE ACCESS GOAL: ANALYZED (FULL) OF DEPT 14 FILTER 40 SORT (JOIN) 5 TABLE ACCESS (FULL) OF SALGRADE 102 ORACLE KOREA MAGAZINE
(5) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 1.57 1.57 (1) SQL 문장처리정보 query : Consistent Read(CR) current : Current Read(SCUR) disk : Physical Read Parse : 파싱 (Parse Request 수, 실제하드파싱은아님 ) Execute : SQL 문장실행수 Fetch : Fetch 회수 cpu, elapsed : CPU 사용시간및전체처리시간 Logical Read = Query + Current(Logical Read는 Physical Read를포함한다. 그러므로 Logical Read >= Physical Read이다. 그러나반대의경우라면, 임시테이블스페이스로디스크 I/O가발생했다고보면된다 (Sort, Hash, Bitmap Operation 등에의해 ). 여기서의관점은 Logical Read는전체 Block Buffer Operation의양을나타내므로이들처리블록수를줄이는것이튜닝의관건이다. Fetch 회수와로우수와의관계에서 Fetch = Rows일경우는 Single Row Fetch의처리를하였고, Fetch <= Rows일경우는 Array Fetch 또는 Prefetch 처리를한경우이다. 여기서의관점은성능향상을위해 Array Fetch 또는 Prefetch를효과적으로사용하고있느냐의관점이다. Parse 수와 Execute 관점에서본다면, 같은 SQL 문장을매번실행할때마다 Parse Request를할필요가없다는것이다. 이를줄이는방법으로 Pro*C의 RELEASE_CURSOR=NO (Dynamic SQL은해당안됨 ), Java 의 Statement Cache 등이있다. (2) SQL 문장의파싱정보 Misses in library cache during parse 의값이0이면소프트파싱, 1이면 SGA Cache에없어서하드파싱이발생한경우이다. 옵티마이저모드정보및파싱스키마정보를확인할수있다. Recursive SQL일경우 Recursive Depth 정보도나타난다. 수를나타낸다. Oracle9i Database Release 2부터는각로우소스 ( 스텝 ) 별일량을확인할수있는 Statistics정보를보여준다. 상위스텝은하위스텝의값들을포함한다. 그러므로전체일량중에서병목현상이어느곳에서가장많이발생되는지명확히알수있다. 즉튜닝의포인트를정확히확인할수있다. cr= : Consistent Read 총블록수 r= (Oracle Database 10g는 pr=) : Physical Read 총블록수 w= (Oracle Database 10g는 pw=): Physical Write 총블록수 time= : 전체처리시간 ( 마이크로초 (1/1000000초) 단위 ) 여기서가장상위의 cr=21은 (1) 의 SQL 문장처리정보에서확인한 Logical Read(= Query + Current) 의값과같다는것을확인할수있다. 또한전체처리시간은 time=94397 이므로약 0.09초걸렸다는것을알수있다. 전체처리일량중병목을찾고자한다면전체일량중가장많은블록처리및시간으로탑다운식으로찾아내려가면누구든지쉽게찾을수있을것이다. 해당부분을찾은뒤, 조인메소드의변경 -> 조인순서의변경 -> 액세스경로변경의중심으로튜닝을실시하면될것이다. (5) SQL 문장 Wait 정보 이부분은 10046 Event의 8 또는 12 레벨에의해생성되며, SQL 문장을실행하는데발생되었던 Wait 정보의요약값을나타낸다. 사용자가느끼는실행시간은 SQL 문장의실행시간과 Wait 시간의합이므로이들 Wait 시간을주의깊게관찰할필요가있다. Times Waited : Wait Event가발생했던횟수 Max. Wait : 최대로길었던 Wait 시간 ( 초단위 ) Total Waited : 전체 Wait 시간 ( 초단위 ) TKPROF 아웃풋의로우값의버전별변화 TKPROF의아웃풋형태가 Oracle8i Database 이전까지는테이블또는인덱스를찾아들어간로우수를나타내며, Oracle8i Database서부터로우값은필터링되어리턴된로우수를나타낸다. TKPROF 아웃풋에로우값이 0 으로나오는경우는해당SQL 문장의커서가종료되기전에Trace가종료되었거나끊긴경우이다. Runtime Plan은커서가종료되는시점에기록되므로이값이0으로나올수있다. (3), (4) SQL 문장 Execution Plan 정보 (Runtime Plan/TKPROF 실행시플랜 ) TKPROF의 explain=xxx/xxx을주게되면2개의플랜이만들어진다. 처음것이 Runtime Plan이며, 두번째가 TKPROF 유틸리티를돌린시점의 EXPLAIN PLAN에의해만들어진플랜이다. 이들플랜은 TKPROF를실행한시점이다를수있으므로다를수가있는것이다. Rows : Oracle Database 8.0까지는액세스했던, 즉찾아들어갔던로우수로나타나며, Oracle8i Database부터는조건에의해필터되어리턴된로우 -- Oracle Database 8.0.x의플랜예 select /*+ ORDERED USE_NL(d e) */ * from dept d, emp e where e.deptno = d.deptno and d.deptno = 10 2005 SPRING 103
Rows Execution Plan ----- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 3 NESTED LOOPS 4 TABLE ACCESS GOAL: ANALYZED (FULL) OF DEPT 14 TABLE ACCESS GOAL: ANALYZED (FULL) OF EMP DEPT 테이블에서 Full Table Scan에의해4로우가액세스되었으며, EMP 테이블도 Full Table Scan에의해14로우가액세스되었다. Nested Loop Join에의해3로우가리턴되었다. -- Oracle 9.2.x의Plan예 select /*+ ORDERED USE_NL(d e) */ * from dept d, emp e where e.deptno = d.deptno and d.deptno = 10 Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 3 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF DEPT 3 TABLE ACCESS GOAL: ANALYZED (FULL) OF EMP DEPT 테이블에서 Full Table Scan에의해1로우가리턴되었으며, EMP 테이블도 Full Table Scan에의해3로우가리턴되었다. Nested Loop Join에의해3로우가리턴되었다. Oracle Database 10g의튜닝관련신기능 Oracle Database 10g에서튜닝기능은더욱개선, 강화되었다. 자동성능진단과튜닝 Oracle Database 10g부터는자가관리 (self-management) 기능이강화되었으며, Automatic Statistics Collection, Automatic Database Diagnostic Monitoring(ADDM), Automatic SQL 튜닝과같은기능을튜닝에이용할수있다. Automatic Workload Repository(AWR) 는셀프튜닝과문제진단을목적으로성능정보를수집하고처리하고유지한다. Automatic Database Diagnostic Monitor(ADDM) 는 AWR에수집된정보를주기적으로분석하고오라클시스템에대한문제의원인을진단하고권장사항을제공한다. Oracle SQL Tuning Advisor는 SQL 문장의최적화를위해빠르고효과적인방법을제공한다. 내부적으로 DBMS_SQLTUNE 패키지를제공하며, 사람이직접하던튜닝을이제는 Oracle SQL Tuning Advisor의기능을이용해문제의원인을상세하게찾아내고개선안을얻을수있으며, 개선안을수용하면같은 SQL 문장이실행되었을경우튜닝된결과로플랜이처리된다. Application End to End Tracing Application End to End Tracing 기능은클라이언트식별자 (Login ID), 서비스명 (Application Group), 모듈명또는액션명으로시스템자원을과다하게사용하는 SQL 문장과같은워크로드의원인을찾을수있도록해주는유용한기능이다. 즉멀티티어환경에서의성능디버깅을쉽게할수있는기능이다. trcsess 유틸리티 trcsess는커맨드라인유틸리티로, 특정검색조건에해당하는내용을원하는여러Trace 파일에서통합해서한파일로만들어준다. AP 서버를두고있는분산트랜잭션에서여러 AP 서버의 Trace에분산되어있는 Trace 정보에서원하는검색조건인클라이언트식별자, 서비스명, 모듈명또는액션명등으로찾아볼수있다. Application End to End Tracing의기능에의해여러파일에흩어져있는 Trace 내용을모으는데유용하다. Automatic Optimizer Statistics Collection 각오브젝트들에대한옵티마이저통계정보를자동으로수집한다. 통계정보가맞지않거나없는경우에자동으로통계정보를수집할수있다. DBA는어떤오브젝트에대해서통계정보를실행해야되는지, 어떤통계정보가맞지않는지에대해서신경을쓰지않아도되며, 직접실행할필요도없다. 옵티마이저통계정보는 GATHER_STATS_JOB로자동으로수집할수있으며, DB를생성하거나업그레이드할때자동으로설정된다 ( 디폴트 ). 이기능은 Missing Statistics( 통계정보가없음 ) 또는 Stale Statistics( 대량의데이타로딩등에의해로우들이 10% 이상변경된경우 ) 인경우에이들오브젝트들을관리하고이들오브젝트에대해서자동으로수집한다. 수집시기도시스템자원이한가한새벽시간에운영된다. 지금까지 2회에걸쳐 Oracle Optimizer의원리이해를기반으로 SQL 과애플리케이션의튜닝방법에대해서설명하였다. 애플리케이션튜닝부분은자세히다루지는않았지만, 이번기회에 Oracle Optimizer의원리를이해하고, SQL 튜닝기법을활용하여시스템의안정적인개발및운영에도움이되었으면하는바람이다. 104 ORACLE KOREA MAGAZINE