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

Previously on Mac De Oracle
前回、前々回とIASでdirect path writeであるか否かで、そのコミットおよび遅延ブロッククリーンアウトの有無が決まるという動きを確認しました。

今回は、DDLですが、親戚みたいな挙動のCTASではどうなるか確認しておきます。CTASはDDLなのでコミットは不要ですよね。また、 direct path writeが前提になっていることも皆さんご存知の通り。

ということは、IAS + APPENDヒントで、direct path write させた時と同じような挙動になるはず。。。。ですよね。

手順はいつもの図でご確認ください。（DDLなので手順も単純になっています）

---

---

0) 対象表のdrop
対象表のHOGE2は削除しておきます。CTASで作成することになるので。

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

Table dropped.

Elapsed: 00:00:02.79

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ユーザーでCTAS（データサイズは、コミットクリーンアウトではクリーンアウト仕切れないサイズ）

SCOTT@orcl> @ctas_from_hoge.sql
  1* create table hoge2 as  select * from hoge

Table created.

Elapsed: 00:00:07.41

5) CDBのSYSで統計取得（CTAS後）
DDLなのでコミットはありませんが、念の為に確認すると、commit cleanouts、commit cleanouts successfully completed　はほんの少しだけ。このテストケースではノイズ程度の量です。
DDL終了時にクリーンアウトは発生していないと読み取れます。（この後の手順で遅延クリーンアウトも発生していなければ。direct path writeではクリーンアウトが必要な状態にはならないということは間違いないと判断するできますよね）

参考程度ですが、physical writes 、physical writes direct、physical writes non checkpoint が同数です。物理書き込みが発生し、かつ、direct path writeでチェックポイントで書き出されたものではないということが確認できます。想定通り、CTASは direct path writeで書き出されているということがわかります。
(HOGE表のデータが載っているブロック数は、66667 ブロックであることは以前確認した通りの値です)

また、physical reads　と　physical reads direct は、HOGE表のブロック数以上あり、HOGE表は direct path readでお読み込まれていることがわかります。このときのfree buffer requested は非常に低いことは、バッファキャッシュを介さず、ストレージへ書き出されていることを示しています。

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat	commit cleanouts                                                  21
sysstat	commit cleanouts successfully completed                           21
sysstat	consistent gets                                                72654
sysstat	db block changes                                                3898
sysstat	deferred (CURRENT) block cleanout applications                     7
sysstat	free buffer requested                                            819
sysstat	immediate (CURRENT) block cleanout applications                    8
sysstat	no work - consistent read gets                                 69569
sysstat	physical reads                                                 67150
sysstat	physical reads direct                                          66709
sysstat	physical writes                                                66667
sysstat	physical writes direct                                         66667
sysstat	physical writes non checkpoint                                 66667

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat	commit cleanouts                                                  21
sesstat	commit cleanouts successfully completed                           21
sesstat	consistent gets                                                72366
sesstat	db block changes                                                3898
sesstat	deferred (CURRENT) block cleanout applications                     7
sesstat	free buffer requested                                            818
sesstat	immediate (CURRENT) block cleanout applications                    8
sesstat	no work - consistent read gets                                 69415
sesstat	physical reads                                                 67149
sesstat	physical reads direct                                          66709
sesstat	physical writes                                                66667
sesstat	physical writes direct                                         66667
sesstat	physical writes non checkpoint                                 66667

6) PDBのSCOTTユーザーで遅延ブロッククリーンアウト有無確認（対象表をscattered readで全表走査）

IASで direct path writeさせた場合同様に、direct path writeで書き出された場合、クリーンアウトするブロックは存在しないため、遅延ブロッククリーンアウトも発生しないという状況になります。
REDOは多少生成あれていますが、recursive call 等によるものと考えられ、このテストケースではノイズの類程度です。システム／セッション統計値をみることでその点も確認できます。

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:06.66

Execution Plan
----------------------------------------------------------
Plan hash value: 1530105727

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   200K|   382M| 18174   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOGE2 |   200K|   382M| 18174   (1)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         14  recursive calls
         12  db block gets
      80022  consistent gets
      66669  physical reads
       2108  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

7) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認。対象表をscattered readで全表走査）
deferred (CURRENT) block cleanout applications　や、immediate (CURRENT) block cleanout applicationsなど遅延ブロッククリーンアウトで動く統計に極わずかに動きがありますが、数ブロックなので気にする程度ではないです。遅延ブロッククリーンアウトは発生していないと読み取れます。

また、physical reads は該当表のブロック数程度のブロック数となっており、physical reads direct が発生していないことから、 scattered read　でバッファキャッシュに載せられたことが確認できます。free buffer requested もほぼ同じ値になっていることからも同様のことが言えます。

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat	commit cleanouts                                                  16
sysstat	commit cleanouts successfully completed                           16
sysstat	consistent gets                                               105725
sysstat	db block changes                                                 255
sysstat	deferred (CURRENT) block cleanout applications                     8
sysstat	free buffer requested                                          67608
sysstat	immediate (CURRENT) block cleanout applications                    2
sysstat	no work - consistent read gets                                 94138
sysstat	physical reads                                                 67567

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat	commit cleanouts                                                   6
sesstat	commit cleanouts successfully completed                            6
sesstat	consistent gets                                                86719
sesstat	db block changes                                                  43
sesstat	deferred (CURRENT) block cleanout applications                     3
sesstat	free buffer requested                                          66791
sesstat	immediate (CURRENT) block cleanout applications                    2
sesstat	no work - consistent read gets                                 82976
sesstat	physical reads                                                 66783

---

まとめ

CTASはDDLで、　direct path write を伴うため、IASのAPPEND同様、対象データブロックはクリーンアウトが必要な状態にはならず、コミットおよび遅延ブロッククリーンアウトは発生しない！
ことが確認できました。　：）

次回は、一旦中締めにしますか。

つづく。

---

目がショボショボしてるから、なにか浮遊物の影響を受けてる気がする。ブタクサやヨモギの季節だもんな。（目だけなんだよなー。アレルギーの酷方のように鼻水でたりとかではなくて。疲れ目用の目薬では、効かない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
・古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #11

---

古くて新しい？ 遅延ブロッククリーンアウト (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 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

---

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

Previously on Mac De Oracle
前回は、SELECT文であっても遅延ブロッククリーンアウトが発生すると該当ブロックは更新され、REDOログも生成される。ただし、direct path read で読み込まれた場合を除く。
という動きをみました。

今回もdirect pathがらみです。といっても direct path write だったら、コミットクリーンアウトや、遅延ブロッククリーンアウトはどういう扱いになるのだろう。。。と
（このシリーズの初回で紹介したいろいろなブログに答えはあるのですがw）

システム統計やセッション統計の統計値から、それをどう読み取るか。γGTP高いから肝臓あたりに問題があるか、検査前日に酒飲んじゃったでしょ！　的なところを診て、なにがおきているか診察していくシリーズなので、それぞれの統計と、auto traceによる実行計画と実行統計を診ていくわけですが。。w

ということで、今回は、前回利用していた表を元にIASで別表を作成し、コミット前後の状況を診ていきたいとおもいます。IASと言っても direct path write　になるケースと従来型のロードがあるのは皆さんご存知だと思いますが、まずは、 direct path writeの方から挙動を診ていくことにします。手順はざっとこんな感じ

そういえば、以前、IASで、direct path write かどうか判別しやすくなったよねーというネタをやってましたね。実行計画だけでも違いがわかりやすくなっているので便利になりました。：）

実行計画は、SQL文のレントゲン写真だ！ No.30より、LOAD TABL CONVENTIONAL vs. LOAD AS SELECTの実行計画の違い。

---

---

0) 対象表のdrop/create

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

