« TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #2 | トップページ | Oracle Database 12c R1 12.1.0.2.0 In-memory option はじめの一歩。 »

2014年7月26日 (土)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #3

つづきです。
バッファキャッシュにヒットした場合はどうかというと、物理I/Oは発生しないので、そのまんまの結果ですよね。(^^;;;

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 195 0.11 0.18 0 6287 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 0.11 0.18 0 6287 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6287 pr=0 pw=0 time=206651 us)
2907 2907 2907 NESTED LOOPS (cr=3380 pr=0 pw=0 time=116870 us cost=5362 size=1744812 card=2851)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID BATCHED HIGH_CLUSTERING_FACTOR (cr=3108 pr=0 pw=0 time=42395 us cost=2861 size=872406 card=2851)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=0 pw=0 time=12292 us cost=8 size=0 card=2851)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=272 pr=0 pw=0 time=36902 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=0 pw=0 time=37280 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.00
SQL*Net message from client 195 0.00 0.15
SQL*Net more data to client 193 0.00 0.01
********************************************************************************

12cで、optimizer_features_enable='11.2.0.1'にしてバッファキャッシュをクリアすれば、11gと同じ実行計画(TABLE ACCESS BY INDEX ROWID)になって、db file parallel readになるよね!

という確認もしておいた!

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.42 1.59 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 195 18.34 61.02 2450 6287 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 18.77 62.63 2450 6287 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6287 pr=2450 pw=0 time=92881090 us)
2907 2907 2907 NESTED LOOPS (cr=3380 pr=2232 pw=0 time=59488238 us cost=5362 size=1744812 card=2851)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=3108 pr=2221 pw=0 time=31142410 us cost=2861 size=872406 card=2851)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=7 pw=0 time=3493710 us cost=8 size=0 card=2851)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=272 pr=11 pw=0 time=10377721 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=218 pw=0 time=13380036 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.21
Disk file operations I/O 1 0.00 0.00
db file sequential read 452 0.21 1.54
SQL*Net message from client 195 15.35 16.71
SQL*Net more data to client 193 0.00 0.47
db file parallel read 190 0.05 5.91
********************************************************************************


整形前SQLトレースより抜粋 (db file parallel readが索引で現れている

WAIT #140513423347200: nam='db file parallel read' ela= 16615 files=1 blocks=13 requests=13 obj#=93726 tim=6258017207

straceより抜粋...io_submit()、だよね〜。:)

io_submit(140513465466880, 13, ...中略... ) = 13


最後に、11g R2 11.2.0.1で同じことを確認(確認するまでもないんだけどね)

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 169 0.58 6.88 2148 5439 0 2509
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 171 0.58 6.88 2148 5439 0 2509

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
2509 NESTED LOOPS (cr=5439 pr=2148 pw=0 time=19292592 us)
2509 NESTED LOOPS (cr=2930 pr=1931 pw=0 time=24524478 us cost=5012 size=1530612 card=2501)
2509 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2683 pr=1922 pw=0 time=24392808 us cost=2510 size=765612 card=2502)
2509 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=174 pr=5 pw=0 time=11616 us cost=8 size=0 card=2502)(object id 113222)
2509 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=247 pr=9 pw=0 time=0 us cost=0 size=0 card=1)(object id 113220)
2509 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2509 pr=217 pw=0 time=0 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2509 NESTED LOOPS
2509 NESTED LOOPS
2509 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2509 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2509 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2509 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 169 0.00 0.00
SQL*Net message from client 169 24.57 25.54
Disk file operations I/O 1 0.00 0.00
db file parallel read 167 0.09 5.66
db file sequential read 357 0.01 0.92
SQL*Net more data to client 167 0.00 0.01
********************************************************************************

11gまでは、実行計画上には現れず実行時に内部的に行っていたI/O最適化の動きが、12cからは実行計画上でも確認できるようになったというのは分かり易くていい。
時間あったら、v$sesstatも載せるかも。 :)

さて、12c 12.1.0.2.0のダウンロードも終わったので、別の楽しみが増えましたよね、みなさん!

ダウンロード! しましたか〜〜〜〜〜っ! :)

みなさんの、12c ネタ祭りを楽しみにしております。 (^^


TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1
TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #2

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/60044290

この記事へのトラックバック一覧です: TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #3:

コメント

コメントを書く