TECHNICAL WHITE PAPER Tibero Optimizer SQL Execution Plan October 2012

Size: px

Start display at page:

Download "TECHNICAL WHITE PAPER Tibero Optimizer SQL Execution Plan October 2012"

승우 연
4 years ago
Views:

1 Tibero Optimizer SQL Execution Plan

2 목차 1. Introduction 2. Watching SQL Plan 2.1. SQL Plan 이란? 2.2. SQL Plan 확인하기 2.3. Understanding SQL Plan 3. Conclusion Optimizer 에의해만들어진 SQL 플랜을확인한는여러방법들을소개하고플랜에서보여주는정보의의미에대해알아본다. 그리고, 운영중인시스템에서비효율적으로수행중인 SQL 과플랜을찾아내는방법을이해함으로써튜닝대상 SQL 을효율적으로찾을수있는방법을소개한다. 1. Introduction Tibero 의 optimizer 는 SQL 을수행하기위한플랜을만들 때, cost 기반으로가장효율적인플랜을만들게된 다. cost 란 SQL 을수행하면서발생하는디스크 IO 와 CPU 연산을모두고려해서만든수치로서절대비교가가능한 값이다. optimizer 는각플랜단계별로발생할디스크 IO, CPU 연산을테이블의통계정보를기반으로예측하면서 cost 값을계산하여가장효율적인플랜을만들게된다. 하지만, 이 cost 값계산은다음과같은상황에의해잘못 예측되는경우전체적으로비효율적인플랜을만들수도 있다. cost 모델의잘못된가정 오래된통계정보 통계정보가없을시데이타 sampling 오류 조건문간의의존성 예측불가능한함수가포함된조건문 조건문에사용된사용자파라미터 join 순서결정시너무많은조합에따른 pruning 에의한 local optimization Tibero 에서는 optimizer 에의해만들어진플랜의단계별 cost 값들을쉽게조회하고, 분석할수있는기능을다양한방법으로제공하고있다. 이문서에서는플랜을확인하는여러방법과플랜에대한기본적인정보와값들의의미에대해서설명하도록하고, 마지막으로는 Summary 현재운영중인시스템에서비효율적으로수행되는쿼리를 찾을수있는여러방법을소개하도록하겠다. 2

3 optimizer 에의해만들어진플랜을확인하는방법은다음 2. Watching SQL Plan 2.1. SQL Plan 이란? optimizer 에서만들어낸플랜은여러개의수행 operation 의트리형태로이루어져있고, SQL 수행을하게되면이 플랜을통해제일하단에서만들어진 row 들이위 operation 으로전달되면서최상단 operation 의결과 row 가쿼리의최종결과가된다. 각단계별 operation 을플랜노드라고부르고노드의종 류는현재 Tibero 5.0 에서 65 개가있는데, 노드를크게분 류해보면다음과같다. ( 다음장에 operation 종류에대한 설명이있다 ) row 생성노드 (table, index scan,...) join 노드 (nested join, sort merge join, hash join, index join,...) dml 노드 (insert, update, delete, merge,...) 그밖의노드 (orderby, groupby, connectby,...) 플랜노드에는자기의해당 operation 을수행하기위한필 요한정보를담고있어서, SQL 수행시에이정보들을참 고하게된다. 이런수행에필요한자세한정보는사용자에 과같다. V$SQL_PLAN 뷰를통해보기 TBSQL 프로그램의 AUTOTRACE 기능을통해보기 SQLTRACE 기능을이용해서 TBPROF 프로그램으로수행결과와같이보기 EXPLAIN PLAN 문을통해 PLAN_TABLE 에저장하고보기가 ) V$SQL_PLAN 뷰를통해보기플랜을확인하는가장기본적인방법으로서, V$SQL_PLAN 뷰는 library cache를직접 access하여등록된플랜들의정보를가상의 table 형태로만들어주는 DPV(dynamic performance view) 이다. 하나의 SQL 플랜에대해각플랜노드정보가각 row 로표현되고, row의각 column들은해당플랜노드를분석하는데필요한모든정보를포함하고있다. 이뷰의각컬럼의의미는다음과같다. 게제공하지는않고플랜노드별 operation 종류, 추가 option, optimizer에서계산한노드의 cost 값과 cardinality 정도의정보만조회가가능하다. optimizer 단계에서 cost 계산방식은 SQL 수행과마찬가지로최하단부터 cost 계산을하면서최상단플랜노드까지 cost 를누적하는방식으로이루어지는데, 중간중간그리고최종적으로가장 cost 가작은플랜을선택하게된다. 이렇게최적으로판단되어만들어진 SQL 플랜은 library cache 에등록되어같은 SQL 에대한수행요청시에재활용되어 optimizer 단계를건너뛰고 SQL 수행을하게된다. 앞의 optimizer 단계를거치는 parsing 을 hard parsing 이라하고, optimizer 단계를건너뛰고 library cache 에서해당플랜을가져오는 parsing 을 soft parsing 이라고부른다 SQL Plan 확인하기 3

