db file scattered read と db file parallel read と db file sequential read (その６） Tweet

続きです。

system callからも読み取れますが、各待機イベントでは
db file sequential read - 単一ブロック読み込み
db file parallel read - 不連続ブロックの複数ブロックの読み込み
db file scattered read - 連続したブロックの複数ブロック読み込み

が行われ、読み込まれたブロックはバッファキャッシュに乗せられるわけです。

注）
今回は発生しないようにしていますが、Oracle11g R2では実行計画上フルスキャンとなっていても、はdb file scattered readまたはdirect path read（バッファキャッシュを経由しない)になる場合があります。(smart flash cacheが無い場合）

db file scattered read vs direct path read関連の話題は以下のブログを参考に :)
10046 trace name context forever - Oracle OpenWorld 2012 Unconference presented by JPOUG
10046 trace name context forever - Smart Flash Cache 簡単なパフォーマンス比較
技術情報| Insight Techology, Inc. Part 7 - 11gR2からのフルスキャン
Ask Tom "Direct path reads 11gR2" - Thanks for the question regarding "Direct path reads 11gR2", version 11.2.0.1

direct path readが発生していないので :)
...キャッシュヒット率１００％ならば発生しないってこと、ですよね。

試してみます！

バッファキャッシュには以下のSQL文でアクセスする表以外のオブジェクトも含め事前に乗せてあります。
以下SQL文では表、索引ともバッファキャッシュヒット率１００％になるようにしてあります。

v$bhの状態（STATUS=freeがないので空きブロックの無い状態）

STATUS       COUNT(1)
---------- ----------
xcur             8904
cr                 21

SQLトレースを見ると物理読み込みは発生していないこと、db file * read待機イベントが発生していないことが確認できます！

SQLトレース（tkprofにて整形後）

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

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      163      0.06       0.07          0       5252          0        2421
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      165      0.06       0.07          0       5252          0        2421

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

Rows     Row Source Operation
-------  ---------------------------------------------------
   2421  NESTED LOOPS  (cr=5252 pr=0 pw=0 time=63861 us)
   2421   NESTED LOOPS  (cr=2831 pr=0 pw=0 time=67887 us cost=5012 size=1530612 card=2501)
   2421    TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2589 pr=0 pw=0 time=51456 us cost=2510 size=765612 card=2502)
   2421     INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=168 pr=0 pw=0 time=7260 us cost=8 size=0 card=2502)(object id 82774)
   2421    INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=242 pr=0 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=0 pw=0 time=0 us cost=1 size=306 card=1)


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.02          2.51
  SQL*Net more data to client                   161        0.00          0.00
********************************************************************************

select
	/*+
		leading(t1 t2)
		use_nl(t1 t2)
		index(t1 pk_low_clustering_factor)
	*/
	t1.id
	,t1.name
	,t2.name
from
	low_clustering_factor t1
	inner join high_clustering_factor t2
	on
		t1.id = t2.id
where
	t1.id between 1 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      173      0.04       0.05          0       3367          0        2566
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      175      0.04       0.05          0       3367          0        2566

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

Rows     Row Source Operation
-------  ---------------------------------------------------
   2566  NESTED LOOPS  (cr=3367 pr=0 pw=0 time=44887 us)
   2566   NESTED LOOPS  (cr=801 pr=0 pw=0 time=32245 us cost=5231 size=1530000 card=2500)
   5000    TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=564 pr=0 pw=0 time=17496 us cost=229 size=1530000 card=5000)
   5000     INDEX RANGE SCAN PK_LOW_CLUSTERING_FACTOR (cr=181 pr=0 pw=0 time=3624 us cost=11 size=0 card=5000)(object id 82772)
   2566    INDEX UNIQUE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=237 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 82774)
   2566   TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2566 pr=0 pw=0 time=0 us cost=1 size=306 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     173        0.00          0.00
  SQL*Net message from client                   172        0.07          2.01
  SQL*Net more data to client                   171        0.00          0.00

db file * read祭りだった際には明らかに増加していた physical reads prefetch warmupやphysical reads cache prefetchも含め、
物理読み込みが発生しない状況であれば、それらの待機イベントも発生しないのは当然と言えば当然ですよね。 :)

SQL実行前のシステム統計(physical readとprefetch関連のみ）


---------------------------------------------------------------- ----------
index crx upgrade (prefetch)                                              0
physical read IO requests                                              2201
physical read bytes                                                18030592
physical read flash cache hits                                            0
physical read requests optimized                                          0
physical read total IO requests                                        2201
physical read total bytes                                          18030592
physical read total multi block requests                                  0
physical reads                                                         2201
physical reads cache                                                   2201
physical reads cache prefetch                                           741
physical reads direct                                                     0
physical reads direct (lob)                                               0
physical reads direct temporary tablespace                                0
physical reads for flashback new                                          0
physical reads prefetch warmup                                            0
physical reads retry corrupt                                              0
prefetch clients - 16k                                                    0
prefetch clients - 2k                                                     0
prefetch clients - 32k                                                    0
prefetch clients - 4k                                                     0
prefetch clients - 8k                                                     0
prefetch clients - default                                                0
prefetch clients - keep                                                   0
prefetch clients - recycle                                                0
prefetch warmup blocks aged out before use                                0
prefetch warmup blocks flushed out before use                             0
prefetched blocks aged out before use                                     0

SQL文実行後のシステム統計(physical readとprefetch関連のみ）

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch)                                              0
physical read IO requests                                              2201
physical read bytes                                                18030592
physical read flash cache hits                                            0
physical read requests optimized                                          0
physical read total IO requests                                        2201
physical read total bytes                                          18030592
physical read total multi block requests                                  0
physical reads                                                         2201
physical reads cache                                                   2201
physical reads cache prefetch                                           741
physical reads direct                                                     0
physical reads direct (lob)                                               0
physical reads direct temporary tablespace                                0
physical reads for flashback new                                          0
physical reads prefetch warmup                                            0
physical reads retry corrupt                                              0
prefetch clients - 16k                                                    0
prefetch clients - 2k                                                     0
prefetch clients - 32k                                                    0
prefetch clients - 4k                                                     0
prefetch clients - 8k                                                     0
prefetch clients - default                                                0
prefetch clients - keep                                                   0
prefetch clients - recycle                                                0
prefetch warmup blocks aged out before use                                0
prefetch warmup blocks flushed out before use                             0
prefetched blocks aged out before use                                     0

物理読み込みが発生しない状況を作りdb file * read関連の物理読み込みが発生しない状況で、physical reads prefetch warmup/cache prefetchが発生していないことは確認しました。

次回はphysical reads prefetch warmupってどんな時に発生しやすいのか確認してみます。その際、発生する待機イベントってなんだろう？ (もうわかってるでしょ、でしょ？）

つづく

”SSD”、”SSD”、ってプロジェクトでお金握ってる人の耳元で毎日つぶやいていれば、

いつの日か、「あ、そうだ！　SSDがあるじゃまいか」と、自分から言うようになるんじゃないかなと思っているんだが、だれか実践してくれないかなw　

---

バックナンバー

・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 (その５）