1. 들어가며 많은기업들이정보시스템의근간으로데이터베이스를사용하고있고또많은사람들이데이터베이스의성능에대해불만을토로한다. 데이터베이스의성능문제와관련해많은원인과해결책이있지만이문제와관련해자주언급되는개념이있다. Hard Parsing 이그것이다. Hard Parsing 은성능에좋

Hard Parsing 에따른성능문제와효과적인 SQL 작성법 SpeedGate Consulting 김철각

1. 들어가며 많은기업들이정보시스템의근간으로데이터베이스를사용하고있고또많은사람들이데이터베이스의성능에대해불만을토로한다. 데이터베이스의성능문제와관련해많은원인과해결책이있지만이문제와관련해자주언급되는개념이있다. Hard Parsing 이그것이다. Hard Parsing 은성능에좋지못한영향을준다는기술문서들은상당히많다. 하지만아직도많은사람들이 Hard Parsing 이라는것이어떤것이고왜, 어떻게나쁜것인지정확히인식하고있지못한것같다. 여러기업체에서데이터베이스성능개선활동을수행하다보면 Hard Parsing 을유발하도록프로그램을개발한개발자들도그것이얼마나문제를유발하는지를알지못한다. 그렇기때문에 Hard Parsing 을유발하는프로그램이만들어지고이것이문제를일으키는경우를많이보았다. 이문서에서 Parsing 이라는작업이무엇이고그중에서 Hard Parsing 이얼마나성능에영향을미치는지를알아보도록하겠다. 하지만이문서에서는단순히개발툴에서 Hard Parsing 을하지않도록프로그램을작성하는방법에대한논의는다루지않을예정이며구조적으로 Hard Parsing 을하는몇가지프로그램을통해보다효율적인 SQL 작성법에대한논의를하려고한다.

2. Parsing 과 Shared Pool Parsing Parsing 이란사용자가수행을요청한 SQL 문을 Database 에서실행가능한형태로변경하기위해수행하는일련의과정을일컫는다. Parsing 은크게 Sort Paring 과 Hard Parsing 으로나누어질수있는데 Hard Parsing 의경우더많은시스템자원을사용하게된다. Parsing 이라는작업은실제로사용자의요구사항을처리하는단계가아닌, 준비단계로서사용자입장에서는 Overhead 로간주될수있는부분이다. 하지만다음장에언급되듯이 Parsing 작업은내부적으로상당히많은일을수행할수있으며이에따라 CPU 를비롯한시스템자원의사용과그에해당하는만큼의시간을사용하기때문에 Parsing 작업에소요되는시간은가능한최소화되어야한다. 오라클에서는 Shared Pool 이라는공간을이용해이문제를해결하고있는데 Parsing 시많은비용이소요되는일련의작업을수행결과생성한정보를버리지않고공유메모리에보관해두었다가차후동일한 SQL 문이사용자에의해요청될경우 Parsing 작업을다시하지않고공유메모리에보관된정보를이용해 실행 함으로써전체수행속도를빠르게하고보다많은 CPU 자원이실제자료처리에사용될수있도록하고있다. Shared Pool Parsing 을설명하며언급한공유메모리가 Shared Pool 이다. Shared Pool 은 SGA 를구성하고있는부분으로크게 dictionary cache 와 library cache 로구성된다. dictionary cache data dictionary 정보를보관하는메모리영역. SQL 문에대한 parsing 작업또는 PL/SQL 코드에대한 Compile 작업시이부분의내용을참조한다. library cache SQL 또는 PL/SQL 코드의실행가능한형태를보관하는메모리영역. Application 에서사용하는 SQL, PL/SQL 문이사용한다. 오라클에서수행되는 SQL 문과 Parsing 된정보를공유하기위해 Shared Pool 의 library cache 영역을사용한다. 오라클서버가 SQL 문에대한수행요청을받은경우 library cache 영역에수행하려는 SQL 문에대한 parsing 된형태가있는지를조사하고그형태가존재하는경우 (library cache hit) 그정보를사용하는데이를 soft parse 라고한다. 만일수행하려는 SQL 문이 library cache 영역에존재하지않는경우 (library cache miss) 새로 parsing 작업을수행하며이를 hard parse 라한다. 그러므로 SGA 영역중 Buffer Cache 가 Disk I/O 에대한 Cache 자원으로사용되는것처럼, Shared Pool 은 Parsing 에소요되는 CPU Power 에대한 Cache 라고생각할수있다.

3. SQL문수행절차 SQL 문수행은크게다음과같은절차로이루어진다. Parse : SQL 문에대해문법, Object 존재여부, 권한등의검사를수행한다. 또한 SQL 문에대한최적의실행방법을결정 Execute : 실제자료처리가발생하는부분으로다양한사용자의요구가이루어짐 Fetch : Select 문장의경우처리된자료가사용자에게보내지는과정위의 3 가지수행단계는 trace 의결과에서도나타나는단계로가장기본적인수행단계라고할수있다. 여기서는 Execute 나 Fetch 단계보다는 Parse 에관심을두고있으므로 Parse- Execute 단계에서발생하는일들을조금더자세히살펴보기로하자. Soft Parse Hard Parse Parse 필요? Hash 값계산 Shared Pool NO 에동일값존재? YES Object 가동일? NO Lock SQL Area YES Parse SQL SQL 실행 SQL Area 에저장 그림 1.. Parse Step 개요 위의그림에서 Hard Parse 로표시되는다음과같은절차로이루어진다. (Local Node 에서작업하는경우 ) SQL 문에대한변환및문법검증 Data Dictionary 를통해 SQL 문에서사용되는 Table 과 Column 정보검색 SQL 문에서사용되는 Object 들이 Parsing 과정중변경되는일을방지하기위해관련 Object 들에 Lock 설정.

참조되는 Object 들에대해 SQL 문에서요구한작업에대한권한유무점검. SQL 문에대한최적의실행계획생성. 산출된정보들을 library cache 에저장. 최적의실행계획생성단계는매우복잡한계산을거치게되는데이과정에서는 ( 가능한경우 ) Database 의 Parameter, Table/Index 의통계정보등을이용해여러가지경우 (Table Join 순서및방법, Table Scan 및 Index Scan) 에대해실행계획을생성하고이들중최적의실행계획을얻어낸다. 이부분은계산및처리과정이많으므로 Parsing 작업중가장많은 CPU 자원을사용하는부분이라고할수있다. 이부분에서구체적으로수행하는일들은 10053 Event 에대해 Trace 를설정하면볼수있으며 Reference 에기술된 Site 의문서를보면보다구체적인정보를얻을수있다.

4. Literal SQL vs Bind SQL Literal SQL : SQL 문의내용중특정값을지정하거나, 표현하는부분에문자, 숫자와같은상수값을 Hard 코딩해서작성한 SQL Bind SQL : SQL 문의내용중특정값을지정하거나, 표현하는부분에변수를이용해작성한 SQL 다음표의내용을 SQL*Plus 수행하면수행된 SQL 문이화면에표시된다. Literal SQL 문은변수가값으로서 (Literal) 사용되었으나 Bind SQL 문은변수가변수자체로사용되었다. 그렇기때문에 Literal SQL 문은각 SQL 문마다 1 부터 4 까지의값이값으로서사용된반면 Bind SQL 문은 :b0 라는변수이름이반복사용되고있다. Literal SQL Bind SQL set serveroutput on declare v_sql varchar2(1000); v_cnt number; begin for i in 1..4 loop v_sql:='select count(*) from user_objects where rownum<=' i; dbms_output.put_line(v_sql); execute immediate v_sql into v_cnt; end loop; end; / ** 결과로수행되는 SQL select count(*) from user_objects where rownum<=1 select count(*) from user_objects where rownum<=2 select count(*) from user_objects where rownum<=3 select count(*) from user_objects where rownum<=4 set serveroutput on declare v_sql varchar2(1000); v_cnt number; begin for i in 1..4 loop v_sql:='select count(*) from dba_objects where rownum<=:b0'; dbms_output.put_line(v_sql); execute immediate v_sql into v_cnt using i; end loop; end; / ** 결과로수행되는 SQL select count(*) from dba_objects where rownum<=:b0 select count(*) from dba_objects where rownum<=:b0 select count(*) from dba_objects where rownum<=:b0 select count(*) from dba_objects where rownum<=:b0 표 1.. Literal SQL 과 Bind SQL

