バッチプログラムからチューニング対象SQLを抽出する

バッチプログラムからチューニング対象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に識別子を付与する方法を多く使用します。

 今後、この2つの方法を使用した場合に、どのようにバッチプログラムで実行されるSQLの実行情報を抽出できるかを説明し、また、これら2つの方法を適用していない場合にSQLの実行情報を抽出する方法とを説明します。

 内容は、OracleのオブジェクトであるPACKAGE / PROCEDURE / FUNCTIONを使用して作成されたバッチプログラムにのみ適用されることを予めお伝えしておきます。

本格的にバッチプログラムのSQLの実行情報を抽出する方法を紹介する前に、顧客からチューニング要求を受けたSQLリストにあったバッチプログラムを最初に見てみましょう。

以下の構文は、チューニング要求を受け取ったSQLリストにあったものの1つです。

SQLを確認してみると、パフォーマンス改善が必要なSQLではなく、JOBで行われるP_POS_TRANプロシージャに対するチューニング要求をしたものでした。

P_POS_TRANプロシージャは、SELECT、INSERT、UPDATE、DELETEの構文を色々使用している約5000行のプログラムで、ソース内の他のプロシージャを呼び出すなど、かなり複雑な実行ロジックを持っています。

ところが、このような複雑な実行ロジックを持っていて、チェックすべきSQLの数が多いプログラムに対する性能改善要求を受ける場合、そのプログラムで実行されるSQLのうちチューニング対象を選別するのはかなり難しい作業です。

DECLARE

job BINARY_INTEGER := :job ;

next_date DATE := :mydate ; broken BOOLEAN := FALSE ;

BEGIN P_POS_TRAN( 4 , 101 ) ; —> Oracleプロシージャ

:mydate := next_date ; IF broken

THEN :b := 1 ; ELSE :b := 0 ;

END IF ; END ;

このような場合、性能改善が必要なチューニング対象を抽出する最も効率的な方法はトレースを通じて行う内訳を分析することです。

ただし、前述のP_POS_TRANプロシージャのように、データへの入力、変更、削除操作があるバッチプログラムを運用DBサーバーでトレースすることはできません。

 また、開発DBサーバーが構成されていない場合や、開発DBサーバーにプログラムのテストに必要なデータがない場合、トレースを活用してチューニング対象を抽出することは事実上不可能です。

このようにプログラムにトレースを活用して実行結果を分析することが適切でない場合、プログラムの性能チェックのための別の方法でプログラムの全てのＳＱＬに対して性能チェック（プランチェックなど）を行うことになります。

ところで、この方法はチューニング要請を受けた後、プログラムに対する性能改善案を導き出すのに多くの時間がかかることになり、また性能問題を正確に判断できないことがあり、非効率的な方法なのです。

なぜなら、プログラムの全体実行時間（Elapsed Time）の中で最も多くの割合を占めるSQLを改善しなければならないのですが、そのSQLがLoop構文内で実行され、1回実行時に発生するI/Oスループットと実行時間が他のSQLに 比べて少なく、チューニング対象に抽出されないこともあります。

前述のように、トレースの実行やバッチプログラムのすべてのSQLに対する個別のパフォーマンスチェックを通じて、パフォーマンス改善案を導き出すことが困難な場合があります。

このとき、私たちはバッチプログラムのチューニングターゲットSQLを選別するために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;

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;

end;

/

plsql_batch_1; —> データ delete

バッチプログラムの実行履歴を確認するために、前に作成したPLSQL_BATCH_2プロシージャを実行します。

SQL> exec plsql_batch_2 ;

PLSQL_BATCH_2のソース内容を見ると、DBMS_APPLICATION_INFO.SET_MODULEへ

MODULE名を設定しました。 そして、INSERT、UPDATE構文にSQL記述を持つコメントを追加しました。 プロシージャに適用されるこれらの2つは、通常、バッチプログラムまたは単一SQLのパフォーマンス管理に使用される方法です。

 もしチューニング要求を受けたバッチプログラムにどちらかが設定されている場合は、SQLの実行情報を持っているV $ SQLAREAのようなDictionary Viewを活用してチューニング対象を抽出することができます。 しかし、どちらも設定されていないと、チューニング対象を抽出することは難しくなります。

では、前述のプログラムやSQLに識別子を付与した場合と、与えていない場合に応じてどのようにチューニング対象を抽出できるかを調べましょう。

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 ;

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

————- ————————————— ———- ———–

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を抽出することができます。

---

注。 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	NUMBER
INST_ID	NUMBER
KGLHDCDR	RAW(4)
KGLNAOWN	VARCHAR2(64)
KGLNACNM	VARCHAR2(512)	—–> Procedure & Function Name
KGLNACNL	NUMBER
KGLNACHV	NUMBER
KGLHDPDR	RAW(4)
KGLDEPNO	NUMBER
KGLRDHDL	RAW(4)
KGLNADNM	VARCHAR2(512)	—–> SQL Text
KGLNADNL	NUMBER
KGLNADHV	NUMBER	—–> SQL Hash Value

KGLRDFLG                  NUMBER

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

————————————- ———-

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

) t1

where s.hash_value in (3943223768, 3094796916, 111618107)

order by 7 desc

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) ;

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’) )

) t1

order by 7 desc

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

---