« 古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #2 | トップページ | 古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #4 »

2021年9月 2日 (木)

古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #3

Previously on Mac De Oracle
前回は準備を終えたところまででした。

今日は、簡単なところから確認していきましょう。

もしも、「遅延ブロッククリーンアウトが起きない程度のブロック更新量だったなら。。。」。結果は遅延ブロッククリーンアウトは起きないはず。 ですよね。

ざっと手順を紹介しておきましょう。下図の 1)〜9)の順で行います
20210901-221016

前述の手順で、各操作後の統計の差分(変化量)を見る。マニュアルの統計の説明ってざっくり過ぎてよくわからないのが多いわけですがw、操作と値の変化を合わせて観察すると、それなりには理解できる程度に値が変化していることに気づきますw :)

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.


3) CDBのSYSで統計取得(初回)
初回なのですべてリストしていますが、CON_ID=0のCDBのシステム統計([g]v$sysstat)とCON_ID=3のPDBの2)のCLIENT_INFOを設定されたセッションのセッション統計([g]v$sesstat)を取得します。
今回のケースではcleanoutが含まれている統計の差異だけに着目すれば良いのですが。準備運動程度のテストケースなので一応すべて載せておきます :)
(マルチテナントだと、DBRWの動きを見るにはCDBのDBWR関連の統計を見る必要があるため、CDBのシステム統計とPDBの当該セッションのセッション統計を対象にしています)

SYS$orclcdb> @show_stat
SOURCE NAME VALUE CON_ID
------- ------------------------------------------------------------ -------------------- ----------
sysstat DBWR checkpoint buffers written 0 0
sysstat DBWR checkpoints 0 0
sysstat DBWR fusion writes 0 0
sysstat DBWR lru scans 0 0
sysstat DBWR object drop buffers written 0 0
sysstat DBWR parallel query checkpoint buffers written 0 0
sysstat DBWR revisited being-written buffer 0 0
sysstat DBWR tablespace checkpoint buffers written 0 0
sysstat DBWR thread checkpoint buffers written 0 0
sysstat DBWR transaction table writes 0 0
sysstat DBWR undo block writes 0 0
sysstat cleanouts and rollbacks - consistent read gets 6 0
sysstat cleanouts only - consistent read gets 0 0
sysstat commit cleanout failures: block lost 0 0
sysstat commit cleanout failures: buffer being written 0 0
sysstat commit cleanout failures: callback failure 0 0
sysstat commit cleanout failures: cannot pin 0 0
sysstat commit cleanout failures: hot backup in progress 0 0
sysstat commit cleanout failures: write disabled 0 0
sysstat commit cleanouts 570 0
sysstat commit cleanouts successfully completed 570 0
sysstat consistent gets 158,083 0
sysstat db block changes 3,247 0
sysstat deferred (CURRENT) block cleanout applications 316 0
sysstat immediate (CR) block cleanout applications 6 0
sysstat immediate (CURRENT) block cleanout applications 15 0
sysstat no work - consistent read gets 108,516 0
sysstat physical reads 12,551 0
sysstat physical reads direct 0 0
sysstat physical writes 0 0
sysstat physical writes direct 0 0
sysstat physical writes from cache 0 0
sysstat physical writes non checkpoint 0 0
sysstat transaction tables consistent read rollbacks 0 0
sysstat transaction tables consistent reads - undo records applied 0 0
sesstat DBWR checkpoint buffers written 0 3
sesstat DBWR checkpoints 0 3
sesstat DBWR fusion writes 0 3
sesstat DBWR lru scans 0 3
sesstat DBWR object drop buffers written 0 3
sesstat DBWR parallel query checkpoint buffers written 0 3
sesstat DBWR revisited being-written buffer 0 3
sesstat DBWR tablespace checkpoint buffers written 0 3
sesstat DBWR thread checkpoint buffers written 0 3
sesstat DBWR transaction table writes 0 3
sesstat DBWR undo block writes 0 3
sesstat cleanouts and rollbacks - consistent read gets 0 3
sesstat cleanouts only - consistent read gets 0 3
sesstat commit cleanout failures: block lost 0 3
sesstat commit cleanout failures: buffer being written 0 3
sesstat commit cleanout failures: callback failure 0 3
sesstat commit cleanout failures: cannot pin 0 3
sesstat commit cleanout failures: hot backup in progress 0 3
sesstat commit cleanout failures: write disabled 0 3
sesstat commit cleanouts 1 3
sesstat commit cleanouts successfully completed 1 3
sesstat consistent gets 374 3
sesstat db block changes 4 3
sesstat deferred (CURRENT) block cleanout applications 1 3
sesstat immediate (CR) block cleanout applications 0 3
sesstat immediate (CURRENT) block cleanout applications 0 3
sesstat no work - consistent read gets 220 3
sesstat physical reads 28 3
sesstat physical reads direct 0 3
sesstat physical writes 0 3
sesstat physical writes direct 0 3
sesstat physical writes from cache 0 3
sesstat physical writes non checkpoint 0 3
sesstat transaction tables consistent read rollbacks 0 3
sesstat transaction tables consistent reads - undo records applied 0 3