앞에서 Shared Pool 의사용목적이동일한 SQL 문에대한 Parsing 정보를재활용하기위함이라고했으나 Literal SQL 문으로사용하면조건에사용된값이서로틀리므로오라클이동일한 SQL 문으로인식하지않으므로새롭게 Parsing 을 Hard Parsing 을 수행하게된다. 앞에서 Hard Parsing 이자원을상당히많이사용한다고언급했다. 얼마나많이사용하는지를확인하기위해다음과같은 Test 를수행하였다. 본 Test 에사용된 Object 들은 Oracle 9i 에포함된 demo schema 중 SH Schema 의 Object 를사용하였다. Test 환경 H/W : Pentium 4 1.4GHz RAM : 512MB OS : Windows XP Professinal Oracle Version : Oracle9i E/E 9.0.1.4.0 SGA Total System Global Area 126644544 bytes Fixed Size 282944 bytes Variable Size 83886080 bytes Database Buffers 41943040 bytes Redo Buffers 532480 bytes Test Script ** CASE 1. Literal SQL declare v_prod_name v_unit_price varchar2(50); number; begin end; / v_sql varchar2(500); v_sql:='select /* literal */ prd.prod_name,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'') and prd.prod_id='; for i in 1..10000 loop execute immediate v_sql i*5 into v_prod_name,v_unit_price; end loop; ** CASE 2. Bind SQL declare v_prod_name v_unit_price begin varchar2(50); number; for i in 1..10000 loop select /* bind */ prd.prod_name,cost.unit_price into v_prod_name,v_unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date('20000101','yyyymmdd') and prd.prod_id=i*5; end loop;

end; / 다음은 Windows 작업관리자를통해측정한각각의 CPU 사용률과 SQL*Plus 에서표시된 경과시간이다. Case1 경과시간 : 16.04 초소요 Case2 경과시간 : 1.00 초소요 다음은 v$sql performance view 에서추출한수행횟수및 Memory 사용정보이다. Case1 Case2 select substr(sql_text,1,20),avg(sharable_mem),count(*),max(executions) from v$sql where lower(sql_text) like 'select /* literal */%' group by substr(sql_text,1,20); SUBSTR(SQL_TEXT,1,20) AVG(SHARABLE_MEM) COUNT(*) MAX(EXECUTIONS) ---------------------------------------- ----------------- ---------- --------------- select /* literal */ 16090.0436 2112 1 select substr(sql_text,1,20),avg(sharable_mem),count(*),max(executions) from v$sql where lower(sql_text) like 'select /* bind */%' group by substr(sql_text,1,20); SUBSTR(SQL_TEXT,1,20) AVG(SHARABLE_MEM) COUNT(*) MAX(EXECUTIONS) ---------------------------------------- ----------------- ---------- --------------- SELECT /* bind */ pr 16095 1 10000 SQL 문은 10,000 번수행됐으나 Case1 에나타난정보는 2,112 개만존재하는것으로표시된다. 이는 Shared Pool 의크기가 10,000 개의 SQL 문을저장할수없어먼저수행된 SQL 문은공간 확보를위해 Shared Pool 에서삭제되었음을표시한다. 또공유가전혀되지않으므로 수행횟수를표시하는 execution 값의최대값은 1 을표시하고있다. SQL 문 1 개가사용하는

Sharable Memory 의크기는약 16KB 이므로 10,000 개의 SQL 문에대한정보를다보관하는경우 160MB 의공간이필요하다는계산이나온다. 반면 Case2 는단 1 개의형태만존재하고있으며그것이 10,000 번사용됐음을보여주고있다. 하지만사용하는 Memory 량은여전히 16KB 이므로 Case1 의 160MB 와비교하면엄청난차이를보이고있다. 동일한일을수행하는 SQL 문을 Literal SQL 과 Bind SQL 로작성한경우 Computing 자원의주요 Resource 인 CPU 와 Memory 를사용하는형태에서상당한차이가발생한다는것을보았다. 그러면오라클내부적으로는또어떤차이가있을까? 다음장에서몇가지경우에대한소요시간및자원사용량을알아보기로하자.

5. 7 가지 PARSE 형태별자원사용량비교 Parsing 시얼마나많은자원과시간이소요되는지를알아보기위해다음과같은 TEST 를수행하였다. 앞서사용한것과마찬가지로 Oracle 9i 에포함된 demo schema 중 SH Schema 의 Object 를사용하였다. 이들 Object 들은 $ORACLE_HOME/demo/schema 에존재하는 mksample.sql 을수행해생성할수있다. Cursor 를선언하고실행하기까지의구현방법에꽤많은경우의수로구분할수있으나모든경우에대한 Test 는지면관계상어려우므로다음과같은형태로수행한경우의자원사용량을비교하였다. Case 1. Execute immediate 를사용한 Literal SQL Case 2. DBMS_SQL 을사용한 Literal SQL Case 3. cursor_sharing=force 설정후 Execute immediate 를사용한 Literal SQL Case 4. Execute immediate 를사용한 Bind SQL Case 5. DBMS_SQL 을사용한 Bind SQL(Open, Parse 1 회, Bind, Execute 반복 ) Case 6. Static SQL Case 7. DBMS_SQL 을사용한 Bind SQL(Open 1 회, Parse, Bind, Execute 반복 ) Test 환경 H/W : Pentium 4 1.4GHz RAM : 512MB OS : Windows XP Professinal Oracle Version : Oracle9i E/E 9.0.1.4.0 SGA Total System Global Area 126644544 bytes Fixed Size 282944 bytes Variable Size 83886080 bytes Database Buffers 41943040 bytes Redo Buffers 532480 bytes Test 방식 PC 에설치된 Oracle Server 에서 Script 수행용 Session 1 개와 Performance 정보수집용 Session 1 개만접속하였다. Script 수행용 Session 에서는경과시간을표시하기위해 set timing on 을수행하였으며 Performance 정보수집을위해서 v$sesstat, v$latch 값을 Script 수행전, 후로구해그차이를기록하였다. 각각의 Test 수행전 shared pool 에대한 flush 를수행해 Parsing 과관련해다른 Test 에서생성된정보를삭제하였다. Test Script ** CASE 1. Literal SQL (Native Dynamic SQL) declare

v_prod_name varchar2(50); v_prod_list_price number; v_unit_price number; begin end; / v_sql varchar2(500); v_sql:='select prd.prod_name,prd.prod_list_price,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'') and prd.prod_id='; for i in 1..10000 loop execute immediate v_sql i*5 into v_prod_name,v_prod_list_price,v_unit_price; end loop; ** CASE 2. Literal SQL (DBMS_SQL) declare v_prod_name varchar2(50); v_prod_list_price number; v_unit_price number; begin end; / v_sql cur_sql_exec ignore varchar2(500); number; number; cur_sql_exec:=dbms_sql.open_cursor; v_sql:='select prd.prod_name,prd.prod_list_price,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'') and prd.prod_id='; for i in 1..10000 loop dbms_sql.parse(cur_sql_exec,v_sql i*5,1); dbms_sql.define_column(cur_sql_exec,1,v_prod_name,50); dbms_sql.define_column(cur_sql_exec,2,v_prod_list_price); dbms_sql.define_column(cur_sql_exec,3,v_unit_price); ignore:=sys.dbms_sql.execute(cur_sql_exec); ignore:=sys.dbms_sql.fetch_rows(cur_sql_exec); dbms_sql.column_value(cur_sql_exec,1,v_prod_name); dbms_sql.column_value(cur_sql_exec,2,v_prod_list_price); dbms_sql.column_value(cur_sql_exec,3,v_unit_price); end loop; dbms_sql.close_cursor(cur_sql_exec); ** CASE 3. Cursor Sharing 에의한공유 alter session set cursor_sharing = force; declare

