LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@mkyz08

統計が更新できない場合に日付型を検索する実行計画はどうなるか

Posted at

統計が更新できない場合に日付型を検索する実行計画はどうなるか

オプティマイザ統計（統計情報）はデフォルトではOracleによって自動で取得されるため、統計が更新されない（古い）ということはほとんどありません。
ただし、テーブルが大きすぎて統計が更新できなかったり、統計をロックしている場合は統計が古くなっている場合があります。
この場合に日付型の列の実行計画にどのようなことが発生するかを確認してみます。

日付型の列を持つテーブルに対して、毎日古いデータを削除するという処理がよく行われます。
例えば、データの保存期間は1か月で、毎日最後の1日を削除するような処理です。

この場合、最後の1日を消すために約30分の1を検索するため、インデックスを使用した実行計画が有効です。
ただし、統計が古いと思いがけない実行計画が選択されることがあります。

テストデータを作成する

準備として、TBL21テーブルを作成し、2020年1月分のデータを1日10万レコードで作成します。
日付型のカラムは"CREATED"とし、インデックスも貼っておきます。

create table tbl21 (id number not null, created timestamp(0), val char(100));

-- TBL21表へレコード10万件×31日を挿入
begin
  for i in 0..30 loop
    insert into tbl21 select level + i, to_date('2020-01-01', 'YYYY-MM-DD') + i, 'test' || level from DUAL connect by level <= 100000;
    commit;
  end loop ;
end ;
/

create index idx_created on tbl21(created);

統計情報も取得しておきます。

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');

取得した統計情報を確認してみます。

column table_name format a20
column column_name format a20
column high_value format a20
column low_value format a20
set linesize 150

select table_name, num_rows, last_analyzed, stattype_locked, stale_stats from user_tab_statistics where table_name = 'TBL21';

TABLE_NAME             NUM_ROWS LAST_ANAL STATT STALE_S
-------------------- ---------- --------- ----- -------
TBL21                   3100000 11-JAN-21       NO


select table_name, column_name, num_distinct, dbms_stats.convert_raw_to_date(low_value) as low_value, dbms_stats.convert_raw_to_date(high_value) as high_value, density, last_analyzed, histogram from user_tab_col_statistics where table_name = 'TBL21' and column_name = 'CREATED';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT LOW_VALUE            HIGH_VALUE              DENSITY LAST_ANAL HISTOGRAM
-------------------- -------------------- ------------ -------------------- -------------------- ---------- --------- ---------------
TBL21                CREATED                        31 01-JAN-20            31-JAN-20            .032258065 11-JAN-21 NONE

31日分のデータを投入したのでNUM_DISTINCTは"31"になっています。1/1から1/31までのデータなのでLOW_VALUE（上限）とHIGH_VALUE（下限）もそのようになっています。
DENSITYはデータの密度で、今回はまだHISTOGRAMがない（NONE）ので、1 / NUM_DISTINCTの値になっています。

この状態で、1/5のデータを検索した実行計画は以下のようになります

set serveroutput off
set linesize 150
set pagesize 20

select count(*) from tbl21 where created = to_date('2020-01-05', 'YYYY-MM-DD');
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |   283 (100)|          |
|   1 |  SORT AGGREGATE   |             |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_CREATED |   100K|   683K|   283   (1)| 00:00:01 |
---------------------------------------------------------------------------------
※実行計画の出力は省略して表示。以降も同様。
```

ROWSが1/5の条件で返ってきたレコード数で、10万件になっています。
全体310万/31日で10万件として計算されています。
約3%のデータの検索となるため、"INDEX RANGE SCAN"が選択されています。

ここで、1月以外の日付として、2/15で検索してみます。


```sql
select count(*) from tbl21 where created = to_date('2020-02-15', 'YYYY-MM-DD');
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |   142 (100)|          |
|   1 |  SORT AGGREGATE   |             |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_CREATED | 50000 |   341K|   142   (0)| 00:00:01 |
---------------------------------------------------------------------------------

```

1/5の検索では10万件だったのが、半分の5万件になっています。
上限の1/31以降にレコード数は徐々に減衰し、半月後に半分になっています。今回は1か月分のデータを入れたので、ちょうど一か月後にほぼデータがないような状態になるようです。

図にすると以下のようになります。
※ただし、ヒストグラムの有無によって、レコード数の減衰の割合は若干変わってきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/179607/c005a549-3c3a-da3f-a917-36c2dbe225b6.png)


# 本題

ここからが本題で、統計が更新されなかった場合の実行計画を確認してみます。
題材は同じで、約一カ月経過した後の状態で検索を実行してみます。

まずは削除対象の1日分のデータを検索する実行計画を確認します。
※都合上削除はしません。
この実行計画ではINDEX RANGE SCANが使用されています。

```sql
select * from tbl21 where created <= to_date('2020-01-01', 'YYYY-MM-DD');
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |       |  1925 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL21       |   100K|    10M|  1925   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_CREATED |   100K|       |   283   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------

```

次に一か月後のデータを模擬するため、一旦全てのデータを削除し、2月分のデータを投入します。


```sql
delete from tbl21;
commit;

begin
  for i in 0..30 loop
    insert into tbl21 select level + i, to_date('2020-02-01', 'YYYY-MM-DD') + i, 'test' || level from DUAL connect by level <= 100000;
    commit;
  end loop;
end;
/
```

この状態で今度は一か月後の2/1以前のデータを検索してみます。

```sql
select * from tbl21 where created <= to_date('2020-02-01', 'YYYY-MM-DD');
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       | 13823 (100)|          |
|*  1 |  TABLE ACCESS FULL| TBL21 |  3100K|   334M| 13823   (1)| 00:00:01 |
---------------------------------------------------------------------------

```

これまでは31日分から1日分の検索なので実行計画にインデックスが使用されていました。
しかし今回はフルスキャンとなっていしまいました。
これは統計から2/1以前に１カ月（310万レコード）の全てのデータが含まれていると判断してしまっているからです。

今回はバインド変数を使用していないため毎回ハードパースが実行されて、都度実行計画が生成されていました。実際の実装ではバインド変数を使用するため、最初にハードパースされたときに生成された実行計画が使用されるため、このような現象は発生しません。ただし、ハードパースが発生した場合は不適切な実行計画が使用されてしまいます。

統計情報を固定しているのであれば、上限値・下限値を手動で指定することもできます。
上限値・下限値を設定しないということもできるけど、（試したことないけど）悪さしかしなさそう。


# まとめ

- 統計情報が古いと実行計画が変わることがある。
- 統計の上限・下限値の範囲外の検索をすると、範囲内から減衰したレコード数が利用される。


# 参考

- [Histograms are Evil like Chocolate is Evil](https://www.doag.org/formes/pubfiles/9483234/2017-DB-Neil_Chandler-Histograms_are_Evil_like_Chocolate_is_Evil-Manuskript.pdf)
- [ALL_TAB_COL_STATISTICS](https://docs.oracle.com/cd/F19136_01/refrn/ALL_TAB_COL_STATISTICS.html#GUID-40AC6263-089D-4471-B2E2-EBCA15BC4C65)
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?