4 컬럼이름 컬럼타입 설명 HASH_VALUE NUMBER library cache에서의 hash 값 SQL_ID NUMBER 플랜의 unique ID OPERATION VARCHAR 플랜노드이름 OBJECT# NUMBER 플랜노드에서 access 하는 object 번호 OBJECT_OWNER VARCHAR 플랜노드에서 access 하는 object 를소유하는유저이름 OBJECT_NAME VARCHAR 플랜노드에서 access 하는 object 이름 OBJECT_TYPE VARCHAR 플랜노드에서 access 하는 object 타입 ID NUMBER 플랜노드 ID PARENT_ID NUMBER 플랜노드의부모 ID DEPTH NUMBER 플랜노드의트리깊이 POSITION NUMBER 같은부모의자식들간의순서 SEARCH_COLUMNS NUMBER index 검색에서사용되는컬럼개수 COST NUMBER optimizer에서예측한플랜노드의총 cost CPU_COST NUMBER optimizer에서예측한플랜노드의 CPU cost IO_COST NUMBER optimizer에서예측한플랜노드의 IO cost CARDINALITY NUMBER optimizer에서예측한플랜노드의최종 row 수 PSTART NUMBER partition table/index인경우시작 partition 번호 PEND NUMBER partition table/index인경우끝 partition 번호 OTHERS VARCHAR dblink를사용하는플랜노드의경우 remote sql ACCESS_PREDICATES VARCHAR join, index scan에서사용하는 predicate 정보 FILTER_PREDICATES VARCHAR filter로사용하는 predicate 정보 이뷰는 library cache 에등록된모든플랜을보여주기때 문에원하는 SQL_ID 값을조건절에입력하여보고자하는 특정플랜만조회해야한다. 다음예제쿼리를수행하면서 플랜을 V$SQL_PLAN 뷰로조회하는방법에대해서설명 하도록하겠다. 4

5 SQL> select d.deptno, sum(sal) from dept d, emp e where d.deptno = e.deptno and e.mgr is not null group by d.deptno order by d.deptno; DEPTNO SUM(SAL) rows selected. SQL text 를가지고해당플랜의 SQL_ID 를찾기위해서는 다음과같은쿼리를수행한다. SQL> select sql_id from V$SQLTEXT where upper(sql_text) like '%DEPT D, EMP E%'; 해당플랜의 SQL_ID 를구했으면 V$SQL_PLAN 에다음과 같은쿼리를수행하여조회하면해당 SQL 의플랜정보를 한눈에볼수가있다. SELECT SUBSTRB(TO_CHAR(ID), 1, 3) LPAD(' ', LEVEL * 2) UPPER(OPERATION) DECODE(OBJECT_NAME, NULL, NULL, ': ' OBJECT_NAME) ' (Cost:' COST ', %%CPU:' DECODE(COST, 0, 0, TRUNC((COST - IO_COST) / COST * 100)) ', Rows:' CARDINALITY ') ' DECODE(PSTART, '', '', '(PS:' PSTART ', PE:' PEND ')') AS "Execution Plan" FROM (SELECT * FROM V$SQL_PLAN WHERE SQL_ID = 281) START WITH DEPTH = 1 CONNECT BY PRIOR ID = PARENT_ID AND PRIOR SQL_ID = SQL_ID ORDER SIBLINGS BY POSITION; Execution Plan GROUP BY (SORT) (Cost:23, %%CPU:0, Rows:4) 2 HASH JOIN (Cost:24, %%CPU:0, Rows:13) 3 INDEX (FULL): PK_DEPT (Cost:1, %%CPU:0, Rows:4) 4 TABLE ACCESS (FULL): EMP (Cost:23, %%CPU:0, Rows:13) 5

