はじめに
オラクルはDBリンク付きのSQLを実行する時、必要に応じてクエリから一部分のみ抜き出してリモートデータベースで実行したり、またはクエリ全体をリモートデータベースに丸投げしたりします。したがってクエリのチューニングをしているときなどにリモートで実行されたクエリの実行計画や統計情報を確認することが時として重要となります。そこでここでは、ローカルセッションからクエリのDBリンクで使用されたリモートデータベース上のリモートセッションを簡単に特定する方法や、リモートセッションの実行統計を有効化しローカルセッションから実行統計付きの実行計画を取得する方法などについて考察してみます。
DBリンクとリモートセッションの挙動
まず「リモートセッションはいつ作られていつ終了するのか？」についてです。リモートセッションは、ローカルセッションで最初にDBリンクを使用した時点で自動で作られ、ローカルセッションがクローズされるまで保持されます。クエリを実行する度にオープン・クローズされるわけではありません。ローカルセッション内でDBリンク再使用すると、すでに存在するリモートセッションが再利用されるわけです。またリモートセッションはALTER SESSIONやDBMS_SESSIONで意図的にクローズすることもできます。ひとつのセッションで一度にオープンしておけるリモートセッションの数は初期パラメータopen_linksで決まっているので、これの上限を超える場合は不要なリモートセッションをクローズする必要があります。ちなみにデフォルトは４です。
試してみます。クエリでDBリンクを使用してリモートセッションを作成します。カレントセッションでオープンされているDBリンクはv$dblinkで取得できます（自分のセッションのみです。他セッションの情報は参照できません）。しかし残念ながらv$dblinkにはリモートセッションを特定できる情報はありません。
SELECT * FROM global_name@testdb; -- リモートセッションを作る
GLOBAL_NAME
-----------
TESTDB
SELECT db_link FROM v$dblink; -- リモートセッションの存在確認
DB_LINK
---------------
TESTDB
ALTER SESSIONでクローズしてみますがエラーになります。分散トランザクションがオーブンされているので、コミットかロールバック後クローズします。
ALTER SESSION CLOSE DATABASE LINK testdb; -- クローズ失敗
ORA-02080: database link is in use
COMMIT;
Commit complete.
ALTER SESSION CLOSE DATABASE LINK testdb; -- クローズ成功
Session altered.
SELECT db_link FROM v$dblink;
no rows selected
つまりチューニングに際しては、事前にリモートセッションを特定するクエリを実行しておけばクエリ自体がリモートセッションを作成してくれるので、その後実行されるクエリはその特定されたリモートセッション上で実行されモニタリングがとても楽になるということですね。
リモートセッション情報の取得
それではローカルセッションからリモートセッションを特定する方法をいくつか考えてみます。
ダイナミックパフォーマンスビューにアクセスできる場合
まずはいちばん簡単なリモートセッションがダイナミックパフォーマンスビュー(v$)にアクセスできる場合です。リモートのv$mystatをDBリンク越しに参照するだけです。
gv$mystat(グローバルの方)は、なぜかDBリンク経由では正しい答えが返ってきません。RACの場合はインスタンスはv$instanceから取得します。
SELECT sid FROM v$mystat@testdb WHERE ROWNUM = 1;
SID
----------
247
他のダイナミックパフォーマンスビューと組み合わせれば、インスタンスやSERIAL#など一緒にが取得できます。
SELECT
instance_name, sid, serial#, host_name remote, machine local
FROM v$session@&&DBLink, v$instance@&&DBLink,
(SELECT sid mysid FROM v$mystat@&&DBLink WHERE ROWNUM = 1)
WHERE sid = mysid;
Enter value for dblink: testdb
INSTANCE_NAME SID SERIAL# REMOTE LOCAL
---------------- ---------- ---------- --------------- ----------------
TESTDB 274 19662 server40 server50
開発環境ではリモートアカウントにv_$mystat（アンダースコア付きの方）のSELECT権限を付与してもらうのが手っ取り早いですが、そうでない場合は他の方法を考えます。
ダイナミックパフォーマンスビューにアクセスできない場合
トランザクション用のアカウントではほとんどの場合ダイナミックパフォーマンスビューへのアクセス権限を持たないと思います。USERENV()やSYS_CONTEXT()などの関数を使用すればセッション情報を取得することが可能ですが、これらはスキーマオブジェクトではないためDBリンク経由では以下の様にうまく動いてくれません。
SELECT USERENV('SID') sid FROM DUAL@testdb;
SID
----------
4603 <--- リモートセッションでなくローカルセッションのSID
SELECT USERENV@testdb('SID') sid FROM DUAL;
ORA-00904: "USERENV": invalid identifier
ひとつの解決策としてDBリンク先のリモート側にストアードプログラムを作っておく方法もあります。まぁそれが許される環境もすくないでしょうが。
-- リモートデータベース側でファンクションを作成
CREATE OR REPLACE FUNCTION mysid RETURN NUMBER IS BEGIN RETURN USERENV('SID'); END;
/
-- ローカルデータベースから呼び出す
SELECT mysid@testdb FROM DUAL;
MYSID
----------
274
一番確実なのはUSERENVやSYS_CONTEXTを含んだクエリ全体をリモートに投げてしまうことです。これにはDBMS_SQLが利用できます。以下の様なスクリプトを作っておけばいつでも簡単にリモートセッションの情報が得られます。
SET SERVEROUT ON
DEFINE DBLink='&1';
DECLARE
c INTEGER;
r INTEGER;
n INTEGER;
instance VARCHAR2(35);
sid NUMBER;
user VARCHAR2(35);
BEGIN
c:=DBMS_SQL.OPEN_CURSOR@&DBLink;
DBMS_SQL.PARSE@&DBLink(c, q'[SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SID'), sys_context('userenv', 'session_user') from dual]', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN@&DBLink(c, 1, instance, 35);
DBMS_SQL.DEFINE_COLUMN@&DBLink(c, 2, sid);
DBMS_SQL.DEFINE_COLUMN@&DBLink(c, 3, user, 35);
r:=DBMS_SQL.EXECUTE@&DBLink(c);
n:=DBMS_SQL.FETCH_ROWS@&DBLink(c);
DBMS_SQL.COLUMN_VALUE@&DBLink(c, 1, instance);
DBMS_SQL.COLUMN_VALUE@&DBLink(c, 2, sid);
DBMS_SQL.COLUMN_VALUE@&DBLink(c, 3, user);
DBMS_SQL.CLOSE_CURSOR@&DBLink(c);
DBMS_OUTPUT.PUT_LINE(RPAD('INSTANCE NAME', 36, ' ') || RPAD('USERNAME', 35, ' ') || RPAD('SESSION ID', 10, ' '));
DBMS_OUTPUT.PUT_LINE(RPAD('-', 36, '-') || RPAD('-', 35, '-') || RPAD('-', 10, '-'));
DBMS_OUTPUT.PUT_LINE(RPAD(instance, 36, ' ') || RPAD(user, 35, ' ') || RPAD(sid, 10, ' '));
end;
/
@dblinkmyid testdb
INSTANCE NAME USERNAME SESSION ID
---------------------------------------------------------------------------------
TESTDB TESTUSR 274
確実ではないが権限がなくても使えるもう一つの方法
DBMS_SESSION.UNIQUE_SESSION_IDを使用するとSID, SERIAL#, INST_IDが取得できなくもないです。しかし、マニュアルにはユニーク番号を返すとしか書かれてないため、常に正しいセッション情報を取得できるかどうかは不明です。まぁ、チューニング用のスクリプトとして使用する分には特に問題はないでしょう。正しく所得できない場合は、他の方法を使用すればよいだけなので。
SELECT
TO_NUMBER(SUBSTR(u, 9,4), 'XXXX') inst_id,
TO_NUMBER(SUBSTR(u, 1,4), 'XXXX') sid,
TO_NUMBER(SUBSTR(u, 5,4), 'XXXX') serial#
FROM
(SELECT DBMS_SESSION.UNIQUE_SESSION_ID@&DBLink u FROM dual);
Enter value for dblink: TESTDB
INST_ID SID SERIAL#
---------- ---------- ----------
1 274 19662
DBリンクを使用しているローカルセッションが自分のセッションでない場合
ちなみに他セッションが利用しているDBリンクのリモートセッションの情報を得るには、以下のクエリを使用します。ただし他セッションがどのDBリンクを使用しているかは基本的に確認できないので、使用しているDBリンクが判明している場合のみ使えます。
-- ローカルデータベースにログインし、ターゲットセッションのSPIDを得る
SELECT spid FROM v$process p, v$session s WHERE s.paddr = p.addr AND sid = '&SID';
Enter value for sid: 4603
SPID
------------------------
62855
-- リモートデータベースにログインし（またはローカルデータベースからDBリンクを使用し）、上記のSPIDを使用してセッション情報を得る
SELECT inst_id, sid, serial#, machine FROM gv$session WHERE process = '62855';
INST_ID SID SERIAL# MACHINE
---------- ---------- ---------- ---------------
1 274 25315 server50
リモートセッションの実行統計を有効化
リモートセッションを特定してしまえば、リモートデータベースにログインして実行されたSQLの実行計画が取得できます。しかしチューニングに必要な実行計画統計を取得するにはもうひとつ仕掛けが必要となります。
もちろんリモートデータベースで対象セッションに対して外部からトレースを有効にすればいいのですが、他セッションに介入するには強い権限が必要です。またGATHER_PLAN_STATISTICSヒントは、以下のようになぜかリモートセッションにプッシュされません（どうやら18cはプッシュされるようになった様です！）。
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM global_name@testdb;
GLOBAL_NAME
-----------
TESTDB
==リモートセッションで実行されたクエリと実行計画==
SELECT "A1"."GLOBAL_NAME" FROM "GLOBAL_NAME" "A1"
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| PROPS$ | 1 |
---------------------------------------------
リモートセッションで実行計画統計を有効にするには、ALTER SESSION SET STATISTICS_LEVEL=ALLをリモートセッションに丸投げします。これを上記のSYS_CONTEXTを使用したSQLスクリプトにに組み込んでしまえば、セッション情報を取りつつ実行計画統計を有効にできますね。
DECLARE
c INTEGER;
r INTEGER;
BEGIN
c:=DBMS_SQL.OPEN_CURSOR@&&DBLink;
DBMS_SQL.PARSE@&&DBLink(c, q'[ALTER SESSION SET STATISTICS_LEVEL=ALL]', DBMS_SQL.NATIVE);
r:=DBMS_SQL.EXECUTE@&&DBLink(c);
DBMS_SQL.CLOSE_CURSOR@&&DBLink(c);
END;
/
Enter value for dblink: testdb
SELECT * FROM global_name@testdb;
==リモートセッションで実行されたクエリと実行計画（統計付）==
SELECT "A1"."GLOBAL_NAME" FROM "GLOBAL_NAME" "A1"
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
|* 1 | TABLE ACCESS FULL| PROPS$ | 1 | 1 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------
ローカルセッションからリモートセッションの実行計画取得
ついでにリモートで実行された最後のSQLの実行計画をローカルセッションから取得する方法です。特に必要ないかもしれませんが、かなりお手軽にリモートセッションの実行計画を得られます。これもおなじくDBMS_SQLを使って丸投げします。まぁ基本全部これです（^^)。
DBMS_XPLANはパッケージなのでDBリンクで直接呼び出せばいいんじゃ？って思うかもしれませんが、DBMS_XPLAN.DISPLAY_CURSORはパイプラインファンクションなので残念ながらDBリンク経由では直接実行できません。
SET SERVEROUT ON
DEFINE DBLink='&1';
DECLARE
c INTEGER;
r INTEGER;
n INTEGER;
output VARCHAR2(4000);
BEGIN
c:=DBMS_SQL.OPEN_CURSOR@&DBLink;
DBMS_SQL.PARSE@&DBLink(c, q'[SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'))]', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN@&DBLink(c, 1, output, 4000);
r:=DBMS_SQL.EXECUTE@&DBLink(c);
LOOP
IF DBMS_SQL.FETCH_ROWS@&DBLink(c) > 0
THEN
DBMS_SQL.COLUMN_VALUE@&DBLink(c, 1, output);
DBMS_OUTPUT.PUT_LINE(output);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR@&DBLink(c);
END;
/
試しに18cでGATHER_PLAN_STATISTICSヒント付きのコードを実行してヒントがリモートにプッシュされているか確認します。
SELECT version FROM v$instance;
VERSION
-----------------
18.0.0.0.0
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM global_name@testdb;
GLOBAL_NAME
-----------
TESTDB
@lastplanlink testdb
SQL_ID abmpaqgna09j2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ "A1"."GLOBAL_NAME" FROM "GLOBAL_NAME" "A1"
Plan hash value: 415205717
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
|* 1 | TABLE ACCESS FULL| PROPS$ | 1 | 1 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------
（略）
おまけ
自分のセッションでオープンしているすべてのリモートセッションの詳細を一度に得るクエリです。DBリンクはオブジェクトなのでダイナミックSQLを使用しなければなりません。以前投稿した「ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方」が役に立ちました！
SELECT * FROM (
SELECT db_link, logged_on, open_cursors, in_transaction, update_sent FROM v$dblink),
XMLTABLE ('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE (
'SELECT instance_name, sid, serial# serial, host_name remote, machine local FROM '
|| 'v$session@' || db_link || ','
|| 'v$instance@' || db_link || ','
|| '(SELECT sid mysid FROM v$mystat@' || db_link || ' WHERE ROWNUM = 1) '
|| 'WHERE sid = mysid')
COLUMNS
instance_name VARCHAR2(16),
sid NUMBER,
serial NUMBER,
remote VARCHAR2(64),
local VARCHAR2(64));
DB_LINK LOG OPEN_CURSORS IN_ UPD INSTANCE_NAME SID SERIAL REMOTE LOCAL
--------------- --- ------------ --- --- ------------- ---------- ---------- ----------- -----------------
TESTDV YES 0 YES NO TESTDV1 1095 38854 server30 server50
TESTDB YES 0 YES NO TESTDB 274 19662 server40 server50
おわりに
DBリンク周りについて記事を書いてみました。ここではチューニングそのものには触れてませんが、DBリンクが絡むクエリパフォーマンスの問題は大概が駆動データベースかまたはリモートテーブルに対してのNESTED LOOPS関係なので、まぁDRIVING_SITEかUSE_HASHヒントあたりでなんとかなります。たぶん(^^)。
以上です。