db file scattered read と db file parallel read と db file sequential read (最終回） Tweet

無理矢理引っ張った感じですが、db file scattered read と db file parallel read と db file sequential readの最終回。

今日は、db file parallel read　に注目してみようと思います。

db file parallel readはどのような待機イベントだったか再確認しておきましょう！

マニュアルでは以下のように説明されています。

db file parallel read
リカバリ時のイベントです。
バッファ・プリフェッチ中に、最適化(複数のシングル・ブロック読取りの実行ではない)として発生する可能性もあります。
リカバリ時に変更が必要となったデータベース・ブロックはデータベースからパラレルに読み込まれます。

すべてのI/Oが完了するまでの時間が待機時間となる

少々わかりずらいですが、「バッファ・プリフェッチ中に、最適化(複数のシングル・ブロック読取りの実行ではない)として発生する可能性もあります。」ってところがポイントですよね。(リカバリしているわけではないので!)

でてきましたね、 prefetch という単語が！

斜め読みしちゃうと？となりそうですが、「複数のシングル・ブロック読取りの実行ではない」という箇所からもシングル・ブロック読み取りの繰り返しではなく、一括読み込み的なI/O最適化に関連した動き、だろうな〜ということは想像できます.
（db file scattered read と db file parallel read と db file sequential read (その６）でも簡単に記載しているので参考に）

連続したブロックを一括読取りするのは、 db file scattered read
単一ブロックをブロック単位で読み取るのは、 db file sequential read
そして、不連続な複数ブロックを一括読取りするのは、db file parallel read

不連続ってところもポイントですね！　連続してないんですよ！

そこで、db file scattered read と db file parallel read と db file sequential read (その１）に書いた赤字部分が鍵になってきます！

TABLE_NAME                     INDEX_NAME                       NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ------------- -----------------
HIGH_CLUSTERING_FACTOR         PK_HIGH_CLUSTERING_FACTOR          100000        100000             99978
LOW_CLUSTERING_FACTOR          PK_LOW_CLUSTERING_FACTOR           100000        100000              4348

CLUSTERING_FACTORってNUM_ROWSに近ければ近いほど、索引のキー順に行を読んでしまうと読んだ行数ど同じ程度のデータブロックを読み込む必要があるということを示しています。
（マニュアルにも書いてますよね。Oracle® Databaseパフォーマンス・チューニング・ガイド11gリリース2 (11.2) - 11.2.3.1 ブロックのI/O(行ではなく)の想定）

マニュアル、読みました？？　

ほんとに？　
（マニュアル読むの大切ですよ〜マニュアル読んで、試して、大きくなった、私が通りますよ〜〜〜と。）

では、細かい説明はもういいですよね！ (^^;;;

で、
INDEX RANGE SCANとなっている場合、CLUSTERING_FACTORが低い場合と高い場合では、読み込みブロック数に差がある、低い場合は、少ないブロック読み込みで済むが、高い場合はより多くのブロックを読み込む必要があるということになります :)

効率的なI/Oを目指すOracleさんなので....CLUSTERING_FACTORが高く..

select
	/*+
		leading(t2 t1)
		use_nl(t2 t1)
		index(t2 pk_high_clustering_factor)
	*/
	t2.id
	,t2.name
	,t1.name
from
	low_clustering_factor t1
	inner join high_clustering_factor t2
	on
		t1.id = t2.id
where
	t2.id between 30001 and 35000
/

こんな実行計画になっていて、かつ、physical reads prefetch warmupが発生していない状況で、バッファキャッシュヒット率が悪いと物理読み込みが発生して....

Rows     Row Source Operation
-------  ---------------------------------------------------
   2421  NESTED LOOPS  (cr=5252 pr=2841 pw=0 time=2771706 us)
   2421   NESTED LOOPS  (cr=2831 pr=2522 pw=0 time=4204941 us cost=5012 size=1530612 card=2501)
   2421    TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2589 pr=2458 pw=0 time=4188001 us cost=2510 size=765612 card=2502)
   2421     INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=168 pr=121 pw=0 time=7663 us cost=8 size=0 card=2502)(object id 82774)
   2421    INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=242 pr=64 pw=0 time=0 us cost=0 size=0 card=1)(object id 82772)
   2421   TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2421 pr=319 pw=0 time=0 us cost=1 size=306 card=1)

db file sequential read以外に、db file parallel readが発生するんですよね。

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     163        0.00          0.00
  SQL*Net message from client                   163        0.96          3.36
  db file parallel read                         161        0.01          0.63
  SQL*Net more data to client                   161        0.00          0.00
  db file sequential read                        14        0.00          0.00
********************************************************************************

