###【概要】
統計情報取得プロシージャDBMS_STATS.GATHER_TABLE_STATSにmethod_opt というオプションがあるのですが、このオプションを使用するかしないかによって実行計画が変わります。
###【環境】
Oracle 11.2.0
###【テストデータ準備】
-- テーブル作成
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)
)
-- データ作成
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】
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
###【統計情報取得後の実行計画】
####①オプションなしの場合
-- 統計情報取得（第一引数はスキーマ名）
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
####②オプションありの場合
-- 統計情報取得（第一引数はスキーマ名）
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 |