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

つづきです。
バッファキャッシュにヒットした場合はとどうかというと、物理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