Table dropped.

Elapsed: 00:00:00.74
  1* create table hoge2 (id number, data varchar2(2000))

Table created.

Elapsed: 00:00:00.07
  1* select segment_name,blocks from user_segments where segment_name like '%HOGE2%'

no rows selected

Elapsed: 00:00:00.15

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したいので APPEND ヒントで direct path write を強制しています。 direct path read とは異なり、ヒントで制御できるのは楽w

SCOTT@orcl> @ias_from_hoge.sql
  1* insert /*+ append */ into hoge2 select * from hoge

200000 rows created.

Elapsed: 00:00:10.42

5) CDBのSYSで統計取得（APPENDヒント付きのIAS後、未コミット）

IASでHOGE表を読み込み、HOGE2表へdirect path write している様子が確認できますよね。HOGE表の読み込みは direct path read 、HOGE2表へは direct path write している状況がはっきりでていてわかりやすい結果を得られました :)
(HOGE表のデータが載っているブロック数は、66667 ブロックであることは前回も確認した通りの値です)

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat	commit cleanouts                                                  16
sysstat	commit cleanouts successfully completed                           16
sysstat	consistent gets                                                72460
sysstat	db block changes                                                3131
sysstat	deferred (CURRENT) block cleanout applications                    12
sysstat	free buffer requested                                            792
sysstat	immediate (CURRENT) block cleanout applications                    3
sysstat	no work - consistent read gets                                 69328
sysstat	physical reads                                                 67121
sysstat	physical reads direct                                          66709
sysstat	physical writes                                                66667
sysstat	physical writes direct                                         66667
sysstat	physical writes non checkpoint                                 66667

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat	commit cleanouts                                                  16
sesstat	commit cleanouts successfully completed                           16
sesstat	consistent gets                                                72373
sesstat	db block changes                                                3131
sesstat	deferred (CURRENT) block cleanout applications                    12
sesstat	free buffer requested                                            792
sesstat	immediate (CURRENT) block cleanout applications                    3
sesstat	no work - consistent read gets                                 69291
sesstat	physical reads                                                 67121
sesstat	physical reads direct                                          66709
sesstat	physical writes                                                66667
sesstat	physical writes direct                                         66667
sesstat	physical writes non checkpoint                                 66667

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

SCOTT@orcl> commit;

Commit complete.

7) CDBのSYSで統計取得（コミット後）
direct path writeでバッファキャッシュを経由せず書き出されたブロックはコミット時にはクリーンアウトの対象にはなっていないようですね。。。。統計をみる限りノイズ程度ですね。
ということは全てのブロックが遅延ブロッククリーンアウト対象になってしまうのか、または、その逆で、最初からクリーンアウト対象にもなっていないかということになります。次の全表走査の結果でどちらであるか、わかるはずです！！！！（ニヤニヤ、それ、ねらってやってるので、答えは知っているわけですがw）

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sysstat	commit cleanouts                                                  25
sysstat	commit cleanouts successfully completed                           25
sysstat	consistent gets                                                12104
sysstat	db block changes                                                1557
sysstat	deferred (CURRENT) block cleanout applications                    12
sysstat	free buffer requested                                            714
sysstat	immediate (CURRENT) block cleanout applications                    4
sysstat	no work - consistent read gets                                  7192
sysstat	physical reads                                                   705

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

SOURCE  NAME                                                           VALUE
------- ---------------------------------------------------- ---------------
sesstat	commit cleanouts                                                   2
sesstat	commit cleanouts successfully completed                            2
sesstat	consistent gets                                                  864
sesstat	db block changes                                                1474
sesstat	deferred (CURRENT) block cleanout applications                     1
sesstat	free buffer requested                                             37
sesstat	no work - consistent read gets                                   351
sesstat	physical reads                                                    32

8) PDBのSCOTTユーザーで遅延ブロッククリーンアウト有無確認（対象表をscattered readで全表走査）

REDOログは多少生成されていますが、実際に遅延ブロッククリーンアウトが発生した場合REDOログ量この程度では少なすぎますよね。
今回の検証目的からするとノイズの類ですね。むむむ。これは。。。。
（物理読み込みは発生しているので、direct path read か、scattered readのどちらかということにはなります。期待している動きは、Scattered read 。）

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:06.84

Execution Plan
----------------------------------------------------------
Plan hash value: 1530105727

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   200K|   382M| 18174   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOGE2 |   200K|   382M| 18174   (1)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         13  recursive calls
         12  db block gets
      80022  consistent gets
      66668  physical reads
       2284  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で全表走査）
physical reads は想定通りのブロック以上になっていますが、physical reads direct は発生していないので、scattered readによる全表走査であると読み取ることができます。
ただ、この状態でも、遅延ブロッククリーンアウトの発生を示す統計値はノイズ程度の値です。

つまり、direct path write でINSERTされたデータブロックはクリーンアウトが必要な状態だということになりますね。興味深い動きですよね。メモしておいたほうが良さそうです :)

統計値が変動したもののみ記載
(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

---

まとめ

IASで direct path write してINSERTされた場合、ブロッククリーンアウトは発生しない！（コミット時でも遅延でも）
そもそもクリーンアウトが必要な状態になっていないというのが正しいのでしょうね。統計値としては全く動いてないに等しいので。

これも試験にでますよ（嘘w

次回は、同じIASもdirect path write　ではないケースではどうなるでしょう。。。（ここまでのエントリーを読んでいる方は、結果は想像できそうではありますが）

次回へつづく。

---

遅めの夏休みですが、まあ、普段とちがうのは、いろいろなタスクの締め切りに追われずに、マイペースな時間の過ごし方になるぐらいだな。この状況下では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) #9 Tweet

Previously on Mac De Oracle
バッファキャッシュから溢れ出る程度のデータを登録しコミットした場合は、コミットクリーンアウトがどうなるか、遅延ブロッククリーンアウトされるブロック数はどの程度になるかという、寄り道でしたw

今回は、寄り道し過ぎて忘れるところだった、前回まで、scattered readを伴うtable full scanで発生していた遅延ブロッククリーンアウトが direct path read だったらどうなるか、というシリーズ :)　
(まだ続くのかーーーっ！　はいw　いろんなケースがありますから。シンプルなケースであっても。それぞれの基本的な挙動を知っいて損はないとおもいます。 複雑なケースだと脳汁出過ぎるくらい複雑なので考えたくもなくなるのでw)

では、早速再現してみましょう。手順はこれまで行なってきたとおりで、違いは遅延ブロッククリーンアウトを発生させるためのtable full scanでdirect path readさせるという部分のみ。手順はscattered readとの比較も入れるので長くなってしまうので、追加ステップを追記した図を見てもらうと何やっているか、流れは理解しやすいかもしれません。

事前準備
（バッファキャッシュのサイズは元のサイズに戻してあります）

SYS@orclcdb> show sga

Total System Global Area 4294963960 bytes
Fixed Size                  9143032 bytes
Variable Size             805306368 bytes
Database Buffers         3472883712 bytes
Redo Buffers                7630848 bytes

