« 2013年3月 | トップページ | 2013年5月 »

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)

| | | コメント (0) | トラックバック (0)

2013年4月26日 (金)

db file scattered read と db file parallel read と db file sequential read (その8)

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

の予告通り、physical reads prefetch warmupを発生させてみます!

やったことをほぼそのまま書いておきますね:)

オラクルさん、ディクショナリーやらなんやらいろいろ読んでしまって、小さめのバッファキャッシュだとそれらでお腹いっぱいになったりするんだよねw (よく考えられてる:)


23:29:24 SYS> startup
ORACLEインスタンスが起動しました。

Total System Global Area 313880576 bytes
Fixed Size 1336176 bytes
Variable Size 125832336 bytes
Database Buffers 180355072 bytes
Redo Buffers 6356992 bytes
データベースがマウントされました。
データベースがオープンされました。
23:29:47 SYS> select sid,serial# from v$session where username='SCOTT';

SID SERIAL#
---------- ----------
63 11


23:30:06 SYS> select name,value from v$sesstat vss join v$statname vsn on vss.statistic# = vsn.statistic#
where (name like '%prefetch%' or name like '%physical read%') and sid=63 order by name
/
NAME VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch) 0
physical read IO requests 89
physical read bytes 729088
physical read flash cache hits 0
physical read requests optimized 0
physical read total IO requests 89
physical read total bytes 729088
physical read total multi block requests 0
physical reads 89
physical reads cache 89
physical reads cache prefetch 0
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トレース開始!


23:30:27 SYS> exec dbms_monitor.session_trace_enable(63,11,true,false,null);

PL/SQLプロシージャが正常に完了しました。


....この間に、db file * read祭りになるクエリをSCOTTユーザで実行します!....


※指定セッションのセッションレベルSQLトレース終了!


23:32:21 SYS> exec dbms_monitor.session_trace_disable(63,11);

PL/SQLプロシージャが正常に完了しました。


