PSI Labs RSS feed

Oracle:統計情報取得を停止した環境で、日付を使った検索の性能が急激に劣化

こんにちは。shintaniです。

Oracle10gからRBOがサポートされなくなり、統計情報を取得してのCBOでの運用が必須となりました。
しかし、やはり「統計情報を取得したら実行計画が変わって急激に性能が劣化するのではないか?」という心配はあります。
そのため「一度取得した後、テーブルに統計情報ロックを設定して実行計画を固定化する」という運用をしている現場もあると思います。

「統計情報を取得しなければ、徐々に性能劣化していくとしても急激に劣化することは無い」
私もそう思っていた時期がありました・・・(遠い目)


しかしタイトルのような現象が実際に起こってしまいました。
起こったのは下記のような画面においてでした。

WS000000.JPG

その検証のため下記のようなテーブルとデータを用意しました。

【検証環境:Oracle12.1.0.2】

create table TBL_TEST
(   col1_yyyymmdd     VARCHAR2(8)
   ,col2_unique       VARCHAR2(20)
   ,col3_non_unique   VARCHAR2(10)
);

create index  IND_TEST_COL1  ON  TBL_TEST(col1_yyyymmdd) TABLESPACE IDX_TBS;
create index  IND_TEST_COL2  ON  TBL_TEST(col2_unique) TABLESPACE IDX_TBS;
create index  IND_TEST_COL3  ON  TBL_TEST(col3_non_unique) TABLESPACE IDX_TBS;

データ:7300万レコード(1日10万レコード × 2年分)
COL1 は日付項目。文字型YYYYMMDD。
COL2 は全データで一意。
COL3 は日付の中で一意。


【データ作成手順】

※2年間データ保存するテーブル対して2015/12/31に統計情報の取得を停止。そして2年経過したという想定。

① テーブルに 20140101 ~ 20151231 で、7300万レコード作成する。
② 統計情報を取得する
③ テーブル統計情報をロックする
④ ①のデータを削除し、 20160101 ~ 20171231 のデータを作成する。


この状態のテーブルに対し、col3 の一致条件 、および col1 の範囲条件(1か月)を設定し、
日付を1日ずつずらしながらどのように実行計画が変わるか調査しました。

SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120101' AND '20120201';
SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120102' AND '20120202';
SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120103' AND '20120203';
・
・
・
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181129' AND '20181230';
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181130' AND '20181231';
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181201' AND '20190101';

また下記のように col1のインデックスのヒント句を使って、このインデックスを使った場合の見積もり情報も調査しました。

SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120101' AND '20120201';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120102' AND '20120202';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120103' AND '20120203';
・
・
・
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181129' AND '20181230';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181130' AND '20181231';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181201' AND '20190101';

【set autotrace on traceonly explain で実行計画の見積もりだけ表示】

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |    34 |  1394 | 41494   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL_TEST               |    34 |  1394 | 41494   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN                  | IND_TEST_COL1          |  3451K|       |  9637   (1)| 00:00:01 | ← 約345万レコードがヒットする見積もり
-------------------------------------------------------------------------------------------------------------

その結果をまとめたものが下記のイメージです。

20180101_統計情報_日付_グラフ.JPG

統計情報を取得した時点での日付範囲については、col1のインデックスを使った場合のヒット数は 約300万レコード(10万×30日分)と見積もられています。
しかしそれから外れた日付については前後1年間は 1/100 以下 (30000~ 10000) に落ち、そして 2017/01/01を境に急激に落ちて 「1」となりました。
そうなると col3インデックスを使った場合の見積もり 30 より小さいため、col1のインデックスが使用されます。

しかし実際には1日でも10万レコードが入っているため大量にヒットし、非常に遅いSQLとなります。。

この問題の原因は、統計情報として「列の統計情報」と「列ヒストグラム」も取得されていることです。

select 
TABLE_NAME, COLUMN_NAME,  UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) ,  UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
 from USER_TAB_COL_STATISTICS 
  where TABLE_NAME = 'TBL_TEST'
and column_name = 'COL1_YYYYMMDD'

TABLE_NAME, COLUMN_NAME, UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE), UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
---------------------------------------------------------------------------------
TBL_TEST	COL1_YYYYMMDD	20140101	20151231


-- ※ヒストグラムは省略。

LOW_VALUEとHIGH_VALUE に最小値と最大値を持ち、ヒストグラムでその分布情報を持っているため、
その範囲から外れた場合はヒットするレコードを少なく見積もります。

一定期間(この場合は1年ほど)は、いきなり0ではなくある程度の余裕を持って見積もっているから
実行計画が変わることは無かったのですが、1年以上経過したら「もういいよね・・・」と、1レコードと見積もって、実行計画が変わってしまったわけです。

みなさんもご注意下さい。

ではでは。


【追記】
この現象ですが、データを保持する期間や列のデータ型などによって変わってくると思われます。

① 検証環境では見積もり数の激減は 年替わり(20161231 → 20170101)の箇所で起こったが、実環境では 年の途中で起こった。
② 検証環境では「統計情報から外れても 1/100 程度に見積もる期間」は1年間ほどだったが、実環境では2年だった。
③ 最初データ保持期間を「1年間」で作ったが、この場合はどれだけ統計情報から外れても 1/100 程度に見積もり続けた。

特に③ですが、これは「データに間が空いた部分が無かった」ためではないかと推測しています。
「20141231 → 20150101」 は人の感覚では +1日ですが、文字型データ的には「8700ほど飛んだ」形になっています。
DATE型で列を作ってあったならはこの間の空きは無いわけですから、違う結果が出る可能性もあります。