作成日：2020/02/25　更新日：2022/01/30

概要

ORACLEで０件で統計情報を収集すると危険な理由を実際のコード例を交えて説明します

0件統計は何故だめなのか

ORACLEは統計情報を元に実行計画を立てますが、遅い実行計画を選択してしまう原因として０件状態の統計情報収集が原因であることがよくあります。本ページでは実際に0件状態で統計取集することで遅い実行計画を選択させ、何故遅い実行計画を選択するのかを解説します。

0件統計によりハズレ実行計画を選択させる

以下のスクリプトを実行すると索引スキャンではなくFULLスキャンの実行計画が採択されます。 実データとしてはcol1=1で10万件中1件まで絞り込めるデータのため100%索引のほうが早くなりますが、 0件状態で統計情報をとると不具合ではなく仕様としてこのような動作になります。 （2020年時点で最新のバージョン19cで確認）

--表作成
create table b(col1 number ,col2 varchar2(100),col3 char(2000));

--0件の状態で統計情報を収集
exec dbms_stats.gather_table_stats(null,'B');

--適当なデータをinsert
begin
  for i in 1..100000 loop
    insert into b values(i,i,i);
  end loop;
  commit;
end;
/

--索引を作成＋索引の統計収集
create index b_ix01 on b(col1,col2,col3);

set autot on
select count(*) from B where col1 = 1;
★索引を使わずフルスキャンになってしまう。
実行計画
----------------------------------------------------------
Plan hash value: 749587668

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("COL1"=1)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      33441  consistent gets  --実際に実行した場合のブロック読み込み数は33441
          0  physical reads
          0  redo size
        573  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

なぜfullスキャンになるのか

ORACLEは統計情報を拠り所として最もCOSTの低い実行計画を選択します。上記の例ではFULLスキャンの実行計画のCOSTが2と非常に低いとORACLEが予想しており、本来適切であろう索引スキャンをヒントで強制してみると COSTが2より大きい336で索引スキャンよりもFULLスキャンのほうがCOSTが低い(早い) と判断しているためFULLスキャンが選択されていることがわかります。

SQL> select /*+ index(b) */ count(*) from B where col1 = 1;

実行計画
----------------------------------------------------------
Plan hash value: 2794869083

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    13 |   336   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| B_IX01 |     1 |    13 |   336   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("COL1"=1)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets    --実際に実行した場合のブロック読み込み数は3
          0  physical reads
          0  redo size
        573  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

なぜfullスキャンのコストが異常に低いのか

端的にいうと統計情報を0件の状態で取得したため、FULLスキャンも0件の表に対するFULLスキャンの想定でCOST計算されるためです。表の統計情報はDBA_TABLES等で確認できますが、0件状態で収集した統計なので以下の通り表のブロック数統計（blocks）が0になっています(COSTが0ではないのは管理系の読み込みブロックが考慮されるため)。

これに対し、索引は10万件の状態で統計を収集(索引作成時に自動で統計情報が収集される)しているためリーフブロック数が33334となっており索引スキャンのCOSTはFULLスキャンよりも大きくなります。

col table_name format a30
select table_name,num_rows,blocks from user_tables where table_name = 'B';
TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
B                                       0          0

col index_name format a30
select index_name,blevel,leaf_blocks from user_indexes where table_name = 'B';
INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
B_IX01                                  2       33334

まとめ：0件で統計情報は収集しない

0件状態の統計情報を収集してしまうとデータが0件ではなくなった場合極端に劣化しやすい実行計画が選択されやすくなるため常に0件である表以外は0件状態での統計情報収集はしないことをお勧めします。統計情報を収取していない場合ダイナミックサンプリングによりその時点のデータがサンプリングされるため経験則では0件統計を収集するよりは統計を収集しないほうがまだましな実行計画になる場合が多いです。

★ORACLE案件承ります