前回のおさらい

前回は「オプティマイザ統計の保留」をシナリオに沿って動作を検証しました。

テーブルの統計情報公開属性を「FALSE」（=保留）に設定すれば、統計情報を取得したとしても現在の実行計画に影響を与えず、保留状態に置かれます。

そして、保留状態の統計情報を手動で公開すれば、次回実行計画を算出されるタイミングでその公開された統計情報が使用されて新しい実行計画が作成されることを確認しました。

この機能の概要は前々回の記事「オプティマイザ統計の保留」の検証（その１）で紹介しましたが、別のマニュアルにある記事も紹介しておきます。

Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス
11g リリース2(11.2)
B56262-06
保留中の統計情報

「保留中の統計情報」のポイント

• デフォルトでは収集された統計情報はディクショナリに格納される。
• テーブルのPUBLISHオプション（統計情報公開属性）を「FALSE」に設定すると、統計情報はディクショナリではなくシステムのプライベート・エリアに格納される。
• プライベート・エリアに格納された統計情報は原則的にはコストベース・オプティマイザ（CBO）で使用されない。
• パラメータOPTIMIZER_USE_PENDING_STATISTICSをセッション（またはシステム）レベルで「TRUE」に設定変更すると、保留中の統計情報が新しい実行計画に影響を与えるか否かの確認を行うことができる。

プライベート・エリアはどこにあるのか？

上のポイントに出てきた保留中の統計情報が格納されるプライベート・エリアとは一体どこなのでしょうか？
前回の検証で、保留中の統計情報はデータ・ディクショナリ・ビュー「XXX_TAB_PENDING_STATS」で参照することを紹介しました。
まずは、このビュー定義からどのような実表に情報が格納されるのかを見ていきましょう。

ビュー定義を確認する

ビュー定義はDBA_VIEWSのTEXT列を確認することで得られます。以下はDBA_TAB_PENDING_STATSのビュー定義です。

SQL> set long 2000000000
SQL> set pages 200
SQL> select TEXT from dba_views where VIEW_NAME = 'DBA_TAB_PENDING_STATS';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null, h.rowcnt, h.blkcnt, h.avgrln,
         h.samplesize, h.analyzetime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
    and  h.savtime > systimestamp
  union all
  -- partitions
  select u.name, o.name, o.subname, null, h.rowcnt, h.blkcnt,
         h.avgrln, h.samplesize, h.analyzetime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
    and  h.savtime > systimestamp
  union all
  -- sub partitions
  select u.name, osp.name, ocp.subname, osp.subname, h.rowcnt,
         h.blkcnt, h.avgrln, h.samplesize, h.analyzetime
  from  sys.user$ u,  sys.obj$ osp, obj$ ocp,  sys.tabsubpart$ tsp,
        sys.wri$_optstat_tab_history h
  where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj# and
        tsp.pobj# = ocp.obj# and osp.owner# = u.user#
    and h.savtime > systimestamp

このビューは３つの部分（テーブル、パーティション、サブパーティション）の問い合わせから成っていることがわかります。
今回の検証ではテーブルのみを対象とするので、１番最初のパートに着目します。
それから、ハイライト表示した11行目の「h.savtime > systimestamp」というWHERE条件はこの先重要になりますので覚えておいてください。

さて、保留中の統計情報はWRI$_OPTSTAT_TAB_HISTORY（所有者はSYSユーザ）という実表に格納されるようです。
このテーブルの構成を確認しておきましょう。

SQL> desc sys.wri$_optstat_tab_history
 名前               NULL?    型
 ----------------- -------- ----------------------------
 OBJ#              NOT NULL NUMBER
 SAVTIME                    TIMESTAMP(6) WITH TIME ZONE
 FLAGS                      NUMBER
 ROWCNT                     NUMBER
 BLKCNT                     NUMBER
 AVGRLN                     NUMBER
 SAMPLESIZE                 NUMBER
 ANALYZETIME                DATE
 CACHEDBLK                  NUMBER
 CACHEHIT                   NUMBER
 LOGICALREAD                NUMBER
 SPARE1                     NUMBER
 SPARE2                     NUMBER
 SPARE3                     NUMBER
 SPARE4                     VARCHAR2(1000)
 SPARE5                     VARCHAR2(1000)
 SPARE6                     TIMESTAMP(6) WITH TIME ZONE

またこのテーブルが格納されている表領域を確認しておきます。

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME
  2  from dba_tables
  3  where OWNER = 'SYS' and TABLE_NAME = 'WRI$_OPTSTAT_TAB_HISTORY';

OWNER  TABLE_NAME                     TABLESPACE_NAME
------ ------------------------------ ----------------
SYS    WRI$_OPTSTAT_TAB_HISTORY       SYSAUX

SYSAUX表領域であることがわかります。つまり「プライベート・エリア」というのはSYSAUX表領域にあるSYS.WRI$_OPTSTAT_TAB_HISTORY表のようです。

実は、このSYS.WRI$_OPTSTAT_TAB_HISTORYを元表とするビューは他にも存在します。
以下は、DBA_TAB_STATS_HISTORYビューの定義を確認したところです。