v_prod_name varchar2(50); v_prod_list_price number; v_unit_price number; begin end; / v_sql varchar2(500); v_sql:='select prd.prod_name,prd.prod_list_price,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'') and prd.prod_id='; for i in 1..10000 loop execute immediate v_sql i*5 into v_prod_name,v_prod_list_price,v_unit_price; end loop; alter session set cursor_sharing = exact; ** CASE 4. Bind 변수사용 (Native Dynamic SQL) declare v_prod_name varchar2(50); v_prod_list_price number; v_unit_price number; v_sql varchar2(500); begin v_sql:='select prd.prod_name,prd.prod_list_price,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'') and prd.prod_id='; for i in 1..10000 loop execute immediate v_sql ':b1' into v_prod_name,v_prod_list_price,v_unit_price using i*5; end loop; end; / ** CASE 5. Bind 변수사용 (DBMS_SQL) declare v_prod_name v_prod_list_price number; v_unit_price number; varchar2(50); begin v_sql cur_sql_exec ignore varchar2(500); number; number; cur_sql_exec:=dbms_sql.open_cursor; v_sql:='select prd.prod_name

end; /,prd.prod_list_price,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'') and prd.prod_id='; dbms_sql.parse(cur_sql_exec,v_sql ':b0',1); for i in 1..10000 loop dbms_sql.define_column(cur_sql_exec,1,v_prod_name,50); dbms_sql.define_column(cur_sql_exec,2,v_prod_list_price); dbms_sql.define_column(cur_sql_exec,3,v_unit_price); dbms_sql.bind_variable(cur_sql_exec,'b0',i*5); ignore:=sys.dbms_sql.execute(cur_sql_exec); ignore:=sys.dbms_sql.fetch_rows(cur_sql_exec); dbms_sql.column_value(cur_sql_exec,1,v_prod_name); dbms_sql.column_value(cur_sql_exec,2,v_prod_list_price); dbms_sql.column_value(cur_sql_exec,3,v_unit_price); end loop; dbms_sql.close_cursor(cur_sql_exec); ** CASE 6. Static SQL declare begin end; / v_prod_name varchar2(50); v_prod_list_price number; v_unit_price number; for i in 1..10000 loop select prd.prod_name,prd.prod_list_price,cost.unit_price into v_prod_name,v_prod_list_price,v_unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date('20000101','yyyymmdd') and prd.prod_id=i*5; end loop; ** CASE 7. Bind 변수사용 (DBMS_SQL) + 반복 PARSE declare begin v_prod_name varchar2(50); v_prod_list_price number; v_unit_price number; v_sql cur_sql_exec ignore varchar2(500); number; number; cur_sql_exec:=dbms_sql.open_cursor; v_sql:='select prd.prod_name,prd.prod_list_price,cost.unit_price from sh.products prd, sh.costs cost where prd.prod_id=cost.prod_id(+) and cost.time_id(+)=to_date(''20000101'',''yyyymmdd'')

end; / and prd.prod_id='; for i in 1..10000 loop dbms_sql.parse(cur_sql_exec,v_sql ':b0',1); dbms_sql.define_column(cur_sql_exec,1,v_prod_name,50); dbms_sql.define_column(cur_sql_exec,2,v_prod_list_price); dbms_sql.define_column(cur_sql_exec,3,v_unit_price); dbms_sql.bind_variable(cur_sql_exec,'b0',i*5); ignore:=sys.dbms_sql.execute(cur_sql_exec); ignore:=sys.dbms_sql.fetch_rows(cur_sql_exec); dbms_sql.column_value(cur_sql_exec,1,v_prod_name); dbms_sql.column_value(cur_sql_exec,2,v_prod_list_price); dbms_sql.column_value(cur_sql_exec,3,v_unit_price); end loop; dbms_sql.close_cursor(cur_sql_exec); Test 결과 Stat Latch (get) 구 분 Case 1 Case 2 Case 3 Case 4 Case 5 Case 6 Case 7 소요시간 15.07 16.04 2.05 1.08 1.06 1.01 2.08 execute count 10365 10415 10361 10226 10312 10216 10320 recursive calls 15346 35902 15250 12788 33960 12261 44122 parse count (total) 10182 10226 10184 10096 171 110 10172 parse count (hard) 10042 10043 45 41 41 41 43 parse time cpu 1299 1320 143 40 29 22 28 parse time elapsed 1307 1327 145 43 29 31 30 opened cursors cumulative 10150 173 10152 10078 117 86 119 CPU used by this 1560 1632 248 171 163 106 251 session library cache 497408 498217 135531 314887 5330 25022 315422 shared pool 633460 635323 13387 23273 3510 2895 23606 row cache objects 1412354 1412423 2121 2052 2060 1952 2140 결과는크게 3 가지부분으로구분되며다음과같은의미를갖는다. 소요시간 : SQL*Plus 에서 set timing on 을수행한후각작업이종료된후표시되는시간. 실제로 Database 에서수행되는것과약간의오차가있을수있으나 10046 Trace 를수행한경우본 Test 환경의 File System 으로부터의 Overhead 가매우크게발생해실제값자체를상당히왜곡시키기때문에이지표를사용했다. 하지만, 이지표는절대적으로신뢰할수있는값은아니며특히소수점이하로는오차가클수있기때문에단순참고용으로기재했다. exeute count : 각 Operation 수행을위해처리된 SQL 문의개수. recursive calls : Oracle 이내부적으로요구되는작업을수행하기위해수행한횟수와 PL/SQL 에서호출된 SQL 문의횟수이다. 내부적으로요구되는작업은 Hard Parsing 시내부적으로사용되는 SQL 문으로볼수있으며나머지는 PL/SQL 문에서수행되는횟수로볼

수있다. execute count 와 recursive calls 모두자료수집방법에따라조금다른결과를보여주므로여기서는절대적인지표에대해언급하기보다는상대적인지표로언급한다. parse count (total) : 작업수행중발생한 Parse Count (Hard Parse + Soft Parse) parse count (hard) : 작업수행중발생한 Hard Parse Count parse time cpu : Parsing 작업시소요된 CPU Time. Centi-second(1/100 초 ) 단위로표시되지만측정방식의한계로인해약간의오차가존재할수있다. parse time elapsed : Parsing 작업시소요된경과시간. Centi-second(1/100 초 ) 단위로표시된다. CPU used by this session : 사용자의요청이시작해서종료될때까지사용된 CPU 시간. Centi-second(1/100 초 ) 단위로표시되지만측정방식의한계로인해약간의오차가존재할수있다. library cache : library cache latch 에접근한횟수를나타낸다. 몇가지요인이영향을미칠수있으나본 Test 에서는 (Hard) Parse Count 가변동요인이므로이들값의변화량에대한변동값을측정할수있다. shared pool : shared pool latch 에접근한횟수를나타낸다. row cache objects : row cache objects latch 에접근한횟수를나타낸다. 이상의 Test 결과 Parse 횟수, 특히 Hard Parse 횟수가많을수록수행시간및 Latch Access 횟수가많아진다는사실을알수있다. 사용하는 SQL 문의복잡도에따라다르지만본 Test 에서는수행시간이최대 16 배의차이가나는것으로나타났다. 또한주요 Latch 에대한접근횟수또한최대 220 배가량의차이가나는것으로나타나 Case 5, Case 6 과같은식의프로그램이다른방식보다우수하다는것을알수있다. Test 결과에의하면어떤방법을사용하던 Literal SQL 문을사용해 Hard Parse 가많을경우 Latch 자원 특히 row cache objects latch 에대한상당한 Access 와 Performance 의저하현상이발생하는것을알수있다. 또한 Case 5 과 Case 7 을통해 Hard Parse 를최소화하더라도 Soft Parse 가많으면성능에 특히 Latch 자원에 문제를줄수있다는것을알수있다. 또한 execute immediate 에의해수행되는 NDS(Native Dynamic SQL) 문의경우 cursor 를 SQL 문 1 개수행시 Cursor 를반복적으로 Open 한다는것을 opened cursors cumulative 값을통해알수있으며 DBMS_SQL Package 에비해사용하기편하다는장점이있지만 Cursor Open 을불필요하게많이수행한다는것을알수있다. Case5 와 Case6 의경우어느방법이우수한지는본 Test 만으로판단하기는어렵다. 일반적으로 Case6 가조금더우수할것으로보이지만 library cache latch 의 Access 횟수가 Case5 보다더크기때문에 library cache latch 가심하게문제가되는경우라면 Case5 가우수할수있다. 한가지분명한점은 Case5 와 Case6 의경우 Dynamic SQL 과 Static SQL 로분명히구분되는영역이존재한다는사실이다. 때문에 Dynamic SQL 을사용한다면 DBMS_SQL Package 를사용하는것이유리하며 Static SQL 을사용한다면복잡한 DBMS_SQL Package 를사용하기보다는위와같은방식으로사용하는것이합리적이지않을까생각한다.

