« db file scattered read と db file parallel read と db file sequential read (その8) | トップページ | TimeMachine応用編 »

2013年4月28日 (日)

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 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 (その6)でも簡単に記載しているので参考に)

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


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

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

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 (その1)
db file scattered read と db file parallel read と db file sequential read (その2)
db file scattered read と db file parallel read と db file sequential read (その3)
db file scattered read と db file parallel read と db file sequential read (その4)
db file scattered read と db file parallel read と db file sequential read (その5)
db file scattered read と db file parallel read と db file sequential read (その6)
db file scattered read と db file parallel read と db file sequential read (その7)
db file scattered read と db file parallel read と db file sequential read (その8)

|

トラックバック

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

この記事へのトラックバック一覧です: db file scattered read と db file parallel read と db file sequential read (最終回) :

コメント

コメントを書く