SQL> select TEXT from dba_views where VIEW_NAME = 'DBA_TAB_STATS_HISTORY';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null, h.savtime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
    and  h.savtime <= systimestamp  -- exclude pending statistics
  union all
  -- partitions
  select u.name, o.name, o.subname, null, h.savtime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
    and  h.savtime <= systimestamp  -- exclude pending statistics
  union all
  -- sub partitions
  select u.name, osp.name, ocp.subname, osp.subname, h.savtime
  from  sys.user$ u,  sys.obj$ osp, obj$ ocp,  sys.tabsubpart$ tsp,
        sys.wri$_optstat_tab_history h
  where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj#
    and tsp.pobj# = ocp.obj# and osp.owner# = u.user#
    and h.savtime <= systimestamp  -- exclude pending statistics
  union all
  -- fixed tables
  select 'SYS', t.kqftanam, null, null, h.savtime
  from  sys.x$kqfta t, sys.wri$_optstat_tab_history h
  where t.kqftaobj = h.obj#
    and h.savtime <= systimestamp  -- exclude pending statistics

驚いたことに、DBA_TAB_PENDING_STATSビューと定義がほぼ同じで、8行目のWHERE条件が正反対になっています。

実は
13.5.3 前のバージョンの統計のリストア
Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06

に出てくる統計情報の履歴管理と、統計情報の保留はほぼ同じ仕組みで実装されているようです。

そこで、以下の「保留中の統計情報」と「統計情報の履歴」を同時に確認できるSQL文を使って、統計情報取得に伴う情報の管理のされ方を追いかけて行きたいと思います。

showstat.sql

COLUMN TABLE_NAME FORMAT a11
COLUMN OWNER      FORMAT a6
select
  case when h.savtime >  systimestamp
        then 'PENDING'
       when h.savtime <= systimestamp
        then 'PUBLISH'
  end as        STAT
