LoginSignup
9
10

More than 5 years have passed since last update.

@tlokweng

DBリンク付きのSQLをチューニングするときのあれこれ

Posted at

はじめに

オラクルはDBリンク付きのSQLを実行する時、必要に応じてクエリから一部分のみ抜き出してリモートデータベースで実行したり、またはクエリ全体をリモートデータベースに丸投げしたりします。したがってクエリのチューニングをしているときなどにリモートで実行されたクエリの実行計画や統計情報を確認することが時として重要となります。そこでここでは、ローカルセッションからクエリのDBリンクで使用されたリモートデータベース上のリモートセッションを簡単に特定する方法や、リモートセッションの実行統計を有効化しローカルセッションから実行統計付きの実行計画を取得する方法などについて考察してみます。

DBリンクとリモートセッションの挙動

まず「リモートセッションはいつ作られていつ終了するのか？」についてです。リモートセッションは、ローカルセッションで最初にDBリンクを使用した時点で自動で作られ、ローカルセッションがクローズされるまで保持されます。クエリを実行する度にオープン・クローズされるわけではありません。ローカルセッション内でDBリンク再使用すると、すでに存在するリモートセッションが再利用されるわけです。またリモートセッションはALTER SESSIONやDBMS_SESSIONで意図的にクローズすることもできます。ひとつのセッションで一度にオープンしておけるリモートセッションの数は初期パラメータopen_linksで決まっているので、これの上限を超える場合は不要なリモートセッションをクローズする必要があります。ちなみにデフォルトは４です。

試してみます。クエリでDBリンクを使用してリモートセッションを作成します。カレントセッションでオープンされているDBリンクはv$dblinkで取得できます（自分のセッションのみです。他セッションの情報は参照できません）。しかし残念ながらv$dblinkにはリモートセッションを特定できる情報はありません。

オープン中のDBリンク
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リンク先のリモート側にストアードプログラムを作っておく方法もあります。まぁそれが許される環境もすくないでしょうが。

セッションIDを返すファンクション
-- リモートデータベース側でファンクションを作成

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が取得できなくもないです。しかし、マニュアルにはユニーク番号を返すとしか書かれてないため、常に正しいセッション情報を取得できるかどうかは不明です。まぁ、チューニング用のスクリプトとして使用する分には特に問題はないでしょう。正しく所得できない場合は、他の方法を使用すればよいだけなので。

DBMS_SESSION.UNIQUE_SESSION_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リンク経由では直接実行できません。

lastplanlink.sql
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ヒントあたりでなんとかなります。たぶん(^^)。

以上です。

9
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
10