Oracle性能Troubleshootingに必須なプログラミング知識ーPL/SQLを利用した同時性制御トリック

PL/SQLは今まで使用したことがあるプログラミング言語中で一番易しいものです。CやJavaなどに比べてこれを言語と言ってもいいのかという思いするほどに。

PL/SQLを良く使用するため必要なパターンは本当にこれだけだと思います。

declare
v_value number;
begin
for idx in 1 .. 1000 loop
 null;
end loop;
end;
/

性能問題を分析してみたら、多い場合、問題を再現する必要ができます。この時PL/SQLが有効です。特に、同時に多数の作業を実行したり、時間および順序を制御する機能はPL/SQLではなければ具現しにくいです。

下のようにすれば、プログラムの実行中0.1秒待機させることができます。

exec dbms_lock.sleep(1/10);

単純に与えられた時間くらい待機することではなく、誰かが起こしてくれるまで待機する機能が必要になる場合もあります。一見具現しにくく見えるかも知れませんが、Oracleが提供するDBMS_PIPEパッケージを利用すれば、手軽に具現できます。

create or replace package pkg_sync as
procedure wait_for_signal(p_maxwait number default dbms_pipe.maxwait);

procedure signal;
end;
/

show errors

create or replace package body pkg_sync as
procedure wait_for_signal(p_maxwait number  default dbms_pipe.maxwait) as
  v_status  number;
  v_received varchar2(1);
begin
  v_status := dbms_pipe.receive_message('signal', p_maxwait);

  if v_status = 0 then
    dbms_pipe.unpack_message(v_received);
  end if;
end wait_for_signal;

procedure signal as
  v_status  number;
begin
  dbms_pipe.pack_message('Y');
  v_status := dbms_pipe.send_message('signal');
end signal;
end;
/

show errors

このパッケージを適当に利用すれば、次のように多数のセッションの作業を同期化することができます。

-- session #1
exec pkg_sync.wait_for_signal;
-- 待機状態になる。

-- session #2
exec pkg_sync.signal;
-- session #1が待機から脱する。

上の機能たちはQueryの実行を必要なだけ遅延させるのに、特に有効です。

create or replace function fsleep(v1 number, vsleep number)
return number
is
begin
dbms_lock.sleep(vsleep);
return 1;
end;
/

select * from t1 where fsleep(c1, 1) = 1;

または。。。

create or replace function fwait(v1 number)
return number
is
begin
pkg_sync.wait_for_signal;
return 1;
end;
/

select * from t1 where fwait(c1) = 1;

多数のセッションが同時に特定の作業を実行するようにするのもよく要求される機能です。次のようにDBMS_JOB.SUBMITプロシージャを利用すればいいです。

-- concurrent job
create or replace procedure proc1
is
begin
dbms_lock.sleep(10);
end;
/

var job_no number;
exec dbms_job.submit(:job_no, 'proc1;');
exec dbms_job.submit(:job_no, 'proc1;');
exec dbms_job.submit(:job_no, 'proc1;');
exec dbms_job.submit(:job_no, 'proc1;');
e

でも、個人的に選り好みおする方法はHOSTコマンドを使って、物理的にいくつかのSQL*Plusを実行するのです。

ed temp
/*
exec proc1;
*/
-- Windows環境ではstart命令, Unix環境ではbackgroundで実行
ho start sqlplus ukja/ukja@ukja116 @temp
ho start sqlplus ukja/ukja@ukja116 @temp
ho start sqlplus ukja/ukja@ukja116 @temp
ho start sqlplus ukja/ukja@ukja116 @temp
ho start sqlplus ukja/ukja@ukja116 @temp

ここで紹介した方法が単純に見えますか。でも、適切に活用すれば作業効率が劇的に上がると感じるようになるでしょう。