, u.name        OWNER
, o.name        TABLE_NAME
, h.rowcnt      NUM_ROWS
, h.blkcnt      BLOCKS
, h.avgrln      AVG_ROW_LEN
, h.samplesize  SAMPLE_SIZE
, to_char(h.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
, to_char(h.savtime,'yyyy-mm-dd hh24:mi:ss')     SAVE_TIME
From
  sys.user$                     u
, sys.obj$                      o
, sys.wri$_optstat_tab_history  h
where h.obj#    = o.obj#
and   o.type#   = 2
and   o.owner#  = u.user#
and   u.name    = 'TEST'
and   o.name    like 'TEST%'
;
select
  'CURRENT'     STAT
, u.name        OWNER
, o.name        TABLE_NAME
, t.rowcnt      NUM_ROWS
, t.blkcnt      BLOCKS
, t.avgrln      AVG_ROW_LEN
, t.samplesize  SAMPLE_SIZE
, to_char(t.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
from
  sys.user$ u
, sys.tab$  t
, sys.obj$  o
where o.owner#  = u.user#
and   o.obj#    = t.obj#
and   u.name    = 'TEST'
and   o.name    like 'TEST%'
/

解説：
4-8行目のCASE式により、h.savtime列の値が現在日時に対して大小関係がどうなっているかで表示を変えています。
また27行目以降は、現在オプティマイザで使用されているディクショナリ（SYS.TAB$）に格納された統計情報の状況を確認するための２番目のクエリーです。

ちなみにSYS.TAB$は、以下のとおりSYSTEM表領域に格納されるディクショナリ表です。

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME
  2  from dba_tables
  3  where OWNER = 'SYS' and TABLE_NAME = 'TAB$';

OWNER  TABLE_NAME  TABLESPACE_NAME
------ ----------- ----------------
SYS    TAB$        SYSTEM

長くなったので、検証結果は次回に紹介します。

続く

検証開始！

前回示した検証シナリオに沿って、「オプティマイザ統計の保留」の基本的な機能を確認していきます。

1. 検証準備（スキーマの作成）

ここでは検証用スキーマとして「TEST」を作成し、必要な権限を与えます。特にデータ・ディクショナリを確認できるように「SELECT ANY DICTIONARY」権限を与えておきます。

SQL> create user test identified by "test" default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> grant select any dictionary to test;

Grant succeeded.

SQL> grant plustrace to test;

Grant succeeded.

2. テーブル作成、データ作成、インデックス作成および統計情報の取得

簡単な構造の検証用テーブル「TEST」を作成、データを1,000件インサートし、さらにインデックスを作成します。
その後、統計情報を取得します。

SQL> conn test/test
Connected.

SQL> create table test(col1 number,col2 varchar2(20));

Table created.

SQL> desc test
 Name                  Null?    Type
 --------------------- -------- ------------------
 COL1                           NUMBER
 COL2                           VARCHAR2(20)

SQL> begin
  2 for i in 1..1000 loop
  3   insert into test values (i,'TEST DATA');
  4   commit;
  5 end loop;
  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
      1000

SQL> create index test_idx on test (col1);

Index created.

SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
  3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows from user_tables
  2  where table_name = 'TEST';

LAST_ANA   NUM_ROWS
-------- ----------
20:44:35       1000

3. 実行計画の確認

それではこの状態で、以下の問い合わせを実行してみましょう。（この後同じ問い合わせを実行し、実行計画がどのようになるかを確認します。）

SQL> set autot traceonly
SQL> set lines 200
SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL1">999)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        625  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

「INDEX RANGE SCAN」が選択され、1件のデータを取得していることがわかります。

4. テーブルの統計情報公開属性を「FALSE=保留」に設定変更する。

デフォルトではテーブルの統計情報公開属性は「TRUE=公開」になっています。
以下のプロシージャにより属性を「FALSE=保留」に設定変更します。
（テーブルの属性を変更しますが「ALTER TABLE」文を使用しないことに注意してください。）

SQL> begin
  2   DBMS_STATS.SET_TABLE_PREFS('TEST','TEST','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> col prefs for a10
SQL> select DBMS_STATS.GET_PREFS('PUBLISH','TEST','TEST') prefs from dual;

PREFS
----------
FALSE

5. テーブルをTRUNCATE後、データを50,000件インサートする。

SQL> truncate table test;

Table truncated.

SQL> begin
  2  for i in 1..50000 loop
  3   insert into test values (i,'TEST DATA');
  4   commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
     50000

6. 統計情報を取得し、内容を確認する。

デフォルトではテーブルをTRUNCATEした時点で、統計情報も削除されます。その後データを50,000件インサートしても統計情報を再取得しない限り統計情報は「なし」のままです。
「オプティマイザ統計の保留」が有効になっている状態で、統計情報がどうなるかを確認しましょう。

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows from user_tables
  2  where table_name = 'TEST';

LAST_ANA   NUM_ROWS
-------- ----------
20:44:35       1000

非常に興味深い結果となりましたが、TRUNCATEする前の統計情報がそのまま「保留」されていることがわかります。
TRUNCATEではデータを削除するのではなく、同じ構造で空のテーブルを再作成するような内部動作が行われます（オブジェクトIDが変更される）が、テーブルに紐付く（公開されている）統計情報はテーブル定義とは別の場所に保持されていることがわかります。
しかも、統計情報を再取得してもこの保留されている統計情報を上書きすることはありません。

7. 保留されている統計情報および公開情報との違いを確認する。

「オプティマイザ統計の保留」が有効になっているテーブルは「USER_TAB_PENDHING_STATS」ビューによって保留中の統計情報を確認することができます。
さらに、「DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING」ファンクションで、公開中と保留中の統計情報を比較することができます。

SQL> col table_name for a12
SQL> select table_name, to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows
  2  from user_tab_pending_stats where table_name = 'TEST';

TABLE_NAME   LAST_ANA   NUM_ROWS
------------ -------- ----------
TEST         20:49:44      50000

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('TEST','TEST'));

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : TEST
OWNER         : TEST
SOURCE A      : Current Statistics in dictionary (注：公開中の統計情報）
SOURCE B      : Pending Statistics               (注：保留中の統計情報）
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

TEST                        T   A   1000       5          14         1000
                                B   50000      244        15         50000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
                    (=Number of Distinct Value)
...............................................................................

COL1            A   1000    .001       NO   0       4    C102  C20B  1000
                B   50000   .00002     NO   0       5    C102  C306  50000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX:TEST_IDX
                                 ...............

TEST_IDX        I   A   1000    3       1000    1     1     3       1   1000
                    B   50000   99      50000   1     1     137     NUL 50000
###############################################################################

SQL> set head on

7. 同じ問い合わせを実行し、実行計画を確認する。

データ件数が50倍になったところで、同じ問い合わせを実行し実行計画を確認します。

SQL> set autot traceonly
SQL> set timing on
SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("COL1">999)

Statistics
----------------------------------------------------------
         26  recursive calls
          0  db block gets
       6791  consistent gets
          0  physical reads
          0  redo size
    1578657  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      49001  rows processed

50,000件のうち49,001件を取得する問い合わせなので、理想的には「TABLE ACCESS FULL」が選択されるべきですが、統計情報上は1,000件のテーブルなので最初の問い合わせ時と同じ「INDEX RANGE SCAN」が選択されています。
取得された49,001個のrowidに対してそれぞれ「TABLE ACCESS BY INDEX ROWID」が実行されるため、あまり効率的ではないI/Oが発生してしまいます。

8. 保留中の統計情報を使って同じ問い合わせを実行し、実行計画を確認する。

次に、今接続中のこのセッションのみ保留中の統計情報を使うように設定（「OPTIMIZER_USE_PENDING_STATISTICS」初期化パラメータ）を変更し、再度同じ問い合わせを実行してみます。

SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

Session altered.

SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
        592  recursive calls
          0  db block gets
       4413  consistent gets
          0  physical reads
        124  redo size
    1078861  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
        160  sorts (memory)
          0  sorts (disk)
      49001  rows processed

今度は「TABLE ACCESS FULL」が選択されていることがわかります。

9. 再接続しセッション情報をクリアした後に同じ問い合わせを実行する。

SQL> conn test/test
Connected.
SQL> show parameter OPTIMIZER_USE_PENDING_STATISTICS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL> set autot traceonly
SQL> set timing on
SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6743  consistent gets
          0  physical reads
          0  redo size
    1578657  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49001  rows processed

CONNECTコマンドで再接続すると、「ALTER SESSION」文で変更した「OPTIMIZER_USE_PENDING_STATISTICS」初期化パラメータの値がデフォルトの「FALSE」に戻ります。
その状態で同じ問い合わせを実行すると、非効率な「INDEX RANGE SCAN」を使用した実行計画に戻ってしまうことがわかります。

10. 保留中の統計情報を公開した後に同じ問い合わせを実行する。

今度は保留中の統計情報をDBMS_STATS.PUBLISH_PENDING_STATSプロシージャによって永続的に公開し、再度同じ問い合わせを実行して実行計画を確認します。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('TEST','TEST');

PL/SQL procedure successfully completed.

SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6743  consistent gets
          0  physical reads
          0  redo size
    1578657  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49001  rows processed

SQL> set autot off

永続的に公開された統計情報を使った実行計画なので、他のセッションから同じ問い合わせが実行されてもこの実行計画が選択されます。

11. レコードを削除し件数を1,000件とした後に同じ問い合わせを実行する。

次に50,000件のレコードから49,000件を削除（DELETE）し、同じ問い合わせを実行して実行計画を確認します。

SQL> delete from test where col1 > 1000;

49000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
      1000

SQL> set autot traceonly
SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        101  consistent gets
          0  physical reads
          0  redo size
        625  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

統計情報上はこのテーブルはまだ50,000件のデータがあるとみなされているため、同じように「TABLE ACCESS FULL」が選択されています。
（蛇足ですが、実行計画ツリーでRowsが「49002」となっていることが興味深いです。）

12. 統計情報を再取得し、公開済みと保留中の統計情報を比較する。

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows from user_tables
  2  where table_name = 'TEST';

LAST_ANA   NUM_ROWS
-------- ----------
20:49:44      50000

SQL> select table_name, to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows
  2  from user_tab_pending_stats where table_name = 'TEST';

TABLE_NAME   LAST_ANA   NUM_ROWS
------------ -------- ----------
TEST         21:18:42       1000

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('TEST','TEST'));

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : TEST
OWNER         : TEST
SOURCE A      : Current Statistics in dictionary
SOURCE B      : Pending Statistics
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

TEST                        T   A   50000      244        15         50000
                                B   1000       244        14         1000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

COL1            A   50000   .00002     NO   0       5    C102  C306  50000
                B   1000    .001       NO   0       4    C102  C20B  1000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX:TEST_IDX
                                 ...............

TEST_IDX        I   A   50000   99      50000   1     1     137     1   50000
                    B   1000    2       1000    1     1     3       NUL 1000
###############################################################################

SQL> set head on

DELETEの結果レコード件数が50,000件から1,000件になっても、ブロック数が「244」のままとなっていることに注目してください。
つまり、「TABLE ACCESS FULL」によって「244」ブロックもの無駄なI/Oが発生することになります。

13. テーブルの統計情報公開属性を「TRUE=公開」に設定変更し、同じ問い合わせを実行する。

SQL> begin
  2   DBMS_STATS.SET_TABLE_PREFS('TEST','TEST','PUBLISH','TRUE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select DBMS_STATS.GET_PREFS('PUBLISH','TEST','TEST') prefs from dual;

PREFS
----------
TRUE

SQL> set autot traceonly
SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
         19  recursive calls
          0  db block gets
        234  consistent gets
          0  physical reads
          0  redo size
        621  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

テーブルの統計情報公開属性を「TRUE=公開」に設定変更したとしても、保留中の統計情報は即時に公開されるわけではないことに注目してください。

14. 保留中の統計情報を公開し、同じ問い合わせを実行する。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('TEST','TEST');

PL/SQL procedure successfully completed.

SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        191  consistent gets
          0  physical reads
          0  redo size
        621  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

保留中の統計情報は公開して初めて実行計画の算出に使われるようになります。
テーブル件数に応じた適切な実行計画が選択されました。

今回はマニュアルに記載のある基本的な動作について確認しました。
次回は保留中の統計情報がどのように保持されているのかを詳しく見てみることにします。

続く

「オプティマイザ統計の保留」とは？

今回から「オプティマイザ統計の保留」というOracle 11g以降で使えるようになった機能を紹介・検証していきます。

以下はマニュアルへのリンクです。
13.5.1 統計の保留
Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06

機能概要

• 統計情報は収集後即時に公開（PUBLISH）される。（デフォルトの動作）
• 公開された統計はXXXX_TAB_STATISTICSやXXXX_IND_STATISTICSデータ・ディクショナリ・ビューに格納される。
• オプティマイザは原則的にデータ・ディクショナリ・ビューに格納されている公開済の統計を使用する。
• 従って即時に公開された新しい統計情報は実行計画に影響を与える可能性がある。
• 新しい統計を「保留中」として保存することができる。（統計の保留）
• PUBLISH設定は、スキーマまたはテーブル・レベルで変更することができる。
• 保留中の統計は、XXXX_TAB_PENDING_STATSやXXXX_IND_PENDING_STATSビューに格納される。
• 保留された統計情報は実行計画に反映されない。
• OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをTRUEに設定（セッション単位で変更可）すると、保留中の統計情報を実行計画に反映することができる。
• 現在の公開済み統計情報と新しく保留された統計情報を比較することができる。
• 保留中の統計情報を手動で公開することができる。

関連コマンド等

検証を行う前に、関連するコマンド等を整理しておきます。

1. PUBLISH設定を変更するには

PUBLISH属性は、スキーマあるいはテーブルに関する属性になりますが、「保留中」に変更するには以下の要領でDBMS_STATS.SET_TABLE_PREFSパッケージ（プロシージャ）を使用して行います。（ALTER TABLEは使用しません。）

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('<スキーマ名>', '<テーブル名>;', 'PUBLISH', 'false');

例：

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');

2. 現在のPUBLISH設定を確認するには

現在設定されているPUBLISH属性の状態を確認するためには、データ・ディクショナリ・ビューへの問い合わせではなく、DBMS_STATS.GET_PREFSパッケージ（ファンクション）を使用して行います。

SQL> select DBMS_STATS.GET_PREFS('PUBLISH', '<スキーマ名>', '<テーブル名>') prefs from dual;

例：

SQL> select DBMS_STATS.GET_PREFS('PUBLISH', 'SH', 'CUSTOMERS') prefs from dual;

3. 保留中の統計を一時的に使用して実行計画を作成するには

SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

このコマンド実行後のセッションではオプティマイザは保留中の統計を使用して実行計画を作成します。

4. 現在の公開済み統計と新しく保留された統計を比較するには

この場合は、DBMS_STATS.DIFF_TABLE_STATS_IN_PENDINGパッケージ（ファンクション）を使用して行います。

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('<スキーマ名>', '<テーブル名>'));

例：

SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('SH', 'CUSTOMERS'));

5. 保留中の統計を永続的に公開するには

DBMS_STATS.PUBLISH_PENDING_STATSパッケージ（プロシージャ）を使用して行います。公開された統計はもはや保留中ではありません。オプティマイザは次に実行計画を作成する際にこの統計を使用します。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('<スキーマ名>', '<テーブル名>');

例：

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('SH', 'CUSTOMERS');

検証シナリオ

それでは、この機能を検証するためのシナリオを以下に紹介します。

1. テーブル作成
   • TEST表（ID, VALUE）
2. 少量データ作成（1,000件）
3. インデックス作成
   • ID列に対して
4. クエリー実行①
   • インデックス・レンジ検索（1件取得）
5. テーブルTRUNCATE
6. 大量データ作成（50,000件）
7. クエリー実行②
   • 4.と同じクエリー（ただし、検索レンジが異なる）
8. 大量データ削除（50,000→1,000件）
9. クエリー実行③
   • 4, 7と同じクエリー

レコード件数が変化したタイミングで同じクエリーを実行し実行計画を確認しますが、統計情報の取得によって実行計画がどのように変化する（あるいは変化しないか）を検証していきます。

次回へ続く

自動化の落とし穴

グローバル社会は２４時間眠らない。

「９時から５時まで働いて、土日は完全休日。」正月からスーパーが開いている現代ではそんな古き良き時代の働き方は皆無と言えるかもしれません。

私の場合、以前某官公庁で大型汎用計算機の運用に携わっていた頃は、朝７時に当番が早めに出勤してコンピュータを立ち上げ、夕方１８時くらいには電源を落として帰るような運用をしていました。（当然土日は休みです。）

しかし、DBAとして本格的に働き始めた2000年ごろから、２４時間止められない運用とその運用を支える仕組みを直に担当するようになりました。

24×365（２４時間３６５日）運用というものは携わった者でなければ理解できないシビアな世界です。ちょうどこの頃はインターネットをビジネスで使うということが当たり前のようになっていった時期であり、世界規模（グローバル）にビジネスを展開することが多くの企業で求められ始めた頃でもありました。

私はスペシャリストとしてオンコールで対応する立場でしたが、運用エンジニアは３交代シフトで２４時間３６５日運用を行うような体制でした。

この会社は主に日本国内の企業を対象にしていましたが、それでも２４時間システムを維持管理する必要があります。

特にグローバルに事業を展開している企業はより高いレベルでサービスを提供するため、例えばOracle社のサポートは当時日本、米国、英国に拠点を持ち、サポート案件を拠点間でハンドリングすることで、シームレスで高度なサポートを提供しているというようなことを聞きました。

その他にも外国為替市場のように、業界全体がグローバルに広がっている場合の運用もシビアです。

あるFX会社におけるパフォーマンス問題

Oracle10gは2004年にR1がリリースされましたが、以下は比較的早い時期にOracle9iから10gR1にアップグレードした、とあるFX（外国為替証拠金取引）会社で実際に起きた事例を若干の脚色を加えて再現したものです。

FXにおける繁忙時間帯は夜の21時です。なぜなら世界の主要取引市場である東京、ロンドン、ニューヨーク市場はそれぞれ朝7時に開かれます。ニューヨーク市場は元々の規模が大きことや、東京・ロンドン市場の取引傾向の影響を受けるために、日本国内のFX個人投資家にとっても14時間の時差があるニューヨーク市場の動向は非常に気になるのです。仕事から帰ってきたサラリーマンが自宅のパソコンで取引を始める時間帯ということもあり、21時からの数時間は日本のFX業者にとって最もクリティカルな時間帯なのです。

問題は21時にニューヨーク市場が開いて、トランザクション数が次第に伸びてきた22時過ぎに起きました。

さっきまで何の問題もなかったOLTPの取引処理が全く無応答になってしまったのです。

秒単位以下で変動する為替レートに対しリアルタイムに処理が完了しなければならないのに、この状況は致命的でした。

注文や約定と呼ばれる処理が遅れてしまっているため、焦った個人投資家がさらに取引処理を行おうとしたのかもしれません。滞留したトランザクションが膨大になり、状況はさらに悪化していきました。

このようなタイミングでアプリケーションの変更を行うはずがありません。性能劣化の原因が不明のまま時間だけが過ぎていきます。

ついに運用責任者がデータベースの強制再起動を決断しました。

未処理のトランザクションはすべてロールバックされ、せっかくの注文処理が無効になってしまいました。
そればかりか、レートが大きく変動したことによる損失を少しでも小さくするための決済処理が無効になってしまったため、投資家によっては大きな損失が発生してしまったかもしれません。

ほとんどの投資家はネット上で取引を行っているため、深刻なシステム障害はネットの掲示板等で瞬時に広まります。業者にとって投資家に対する補償等だけでなく、悪い評判が拡散してしまうということも大変にダメージが大きなことなのです。

障害の真相

後日、障害の原因を特定することができたのですが、それは唖然とするものでした。

まず初めに障害の背景となった事情について触れておきます。

Oracle10gからルール・ベース・オプティマイザ（RBO）がサポートされなくなりました。RBOで開発していた頃は、FROM句の後に記述するテーブルの順序を入れ替えただけで実行計画がガラッと変わり、パフォーマンスが大きく異なることがよくありました。

具体的には、レコード件数の多い順に記述するような原則で

FROM TBL-A（件数大）,TBL-B（件数中）,TBL-C（件数小）

のような感じで記述します。（この最適な順番を見つけるのが開発者の腕の見せどころでもありました。）
従って、テーブル件数が大きく変動するような場合は、なかなか開発者の意図した結果とならず苦労したという経験は、ベテランのOracle開発者であれば誰でも１つや２つ持っているのではないでしょうか？

ところが、Oracle10g 以降でコスト・ベース・オプティマイザ（CBO）のみになってから、世界は大きく変わりました。データ件数が変動しても統計情報を正しく取得していれば、最適な実行計画を必要に応じて算出してくれる、という理想のオプティマイザがCBOなのです。

前回のルート検索の例で言うと、渋滞情報をリアルタイムに収集して場合に応じた経路を示してくれるという機能がCBOに求められているのです。
つまり、地図上の最短距離だけでルートを示してくれるのがRBOで、渋滞情報を加味したルートを示すのがCBOなのです。

また、カーナビの渋滞情報が変わることに対するルート計算は比較的柔軟に再実行できるような気がしますが、実行計画算出（解析）は負荷の高い処理故、Oracleでは解析済みSQL文を共有することでその負荷がなるべく小さくなるような仕組みになっています。
つまり、渋滞がされた後でもずっと迂回路を案内してしまうような難しさをCBOは併せ持っているのです。

しかも、「正しく統計情報が取得されている」状態でなければ最適な実行計画は算出されないのです。Oracle社はこれに対してテーブル・インデックスの統計情報をデフォルトで自動的に収集する機能を実装しました。

収集の対象となるテーブル・インデックスを特定する条件はありますが、自動統計情報収集処理は10gの場合毎日22時に起動されています。

ほとんどの企業にとって夜の22時はオンライン業務が終了し、バッチ処理も終わっている頃、というのが自動統計情報収集の仕組みを作った開発者の頭の中にあったかどうかはわかりませんが、デフォルト設定がそのようになっているというのは事実です。

日本のFX業者にとってこの仕様が最悪の結果をもたらす可能性があるということは残念ながらあまり知られていませんでした。

詳細は関知していないのですが、このパフォーマンス問題が発生した経緯や背景事情を以下の通り想像を交えてまとめてみます。

1. 繁忙時間帯が夜21時過ぎから日付が変わるあたりまでということは前述の通りですが、ニューヨーク市場がクローズされてから東京市場がオープンされる翌朝7時までのごく短い間にバッチ処理は終了するようになっています。
2. 通常の問題のない日においては22時に自動統計情報収集処理が起動されていましたが、実行計画に影響を与えるようなデータの変動がバッチ処理で行われなかったため、統計情報が大きく変更され実行計画が突然入れ替わってしまうようなことはありませんでした。
3. 問題の起きた日は、朝のバッチ処理でデータの大きな変動がありました。
4. 21時の時点では前日の統計情報により良好な実行計画でOLTPが動いていました。
5. 22時に通常と同じように自動統計情報収集処理が起動され、結果として良好な実行計画が変更されてしまいました。
6. 実行計画算出処理（SQL文解析処理）は元々CPUリソースを消費するものでもあるので、それがOLTPに対して少なからず影響を与えていたというのも事実です。

再発防止策は？

障害の大きな原因として考えられるのは以下の２つです。

• 22時という極めてクリティカルな時間帯であるにも関わらず、自動統計情報収集処理がデフォルトのまま起動されてしまった。
• 実行計画に影響を与えるようなデータの変更があり、実行計画が変更されてしまった。

このケースでは「実行計画が意図せず変更されてしまった。」という問題に対する対策としては、

1. 自動統計情報収集処理を業務影響の少ない時間帯に変更する。
2. 統計情報収集処理を必要最小限の実行にとどめ、毎日は実施しない。
3. 実行計画に影響を与えることが予めわかっているテーブルは統計情報が変更されないようにロックする。

のような項目を実施したと聞いています。

変動するデータに応じて最適な実行計画を立案する仕組みが、Oracle10g以降の「自動化」機能で実現されたわけですが、自動化というのは決して万能ではありません。

上の対策はある意味せっかくの自動化を意図的に殺すことで、（最適ではないかもしれないけれど）安定した運用を実現するものです。

私は「自動化の落とし穴」というテーマで、今までこの事例をいろいろな機会において紹介してきたのですが、反面自動化を選択するか否かの二者択一という考え方で本当に良いのかということをずっと考えてきました。

つまり、自動化と非自動化の中間に「半自動化」という概念があっても良いのではないかというのがその結論であり、次回紹介する「オプティマイザ統計の保留」というあまり注目されていない機能の特徴でもあります。

次回へ続く

オプティマイザの正体

「HOW型」「WHAT型」コンピュータとは？

コンピュータを大きく分類すると「HOW型」と「WHAT型」という２つのタイプに分けることができるという説があります。

これは私が勝手に言っていることではなく１９８０年代にTRONを提唱した東大・坂村健教授の言葉です。（新版 TRONで変わるコンピュータ P.44〜、TRONプロジェクト Wikipedia）

「HOW型」というのは、現在使われているコンピュータつまりノイマン型コンピュータと呼ばれるコンピュータのことで、コンピュータがどのように振る舞うかを人間がプログラムという形で指示するものです。コンピュータが行う複雑な処理の一つ一つを厳密に定義しなければならないためプログラムを作るのは大変ですが、いったんプログラムができてしまえばコンピュータはそれを忠実に実行するだけというものです。

コンピュータ制御された車を考えると、「次の角を左に曲がれ。」とか「国道１号線を東京方面に進め。」等の指示を次々に与えながら目的地に誘導していくのが「HOW（どうやってやる）型」です。

一方「WHAT型」というのは１９８０年代当時研究されていた第五世代コンピュータとか、人工知能専用コンピュータに該当するもので、コンピュータ・カーの例で言うと「道路の左側を車線に沿って走れ。」とか「赤信号では止まること。」のように基本的なルール（専門的には知識ベース）だけを先に教えておいて、「何処何処へ行け！」という指示だけで目的地に向かわせるのが「WHAT（何をする）型」です。

第五世代コンピュータは当時最高の頭脳を結集させたプロジェクトだったようですが、結局は成果を出すことができずに終了してしまいました。まだコンピュータのパワーが非力だったということが最大の原因だったと思いますが、とりあえずムチャクチャに動いて（プログラムなしでコンピュータを動かすのは大変な事）最終的に結果を出せば良いというアプローチにやはり無理があったようです。

オプティマイザは「HOW型」でもあり「WHAT型」でもある

オプティマイザはリレーショナル・データベースの中で最も重要な機能と言っても過言ではありません。正しい結果をより早く返すためのアプローチを最適化する（optimize）機能・プログラムがオプティマイザ（optimizer）です。

それは当然「HOW型」コンピュータの上で動くプログラムですが、「WHAT型」しての性格も色濃く持っています。それはSQL（Structured Query Language）がまさに「（求める）結果＝WHAT」の構造を記述するものだからです。

これはカーナビで経路検索をすることに似ています。例えば「日本橋から横浜ランドマークタワー」まで車で行きたい場合、Google Mapで検索すると

1. 首都高速1号羽田線 と 首都高速神奈川1号横羽線 経由：35.1km、46分（31分）
2. 首都高速3号渋谷線 と 第三京浜道路 経由：41.8km、49分（41分）
3. 第二京浜/国道1号線 経由：35.2km、1時間8分（50分）

という結果が返ってきました。

（イメージと記事の内容は異なります。）

この２点間を結ぶ道は無数にあります（遠回りして新宿駅経由のルートでも目的地には着くことができます）が

1. どんなにお金がかかっても最短時間で着くことができるルート
2. 有料道路でも若干安いルート
3. 有料道路を使わないルート

というような基準でそれぞれのルートを算出し（文字通り計算で求め）たのが上の結果です。ちなみにこれらは渋滞情報も加味されていて（カッコ）内は渋滞なしの場合の所要時間です。（これは実に興味深いことなので次回で取り上げます。）

カーナビで検索されたルートに該当するのが「アクセスパス」です。オプティマイザが最終的に１つに決定したアクセスパスに従って、実際のデータにアクセスされ、加工され、結果が返されます。

言い換えると、オプティマイザの役割は最適なアクセスパスを算出するところまでで、実際の物理的なI/Oやメモリ間操作などはオプティマイザの関知するところではありません。運転前にルートを検索することと実際にそのルートに従って車を運転することが違うことに相当します。

オプティマイザの計算量は膨大

オプティマイザは前述のように、決められたアルゴリズムに従って最適解を得ると言った面では「HOW型」と言えますが、ユーザの求める結果を実現するための「アクセスパス」を最終的に１つに決定するということでは「WHAT型」です。

２点間を結ぶルート検索であれば選択すべき経路はそれほど多くないのですが、複数のテーブルから求める結果を得るということは想像以上に大変なことです。

A、Bという２つのテーブルを結合して結果を得る場合、最初にAテーブルにアクセスしてその結果を基にBテーブルにアクセスすることを「A→B」と表現すると、A→BとB→Aという２通りのアクセスパスが存在します。

さらにA、B、Cの３つのテーブルでは、A→B、A→C、B→A、B→C、C→A、C→Bの６通りになります。（結合は原則的に２つのテーブルあるいは結果セット同士になります。）

テーブル数が増えるごとにアクセスパスは多くなり、簡単に説明すると（テーブル数）！：テーブル数の階乗となります。つまり１０個のテーブルを結合するSQL文の場合は実に 3,628,800通りとなってしまいます。この中から最適なものを１つだけ選択しなければならないのでオプティマイザの計算量は膨大なものとなってしまいます。

実は、Oracleのオプティマイザは300万通り以上の組み合わせを律儀に評価するようなことはしません。「OPTIMIZER_MAX_PERMUTATIONS」というOracle8から導入された初期化パラメータによって評価する組み合わせの上限値が決められています。（これは解析時間を短縮するための苦肉の策と思われます。）

Oracle8と8iではこの値は「80,000」でしたが、9i以降では「2,000」となり、さらに10g以降では隠しパラメータ「_optimizer_max_permutations」となったため基本的に変更しないパラメータとなってしまいました。

SQL> select
  2   a.ksppinm  "Parameter"
  3  ,b.ksppstvl "Value"
  4  from
  5   x$ksppi  a
  6  ,x$ksppcv b
  7  where a.indx    = b.indx
  8  and   a.ksppinm like '%optimizer_max_permutations%';

Parameter                      Value
------------------------------ ----------
_optimizer_max_permutations    2000

７個以上のテーブルを結合するとアクセスパス算出が不十分になる？

「OPTIMIZER_MAX_PERMUTATIONS（または _OPTIMIZER_MAX_PERMUTATIONS）」パラメータが2,000であることの意味を考えてみましょう。

前述のとおり複数テーブルを結合する組み合わせの数は「（テーブル数）！」となります。テーブル数６の場合6!=720、７の場合7!=5,040であるので、７つ以上のテーブルを結合する場合、すべての組み合わせを評価して真に最適なアクセスパスを算出する前に、オプティマイザが評価を諦めてしまう可能性があります。

以前、ある企業のコンサルティングを行った際「テーブルの結合は５つまでとする。」というルールを定めているのを目にしたことがあります。
これは恐らく本パラメータを意識したルールでなかなか興味深い考え方だなと記憶しているのですが、原則的には間違った発想だと思います。

一般的に５つのテーブルを結合するような複雑なクエリーを書くことは珍しいかもしれませんが

.....
from
 emp  e1
,emp  e2
,emp  e3
,dept d
.....

のように、FROM句の後に同じテーブルを複数記述するようなことは簡単にできてしまうので、５つという制限は意味のない足かせになるかもしれませんし、そもそも結合を減らすためにせっかく正規化したテーブルを非正規化するようなことは本末転倒です。

それではもし、正規化された７つ以上のテーブルをどうしても結合しなければならない場合はどうしたらよいでしょうか？

• LEADINGヒントやORDEREDヒントにより、FROM句の後に記述された順にテーブルが結合されるようオプティマイザに情報を与える。
  • デメリット：テーブル順を間違えると悪い結果をもたらす。統計情報が変動した場合どうする？
• PL/SQLでカーソルを定義し（例えば４テーブルのSELECT文）、カーソル・ループの中で残りのテーブルを参照する。
  • デメリット：想像のとおりプログラムが複雑になり、手間の割には成果が少ないかもしれない。
• 一時的に_OPTIMIZER_MAX_PERMUTATIONSパラメータの値を変更する。
  • デメリット：解析済みSQLがキャッシュアウトされる度にパラメータが変更できるか？実行計画を固定化する高度なスキルが必要。

いろいろ考えられるのですが、一長一短ありでなかなか単純ではありません。（あえて言えば３番目が一番スマートでしょう。）

次回へ

「オプティマイザ統計の保留」というあまり注目されていない機能を取り上げ（検証し）ようとしているのですが、オプティマイザについて語ると脱線してしまってなかなかたどり着けません。

次回「序章②」として、オプティマイザを理解する上で前提として押さえておきたいことを説明し、次々回で検証に入りたいと思います。