「オプティマイザ統計の保留」の検証(その2)

検証開始!

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

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

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

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

続く