robios::blog

前述した、Stored OutlineのHackを実践してみる。

まずは実験用テーブルを作る。

ora817@robios.org> create table hoge_transactions
  2  as
  3  select rownum as transaction_id, 'HOGE' as data,
  4  4 as status_code
  5  from all_objects
  6  where rownum <= 100000;

表が作成されました。

経過: 00:00:20.04
ora817@robios.org> create index hoge_transactions_n1
  2  on hoge_transactions (status_code);

索引が作成されました。

経過: 00:00:00.08
ora817@robios.org> update hoge_transactions
  2  set status_code = 1
  3  where transaction_id between 99000 and 99099;

100行が更新されました。

経過: 00:00:00.01
ora817@robios.org> commit;

コミットが完了しました。

経過: 00:00:00.01
ora817@robios.org> analyze table hoge_transactions
  2  compute statistics
  3  for table
  4  for all indexes
  5  for all indexed columns;

表が分析されました。

経過: 00:00:02.02

これで、Cardinalityが非常に低いコラムを索引対象したテーブルが作成された。ヒストグラムが作成されているため、STATUS_CODE = 1で検索すると、索引検索が行われる。

ora817@robios.org> select *
  2  from hoge_transactions
  3  where status_code = 1;

TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
         99000 HOGE           1
         99001 HOGE           1
         99002 HOGE           1

- 途中省略 -

         99097 HOGE           1
         99098 HOGE           1
         99099 HOGE           1

100行が選択されました。

経過: 00:00:03.02

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=160
          0)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HOGE_TRANSACTIONS' (Cost
          =2 Card=100 Bytes=1600)

   2    1     INDEX (RANGE SCAN) OF 'HOGE_TRANSACTIONS_N1' (NON-UNIQUE
          ) (Cost=1 Card=100)





統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          5  physical reads
          0  redo size
       2759  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

しかし、バインド変数を使うと、索引は使用されなくなる。(→理由についてはCBOにおける索引の使用判断 - コストの計算例を参照)

ora817@robios.org> variable status number;
ora817@robios.org> exec :status := 1;

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.00
ora817@robios.org> select *
  2  from hoge_transactions
  3  where status_code = :status;

TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
         99000 HOGE           1
         99001 HOGE           1
         99002 HOGE           1

- 途中省略 -

         99097 HOGE           1
         99098 HOGE           1
         99099 HOGE           1

100行が選択されました。

経過: 00:00:03.02

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=50000 Bytes=
          800000)

   1    0   TABLE ACCESS (FULL) OF 'HOGE_TRANSACTIONS' (Cost=39 Card=5
          0000 Bytes=800000)





統計
----------------------------------------------------------
          0  recursive calls
         12  db block gets
        257  consistent gets
        247  physical reads
          0  redo size
       2759  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

さて、このようなSQLがOracle ApplicationのProcedureの中にあり、手が出せないとする(実際にこのようなSQLはよくある)。先のOutline Hackでパフォーマンス改善を行う。

まずはProcedureを作る。

ora817@robios.org> create or replace procedure process_hoge_transactions is
  2    unprocessed_status constant number := 1;
  3  begin
  4    for cur in (select *
  5                from hoge_transactions
  6                where status_code = unprocessed_status)
  7    loop
  8      dbms_output.put_line(cur.transaction_id || ', ' || cur.data);
  9    end loop;
 10  end;
 11  /

プロシージャが作成されました。

経過: 00:00:00.01
ora817@robios.org> alter session set sql_trace=true;

セッションが変更されました。

経過: 00:00:00.01
ora817@robios.org> exec process_hoge_transactions;
99000, HOGE
99001, HOGE
99002, HOGE

- 途中省略 -

99097, HOGE
99098, HOGE
99099, HOGE

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.08
ora817@robios.org> alter session set sql_trace=false;

セッションが変更されました。

経過: 00:00:00.01

トレースの結果を示す。

********************************************************************************

SELECT *
FROM
 HOGE_TRANSACTIONS  WHERE STATUS_CODE = :b1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.06       0.07        247        350         12         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.06       0.07        247        350         12         100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 183     (recursive depth: 1)
********************************************************************************

まず、ターゲットSQLのOutlineを作る。CREATE_STORED_OUTLINESフラグを立てて、Procedureを実行する。

ora817@robios.org> alter session set create_stored_outlines=true;

セッションが変更されました。

経過: 00:00:00.00
ora817@robios.org> exec process_hoge_transactions;
99000, HOGE
99001, HOGE
99002, HOGE

- 途中省略 -

99097, HOGE
99098, HOGE
99099, HOGE

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.09
ora817@robios.org> alter session set create_stored_outlines=false;

セッションが変更されました。

経過: 00:00:00.00
ora817@robios.org> select ol_name, sql_text
  2  from outln.ol$;

