SQLチューニング対象選定方法 の第7回目は（Case別）SQLチューニング対象選定方法の4回目 です。
「Case4. パッチプログラム関連性能改善対象選定」について解説していきます。
それでは早速スタートしましょう！！

　2.3.4 Case4.バッチプログラム関連性能改善対象選定

■　ターゲット抽出スクリプト

複数のロジックと複数のSQLを持つバッチプログラム（PL/SQLで作成されたProcedure, Functionなど）の処理遅延が
発生する場合、どのように対処すべきでしょうか?

バッチプログラムにTrace ON/OFFを設定し、テスト後に性能改善をすべきでしょうか？
この方法も性能問題を正確に把握して改善できる良い方法です。

このような場合も、Dictionary Viewの活用だけで十分な対応が可能です。

下記のスクリプトを見ると、インラインビューO部分にバッチプログラム名を入力すると、そのプログラムで実行する
すべてのSQLに対するI/O発生量、抽出Row数、応答時間、実行回数などの情報が抽出されます。
これらの情報を使って実行時間が長くかかる部分のSQLを抽出して改善することができます。

［ 特定のProgram内で実行されたSQL抽出スクリプト ］

SELECT o.object_name、
         s.parsing_schema_name AS schema、         
         s.module、           
         s.sql_id、           
         s.hash_value、               
         substr(s.sql_text,1,100) as sqltext、          
         s.executions、               
         s.buffer_gets、                
         s.disk_reads、               
         round(s.rows_processed/s.executions,1) as "Rows" 、   
         round(s.buffer_gets/s.executions,1) lio、      
         round(s.elapsed_time/s.executions/1000000,1) elapsed_sec、 
         round(s.cpu_time/s.executions/1000000,1) cpu_sec、          
         round(s.elapsed_time/1000000,1) as elapsed_time
FROM ( SELECT object_id, object_name
         FROM dba_objects
         WHERE object_name = :Procedure_Name ) o 、
       v$sqlarea s
WHERE o.object_id = s.program_id
ORDER BY 14 DESC

■　活用例

複雑なロジックを持つバッチプログラムに対する性能問題を把握するため、実際のテストデータを生成して上記の
スクリプトを活用した一連の過程を説明します。

バッチプログラムのテストデータ生成

* テーブルを作成する

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 ;


* Index 作成と統計情報の収集

create index plsql_t1_idx_01 on plsql_t1 ( c1 ) ;

exec dbms_stats.gather_table_stats(ownname=>'exem',tabname=>'plsql_t1',cascade=>true,estimate_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 */ from 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;
      
plsql_batch_1; ---> 데이터 delete
end;
/

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

SQL> exec plsql_batch_2 ；

Dictionary ViewのうちV$SQLAREA、DBA_OBJECTSを活用すると、バッチプログラムの実行履歴を照会することができます。V$SQLAREAはSQLの実行履歴を確認することができ、DBA_OBJECTSはObject(Function, Procedure)の情報を照会することができます。V$SQLとV$SQLAREAのPROGRAM_IDカラムはDBA_OBJECTSのOBJECT_IDカラムと連結が可能で、PROCEDUREやFUNCTIONで作成されたバッチプログラムの場合、DBA_OBJECTSとV$SQLまたはV$SQLAREAを通じてチューニング対象のSQLを抽出することができます。

Dictionary Viewのうち V$SQLAREA、DBA_OBJECTS を活用すると、バッチプログラムの実行履歴を照会できます。
V$SQLAREA はSQLの実行履歴を確認することができるため、DBA_OBJECTS は Object（Function, Procedure）
の情報を照会することができます。
V$SQL と V$SQLAREAの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を抽出することができます。

ol 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') )
         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

SQLチューニングブログ 2nd Season（第10回）　終

今回はこれで終わりたいと思います。次回SQLチューニングブログもお楽しみに！！

次回SQLチューニングブログ

SQLチューニングブログ 2nd Season（第11回）
「 Case5.ASHを活用した性能改善対象選定 」 について

データベース運用のことなら日本エクセムにお任せください

私たちは、日本のITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。

日本エクセム株式会社
営業推進部 まで
✉ sales@ex-em.co.jp