다음에는실무에서발생한 Literal SQL 문사례와그에따른성능문제그리고해결사례를소개하려고한다. 하지만단순히개발 Tool 에서 Bind SQL 문을사용하는방법을알지못해 Literal SQL 문이사용된사례는지양하고자하며구조적인문제에서발생하는문제들에대해다루도록하겠다.

6. Literal SQL 해결사례 본절에서는전장에서언급한대로 Logic 구성, 또는구조적문제에서발생하는 3 가지의사례를통해진행하고자한다. 사례에서사용되는자료는 Oracle 9i 에포함된 demo schema 를통해구현하였다. 제한된자료를통해구현했으므로실제업무적인내용에서는좀미진할수있으므로많은양해바란다. 각각의 Test 는 SQL 문수행전 Shared Pool 을 Flush 한후수행되었다. 6.1 Loop 구조로반복수행되는 Literal SQL 본프로그램은 sh 스키마의 product table 내용중 category 가 men 또는 women 이며현재 재고가있는 ( available, on stock ) 제품중가격이 100 달러가넘는것은판매금액과판매량을 구하고, 100 달러가되지않은것은판매금액만구하는 logic 이다. 01 declare 02 v_sql varchar2(1000); 03 type refcurtype is ref cursor; 04 cur_sql refcurtype; 05 cursor cur_products is 06 select prod_id,prod_name,prod_list_price 07 from sh.products 08 where prod_status = 'available, on stock' 09 and prod_category in ('Men','Women'); 10 rec_products cur_products%rowtype; 11 v_amount_sold number; 12 v_quantity_sold number; 13 begin 14 15 open cur_products; 16 loop 17 fetch cur_products into rec_products; 18 exit when cur_products%notfound; 19 if rec_products.prod_list_price>100 then 20 v_sql:='select sum(amount_sold),sum(quantity_sold)*' rec_products.prod_list_price ' from sh.sales where time_id>=to_date(''20000101'',''yyyymmdd'') and time_id<to_date(''20010101'',''yyyymmdd'') and prod_id=' rec_products.prod_id; 21 else 22 v_sql:='select sum(amount_sold),0 from sh.sales where time_id>=to_date(''20000101'',''yyyymmdd'') and time_id<to_date(''20010101'',''yyyymmdd'') and prod_id=' rec_products.prod_id; 23 end if; 24 open cur_sql for v_sql; 25 fetch cur_sql into v_amount_sold,v_quantity_sold; 26 -- dbms_output.put_line('amount SOLD : ' v_amount_sold ', QUANTITY SOLD : ' v_quantity_sold); 27 close cur_sql; 28 end loop; 29 close cur_products; 30 end;

본프로그램을수행시키면 cur_products cursor 의자료개수만큼 hard parsing 이발생한다. 왜 그럴까? 20 line 과 22 line 에서 select 문장을문자열로처리하고있음을알수있다. 이경우 rec_products.prod_id 라는변수는상수화되어 shared pool 의입장에서는서로다른 SQL 문으로수행되기때문이다. 실무에서사용하는개발툴의종류를불문하고이런식의 프로그램방식이많이사용된다. Literal SQL 문과관련한문제해결방법은보통 2 가지가있다고생각한다. 첫번째는반복 수행되는 SQL 문이 Bind SQL 문을사용하도록변경하는것이고두번째는 SQL 문을통합해반복 수행하는부분자체를없애는방법이그것이다. 다음의 Source 는각각 Bind SQL 문을사용한경우와 SQL 문을통합한경우를보여주고있다. 01 declare 02 v_sql varchar2(1000); 03 type refcurtype is ref cursor; 04 cur_sql refcurtype; 05 cursor cur_products is 06 select prod_id,prod_name,prod_list_price 07 from sh.products 08 where prod_status = 'available, on stock' 09 and prod_category in ('Men','Women'); 10 rec_products cur_products%rowtype; 11 v_amount_sold number; 12 v_quantity_sold number; 13 begin 14 open cur_products; 15 loop 16 fetch cur_products into rec_products; 17 exit when cur_products%notfound; 18 if rec_products.prod_list_price>100 then 19 v_sql:='select sum(amount_sold),sum(quantity_sold)*:b0 from sh.sales where time_id>=to_date(''20000101'',''yyyymmdd'') and time_id<to_date(''20010101'',''yyyymmdd'') and prod_id=:b1'; 20 open cur_sql for v_sql using rec_products.prod_list_price,rec_products.prod_id; 21 else 22 v_sql:='select sum(amount_sold),0 from sh.sales where time_id>=to_date(''20000101'',''yyyymmdd'') and time_id<to_date(''20010101'',''yyyymmdd'') and prod_id=:b1'; 23 open cur_sql for v_sql using rec_products.prod_id; 24 end if; 25 fetch cur_sql into v_amount_sold,v_quantity_sold; 26 -- dbms_output.put_line('amount SOLD : ' v_amount_sold ', QUANTITY SOLD : ' v_quantity_sold); 27 close cur_sql; 28 end loop; 29 close cur_products; 30 end; 19line 과 22 라인에서문자열을구성하는부분은전과유사하다. 다만변수를 Operator 를 통해결합시키지않고 :b1 라는부분을사용한것과 open cursor 명령에서 using 절을이용해 앞서선언한 :b1 부분에 rec_products.prod_id 라는변수를입력하는것이다르다. 이부분을

통해각각의경우입력받는값이 1 개또는 2 개라도각각의경우에맞는 Bind SQL 문을 사용할수있으며 Hard Parsing 에의한성능감소현상을거의없앨수있다. 대부분의경우위와같이처리하면큰문제없이수행이가능하지만 5 장에서살펴본바와 마찬가지로이경우에도 Soft Parse 과다현상및반복적인 Cursor 의 Open Close 에의한 부하는여전히존재한다는문제가있다. 따라서보다근원적인문제해결방식은 SQL 문의반복 수행자체를막는것이라고할수있다. 아래 Source 를보자. 01 declare 02 cursor cur_products(p_from_time in varchar2, p_to_time in varchar2) is 03 select 04 prd.prod_id 05,prd.prod_name 06,sum(sales.amount_sold) amount_sold 07,case 08 when prd.prod_list_price>100 then sum(sales.quantity_sold)*prd.prod_list_price 09 else 10 0 11 end quantity_sold 12 from 13 sh.products prd 14,sh.sales sales 15 where 16 prd.prod_status = 'available, on stock' 17 and prod_category in ('Men','Women') 18 and sales.time_id(+) >= to_date(p_from_time,'yyyymmdd') 19 and sales.time_id(+) < to_date(p_to_time,'yyyymmdd') 20 and prd.prod_id = sales.prod_id(+) 21 group by 22 prd.prod_id 23,prd.prod_name 24,prd.prod_list_price; 25 rec_products cur_products%rowtype; 26 begin 27 open cur_products('20000101','20010101'); 28 loop 29 fetch cur_products into rec_products; 30 exit when cur_products%notfound; 31 -- dbms_output.put_line('amount SOLD : ' rec_products.amount_sold ', QUANTITY SOLD : ' rec_products.quantity_sold); 32 end loop; 33 close cur_products; 34 end; 반복적으로사용되던부분이 cur_products cursor 부분에흡수되었다. Group by 절이추가되고 prod_id, prod_name,prod_list_price 가추가되었다. SQL 문통합시반복수행되는 prod_id 만을 group by 에지정하면 prod_name, prod_list_price 는구할방법이없다. prod_name, prod_list_price 는 prod_id 에종속적이므로 group by 절에 prod_id 뿐만아니라 prod_name, prod_list_price 까지추가해원하는자료를가져올수있도록하였다.