200,000行登録したデータ(セグメントサイズ農地純粋にデータが乗っているブロック数）が乗っているブロック数はこんなところ。セグメントサイズはこれより多いですよ。行データが載っているブロックだけカウントしているので。

SCOTT@orcl> select count(distinct dbms_rowid.rowid_block_number(rowid)) as "blocks" from hoge;

    blocks
----------
     66667

ということで、セグメントサイズも確認。

SCOTT@orcl> select segment_name,blocks from user_segments where segment_name = 'HOGE';

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
HOGE                                67584

バッファキャッシュの10-15%程度はコミットクリーンアウトされるので、間をとってこれぐらいはコミットクリーンアウトされる。。。

SCOTT@orcl> select 3472883712 / 8192 * 0.13 from dual;

3472883712/8192*0.13
--------------------
            55111.68

残りは遅延ブロッククリーンアウトする。だいだいこんなもん。

SCOTT@orcl> select 66667 - 55112 from dual;

66667-55112
-----------
      11555

direct path read狙いの全表走査（シリアル実行で発動させることを意図していますが、言うこときいてくれるかあなぁ）

$ cat table_full_scan_with_dpr.sql
alter session set "_serial_direct_read" = always
.
l
/
alter session set "_very_large_object_threshold" = 512
.
l
/

!echo set autot trace exp stat
set autot trace exp stat

select * from hoge
.
l
/

!echo set autot off
set autot off

Scattered Read狙いの全表走査のスクリプト

$ cat table_full_scan.sql
alter session set "_serial_direct_read" = never
.
l
/
alter session set "_very_large_object_threshold" = 20400
.
l
/

!echo set autot trace exp stat
set autot trace exp stat


select * from hoge
.
l
/

!echo set autot off
set autot off

---

---

さて、うまく再現できるかどうか。。(今回もやったことをほぼすべて載せているので長いです）

0) 対象表をdrop/create
オブジェクトを作り直し前提合せ

SCOTT@orcl> @droppurge_create_hoge

Table 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.00
SCOTT@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.48
SCOTT@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                                22909
sysstat	DBWR checkpoints                                                   3
sysstat	DBWR thread checkpoint buffers written                         22902
sysstat	DBWR transaction table writes                                      6
sysstat	DBWR undo block writes                                           573
sysstat	commit cleanouts                                                   6
sysstat	commit cleanouts successfully completed                            6
sysstat	consistent gets                                                49682
sysstat	db block changes                                              744727
sysstat	deferred (CURRENT) block cleanout applications                     4
sysstat	free buffer requested                                          68766
sysstat	immediate (CURRENT) block cleanout applications                    1
sysstat	no work - consistent read gets                                    82
sysstat	physical reads                                                     9
sysstat	physical writes                                                22909
sysstat	physical writes from cache                                     22909
sysstat	physical writes non checkpoint                                 22892

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

SOURCE  NAME　　　　　　　　　　　　　　　　　　　　　　　                     VALUE
------- ---------------------------------------------------- ---------------
sesstat	commit cleanouts                                                   6
sesstat	commit cleanouts successfully completed                            6
sesstat	consistent gets                                                49561
sesstat	db block changes                                              744727
sesstat	deferred (CURRENT) block cleanout applications                     4
sesstat	free buffer requested                                          68766
sesstat	immediate (CURRENT) block cleanout applications                    1
sesstat	no work - consistent read gets                                    40
sesstat	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                        15
sysstat	commit cleanouts                                               56077
sysstat	commit cleanouts successfully completed                        56062
sysstat	consistent gets                                                50494
sysstat	db block changes                                                7894
sysstat	deferred (CURRENT) block cleanout applications                   158
sysstat	free buffer requested                                           3217
sysstat	immediate (CURRENT) block cleanout applications                  154
sysstat	no work - consistent read gets                                 32764
sysstat	physical reads                                                  2962

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

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

Session altered.

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

Session altered.

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

  1* select * from hoge

200000 rows selected.

Elapsed: 00:00:10.84

Execution 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 processed

set 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                                44793
sysstat	DBWR checkpoints                                                   2
sysstat	DBWR object drop buffers written                                   2
sysstat	DBWR parallel query checkpoint buffers written                 44793
sysstat	cleanouts only - consistent read gets                          33057
sysstat	commit cleanouts                                                  18
sysstat	commit cleanouts successfully completed                           18
sysstat	consistent gets                                               120519
sysstat	db block changes                                                  97
sysstat	deferred (CURRENT) block cleanout applications                     8
sysstat	free buffer requested                                            127
sysstat	immediate (CR) block cleanout applications                     33057
sysstat	immediate (CURRENT) block cleanout applications                    5
sysstat	no work - consistent read gets                                 50145
sysstat	physical reads                                                 66827
sysstat	physical reads direct                                          66710
sysstat	physical writes                                                44795
sysstat	physical writes from cache                                     44795
sysstat	physical writes non checkpoint                                 44795

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

SOURCE  NAME　　　　　　　　　　　　　　　　　　　　　　　                     VALUE
------- ---------------------------------------------------- ---------------
sesstat	cleanouts only - consistent read gets                          33057
sesstat	commit cleanouts                                                   5
sesstat	commit cleanouts successfully completed                            5
sesstat	consistent gets                                               120131
sesstat	db block changes                                                  48
sesstat	deferred (CURRENT) block cleanout applications                     1
sesstat	free buffer requested                                            120
sesstat	immediate (CR) block cleanout applications                     33057
sesstat	immediate (CURRENT) block cleanout applications                    2
sesstat	no work - consistent read gets                                 49952
sesstat	physical reads                                                 66823
sesstat	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.00
SCOTT@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" = always

Session altered.

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

Session altered.

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

  1* select * from hoge

200000 rows selected.

Elapsed: 00:00:03.90

Execution 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 processed

set 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                                                   1
sysstat	DBWR object drop buffers written                                   2
sysstat	cleanouts only - consistent read gets                          10959
sysstat	commit cleanouts                                                  36
sysstat	commit cleanouts successfully completed                           36
sysstat	consistent gets                                               106766
sysstat	db block changes                                                 166
sysstat	deferred (CURRENT) block cleanout applications                    18
sysstat	free buffer requested                                           1130
sysstat	immediate (CR) block cleanout applications                     10959
sysstat	immediate (CURRENT) block cleanout applications                   10
sysstat	no work - consistent read gets                                 77392
sysstat	physical reads                                                 67827
sysstat	physical reads direct                                          66710
sysstat	physical writes                                                    2
sysstat	physical writes from cache                                         2
sysstat	physical writes non checkpoint                                     2

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

SOURCE  NAME　　　　　　　　　　　　　　　　　　　　　　　                     VALUE
------- ---------------------------------------------------- ---------------
sesstat	cleanouts only - consistent read gets                          10959
sesstat	commit cleanouts                                                   2
sesstat	commit cleanouts successfully completed                            2
sesstat	consistent gets                                                98223
sesstat	db block changes                                                  30
sesstat	deferred (CURRENT) block cleanout applications                     1
sesstat	free buffer requested                                            587
sesstat	immediate (CR) block cleanout applications                     10959
sesstat	no work - consistent read gets                                 72324
sesstat	physical reads                                                 67289
sesstat	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.00
SCOTT@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" = 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 hoge

200000 rows selected.

Elapsed: 00:00:04.84

Execution 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 processed

set 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                          10959
sysstat	commit cleanouts                                                   3
sysstat	commit cleanouts successfully completed                            3
sysstat	consistent gets                                                98846
sysstat	db block changes                                               10992
sysstat	deferred (CURRENT) block cleanout applications                     2
sysstat	free buffer requested                                          67078
sysstat	immediate (CR) block cleanout applications                     10959
sysstat	no work - consistent read gets                                 72660
sysstat	physical reads                                                 67069

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

SOURCE  NAME　　　　　　　　　　　　　　　　　　　　　　　                     VALUE
------- ---------------------------------------------------- ---------------
sesstat	cleanouts only - consistent read gets                          10959
sesstat	commit cleanouts                                                   2
sesstat	commit cleanouts successfully completed                            2
sesstat	consistent gets                                                98234
sesstat	db block changes                                               10988
sesstat	deferred (CURRENT) block cleanout applications                     1
sesstat	free buffer requested                                          67071
sesstat	immediate (CR) block cleanout applications                     10959
sesstat	no work - consistent read gets                                 72324
sesstat	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.00
SCOTT@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" = never

Session altered.

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

Session altered.

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

  1* select * from hoge

200000 rows selected.

Elapsed: 00:00:04.32

Execution 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 processed

set autot off

24) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）（対象表をscattered readで全表走査）4回目
physical reads はありますが、 physical reads direct は変化していません。これは scattered read で全データを読み込んだと見て良いでしょうね。読み込んだブロックサイズも該当表の想定データブロック数程度です。
また、遅延ブロッククリーンアウトが発生したことを示す統計は変化していないことから、遅延ブロッククリーンアウトは発生していないことも読み取れます。(^^)

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

