オプティマイザをだましちゃお！ の続き。 Tweet

オプティマイザをだましちゃお！ の続きです。

リテラル値指定、かつ、全件ヒットするからTABLE FULL SCANになるはずなのに、何故、INDEX RANGE SCANしたのか？　の種明かしです

status列の値が全て 0 の状態で統計情報を取得した状態で、status列全てを 2　に更新、その後統計情報は再取得していません。
オプティマイザは、status列が全て 0 だと思い込んでいるわけですね、実際は、全て 2 なのに。

08:55:05 SCOTT> update deluding_tab set status = 2;

100000行が更新されました。

08:55:15 SCOTT> commit;

コミットが完了しました。

status列が全て、またはほぼ、 0 なのであれば、全データをINDEX RANGE SCANで取得するなんて通常はあり得ないですよね :)　

列値と統計情報の取得タイミングでオプティマイザを騙しているわけです。
（これは意図的ですが、意図せずオプティマイザが誤った実行計画を算出して夜中に電話が鳴った，なんて方は意外に多いかも…w)

念のため 10053　トレースで status = 1 と status = 2　で同じ実行計画を求めているかも確認してみると…

08:55:18 SCOTT> alter session set events '10053 trace name context forever, level 1';

セッションが変更されました。

08:55:24 SCOTT> select * from deluding_tab where status = 1;

08:55:37 SCOTT> select * from deluding_tab where status = 2;

リテラル値部分が異なる事以外は皆同じということが分かります。うまくオプティマイザを騙せてますね :)

discus$ diff status_1.txt status_2.txt
1c1
< *** 2012-05-06 08:55:37.663
---
> *** 2012-05-06 08:55:50.630
23,24c23,24
< ----- Current SQL Statement for this session (sql_id=2pa417mwk4crp) -----
< select * from deluding_tab where status = 1
---
> ----- Current SQL Statement for this session (sql_id=7sb6ypqd43bdr) -----
> select * from deluding_tab where status = 2
824c824
< CBQT: Validity checks failed for 2pa417mwk4crp.
---
> CBQT: Validity checks failed for 7sb6ypqd43bdr.
847c847
< CBQT: Validity checks failed for 2pa417mwk4crp.
---
> CBQT: Validity checks failed for 7sb6ypqd43bdr.
870c870
< "DELUDING_TAB"."STATUS"=1
---
> "DELUDING_TAB"."STATUS"=2
872c872
< finally: "DELUDING_TAB"."STATUS"=1
---
> finally: "DELUDING_TAB"."STATUS"=2
874c874
< apadrv-start sqlid=3073848844558152437
---
> apadrv-start sqlid=8947771396877036983
883c883
< SELECT "DELUDING_TAB"."ID" "ID","DELUDING_TAB"."STATUS" "STATUS","DELUDING_TAB"."DATA" "DATA" FROM "SCOTT"."DELUDING_TAB"
"DELUDING_TAB" WHERE "DELUDING_TAB"."STATUS"=1
---
> SELECT "DELUDING_TAB"."ID" "ID","DELUDING_TAB"."STATUS" "STATUS","DELUDING_TAB"."DATA" "DATA" FROM "SCOTT"."DELUDING_TAB"
"DELUDING_TAB" WHERE "DELUDING_TAB"."STATUS"=2
893c893
< select * from deluding_tab where status = 1
---
> select * from deluding_tab where status = 2
959,960c959,960
< id=0 frofkks[i] (index start key) predicate="DELUDING_TAB"."STATUS"=1
< id=0 frofkke[i] (index stop key) predicate="DELUDING_TAB"."STATUS"=1
---
> id=0 frofkks[i] (index start key) predicate="DELUDING_TAB"."STATUS"=2
> id=0 frofkke[i] (index stop key) predicate="DELUDING_TAB"."STATUS"=2
980,982c980,982
< sql_id=2pa417mwk4crp plan_hash_value=1226994206 problem_type=3
< ----- Current SQL Statement for this session (sql_id=2pa417mwk4crp) -----
< select * from deluding_tab where status = 1
---
> sql_id=7sb6ypqd43bdr plan_hash_value=1226994206 problem_type=3
> ----- Current SQL Statement for this session (sql_id=7sb6ypqd43bdr) -----
> select * from deluding_tab where status = 2
984c984
< sql=select * from deluding_tab where status = 1
---
> sql=select * from deluding_tab where status = 2
1000c1000
< 2 - access("STATUS"=1)
---
> 2 - access("STATUS"=2)
discus$ 

status列の値が全て 2 なのですが、統計情報は 0 で取得されているので status = 0 で検索すると、索引は使わず TABLE FULL SCANですよね。対象データは 0 件なんですけど。 (^^;;;

14:52:04 SCOTT> select * from deluding_tab where status = 0;

レコードが選択されませんでした。

経過: 00:00:00.64

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

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 99991 |    48M|  1947   (1)| 00:00:24 |
|*  1 |  TABLE ACCESS FULL| DELUDING_TAB | 99991 |    48M|  1947   (1)| 00:00:24 |
----------------------------------------------------------------------------------

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

   1 - filter("STATUS"=0)


統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   7184  consistent gets
   7174  physical reads
      0  redo size
    487  bytes sent via SQL*Net to client
    509  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

---

ここまでのあらすじ

・オプティマイザをだましちゃお！　（マジック・ザ・ギャザリング風w かも)