実行計画・コストが統計情報取得プロシージャのオプションで変わる

###【概要】
統計情報取得プロシージャ`DBMS_STATS.GATHER_TABLE_STATS`に`method_opt `というオプションがあるのですが、このオプションを使用するかしないかによって実行計画が変わります。

###【環境】
Oracle 11.2.0


###【テストデータ準備】
```sql
-- テーブル作成
CREATE TABLE JOB_MASTER (
    UPD_TIME      DATE DEFAULT SYSDATE
   ,NAME_ID       CHAR(8)
   ,JOB_ID        CHAR(6)
   ,TSUBAN        NUMBER(10)
   ,COMPANY_NAME  VARCHAR(30)
   ,EXIST_FLG     CHAR(1)
   ,SAVE_MONEY    NUMBER(15)
   ,PRIMARY KEY(NAME_ID, JOB_ID)
)
```

```sql
-- データ作成
BEGIN
    FOR I IN 1..1000000 LOOP
        INSERT INTO JOB_MASTER
          SELECT SYSDATE
                 ,LPAD(I, 8, 'N')
                 ,LPAD(I, 6, 'J')
                 ,I
                 ,DBMS_RANDOM.STRING('A', 30)
                 ,'0'
                 ,ABS(DBMS_RANDOM.VALUE(0, 999999999999999))
          FROM DUAL;
    END LOOP;
    COMMIT;
END;
/
```
###【実行計画取得対象のSQL】
```sql
DELETE FROM JOB_MASTER WHERE NAME_ID <= 'NN453214';
```

###【統計情報取得前の実行計画】

実行計画は`INDEX RANGE SCAN`、コストは`60`。

```
353,216行削除されました。

Plan hash value: 3759745238

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |             |   117 |  2106 |    60   (0)| 00:00:01 |
|   1 |  DELETE           | JOB_MASTER  |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C008248 |   117 |  2106 |    60   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   2 - access("NAME_ID"<='NN453214')

Note
-----
   - dynamic sampling used for this statement (level=2)

   Statistics
-----------------------------------------------------------
             603  CPU used by this session
             603  CPU used when call started
            1167  DB time
             216  enqueue conversions
             399  enqueue releases
             401  enqueue requests
             323  messages sent
            8592  non-idle wait count
             579  non-idle wait time
              84  opened cursors cumulative
               1  opened cursors current
            8548  physical read total IO requests
        82534400  physical read total bytes
              51  physical write total IO requests
        12804096  physical write total bytes
              50  physical write total multi block requests
               1  pinned cursors current
             725  recursive calls
               3  recursive cpu usage
          399179  session logical reads
             564  user I/O wait time
               6  user calls
```

###【統計情報取得後の実行計画】

####①オプションなしの場合
```sql
-- 統計情報取得（第一引数はスキーマ名）
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS', 'JOB_MASTER');
```

#####【実行計画】

実行計画は`TABLE ACCESS FULL`、コストは`2929`。

```
353,216行削除されました。

Plan hash value: 941518712

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |            |   996K|    15M|  2929   (2)| 00:00:36 |
|   1 |  DELETE            | JOB_MASTER |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| JOB_MASTER |   996K|    15M|  2929   (2)| 00:00:36 |
---------------------------------------------------------------------------------

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

   2 - filter("NAME_ID"<='NN453214')

   Statistics
-----------------------------------------------------------
            1650  CPU used by this session
            1650  CPU used when call started
            2276  DB time
              90  enqueue conversions
             441  enqueue releases
             443  enqueue requests
               1  enqueue waits
             329  messages sent
            3645  non-idle wait count
             550  non-idle wait time
              52  opened cursors cumulative
               1  opened cursors current
            3495  physical read total IO requests
               1  pinned cursors current
             400  recursive calls
              13  recursive cpu usage
         1461632  session logical reads
             531  user I/O wait time
               6  user calls
```

####②オプションありの場合
```sql
-- 統計情報取得（第一引数はスキーマ名）
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS', 'JOB_MASTER', method_opt => 'FOR ALL INDEXES');
```

#####【実行計画】

実行計画は`INDEX RANGE SCAN`、コストは`61`。

```
353,216行削除されました。

Plan hash value: 2951507301
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |             | 50000 |   878K|    61   (0)| 00:00:01 |
|   1 |  DELETE           | JOB_MASTER  |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C008757 | 50000 |   878K|    61   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("NAME_ID"<='NN453214')

   Statistics
-----------------------------------------------------------
             571  CPU used by this session
             571  CPU used when call started
            1144  DB time
              67  calls to get snapshot scn: kcmgss
              13  calls to kcmgas
              55  calls to kcmgcs
        93413376  cell physical IO interconnect bytes
             273  change write time
            4418  consistent gets
            1901  consistent gets - examination
            4418  consistent gets from cache
             181  consistent gets from cache (fastpath)
          740844  db block changes
          389800  db block gets
          389800  db block gets from cache
            6183  db block gets from cache (fastpath)
           16650  dirty buffers inspected
             124  enqueue releases
             126  enqueue requests
           17944  free buffer inspected
           17928  free buffer requested
               2  hot buffers moved to head of LRU
             248  messages sent
            8320  non-idle wait count
             584  non-idle wait time
              66  opened cursors cumulative
               1  opened cursors current
            8509  physical read IO requests
        80609280  physical read bytes
            8509  physical read total IO requests
        80609280  physical read total bytes
            9840  physical reads
            8277  physical reads cache
            1563  physical reads direct
            1563  physical reads direct temporary tablespace
              61  physical write IO requests
        12804096  physical write bytes
              61  physical write total IO requests
        12804096  physical write total bytes
              49  physical write total multi block requests
            1563  physical writes
            1563  physical writes direct
            1563  physical writes direct temporary tablespace
            1563  physical writes non checkpoint
              18  pinned buffers inspected
               1  pinned cursors current
            1260  prefetched blocks aged out before use
             558  recursive calls
          363569  redo entries
          394218  session logical reads
              55  shared hash latch upgrades - no wait
               2  switch current to new buffer
             573  user I/O wait time
               6  user calls
               9  write clones created in foreground
```

###【まとめ】
|                              | 実行計画         | コスト|
|:-----------------------------|:----------------|:-------|
| 統計情報取得前                | INDEX RANGE SCAN | 60    |
| 統計情報取得後(オプションなし) | TABLE ACCESS FULL| 2929  |
| 統計情報取得後(オプションあり) | INDEX RANGE SCAN | 61    |