Mac De Oracle Heterogeneous! #68 Tweet

前回のつづき。Generic Connectivyty経由でMySQL4.0.25の文字型にアクセス。五回目。

前回のおさらい。
リモートソートが行われる場合と、そうでない場合があるということであった。
異機種間接続サービスにおいて、コストベースオプティマイザが行う最適化に、リモートソートの排除という記述があるのだが、なにもせず常にそれが行われるということでないようだ。

下記、クエリでは、DML全体がリモートで実行されているため、ソートもリモートソートとなり　MySQLのbinary属性のないvarchar型のソート結果（つまり、NULLや空文字が昇順で先頭になり、英字大文字小文字を区別しない）となっている。　

CORYDORAS> l
  1  select
  2    "id",
  3    "r_varchar_20"
  4  from
  5    varchar_test_mysql4025_mac@oracle10g_win
  6  where
  7    "id" between 1 and 18
  8  order by
  9*   "r_varchar_20"
CORYDORAS> /

        id r_varchar_20
---------- ----------------------------------------
        17
        18
         5 A
         6 a
         3 B
         4 b
         1 C
         2 c
        16 ぁ
        11 あ
        15 ぃ
        10 い
        14 ぅ
         9 う
        13 ぇ
         8 え
        12 ぉ
         7 お

18行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT (REMOTE) Optimizer=ALL_ROWS (Cost=53 Card=5 Bytes=175)
   1    0   SORT (ORDER BY) (Cost=53 Card=5 Bytes=175)
   2    1     REMOTE* (Cost=52 Card=5 Bytes=175)                       MYSQL4025_MAC

   2 SERIAL_FROM_REMOTE            SELECT "id","r_varchar_20" FROM "varchar_tes
                                   t" WHERE "id">=1 AND "id"<=18

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        755  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

ということで、コストベースオプティマイザの動きを制御できないか？　といろいろ調べるた結果、下記の書き換えと、ヒントを利用することにした。
ヒントを利用しなくとも、利用する関数によっては　Oracle（ローカル）側でソートするようになるのだが、これも状況次第なので、ご自分の環境で十分なテストを行うことをお勧めする。

まず、該当部分だけをインラインビューに書き換える。（これだけでは全てリモートマップされてしまう）

CORYDORAS> l
  1  select *
  2  from
  3  (
  4    select
  5      "id",
  6      "r_varchar_20"
  7    from
  8      varchar_test_mysql4025_mac@oracle10g_win
  9    where
 10      "id" between 1 and 18
 11  )
 12  order by
 13*   "r_varchar_20"
CORYDORAS> /

        id r_varchar_20
---------- ----------------------------------------
        17
        18
         5 A
         6 a
         3 B
         4 b
         1 C
         2 c
        16 ぁ
        11 あ
        15 ぃ
        10 い
        14 ぅ
         9 う
        13 ぇ
         8 え
        12 ぉ
         7 お

18行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT (REMOTE) Optimizer=ALL_ROWS (Cost=53 Card=5 Bytes=175)
   1    0   SORT (ORDER BY) (Cost=53 Card=5 Bytes=175)
   2    1     REMOTE* (Cost=52 Card=5 Bytes=175)                       MYSQL4025_MAC

   2 SERIAL_FROM_REMOTE            SELECT "id","r_varchar_20" FROM "varchar_tes
                                   t" WHERE "id">=1 AND "id"<=18

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        755  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

CORYDORAS> 

次に、異機種間接続機能向けに使えそうなヒントは　NO_MERGEヒント、DRIVING_SITEヒント、REMOTE_MAPPEDヒントのようなのだが、REMOTE_MAPPEDヒントは明文化されていないのとクエリをリモートサイトで実行させるためと思われる名称のヒントであるため対象外とした。

まずは、NO_MERGEヒントから試してみた、