SQL 통합시고려해야할또다른중요한점은 18~20 line 에나타나있다. 바로 Outer Join 의사용이그것이다. 원 source 를보면 cur_products cursor 의자료를통해반복수행되는 cur_sql 부분이호출됨을알수있으나 cur_sql 에서아무런자료도출력되지않을경우그것을사용하지않겠다는부분은나타나지않고있다. 만일 v_amount_sold 값이 0 보다크다 라거나또다른명시적조건이존재하지않는경우는 Child 관계에해당하는 cur_sql cursor 의결과값이없더라도 Parent 에해당하는 cur_products cursor 부분의자료는유효하다는것을인지해야한다. 다음의표는지난호에서사용된주요자원사용량을각각의경우로비교하고있다. Stat Latch (get) 구 분 Case 1 Case 2 Case 3 소요시간 38.00 18.05 4.01 execute count 5,078 5,125 303 recursive calls 29,075 29,793 8,484 parse count (total) 4,928 4,938 148 parse count (hard) 4,722 29 25 parse time cpu 709 46 24 parse time elapsed 992 172 122 opened cursors cumulative 4,887 4,897 104 CPU used by this session 15.04 758 201 library cache 273,091 152,812 5,093 shared pool 301,141 13,094 3,098 row cache objects 696,675 3,105 2,591 본 Table 에나타난수치는시스템환경에따라절대값자체는어느정도변경될수있으나값의비율은어느정도유지되므로상대적인수치로이해하는것이더바람직하다. Case1 에서 Case2 로가면서 Literal SQL 문의감소로인해 Hard Parsing 횟수가감소하고그로인해 Parse Time CPU 가떨어졌으며그시간이소요시간에반영되었음을알수있으며 Latch 자원사용량도상당히많이감소했음을보여주고있다. Case2 에서 Case3 로의변화로인해 Soft Parse 횟수가많이감소되었고 Parsing 에소요된시간이약간감소했음을알수있는데 Soft Parse 는이미 Shared Pool 에있는정보를활용하기때문에부담이작다는것을알수있다. 하지만 SQL 문통합에따라수행횟수가상당히감소되었고, 부담이적기는하지만 Soft Parse 의감소그리고 Latch 자원의사용량감소는수행시간을더단축시켜주고있다. 프로그램설계구조로인해 Case3 을적용할수도있고그렇지못할수도있으나가능하면 Case3 과같은식으로프로그램을작성하는것이훨씬더효율적인것이라고알수있다. 6.2 자료구조차이에서발생하는 Literal SQL 본프로그램은 oe 스키마의 order_items table 의내용을신규로생성한 order_summary 라는 table 로자료를이동시키는사례이다. 각각의 Order 별로주문번호, 주문일,15 개까지의제품번호, 15 개까지의주문액수를 1 개 Record 에표시하고자한다.

ORDER_ID 2410 2410 2410 2410 2410 2410 LINE_ ITEM_ID 1 2 3 4 5 6 PRODUCT_ID UNIT_PRICE 2976 46 2982 40 2986 120 2995 68 3003 2866.6 3051 12 QUANTITY 10 5 6 8 15 21 ORDER_ID 2410 ORDER_DATE 2000.05.24 PRD_ID01 2976 PRD_ID02 2982 PRD_AMT01 460 PRD_AMT02 200 Table, Column 과같은 Database Object 이름은 bind 처리가되지않으므로 (parse 단계가 bind 단계보다먼저수행되고, 그렇기때문에 parse 단계에서 bind 변수의내용을알수없기때문에 사용하는 Object 가무엇인지알수없기때문이다.) 개발자는다음과같이프로그램을 작성하였다. 01 declare 02 v_sql varchar2(200); 03 cursor cur_order_item is 04 select order_id,trim(to_char(line_item_id,'09')) line_item_id,product_id,unit_price*quantity amt 05 from oe.order_items; 06 begin 07 insert into oe.order_summary(order_id,order_date) select order_id,to_char(order_date,'yyyy.mm.dd') from oe.orders; 08 for rec_order_item in cur_order_item loop 09 if rec_order_item.line_item_id is NOT NULL then 10 v_sql:='update oe.order_summary set prd_id' rec_order_item.line_item_id '=' rec_order_item.product_id 11 ', prd_amt' rec_order_item.line_item_id '=' rec_order_item.amt 12 'where order_id=' rec_order_item.order_id; 13 execute immediate v_sql; 14 end if; 15 end loop; 16 end; order_summary table 에 order_id 와 order_date 를입력한후 order_items table 의내용을 loop 를수행하며 literal SQL 문을만들어수행하였다. prd_id, prd_amt 다음의 line_item_id 가 컬럼이름이 hard coding 되어있으므로이부분에대한 bind 처리가되지않으므로 line 10,11 에서 line_item_id 를추가시켜 literal SQL 로사용하고있음을알수있다. SQL 문은 cur_order_item 의건수만큼수행되므로 order_items table 의건수만큼수행된다는것을알수 있으며이들모두서로다른값을가지고수행되므로 order_items table 건수만큼 Hard Parsing 이발생할것이라는것을짐작할수있다. 각각 15 개인 prd_id, prd_amt 컬럼에대해서는 literal SQL 문의사용이불가피하지만입력되는 값자체는 bind 처리가가능하므로프로그램을다음과같이작성할수있다. 01 declare 02 v_sql varchar2(200);

03 cursor cur_order_item is 04 select order_id,trim(to_char(line_item_id,'09')) line_item_id,product_id,unit_price*quantity amt 05 from oe.order_items; 06 begin 07 insert into oe.order_summary(order_id,order_date) select order_id,to_char(order_date,'yyyy.mm.dd') from oe.orders; 08 for rec_order_item in cur_order_item loop 09 if rec_order_item.line_item_id is NOT NULL then 10 v_sql:='update oe.order_summary set prd_id' rec_order_item.line_item_id '=:b0' 11 ', prd_amt' rec_order_item.line_item_id '=:b1 where order_id=:b2'; 12 execute immediate v_sql using rec_order_item.product_id,rec_order_item.amt,rec_order_item.order_id; 13 end if; 14 end loop; 15 end; line 10 과 line11 에서 literal SQL 문을생성하는과정은전과동일하다. 하지만실제로입력되는 prd_id,prd_amt 는 bind 변수를사용했음을알수있다. 이렇게할경우어느정도 Literal SQL 문은감소하지만 prd_id,prd_amt 의개수인 15 번의 Hard Parsing 은피할길이없다. 이경우도다음과같이 SQL 문을통합해 1 개의 SQL 문으로수행하도록할수있다. 1 insert into oe.order_summary 2 select ord.order_id,to_char(order_date,'yyyy.mm.dd') 3,max(decode(line_item_id,1,product_id)), 4,max(decode(line_item_id,1,unit_price*quantity)),... 5 from oe.order_items ordi, oe.orders ord 6 where ord.order_id=ordi.order_id 7 group by ord.order_id,to_char(order_date,'yyyy.mm.dd'); PL/SQL Logic 이사라지고 insert 문장으로대치되었다 - line3 과 line4 에서는 line_item_id 숫자만 증가하며동일한구조로사용된다. 이렇게할경우 SQL 문은단 1 회만수행되며 Parsing 작업도단 1 회면충분하다 본 Test 결과에서는 shared pool truncate 에따른내부작업으로 인해 execute count, parse count 가더큰값으로표시되므로유의하기바란다. Stat Latch (get) 구 분 Case 1 Case 2 Case 3 소요시간 1.02 1.01 0.03 execute count 795 785 55 recursive calls 3,539 3,179 780 parse count (total) 723 735 14 parse count (hard) 672 43 6 parse time cpu 36 7 6 parse time elapsed 86 73 22 opened cursors cumulative 714 712 14 CPU used by this session 60 35 7 library cache 19,174 23,225 1,090 shared pool 21,964 3,854 890 row cache objects 6,476 1,012 655

