배치프로그램에서튜닝대상 SQL 추출하기 엑셈컨설팅본부 /DB 컨설팅팀박성호 배치프로그램의성능문제를진단하기위해트레이스를사용할수없고, 개별 SQL 에대한성 능점검은비효율적인경우에어떻게배치프로그램의성능문제를제대로파악하고개선안을도 출할것인가? 복잡한로직을가지고있는프로그램 ( 이후배치프로그램 ) 에대한성능문제를파악하기위해수행되는모든 SQL 에대한개별수행내역을정확히판단할수있어야한다. 왜냐하면, 특정배치프로그램에서수행되는모든 SQL 의 I/O 처리량이나수행시간등에대한정확한정보를추출할수있다면, 배치프로그램의 SQL 중튜닝대상을선별하는작업을효율적으로수행할수있기때문이다. 따라서이문서에서 Oracle 이제공하는 X$KGLRD 테이블과 SQL 의수행내역을조회할수있는 Dynamic Performance View 를활용하여배치프로그램에서수행하는 SQL 의수행정보를추출할수있는방법을제공할것이다. 그래야만추출된 SQL 의수행정보를정확하게분석하여배치프로그램의 SQL 중튜닝대상을제대로선정할수있기때문이다. 보통 DB 서버에서수행되는프로그램 (SQL 이나배치프로그램등 ) 에대해성능관리를위해서, DBMS_APPLICATION_INFO 패키지를활용하여 MODULE 명을설정하거나 SQL 에식별자를부여하는방법을많이사용한다. 앞으로이두방법을사용했을경우에어떻게배치프로그램에서수행되는 SQL 의수행정보를추출할수있는지기술할것이고, 또한이두방법을적용하지않은경우에 SQL 의수행정보를추출하는방법으로기술할내용은 Oracle 의오브젝트인 PACKAGE/PROCEDURE/FUNCTION 을이용하여작성된배치프로그램에만적용되는점을미리알린다. 본격적으로배치프로그램의 SQL 에대한수행정보를추출하는방법에대해소개하기전에고 객사에서튜닝요청을받았던 SQL 목록에있던배치프로그램을먼저보도록하자. 아래의구문은튜닝요청을받은 SQL 목록에있던것중하나이다. 그런데 SQL 을확인해보니 성능개선이필요한 SQL 이아닌, JOB 으로수행되는 P_POS_TRAN 프로시저에대한튜닝요청 Part 1 ORACLE 39
을한것이었다. P_POS_TRAN 프로시저는 SELECT, INSERT, UPDATE, DELETE 구문을다양하게가진약 5,000 라인의프로그램으로, 소스내에또다른프로시저를호출하는등상당히복잡한수행로직을가지고있었다. 그런데이렇게복잡한수행로직을가지고있고, 점검해야할 SQL 의개수가많은프로그램에대한성능개선요청을받는경우, 해당프로그램에서수행되는 SQL 중튜닝대상을선별하는것은상당히어려운작업이다. DECLARE job BINARY_INTEGER := :job ; next_date DATE := :mydate ; broken BOOLEAN := FALSE ; BEGIN P_POS_TRAN( 4, 101 ) ; ---> 오라클프로시저 :mydate := next_date ; IF broken THEN :b := 1 ; ELSE :b := 0 ; END IF ; END ; 이런경우성능개선이필요한튜닝대상을추출하는가장효율적인방법은트레이스를통해수행내역을분석하는것이다. 그러나앞에서언급된 P_POS_TRAN 프로시저와같이데이터에대한입력, 변경, 삭제작업이있는배치프로그램을운영 DB 서버에서트레이스를수행할수없다. 또한개발 DB 서버가아예구성되어있지않거나, 개발 DB 서버에프로그램의테스트에필요한데이터가없다면, 트레이스를활용하여튜닝대상을추출하는것은사실상불가능하다. 이렇게프로그램에트레이스를활용하여수행결과를분석하는것이적절하지않는경우, 프로그램의성능점검을위한또다른방법으로프로그램의모든 SQL 에대한성능점검 ( 플랜점검등 ) 을수행하게된다. 그런데이방법은튜닝요청을받은후프로그램에대한성능개선안을도출하는데까지많은시간이소요될것이고, 또한성능문제를정확하게판단하지못할수있어비효율적인방법이다. 왜냐하면, 프로그램의전체수행시간 (Elapsed Time) 중가장많은비중을차지하는 SQL 을개선해야하나, 해당 SQL 이 Loop 구문안에서수행되고, 1 회수행시발생하는 I/O 처리량과수행시간이타 SQL 에비해적어튜닝대상으로추출되지않을수도있기때문이다. 앞에서와같이트레이스수행이나배치프로그램의모든 SQL 에대한개별성능점검을통해성 능개선안을도출하는것이힘든경우가있다. 이럴때우리는배치프로그램의튜닝대상 SQL 을 40 2013 기술백서 White Paper
선별하기위해서 SQL 의수행정보를추출하는방법으로 Oracle 이제공하는정보를원활하게 조회하여활용할수있다면좀더쉽고, 효율적이고, 빠르게배치프로그램에대한성능개선을할 수있을것이다. 그럼 Oracle 이제공하는 X$KGLRD 테이블과 SQL 의수행내역을조회할수있는 Dynamic Performance View 를활용하는방법을알아보도록하자. 테스트를진행하면서내용을확인하기위해서먼저테스트데이터를생성하도록하자. Script. 배치프로그램테스트데이터생성 * 테이블생성하기 drop table plsql_t1 purge; create table plsql_t1 as select level as c1, chr(65+mod(level,26)) as c2, level+99999 as c3 from dual connect by level <= 1000000 ; * 인덱스생성및통계정보수집하기 create index plsql_t1_idx_01 on plsql_t1 ( c1 ) ; exec dbms_stats.gather_table_stats(ownname=>'exem',tabname=>'plsql_t1',cascade=>true,estima te_percent=>100) ; * 프로시저생성하기 drop procedure plsql_batch_1 ; drop procedure plsql_batch_2 ; create or replace procedure plsql_batch_1 as begin delete /*+ BatchTest_plsql_batch_1 */ plsql_t1 ---> SQL 에식별자부여 where c2 = 'aa'; commit; Part 1 ORACLE 41
end; / create or replace procedure plsql_batch_2 as begin dbms_application_info.set_module('batchtest',''); ---> Module Name 설정 insert /*+ BatchTest_plsql_batch_2 */ into plsql_t1 ---> SQL 에식별자부여 select c1, 'a', c3 from plsql_t1 where c2 = 'A'; commit; update /*+ BatchTest_plsql_batch_2 */ plsql_t1 ---> SQL 에식별자부여 set c2 = 'aa' where c2 = 'a'; commit; plsql_batch_1; ---> 데이터 delete end; / 배치프로그램의수행내역을확인하기위해서앞에서생성한 PLSQL_BATCH_2 프로시저를수행한다. SQL> exec plsql_batch_2 ; PLSQL_BATCH_2 의소스내용을보면, DBMS_APPLICATION_INFO.SET_MODULE 으로 MODULE 명을설정했다. 그리고 INSERT, UPDATE 구문에 SQL 설명을가지는주석을추가하였다. 프로시저에적용한이두가지는일반적으로배치프로그램이나단일 SQL 의성능관리를위해사용되는방법이다. 만약튜닝요청을받은배치프로그램에둘중한가지라도설정되어있는경우는 SQL 의수행정보를가지고있는 V$SQLAREA 와같은 Dictionary View 를활용하여튜닝대상을추출할수있다. 그러나둘중어떤것도설정되어있지않다면튜닝대상을추출하는것은어려워진다. 그럼앞에서언급한프로그램이나 SQL 에식별자를부여한경우와부여하지않은경우에따라 어떻게튜닝대상을추출할수있는지알아보도록하자. 42 2013 기술백서 White Paper
MODULE 명또는 SQL 에식별자가있는경우 MODULE 명이설정되어있는경우 PLSQL_BATCH_2 의소스내용을확인해보면, 아래와같이해당배치프로그램에 MODULE 명 을설정하였다. dbms_application_info.set_module('batchtest',''); ---> Module 명설정 Oracle 11.2.0.3 에서테스트를수행한결과 PLSQL_BATCH_2 프로시저에적용한 MODULE 명은 PLSQL_BATCH_2 프로시저에서호출하는 PLSQL_BATCH_1 에도적용되기때문에, 배치프로그램에서수행되는모든 SQL 의수행정보를 V$SQL 의 MODULE 칼럼으로조회가가능하다. 해당배치프로그램에 MODULE 명이설정되어있다고가정하고, 배치프로그램에서수행하는 모든 SQL 중, 총 I/O 처리량이많이발생한순서대로정렬하여추출하고자한다면아래의스크 립트를수행하면된다. select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets from ( select parsing_schema_name Schema, --> 1 module, --> 2 sql_id, --> 3 hash_value, --> 4 substr(sql_text,1,37) substr_sqltext --> 5 executions, --> 6 buffer_gets, --> 7 disk_reads, --> 8 rows_processed, --> 9 round(buffer_gets/executions,1) lio, --> 10 round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11 round(cpu_time/executions/1000000,1) cpu_sec --> 12 from v$sqlarea s where s.module = BatchTest ---> MODULE 명으로검색 order by 7 desc ---> 전체 I/O 처리량이높은순으로정렬 ) t1 where rownum <= 50 ; Part 1 ORACLE 43
MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS ------------- --------------------------------------- ---------- ----------- BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206 BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014 BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901 SQL TEXT 로식별할수있는경우 앞에서 PLSQL_BATCH_1, PLSQL_BATCH_2 을생성할때, 아래와같이개별 SQL 에식별자를 추가하였다. delete /*+ BatchTest_plsql_batch_1 */ insert /*+ BatchTest_plsql_batch_2 */ update /*+ BatchTest_plsql_batch_2 */ 배치프로그램의모든 SQL 에식별자를추가했으므로, 아래와같이배치프로그램의모든 SQL 에대한수행정보를조회할수있다. select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets from ( select parsing_schema_name Schema, --> 1 module, --> 2 sql_id, --> 3 hash_value, --> 4 substr(sql_text,1,37) substr_sqltext, --> 5 executions, --> 6 buffer_gets, --> 7 disk_reads, --> 8 rows_processed, --> 9 round(buffer_gets/executions,1) lio, --> 10 round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11 round(cpu_time/executions/1000000,1) cpu_sec --> 12 from v$sqlarea s where s.sql_fulltext like %BatchTest_plsql_batch% ---> SQL TEXT 로검색 order by 7 desc ) t1 where rownum <= 50 ; MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS ------------- --------------------------------------- ---------- ----------- 44 2013 기술백서 White Paper
BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206 BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014 BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901 MODULE 명또는 SQL 에식별자가없는경우 앞에서 PLSQL_BATCH_1, PLSQL_BATCH_2 프로시저생성시 MODULE 명설정이나 SQL 에식별자를추가하지않았다면, 어떻게튜닝대상을추출할수있을까? 이런경우 Oracle 이제공하는 X$KGLRD 테이블과 DBA_OBJECTS.OBJECTID 와 V$SQL.PROGRAM_ID 으로배치프로그램에서튜닝대상 SQL 을추출할수있다. Note. Oracle 버전이 10g 이전까지는 X$KGLRD 를활용하여추출해야한다. 왜냐하면, 10g 이후 버전에 V$SQL 나 V$SQLAREA 에 PROGRAM_ID 가추가되었기때문이다. X$KGLRD 활용하기 아래에 X$KGLRD 의칼럼정보와테스트예제를통해사용방법을알아보도록하자. x$kglrd 칼럼구성 - Oracle Version: 11.2.0.3 에서추출 Column Name DataType ----------------------- --------------- ADDR RAW(4) INDX INST_ID KGLHDCDR RAW(4) KGLNAOWN VARCHAR2(64) KGLNACNM VARCHAR2(512) -----> Procedure & Function Name KGLNACNL KGLNACHV KGLHDPDR RAW(4) KGLDEPNO KGLRDHDL RAW(4) KGLNADNM VARCHAR2(512) -----> SQL Text KGLNADNL KGLNADHV -----> SQL Hash Value Part 1 ORACLE 45
KGLRDFLG Oracle 이제공하는 X$KGLRD 은 SQL 의수행정보를담고있는데, 특정 PROCEDURE 나 FUNCTION 내에서수행되는 SQL 에대해오브젝트명과함께확인할수있기때문에, 특정배치 프로그램에서수행되는모든 SQL 을추출하고자할때유용하다. Procedure/Function 명으로조회하기 PROCEDURE 나 FUNCTION 명은대문자로입력되어있으므로조회시유의하자. X$KGLRD 에서 PROCEDURE 나 FUNCTION 명으로조회할경우에 PLSQL_BATCH_2 프로시저에서호출 하는 PLSQL_BATCH_1 도같이조회해야전체 SQL 을추출할수있다. col kglnacnm for a15 col kglnadnm for a37 set pagesize 100 select kglnacnm, substr(kglnadnm,1,37) kglnadnm, kglnadhv from x$kglrd where kglnacnm in ('PLSQL_BATCH_2', 'PLSQL_BATCH_1') ; KGLNACNM KGLNADNM KGLNADHV --------------- ------------------------------------- ---------- PLSQL_BATCH_2 UPDATE /*+ BatchTest_plsql_batch_2 */ 3943223768 PLSQL_BATCH_2 COMMIT 255718823 PLSQL_BATCH_2 INSERT /*+ BatchTest_plsql_batch_2 */ 111618107 PLSQL_BATCH_1 COMMIT 255718823 PLSQL_BATCH_1 DELETE /*+ BatchTest_plsql_batch_1 */ 3094796916 SQL 을 SQL Text 로조회하기 SQL Text 로 X$KGLRD 에서조회할경우에는아래와같이수행하면된다. select distinct substr(kglnadnm,1,37) kglnadnm, kglnadhv from x$kglrd where kglnadnm like '%BatchTest_plsql_batch%'; KGLNADNM KGLNADHV 46 2013 기술백서 White Paper
------------------------------------- ---------- UPDATE /*+ BatchTest_plsql_batch_2 */ 3943223768 DELETE /*+ BatchTest_plsql_batch_1 */ 3094796916 INSERT /*+ BatchTest_plsql_batch_2 */ 111618107 SQL 을 Hash Value 로조회하기 DB 서버를모니터링시 Hash_Value 를알고있을때, 만약해당 SQL 이배치프로그램에서수 행되는경우에어떤프로그램에서수행된것인지찾아야할때아래와같이 Hash_Value 로 X$KGLRD 에서조회하면확인할수있다. select distinct substr(kglnadnm,1,37) kglnadnm, kglnadhv from x$kglrd where kglnadhv = 3094796916 ; KGLNADNM KGLNADHV ------------------------------------- ---------- DELETE /*+ BatchTest_plsql_batch_1 */ 3094796916 튜닝대상추출하기 X$KGLRD 을활용하여배치프로그램에서수행된 SQL 을추출후, 아래와같이각 SQL 의수 행정보를분석후튜닝대상을추출하면된다. select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets from ( select parsing_schema_name Schema, --> 1 module, --> 2 sql_id, --> 3 hash_value, --> 4 substr(sql_text,1,37) substr_sqltext, --> 5 executions, --> 6 buffer_gets, --> 7 disk_reads, --> 8 rows_processed, --> 9 round(buffer_gets/executions,1) lio, --> 10 round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11 round(cpu_time/executions/1000000,1) cpu_sec --> 12 from v$sqlarea s Part 1 ORACLE 47
where s.hash_value in (3943223768, 3094796916, 111618107) order by 7 desc ) t1 where rownum <= 50 ; MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS ------------- --------------------------------------- ---------- ----------- BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206 BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014 BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901 DBA_OBJECTS & V$SQLAREA 활용하기 Oracle 버전이 10g 이후부터 V$SQL 과 V$SQLAREA 에 PROGRAM_ID 칼럼이추가되었다. PROGRAM_ID 칼럼은 DBA_OBJECTS 의 OBJECT_ID 칼럼과연결이된다. 그러므로 PROCEDURE 나 FUNCTION 으로작성된배치프로그램의경우, DBA_OBJECTS 와 V$SQL [V$SQLAREA] 을통해튜닝대상 SQL 을추출할수있다. 먼저배치프로그램명으로 OBJECT_ID 를추출한다. select object_name, object_id from dba_objects where object_name IN ('PLSQL_BATCH_1','PLSQL_BATCH_2') ; OBJECT_NAME OBJECT_ID -------------------- ---------- PLSQL_BATCH_1 61738 PLSQL_BATCH_2 61739 DBA_OBJECTS 에서추출된 OBJECT_ID 값으로 V$SQLAREA 의 PROGRAM_ID 와연결하여조회하면아래와같이 SQL 을추출할수있다. col substr_text for a30 col module for a15 select substr(sql_text,1,30) substr_text, module, program_id from v$sqlarea where program_id in (61738, 61739) ; 48 2013 기술백서 White Paper
SUBSTR_TEXT MODULE PROGRAM_ID ------------------------------ --------------- ---------- UPDATE /*+ BatchTest_plsql_bat BatchTest 61739 DELETE /*+ BatchTest_plsql_bat BatchTest 61738 INSERT /*+ BatchTest_plsql_bat BatchTest 61739 앞에서 DBA_OBJECTS 와 V$SQLAREA 를활용하여, 해당배치프로그램에서수행한모든 SQL 에대한수행정보를아래와같이조회할수있다. 그리고조회된정보를면밀히분석하면 배치프로그램의 SQL 중튜닝대상을추출하는것은그리어렵지않을것이다. select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets from ( select parsing_schema_name Schema, --> 1 module, --> 2 sql_id, --> 3 hash_value, --> 4 substr(sql_text,1,37) substr_sqltext, --> 5 executions, --> 6 buffer_gets, --> 7 disk_reads, --> 8 rows_processed, --> 9 round(buffer_gets/executions,1) lio, --> 10 round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11 round(cpu_time/executions/1000000,1) cpu_sec --> 12 from v$sqlarea s where s.program_id in ( select object_id from dba_objects where object_name in ( 'PLSQL_BATCH_1', 'PLSQL_BATCH_2') ) order by 7 desc ) t1 where rownum <= 50 ; MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS ------------- --------------------------------------- ---------- ----------- BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206 BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014 BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901 Part 1 ORACLE 49