6 해당 SQL 플랜은다음과같은형태의트리구조를가지고 있는 SQL 이다. SET AUTOTRACE OFF : 수행결과만보여주기 (default) SET AUTOTRACE ON : 수행결과와 trace 정보 (EXPLAIN, STATISTICS) 보여 주기 SET AUTOTRACE ON EXPLAIN : 수행결과와 trace 정보 (EXPLAIN) 보여주기 SET AUTOTRACE ON STATISTICS : 수행결과와 trace 정보 (STATISTICS) 보여주기 SET AUTOTRACE ON PLANSTAT : 수행결과와 trace 정보 (PLANSTAT) 보여주기 SET AUTOTRACE ON EXPLAIN PLANSTAT 나 ) TBSQL 프로그램의 AUTOTRACE 기능을이용하기 V$SQL_PLAN는많은정보를가지고있지만, 위와같이실제로사용하기에는여러단계에필요한정보가있어서많은번거로움이따른다. Tibero는 TBSQL이라는인터액티브쿼리수행프로그램을제공하고있는데, 이프로그램에서제공하는많은기능들을이용하면쉽게 SQL을수행하고결과를확인할수있다. TBSQL의 AUTOTRACE 기능은방금수행한 SQL에대한플랜정보와수행통계정보를바로바로보여주는기능으로다양한 option을제공하고있고매우편리하다. 동작방식은위에서수동으로 V$SQL_PLAN 뷰로플랜을확인한것과같이 TBSQL 프로그램내부에서방금수행한 SQL의 SQL_ID를구해서 V$SQL_PLAN 뷰에조회해서보여주는방식이다. AUTOTRACE 옵션을사용하려면다음과같이 TBSQL 프로그램내에서 SET 명령어를이용하면된다. : 수행결과와 trace 정보 (EXPLAIN, PLANSTAT) 보여주기 ( 세가지 trace 정보조합가능 ) SET AUTOTRACE TRACEONLY : 수행은하지만결과출력없이 trace 정보 (EXPLAIN, STATISTICS) 만보여주기 SET AUTOTRACE TRACEONLY EXPLAIN : 수행은하지않고결과출력없이 trace 정보 (EXPLAIN) 만보여주기 SET AUTOTRACE TRACEONLY STATISTICS : 수행은하지만결과출력없이 trace 정보 (STATISTICS) 만보여주기 SET AUTOTRACE TRACEONLY PLANSTAT : 수행은하지만결과출력없이 trace 정보 (PLANSTAT) 만보여주기 TBSQL에서 AUTOTRACE 기능으로출력한화면예는다음과같다. TRACE에서보여주는정보에대해서는다음장에서설명하도록하겠다. SET AUTOT[RACE] {OFF ON TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] [PLANS[TAT]] 보여주는 TRACE 정보는 EXPLAIN ( 플랜노드정보 ), STATISTICS ( 수행후전체통계정보 ), PLANSTAT ( 플랜노드별수행정보 ) 이다. 옵션이많아서사용하기복잡해보이지만, option의조합은다음과같다. 6

7 SQL> SET AUTOTRACE ON EXPLAIN STATISTICS PLANSTAT SQL> select d.deptno, sum(sal) from dept d, emp e where d.deptno = e.deptno and e.mgr is not null group by d.deptno order by d.deptno; DEPTNO SUM(SAL) rows selected. : 528 Plan Hash Value: Execution Plan GROUP BY (SORT) (Cost:23, %CPU:0, Rows:4) 2 HASH JOIN (Cost:24, %CPU:0, Rows:13) 3 INDEX (FULL): PK_DEPT (Cost:1, %CPU:0, Rows:4) 4 TABLE ACCESS (FULL): EMP (Cost:23, %CPU:0, Rows:13) Predicate Information access: ("D"."DEPTNO" = "E"."DEPTNO") 4 - filter: ("E"."MGR" IS NOT NULL) VALUE EVENT_NAME db block gets 20 consistent gets 0 physical reads 0 redo size 0 sorts (disk) 1 sorts (memory) 3 rows processed Execution Stat GROUP BY (SORT) (Time:.03 ms, Rows:3, Starts:1) 2 HASH JOIN (Time:.02 ms, Rows:13, Starts:1) 3 INDEX (FULL): PK_DEPT (Time:.01 ms, Rows:4, Starts:1) 4 TABLE ACCESS (FULL): EMP (Time:.04 ms, Rows:13, Starts:1) 7