CORYDORAS> set autot on
CORYDORAS> l
  1  select *
  2  from
  3  (
  4    select /*+ NO_MERGE */
  5      "id",
  6      "r_varchar_20"
  7    from
  8      varchar_test_mysql4025_mac@oracle10g_win
  9    where
 10      "id" between 1 and 18
 11  )
 12  order by
 13*   "r_varchar_20"
CORYDORAS> /

        id r_varchar_20
---------- ----------------------------------------
         5 A
         3 B
         1 C
         6 a
         4 b
         2 c
        16 ぁ
        11 あ
        15 ぃ
        10 い
        14 ぅ
         9 う
        13 ぇ
         8 え
        12 ぉ
         7 お
        17
        18

18行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=5 Bytes=175)
   1    0   SORT (ORDER BY) (Cost=53 Card=5 Bytes=175)
   2    1     VIEW (Cost=52 Card=5 Bytes=175)
   3    2       REMOTE* (Cost=52 Card=5 Bytes=175)                     ORACLE10G_WIN

   3 SERIAL_FROM_REMOTE            SELECT "id","r_varchar_20" FROM "VARCHAR_TES
                                   T_MYSQL4025_MAC" "VARCHAR_TEST_MYSQL

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        812  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         18  rows processed

CORYDORAS> 

　
思い通りのソート結果を得られた。自動トレースの実行計画、統計情報からソートがローカルデータベース側（つまり、Oracle側）で実行されているという状況を確認できた。

NO_MERGEヒントで目的の結果を得られたので、DRIVING_SITEヒントの確認は省略。
NO_MERGEヒントを利用したクエリとそうでないクエリの実行計画を　explain plan文と、utlxpls.sqlスクリプトでさらに確認した結果は以下。

CORYDORAS> l
  1  explain plan for
  2  select *
  3  from
  4  (
  5    select
  6      "id",
  7      "r_varchar_20"
  8    from
  9      varchar_test_mysql4025_mac@oracle10g_win
 10    where
 11      "id" between 1 and 18
 12  )
 13  order by
 14*   "r_varchar_20"
CORYDORAS> /

解析されました。

CORYDORAS> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4028966153

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     5 |   175 |    53   (2)| 00:00:01 |
|   1 |  SORT ORDER BY         |      |     5 |   175 |    53   (2)| 00:00:01 |
|   2 |   REMOTE               |      |     5 |   175 |    52   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - fully remote statement

13行が選択されました。

CORYDORAS> select id,other from plan_table where operation='REMOTE';

        ID OTHER
---------- --------------------------------------------------------------------------------
         2 SELECT "id","r_varchar_20" FROM "varchar_test" WHERE "id">=1 AND "id"<=18

CORYDORAS> 

以下は、NO_MERGEヒントを利用した結果。

CORYDORAS> l
  1  explain plan for
  2  select *
  3  from
  4  (
  5    select /*+ NO_MERGE */
  6      "id",
  7      "r_varchar_20"
  8    from
  9      varchar_test_mysql4025_mac@oracle10g_win
 10    where
 11      "id" between 1 and 18
 12  )
 13  order by
 14*   "r_varchar_20"
CORYDORAS> /

解析されました。

CORYDORAS> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4108374534

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     5 |   175 |    53   (2)| 00:00:01 |
|   1 |  SORT ORDER BY   |      |     5 |   175 |    53   (2)| 00:00:01 |
|   2 |   VIEW           |      |     5 |   175 |    52   (0)| 00:00:01 |
|   3 |    REMOTE        |      |     5 |   175 |    52   (0)| 00:00:01 |
-------------------------------------------------------------------------

10行が選択されました。

CORYDORAS> select id,other from plan_table where operation='REMOTE';

        ID　OTHER
----------　--------------------------------------------------------------------------------
         3　SELECT "id","r_varchar_20" FROM "VARCHAR_TEST_MYSQL4025_MAC" "VARCHAR_TEST_MYSQL
　　　　　　　4025_MAC" WHERE "id">=1 AND "id"<=18

CORYDORAS> 

さらに、プランスタビリティ機能を利用すればアクセスプランを固定化できるはずである。
今日はここまで、次回へつづく。