Oracle性能Troubleshootingに必須なプログラミング知識ーSQL*Plusでのパラメータ使用

Oracleに性能異常現状が発生した時、分析のためにさまざまな作業を行うようになります。このような作業を効果的にするために知らなければならない基本的なプログラミング知識があります。このぐらいの知識がなければ、分析の速度と正確度が落ちてしまうといえます。

その中でも一番基本的なものがSQL*Plusでパラメータを取り交わす方法です。この方法を身につけたら一見複雑に見える作業も易しく処理できる場合がたくさんあります。

簡単な例で説明します。まず、test.sqlスクリプトを二つのパラメータで実行します。

UKJA@ukja116> @test "logical reads" "name,class,value"

次のようにさまざまの方式でこのパラメータを利用できます。

select name, value from v$sysstat
where name like '%&1%';

NAME                                          VALUE                             
------------------------------ --------------------                             
session logical reads                     4,462,033                             


define __STATNAME = &1

select name, value from v$sysstat
where name like '%&__STATNAME%';

NAME                                          VALUE                             
------------------------------ --------------------                             
session logical reads                     4,462,033                             

define __COLUMNS = "&2"

select &__COLUMNS from v$sysstat
where name like '%&__STATNAME%';

NAME                                CLASS                VALUE                  
------------------------------ ---------- --------------------                  
session logical reads                   1            4,462,033                  

COLUMN命令のNEW_VALUEオプションを使えば、SQLの結果の特定の値をSQL*Plusの変数に変換できます。とても有効な方法です。

col name new_value matching_name

select &__COLUMNS from v$sysstat
where name like '%&__STATNAME%';
NAME                                CLASS                VALUE                  
------------------------------ ---------- --------------------                  
session logical reads                   1            4,462,033                  

prompt matching_name

select &__COLUMNS from v$sysstat
where name = '&matching_name';

NAME                                CLASS                VALUE                  
------------------------------ ---------- --------------------                  
session logical reads                   1            4,462,033                  

ホスト変数を使ってPL/SQLと連動してパラメータをお互いに取り交わすこともできます。複雑なロジックを具現したい場合に有効です。

var matching_name varchar2(100)

begin
 select name into :matching_name
 from v$sysstat
 where name like '%&__STATNAME%';
end;
/

print :matching_name

MATCHING_NAME                                                                   
--------------------------------------------------------------------------------
session logical reads                                                           

この方法の最高峰は次のようにカーソルオブジェクトを取り交わすことです。

var v_cursor refcursor;

declare
  v_sql   varchar2(4000);
begin
  
  v_sql := 'select &__COLUMNS from v$sysstat
        where name = ''&matching_name''';

  open :v_cursor for v_sql;
  
end;
/

print :v_cursor;

NAME                                CLASS                VALUE                  
------------------------------ ---------- --------------------                  
session logical reads                   1            4,462,033                  

上で紹介した方法の中で特にSQL*PLusを呼び出す時パラメータを得る方式とCOLUMN...NEW_VALUE命令を使って動的にパラメータを受ける方式だけは必ず身につける必要があります。

このような方法をよく活用すればなにより自分の生活が美しくて便利になるからです。