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

## はじめに

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

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

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

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

```sql:オープン中のDBリンク
SELECT * FROM global_name@testdb;    -- リモートセッションを作る

GLOBAL_NAME
-----------
TESTDB

SELECT db_link FROM v$dblink;        -- リモートセッションの存在確認

DB_LINK
---------------
TESTDB
```

``ALTER SESSION``でクローズしてみますがエラーになります。分散トランザクションがオーブンされているので、コミットかロールバック後クローズします。

```sql:
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``から取得します。

```sql:リモートセッションの情報を得る（権限あり）
SELECT sid FROM v$mystat@testdb WHERE ROWNUM = 1;

       SID
----------
       247
```

他のダイナミックパフォーマンスビューと組み合わせれば、インスタンスや``SERIAL#``など一緒にが取得できます。

```sql:
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リンク経由では以下の様にうまく動いてくれません。

```sql:
SELECT USERENV('SID') sid FROM DUAL@testdb;

       SID
----------
      4603    <--- リモートセッションでなくローカルセッションのSID

SELECT USERENV@testdb('SID') sid FROM DUAL;

ORA-00904: "USERENV": invalid identifier
```

ひとつの解決策としてDBリンク先のリモート側にストアードプログラムを作っておく方法もあります。まぁそれが許される環境もすくないでしょうが。

```sql:セッション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が利用できます。以下の様なスクリプトを作っておけばいつでも簡単にリモートセッションの情報が得られます。

```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;
/
```

```sql:
@dblinkmyid testdb

INSTANCE NAME                       USERNAME                           SESSION ID
---------------------------------------------------------------------------------
TESTDB                              TESTUSR                            274
```

### 確実ではないが権限がなくても使えるもう一つの方法

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

```sql: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リンクが判明している場合のみ使えます。

```sql:
-- ローカルデータベースにログインし、ターゲットセッションの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
```

```sql:
-- リモートデータベースにログインし（またはローカルデータベースから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はプッシュされるようになった様です！**）。

```sql:
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スクリプトにに組み込んでしまえば、セッション情報を取りつつ実行計画統計を有効にできますね。

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

```sql: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ヒント付きのコードを実行してヒントがリモートにプッシュされているか確認します。

```sql:
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 ステートメントの作り方](https://qiita.com/tlokweng/items/ead23e3ebd194f0fe851)」が役に立ちました！

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

以上です。