下記のようにテーブル&レコード作成した環境で統計情報の取得前と取得後の実行計画を比較してみました。
すると、統計情報取得後の方がコストが高くなっているのですが、これはどうしてなのでしょうか?
###■環境準備
sql
1-- テーブル作成 2CREATE TABLE JOB_MASTER ( 3 UPD_TIME DATE DEFAULT SYSDATE 4 ,NAME_ID CHAR(8) 5 ,JOB_ID CHAR(6) 6 ,TSUBAN NUMBER(10) 7 ,COMPANY_NAME VARCHAR(30) 8 ,EXIST_FLG CHAR(1) 9 ,SAVE_MONEY NUMBER(15) 10 ,PRIMARY KEY(NAME_ID, JOB_ID) 11) 12 13-- データ作成 14BEGIN 15 FOR I IN 1..1000000 LOOP 16 INSERT INTO JOB_MASTER 17 SELECT SYSDATE 18 ,LPAD(I, 8, 'N') 19 ,LPAD(I, 6, 'J') 20 ,I 21 ,DBMS_RANDOM.STRING('A', 30) 22 ,'0' 23 ,ABS(DBMS_RANDOM.VALUE(0, 999999999999999)) 24 FROM DUAL; 25 END LOOP; 26 COMMIT; 27END; 28/
###実行計画取得対象SQL
sql
1DELETE FROM JOB_MASTER WHERE NAME_ID <= 'NN453214';
###■統計情報取得前
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
1-- 統計情報取得(第一引数はスキーマ名) 2EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS', 'JOB_MASTER');
###■統計情報取得後
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
回答1件
あなたの回答
tips
プレビュー