8 위와같이하나의 SQL을수행한후플랜정보, SQL 수행정보, 플랜노드별수행정보등을한눈에파악할수있기때문에 SQL 분석에많은도움이된다. TBSQL의다른기능들인스풀링, 사용자변수, 포맷팅등을함께사용하면더편하게분석할수있으므로이기능들을 TBSQL 매뉴얼에서참고하기바란다. 다 ) SQLTRACE 기능으로수행결과와함께보기특정 SQL에대한플랜을분석하는것이아니라, 실제운영중인서버에서수행되고있는불특정다수의 SQL 플랜들을분석하고싶을때는 SQLTRACE 기능을이용하는것이가장좋은방법이다. 특정시간동안 SQLTRACE를켜고끄기위해서는다음과같은 ALTER 문을수행한다. SQL> alter system set sql_trace=y;... SQL> alter system set sql_trace=n; 사용할때주의할점은 ALTER SYSTEM의경우, 기존에접속을맺고있는세션에대해서는적용이안되고, ALTER SYSTEM이수행된이후에새로접속을맺는세션부터해당파라미터변경이적용받는다. SQLTRACE가적용된세션에서수행된 SQL은매수행마다플랜노드별수행정보를 SQL_TRACE_DEST 파라미 터에정의된경로에확장자 TRC 파일로남기게된다. 매수행마다파일에수행정보를 write 하게되어성능저하가발생하지만, 세션간경합없이각자파일에남기므로일정비율 (5-10%) 로만성능저하가발생한다. 하지만, optimizer에서예상한정보와실제수행정보가동시에남기때문에정확한문제분석이가능하고, 실제운영에서수행되는 SQL 플랜정보이므로정기적으로분석해보는것이많은도움이될수있다. SQLTRACE를통해만들어진 TRC 파일은수행중에최소한의성능피해를주기위한포맷으로정보를남기기때문에바로읽고분석하기에는많은불편이있으므로이정보를같은 SQL 수행별로통합하거나사용자가보기더편한포맷으로변환해주는툴인 TBPROF를이용해야한다. TBPROF 프로그램에도많은옵션이있는데사용법은다음과같다. tbprof tracefile outputfile [print= ] [sys= ] [sort= ] [aggregate= ] print=integer ( 첫 n개의 SQL 수행정보만출력 ) sys=yes no ('SYS' 사용자로수행된 SQL 수행정보를제거 ) aggregate=yes no ( 같은 SQL 수행정보를합쳐서보여주기 ) sort=options ( 주어진옵션으로정렬해서출력. 옵션종류는매뉴얼참고 ) SQL 수행후 TBPROF 프로그램을이용해서변환한파일내용은다음과같다. 8

9 select d.deptno, sum(sal) from dept d, emp e where d.deptno = e.deptno and e.mgr is not null group by d.deptno order by d.deptno; stage count cpu elapsed current query disk rows parse exec fetch sum rows execution plan group by (sort) (et=36, cr=0, cu=0, co=23, cpu=0, ro=4) 13 hash join (et=26, cr=0, cu=0, co=24, cpu=0, ro=13) 4 index (full) PK_DEPT(3456) (et=16, cr=1, cu=0, co=1, cpu=0, ro=4) 13 table access (full) EMP(3457) (et=50, cr=0, cu=2, co=23, cpu=0, ro=13) 차도운영서버에부하를줄수있는상황에서서버에 V$SQL_PLAN 뷰나 TBSQL을통해서보는정보와매우 유사하지만, 사용자입력뿐이아니라운영중인 Tibero에서수행중인모든 SQL들을분석할수있는점에서다르다. 일반적으로 SQLTRACE 기능을이용해서문제의쿼리를찾아내고, 해당쿼리를 TBSQL을이용해서수행해보면서분석 / 튜닝하는식으로 SQL 수정작업을진행하게된다. 대한부하를최소화하면서특정 SQL 플랜을수행없이확인하는용도로사용하고싶거나, 해당 SQL의플랜정보를특정 persistent 테이블에저장하고지속적인분석을하고싶을때사용하면좋다. 사용법은다음과같다. EXPLAIN PLAN [SET STATEMENT_ID = literal] [INTO 라 ) EXPLAIN PLAN 구문을이용해서플랜정보저장하기이전까지소개했던방법들은 V$SQL_PLAN 뷰와실제수행할때가지고있는 SQL 플랜정보를통해서조회하는방법이었지만, EXPLAIN PLAN 구문을이용하면분석하고자하는대상 SQL 플랜을 PLAN_TABLE이라는임시테이블에저장하고이테이블에조회함으로써플랜정보를얻을수있다. library cache가큰시스템이거나 V$SQL_PLAN 뷰조회조 table_name] FOR SQL STATEMENT; : statement_id는저장된테이블에서해당 SQL 플랜을찾는 ID로사용된다. : INTO 절을이용하면기본 PLAN_TABLE이아닌다른테이블을지정해서저장할수가있다. 위에서언급한대로 V$SQL_PLAN 에대한조회없이특정테이블에해당플랜을저장하고조회할수있다는점을제외하고는사용법은뷰를이용할때와다르지않다. 9

