EXPLAIN PLAN FOR 〜(※AUTOTRACE TRACEONLY含む)で出てくる実行計画 と SQL実行時の実行計画が異なるケースを作ってみる。

EXPLAIN PLAN FOR 〜(※AUTOTRACE TRACEONLY含む)で出てくる実行計画 と
SQL実行時の実行計画が異なるケースを作ってみるZe!(｀･ω･)Ъ

幾つかパターンは有るんですが、WHERE句の範囲検索を
バインド変数で指定するケースでやってみます。簡単なんで。

まず服は脱がずに、データ作る。

DROP TABLE TBL_A PURGE;

CREATE TABLE TBL_A (
    C1 NUMBER
  , C2 DATE
);

ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY(C1) USING INDEX;

DECLARE
  TYPE t_array_A IS TABLE OF TBL_A%ROWTYPE INDEX BY BINARY_INTEGER;
  array_A t_array_A;
BEGIN
  array_A.DELETE;
  FOR i IN 1..10000
  LOOP
    array_A(i).C1 := i;
    array_A(i).C2 := TO_DATE('20150723', 'YYYYMMDD') + (i/24/60/60);
  END LOOP;
  FORALL i in array_A.FIRST..array_A.LAST
    INSERT INTO TBL_A VALUES array_A(i);
  COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TBL_A', NO_INVALIDATE => FALSE);

SQL> SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
     10000

SQL>

次に不等号入りのSQLをEXPLAIN PLANしてみます。INDEX RANGE SCAN のプランやで！

EXPLAIN PLAN FOR
SELECT * FROM TBL_A WHERE C1 <= :B1;

Explained.

SET LINESIZE 170;
SET PAGESIZE 1000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 3263267004

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   500 |  6000 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL_A    |   500 |  6000 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TBL_A_PK |    90 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
：
26 rows selected.

バインド変数使用／SET AUTOTRACE TRACEONLY で実際にSQLを実行してみる。
10,000件全てを取得する SQL なんで、TABLE を FULL SCAN する実行計画が期待値ですが、、、

VAR B1 NUMBER;
EXEC :B1 := 10000;
SET AUTOTRACE TRACEONLY;
-- SQL_ID = 'gqr0w2pjvq3mv'
SELECT * FROM TBL_A WHERE C1 <= :B1;

10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3263267004

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   500 |  6000 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL_A    |   500 |  6000 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TBL_A_PK |    90 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
：
Statistics
----------------------------------------------------------
：
        696  consistent gets
：
      10000  rows processed

INDEX RANGE SCAN のプラン……？と思いきやなんすが、AUTOTRACE で出て来るプランは
内部的に EXPLAIN PLAN 〜 をしているので、実際の実行計画とは異なる場合があります。

DBMS_XPLAN.DISPLAY_CURSOR で共有プールに格納された実際の実行計画を抜いてみます。

SET AUTOTRACE OFF;
SET LINESIZE 170;
SET PAGESIZE 1000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gqr0w2pjvq3mv', NULL, 'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
SQL_ID  gqr0w2pjvq3mv, child number 0
-------------------------------------
SELECT * FROM TBL_A WHERE C1 <= :B1

Plan hash value: 723866848

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     9 (100)|          |
|*  1 |  TABLE ACCESS FULL| TBL_A |  8001 | 96012 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------
：

彡(^)(^)「TABLE FULL SCAN の実行計画だったやで！」

このように EXPLAIN PLAN FOR〜(※AUTOTRACE TRACEONLY含む)で出て来る実行計画と、
実際の実行計画は異なるケースが有るから、気を付けるんやで。下記マニュアルも参照！

Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06
12 EXPLAIN PLANの使用方法
12.1.4 EXPLAIN PLANの制限事項
http://docs.oracle.com/cd/E16338_01/server.112/b56312/ex_plan.htm
 > 一般にバインド変数では、EXPLAIN PLANが実際の実行計画を表していない場合があります。

EXPLAIN PLAN FOR〜はSQLを実行せずに実行計画を確認できるので非常に
便利なんですが、この記事で紹介したケース、云わば落とし穴が有ります。

DBMS_XPLAN.DISPLAY_CURSOR も併用して、共有プールに格納された
実際の実行計画も見るようにしませう(´･ω･)ゞ

関連記事：
【SQLの実行計画を出力:id:gonsuke777:20110826:1314340326】
【共有プール上の実行計画を出力する。(DBMS_XPLAN.DISPLAY_CURSOR):id:gonsuke777:20120612:1339488014】