SOURCE  NAME　　　　　　　　　　　　　　　　　　　　　　　                     VALUE
------- ---------------------------------------------------- ---------------
sysstat	commit cleanouts                                                   3
sysstat	commit cleanouts successfully completed                            3
sysstat	consistent gets                                                87659
sysstat	db block changes                                                  34
sysstat	deferred (CURRENT) block cleanout applications                     2
sysstat	free buffer requested                                          67058
sysstat	no work - consistent read gets                                 83510
sysstat	physical reads                                                 67048

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

SOURCE  NAME　　　　　　　　　　　　　　　　　　　　　　　                     VALUE
------- ---------------------------------------------------- ---------------
sesstat	commit cleanouts                                                   2
sesstat	commit cleanouts successfully completed                            2
sesstat	consistent gets                                                87049
sesstat	db block changes                                                  30
sesstat	deferred (CURRENT) block cleanout applications                     1
sesstat	free buffer requested                                          67051
sesstat	no work - consistent read gets                                 83175
sesstat	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

---

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

Previously on Mac De Oracle
前回からのつづき（ちょいと寄り道中）
です。

では、とっとと試してみましょうw

バッファキャッシュから溢れ出る程度のデータ量だったら、どうなるのかなーーーー、という検証です。やりたいことは図の通りです。

検証方法を考えていたのですが、自動共有メモリー管理になっているのと、sga_max_size/sga_targetを小さくしすぎるとOracle Databaseが起動しないなど諸々引きそうなので、shared_pool_sizeを大きく設定して、バッファキャッシュに回せるメモリーを減らすことで、バッファキャッシュを小さく、バッファから溢れる程度のデータ量も少なくて済むようにして試してみることにします。

準備段階から書いてます。再現させる環境をどうセットアップしたかっていうことも重要だと思うのですよね。少々長くなっちゃいますが。

---

検証準備

CDBに接続して初期化パラメータを調整！！

SGAコンポーネントの状況
Database Buffersが、3G以上になってます。検証データ量も多くなってしまうので、これを1GB程度まで下げたいですね。検証時間も節約できますし、最小の手数で検証できるほうが良いですから:)

SYS@orclcdb> show sga
Total System Global Area 4294963960 bytes
Fixed Size                  9143032 bytes
Variable Size             805306368 bytes
Database Buffers         3472883712 bytes
Redo Buffers                7630848 bytes

SYS@orclcdb> select 3472883712 / 1024 / 1024 AS "MB" from dual;

        MB
----------
      3312

Elapsed: 00:00:00.00