10 2.3. Understanding SQL Plan 이제까지 SQL 플랜을볼수있는방법에대해알아보았는데, 플랜에서보여주는정보들의의미가무엇인지알아보도록하겠다. 가 ) Execution Plan (TBSQL) TBSQL에서 AUTOTRACE 기능을이용하면, 다음과같은형식으로조회결과를얻게된다. 볼수있는정보는크게세가지로분류가된다. 플랜노드별정보를보여주는 Execution Plan 부분 플랜노드별로적용된 predicate 정보를 보여주는 Predicate Information 부분 외부 database 에대한 dblink 를이용하는 노드에서수행할 SQL 정보를보여주는 Remote SQL Information 부분 select d.deptno, sum(sal) from dept d, emp@ln01 e where d.deptno = e.deptno and e.mgr is not null group by d.deptno order by d.deptno; Execution Plan GROUP BY (SORT) (Cost:23, %CPU:0, Rows:4) 2 HASH JOIN (Cost:24, %CPU:0, Rows:13) 3 INDEX (FULL): PK_DEPT (Cost:1, %CPU:0, Rows:4) 4 PROXY: LN01 (Cost:22, %CPU:0, Rows:13) Remote SQL Information SELECT QB_007."SAL",QB_007."DEPTNO" FROM "EMP" QB_007 WHERE (QB_007."MGR" IS NOT NULL) Predicate Information access: ("D"."DEPTNO" = "E"."DEPTNO") 4 - filter: ("E"."MGR" IS NOT NULL) 10

11 Execution Plan에서볼수있는내용은다음과같다. operation name (option) [: object name] (total_cost, cpu_cost_percentage, cardinality) 항목내용 operation 해당노드에서수행할 operation 이름 name option operation 의부가적인정보를보여준다. join 타입 (semi, anti, left outer 등 ), sort 종류 (top-n), rowid scan 종류 (local/global rowid), partition iterating 종류 (empty, single, subset, all) object name object 에대한 operation 의경우해당 object 이름을보여준다. (table name, index name, dblink name 등 ) 만들어내기때문에 dblink가포함된 SQL을분석하기위해서꼭필요한정보이다. TBSQL에서 AUTOTRACE 중 PLANSTAT을켰을때나오는 SQL 수행정보는다음과같다. Execution Stat GROUP BY (SORT) (Time:.03 ms, Rows:3, Starts:1) 2 HASH JOIN (Time:.02 ms, Rows:13, Starts:1) 3 INDEX (FULL): PK_DEPT (Time:.01 ms, Rows:4, Starts:1) 4 TABLE ACCESS (FULL): EMP (Time:.04 ms, Rows:13, Starts:1) operation name (option) [: object name] (elapsed time, output rows, recursive execution) 항목내용 elapsed time 해당플랜노드수행시간 total cost V$SQL_PLAN 의 cost 컬럼값으로 output rows 해당플랜노드를수행하고나온결과 하위플랜노드밑으로총누적된 cost row 수 cpu cost percentage cardinality 값을보여준다. total cost 에서 cpu cost 가차지하는비율 optimizer 에서예측한플랜노드수행결과 row 수 recursive 해당플랜노드가전체 SQL execution 수행하면서반복수행된횟수 (nested loop join, index join 의오른쪽플랜노드는여러번수행가능하다 ) TBSQL에서 execution 플랜정보와플랜수행정보를동 Predicate Information 부분은 join predicate이나 index access predicate에대해선 access란키워드로표시해주고, 그외일반 filter predicate에대해서는 filter란키워드로 predicate 정보를보여준다. Remote SQL Information 부분은 dblink를이용해서타 database에 SQL을수행해서결과를받아와서처리하는플랜노드에서수행하는 SQL을보여준다. Tibero 5.0에서는 dblink 에대해서도 cost 계산을통해최적의플랜을 시에보게되면, optimizer의예측과실제수행결과를한눈에보게되므로문제분석에많은도움을준다. 나 ) Execution Plan (TBPROF) TBPROF에서보여주는정보는 TBSQL에서보여주는정보와비슷하지만, 다음과같이수행결과와 optimizer에서예측한정보를약간다른형태로보여주고있다. 11

