検証開始！
前回示した検証シナリオに沿って、「オプティマイザ統計の保留」の基本的な機能を確認していきます。
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
保留中の統計情報は公開して初めて実行計画の算出に使われるようになります。
テーブル件数に応じた適切な実行計画が選択されました。
今回はマニュアルに記載のある基本的な動作について確認しました。
次回は保留中の統計情報がどのように保持されているのかを詳しく見てみることにします。
続く