4) PDBのSCOTTユーザーでデータINSERT(コミットなし)
データを1行単位でインサートしています。バルクインサートも使ってないです。綺麗なぐるぐる系ですねw。コミット時の効果を確認しやすいようにコミットは後で実行します!
このインサートで、前回の事前準備の時に確認しておいた、33334ブロックが更新されることになります

SCOTT@orcl> @insert_each_rows
1* begin for i in 1..100000 loop insert into hoge values(i, lpad('*', 2000, '*')); end loop; end;

PL/SQL procedure successfully completed.


5) CDBのSYSで統計取得(INSERT後、未コミット)

注) 3)の統計との差分のみ記載

未コミットであるこの時点で、commitクリーンアウトが発生(該当セッション統計でも同数発生。commit cleanoutsとcommit cleanouts successfully completed)してますが、これは気にしなくてもよいですね。
実行したトランザクションは、未コミットなので、この実行による直接的な影響ではないので。
また、deferred (CURRENT) block cleanout applicationsやimmediate (CURRENT) block cleanout applications の遅延ブロッククリーンアウトを示す統計も微量ですがこれも同様とみて良いでしょう。

この時点で統計取得理由は、操作毎に変化する統計を追うためなので、ふーーん。ぐらいでの雰囲気でOKです :)
コミット後とその後に該当オブジェクトをアクセスさせた時の遅延ブロッククリーンアウトの有無部分の部分が主役ですので。

参考)
INSERTした行の含まれる全ブロックはバッファキャッシュに載り切るブロック数なので、バッファキャッシュから書き出されているような動きも観測されていないのは確認できると思います。
(physical writes from cache,physical writes non checkpoint,physical writesの統計に変化がないので未記載ですが、それらの統計が動いていないということがその理由です)


(CDB)システム統計

SOURCE  NAME                                            VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoints 1
sysstat commit cleanouts 6
sysstat commit cleanouts successfully completed 6
sysstat consistent gets 22884
sysstat db block changes 373785
sysstat deferred (CURRENT) block cleanout applications 5
sysstat immediate (CURRENT) block cleanout applications 1
sysstat no work - consistent read gets 59
sysstat physical reads 12


(PDB) SCOTTのセッション統計

SOURCE  NAME                                            VALUE
------- ---------------------------------------------------- ---------------
sesstat commit cleanouts 6
sesstat commit cleanouts successfully completed 6
sesstat consistent gets 22816
sesstat db block changes 373785
sesstat deferred (CURRENT) block cleanout applications 5
sesstat immediate (CURRENT) block cleanout applications 1
sesstat no work - consistent read gets 49
sesstat physical reads 12


6) PDBのSCOTTユーザーでコミットの実行

SCOTT@orcl> commit;

Commit complete.


7) CDBのSYSで統計取得(コミット後)

注)5)と7)で取得した統計の差分のみ記載

ここが主役ですよー。このケースではバッファキャッシュの10%に満たないブロック数になるようにしたINSERT文(繰り返し実行)で 33334ブロックになるようにしました。これらを1度でcommitした場合、すべてのブロックがcommit時にblock cleanoutされるはずです。

では見てみましょう。

SCOTTのセッション統計より、commit cleanouts および commit cleanouts successfully completed から想定どおり全ブロックがcommit時にcleanoutされていることがわかります!
システム統計はインスタンス全体なのでPDBのそれら統計より大きめにでているのも確認できます。CDB側ではほんの少し deferred (CURRENT) block cleanout applications がありますが、管理情報系の遅延ブロッククリーンアウトでしょうね。 
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #1で紹介したとおり 10% 未満では commit 時点で該当ブロックすべてcleanoutされることが確認できました。


(CDB)システム統計

SOURCE  NAME                                            VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 139
sysstat DBWR transaction table writes 22
sysstat DBWR undo block writes 55
sysstat commit cleanouts 33343
sysstat commit cleanouts successfully completed 33343
sysstat consistent gets 18111
sysstat db block changes 138
sysstat deferred (CURRENT) block cleanout applications 5
sysstat no work - consistent read gets 10853
sysstat physical reads 827
sysstat physical writes 139
sysstat physical writes from cache 139
sysstat physical writes non checkpoint 139


