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

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

ということで、続きです。

physical reads prefetch warmupってどのような状況で増加するのか、待機イベントはどのようなものなのか確認してみることに..... (もうわかってるくせに〜w）

physical reads prefetch warmupは以下のように説明されています。
- バッファ・キャッシュの自動プリウォーム中にディスクから読み取られたデータ・ブロックの数 / class - 8 : cache

また、津島博士のパフォーマンス講座　第13回　キャッシュ周りについて でも説明されています。（この手の解説をマニュアルで読んだ記憶がないが、どこかに記載されてるのかな？....)

では、どのような待機イベントが発生するのか...
試してガッテン、じゃなくて、試して納得！でおなじみの、しばちょう先生のセッションの資料(ODDD2011)によると、db file scattered readが発生するようですね。

なるほど....どの程度かわかりませんが、バッファキャッシュにある程度データブロックが乗っかっているとphysical reads prefetch warmupって発生しないんだろうなぁ。というところまでは、なんとなく理解できました。:)

さっそく、physical reads prefetch warmupを発生させてみましょう！...

と、その前に、physical reads prefetch warmupは発生させず、physical reads cache prefetchだけを発生させてみますw

バッファキャッシュは本検証で実行するクエリでアクセスする表や索引以外のブロックで満たしてあります。

v$bhの状態


SYS> select status,count(1) from v$bh group by status;

STATUS       COUNT(1)
---------- ----------
xcur             8903
cr                 20

という状況(物理読み込みが発生しない状況）でdb file * read祭りになるクエリを実行してみることにします。

クエリを実行する前のセッション統計(prefetch関連のみ）は以下の通り。


22:04:51 SYS> l
  1  select
  2     name
  3     ,value
  4  from
  5     v$sesstat vss
  6     join v$statname vsn
  7     on
  8             vss.statistic# = vsn.statistic#
  9  where
 10     (
 11             name like '%prefetch%'
 12             or name like '%physical read%'
 13     )
 14     and sid=195
 15  order by
 16*    name
22:04:52 SYS> /


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch)                                              0
physical read IO requests                                               479
physical read bytes                                                 7012352
physical read flash cache hits                                            0
physical read requests optimized                                          0
physical read total IO requests                                         479
physical read total bytes                                           7012352
physical read total multi block requests                                  2
physical reads                                                          856
physical reads cache                                                    856
physical reads cache prefetch                                           377
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                               10
prefetch warmup blocks flushed out before use                             0
prefetched blocks aged out before use                                     0

※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.37       1.46       2084       5252          0        2421
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      165      0.38       1.47       2084       5252          0        2421

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

Rows     Row Source Operation
-------  ---------------------------------------------------
   2421  NESTED LOOPS  (cr=5252 pr=2084 pw=0 time=898088 us)
   2421   NESTED LOOPS  (cr=2831 pr=1866 pw=0 time=2127562 us cost=5012 size=1530612 card=2501)
   2421    TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2589 pr=1855 pw=0 time=2109985 us cost=2510 size=765612 card=2502)
   2421     INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=168 pr=8 pw=0 time=14251 us cost=8 size=0 card=2502)(object id 82774)
   2421    INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=242 pr=11 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=218 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
  db file sequential read                       624        0.01          0.84
  SQL*Net message from client                   163        0.02          2.22
  SQL*Net more data to client                   161        0.00          0.00
  db file parallel read                         141        0.02          0.42
********************************************************************************

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.31       0.85       1383       3367          0        2566
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      175      0.31       0.85       1383       3367          0        2566

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

Rows     Row Source Operation
-------  ---------------------------------------------------
   2566  NESTED LOOPS  (cr=3367 pr=1383 pw=0 time=1180156 us)
   2566   NESTED LOOPS  (cr=801 pr=233 pw=0 time=340045 us cost=5231 size=1530000 card=2500)
   5000    TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=564 pr=227 pw=0 time=98730 us cost=229 size=1530000 card=5000)
   5000     INDEX RANGE SCAN PK_LOW_CLUSTERING_FACTOR (cr=181 pr=9 pw=0 time=7998 us cost=11 size=0 card=5000)(object id 82772)
   2566    INDEX UNIQUE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=237 pr=6 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=1150 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
  db file sequential read                      1383        0.01          0.66
  SQL*Net message from client                   172        0.02          2.24
  SQL*Net more data to client                   171        0.00          0.00

実行後のセッション統計を見てみると....おおおおおおおお！

physical reads cache prefetchは増加しているのでprefetchは発生していますが、physical reads prefetch warmupは発生していません。


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch)                                              0
physical read IO requests                                              3963
physical read bytes                                                35553280
physical read flash cache hits                                            0
physical read requests optimized                                          0
physical read total IO requests                                        3963
physical read total bytes                                          35553280
physical read total multi block requests                                  2
physical reads                                                         4340
physical reads cache                                                   4340
physical reads cache prefetch                                          1696
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                               10
prefetch warmup blocks flushed out before use                             0
prefetched blocks aged out before use                                     0

physical reads prefetch warmupとphysical reads cache prefetchが同時に増加していた時に比べ、発生している待機イベントに違いがあることに気づきましたか？　:)　そう、db file scattered readが発生していないんですよ！

次回はphysical reads prefetch warmupを発生させます。（引っ張り過ぎw

ところで、prefetch warmup blocks aged out before useという統計値が増加していますよね。
キャッシュバッファを暖めただけで利用されずに捨てられたデータブロック数をカウントしているようです。（マニュアルに記載されていないので、想像ですが）

悲しいですね、使われずに捨てられるなんて。

---

バックナンバー

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