OL_NAME                        SQL_TEXT
------------------------------ ----------------------------------------
SYS_OUTLINE_040729043824572    SELECT *   FROM HOGE_TRANSACTIONS
                               WHERE STATUS_CODE = :b1

経過: 00:00:00.02

次に、ターゲットSQLにヒントを埋め込み、索引が正しく使われるようにする。そしてそのSQLのOutlineを作成する。

ora817@robios.org> select /*+ RULE */ *
  2  from hoge_transactions
  3  where status_code = 1;

TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
         99000 HOGE           1
         99001 HOGE           1
         99002 HOGE           1

- 途中省略 -

         99097 HOGE           1
         99098 HOGE           1
         99099 HOGE           1

100行が選択されました。

経過: 00:00:03.03

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HOGE_TRANSACTIONS'
   2    1     INDEX (RANGE SCAN) OF 'HOGE_TRANSACTIONS_N1' (NON-UNIQUE
          )





統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          3  physical reads
          0  redo size
       2680  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

ora817@robios.org> create outline temp on
  2  select /*+ RULE */ *
  3  from hoge_transactions
  4  where status_code = 1;

アウトラインが作成されました。

経過: 00:00:00.01

先ほどのOutlineと、今作成したOutlineを入れ替える。OL$のHINTCOUNTも忘れずに(値は前もって確認)。

ora817@robios.org> update outln.ol$hints
  2  set ol_name = decode(ol_name, 'SYS_OUTLINE_040729043824572', 'TEMP',
  3                                'TEMP', 'SYS_OUTLINE_040729043824572')
  4  where ol_name in ('SYS_OUTLINE_040729043824572', 'TEMP');

13行が更新されました。

経過: 00:00:00.01
ora817@robios.org> update outln.ol$
  2  set hintcount = decode(ol_name, 'SYS_OUTLINE_040729043824572', 7,
  3                                  'TEMP', 6)
  4  where ol_name in ('SYS_OUTLINE_040729043824572', 'TEMP');

2行が更新されました。

経過: 00:00:00.01
ora817@robios.org> commit;

コミットが完了しました。

経過: 00:00:00.00

これで完了である。試しに、USE_STORED_OUTLINESフラグをオンにし、ヒント埋め込みバージョンを実行してみる。うまくいっていれば、全スキャンが行われるはずである。

ora817@robios.org> alter session set use_stored_outlines=true;

セッションが変更されました。

経過: 00:00:00.00
ora817@robios.org> select /*+ RULE */ *
  2  from hoge_transactions
  3  where status_code = 1;

TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
         99000 HOGE           1
         99001 HOGE           1
         99002 HOGE           1

- 途中省略 -

         99097 HOGE           1
         99098 HOGE           1
         99099 HOGE           1

100行が選択されました。

経過: 00:00:03.04

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=39 Card=100 Byte
          s=1600)

   1    0   TABLE ACCESS (FULL) OF 'HOGE_TRANSACTIONS' (Cost=39 Card=1
          00 Bytes=1600)





統計
----------------------------------------------------------
         48  recursive calls
         20  db block gets
        264  consistent gets
        252  physical reads
        600  redo size
       2727  bytes sent via SQL*Net to client
        292  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        100  rows processed

全スキャンが行われている。次に本題のProcedureを実行し、トレースを取ってみる。

********************************************************************************

SELECT *
FROM
 HOGE_TRANSACTIONS  WHERE STATUS_CODE = :b1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.01       0.00          3        202          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.01       0.01          3        202          0         100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 183     (recursive depth: 1)
********************************************************************************

索引が使用され、Disk IOが大幅に減ったのが分かっていただけると思う。

その他:

• PL/SQLで、定数として変数を宣言し、それを検索のキーとして使うのは避けるべきである。定数と宣言してもバインド変数となり、せっかくのヒストグラムが使用されない。上記Procedureで、STAUTS_CODE = 1と素直に書けば、ヒストグラムが使用され、索引検索が行われる。
• ヒント埋め込みバージョンSQLの作成は、今回の場合はヒストグラムがある為あえてRULEヒントを加える必要はない。
• 今回作成した表の、STATUS_CODEのようなCardinalityが非常に低い項目に索引を作成する場合、通常の索引ではなくビットマップ索引を作成すると効果的である。しかし、ビットマップ索引の場合、近いレコードへの同時更新が発生すると、ロックが発生するため注意が必要。
• USE_STORED_OUTLINESフラグを立てた場合、ハード解析時に、そのSQLのOutlineがOL$に定義されているかどうかを調べるため、OL$をHash Valueで検索する。そのため、多少のオーバーヘッドが生じることになる。オーバーヘッドはRecursive Callという形で現れる。このオーバーヘッドについての検証はいずれ。