:) db file scattered 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.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 163 0.27 1.81 2364 5252 0 2421
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 165 0.28 1.82 2364 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=2364 pw=0 time=7618698 us)
2421 NESTED LOOPS (cr=2831 pr=2104 pw=0 time=8830835 us cost=5012 size=1530612 card=2501)
2421 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2589 pr=2070 pw=0 time=8807144 us cost=2510 size=765612 card=2502)
2421 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=168 pr=34 pw=0 time=3764 us cost=8 size=0 card=2502)(object id 82774)
2421 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=242 pr=34 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=260 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
Disk file operations I/O 1 0.00 0.00
db file sequential read 251 0.03 0.40
db file scattered read 37 0.02 0.23
SQL*Net message from client 163 0.02 1.71
SQL*Net more data to client 161 0.00 0.00
db file parallel read 160 0.08 1.07
********************************************************************************

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.24 0.77 1302 3367 0 2566
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 175 0.24 0.78 1302 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=1302 pw=0 time=913653 us)
2566 NESTED LOOPS (cr=801 pr=232 pw=0 time=324655 us cost=5231 size=1530000 card=2500)
5000 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=564 pr=226 pw=0 time=78984 us cost=229 size=1530000 card=5000)
5000 INDEX RANGE SCAN PK_LOW_CLUSTERING_FACTOR (cr=181 pr=8 pw=0 time=5896 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=1070 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 1302 0.00 0.62
SQL*Net message from client 172 0.01 1.66
SQL*Net more data to client 171 0.00 0.00

よかったよかった、うまく physical reads prefetch warmupが増加しててw (^^


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch) 0
physical read IO requests 3294
physical read bytes 30957568
physical read flash cache hits 0
physical read requests optimized 0
physical read total IO requests 3294
physical read total bytes 30957568
physical read total multi block requests 15
physical reads 3779
physical reads cache 3779
physical reads cache prefetch 1916
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 485
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 20
prefetch warmup blocks flushed out before use 0
prefetched blocks aged out before use 0


physical reads prefetch warmupってその名の通り、warmupなんですよね。バッファキャッシュの。

いつもより余計に読み込んでるからって驚かないでね!

おまけ。

起動直後には、SYS関連のオブジェクトがバッファキャッシュに読み込まれてしまって、physical read prefetch warmupが発生しないこともなんどかありました。

で、試しに、バッファキャッシュをクリアしてみたんですね...そしたら、warmupするじゃないですか!


23:15:00 SYS> startup
ORACLEインスタンスが起動しました。

Total System Global Area 313880576 bytes
Fixed Size 1336176 bytes
Variable Size 125832336 bytes
Database Buffers 180355072 bytes
Redo Buffers 6356992 bytes
データベースがマウントされました。
データベースがオープンされました。
23:15:18 SYS> select status,count(1) from v$bh group by status;

STATUS COUNT(1)
---------- ----------
xcur 5413
cr 165


※このとき既にバッファキャッシュはイッパイに! なにが乗っていたかというと..

SYS関連のオブジェクト...


OBJECT_NAME                    STATUS       COUNT(1)
------------------------------ ---------- ----------
ACCESS$ xcur 49
AQ$_MGMT_TASK_QTABLE_I xcur 2
AQ$_QTABLE_AFFINITIES_PK xcur 1
AQ$_QUEUES xcur 1
AQ$_QUEUES_CHECK xcur 1
AQ$_QUEUE_TABLES xcur 1
AQ$_QUEUE_TABLES_PRIMARY xcur 1
AQ$_QUEUE_TABLE_AFFINITIES xcur 2
AQ$_SCHEDULES xcur 1
AQ$_SUBSCRIBER_TABLE xcur 2
AQ$_SUBSCRIBER_TABLE_PRIMARY xcur 1
ARGUMENT$ xcur 11
ASSOC1 xcur 1
C_COBJ# xcur 45
C_FILE#_BLOCK# cr 4
C_FILE#_BLOCK# xcur 141
C_OBJ# xcur 159
C_OBJ#_INTCOL# xcur 319
C_TOID_VERSION# xcur 2821
C_TS# xcur 13
C_USER# xcur 6
DAM_CONFIG_PARAM$ xcur 2
DBMS_LOCK_ALLOCATED xcur 1
DEPENDENCY$ xcur 60
EDITION$ xcur 2
EMDW_TRACE_CONFIG_IDX_01 xcur 1

.... 以下略 ...


バッファキャッシュ、フラ〜〜〜ッシュ! ❤


23:15:35 SYS> alter system flush buffer_cache;

システムが変更されました。


※バッファキャッシュにはfreeができました。


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

STATUS COUNT(1)
---------- ----------
xcur 2
free 5576


※クエリ実行前のセッション統計


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch) 0
physical read IO requests 88
physical read bytes 720896
physical read flash cache hits 0
physical read requests optimized 0
physical read total IO requests 88
physical read total bytes 720896
physical read total multi block requests 0
physical reads 88
physical reads cache 88
physical reads cache prefetch 0
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


いつものクエリを実行.....

...中略...


※実行後、該当セッションのセッション統計を見てみると...


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch) 0
physical read IO requests 3026
physical read bytes 32858112
physical read flash cache hits 0
physical read requests optimized 0
physical read total IO requests 3026
physical read total bytes 32858112
physical read total multi block requests 27
physical reads 4011
physical reads cache 4011
physical reads cache prefetch 2376
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 985
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 65
prefetch warmup blocks flushed out before use 0
prefetched blocks aged out before use 0

バッファキャッシュクリアしても出ることあるんだね。


これでおしまい。  あ、一つ思い出したので、次回にでも。

つづく。




バックナンバー

・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)

| | | コメント (0) | トラックバック (0)

2013年4月24日 (水)

db file scattered read と db file parallel read と db file sequential read (その7)

前回、物理読み込みが発生しない状況を作り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 (その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)

| | | コメント (0) | トラックバック (0)

2013年4月14日 (日)

db file scattered read と db file parallel read と db file sequential read (その6)

続きです。

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が発生していないので :)
...キャッシュヒット率100%ならば発生しないってこと、ですよね。

試してみます!

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

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 (その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)

| | | コメント (0) | トラックバック (0)

2013年4月13日 (土)

