« 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 (最終回) »

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)

|

トラックバック

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

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

コメント

コメントを書く