INDEX RANGE SCANとなっている場合、CLUSTERING_FACTORが低い場合と高い場合では、読み込みブロック数に差がある、低い場合は、少ないブロック読み込みで済むが、高い場合はより多くのブロックを読み込む必要があるということになりますよね :)

と書きましたが、それを確認する簡単な実験を一つ。

バッファキャッシュヒット率が100%の状態でauto traceした結果です。
同一件数ヒットするクエリかつ、実行計画もINDEX RANGE SCANなのですが、
CLUSTERING_FACTORが低い表は、consistent getsが少なく、CLUSTERING_FACTORが高い表は、consistent getsが多くなるという差が発生しているのに気づきましたか？
（どちらの表も同じ定義の表なのですが、CLUSTERING_FACTORだけは変えてあります。）

注）CLUSTERING_FACTORが高い場合オプティマイザは索引を利用したアクセス効率が悪いと判断しTABLE FULL SCANを行う場合もあります。（以下の例ではヒントでINDEX RANGE SCANを強制しています）

select * from low_clustering_factor where id between 1000 and 3057;

2058行が選択されました。

経過: 00:00:00.09

実行計画
----------------------------------------------------------
Plan hash value: 2077556633

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                          |  2059 |   615K|    95   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LOW_CLUSTERING_FACTOR    |  2059 |   615K|    95   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | PK_LOW_CLUSTERING_FACTOR |  2059 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   2 - access("ID">=1000 AND "ID"<=3057)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        365  consistent gets
          0  physical reads
          0  redo size
     666586  bytes sent via SQL*Net to client
       1923  bytes received via SQL*Net from client
        139  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2058  rows processed

select /*+ INDEX_ASC(t1 pk_high_clustering_factor) */ * from high_clustering_factor t1 where id between 1000 and 5000;

2058行が選択されました。

経過: 00:00:00.10

実行計画
----------------------------------------------------------
Plan hash value: 3928373190

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |  2002 |   598K|  2009   (0)| 00:00:25 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HIGH_CLUSTERING_FACTOR    |  2002 |   598K|  2009   (0)| 00:00:25 |
|*  2 |   INDEX RANGE SCAN          | PK_HIGH_CLUSTERING_FACTOR |  2002 |       |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   2 - access("ID">=1000 AND "ID"<=5000)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2201  consistent gets
          0  physical reads
          0  redo size
     666587  bytes sent via SQL*Net to client
       1923  bytes received via SQL*Net from client
        139  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2058  rows processed

各SQL文実行時に物理I/Oが発生しCLUSTERING_FACTORが高ければ、db file parallel readが発生することに...

※SQLトレース（tkprofで整形済み）

SQL ID: ap97zhtgsmt34
Plan Hash: 2077556633
select *
from
 low_clustering_factor where id between 1000 and 3057


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      139      0.03       0.07         96        365          0        2058
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      141      0.03       0.07         96        365          0        2058

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
   2058  TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=365 pr=96 pw=0 time=94236 us cost=95 size=630054 card=2059)
   2058   INDEX RANGE SCAN PK_LOW_CLUSTERING_FACTOR (cr=143 pr=6 pw=0 time=3630 us cost=5 size=0 card=2059)(object id 82772)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     139        0.00          0.00
  db file sequential read                        96        0.00          0.05
  SQL*Net message from client                   139        0.02          1.09
********************************************************************************

SQL ID: fmynvkvbvrv49
Plan Hash: 3928373190
select /*+ INDEX_ASC(t1 pk_high_clustering_factor) */ *
from
 high_clustering_factor t1 where id between 1000 and 5000


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      139      0.28       1.85       1647       2201          0        2058
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      141      0.28       1.85       1647       2201          0        2058

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
   2058  TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2201 pr=1647 pw=0 time=1756678 us cost=2009 size=612612 card=2002)
   2058   INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=144 pr=6 pw=0 time=8099 us cost=7 size=0 card=2002)(object id 82774)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     139        0.00          0.00
  db file sequential read                      1594        0.01          1.66
  SQL*Net message from client                   139        0.02          1.68
  db file parallel read                           6        0.00          0.00
********************************************************************************

あ〜〜〜すっきり！　:）

みなさん、よいゴールデンウィークを！

---

バックナンバー

・db file scattered read と db file parallel read と db file sequential read (その１）
・db file scattered read と db file parallel read と db file sequential read (その２）
・db file scattered read と db file parallel read と db file sequential read (その３）
・db file scattered read と db file parallel read と db file sequential read (その４）
・db file scattered read と db file parallel read と db file sequential read (その５）
・db file scattered read と db file parallel read と db file sequential read (その６）
・db file scattered read と db file parallel read と db file sequential read (その７）
・db file scattered read と db file parallel read と db file sequential read (その８）