db file scattered read と db file parallel read と db file sequential read (その5)

続きです。

@yoheiaさんがprefetch的な動き...と言っていますね。気になる気になる単語です :)

こことか、
http://www.oracle.com/technetwork/jp/content/007-087711-ja.html

ここなどにもちょろっと書かれていいます。
TANEL PODER'S BLOG: Advanced Oracle Troubleshooting Guide – Part 10: Index unique scan doing multiblock reads?!

マニュアルで記載されている箇所ってあるんでしたっけ? (誰となく..

v$sesstatから該当セッションのセッション統計から"phsical read"または"prefetch"を含む統計値を見みてみると...

SQL文の実行前と実行後を比較するとどの統計値が増加したか一目瞭然です。赤字部分にちゅうもーく!

Oracle® Databaseリファレンス11g リリース2 (11.2)を見てみると、

physical reads cache prefetch
- 事前にフェッチされた連続および不連続ブロックの数 / class - 8 : cache


physical reads prefetch warmup
- バッファ・キャッシュの自動プリウォーム中にディスクから読み取られたデータ・ブロックの数 / class - 8 : cache

と記載されています。どちらもprefetchと関連がありそうですね:)

physical reads cache prefetchの説明にもありますが、連続及び不連続ブロックという部分、待機イベントにも現れていますよね。

連続ブロックを物理読み込みする待機イベントと言えば、db file scatterd readですし、
不連続ブロックを一括読み込みするといえば、 db file parallel readですよね。:)

※SQL文実行前

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch) 0
physical read IO requests 48
physical read bytes 393216
physical read flash cache hits 0
physical read requests optimized 0
physical read total IO requests 48
physical read total bytes 393216
physical read total multi block requests 0
physical reads 48
physical reads cache 48
physical reads cache prefetch 0
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文実行後

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
index crx upgrade (prefetch) 0
physical read IO requests 4141
physical read bytes 42541056
physical read flash cache hits 0
physical read requests optimized 0
physical read total IO requests 4141
physical read total bytes 42541056
physical read total multi block requests 34
physical reads 5193
physical reads cache 5193
physical reads cache prefetch 4599
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 1052
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


ところで、prefetchもprefetch warmupも物理読み込みなのは分かりましたが、どのような状況だと発生するのでしょう? もしくは発生しやすいのでしょう?

そのヒントはその1に..

次回へつづく




バックナンバー

・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)

| | | コメント (0) | トラックバック (0)

2013年4月 8日 (月)

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 の続きです。

再びリクエストにお答えしてw、SQLとレースを有効した状態でstraceして該当待機イベントでどのようなsystem callになっているのか確認してみたよ > @yoheia :)


db file sequential read

WAIT #4: nam='db file sequential read' ela= 53584 file#=7 block#=375476 blocks=1 obj#=82773 tim=1365258291285100

....
pread64(17, "\6\242\0\0\372\267\5\0\\]\216\0\0\0\1\6\226y\0\0\1\0\0\0UC\1\0009]\216\0"..., 8192, 3070181376) = 8192

db file scattered read

WAIT #4: nam='db file scattered read' ela= 14462 file#=7 block#=374752 blocks=26 obj#=82773 tim=1365258291450333

....
pread64(17, "\6\242\0\0\340\267\5\0005]\216\0\0\0\1\6}\372\0\0\1\0\0\0UC\1\0002]\216\0"..., 212992, 3069968384) = 212992

db file parallel read

WAIT #4: nam='db file parallel read' ela= 25040 files=1 blocks=11 requests=11 obj#=82773 tim=1365258291641614