sga_max_size,sga_min_sizeが4GBですが、ここはそのままにします。あまり小さくしすぎると起動しなくなったり。(^^;;;;

SYS@orclcdb> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 4G
sga_min_size                         big integer 0
sga_target                           big integer 4G
unified_audit_sga_queue_size         integer     1048576

自動SGA管理なので、Shared Pool Sizeに大きめの値を設定。
自動SGA管理下で自動管理対象メモリーコンポーネントパラメータに値を設定した場合、その値が下限値となり最低でもその値は確保されるという仕組みを利用します！

SYS@orclcdb> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 39426457
shared_pool_size                     big integer 0

起動しなくなっても戻しやすいようにspfileをpfileに書き出して退避後に、shared_pool_sizeを3GBへ増やします。これで4Gのうちの3G程度がshared poolに割り当てられ、バッファキャッシュは1GBぐらいになるはず。（VirtualBoxなのでスナップショット取得しておいて戻すのもありですけどw）

SYS@orclcdb> create pfile='pfile20210912.ora' from spfile;

File created.

Elapsed: 00:00:00.00
SYS@orclcdb> alter system set shared_pool_size = 3g scope=both;

System altered.

Elapsed: 00:00:00.77

Database Buffersがいい感じにシュリンクしました。1GBぐらいになりました。これで進めますよー。

SYS@orclcdb> show sga
Total System Global Area 4294963960 bytes
Fixed Size                  9143032 bytes
Variable Size            3238002688 bytes
Database Buffers         1040187392 bytes
Redo Buffers                7630848 bytes

SYS@orclcdb> select 1040187392 / 1024 / 1024 AS "MB" from dual;

        MB
----------
       992

Elapsed: 00:00:00.01

ざっくりとブロック数を計算

SYS@orclcdb> select 1040187392 / 8192 AS "blocks"　from dual;

         blocks
---------------
         126976

Elapsed: 00:00:00.01

前回のHOGE表に200,000 rowsで、66,667 blocks のデータを生成したので、126,976 blocks を満たすデータ量にしようとすると 400,000 rowsほど必要になりそうですね。。。。少々多めで、バッファキャッシュから溢れる程度の量で、 500,000 rowsのデータを登録することにしましょう！！！！

SYS@orclcdb> select ceil(126976 / 66667) * 200000 AS "rows" from dual;

      rows
----------
    400000

Elapsed: 00:00:00.00

これまでの検証から 10%-15%程度がCOMMITクリーンアウトされ、残りが遅延されるのは確認できたので、126976 blocks のバッファキャッシュだと、　17,777 blocks ぐらいがコミットクリーンアウトされそうですね。(今回のテストケースではコミットクリーンアウトされないけど。。された場合は最大でこの程度。。。というメモです。はい)

SYS@orclcdb> select ceil(126976 * 0.14) AS "blocks for commit cleanout" from dual;

blocks for commit cleanout
--------------------------
                     17777

Elapsed: 00:00:00.00

前回作成したデータは、200,000rowsで、66,667 blocksだったので、500,000 rows だと、ざっくり　166,668 blocks ほど。

SCOTT@orcl> select ceil(66667 / 2 * 5) AS "blocks" from dual;

    blocks
----------
    166668

Elapsed: 00:00:00.00

なので、遅延ブロッククリーンアウトされると想定される（コミットクリーンアウト分を覗くと）ブロック数は、148,891 blocks 程度にはなりそう。

SYS@orclcdb>  select ceil((66667 / 2 * 5) - 17777) AS "blocks" from dual;
    blocks
----------
    148891

Elapsed: 00:00:00.01

それに加えて、バッファキャッシュに収まらず、コミットする前にバッファキャッシュから落とされ、ストレージへかきだされてしまうブロック数は、これまた、ざっくり計算すると　39,692 blocks ほどですかね。バッファキャッシュのサイズから全てのブロックは乗り切らないので、最初に読み込まれていたブロックから落とされていくことにはなりますね。。
とはいえ、この検証ではキャッシュ落とされるブロック数は特に気にしてなくて、バッファキャッシュ以上のブロック数が生成されていればいいので、落とされそうなのが確認できればOK.

SYS@orclcdb>  select ceil((66667 / 2 * 5) - 126976) AS "blocks" from dual;

    blocks
----------
     39692

Elapsed: 00:00:00.00

とりあえず、生成するデータ量（行数）の算出とバッファキャッシュサイズの調整はおわり。

次に、実際にデータを生成してブロック数とセグメントサイズを確認して、実行用スクリプトの調整を行なっておきます。

---

PDBでテストデータの実サイズの確認

SCOTT@orcl> @droppurge_create_hoge.sql
  1* drop table hoge purge

Table dropped.

Elapsed: 00:00:00.25
  1* create table hoge (id number, data varchar2(2000))

Table created.

Elapsed: 00:00:00.04
  1* select segment_name,blocks from user_segments where segment_name like '%HOGE%'

no rows selected

Elapsed: 00:00:00.11

500,000行登録！！

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

PL/SQL procedure successfully completed.

Elapsed: 00:02:37.68

データが登録されているブロック数は、166,667 blocks で、事前に計算していた　166,668 blocks にほぼおなじ。（狙い通り）
セグメントサイズは、約 1344 MB ですね。

SCOTT@orcl> select count(distinct dbms_rowid.rowid_block_number(rowid)) as "blocks" from hoge;

    blocks
----------
    166667

Elapsed: 00:00:05.03
SCOTT@orcl> select segment_name,blocks,bytes/1024/1024 AS "MB" from user_segments where segment_name = 'HOGE';

SEGMENT_NAME                       BLOCKS         MB
------------------------------ ---------- ----------
HOGE                               172032       1344

Elapsed: 00:00:00.17

Scattered read でTable Full Scanできるように少々隠しパラメータを調整しておきますね。念の為。（セッションレベルで調整してます）
セグメントサイズが、1344 MBなので、_very_large_object_threshold　は、2048 MBぐらい設定しておけば、Scattered readのまま行けそうですね。

$ cat table_full_scan.sql
alter session set "_serial_direct_read" = never
.
l
/
alter session set "_very_large_object_threshold" = 2040
.
l
/

!echo set autot trace exp stat
set autot trace exp stat


select * from hoge
.
l
/

!echo set autot off
set autot off

準備完了！！！！！

---

---

準備長かったけどw　やっと本題です！！！w　実行している内容はいままでと同じなのでかなり端折ってポイントだけ記載。

バッファキャッシュから溢れるほどのデータ量で。コミットクリーンアウトはどうなるのだろうか。。。。想定では、ほぼコミットクリーンアウトできないはずではあるのだが。。。。

CDBのSYSで統計取得（コミット後）

commit cleanouts successfully completedはどれぐらいだったのか。。。。。ありません。commit cleanouts successfully completedに差分がなかったので、コミットクリーンアウトしようして失敗、commit cleanout failures: block lostと同数なので、１ブロックもコミットクリーンアウトできない！　　commit cleanout failures: block lostがバッファキャッシュに対象ブロックがなかったことを示しています。

つまり、バッファキャッシュに乗り切らなため、最初にINSERTされたブロックはそのままストレージへ物理書き込みされて追い出された結果。。ということになりますね。

登録したブロック全てが遅延クリーンアウト対象になってしまった、ということになります。コミット時にクリーンアウトできてないわけだから！！！！！！！！　（イメージ図でざっくり書いたとおりの感じに。。。

差分のあった統計のみ記載

CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	commit cleanout failures: block lost                              3028
sysstat	commit cleanouts                                                  3028
sysstat	consistent gets                                                    274
sysstat	db block changes                                                     1
sysstat	no work - consistent read gets                                     149
sysstat	physical reads                                                      60

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	commit cleanout failures: block lost                              3028
sesstat	commit cleanouts                                                  3028
sesstat	db block changes                                                     1

次に、Scattered Readが実行される全表走査を行わせ、遅延ブロッククリーンアウトどれだけ発生するか結果確認！

PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）
想定通り、物理読み込み（この時点ではscattered readなのか、direct path readなのかわかりませんが）になっています。また、大量のREDOログが生成されているので遅延ブロッククリーンアウトが発生しています。

SCOTT@orcl> @table_full_scan.sql
  1* alter session set "_serial_direct_read" = never

Session altered.

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

Session altered.

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

  1* select * from hoge

500000 rows selected.

Elapsed: 00:01:03.08

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   669K|   647M| 46462   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| HOGE |   669K|   647M| 46462   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
         46  recursive calls
         13  db block gets
     367852  consistent gets
     160269  physical reads
   14670268  redo size
 1016952118  bytes sent via SQL*Net to client
     367706  bytes received via SQL*Net from client
      33335  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     500000  rows processed

set autot off

物理読み込みを伴う全表走査でどの程度の遅延ブロッククリーンアウトが発生したか統計を確認！！！！

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

想定どおり、INSERTした全ブロックが、immediate (CR) block cleanout applications = 166667 で遅延ブロッククリーンアウトされたことがわかります。（冒頭に記載していますが、データが格納されているブロック数は、　166667 blocks　でしたよね）
physical readsは意図通り発生していますが、physical reads directが変化していないので、狙い通りScattered Readになったようですね

差分のあった統計のみ記載

CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	DBWR checkpoint buffers written                                    241
sysstat	DBWR thread checkpoint buffers written                             241
sysstat	DBWR undo block writes                                            1109
sysstat	cleanouts only - consistent read gets                           166667
sysstat	commit cleanouts                                                    55
sysstat	commit cleanouts successfully completed                             55
sysstat	consistent gets                                                 377331
sysstat	db block changes                                                166904
sysstat	deferred (CURRENT) block cleanout applications                      29
sysstat	immediate (CR) block cleanout applications                      166667
sysstat	immediate (CURRENT) block cleanout applications                     15
sysstat	no work - consistent read gets                                   38449
sysstat	physical reads                                                  160805
sysstat	physical writes                                                 137551
sysstat	physical writes from cache                                      137551
sysstat	physical writes non checkpoint                                  137466

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	cleanouts only - consistent read gets                           166667
sesstat	commit cleanouts                                                     6
sesstat	commit cleanouts successfully completed                              6
sesstat	consistent gets                                                 374538
sesstat	db block changes                                                166710
sesstat	deferred (CURRENT) block cleanout applications                       3
sesstat	immediate (CR) block cleanout applications                      166667
sesstat	immediate (CURRENT) block cleanout applications                      1
sesstat	no work - consistent read gets                                   36779
sesstat	physical reads                                                  160513

念の為、今一度、物理読み込みを伴う全表走査を行なって、クリーンアウトされたのか確認してみましょうw（疑い深いw）
もう一度、PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）

redo sizeが 0 なのでクリーンアウトは発生してない。想定通り

SCOTT@orcl> @table_full_scan.sql
  1* alter session set "_serial_direct_read" = never

Session altered.

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

Session altered.

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

  1* select * from hoge

500000 rows selected.

Elapsed: 00:00:33.17

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   669K|   647M| 46462   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| HOGE |   669K|   647M| 46462   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     200399  consistent gets
     148198  physical reads
          0  redo size
 1016952118  bytes sent via SQL*Net to client
     367706  bytes received via SQL*Net from client
      33335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     500000  rows processed

set autot off

同様に、統計でも確認してみます！
CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）

クリーンアウトを示す統計値は上昇していません！！！（うんうんw）

差分のあった統計のみ記載

CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	DBWR checkpoint buffers written                                    732
sysstat	commit cleanouts                                                     1
sysstat	commit cleanouts successfully completed                              1
sysstat	consistent gets                                                 200522
sysstat	db block changes                                                    13
sysstat	deferred (CURRENT) block cleanout applications                       1
sysstat	no work - consistent read gets                                  200403
sysstat	physical reads                                                  148200
sysstat	physical writes                                                  68570
sysstat	physical writes from cache                                       68570
sysstat	physical writes non checkpoint                                   68331

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	consistent gets                                                 200414
sesstat	db block changes                                                     9
sesstat	no work - consistent read gets                                  200366
sesstat	physical reads                                                  148198

---

OK. Done. ということで、まとめ！

バッファキャッシュには収まりきれないデータ量の場合、コミットクリーンアウトしようとしていたブロックも追い出されてしまうので、結果的に、全ブロックが遅延ブロッククリーンアウトになった。というイメージしていた結果の通りでした。
（今回のケースもシンプルケースなので比較的予想しやすい結果ですが、クリーンアウトに関わる統計は以外に多く、複雑な動きになるものもあります。再現するののめんどくさいのでしませんがw）

寄り道はここまで、次回は、こんどこそ、direct path readと遅延ブロッククリーンアウトの関係をみていきたいと思います。

来週天気いいかなー。遅い夏休みなのに。微妙な気がしてきた。。。。。

---

・古くて新しい？ 遅延ブロッククリーンアウト (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) #7 Tweet

Previously on Mac De Oracle
前回は、コミットクリーンアウトと遅延クリーンアウト、そして、そこにTable Full ScanでScattered Read （待機イベントだと db file scattered read) を絡めてストレージへ永続化されたクリーンアウトが遅延されてしまったブロックを物理読み込みませつつ遅延ブロッククリーンアウトを再現させてみました。
また、次回は、図中のscattered read 部分を direct path read にしつつ、最後の最後で、scattered read　にしてみる、とか、そんなイメージをぼやーーーーんと浮かべながら、発生させる方法をどうするか考えてますw。つづく。なんてことを言っていましたが、またまた、ちょいと寄り道ですしますw