Case2 는주어진구조에서 Bind SQL 을사용한형태이다. 앞서언급했듯이값에대한부분은 Bind SQL 을통해공유가가능하지만컬럼이름에대한부분은여전히컬럼이름의개수만큼 Literal SQL 문으로작성되어야하므로어느정도의 Hard Parsing 은피하지못하고있다. 하지만구조는그대로사용하면서일부만 Bind SQL 로사용한것은많은향상을일으키지는못하고있다. 물론더많은자료에대해수행할경우 Case1 과 Case2 의격차는좀더커질수있지만큰효과는없어보인다. Case3 는 PL/SQL 로작성된프로그램을 SQL 문으로변경하면서구조를변경한사례이다. 실행횟수, 구조를변경한 Case3 은 parse 횟수, latch 자원사용량등 Case1 은물론 Case2 와비교해서도상당한향상이있었음을알수있다. 6.3 정해지지않은개수의변수를사용해발생하는 Literal SQL 앞의두가지사례는모두 SQL 문을통합해상당한성능향상을이룩한사례를제시하였다. 하지만실무에서는프로그램의구조상 SQL 문의통합이불가능한경우가많이존재한다. 예를들어전사개발표준이각단위별로작성된작은모듈을호출해사용하도록한경우이거나패키지프로그램을이용해또다른업무구축을하고자할경우등이런제약사항이발생하는경우는주변에서많이찾아볼수있다. 본예제는기능별로프로그램이모듈화한경우이며각모듈간기능의통합을불가능한경우를다룬다. 본프로그램은다음과같은 2 개의모듈로구성되어있으며 Caller 모듈은처리대상을추출해 Processor 모듈로넘겨주고, Processor 모듈은 Caller 모듈에서넘겨준처리대상으로값을처리한후 Caller 모듈로결과를보낸다. Caller 모듈은넘겨받은결과값으로후속처리 여기서는화면출력 을하는구조를갖는다. Caller -------------------- 처리될값의인자로 Processor 모듈을호출한후처리결과수신 1개이상의값 List 처리결과 Processor -------------------- 호출한모듈로부터넘겨받은인자를이용해처리 Processor 모듈을개발한개발자는 Processor 모듈을호출하는모듈이얼마나많은값 List 를 넘겨줄지알수없으므로입력되는값 List 를문자열로구성한후 IN 을이용해처리하는 다음과같은방식을사용하였다. ( 편의상 1 개의 Package 로 Caller 와 Processor 모듈을 표현하였다.) 본예제는 Caller 모듈에서외부에서입력받은기간중회계적으로 1/4, 2/4 기에 있었던프로모션행사중 Internet 을통해수행됐으며직접판매점을대상으로한프로모션 행사의정보를구하고 Processor 모듈에서는 Caller 모듈에서구해진프로모션행사에대해 일자별로프로모션비용을구해 Caller 모듈로넘겨지는구조를갖는다. -- Package Spec 1 create or replace package sh.lit_case3 AUTHID CURRENT_USER as 2 TYPE typ_promo IS TABLE OF promotions.promo_id%type index by binary_integer; 3 4 procedure proc_caller(p_begin_date in varchar2, p_end_date in varchar2); 5 procedure proc_processor(p_promo_list in typ_promo, p_time in varchar2, p_day_name out varchar2, p_sum_promo out number); 6 end lit_case3;

-- Package Body 01 create or replace package body sh.lit_case3 as 02 procedure proc_caller(p_begin_date in varchar2, p_end_date in varchar2) is 03 cursor cur_times(i_begin_date varchar2,i_end_date varchar2) is 04 select to_char(time_id,'yyyymmdd') time_id 05 from sh.times 06 where fiscal_quarter_number in (1,2) 07 and time_id between to_date(i_begin_date,'yyyymmdd') and to_date(i_end_date,'yyyymmdd'); 08 cursor cur_sales(i_date varchar2) is 09 select distinct prom.promo_id 10 from 11 sh.channels chan 12,sh.sales sales 13,sh.promotions prom 14 where 15 chan.channel_id=sales.channel_id 16 and sales.promo_id=prom.promo_id 17 and time_id=to_date(i_date,'yyyymmdd') 18 and chan.channel_class = 'Direct' 19 and prom.promo_category = 'internet'; 20 rec_time cur_times%rowtype; 21 tab_promo_id typ_promo; 22 v_sum_promo number; 23 v_day_name varchar2(20); 24 begin 25 open cur_times(p_begin_date,p_end_date); 26 loop 27 fetch cur_times into rec_time; 28 exit when cur_times%notfound; 29 open cur_sales(rec_time.time_id); 30 v_sum_promo:=0; 31 fetch cur_sales bulk collect into tab_promo_id; 32 if cur_sales%rowcount>0 then 33 proc_processor(tab_promo_id,rec_time.time_id,v_day_name,v_sum_promo); 34 -- dbms_output.put_line(rec_time.time_id '[' v_day_name '] : ' v_sum_promo); 35 end if; 36 close cur_sales; 37 end loop; 38 close cur_times; 39 end; 40 41 procedure proc_processor(p_promo_list in typ_promo, p_time in varchar2, p_day_name out varchar2, p_sum_promo out number) is 42 v_param_list varchar2(1000); 43 v_sql varchar2(1000); 44 i pls_integer:=1; 45 begin 46 v_param_list:=''; 47 while p_promo_list.exists(i) loop 48 if v_param_list is null then 49 v_param_list:='(' to_char(p_promo_list(i)); 50 else 51 v_param_list:=v_param_list ',' to_char(p_promo_list(i)); 52 end if; 53 i:=i+1;