12 rows execution plan group by (sort) (et=36, cr=0, cu=0, co=23, cpu=0, ro=4) 13 hash join (et=26, cr=0, cu=0, co=24, cpu=0, ro=13) 4 index (full) PK_DEPT(3456) (et=16, cr=1, cu=0, co=1, cpu=0, ro=4) 13 table access (full) EMP(3457) (et=50, cr=0, cu=2, co=23, cpu=0, ro=13) 새로운기능이계속추가되면해당 operation 수도계속 rows 컬럼은각플랜노드에서의최종결과 row 수이고, execution plan 컬럼부분에서보여주는정보는다음과같다. operation name (option) [: object name] (elapsed time, cr read, cu read, cost, cpu_cost, cardinality) 늘어나게된다. Tibero에존재하는 operation들을파악하고있으면수행한 SQL과 SQL 플랜을매치시켜해당 SQL 의구조를파악하기가쉬워지고, 효율적인플랜인지아닌지를판단할수가있다. 현재 Tibero 5.0 버젼에서제공하는 operation은다음과같다. 항목 내용 elapsed time 해당플랜노드수행시간 cr read 해당플랜노드를수행하면서읽은 CR 블럭개수 cu read 해당플랜노드를수행하면서읽은 current 블럭개수 cost V$SQL_PLAN 의 cost 컬럼값으로 플랜노드밑으로총누적된 cost 값 cpu cost total cost 에서 cpu cost 가차지하는 비율 cardinality optimizer 에서예측한플랜노드수행 결과 row 수 다 ) Operation 종류 SQL 플랜노드는각고유의 operation을처리하기위한정보를담고있다. 이런플랜노드들의조합을통해사용자가수행한 SQL을의미에맞게절차적으로수행할수가있다. Tibero에는 SQL 99 스펙과그밖의특수기능들을지원하기위해 65가지정도의 operation 노드가존재한다. 12

13 항목 table scan index scan join set project filter orderby groupby dml count rownum connect by call file scan window buff buff transformation cube proxy/proxy dml sort aggregation pe manager pe send pe recv pe block iterator pe index maintenance cache inlist iterator partition iterator xml table collector result cache for update 내용 table 에접근 (full scan/rowid scan) index 에접근 (full/range/unique/skip/fast full) 두개의 table 에대한 join 을처리 (hash/sort merge/nested loop/index join) 집합함수처리 (union/intersect/minus/bag union) 컬럼을줄이거나늘리거나 expression 계산처리 조건문을계산하여결과 row 를줄이는작업처리 결과정렬 같은그룹에속하는 row 를모아그룹함수처리 (sort/hash) dml 처리 (insert/update/delete/merge/multi table insert) 특정개수의 row 만통과시키기 계층관계데이타처리 프로시져 / 함수호출 외부파일접근처리 analytic 함수처리 SQL 수행중저장된중간결과에대한접근처리 SQL 수행중중간결과저장및접근에대한제어 cube 기능을위한처리 외부 database 에대한 dblink 처리 aggregation 함수처리 parallel execution manager parallel execution 중간결과를다른 slave 에보내기 (hash/range/broad/rr/qc rand/qc order/index maintenance/partition) parallel execution 중간결과를다른 slave 에서받기 parallel 하게 table 접근을위한반복수행처리 parallel dml 에서 index 무결성보장을위한처리 subquery 결과 cache in predicate 반복수행처리 partition 반복수행처리 xmltable() 구문처리 pipeline table 처리 쿼리결과저장및 cache 처리 select for update 처리 13