バッファキャッシュから溢れるぐらいのデータをぐるぐる系INSERTで、しかも1回のコミットにしたら、コミット前にあふれたデータはストレージへ書き出され、かつ、クリーンアウトも遅延されるよなー。という予想を元に、ちょいと遊んでから次に進みたいw　と思います。

これまでの流れから、基本的なクリーンアウトおよび遅延ブロッククリーンアウトとしては以下ようなパターンを確認してきました。

バッファキャッシュの上でコミットクリーンアウトおよび、遅延ブロッククリーンアウト（単純なタイプ）が行われているケース

ここからが想像というか、私が理解している範囲から想像した動き。バッファキャッシュから溢れはしないけど、いっぱいいっぱいな場合は、クリーンアウトされるブロックがキャッシュ上に多くあるだろうな。と.
とは言っても、バッファキャッシュ上ではあるわけです。

そこで、ちょいと意地悪をして、バッファキャッシュから溢れ出る程度のデータ量だったどうなるのかなーーーーと。冒頭ですでコメントしているわけですけどもw　多分、以下のような動きだよねー、と。
そういえば、以前、DBTSで行なったセッションの「バッファキャッシュ欠乏症」の部分で、似たようなバッファキャッシュから溢れ出したブロックの挙動をなんとかするみたいな資料も今回の動きを想像するにはよいかもしれないですね。

と、思い、頭の中のイメージを Pagesでざざっと作ったところで、本日はここまで。次回へつづく。

Beat SaberとWalkingの合わせ技で、効果的な減量継続中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) #6 Tweet

Previously on Mac De Oracle
前々回と前回はバッファキャッシュの10%を超えるデータ量のINSERT文の実行とCOMMITの実行で、バッファキャッシュの10%-15%程度は、COMMIT時にクリーンアウトされ、残ったブロックのクリーンアウトは先送りされる。という検証を2つのパターンで確認してみました。

どのような流れで発生するかを各ステップ毎にシステム統計（CDB）とクエリーを実行するセッションのセッション統計（PDB)を取得し、どのように統計値が変化すれば、コミットクリーンアウトや遅延ブロッククリーンアウトが起きているのかを見ながらすすめました。以下２つのエントリーで確認した動きの違いはイメージできたのか少々不安ではありますがw　（そこそこ長いエントリーなのでw）

こちら前々回は、クリーンアウトが遅延されたブロックが永続化される前に、遅延ブロッククリーンアウトさせてみたケース
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #4
で、

前回は、クリーンアウトが遅延されたブロックが永続化された後に、遅延ブロッククリーンアウトさせてみたケース
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #5

なんです　：）　

---

まだ、イメージつかめない方もいるかもしれないので

超ざっくりした絵が頭の中に浮かばない方向けに、上記検証を行う前に、私の頭の中にうかんだ、ラフイメージをほぼそのまま

（こまけーとこは気にしないでくださいね。ラフイメージですから、こうだろうなーというのを想像している状態そのままのイメージですのでw）

クリーンアウトが遅延されたブロックが永続化される前に、遅延ブロッククリーンアウトさせてみたケース
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #4

クリーンアウトが遅延されたブロックが永続化された後に、遅延ブロッククリーンアウトさせてみたケース
古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #5

再現させてるケースはシンプルなものなので処理時間云々を比較してはいないですが、複雑なケースになると、本来スマートスキャンさせたいのにシングルブロックリードが多くなったりするケースなど、以前紹介したURLを見ていただければ参考になるかもしれないですね。
クエリーやDMLの処理時間が伸びてビビるぐらいに仕事量が増えてたり、先送りされたことで、もろもろ後処理が複雑化する場合もあるわけで）

ということで、こんな図をイメージしながら、ネタ作ってます。はいw

次回は、図中のscattered read 部分を direct path read にしつつ、最後の最後で、scattered read　にしてみる、とか、そんなイメージをぼやーーーーんと浮かべながら、発生させる方法をどうするか考えてますw。つづく。

---

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

---

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

前回はバッファキャッシュの10%を超えるデータブロックへのINSERT文の実行とCOMMITの実行で、バッファキャッシュの13%-15%程度はCOMMIT時にクリーンアウトされ、残りは遅延ブロッククリーンアウト（先送り）される。
direct path readではないSELECT文による(前回のケースでは scattered read）)遅延ブロッククリーンアウトは、１度のみ発生するという状況を確認しました。

ところで、
前回のエントリで、2度、全表走査（前回の検証ではscattered read）を実行しているのですが、物理読み込みは発生させていません。（INSERT→COMMIT→SELECT→SELECTという流れで、十分なバッファキャッシュがあるので、当然ではあるのですがw）
バッファキャッシュに乗ったままのブロックが遅延クリーンアウトされていたわけです。

前回のエントリ：古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #4

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

1. 遅延ブロッククリーンアウトを発生させた場合。遅延ブロッククリーンアウト対象のデータがバッファキャッシュ上ににあるためクリーンアウトに伴う物理読み込みはない。

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

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 processed

前述の1.2.それぞれの実行で物理読み込みだったなにか変化はあるのだろうか。。。予想では、上記に加えてscattered readに伴う物理読み込みが増えるだけのはずです。その動きを見てみることにします。（こういう動きを見ていると楽しいですよねw）
手順は前回と同じですが、各全表走査の前にインスタンスを再起動してバッファキャッシュを空にしておきます。
(buffer cacheをflushすればいいじゃん。という声も聞こえてきそうですが、今回は再起動でクリアしました。はいw)

前回から多少変更したスクリプトも載せておきます（本文中にもありますが）、細かい解説は後述

$ cat table_full_scan.sql
alter session set "_serial_direct_read" = never
.
l
/
alter session set "_very_large_object_threshold" = 1056
.
l
/

!echo set autot trace exp stat
set autot trace exp stat


select * from hoge
.
l
/

!echo set autot off
set autot off

---

0) 対象表をdrop/create
オブジェクト作り直し

SCOTT@orcl> @droppurge_create_hoge

Table dropped.

Table created.

SCOTT@orcl> select segment_name,blocks from user_segments where segment_name like '%HOGE%';

no rows selected

1) 統計をクリアするのにインスタンス再起動

$ 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.00
SCOTT@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.48
SCOTT@orcl>

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