(PDB) SCOTTのセッション統計

SOURCE  NAME                                            VALUE
------- ---------------------------------------------------- ---------------
sesstat commit cleanouts 33334
sesstat commit cleanouts successfully completed 33334
sesstat db block changes 1


8) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認(対象表を全表走査)

_serial_direct_read = never はdirect path readさせないためのおまじないです。direct path readで読み込まれたケースの動きは別エントリーで見ていく予定なので、direct path readを発生させずtable full scan (ようするにバッファキャッシュに載せる動き)で読み込むよう強制しています。
また、行数が多いのでその時間の短縮のためにautot trace exp statを有効にしてSELECT文を実行させつつ、termout offと同じ効果とauto explainの機能でSELECT文の実行統計からredoの生成有無を確認しています。(redoがあるということはなんらかの更新が行われているわけで、SELECT文の場合は比較的容易に遅延ブロッククリーンアウトの発生を推測できる統計にもなります)
以下のケースでは多少redoが生成されていますが、おそらく recursive callによるもので、HOGE表のオブジェクトそのものに対するもではなさそうです。(このケースではhoge表のオブジェクトに対する遅延ブロッククリーンアウトや
コミット時のブロッククリーンアウトの観察が主題なので、周りのノイズはあまり気にしなくてもOK(追いかけたい場合は別ですがw)


SCOTT@orcl> set autot trace exp stat
SCOTT@orcl> alter session set "_serial_direct_read" = never;

Session altered.

Elapsed: 00:00:00.00
SCOTT@orcl> select * from hoge;

100000 rows selected.

Elapsed: 00:00:02.59

Execution Plan
----------------------------------------------------------
Plan hash value: 2339479017

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57526 | 55M| 9098 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| HOGE | 57526 | 55M| 9098 (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
21 recursive calls
13 db block gets
40465 consistent gets
3 physical reads
2212 redo size
203382760 bytes sent via SQL*Net to client
73706 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed

SCOTT@orcl> set autot off


9) CDBのSYSで統計取得(遅延ブロッククリーンアウト有無確認)

注)7)のコミット時点からとの差異のみ記載。

多少ですが、SELECT文でredoが生成されてはいます。これは、commit cleanouts/commit cleanouts successfully completed (commit時に実施されるblock cleanout)と遅延ブロッククリーンアウトの統計の一つ、deferred (CURRENT) block cleanout applications が現れている影響ですね。コミット時点でHOGE表の全ブロックはcleanout済みなので recursive callによる内部管理情報関連で定常的に現れるものと考えられ、この検証では気にするところではないのでスルーしてください。

また、physical reads は、ほぼないため、この全表走査では、物理読み込み(direct path read含む)は、発生していないことも確認できます。
HOGE表はバッファキャッシュに載ったままという意図通りの状態にはなっているようです。
これ、今後のテストケースでも利用するので、キャッシュからエージアウトされないという点が確認できてると、以降の検証やりやすいんです:)


(CDB) システム統計

SOURCE  NAME                                            VALUE
------- ---------------------------------------------------- ---------------
sysstat DBWR checkpoint buffers written 34849
sysstat DBWR transaction table writes 32
sysstat DBWR undo block writes 897
sysstat commit cleanouts 558
sysstat commit cleanouts successfully completed 558
sysstat consistent gets 69973
sysstat db block changes 2862
sysstat deferred (CURRENT) block cleanout applications 311
sysstat immediate (CURRENT) block cleanout applications 28
sysstat no work - consistent read gets 57908
sysstat physical reads 812
sysstat physical writes 34849
sysstat physical writes from cache 34849
sysstat physical writes non checkpoint 34738


(PDB) SCOTTのセッション統計

SOURCE  NAME                                            VALUE
------- ---------------------------------------------------- ---------------
sesstat commit cleanouts 5
sesstat consistent gets 47037
sesstat db block changes 38
sesstat deferred (CURRENT) block cleanout applications 4
sesstat no work - consistent read gets 43103
sesstat physical reads 117


まとめ

DMLにより更新されたブロック数が、バッファキャッシュの10%未満のブロック数である場合、commit時にすべてcleanoutされ、対象表では遅延ブロッククリーンアウトは発生しない。
(想定通りなのですが、ちょいと安心w 19cでどう動くか確認してなかってしので少々ドキドキしてたw のはナイショ)

というわけで、今回はここまで。

いきなり涼しくなって、なんだこりゃ。。。。



古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #1
古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #2



|

コメント

コメントを書く