14 Finding Bad SQL Plan 수행시간이긴 SQL은원래오래걸리는 SQL일수도있지만, 서두에서언급한것처럼 optimizer가플랜을잘못푸는경우도많이존재하므로, 예상한것과달리플랜이잘못풀리게되어수행이오래걸리는 SQL일수있다. 이런 SQL들을찾아내서플랜을확인하고적절한조치를취하는것은매우중요한일인데, Tibero에서는이런 SQL들을찾아내기위한여러가지방법들을편리하게제공하고있다. 사 ) Top SQL by executions SQL 수행횟수가가장많은 top 5 SQL 컬럼내용 Exec SQL 수행횟수 Rows 총 SQL 수행결과 row 수 processed Rows/Exec SQL 실행당평균 row 수 라 ) APM Tibero APM(Automatic Performance Monitoring) 기능을 사용하면, 측정기간동안 Tibero 에서수행한 SQL 에대한 Elap/Exec SQL 실행당수행시간 요약정보를얻을수가있다. APM에서 SQL이외에도볼수있는항목이많지만, SQL Reports 부분에서우리가원하는다양한기준의 top 5 SQL에대한수행정보와 SQL ID를알아낼수가있다. 일단 를구하면해당 SQL에대해 SQL문 (V$SQLTEXT), SQL 플랜정보 (V$SQL_PLAN), SQL 플랜수행정보 (V$SQL_PLAN_STATISTICS) 를조회할수있으므로문제가될만한플랜들을점검할수가있다. SQL Reports에서보여주는정보는다음과같다. 아 ) Top SQL by CR blocks read count SQL 수행중 CR block 읽는횟수가가장많은 top 5 SQL 컬럼내용 Buffer Gets CR block 요청횟수 Exec SQL 수행횟수 Gets/Exec SQL 실행당 CR block 요청횟수 마 ) Top SQL by elapsed time SQL 수행시간이가장긴 top 5 SQL 컬럼내용 Elapsed time SQL 총수행시간 Total Gets(%) Elapsed Time 총 CR block 요청횟수에대한비율 SQL 총수행시간 Exec SQL 수행횟수 Elap/Exec SQL 실행당수행시간 DB Time (%) 해당 APM 측정기간동안총 DB time 에대한수행시간비율 14

15 자 ) Top SQL by I/O SQL 수행중 I/O 시간이가장많은 top 5 SQL 카 ) Top SQL by CPU SQL 수행중 CPU 시간이가장많은 top 5 SQL 컬럼 내용 컬럼 내용 Disk Read Time disk read 시간 Avg. CPU Time 총수행시간에서 IO 시간을제외한 Exec SQL 수행횟수 시간 Disk Read SQL 실행당 disk read 시간 Exec SQL 수행횟수 Time/Exec Elapsed Time SQL 총수행시간 Elapsed Time SQL 총수행시간 타 ) Top SQL by Parse Calls 차 ) Top SQL by temporary segment usage SQL 수행중 temporary segment 사용시간이가장많은 top 5 SQL 컬럼내용 SQL 수행중 parse 횟수가가장많은 top 5 SQL 컬럼 내용 Parse Calls soft parse 요청횟수 Exec SQL 수행횟수 Tmp Sgmt IO Tm temp segment read/write 시간 Total Parse(%) SQL 총 parse 횟수에대한비율 Exec SQL 수행횟수 Tmp Sgmt IO Tm/Exec Elapsed Time SQL 실행당 temp segment read/write 시간 SQL 총수행시간 파 ) V$SESSION_LONGOPS 특정시간이상수행되는 operation에대한정보를조회할수있는뷰이다. 특정 SQL 수행중한플랜노드가 LONGOPS_THRESHOLD_SEC 값의초이상수행되면자동으로세션에해당정보를남기게된다. SQL 수행이외에도사용자가임의의작업에대해서도 DBMS_APPLICATION_INFO 패키지를이용해서 longops 정보를남길수가있다. 등록된정보는동적으로계속업데이트되기때문에처음에등록된총작업량에비해현재어느정도진행되었는지를알수가있어앞으로남은작업시간을예상할수가있다. 해당뷰의각컬럼값은다음과같은의미를가진다. 15