未コミットの状態なので特に、気にせず、ふーーーん。ぐらいの感じで眺めていただければいいですね。前回同様に、commit cleanouts, commit cleanouts successfully completed, deferred (CURRENT) block cleanout applications, immediate (CURRENT) block cleanout applicationsといったクリーンアウト関連統計が極わずかありますが、この時点で発生しているのは本題ではないので気にしなくてOK

差分のある統計のみ記載

(CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	DBWR checkpoint buffers written                                  23589
sysstat	DBWR checkpoints                                                     2
sysstat	DBWR thread checkpoint buffers written                           22643
sysstat	DBWR transaction table writes                                       50
sysstat	DBWR undo block writes                                             848
sysstat	cleanouts and rollbacks - consistent read gets                       5
sysstat	commit cleanout failures: callback failure                          20
sysstat	commit cleanouts                                                  1320
sysstat	commit cleanouts successfully completed                           1300
sysstat	consistent gets                                                 124041
sysstat	db block changes                                                757165
sysstat	deferred (CURRENT) block cleanout applications                     715
sysstat	immediate (CR) block cleanout applications                           5
sysstat	immediate (CURRENT) block cleanout applications                    196
sysstat	no work - consistent read gets                                   46398
sysstat	physical reads                                                    4063
sysstat	physical writes                                                  23589
sysstat	physical writes from cache                                       23589
sysstat	physical writes non checkpoint                                   23494

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	commit cleanouts                                                     6
sesstat	commit cleanouts successfully completed                              6
sesstat	consistent gets                                                  49596
sesstat	db block changes                                                744727
sesstat	deferred (CURRENT) block cleanout applications                       4
sesstat	immediate (CURRENT) block cleanout applications                      1
sesstat	no work - consistent read gets                                      49
sesstat	physical reads                                                      15

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

SCOTT@orcl> commit;

Commit complete.

7) CDBのSYSで統計取得（コミット後）
前回同様ノイズもなく、綺麗にコミット時のクリーンアウトが発生しています。バッファキャッシュの約14-5%程度なのは前回と変わらずですね。

差分のある統計のみ記載

(CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	commit cleanouts                                                 55700
sysstat	commit cleanouts successfully completed                          55700
sysstat	db block changes                                                     1

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

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

8) Oracle Databaseを再起動してバッファキャッシュをクリア

ここが前回と違う手順で、クリーンアウトされないブロックはずーーーーーっと残るよね。ということの確認でもあります。（alter system flush buffer_cacheでも同じことができるわけですが、ここでは再起動しています）

$ sudo service oracle restart
[sudo] password for oracle:
Restarting oracle (via systemctl):                         [  OK  ]
$

9) PDBのscottでログインし、client_infoをセット

disconnectしたので再度、client infoをセットし直し！

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.00
SCOTT@orcl>

10) CDBのSYSで統計取得（再起動後初回）

内容は省略！（再起動したので統計値の差分取得用ベースライン統計の取得）

SYS$orclcdb> @show_stat scott

11) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）- scattered read / table full scan の1回目

ここでは待機イベントまでは確認できませんが、Table full scanと実行統計よりRedoログがたっぷり生成されていることは確認できます。SELECT文ですが。。。つまり、遅延ブロッククリーンアウトが発生しているということですね。確認は後述の統計で。
狙い通り、物理読み込みも発生しています！

Note)
"_very_large_object_threshold" = 1056　としているのは、direct path readとなる上限セグメントサイズをhoge表が超えているためdirect path readを抑止するためにこの隠しパラメータで上限値を引き上げ、scattered readになるように強制しています。

SCOTT@orcl> @table_full_scan.sql
  1* alter session set "_serial_direct_read" = never

Session altered.

Elapsed: 00:00:00.01
  1* alter session set "_very_large_object_threshold" = 1056

Session altered.

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

  1* select * from hoge

200000 rows selected.

Elapsed: 00:00:06.10

Execution 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
----------------------------------------------------------
         38  recursive calls
         13  db block gets
      91519  consistent gets
      67073  physical reads
     967432  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
          5  sorts (memory)
          0  sorts (disk)
     200000  rows processed

set autot off
SCOTT@orcl>

12) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）/ scattered read / table full scan の１回目

遅延ブロッククリーンアウト関連統計値が上昇しているので、遅延ブロッククリーンアウトの発生が確認できます。ここまでは前回と同じ。（同じじゃないと困りますがw）

違う点は、事前にインスタンスを再起動しているため、physical reads が上昇しています。これは hoge表を scattered readで全表走査しているからです。（phsical read directは発生していない）バッファキャッシュを経由するのでconsistent gets,no work - consistent read gets も上昇しています。狙い通りです。

そして、重量な遅延ブロッククリーンアウトですが、バッファキャッシュでヒットしていた時と同数のブロックで発生しています。（ニッコリ

インスタンスの停止や起動があったとしても、クリーンアウトが先送りされたブロックはアクセスされない限りクリーンアウトされず残っているということを示しています！！！！（ここ試験にでますよー。嘘）

(CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	cleanouts only - consistent read gets                            10967
sysstat	commit cleanouts                                                     7
sysstat	commit cleanouts successfully completed                              7
sysstat	consistent gets                                                  98967
sysstat	db block changes                                                 11016
sysstat	deferred (CURRENT) block cleanout applications                       3
sysstat	immediate (CR) block cleanout applications                       10967
sysstat	immediate (CURRENT) block cleanout applications                      2
sysstat	no work - consistent read gets                                   72543
sysstat	physical reads                                                   67403

(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                                                  98357
sesstat	db block changes                                                 11012
sesstat	deferred (CURRENT) block cleanout applications                       2
sesstat	immediate (CR) block cleanout applications                       10967
sesstat	immediate (CURRENT) block cleanout applications                      2
sesstat	no work - consistent read gets                                   72208
sesstat	physical reads                                                   67397

13) Oracle Databaseを再起動してバッファキャッシュをクリア

再度、インスタンスを再起動して、バッファキャッシュをクリアします。後続の全表走査では、遅延ブロッククリーンアウトは発生せず、物理読み込み(この検証では scattered readさせています)を伴うTable full scanが行われるだけのはずです。

$ sudo service oracle restart
[sudo] password for oracle:
Restarting oracle (via systemctl):                         [  OK  ]
$

14) PDBのscottでログインし、client_infoをセット

disconnectしたので再度、client infoをセットし直し！

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.00
SCOTT@orcl>

15) CDBのSYSで統計取得（再起動後初回）

内容は省略！（再起動したのでベースラインとなる統計を取得）

SYS$orclcdb> @show_stat scott

16) PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）- scattered read / table full scan の2回目

実行統計から、physical readsが、発生しています。Redoは生成されていないことも読み取れるので、遅延ブロッククリーンアウトは発生していないことも確認できます。：）　想定通りですね。

SCOTT@orcl> @table_full_scan.sql
  1* alter session set "_serial_direct_read" = never

Session altered.

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

Session altered.

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

  1* select * from hoge

200000 rows selected.

Elapsed: 00:00:06.44

Execution 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
----------------------------------------------------------
         30  recursive calls
          0  db block gets
      80211  consistent gets
      66719  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
          5  sorts (memory)
          0  sorts (disk)
     200000  rows processed

set autot off
SCOTT@orcl>

17) CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）/ scattered read / table full scan の2回目

physical reads は発生していますが、physical reads directではないことが確認できます。Table full scanをscattered readで読み込んでいるという想定通りの結果。　
遅延ブロッククリーンアウトを示deferred (CURRENT) block cleanout applications 、immediate (CURRENT) block cleanout applicationsや、コミットクリーンアウトを示すcommit cleanouts 、commit cleanouts successfully completed という統計が極わずかに変動していますが、今回の検証ではノイズなので気にしたくてOK。

