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

2021年9月24日 (金)

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

Previously on Mac De Oracle
前回は、クリーンアウトの後始末をさせられる側ではなく、そもそも、後始末不要な状況もあるという確認でした。
IAS(Insert as Select)でdirect path writeで書き込まれたブロックは、クリーンアウトする必要がない状態なので、コミットクリーンアウトも遅延ブロッククリーンアウトも発生していないことはシステム統計やセッション統計からも明らかでした。

今回は、今一度確認ということで、IASでも非direct path writeだったら、やはりコミットクリーンアウトや遅延ブロッククリーンアウトは発生するよね! というところだけは見ておこうと思います。

手順は前回と同じ。前回と異なる点は、4) の部分。IASで NOAPPEND ヒントを使い direct path writeを抑止している部分のみ。
Iasstep





0) 対象表のdrop/create

SCOTT@orcl> @droppurge_create_hoge2.sql
1* drop table hoge2 purge

Table 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 selected

Elapsed: 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.00
SCOTT@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 hoge

200000 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 19158
sysstat DBWR checkpoints 2
sysstat DBWR thread checkpoint buffers written 19158
sysstat DBWR transaction table writes 22
sysstat DBWR undo block writes 299
sysstat commit cleanouts 6
sysstat commit cleanouts successfully completed 6
sysstat consistent gets 110098
sysstat db block changes 443304
sysstat deferred (CURRENT) block cleanout applications 5
sysstat free buffer requested 67998
sysstat immediate (CURRENT) block cleanout applications 1
sysstat no work - consistent read gets 66823
sysstat physical reads 66730
sysstat physical reads direct 66709
sysstat physical writes 19158
sysstat physical writes from cache 19158
sysstat physical writes non checkpoint 19158
sysstat transaction tables consistent reads - undo records applied 2

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat commit cleanouts 6
sesstat commit cleanouts successfully completed 6
sesstat consistent gets 109971
sesstat db block changes 443304
sesstat deferred (CURRENT) block cleanout applications 5
sesstat free buffer requested 67996
sesstat immediate (CURRENT) block cleanout applications 1
sesstat no work - consistent read gets 66781
sesstat physical reads 66730
sesstat 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 55709
sysstat commit cleanouts successfully completed 55709
sysstat consistent gets 18547
sysstat db block changes 289
sysstat deferred (CURRENT) block cleanout applications 4
sysstat free buffer requested 791
sysstat no work - consistent read gets 10937
sysstat physical reads 758
sysstat transaction tables consistent reads - undo records applied 1

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat commit cleanouts 55700
sesstat commit cleanouts successfully completed 55700
sesstat 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" = never

Session altered.

Elapsed: 00:00:00.00
1* alter session set "_very_large_object_threshold" = 20400

Session altered.

Elapsed: 00:00:00.00
set autot trace exp stat

1* select * from hoge2

200000 rows selected.

Elapsed: 00:00:05.74

Execution 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 10967
sysstat commit cleanouts 19
sysstat commit cleanouts successfully completed 19
sysstat consistent gets 98619
sysstat db block changes 11065
sysstat deferred (CURRENT) block cleanout applications 8
sysstat free buffer requested 180
sysstat immediate (CR) block cleanout applications 10967
sysstat immediate (CURRENT) block cleanout applications 5
sysstat no work - consistent read gets 72340
sysstat physical reads 169

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistent read gets 10967
sesstat commit cleanouts 6
sesstat commit cleanouts successfully completed 6
sesstat consistent gets 98015
sesstat db block changes 11011
sesstat deferred (CURRENT) block cleanout applications 2
sesstat free buffer requested 121
sesstat immediate (CR) block cleanout applications 10967
sesstat immediate (CURRENT) block cleanout applications 2
sesstat no work - consistent read gets 72013
sesstat physical reads 113




まとめ

IASで 非direct path write してINSERTされた場合、ブロッククリーンアウトが通常通り発生する(コミット時でも遅延でも)
direct path write時との統計値の違いからも明らかですね。

前回のIAS(APPEND)時の遅延ブロッククリーンアウトがなかったケースの統計を再掲しておきます。統計値の違いをよーく確認してみてください。(試験に。。。でないですけどw)

統計値が変動したもののみ記載
(CDB)システム統計

SOURCE  NAME                                                            VALUE
------- ---------------------------------------------------- ---------------
sysstat cleanouts only - consistent read gets 1
sysstat commit cleanouts 16
sysstat commit cleanouts successfully completed 16
sysstat consistent gets 105046
sysstat db block changes 225
sysstat deferred (CURRENT) block cleanout applications 9
sysstat free buffer requested 67576
sysstat immediate (CR) block cleanout applications 1
sysstat immediate (CURRENT) block cleanout applications 2
sysstat no work - consistent read gets 93908
sysstat physical reads 67536

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat cleanouts only - consistent read gets 1
sesstat commit cleanouts 6
sesstat commit cleanouts successfully completed 6
sesstat consistent gets 86566
sesstat db block changes 44
sesstat deferred (CURRENT) block cleanout applications 3
sesstat free buffer requested 66778
sesstat immediate (CR) block cleanout applications 1
sesstat immediate (CURRENT) block cleanout applications 2
sesstat no work - consistent read gets 82891
sesstat 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



|

コメント

コメントを書く