Remote SQL

数日前にデータベースリンクを使う分散クエリ(Distributed Query)の動作原理に対する質問を受けました。それに対する答えを簡単なテストでします。

テスト環境はOracle 11.1.0.6です。

TPACK@ukja1106> -- version
TPACK@ukja1106> select * from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

分散クエリのためにループバックデータベースリンクを作ります。ループバックデータベースリンクとは自己自身がリモートデータベースとなるデータベースリンクとことを意味します。

TPACK@ukja1106> -- create loopback database link
TPACK@ukja1106> create public database link loopback
  2  connect to {user}
  3  identified by {password}
  4  using '{service_name}';

Database link created.

テーブルT1、T2を作ります。

TPACK@ukja1106> -- create table
TPACK@ukja1106> create table t1(c1, c2)
  2  as
  3  select level, level
  4  from dual
  5  connect by level <= 1000
  6  ;

Table created.

TPACK@ukja1106> 
TPACK@ukja1106> create table t2(c1, c2)
  2  as
  3  select level, level
  4  from dual
  5  connect by level <= 1000
  6  ;

Table created.

TPACK@ukja1106> 
TPACK@ukja1106> create index t2_n1 on t2(c1);

Index created.

TPACK@ukja1106> 
TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.

テーブルT1(ローカル)が先行テーブルとなり、テーブルT2(リモート)が結合対象となる分散くえりの実行計画です。Remote SQL Informationという部分に注意してください。

TPACK@ukja1106> -- explain plan
TPACK@ukja1106> -- nested loops join
TPACK@ukja1106> 
TPACK@ukja1106> -- execute it, but 0 row
TPACK@ukja1106> explain plan for
  2  select /*+ leading(t1) use_nl(d) */
  3   *
  4  from t1, t2@loopback d
  5  where t1.c1 = d.c1
  6   and t1.c1 < 0
  7  ;

TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|   1 |  NESTED LOOPS      |      |        |      |
|*  2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   | LOOPB~ | R->S |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."C1"<0)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"
       (accessing 'LOOPBACK' )

Remote SQLとは分散くえりを実行する時、リモートデータベースからデータを伝送させられうためにリモートデータベースで実行するSQLのことです。すなわち、ローカルデータベースはRemote SQLをリモートデータベースに発行してほしいデータを受け付けます。

ローカルデータベースはリモートデータベースからリモートテーブルとインデックスの基本統計情報を伝送されます。その情報を利用して実行計画を作ります。この過程でRemote SQLを作ります。Remote SQLは実際にデータを伝送されるために実行するまではリモートデータベースに送りません。

これを証明するために、クエリを実行した後、リモートデータベースでRemote SQLが実行されたかどうか確認してみます。次のクエリを実行したら、先行テーブルから一件の行もでないので（t1.c1 < 0 の条件のため）リモートデータベースへのデータリクエストもないはずです。つぎの結果を見ると、この推測があたることが分かります。

TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */
  2   *
  3  from t1, t2@loopback d
  4  where t1.c1 = d.c1
  5   and t1.c1 < 0
  6  ;

no rows selected

TPACK@ukja1106> 
TPACK@ukja1106> select sql_id, executions
  2  from v$sqlarea
  3  where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"';

no rows selected

今度は同一なテストを1,000回のデータリクエストをリモートデータベースへ送る分散クエリに対して修行してみます。

TPACK@ukja1106> -- execute it, for 1000 rows
TPACK@ukja1106> explain plan for
  2  select /*+ leading(t1) use_nl(d) */
  3   *
  4  from t1, t2@loopback d
  5  where t1.c1 = d.c1
  6  ;

Explained.

TPACK@ukja1106> 
TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|   1 |  NESTED LOOPS      |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   | LOOPB~ | R->S |
---------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1" (accessing
       'LOOPBACK' )

1,000回のデータリクエストを送った結果、Remote SQLがリモートデータベースで1,000回修行されました。これはまるでSQL*Plusで該当くえりを1,000回修行したのは同一です。

TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */
  2   *
  3  from t1, t2@loopback d
  4  where t1.c1 = d.c1
  5  ;

        C1         C2         C1         C2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
...
       999        999        999        999
      1000       1000       1000       1000

1000 rows selected.

TPACK@ukja1106> 
TPACK@ukja1106> col sql_id new_value sql_id
TPACK@ukja1106> select sql_id, executions
  2  from v$sqlarea
  3  where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1"';

SQL_ID        EXECUTIONS
------------- ----------
6skxmvb24s6v4       1000

DBMS_XPLAN.DISPLAY_CURSOR関数を利用したら、リモートデータベースでのRemote SQLの実行計画も分かります。次のようにIndex Range Scanが選択されました。

TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=:1)

今度はNested Loops JoinではなくてHash Joinに対して同一なテストを修行してみます。Remote SQLは次のようです。

TPACK@ukja1106> explain plan for
  2  select /*+ leading(t1) use_hash(d) */
  3   *
  4  from t1, t2@loopback d
  5  where t1.c1 = d.c1
  6  ;

Explained.

TPACK@ukja1106> 
TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   | LOOPB~ | R->S |
---------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="D"."C1")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D" (accessing 'LOOPBACK' )

たとえ1,000件をフェッチするのは同様ですが、Hash Joinの特性上リモートデータベースへRemote SQLを1,000回送ることではなく、ただ一回の実行でほしいデータを受け付けることができます。したがって実行回数(EXECUTIONS)は”１”となっています。

TPACK@ukja1106> select /*+ leading(t1) use_hash(d) */
  2   *
  3  from t1, t2@loopback d
  4  where t1.c1 = d.c1
  5  ;

        C1         C2         C1         C2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
...
       999        999        999        999
      1000       1000       1000       1000

1000 rows selected.

TPACK@ukja1106> col sql_id new_value sql_id
TPACK@ukja1106> select sql_id, executions
  2  from v$sqlarea
  3  where sql_text = 'SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"';

SQL_ID        EXECUTIONS
------------- ----------
0uksumbhuswyx          1

1 row selected.

そしてRemote SQLはTable Full Scanの実行計画を持ちます。

TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));

SQL_ID  0uksumbhuswyx, child number 0
-------------------------------------
SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| T2   |  1000 |  7000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

上の一連のテスト結果を見ると次のような結論が下せます。

• 分散くえりの動作方式はローカルクエリの動作方式とほとんど同一。
  
• リモートデータベースからデータを伝送されるためRemote SQLをリモートデータベースの送る。
  

これから、分散クエリをテストする時、上のような方法の分析が役に立つことをお願いします。