16 컬럼이름컬럼타입설명 SESS_ID NUMBER session ID SERIAL_NO NUMBER session serial number USER_NAME VARCHAR 접속유저이름 WTHR_ID NUMBER Tibero work thread ID SQL_ID NUMBER 해당플랜의 SQL_PLAN_ID NUMBER 해당플랜의 SQL 플랜 ID OPNAME VARCHAR opeartion 이름 TARGET VARCHAR operation 대상객체이름 SOFAR NUMBER 지금까지수행된작업량 TOTALWORK NUMBER 앞으로수행할총작업량 UNITS VARCHAR 작업의단위 START_TIME DATE 작업시작시간 다음과같은조회쿼리를통해서현재오래수행되고있는 SQL 의정보를쉽게파악할수가있다. SQL> select sql_id, sql_plan_id, substr(opname, 1, 14) opname, to_char(sofar/totalwork*100, 99.99) '%' progress, (sysdate - start_time)*24*60*60 elapsed_sec from v$session_longops; SQL_ID SQL_PLAN_ID OPNAME PROGRESS ELAPSED_SEC nested loops 3.37% row selected. 16

17 위의예에서는 215번 SQL_ID를가지는쿼리의 3번플랜노드가 nested loop join인데현재 1955초동안수행되고있고전체예상작업중 3.37% 진행중인쿼리수행이존재한다라는것을알수가있다. 아주오래걸리는 batch 작업을 psm을이용해서작성하는경우에 DBMS_APPLICATION_INFO 패키지의 SET_SESSION_LONGOPS() 프로시져를적절히이용하면위의뷰로해당작업에대해진행상황을파악할수가있다. 이기능과관련된설정파라미터는다음과같다. 파라미터이름값타입설명 LONGOPS_THR NUMBER 이값보다오래수행되 ESHOLD_SEC 는작업을 LONGOPS 에등록 3. Conclusion SQL을수행하면 Tibero optimizer가통계정보에의한 cost 값에따라플랜을수립하는데, 이렇게만들어진 SQL 플랜을확인하는방법에대해알아보았고, 비효율적으로만들어진 SQL 플랜을쉽게찾을수있는방법을소개하였다. 앞으로 Tibero에사용자들이 SQL 플랜을더편리하게관리할수있는기능들을지속적으로추가할예정이다. 이문서에서제시한방법들은앞으로제공할기능들의가장기본이되는것들이고이기능들을바탕으로계속확장될것이므로이에대한정확한이해를가지는것이앞으로많은도움이되리라생각된다. 그리고, 향후에이문서에서다루지않은비효율적인 SQL 플랜을튜닝하는방법에대해서는다른문서를통해제공하도록하겠다. LONGOPS_KEE P_INTERVAL NUMBER LONGOPS 정보들을 keep 하는주기 _LONGOPS_CL EANUP_INTERV AL NUMBER 주기적으로오래된 LONGOPS 를지우는 시간간격 하 ) SQLTRACE 이용하기 SQLTRACE 기능에대한소개에서언급한바와같이 ALTER 구문을이용해서특정시간동안수행되는 SQL 정보를파일로남길수가있다. 남겨진 TRC 파일로 TBPROF 프로그램을이용할때, 다양한 SORT 옵션을이용하면해당기준으로정렬된 trace 파일을얻을수가있다. 이파일을분석함으로써잘못수행되고있는 SQL 플랜을찾을수있다. SORT 옵션에서 FCHELA (select 쿼리수행시간 ), EXEELA (dml 수행시간 ) 옵션을이용하여정렬하면느린쿼리 /DML 을찾는데많은도움이된다. 17

18 c Copyright TIBERO All Rights Reserved. 본문서에서제공하는기술적또는상업적정보에대한저작권은 TIBERO 에있으며, 본문서는 TIBERO 의허락없이타인에의해복제또는다른언어, 수단, 목적으로변형되거나배포될수없다. 본문서는오로지정보의제공만을목적으로하고, 이로인한계약상의직접적또는간접적책임을지지아니하며, 본문서상의내용은구두로제공되거나법적또는상업적인특정한조건을만족시키는것을보장하지는않는다. 본문서의내용은제품의업그레이드나수정에따라그내용이예고없이변경될수있으며, 내용상의오류가없음을보장하지아니한다. TIBERO 상표는한국및기타다른나라에서 TIBERO 의상표로등록된것이다. 기타다른회사명또는상표는다른권리자의상표혹은서비스표일수있다. 문서번호넣으실곳 18

歯sql_tuning2

歯sql_tuning2 SQL Tuning (2) SQL SQL SQL Tuning ROW(1) ROW(2) ROW(n) update ROW(2) at time 1 & Uncommitted update ROW(2) at time 2 SQLDBA> @ UTLLOCKT WAITING_SESSION TYPE MODE_REQUESTED MODE_HELD LOCK_ID1