古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #4 Tweet
Previously on Mac De Oracle
前回はバッファキャッシュの10%未満のデータブロックへのINSERT文の実行とCOMMITの実行で、遅延ブロッククリーンアウトは発生せず、COMMIT時にすべての対象ブロックがクリーンアウトされるということを確認しました。
今回は、そのデータ量を倍にして、バッファキャッシュの10％程度を超えるデータブロックが遅延ブロッククリーンアウトされるのかを見ていくことにします。手順は前回と同じですが、遅延ブロッククリーンアウトさせた後で、もう一度全表走査してクリーンアウトが繰り返されないことも確認しておきます（次回以降に予定している確認への伏線なのですがw）
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('Target Session'); 3* end;PL/SQL procedure successfully completed.Elapsed: 00:00:00.00SCOTT@orcl>
3) CDBのSYSで統計取得（初回）
内容は省略！（ベースラインを取得しているだけなので）
SYS$orclcdb> @show_stat
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しただけです。未コミットなので特に気になる情報は現れていません。この値からコミット後にどのように変化するのか？ という部分に注目する必要があるんですよー。
deferred (CURRENT) block cleanout applications と immediate (CURRENT) block cleanout applications が僅かにありますが、この時点では気にする部分ではないです
（値の変化が1以上ある統計のみ表示）
(CDB）システム統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sysstat DBWR checkpoint buffers written 22756sysstat DBWR checkpoints 33sysstat DBWR thread checkpoint buffers written 22756sysstat DBWR transaction table writes 22sysstat DBWR undo block writes 606sysstat consistent gets 49761sysstat db block changes 744980sysstat deferred (CURRENT) block cleanout applications 4sysstat immediate (CURRENT) block cleanout applications 1sysstat no work - consistent read gets 192sysstat physical reads 18sysstat physical writes 22756sysstat physical writes from cache 22756sysstat physical writes non checkpoint 22756
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat consistent gets 49501sesstat db block changes 744980sesstat deferred (CURRENT) block cleanout applications 4sesstat immediate (CURRENT) block cleanout applications 1sesstat no work - consistent read gets 65sesstat physical reads 17
6) PDBのSCOTTユーザーでコミットの実行
SCOTT@orcl> commit;
Commit complete.
7) CDBのSYSで統計取得（コミット後）
この結果、ノイズも少なく、綺麗に取れてます！！！ w
前々回の事前確認の通り、2倍のデータブロック数は、 66667ブロック で、バッファキャッシュの10%は、ざっくり計算で、42394ブロック。つまり、想定では 42394ブロック ほどが、commit時のブロッククリーンアウトの対象と想定していました。
覚えてますか？ みなさん！
実際にcommit時にクリーンアウトされたのはどれぐらいでしょう？
結果は、55700ブロックとなりました。想定より多いですねw ほぼ合ってはいますが。
実際にはバッファキャッシュの13%〜15％程度が閾値になっているように見えます。とはいえ、commit時にcleanoutされたブロック数は 55700ブロック ですから、残る 10967ブロック のcleanoutは遅延されたということは確実です。commit対象のデータブロック全てをcleanoutするわけではない、ということは確認できたのではないでしょうか？
(差分が1以上ある統計のみ記載)
(CDB）システム統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sysstat commit cleanouts 55700sysstat commit cleanouts successfully completed 55700sysstat db block changes 1
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat commit cleanouts 55700sesstat commit cleanouts successfully completed 55700sesstat db block changes 1
8) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）
SCOTT@orcl> sset autot trace exp statSCOTT@orcl> salter session set "_serial_direct_read" = never;Session altered.Elapsed: 00:00:00.00SCOTT@orcl> select * from hoge;200000 rows selected.Elapsed: 00:00:05.14Execution Plan----------------------------------------------------------Plan hash value: 2339479017--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 194K| 188M| 18189 (1)| 00:00:01 || 1 | TABLE ACCESS FULL| HOGE | 194K| 188M| 18189 (1)| 00:00:01 |--------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 46 recursive calls 13 db block gets 91636 consistent gets 7 physical reads 967348 redo size 406775148 bytes sent via SQL*Net to client 147264 bytes received via SQL*Net from client 13335 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 200000 rows processed
9) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）
遅延ブロッククリーンアウトは、事前の計算通り、 10967ブロック 発生しています。SELECT文では、immediate (CR) block cleanout applications として現れることも確認できます。
また、cleanouts only - consistent read gets として も同数計上されているところが見てます。綺麗に現れています。
commit cleanouts, ommit cleanouts successfully completed がでていますが、ここでは気にしなくてよいですね、極わずかで。SELECT文なので。
immediate (CURRENT) block cleanout applications、deferred (CURRENT) block cleanout applications もでていますが、同じく極わずかで、対象表のものではないと考えられるためここでは気にしなくて良いですね。
しかし、計算通りに発生してくれると確認が楽w （想定外の動きじゃなくてよかったw）
(CDB）システム統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sysstat DBWR checkpoint buffers written 272sysstat DBWR thread checkpoint buffers written 272sysstat cleanouts only - consistent read gets 10967sysstat commit cleanouts 16sysstat commit cleanouts successfully completed 16sysstat consistent gets 117000sysstat db block changes 11207sysstat deferred (CURRENT) block cleanout applications 10sysstat immediate (CR) block cleanout applications 10967sysstat immediate (CURRENT) block cleanout applications 2sysstat no work - consistent read gets 83351sysstat physical reads 939sysstat physical writes 272sysstat physical writes from cache 272sysstat physical writes non checkpoint 260
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat cleanouts only - consistent read gets 10967sesstat commit cleanouts 6sesstat commit cleanouts successfully completed 6sesstat consistent gets 98162sesstat db block changes 11011sesstat deferred (CURRENT) block cleanout applications 2sesstat immediate (CR) block cleanout applications 10967sesstat immediate (CURRENT) block cleanout applications 2sesstat no work - consistent read gets 72113sesstat physical reads 122
10) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）
そして、ここからがおまけの確認ステップです
もう一度、同じ表を全表走査してみます。どうなると思いますか？ 遅延されていたブロッククリーンアウトも行われたのですから、当然、該当オブジェクトで遅延ブロッククリーンアウトは発生しない。はず！ ですよね。
確認してみましょう。（発生してたらどうしようw、もうしそうなったらバグレポートでも上げようかなw）
.......
Redoは生成されてない！ （よかった！ 想定どおりだ！w）
SCOTT@orcl> @table_full_scan 1* alter session set "_serial_direct_read" = neverSession altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge200000 rows selected.Elapsed: 00:00:05.43Execution 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---------------------------------------------------------- 0 recursive calls 0 db block gets 80061 consistent gets 0 physical reads 0 redo size 406775148 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 offSCOTT@orcl>
11) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）2回目
これもノイズが少なく綺麗に取れました。該当セッションでは物理読み込みも発生していないので、キャッシュから全データを読み込んだようです。
そして、想定どおり、該当セッションでは遅延ブロッククリーンアウトは発生していません！
commit cleanouts、commit cleanouts successfully completed、deferred (CURRENT) block cleanout applicationsが1ブロックありますがCDB側の管理情報関連でしょうね。気にする部分ではないですね。
（差分のあった統計のみ記載）
(CDB）システム統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sysstat commit cleanouts 1sysstat commit cleanouts successfully completed 1sysstat consistent gets 80150sysstat db block changes 13sysstat deferred (CURRENT) block cleanout applications 1sysstat no work - consistent read gets 80075
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat consistent gets 80076sesstat db block changes 9sesstat no work - consistent read gets 80043
まとめ
おおよそ、バッファキャッシュの10%程度が commit時にcleanout されるという点については、約15%程度と見ておいたほうが良さそうですが、まあ大きな違いはないので、その辺りに閾値があると考えて問題はなさそうです。
また、それを超えるブロックについては、cleanoutが先送りされ、最初に該当ブロックにアクセスしたSQLがその影響を受ける。
この検証ではSELECT文では、immediate (CR) block cleanout applications という形で統計に現れました。UPDATE文やDELETE文の場合は他の統計として現れそうですね。(CURRENT)関連のcleanoutの統計は今回動いていないのでSQL文を変えて同じような検証をしてみると面白い結果をえられそうです。
そして、SELECT文で、遅延ブロッククリーンアウトされてしまえば、その間に更新が発生しなければ、クリーンアウトは発生しない（おまけで検証した部分ですが、別検証では興味深い動きを紹介する予定です。その伏線でもあります）
次回へつづく
5年目を迎えた、パナソニックのドラム洗濯機がH故障した。慌てて近所のコインランドリーを検索w 近所にあってよかったw
・古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #1
・古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #2
・古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #3
| 固定リンク | 0
コメント