古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #11 Tweet
Previously on Mac De Oracle
前回は、クリーンアウトの後始末をさせられる側ではなく、そもそも、後始末不要な状況もあるという確認でした。
IAS（Insert as Select)でdirect path writeで書き込まれたブロックは、クリーンアウトする必要がない状態なので、コミットクリーンアウトも遅延ブロッククリーンアウトも発生していないことはシステム統計やセッション統計からも明らかでした。
今回は、今一度確認ということで、IASでも非direct path writeだったら、やはりコミットクリーンアウトや遅延ブロッククリーンアウトは発生するよね！ というところだけは見ておこうと思います。
手順は前回と同じ。前回と異なる点は、4) の部分。IASで NOAPPEND ヒントを使い direct path writeを抑止している部分のみ。
0) 対象表のdrop/create
SCOTT@orcl> @droppurge_create_hoge2.sql 1* drop table hoge2 purgeTable dropped.Elapsed: 00:00:00.67 1* create table hoge2 (id number, data varchar2(2000))Table created.Elapsed: 00:00:00.05 1* select segment_name,blocks from user_segments where segment_name like '%HOGE2%'no rows selectedElapsed: 00:00:00.16
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('Target Session'); 3* end;PL/SQL procedure successfully completed.Elapsed: 00:00:00.00SCOTT@orcl>
3) CDBのSYSで統計取得（初回）
内容は省略！（ベースラインを取得しているだけなので）
SYS$orclcdb> @show_stat scott
...略...
4) PDBのSCOTTユーザーでIAS（データサイズは、コミットクリーンアウトではクリーンアウト仕切れないサイズ、コミットなし）
このケースでは 非direct path write でINSERTしたいので NOAPPEND ヒントで direct path write を抑止しています。
SCOTT@orcl> @ias_noappend_from_hoge.sql 1* insert /*+ noappend */ into hoge2 select * from hoge200000 rows created.Elapsed: 00:00:16.79
5) CDBのSYSで統計取得（APPENDヒント付きのIAS後、未コミット）
IASでHOGE表を読み込み、HOGE2表へ非direct path write している様子が確認できますよね。HOGE表の読み込みは direct path read 、HOGE2表へは 非direct path write していることが読み取れます。
free buffer requested で読み込みブロック相当のブロックがバッファキャッシュへ載せられているように見えますが、読み込みは direct path read なのでバッファキャッシュには載りません。ようするに、HOGE2表向けのデータと考えることができますよね。このキャッシュされたブロックが本当にHOGE2表向けブロックであるかどうかは、これ以降の操作で物理読み込みが発生しないということで確認することができます。
(HOGE表のデータが載っているブロック数は、66667 ブロックであることは前々回も確認した通りの値です)
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat DBWR checkpoint buffers written 19158sysstat DBWR checkpoints 2sysstat DBWR thread checkpoint buffers written 19158sysstat DBWR transaction table writes 22sysstat DBWR undo block writes 299sysstat commit cleanouts 6sysstat commit cleanouts successfully completed 6sysstat consistent gets 110098sysstat db block changes 443304sysstat deferred (CURRENT) block cleanout applications 5sysstat free buffer requested 67998sysstat immediate (CURRENT) block cleanout applications 1sysstat no work - consistent read gets 66823sysstat physical reads 66730sysstat physical reads direct 66709sysstat physical writes 19158sysstat physical writes from cache 19158sysstat physical writes non checkpoint 19158sysstat transaction tables consistent reads - undo records applied 2
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat commit cleanouts 6sesstat commit cleanouts successfully completed 6sesstat consistent gets 109971sesstat db block changes 443304sesstat deferred (CURRENT) block cleanout applications 5sesstat free buffer requested 67996sesstat immediate (CURRENT) block cleanout applications 1sesstat no work - consistent read gets 66781sesstat physical reads 66730sesstat physical reads direct 66709
6) PDBのSCOTTユーザーでコミットの実行
SCOTT@orcl> commit;
Commit complete.
7) CDBのSYSで統計取得（コミット後）
APPENDモードのIASと異なり、NOAAPENDモードでは、はやり、コミットクリーンアウトが発生しています。バッファキャッシュの10%-15%の範囲のブロックがコミット時にクリーンアウトされている状況が commit cleanouts successfully completed および、commit cleanouts から確認することができます。
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat commit cleanouts 55709sysstat commit cleanouts successfully completed 55709sysstat consistent gets 18547sysstat db block changes 289sysstat deferred (CURRENT) block cleanout applications 4sysstat free buffer requested 791sysstat no work - consistent read gets 10937sysstat physical reads 758sysstat transaction tables consistent reads - undo records applied 1
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat commit cleanouts 55700sesstat commit cleanouts successfully completed 55700sesstat db block changes 1
8) PDBのSCOTTユーザーで遅延ブロッククリーンアウト有無確認（対象表をscattered readで全表走査）
direct path writeと非direct path writeの違いはハッキリでました。REDOログは大量に生成され、遅延ブロッククリーンアウトは発生しているようです。
また、物理読み込みは発生していないので、バッファキャッシュにヒットしているという状況も読み取れます。
SCOTT@orcl> @table_full_scan_hoge2.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 hoge2200000 rows selected.Elapsed: 00:00:05.74Execution Plan----------------------------------------------------------Plan hash value: 1530105727---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 172K| 167M| 18223 (1)| 00:00:01 || 1 | TABLE ACCESS FULL| HOGE2 | 172K| 167M| 18223 (1)| 00:00:01 |---------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 19 recursive calls 13 db block gets 91462 consistent gets 2 physical reads 967388 redo size 406775148 bytes sent via SQL*Net to client 147265 bytes received via SQL*Net from client 13335 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 200000 rows processed¥set autot off
9) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認。対象表をscattered readで全表走査）
実行統計の示す通り、遅延ブロッククリーンアウトが発生していることは、immediate (CR) block cleanout applications、cleanouts only - consistent read getsのブロック数からもハッキリ確認することができます！（コミットクリーンアウトできなかったブロック数です）
physical readsはほんの少しありますが、発生していないとみなしても良い程度です。このケースはscattered readが発生していない。つまり、非direct path writeでバッファキャッシュに載ったブロックにヒットしていることで、scattered readの必要がなかった！ ということを意味しています。もし、この時、バッファキャッシュから該当表のブロックがある程度落ちていれば、physical readsが表のブロック数程度まで増加していたはずです。（多数の同時実行トランザクションが存在する状況であればキャッシュからエージアウトされ、物理読み込みが大量に発生するというケースは珍しくありません。その分処理時間も長くなるわけで、程度とデータサイズサイズしだいで処理時間も延びることが予想できます。場合よっては、処理時間がかかりすぎて、ザワザワしたり。。。w）
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat cleanouts only - consistent read gets 10967sysstat commit cleanouts 19sysstat commit cleanouts successfully completed 19sysstat consistent gets 98619sysstat db block changes 11065sysstat deferred (CURRENT) block cleanout applications 8sysstat free buffer requested 180sysstat immediate (CR) block cleanout applications 10967sysstat immediate (CURRENT) block cleanout applications 5sysstat no work - consistent read gets 72340sysstat physical reads 169
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat cleanouts only - consistent read gets 10967sesstat commit cleanouts 6sesstat commit cleanouts successfully completed 6sesstat consistent gets 98015sesstat db block changes 11011sesstat deferred (CURRENT) block cleanout applications 2sesstat free buffer requested 121sesstat immediate (CR) block cleanout applications 10967sesstat immediate (CURRENT) block cleanout applications 2sesstat no work - consistent read gets 72013sesstat physical reads 113
まとめ
IASで 非direct path write してINSERTされた場合、ブロッククリーンアウトが通常通り発生する（コミット時でも遅延でも）
direct path write時との統計値の違いからも明らかですね。
前回のIAS（APPEND)時の遅延ブロッククリーンアウトがなかったケースの統計を再掲しておきます。統計値の違いをよーく確認してみてください。（試験に。。。でないですけどw）
統計値が変動したもののみ記載
(CDB）システム統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sysstat cleanouts only - consistent read gets 1sysstat commit cleanouts 16sysstat commit cleanouts successfully completed 16sysstat consistent gets 105046sysstat db block changes 225sysstat deferred (CURRENT) block cleanout applications 9sysstat free buffer requested 67576sysstat immediate (CR) block cleanout applications 1sysstat immediate (CURRENT) block cleanout applications 2sysstat no work - consistent read gets 93908sysstat physical reads 67536
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ---------------------------------------------------- ---------------sesstat cleanouts only - consistent read gets 1sesstat commit cleanouts 6sesstat commit cleanouts successfully completed 6sesstat consistent gets 86566sesstat db block changes 44sesstat deferred (CURRENT) block cleanout applications 3sesstat free buffer requested 66778sesstat immediate (CR) block cleanout applications 1sesstat immediate (CURRENT) block cleanout applications 2sesstat no work - consistent read gets 82891sesstat physical reads 66770
次回は、CTAS ( create table as select ) ではどうなるか確かめます。（まだいくつかの関連統計を動かせていないケースありw）
次回へつづく。
東京は、自転車での移動や買い物のほうが渋滞や混雑のストレスがなくていいな。久々に coutry side側をドライブしてたが、ノーストレレスだった。一転機能は、買い物でドライブするも、いちいち渋滞w
WfHも年単位になると、すでに都心の満員電車や渋滞に耐えられない感じになってる気がしないでもないw （渋滞でスタックしている時間がもったいない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
・古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #9
・古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #10
| 固定リンク
コメント