さて、うまく再現できるかどうか。。(今回もやったことをほぼすべて載せているので長いです）
0) 対象表をdrop/create
オブジェクトを作り直し前提合せ
SCOTT@orcl> @droppurge_create_hogeTable dropped.Table created.SCOTT@orcl> select segment_name,blocks from user_segments where segment_name like '%HOGE%';no rows selected
|
1) 統計をクリアためOracle再起動
$ sudo service oracle restart
|
2) PDBのscottでログインしてclient_infoをセット
v$sessionのclient_info列の'TargetSession'文字列で他のSCOTTユーザーのセッションを特定するため。
SCOTT@orcl> @set_client_info 1 begin 2 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TargetSession'); 3* end;PL/SQL procedure successfully completed.Elapsed: 00:00:00.00SCOTT@orcl>
|
3) CDBのSYSで統計取得（初回）
内容は省略！（ベースラインを取得しているだけなので）
SYS$orclcdb> @show_stat scott
...略...
|
4) PDBのSCOTTユーザーでINSERT（データ量2倍、コミットなし）
データサイズはバッファキャッシュに載るサイズ、コミットクリーンアウトではクリーンアウト仕切れないサイズで、ある程度の遅延ブロッククリーンアウトが発生するサイズになっているのは以前と同じ。
SCOTT@orcl> @insert_each_rows_2 1* begin for i in 1..200000 loop insert into hoge values(i, lpad('*', 2000, '*')); end loop; end;PL/SQL procedure successfully completed.Elapsed: 00:00:29.48SCOTT@orcl>
|
5) CDBのSYSで統計取得（INSERT後、未コミット）
insertしただけなので、insertしたデータ量に応じたブロック数がバッファキャッシュに確保されたという程度の情報( free buffer requested = 68766 なので事前に確認していた 66667 以上になっています)は確認できます。
（バッファキャッシュに収まるデータ量ですし）ただ、checkpointの発生でいくらか書き出されているのも見えますよね( DBWR checkpoint buffers written )
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat DBWR checkpoint buffers written 22909sysstat DBWR checkpoints 3sysstat DBWR thread checkpoint buffers written 22902sysstat DBWR transaction table writes 6sysstat DBWR undo block writes 573sysstat commit cleanouts 6sysstat commit cleanouts successfully completed 6sysstat consistent gets 49682sysstat db block changes 744727sysstat deferred (CURRENT) block cleanout applications 4sysstat free buffer requested 68766sysstat immediate (CURRENT) block cleanout applications 1sysstat no work - consistent read gets 82sysstat physical reads 9sysstat physical writes 22909sysstat physical writes from cache 22909sysstat physical writes non checkpoint 22892
|
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat commit cleanouts 6sesstat commit cleanouts successfully completed 6sesstat consistent gets 49561sesstat db block changes 744727sesstat deferred (CURRENT) block cleanout applications 4sesstat free buffer requested 68766sesstat immediate (CURRENT) block cleanout applications 1sesstat no work - consistent read gets 40sesstat physical reads 9
|
6) PDBのSCOTTユーザーでコミットの実行
SCOTT@orcl> commit;
Commit complete.
|
7) CDBのSYSで統計取得（コミット後）
コミットクリーンアウトされているブロック数を見ると、事前に計算していた バッファキャッシュの13%( 55112 blocks )に近い 55700 ブロックがコミットのタイミングでクリーンアウトされていることがわかります。ここまでは想定通りの動きです。
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat commit cleanout failures: callback failure 15sysstat commit cleanouts 56077sysstat commit cleanouts successfully completed 56062sysstat consistent gets 50494sysstat db block changes 7894sysstat deferred (CURRENT) block cleanout applications 158sysstat free buffer requested 3217sysstat immediate (CURRENT) block cleanout applications 154sysstat no work - consistent read gets 32764sysstat physical reads 2962
|
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat commit cleanouts 55700sesstat commit cleanouts successfully completed 55700sesstat db block changes 1
|
8) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表をdirect path readで全表走査）
全表走査させてコミットクリーンアウトされなかったブロックがクリーンアウトを確認します。ただし、全表走査ではありますが、direct path read で読み込ませるように工夫しています。
さて狙い通りになるかどうか。。。パラレルクエリーでない場合の強制はちょいとむずいのですが、見る限り、REDOログは生成されています。
ただ、以前のscattered read ( db file sequential read )で発生させた遅延ブロッククリーンアウトの検証結果に比べると明らかに少ない。。
なにかが違いますね。。。。むむむ。なんだろう？
scattered readでほぼ同じバッファキャッシュサイズで、遅延ブロッククリーンアウトさせた際、967432 redo size というサイズが生成されていたのを思い出してみてください！！！ 明らかに少ないです。。。。
SCOTT@orcl> @table_full_scan_with_dpr.sql 1* alter session set "_serial_direct_read" = alwaysSession altered.Elapsed: 00:00:00.00 1* alter session set "_very_large_object_threshold" = 512Session altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge200000 rows selected.Elapsed: 00:00:10.84Execution Plan----------------------------------------------------------Plan hash value: 2339479017--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 214K| 207M| 18223 (1)| 00:00:01 || 1 | TABLE ACCESS FULL| HOGE | 214K| 207M| 18223 (1)| 00:00:01 |--------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 23 recursive calls 13 db block gets 113559 consistent gets 66712 physical reads 2996 redo size 4539159 bytes sent via SQL*Net to client 147264 bytes received via SQL*Net from client 13335 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 200000 rows processedset autot off
|
9) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）（対象表をdirect path readで全表走査）
direct path read、遅延ブロッククリーンアウトの有無をシステム統計およびセッション統計から読み取ってみます！
物理読み込み( physical writes )とダイレクトパスリードを示す( physical writes from cache )が同じであることから、間違いなく direct path readが発生しています。ブロック数も 66667 ブロックを超えていることは確認できます。
ただ、immediate (CR) block cleanout applications が想定している量の3倍ぐらいあります:)
遅延ブロッククリーンアウトは行われているのは間違いないですが、前述の通りREDOサイズが異常に少ない。どういうことだろう。。（想定通りの結果に、ニヤニヤなわけですがw）
真相を探るため、われわれはアマゾンの奥深くへ入っていくのであった。。。W
少々本題からそれますが、DBWR parallel query checkpoint buffers written で 44793 ブロックほど書き出されています。これが発生するのは direct path readの影響です。direct path read バッファキャッシュを介ず、常にストレージからデータを読み込む必要があります。この検証では、INSERTでバッファキャッシュに載っているデータであるため一旦書き出す必要があります。書き出されたデータを direct path read で読み込むのでこんな動きになっているというわけですね。。。。。
これ、よくよく考えると、コミットクリーンアウトされていないブロックもそのままの状態で書き出されてますよね。。。ここ試験にでますよ（嘘ですw
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat DBWR checkpoint buffers written 44793sysstat DBWR checkpoints 2sysstat DBWR object drop buffers written 2sysstat DBWR parallel query checkpoint buffers written 44793sysstat cleanouts only - consistent read gets 33057sysstat commit cleanouts 18sysstat commit cleanouts successfully completed 18sysstat consistent gets 120519sysstat db block changes 97sysstat deferred (CURRENT) block cleanout applications 8sysstat free buffer requested 127sysstat immediate (CR) block cleanout applications 33057sysstat immediate (CURRENT) block cleanout applications 5sysstat no work - consistent read gets 50145sysstat physical reads 66827sysstat physical reads direct 66710sysstat physical writes 44795sysstat physical writes from cache 44795sysstat physical writes non checkpoint 44795
|
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat cleanouts only - consistent read gets 33057sesstat commit cleanouts 5sesstat commit cleanouts successfully completed 5sesstat consistent gets 120131sesstat db block changes 48sesstat deferred (CURRENT) block cleanout applications 1sesstat free buffer requested 120sesstat immediate (CR) block cleanout applications 33057sesstat immediate (CURRENT) block cleanout applications 2sesstat no work - consistent read gets 49952sesstat physical reads 66823sesstat physical reads direct 66710
|
10) Oracle再起動
Oracleを再起動して、諸々綺麗にした状態で、今一度、direct path readで全表走査させてみましょう。
$ sudo service oracle restart
|
11) PDBのscottでログインしてclient_infoをセット
v$sessionのclient_info列の'TargetSession'文字列で他のSCOTTユーザーのセッションを特定するため。
SCOTT@orcl> @set_client_info 1 begin 2 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TargetSession'); 3* end;PL/SQL procedure successfully completed.Elapsed: 00:00:00.00SCOTT@orcl>
|
12) CDBのSYSで統計取得（再起動後初回）
内容は省略！（ベースラインを取得しているだけなので）
SYS$orclcdb> @show_stat scott
...略...
|
13) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表をdirect path readで全表走査）2回目
お！！！ REDOが生成されていないですね。Scattered Readの場合でも、コミット時でも一度クリーンアウトされたブロックはクリーンアウト済みなので、クリーンアウトされるような挙動は発生しませんでしたが、 direct path read でもおなじかなーー。
と。。。。とりあえず、システム統計とセッション統計も確認しておきましょう！
SCOTT@orcl> @table_full_scan_with_dpr.sql 1* alter session set "_serial_direct_read" = alwaysSession altered.Elapsed: 00:00:00.00 1* alter session set "_very_large_object_threshold" = 512Session altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge200000 rows selected.Elapsed: 00:00:03.90Execution Plan----------------------------------------------------------Plan hash value: 2339479017--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 214K| 207M| 18223 (1)| 00:00:01 || 1 | TABLE ACCESS FULL| HOGE | 214K| 207M| 18223 (1)| 00:00:01 |--------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 33 recursive calls 0 db block gets 91159 consistent gets 66938 physical reads 0 redo size 4539159 bytes sent via SQL*Net to client 147264 bytes received via SQL*Net from client 13335 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 200000 rows processedset autot off
|
14) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）（対象表をdirect path readで全表走査）2回目
セッション統計のphysical readsとhysical reads direct は同一であることから direct path readになっていることは間違いありません。また、ブロック数も 66667 ブロック以上にはなっているので全ブロック読み込まれているようですね。
ただ、妙な値を示している統計があります。
immediate (CR) block cleanout applications 10959
|
遅延ブロッククリーンアウトが行われている時に上がる統計です。しかも、コミットクリーンアウトされたブロック数を差し引いたブロック数にほぼ一致します。（1回目の実行ではこの3倍ぐらいに跳ね上がっていましたが。。一度クリーンアウトされたのでは？？）
さらに不思議なことに、REDO生成されないんですね。。。。
ん？ ちょっと待ってください。一度、クリーンアウトされたブロックがなぜ、再度クリーンアウトされているのでしょう？ scattered readで遅延ブロッククリーンアウトされたケースと動きが違います！！！！！！
Oracleを再起動する前のステップでREDOログが異常に少ないにも関わらず、遅延クリーンアウトされていた統計値が高くなった。Oracleを再起動した後でも、同様に、direct path read で読み込み、遅延ブロッククリーンアウト発生。しかもREDOログはありません。。。これって、クリーンアウト行われているようですが、実際にはメモリー上だけで実祭のブロックはクリーンアウトされずに残っているということですよね。なんども発生しているわけですから。
（ブロックダンプしなくても統計値から状況は見えてきましたよね！！）
direct path readはその名の通り、バッファキャッシュを介さず、常にストレージからデータブロックを読み込み、PGAへ。このケースだとSELECT文なので単純にPGAへ直接読み込み、メモリ上ではクリーンアップは行なっているようですが、クエリーが終了すれば、単に捨てられるのみ。。。なので、クリーンアウトの結果は永続化されない。。。ということになりますよね！
ということは、SELECT文の場合は、scattered read等でバッファキャッシュを経由させないと、遅延ブロッククリーンアウトは、ずーっと先延ばしされる。。。direct path readのSELECT文を2回実行してクリーンアウトさせたわけだが、この後、scattered readで全表走査させれば、遅延ブロッククリーンアウトが発生して、大量のREDOログが生成されるて、完全にクリーンアウトされる。。。。。。はず。。。ですよね。
試してみよう！！！！
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat DBWR checkpoints 1sysstat DBWR object drop buffers written 2sysstat cleanouts only - consistent read gets 10959sysstat commit cleanouts 36sysstat commit cleanouts successfully completed 36sysstat consistent gets 106766sysstat db block changes 166sysstat deferred (CURRENT) block cleanout applications 18sysstat free buffer requested 1130sysstat immediate (CR) block cleanout applications 10959sysstat immediate (CURRENT) block cleanout applications 10sysstat no work - consistent read gets 77392sysstat physical reads 67827sysstat physical reads direct 66710sysstat physical writes 2sysstat physical writes from cache 2sysstat physical writes non checkpoint 2
|
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat cleanouts only - consistent read gets 10959sesstat commit cleanouts 2sesstat commit cleanouts successfully completed 2sesstat consistent gets 98223sesstat db block changes 30sesstat deferred (CURRENT) block cleanout applications 1sesstat free buffer requested 587sesstat immediate (CR) block cleanout applications 10959sesstat no work - consistent read gets 72324sesstat physical reads 67289sesstat physical reads direct 66710
|
15) Oracle再起動
諸々情報を綺麗にするので再起動！！
$ sudo service oracle restart
|
16) PDBのscottでログインしてclient_infoをセット
v$sessionのclient_info列の'TargetSession'文字列で他のSCOTTユーザーのセッションを特定するため。
SCOTT@orcl> @set_client_info 1 begin 2 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TargetSession'); 3* end;PL/SQL procedure successfully completed.Elapsed: 00:00:00.00SCOTT@orcl>
|
17) CDBのSYSで統計取得（再々起動後初回）
内容は省略！（ベースラインを取得しているだけなので）
SYS$orclcdb> @show_stat scott
...略...
|
18) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表をscattered readで全表走査）3回目
キターーーーーーーーーーーーーーっ！。 予想的中！！。（競馬ならいいのにw）
大量のREDOログが生成され、物理読み込みも初生しています。direct path readで全表走査させた時とは明らかに違う！！！（以前、見た光景！！w）
SCOTT@orcl> @table_full_scan.sql 1* alter session set "_serial_direct_read" = neverSession altered.Elapsed: 00:00:00.00 1* alter session set "_very_large_object_threshold" = 20400Session altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge200000 rows selected.Elapsed: 00:00:04.84Execution Plan----------------------------------------------------------Plan hash value: 2339479017--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 214K| 207M| 18223 (1)| 00:00:01 || 1 | TABLE ACCESS FULL| HOGE | 214K| 207M| 18223 (1)| 00:00:01 |--------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 33 recursive calls 0 db block gets 91170 consistent gets 66720 physical reads 964436 redo size 4539159 bytes sent via SQL*Net to client 147264 bytes received via SQL*Net from client 13335 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 200000 rows processedset autot off
|
19) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）（対象表をscattered readで全表走査）3回目
physical reads が想定ブロック数以上あるため、物理読み込みされ全ブロックが読み込まれていると読み取れます。また、physical reads direct は変化していません。（変化のない統計は記載していません）
つまり direct path read ではなく scattered read で全表走査が行われたことを示しています。
immediate (CR) block cleanout applications 10959
|
という統計から、遅延ブロッククリーンアウトが発生し、ほぼ想定していたブロック数であることも確認できます。
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat cleanouts only - consistent read gets 10959sysstat commit cleanouts 3sysstat commit cleanouts successfully completed 3sysstat consistent gets 98846sysstat db block changes 10992sysstat deferred (CURRENT) block cleanout applications 2sysstat free buffer requested 67078sysstat immediate (CR) block cleanout applications 10959sysstat no work - consistent read gets 72660sysstat physical reads 67069
|
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat cleanouts only - consistent read gets 10959sesstat commit cleanouts 2sesstat commit cleanouts successfully completed 2sesstat consistent gets 98234sesstat db block changes 10988sesstat deferred (CURRENT) block cleanout applications 1sesstat free buffer requested 67071sesstat immediate (CR) block cleanout applications 10959sesstat no work - consistent read gets 72324sesstat physical reads 67063
|
20) Oracle再起動
以前の検証で、scattered readでブロッククリーンアウトされた場合のSELECT文であっても結果は永続化されるので、再度読み込ませた場合はクリーンアウト済みなので再度遅延クリーンアウトが発生しないことは確認確認済みですが、今一度確認しておきましょうw
$ sudo service oracle restart
|
21) PDBのscottでログインしてclient_infoをセット
v$sessionのclient_info列の'TargetSession'文字列で他のSCOTTユーザーのセッションを特定するため。
SCOTT@orcl> @set_client_info 1 begin 2 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TargetSession'); 3* end;PL/SQL procedure successfully completed.Elapsed: 00:00:00.00SCOTT@orcl>
|
22) CDBのSYSで統計取得（再再々起動後初回）
内容は省略！（ベースラインを取得しているだけなので）
SYS$orclcdb> @show_stat scott
...略...
|
23) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表をscattered readで全表走査）4回目
想定通り、遅延ブロッククリーンアウトは発生せず、REDOログも生成されていません！ めでたしめでたしw
SCOTT@orcl> @table_full_scan.sql 1* alter session set "_serial_direct_read" = neverSession altered.Elapsed: 00:00:00.01 1* alter session set "_very_large_object_threshold" = 20400Session altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge200000 rows selected.Elapsed: 00:00:04.32Execution Plan----------------------------------------------------------Plan hash value: 2339479017--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 214K| 207M| 18223 (1)| 00:00:01 || 1 | TABLE ACCESS FULL| HOGE | 214K| 207M| 18223 (1)| 00:00:01 |--------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 33 recursive calls 0 db block gets 80211 consistent gets 66719 physical reads 0 redo size 4539159 bytes sent via SQL*Net to clientyoutub 147264 bytes received via SQL*Net from client 13335 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 200000 rows processedset autot off
|
24) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）（対象表をscattered readで全表走査）4回目
physical reads はありますが、 physical reads direct は変化していません。これは scattered read で全データを読み込んだと見て良いでしょうね。読み込んだブロックサイズも該当表の想定データブロック数程度です。
また、遅延ブロッククリーンアウトが発生したことを示す統計は変化していないことから、遅延ブロッククリーンアウトは発生していないことも読み取れます。(^^)
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat commit cleanouts 3sysstat commit cleanouts successfully completed 3sysstat consistent gets 87659sysstat db block changes 34sysstat deferred (CURRENT) block cleanout applications 2sysstat free buffer requested 67058sysstat no work - consistent read gets 83510sysstat physical reads 67048
|
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat commit cleanouts 2sesstat commit cleanouts successfully completed 2sesstat consistent gets 87049sesstat db block changes 30sesstat deferred (CURRENT) block cleanout applications 1sesstat free buffer requested 67051sesstat no work - consistent read gets 83175sesstat physical reads 67042
|
まとめ
SELECT文であっても、遅延ブロッククリーンアウトが発生すると該当ブロックは更新され、REDOログが生成される。ただし、direct path read で読み込まれた場合を除く。
ということのようですね。
ふむふむという興味深い動きですよね。これ。:)
では、次回は direct path に関わる別の動きも確認してみましょうか。。。
このシリーズ、まだまだ引っ張れそうw
ということで次回へつづく。
台風の影響を心配したけどなんとか良い天気の遅い夏休みで。暑くも寒くもないく散歩の気持ちいい秋空 :)
・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #1・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #2・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #3・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #4・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #5・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #6・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #7・
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #8
最近のコメント