....
io_submit(1261568, 11, {{0x7981c8, 0, 0, 0, 17}, {0x79834c, 0, 0, 0, 17}, {0x7984d0, 0, 0, 0, 17}, {0x798654, 0, 0, 0, 17}, {0x7987d8, 0, 0, 0, 17}, {0x79895c, 0, 0, 0, 17}, {0x798ae0, 0, 0, 0, 17}, {0x798c64, 0, 0, 0, 17}, {0x798de8, 0, 0, 0, 17}, {0x798f6c, 0, 0, 0, 17}, {0x7990f0, 0, 0, 0, 17}}) = 11
io_getevents(1261568, 11, 128, {{0x79834c, 0x79834c, 8192, 0}, {0x7984d0, 0x7984d0, 8192, 0}, {0x7981c8, 0x7981c8, 8192, 0}, {0x798654, 0x798654, 8192, 0}, {0x7987d8, 0x7987d8, 8192, 0}, {0x798ae0, 0x798ae0, 8192, 0}, {0x798c64, 0x798c64, 8192, 0}, {0x798de8, 0x798de8, 8192, 0}, {0x7990f0, 0x7990f0, 8192, 0}, {0x798f6c, 0x798f6c, 8192, 0}, {0x79895c, 0x79895c, 8192, 0}}, {600, 0}) = 11

このdb file parallel readも狙って出せるくらいだから、たまたまそういう状況では発生しちゃうわけですよ。

以下のSQLトレースは、全てHIGH_CLUSTERING_FACTOR(表)に対する待機イベントですが、何が起きているか想像するとワクワクしてきませんか? (変態だな)

WAIT #4: nam='db file parallel read' ela= 10307 files=1 blocks=14 requests=14 obj#=82773 tim=1364725159669764 
WAIT #4: nam='db file scattered read' ela= 1931 file#=7 block#=374755 blocks=29 obj#=82773 tim=1364725159671819
WAIT #4: nam='db file parallel read' ela= 5874 files=1 blocks=14 requests=14 obj#=82773 tim=1364725159733551 
WAIT #4: nam='db file sequential read' ela= 337 file#=7 block#=374065 blocks=1 obj#=82773 tim=1364725159733959 

ということで、いろいろと、力尽きたので今日はここまで。

次回へつづく。



バックナンバー

・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)

| | | コメント (0) | トラックバック (0)

2013年4月 4日 (木)

db file scattered read と db file parallel read と db file sequential read (その3)

続きです。

リクエストにお答えして生SQLトレース(抜粋)を見てみましょう :)

最初に実行したSQL文のSQLトレースを見てみることにします。

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
/

実行計画は以下の通り、index range/unique scan + nested loop joinが行われていました。

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)