(CDB）システム統計

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sysstat	DBWR checkpoint buffers written                                     11
sysstat	commit cleanouts                                                    15
sysstat	commit cleanouts successfully completed                             15
sysstat	consistent gets                                                  87375
sysstat	db block changes                                                    79
sysstat	deferred (CURRENT) block cleanout applications                      12
sysstat	immediate (CURRENT) block cleanout applications                      3
sysstat	no work - consistent read gets                                   83322
sysstat	physical reads                                                   67028
sysstat	physical writes                                                     11
sysstat	physical writes from cache                                          11
sysstat	physical writes non checkpoint                                      11

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	commit cleanouts                                                     2
sesstat	commit cleanouts successfully completed                              2
sesstat	consistent gets                                                  86936
sesstat	db block changes                                                    29
sesstat	deferred (CURRENT) block cleanout applications                       1
sesstat	no work - consistent read gets                                   83098
sesstat	physical reads                                                   67023

---

まとめ

遅延ブロッククリーンアウトは、インスタンスを停止して残ったままということが確認できました。（クリアされるまで残るのですよねー）
クリーンアウトが遅延されているブロックが物理読み込みされた（アクセスされた）タイミングで遅延ブロッククリーンアウトが発生することも確認できました。（物理読み込みの有無には関係しない）
バッファキャッシュ上の遅延ブロッククリーンアウト同様、クリーンアウトされたブロックでは、再度、クリーンアウト対象になるような更新が発生しなければ、遅延ブロッククリーンアウトは発生しない

久しぶりにシステム統計やセッション統計を見ててワクワクしてきましたよーーーーっw

ところで、
冒頭でも記載しましたが、バッファキャッシュ上の遅延ブロッククリーンアウト（再掲）に加え、物理読み込みを伴う遅延ブロッククリーンアウトのauto traceの結果をまとめて記載しておきます。SELECT文ですが、REDOログが生成されている場合は遅延ブロッククリーンアウトが発生しているということになります。

実行計画

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

1. 遅延ブロッククリーンアウトを発生させた場合。遅延ブロッククリーンアウト対象のデータがバッファキャッシュ上ににあるためクリーンアウトに伴う物理読み込みはない。

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

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 processed

3. 遅延ブロッククリーンアウトを発生させた場合。遅延ブロッククリーンアウト対象のデータを物理読み込みし、バッファキャッシュに載せている動きが見えます。

Statistics
----------------------------------------------------------
         38  recursive calls
         13  db block gets
       91519  consistent gets
      67073  physical reads
     967432  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
          5  sorts (memory)
          0  sorts (disk)
     200000  rows processed

4. 3.の直後に再度全表走査した場合。事前にキャッシュをクリアしているため、物理読み込みがありますが、遅延ブロッククリーンアウトは発生していません。（Redoが生成されていないことで確認できます）

Statistics
----------------------------------------------------------
        30  recursive calls
         0  db block gets
      80211  consistent gets
      66719  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
         5  sorts (memory)
         0  sorts (disk)
    200000  rows processed

ではでは。
次回は、scattered readのtable full scanではなく、direct path readだったらどうなるのか調べてみましょうか。。（いろいろな再現方法があるわけですが、手間のかからないお手軽な再現方法で確認してみようと思いますw）

---

洗濯機の修理が終わって一安心w　ということで、次回へつづく。

---

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

---

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

Previously on Mac De Oracle
前回はバッファキャッシュの10%未満のデータブロックへのINSERT文の実行とCOMMITの実行で、遅延ブロッククリーンアウトは発生せず、COMMIT時にすべての対象ブロックがクリーンアウトされるということを確認しました。

今回は、そのデータ量を倍にして、バッファキャッシュの10％程度を超えるデータブロックが遅延ブロッククリーンアウトされるのかを見ていくことにします。手順は前回と同じですが、遅延ブロッククリーンアウトさせた後で、もう一度全表走査してクリーンアウトが繰り返されないことも確認しておきます（次回以降に予定している確認への伏線なのですがw）

---

0) 対象表をdrop/create
オブジェクトを作り直して前提条件を合わせておきます

SCOTT@orcl> @droppurge_create_hoge

Table 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.00
SCOTT@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.48
SCOTT@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                                  22756
sysstat	DBWR checkpoints                                                    33
sysstat	DBWR thread checkpoint buffers written                           22756
sysstat	DBWR transaction table writes                                       22
sysstat	DBWR undo block writes                                             606
sysstat	consistent gets                                                  49761
sysstat	db block changes                                                744980
sysstat	deferred (CURRENT) block cleanout applications                       4
sysstat	immediate (CURRENT) block cleanout applications                      1
sysstat	no work - consistent read gets                                     192
sysstat	physical reads                                                      18
sysstat	physical writes                                                  22756
sysstat	physical writes from cache                                       22756
sysstat	physical writes non checkpoint                                   22756

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	consistent gets                                                  49501
sesstat	db block changes                                                744980
sesstat	deferred (CURRENT) block cleanout applications                       4
sesstat	immediate (CURRENT) block cleanout applications                      1
sesstat	no work - consistent read gets                                      65
sesstat	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                                                 55700
sysstat	commit cleanouts successfully completed                          55700
sysstat	db block changes                                                     1

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

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

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

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

Session altered.

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

200000 rows selected.

Elapsed: 00:00:05.14

Execution 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                                    272
sysstat	DBWR thread checkpoint buffers written                             272
sysstat	cleanouts only - consistent read gets                            10967
sysstat	commit cleanouts                                                    16
sysstat	commit cleanouts successfully completed                             16
sysstat	consistent gets                                                 117000
sysstat	db block changes                                                 11207
sysstat	deferred (CURRENT) block cleanout applications                      10
sysstat	immediate (CR) block cleanout applications                       10967
sysstat	immediate (CURRENT) block cleanout applications                      2
sysstat	no work - consistent read gets                                   83351
sysstat	physical reads                                                     939
sysstat	physical writes                                                    272
sysstat	physical writes from cache                                         272
sysstat	physical writes non checkpoint                                     260

(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                                                  98162
sesstat	db block changes                                                 11011
sesstat	deferred (CURRENT) block cleanout applications                       2
sesstat	immediate (CR) block cleanout applications                       10967
sesstat	immediate (CURRENT) block cleanout applications                      2
sesstat	no work - consistent read gets                                   72113
sesstat	physical reads                                                     122

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

そして、ここからがおまけの確認ステップです

もう一度、同じ表を全表走査してみます。どうなると思いますか？　遅延されていたブロッククリーンアウトも行われたのですから、当然、該当オブジェクトで遅延ブロッククリーンアウトは発生しない。はず！　ですよね。

確認してみましょう。（発生してたらどうしようw、もうしそうなったらバグレポートでも上げようかなw）

.......

Redoは生成されてない！　（よかった！　想定どおりだ！w）

SCOTT@orcl> @table_full_scan
  1* alter session set "_serial_direct_read" = never

Session altered.

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

  1* select * from hoge

200000 rows selected.

Elapsed: 00:00:05.43

Execution 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 processed

set autot off

SCOTT@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                                                     1
sysstat	commit cleanouts successfully completed                              1
sysstat	consistent gets                                                  80150
sysstat	db block changes                                                    13
sysstat	deferred (CURRENT) block cleanout applications                       1
sysstat	no work - consistent read gets                                   80075

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

SOURCE  NAME                                                             VALUE
------- ------------------------------------------------- --------------------
sesstat	consistent gets                                                  80076
sesstat	db block changes                                                     9
sesstat	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

---

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

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

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

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

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

前述の手順で、各操作後の統計の差分（変化量）を見る。マニュアルの統計の説明ってざっくり過ぎてよくわからないのが多いわけですが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

---