54 end loop; 55 v_param_list:= v_param_list ')'; 56 v_sql:=' 57 declare 58 p_time varchar2(100); 59 p_sum_promo number; 60 begin 61 :p_sum_promo:=0; 62 select max(tim.day_name),nvl(sum(promo_cost),0) into :p_day_name,:p_sum_promo 63 from sh.promotions prom, sh.times tim 64 where promo_id in ' v_param_list ' and to_date(''' p_time ''',''YYYYMMDD'') between promo_begin_date and promo_end_date and tim.time_id=to_date(''' p_time ''',''YYYYMMDD''); 65 exception 66 when others then 67 :p_sum_promo := -1; 68 end;'; 69 execute immediate v_sql using in out p_sum_promo,in out p_day_name; 70 end; 71 end lit_case3; proc_processor 모듈은 p_promo_list 를통해입력되는값의개수를알지못하므로 line 46~line55 까지입력되는값으로문자열을구성하고있다. 그리고이것을사용해 line64 에서와 같이 IN 연산자를이용해정해지지않은개수의인수값을처리하고있으며실제처리되는 부분에서는 declare begin exception end 의구조를취하고있다. 입력받은값 List 를 문자열로구성하였으므로 Literal SQL 로수행되며 Processor 모듈은수행하는 SQL 문이모두 Hard Parsing 을유발하므로이부분이많이호출될경우성능상의문제가발생할수있다. 위의구조를그대로사용할경우특별히문제를개선할수있는방법은없어보인다. Oracle Version 8.1.6 부터 Literal SQL 문이사용된경우라도내부적으로 Bind SQL 문으로변환해수행할 수있는방식을제공하며 cursor_sharing 이라는 Parameter 값을조정해수행할수있다. 1 차적으로생각할수있는방법은 Processor 모듈에서 cursor_sharing Parameter 값을조정해 프로그램의구조변경없이 Bind SQL 을사용하는방식을생각해볼수있다. 하지만 Processor 모듈의어느부분에 execute immediate alter session set cursor_sharing=force 를사용하더라도 Bind SQL 로변경되지않았다는것을알수있을것이다. Test 결과 PL/SQL 구문에서 cursor_sharing 은 PL/SQL 내부에 declare ~ begin ~ end 구조내부에존재하는 Literal SQL 문에 대해서는적용되지않으며 SQL 내부에 bind 변수가일부사용될경우도적용되지않는것으로 나타났다. 따라서프로그램구조를일부변경해다음과같이재작성해볼수있다. -- Package Spec 위와동일 -- Package Body 01 create or replace package body sh.lit_case3 as 02 procedure proc_caller(p_begin_date in varchar2, p_end_date in varchar2) is 03 cursor cur_times(i_begin_date varchar2,i_end_date varchar2) is 04 select to_char(time_id,'yyyymmdd') time_id 05 from sh.times 06 where fiscal_quarter_number in (1,2) 07 and time_id between to_date(i_begin_date,'yyyymmdd') and to_date(i_end_date,'yyyymmdd');

08 cursor cur_sales(i_date varchar2) is 09 select distinct prom.promo_id 10 from 11 sh.channels chan 12,sh.sales sales 13,sh.promotions prom 14 where 15 chan.channel_id=sales.channel_id 16 and sales.promo_id=prom.promo_id 17 and time_id=to_date(i_date,'yyyymmdd') 18 and chan.channel_class = 'Direct' 19 and prom.promo_category = 'internet'; 20 rec_time cur_times%rowtype; 21 tab_promo_id typ_promo; 22 v_sum_promo number; 23 v_day_name varchar2(20); 24 begin 25 open cur_times(p_begin_date,p_end_date); 26 loop 27 fetch cur_times into rec_time; 28 exit when cur_times%notfound; 29 open cur_sales(rec_time.time_id); 30 v_sum_promo:=0; 31 fetch cur_sales bulk collect into tab_promo_id; 32 if cur_sales%rowcount>0 then 33 proc_processor(tab_promo_id,rec_time.time_id,v_day_name,v_sum_promo); 34 -- dbms_output.put_line(rec_time.time_id '[' v_day_name '] : ' v_sum_promo); 35 end if; 36 close cur_sales; 37 end loop; 38 close cur_times; 39 end; 40 41 procedure proc_processor(p_promo_list in typ_promo, p_time in varchar2, p_day_name out varchar2, p_sum_promo out number) is 42 v_param_list varchar2(1000); 43 v_sql varchar2(1000); 44 i pls_integer:=1; 45 begin 46 v_param_list:=''; 47 while p_promo_list.exists(i) loop 48 if v_param_list is null then 49 v_param_list:='(' to_char(p_promo_list(i)); 50 else 51 v_param_list:=v_param_list ',' to_char(p_promo_list(i)); 52 end if; 53 i:=i+1; 54 end loop; 55 v_param_list:= v_param_list ')'; 56 execute immediate 'alter session set cursor_sharing=force'; 57 begin 58 v_sql:='select max(tim.day_name),nvl(sum(promo_cost),0) 59 from sh.promotions prom, sh.times tim 60 where promo_id in ' v_param_list ' and to_date(''' p_time ''',''YYYYMMDD'') between promo_begin_date and promo_end_date and tim.time_id=to_date(''' p_time ''',''YYYYMMDD'')'; 61 execute immediate v_sql into p_day_name,p_sum_promo;

62 exception 63 when others then 64 p_sum_promo := -1; 65 end; 66 execute immediate 'alter session set cursor_sharing=exact'; 67 end; 68 end lit_case3; 위와같이프로그램을작성하면다음 < 그림 2> 와같이사용된 Literal SQL 문이모두 Bind SQL 문으로변경되어수행됨을알수있다. 그림 2.. Shared Pool 을통해본 Literal SQL 과 Cursor_sharing 으로 Bind 처리된 SQL 이외에는대안이없을까? cursor_shaing 을사용하지않고다음과같이 Bind SQL 문을작성할 수도있다. -- Package Spec 위와동일 -- Package Body 01 create or replace package body sh.lit_case3 as 02 procedure proc_caller(p_begin_date in varchar2, p_end_date in varchar2) is 03 cursor cur_times(i_begin_date varchar2,i_end_date varchar2) is 04 select to_char(time_id,'yyyymmdd') time_id 05 from sh.times 06 where fiscal_quarter_number in (1,2) 07 and time_id between to_date(i_begin_date,'yyyymmdd') and to_date(i_end_date,'yyyymmdd'); 08 cursor cur_sales(i_date varchar2) is 09 select distinct prom.promo_id 10 from 11 sh.channels chan 12,sh.sales sales 13,sh.promotions prom 14 where 15 chan.channel_id=sales.channel_id 16 and sales.promo_id=prom.promo_id 17 and time_id=to_date(i_date,'yyyymmdd') 18 and chan.channel_class = 'Direct'

19 and prom.promo_category = 'internet'; 20 rec_time cur_times%rowtype; 21 tab_promo_id typ_promo; 22 v_sum_promo number; 23 v_day_name varchar2(20); 24 begin 25 open cur_times(p_begin_date,p_end_date); 26 loop 27 fetch cur_times into rec_time; 28 exit when cur_times%notfound; 29 open cur_sales(rec_time.time_id); 30 v_sum_promo:=0; 31 fetch cur_sales bulk collect into tab_promo_id; 32 if cur_sales%rowcount>0 then 33 proc_processor(tab_promo_id,rec_time.time_id,v_day_name,v_sum_promo); 34 -- dbms_output.put_line(rec_time.time_id '[' v_day_name '] : ' v_sum_promo); 35 end if; 36 close cur_sales; 37 end loop; 38 close cur_times; 39 end; 40 41 procedure proc_processor(p_promo_list in typ_promo, p_time in varchar2, p_day_name out varchar2, p_sum_promo out number) is 42 v_sql varchar2(1000); 43 v_day_name varchar2(100); 44 v_sum_promo number; 45 46 i pls_integer:=1; 47 j pls_integer; 48 cur_sql_exec number; 49 ignore number; 50 begin 51 cur_sql_exec:=dbms_sql.open_cursor; 52 v_sql:='select max(tim.day_name),nvl(sum(promo_cost),0) 53 from sh.promotions prom, sh.times tim 54 where to_date(:p_time,''yyyymmdd'') between promo_begin_date and promo_end_date and tim.time_id=to_date(:p_time,''yyyymmdd'') and promo_id in ('; 55 while p_promo_list.exists(i) loop 56 v_sql:=v_sql ':b' i ','; 57 i:=i+1; 58 end loop; 59 v_sql:=substr(v_sql,1,lengthb(v_sql)-1) ')'; 60 dbms_sql.parse(cur_sql_exec,v_sql,1); 61 begin 62 dbms_sql.define_column(cur_sql_exec,1,p_day_name,100); 63 dbms_sql.define_column(cur_sql_exec,2,p_sum_promo); 64 dbms_sql.bind_variable(cur_sql_exec,'p_time',p_time); 65 i:=1; 66 while p_promo_list.exists(i) loop 67 dbms_sql.bind_variable(cur_sql_exec,'b' i,p_promo_list(i)); 68 i:=i+1; 69 end loop; 70 ignore:=sys.dbms_sql.execute_and_fetch(cur_sql_exec); 71 dbms_sql.column_value(cur_sql_exec,1,p_day_name); 72 dbms_sql.column_value(cur_sql_exec,2,p_sum_promo); 73 if ignore=0 then

74 p_sum_promo:=0; 75 end if; 76 exception 77 when others then 78 p_sum_promo := -1; 79 end; 80 dbms_sql.close_cursor(cur_sql_exec); 81 exception 82 when others then 83 if dbms_sql.is_open(cur_sql_exec) then 84 dbms_sql.close_cursor(cur_sql_exec); 85 end if; 86 end; 87 end lit_case3; Processor 로입력되는 p_promo_list 변수를이용해동적으로 Bind SQL 문을구성하는것이다. 이런방식을사용하면 cursor_sharing 사용에따르는내부적으로수행되는 Literal Check 부분을 수행하지않으므로유리하지만 dbms_sql Package 를과다하게호출하는부담도발생하므로 어느것이유리하다고잘라말하기는어려울듯하다. 두번째와세번째방법을이용하면분명히 Bind SQL 문이사용되지만 IN List 에들어오는변수의 개수가서로틀리기때문에그효율이떨어진다고할수있다. 위에첨부된 < 그림 2> 에서도 알수있듯이 2,4,6 번수행된 SQL 문도존재하므로효율이좋다고할수는없다. 이문제를 해결하기위해가장 IN List 에들어가는변수중가장많이들어가는변수를구해서모든 SQL 문의 IN List 를그개수로구성하는방법을떠올릴수있다. -- Global 변수를저장하는 Package 1 create or replace package sh.global_variable AUTHID CURRENT_USER as 2 g_maxcnt number:=15; 3 end global_variable; -- Package Spec 위와동일 -- Package Body 01 create or replace package body sh.lit_case3 as 02 procedure proc_caller(p_begin_date in varchar2, p_end_date in varchar2) is 03 cursor cur_times(i_begin_date varchar2,i_end_date varchar2) is 04 select to_char(time_id,'yyyymmdd') time_id 05 from sh.times 06 where fiscal_quarter_number in (1,2) 07 and time_id between to_date(i_begin_date,'yyyymmdd') and to_date(i_end_date,'yyyymmdd'); 08 cursor cur_sales(i_date varchar2) is 09 select distinct prom.promo_id 10 from 11 sh.channels chan 12,sh.sales sales 13,sh.promotions prom 14 where 15 chan.channel_id=sales.channel_id 16 and sales.promo_id=prom.promo_id 17 and time_id=to_date(i_date,'yyyymmdd') 18 and chan.channel_class = 'Direct' 19 and prom.promo_category = 'internet';

20 rec_time cur_times%rowtype; 21 tab_promo_id typ_promo; 22 v_sum_promo number; 23 v_day_name varchar2(20); 24 begin 25 open cur_times(p_begin_date,p_end_date); 26 loop 27 fetch cur_times into rec_time; 28 exit when cur_times%notfound; 29 open cur_sales(rec_time.time_id); 30 v_sum_promo:=0; 31 fetch cur_sales bulk collect into tab_promo_id; 32 if cur_sales%rowcount>0 then 33 proc_processor(tab_promo_id,rec_time.time_id,v_day_name,v_sum_promo); 34 -- dbms_output.put_line(rec_time.time_id '[' v_day_name '] : ' v_sum_promo); 35 end if; 36 close cur_sales; 37 end loop; 38 close cur_times; 39 end; 40 41 procedure proc_processor(p_promo_list in typ_promo, p_time in varchar2, p_day_name out varchar2, p_sum_promo out number) is 42 v_sql varchar2(1000); 43 v_day_name varchar2(100); 44 v_sum_promo number; 45 46 i pls_integer:=1; 47 j pls_integer; 48 cur_sql_exec number; 49 ignore number; 50 begin 51 cur_sql_exec:=dbms_sql.open_cursor; 52 v_sql:='select max(tim.day_name),nvl(sum(promo_cost),0) 53 from sh.promotions prom, sh.times tim 54 where to_date(:p_time,''yyyymmdd'') between promo_begin_date and promo_end_date and tim.time_id=to_date(:p_time,''yyyymmdd'') and promo_id in ('; 55 for i in 1..sh.global_variable.g_maxcnt loop 56 if i=sh.global_variable.g_maxcnt then 57 v_sql:=v_sql ':b' i ')' ; 58 else 59 v_sql:=v_sql ':b' i ',' ; 60 end if; 61 end loop; 62 dbms_sql.parse(cur_sql_exec,v_sql,1); 63 begin 64 dbms_sql.define_column(cur_sql_exec,1,p_day_name,100); 65 dbms_sql.define_column(cur_sql_exec,2,p_sum_promo); 66 dbms_sql.bind_variable(cur_sql_exec,'p_time',p_time); 67 while p_promo_list.exists(i) loop 68 dbms_sql.bind_variable(cur_sql_exec,'b' i,p_promo_list(i)); 69 i:=i+1; 70 end loop; 71 j:=i; 72 while j<=sh.global_variable.g_maxcnt loop 73 dbms_sql.bind_variable(cur_sql_exec,'b' j,-1); 74 j:=j+1;

75 end loop; 76 ignore:=sys.dbms_sql.execute(cur_sql_exec); 77 ignore:=sys.dbms_sql.fetch_rows(cur_sql_exec); 78 dbms_sql.column_value(cur_sql_exec,1,p_day_name); 79 dbms_sql.column_value(cur_sql_exec,2,p_sum_promo); 80 if ignore=0 then 81 p_sum_promo:=0; 82 end if; 83 exception 84 when others then 85 p_sum_promo := -1; 86 end; 87 dbms_sql.close_cursor(cur_sql_exec); 88 exception 89 when others then 90 if dbms_sql.is_open(cur_sql_exec) then 91 dbms_sql.close_cursor(cur_sql_exec); 92 end if; 93 end; 94 end lit_case3; 이방법은 SQL 문의공유를극대화했다는점에서는장점을갖지만 IN List 를통해입력되는 변수의최대개수를구하는부분이또다른성능의병목이될수있다는점을무시할수없다. 또최개개수만큼 Dummy 로 Bind 값을구성해주어야하므로많이사용되는경우이부분 또한무시할수없는부분이될수있다. 여기서는최대개수가미리정해진것으로가정하고 사용했으나실무에서는미리지정된값을사용하기가어려울수있다. 또최대개수를절대 발생하지않을만큼넉넉하게지정하면앞서언급한대로 Processor 모듈에서 SQL 문구성에 따른부담으로나타나오히려시간이더많이소요될수있으므로이또한바람직하지않다. Stat Latch (get) 구 분 Case 1 Case 2 Case 3 Case 4 소요시간 4.04 3.07 3.02 3.05 execute count 1,227 1,427 885 893 recursive calls 8,564 8,648 7,808 9,055 parse count (total) 641 829 339 347 parse count (hard) 412 45 57 28 parse time cpu 96 28 21 27 parse time elapsed 213 140 110 103 opened cursors cumulative 596 790 300 304 CPU used by this session 297 242 223 236 library cache 28,724 16,516 33,371 47,519 shared pool 28,972 5,189 6,900 6,631 row cache objects 20,232 4,076 4,436 2,870 수행시간은 Case1 을제외하고는모두큰차이없는것으로보인다. 다만 Case4 는 IN List 에사용되는최대변수의개수가이미정해져있다는가정하에작성되었으며동적으로이값을구하는경우는많은부분을검색해결과를가져와야하므로최소 5 초정도의 Overhead 가더발생한다는문제가있기때문에이와같은환경에서는 Case4 는고려대상이될수없다. 여러가지지표를통해볼때 Case2 보다는 Case3 이더바람직해보이지만둘사이에큰차이가있지는않으므로개발자가사용하기편한방법을사용해도좋을듯하다. 하지만 IN List 를

통해넘어오는값의개수가많지않으며대부분동일한개수의값이사용된다면 Case3 이좀 더유리할것으로보인다.

7. 결론 Parsing 이라는작업이무엇인지알아보았고 Hard Parsing 이라는것이얼마나성능에좋지못한영향을미치는지알아보았다. 또몇가지형태별로동일한작업을수행했을때오라클내부적으로어떤자원들이얼마나사용되는지알아보았다. SQL 문작성시 Hard Parsing 을피하면서보다더좋은성능으로작동될수있는몇가지방법을알아보았다. 본문서를처음작성할때는 Literal SQL 의제거를통한성능향상기법까지만알아보려했으나 SQL 사용시그것보다더중요하다고생각되는것이바로 SQL 적인표현기법이기때문에 SQL 통합을통한성능개선까지알아보았다. 본문서에언급되어있는방법외에도여러가지방법으로 Test 를수행해보면더다양한경험을할수있을것으로생각된다. 이문서의내용이많은분들에게도움이됐으면하는바람이다.

8. Reference 1. Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) 2. Oracle9i Database Concepts Release 2 (9.2) 3. Oracle9i Supplied PL/SQL Packages and Types Reference 2 (9.2) 4. Oracle9i Reference Release 2 (9.2) 5. Metalink NOTE 32895.1 : SQL Parsing Flow Diagram 6. Metalink NOTE 62143.1 : Understanding and Tuning the Shared Pool 7. http://technet.oracle.com : Efficient use of bind variables, cursor_sharing and related cursor parameters(bjørn Engsig) 8. http://www.hotsos.com : A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT(Wolfgang Breitling)