待機イベント見てみると、index range/unique scan + table access by index rowidという実行計画からは想像できない待機イベントが....(実は狙って発生させてるくせに〜w

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 scattered read 107 0.01 0.30
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
********************************************************************************

(生)SQLトレース抜粋

...中略...
PARSE #4:c=1000,e=1534,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1087412721,tim=1364725159601671
EXEC #4:c=1000,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1087412721,tim=1364725159601925
WAIT #4: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159602105 
WAIT #4: nam='db file scattered read' ela= 19499 file#=7 block#=371968 blocks=32 obj#=82774 tim=1364725159621743
WAIT #4: nam='db file scattered read' ela= 11156 file#=7 block#=373703 blocks=32 obj#=82774 tim=1364725159633042
WAIT #4: nam='db file scattered read' ela= 1699 file#=7 block#=375476 blocks=32 obj#=82773 tim=1364725159635032
WAIT #4: nam='db file scattered read' ela= 5889 file#=7 block#=367232 blocks=32 obj#=82772 tim=1364725159641300
WAIT #4: nam='db file scattered read' ela= 17223 file#=7 block#=368599 blocks=32 obj#=82771 tim=1364725159658667
FETCH #4:c=2999,e=56653,p=160,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=1087412721,tim=1364725159658788
WAIT #4: nam='SQL*Net message from client' ela= 430 driver id=1650815232 #bytes=1 p3=0 obj#=82771 tim=1364725159659311 
WAIT #4: nam='db file parallel read' ela= 10307 files=1 blocks=14 requests=14 obj#=82773 tim=1364725159669764 
WAIT #4: nam='db file scattered read' ela= 1931 file#=7 block#=374755 blocks=29 obj#=82773 tim=1364725159671819 
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159671902 
WAIT #4: nam='SQL*Net more data to client' ela= 40 driver id=1650815232 #bytes=8330 p3=0 obj#=82773 tim=1364725159672223
FETCH #4:c=2000,e=12910,p=43,cr=38,cu=0,mis=0,r=15,dep=0,og=1,plh=1087412721,tim=1364725159672272
WAIT #4: nam='SQL*Net message from client' ela= 9227 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159681536 
WAIT #4: nam='db file parallel read' ela= 5766 files=1 blocks=12 requests=12 obj#=82773 tim=1364725159687404 
WAIT #4: nam='db file scattered read' ela= 1922 file#=7 block#=376416 blocks=32 obj#=82773 tim=1364725159689681
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159689951 
WAIT #4: nam='SQL*Net more data to client' ela= 13 driver id=1650815232 #bytes=8278 p3=0 obj#=82773 tim=1364725159690473
FETCH #4:c=4000,e=8972,p=44,cr=32,cu=0,mis=0,r=15,dep=0,og=1,plh=1087412721,tim=1364725159690547
WAIT #4: nam='SQL*Net message from client' ela= 7980 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159698617 
WAIT #4: nam='db file parallel read' ela= 2716 files=1 blocks=13 requests=13 obj#=82773 tim=1364725159701431 
WAIT #4: nam='db file scattered read' ela= 2179 file#=7 block#=374240 blocks=32 obj#=82773 tim=1364725159703739 
WAIT #4: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159704315 
WAIT #4: nam='SQL*Net more data to client' ela= 40 driver id=1650815232 #bytes=8278 p3=0 obj#=82773 tim=1364725159704541 
FETCH #4:c=3999,e=5937,p=45,cr=32,cu=0,mis=0,r=15,dep=0,og=1,plh=1087412721,tim=1364725159704589
WAIT #4: nam='SQL*Net message from client' ela= 11759 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159716396 
WAIT #4: nam='db file parallel read' ela= 1999 files=1 blocks=13 requests=13 obj#=82773 tim=1364725159718604 
WAIT #4: nam='db file scattered read' ela= 1667 file#=7 block#=374066 blocks=32 obj#=82773 tim=1364725159720394 
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159720497 
WAIT #4: nam='SQL*Net more data to client' ela= 43 driver id=1650815232 #bytes=8278 p3=0 obj#=82773 tim=1364725159720707 
FETCH #4:c=1000,e=4261,p=45,cr=32,cu=0,mis=0,r=15,dep=0,og=1,plh=1087412721,tim=1364725159720752
WAIT #4: nam='SQL*Net message from client' ela= 6700 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159727511 
WAIT #4: nam='db file parallel read' ela= 5874 files=1 blocks=14 requests=14 obj#=82773 tim=1364725159733551 
WAIT #4: nam='db file sequential read' ela= 337 file#=7 block#=374065 blocks=1 obj#=82773 tim=1364725159733959 
WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=82773 tim=1364725159734041 
WAIT #4: nam='SQL*Net more data to client' ela= 77 driver id=1650815232 #bytes=8278 p3=0 obj#=82773 tim=1364725159734271 
...以下略...


db file * read待機イベントとアクセスブロック数及び参照オブジェクトとオブジェクト名を見やすくすると以下のようになります。
db_file_multiblock_read_count=32なのでscatterd readのblocksは理解できるのですが、parallel readのblocksってどこで制御してるんだ..わからんw..というか調べきれてない..

db file scattered read  blocks=32 (INDEX:PK_HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=32 (INDEX:PK_HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=32 (TABLE:HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=32 (INDEX:PK_LOW_CLUSTERING_FACTOR)
db file scattered read blocks=32 (TABLE:LOW_CLUSTERING_FACTOR)
db file parallel read blocks=14 (TABLE:HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=29 (TABLE:HIGH_CLUSTERING_FACTOR)
db file parallel read blocks=12 (TABLE:HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=32 (TABLE:HIGH_CLUSTERING_FACTOR)
db file parallel read blocks=13 (TABLE:HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=32 (TABLE:HIGH_CLUSTERING_FACTOR)
db file parallel read blocks=13 (TABLE:HIGH_CLUSTERING_FACTOR)
db file scattered read blocks=32 (TABLE:HIGH_CLUSTERING_FACTOR)
db file parallel read blocks=14 (TABLE:HIGH_CLUSTERING_FACTOR)
db file sequential read blocks=1 (TABLE:HIGH_CLUSTERING_FACTOR)

発生しているdb file * read待機イベントでアクセスしているオブジェクトは以下の通り。
実行計画上 index unique/range scan なのですが、索引でもscatterd readが発生することがあるんですよ〜っ。

db file parallel read
- obj# = 82773 - HIGH_CLUSTERING_FACTOR(表)

db file scattered read
- obj# = 82771 - LOW_CLUSTERING_FACTOR(表)
- obj# = 82772 - PK_LOW_CLUSTERING_FACTOR(索引)
- obj# = 82773 - HIGH_CLUSTERING_FACTOR(表)
- obj# = 82774 - PK_HIGH_CLUSTERING_FACTOR(索引)

db file sequential read
- obj# = 82771 - LOW_CLUSTERING_FACTOR(表)
- obj# = 82773 - HIGH_CLUSTERING_FACTOR(表)


2つ目のクエリは db file parallel readが発生しないだけで傾向は同じです。 :)

次回へつづく。



バックナンバー

・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)

| | | コメント (0) | トラックバック (0)

2013年4月 1日 (月)

db file scattered read と db file parallel read と db file sequential read (その2)

答え分かりましたかねぇ?

環境情報など再現方法などは次回にしますが、どうなるかやってみましょう。(事前にSQLトレースを有効にしてあります。)


※各SQLはヒントで実行計画を固定してあります。
最初は参考資料3の実行計画になるクエリから実行します。

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2501 | 1494K| 5012 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2501 | 1494K| 5012 (1)| 00:01:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| HIGH_CLUSTERING_FACTOR | 2502 | 747K| 2510 (1)| 00:00:31 |
|* 4 | INDEX RANGE SCAN | PK_HIGH_CLUSTERING_FACTOR | 2502 | | 8 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_LOW_CLUSTERING_FACTOR | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | LOW_CLUSTERING_FACTOR | 1 | 306 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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
/

次は参考資料2の実行計画になるクエリを実行します。

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 1494K| 5231 (1)| 00:01:03 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2500 | 1494K| 5231 (1)| 00:01:03 |
| 3 | TABLE ACCESS BY INDEX ROWID| LOW_CLUSTERING_FACTOR | 5000 | 1494K| 229 (0)| 00:00:03 |
|* 4 | INDEX RANGE SCAN | PK_LOW_CLUSTERING_FACTOR | 5000 | | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_HIGH_CLUSTERING_FACTOR | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | HIGH_CLUSTERING_FACTOR | 1 | 306 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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 BETWEEM 1 AND 5000
/

どのような待機イベントが発生しているかSQLトレース結果(tkprofで整形済み)を見てみると...

最初のクエリのトレース結果

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.30 1.06 2841 5252 0 2421
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 165 0.30 1.06 2841 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=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)


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 scattered read 107 0.01 0.30
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

2つ目のクエリのトレース結果

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.28 0.72 1874 3367 0 2566
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 175 0.28 0.73 1874 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=1874 pw=0 time=802588 us)
2566 NESTED LOOPS (cr=801 pr=381 pw=0 time=115791 us cost=5231 size=1530000 card=2500)
5000 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=564 pr=286 pw=0 time=42241 us cost=229 size=1530000 card=5000)
5000 INDEX RANGE SCAN PK_LOW_CLUSTERING_FACTOR (cr=181 pr=32 pw=0 time=26744 us cost=11 size=0 card=5000)(object id 82772)
2566 INDEX UNIQUE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=237 pr=95 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=1493 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 scattered read 381 0.01 0.42
db file sequential read 287 0.00 0.14
SQL*Net message from client 172 0.04 2.83
SQL*Net more data to client 171 0.00 0.00


ということで、

A) db file scattered read
B) db file parallel read
C) db file sequential read

A)、B)、C)すべての待機イベント発生しました!!! :)  

実に興味深い。。。。 実行計画見てても、分からないですよね、こればかりは。


次回へ続く。




バックナンバー
・db file scattered read と db file parallel read と db file sequential read (その1)

| | | コメント (0) | トラックバック (0)