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

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

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

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

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

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

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

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

前回はバッファキャッシュの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

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

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

---

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

Previously on Mac De Oracle

前回は、遅延ブロッククリーンアウトに限らず、大きく変わったわけでも、最近実装された機能でもないのに意外に知られてないのか、良いところ悪いところ含め、現場でロストしてしまっているような知識って意外と多いのかもねー。なんて感じたので遅延ブロッククリーンアウトネタのURLリンクをまとめてみた。

続編書くにしても、同じようなことやっても面白くないので、ブロックダンプのような方法は使わず、[g]v$sysstatや[g]v$sesstatなどの統計から、ちょいと血糖値や尿酸値高めだよね的な角度からどのような変化が起きるか見ていくことにした :)

環境はVirtualBox上の19cでこれからの主流になるマルチテナントで試してみます。（非マルチテナントでの変化見ててもこれからはあまり役に立たないので）

今日は準備編

VirtualBox
https://www.virtualbox.org/

Pre-Built Developer VMs (for Oracle VM VirtualBox)のDatabase App Development VMとか
https://www.oracle.com/downloads/developer-vm/community-downloads.html

事前に準備しておくスクリプトは以下のとおり。繰り返し実行するので作っておくと便利ですよ。:)

まず最初に、遅延ブロッククリーンアウトはバッファキャッシュの10%ほどのブロックをコミット時にクリーンアウトして、残りを先送りするという基本的なお約束があるので、上記環境のOracle 19cがどの程度のバッファキャッシュなのかとブロックサイズを確認。これ大切ですよ。スクリプト準備する上でも　：）

メモリサイズは大きめですが。。。w　うちのは。(^^;;;

$ VBoxManage -v
6.1.26r145957
$
$ VBoxManage showvminfo 'Oracle DB Developer VM　19.3' | grep -E 'Memory|CPUs'
Memory size:                 16384MB
Number of CPUs:              4
$

コミット時にブロッククリーンアウトされそうなブロック数をざっくり算出すると　42394　ブロックぐらいになりそう。

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>
SYS@orclcdb> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SYS@orclcdb>
SYS@orclcdb> select ceil( 3472883712 / 8192 * 0.1 ) from dual;

CEIL(3472883712/8192*0.1)
-------------------------
                    42394

Elapsed: 00:00:00.00

42394ブロックを超える程度のサイズのデータを生成するINSERT文と、その範囲に収まるデータ量生成INSERT文スクリプトを作れば良さそうですね。

想定されるブロックに収まる程度の量。1ブロック 8KB　でデフォルトのPCTFREEは10%なのでざっくり6000bytes/rec超えるぐらい。
で、1ブロックに3行ぐらい入るようにすれば面白いかな。。

ということで、

表はなんどもdrop/createするので以下のDDLで。初回は索引を作らず、表のみので影響をみることにする。

$ cat droppurge_create_hoge.sql

drop table hoge purge;
create table hoge (id number, data varchar2(2000));

データ作成（バッファキャッシュの10％未満のデータ登録）
なお、確実に遅延ブロッククリーンアウトの影響を見たいので１行ごとのINSERTを繰り返し、コミット前後の状態の変化も見たいのでcommitも含めていない。（commitは別途実行する）

$ cat insert_rows.sql

begin for i in 1..100000 loop insert into hoge values(i, lpad('*', 2000, '*')); end loop; end;
.
l
/

ブロック数の事前確認

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

    blocks
----------
     33334

データ作成（バッファキャッシュの10％を超えるのデータ登録）
単純にループ回数を倍にして増量。これで事前に算出したバッファキャッシュの10％以上のブロック数は更新される。。。はず。

$ cat insert_rows_2.sql

begin for i in 1..200000 loop insert into hoge values(i, lpad('*', 2000, '*')); end loop; end;
.
l
/

ブロック数の事前確認（20,000ブロックぐらい？は遅延される想定）

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

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

ここまでが遅延ブロッククリーンアウトを意図的に起こすためのデータ作成SQLスクリプト

以降は、遅延ブロッククリーンアウトの発生等を見るための[g]v$sysstatと[g]v$sestatを取得するスクリプトと、[g]v$sesstatから特定のセッションを取得するためのクライアント情報をセットするスクリプト。

$ cat set_client_info.sql
BEGIN
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TargetSession');
END;
.
l
/

システム統計とセッション統計を取得して差分を見ていく必要があるので各統計のスナップショット取得用スクリプトが必要なわけですが、今回はマルチテナント環境。なので、システム統計はCDB全体から、セッション統計は該当するPDBかつ、前述のスクリプトでClient Infoが設定されているセッションに限定するスクリプトを作る必要があるんですよね。DBWRの動きも含めてみたいときって。少し多めに統計名を取得していますが、実際に重要なのはcleanout系の統計ですね。いくつかのテストケースを実施する上で合わせてみておきたい統計も事前に入れてあります:)

$ cat show_stat.sql
set linesize 400
set tab off
set pagesize 1000
col name for a60
col value for 999,999,999,999,999
SELECT
  'sysstat' AS "SOURCE"
  , name
  , value
  , con_id
FROM
 gv$sysstat
WHERE
 name IN (
   'physical writes direct'
   , 'physical writes from cache'
   , 'physical writes non checkpoint'
   , 'consistent gets'
   , 'no work - consistent read gets'
   , 'cleanouts and rollbacks - consistent read gets'
   , 'cleanouts only - consistent read gets'
   , 'deferred (CURRENT) block cleanout applications'
   , 'immediate (CR) block cleanout applications'
   , 'immediate (CURRENT) block cleanout applications'
   , 'commit cleanout failures: block lost'
   , 'commit cleanout failures: buffer being written'
   , 'commit cleanout failures: callback failure'
   , 'commit cleanout failures: cannot pin'
   , 'commit cleanout failures: hot backup in progress'
   , 'commit cleanout failures: write disabled'
   , 'commit cleanouts'
   , 'commit cleanouts successfully completed'
   , 'db block changes'
   , 'physical read requests'
   , 'physical reads'
   , 'physical reads direct'
   , 'physical write requests'
   , 'physical writes'
   , 'physical writes direct'
   , 'DBWR checkpoint buffers written'
   , 'DBWR thread checkpoint buffers written'
   , 'DBWR tablespace checkpoint buffers written'
   , 'DBWR parallel query checkpoint buffers written'
   , 'DBWR object drop buffers written'
   , 'DBWR transaction table writes'
   , 'DBWR undo block writes'
   , 'DBWR revisited being-written buffer'
   , 'DBWR lru scans'
   , 'DBWR checkpoints'
   , 'DBWR fusion writes'
   , 'transaction tables consistent reads - undo records applied'
   , 'transaction tables consistent read rollbacks'
  )
UNION ALL
SELECT
  'sesstat' AS "SOURCE"
  , name
  , value
  , vsesstat.con_id
FROM
 gv$sesstat vsesstat
 inner join gv$statname vstatnam
 on
   vsesstat.statistic# = vstatnam.statistic#
WHERE
 name IN (
   'physical writes direct'
   , 'physical writes from cache'
   , 'physical writes non checkpoint'
   , 'consistent gets'
   , 'no work - consistent read gets'
   , 'cleanouts and rollbacks - consistent read gets'
   , 'cleanouts only - consistent read gets'
   , 'deferred (CURRENT) block cleanout applications'
   , 'immediate (CR) block cleanout applications'
   , 'immediate (CURRENT) block cleanout applications'
   , 'commit cleanout failures: block lost'
   , 'commit cleanout failures: buffer being written'
   , 'commit cleanout failures: callback failure'
   , 'commit cleanout failures: cannot pin'
   , 'commit cleanout failures: hot backup in progress'
   , 'commit cleanout failures: write disabled'
   , 'commit cleanouts'
   , 'commit cleanouts successfully completed'
   , 'db block changes'
   , 'physical read requests'
   , 'physical reads'
   , 'physical reads direct'
   , 'physical write requests'
   , 'physical writes'
   , 'physical writes direct'
   , 'DBWR checkpoint buffers written'
   , 'DBWR thread checkpoint buffers written'
   , 'DBWR tablespace checkpoint buffers written'
   , 'DBWR parallel query checkpoint buffers written'
   , 'DBWR object drop buffers written'
   , 'DBWR transaction table writes'
   , 'DBWR undo block writes'
   , 'DBWR revisited being-written buffer'
   , 'DBWR lru scans'
   , 'DBWR checkpoints'
   , 'DBWR fusion writes'
   , 'transaction tables consistent reads - undo records applied'
   , 'transaction tables consistent read rollbacks'
  )
  and sid = (
    select
      sid
    from
      gv$session
    where
      username = upper('&1')
      and client_info = 'TargetSession'
  )
order by
  4, 1, 2
;

undefine 1

ちなみに、統計を使って状況を確認する方法って意外に利用されているんですよね。日本だとあまり活用されてないようにも感じることは多いのですが、日々の統計を追っかけてると、どのメトリックが高く跳ね上がるのか把握できるので知ってて損することはないと思います:)

そういえば、Tanel PoderのSnapperもその手のツールではありますね。
Session Snapper
http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

私の過去のセッションでもElappsed Timeを見せないでチューニング効果を見てもらうネタとしてシステム統計を使ってたりします。
db tech showcase Tokyo 2013 - A35 特濃JPOUG：潮溜まりでジャブジャブ、SQLチューニング
https://www.slideshare.net/discus_hamburg/db-tech-showcase-tokyo-2013-a35-sql

少々脱線しますが、
最近、VirtualBox、なつかしー。なんて言う方もいますが、古いバージョンのOracleを残しておけるので、リリース毎の動きの差などを見たい場合は便利なのですよーw（クラウドだと強制アップグレードされちゃうので旧バージョンとの動作比較をネタにしたいときなどには向いてないw）

次回へつづく

---

東京では救急車のサイレンがまだまだ通常より多く聞こえます。。。。

Stay home, Stay Safe and Stay Hydrated.

---

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

---

Difference of Initialization Parameters between 11gR2 (11.2.0.4.0) and 19c (19.3.0.0.0) - including hidden params

Previously on Mac De Oracle
19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた

---

ということで、今回はめでたくサポート終了となった11gR2と19cのパラメータ差分を確認してみた（何年振りだろうこれw）。21cがでたらまたやる予定。

11gR2 : 11.2.0.4.0
19c   : 19.3.0.0.0

で比較した結果は以下

Difference of Initialization Parameters between 11gR2 (11.2.0.4.0) and 19c (19.3.0.0.0)

---

どうやればこの差分が作れるかというと、DBリンクでもできるけどそんなことしてません。というかやりたくないw　ので、旧リリースでパラメータをcsv出力して(comma区切りではないですがw)、19c側で外部表として参照して差分を出力.

パラメータの取り出し。linesizeは適当に調整する必要はあるかも

ORACLE-ORCLCDB@SYS> !cat paramout.sql
set linesize 250
set trimspool on
set head off
set feedback off
set timi off
set termout off
set time off
set timi off
spo &1..csv

SELECT
   TRIM(a.ksppinm)
  ||'|'||TRIM(a.ksppdesc)||'|'
FROM
  x$ksppi a
/
spo off
undefine 1

上記スクリプトの実行、結果の例

ORACLE-ORCLCDB@SYS> @paramout 11.2.0.4.0_parameters
ORACLE-ORCLCDB@SYS> !cat 11.2.0.4.0_parameters.csv

_appqos_qt|System Queue time retrieval interval|
_ior_serialize_fault|inject fault in the ior serialize code|
_shutdown_completion_timeout_mins|minutes for shutdown operation to wait for sessions to complete|
_inject_startup_fault|inject fault in the startup code|
_latch_recovery_alignment|align latch recovery structures|
_spin_count|Amount to spin waiting for a latch|
_latch_miss_stat_sid|Sid of process for which to collect latch stats|
_max_sleep_holding_latch|max time to sleep while holding a latch|
_max_exponential_sleep|max sleep during exponential backoff|
_other_wait_threshold|threshold wait percentage for event wait class Other|
_other_wait_event_exclusion|exclude event names from _other_wait_threshold calculations|
・・・略・・・

外部表の元ネタであるファイル置き場用ディレクト作製とディレクトリオブジェクトの作製、そして、元ネタの配置は以下の通り

ORACLE-ORCLCDB@SYS> !pwd
/home/oracle
ORACLE-ORCLCDB@SYS> !ls -l exttab
合計 488
-rw-rw-r--. 1 oracle oracle 194319  3月 29 21:26 11.2.0.4_parameters.csv

ORACLE-ORCLCDB@SYS> CREATE DIRECTORY ext_tab AS '/home/oracle/exttab';

ディレクトリが作成されました。

ORACLE-ORCLCDB@SYS> l
  1  CREATE TABLE ksppi_11_2_0_4_0 (
  2  	ksppinm VARCHAR2(80)
  3  	,ksppdesc VARCHAR2(255)
  4  )
  5  ORGANIZATION EXTERNAL (
  6  	TYPE ORACLE_LOADER
  7  	DEFAULT DIRECTORY ext_tab
  8  	ACCESS PARAMETERS (
  9  		RECORDS DELIMITED BY NEWLINE
 10  		FIELDS TERMINATED BY '|'
 11  			(
 12  				ksppinm
 13  				,ksppdesc
 14  			)
 15  		)
 16  	LOCATION (
 17  		'11.2.0.4_parameters.csv'
 18  	)
 19* )
ORACLE-ORCLCDB@SYS> /

表が作成されました。

ORACLE-ORCLCDB@SYS> SELECT COUNT(1) FROM ksppi_11_2_0_4_0;

  COUNT(1)
----------
      2915

以下のスクリプトでcdb$rootのx$ksppiと前述の外部表をfull outer joinして差分をhtml形式で出力すればできあがり

col "11g R2 11.2.0.4.0" for a17
col "19c 19.3.0.0.0" for a17
set pagesize 10000
set timi off
set feed off
set markup html on spool on
spo param-diff.html
SELECT
	CASE
		WHEN prev.ksppinm = curr.ksppinm
		OR (
			prev.ksppinm IS NULL
			AND curr.ksppinm IS NOT NULL
		)
		THEN curr.ksppinm
		ELSE prev.ksppinm
	END AS ksppinm
	,CASE
		WHEN prev.ksppinm = curr.ksppinm THEN '○'
		WHEN prev.ksppinm IS NOT NULL AND curr.ksppinm IS NULL THEN '○'
		ELSE 'n/a'
	END AS "11g R2 11.2.0.4.0"
	,CASE
		WHEN prev.ksppinm = curr.ksppinm THEN '○'
		WHEN prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL THEN '○'
		ELSE 'n/a'
	END AS "19c 19.3.0.0.0"
	,CASE
		WHEN prev.ksppdesc = curr.ksppdesc THEN curr.ksppdesc
		WHEN prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL THEN curr.ksppdesc
		WHEN prev.ksppinm iS NOT NULL AND curr.ksppinm IS NULL
			THEN prev.ksppdesc
			ELSE prev.ksppdesc
	END AS kspdesc
	,CASE
		WHEN prev.ksppdesc = curr.ksppdesc
			OR (prev.ksppinm IS NULL AND curr.ksppinm IS NOT NULL)
			OR (prev.ksppinm IS NOT NULL AND curr.ksppinm IS NULL)
		THEN NULL
		ELSE curr.ksppdesc
	END AS "New description"
FROM
	(
		SELECT * FROM x$ksppi WHERE con_id = 0
	) curr
FULL OUTER JOIN ksppi_11_2_0_4_0 prev
ON
	curr.ksppinm = prev.ksppinm
ORDER BY
	1
;
spool off
set feed on
set set markup html off

---

露天風呂♨️行きたい...

---

・Difference of Initialization Parameters between 11g r1 (11.1.0.6.0) and 12c r1 (12.1.0.1.0) - including hidden params
・Difference of Initialization Parameters between 11g and 12c #2
・19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた

19cの初期化パラメータ数や隠しパラメータ数などどう変化したのか、久々に確認してみた

ORACLE-ORCLCDB@SYS> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

compute sum label 'Total' of "Num Of Parameters" on report
break on report
WITH cte_params AS
(
  SELECT
    a.ksppinm
  FROM
    x$ksppi a JOIN x$ksppcv b
    ON a.indx = b.indx

)
SELECT
   *
FROM
(
  SELECT
   '1. Single underscore parameters' AS "CATEGORY"
   , COUNT(1) AS "Num Of Parameters"
  FROM
   cte_params
  WHERE
   REGEXP_LIKE(ksppinm, '^([_][^_]){1}.*')
  UNION
  SELECT
   '2. Double underscore parameters'
   , COUNT(1)
  FROM
   cte_params
  WHERE
   REGEXP_LIKE(ksppinm, '^[_]{2}.*')
  UNION
  SELECT
   '3. Non hidden parameters'
   , COUNT(1)
  FROM
   cte_params
  WHERE
   REGEXP_LIKE(ksppinm, '^[^_].*')
)
ORDER by
 category;

カウントした結果配は以下のとおり。やはりhidden parameter、かなり増えてますよね。

CATEGORY                        Num Of Parameters
------------------------------- -----------------
1. Single underscore parameters              4934
2. Double underscore parameters                30
3. Non hidden parameters                      448
                                -----------------
Total                                        5412

経過: 00:00:00.06

ぽかぽか陽気すぎて、海辺でパタパタしたいw

---

・Difference of Initialization Parameters between 11g r1 (11.1.0.6.0) and 12c r1 (12.1.0.1.0) - including hidden params
・Difference of Initialization Parameters between 11g and 12c #2

実行計画は、SQL文のレントゲン写真だ！ No.30

OracleのResource Managerネタを書こう書こうと思いつつ類似ネタが他のブログでも扱われていることに気づきw
ネタを被らないようにしたいなぁとw　考えているうちにすでに2月の半ばw　もうすこし考えまするw

ということで、今回はこないだ、実行計画という名のレントゲンネタに絡んだtwitterのやりとりがあったついでなので、INSERTの実行計画をいくつか追加しておきます。

Oracleもバージョンが上がるごとに実行計画の改善やオペレーション名を変えたりするので古いバージョンだと、そんなオペレーション出なーーーい。ということもありますが、そんなオペレーションがでたら、こんな意味なんだぁ。

と理解しておけば裏でどう動いているかイメージしやすいのではないかと思います。それがイメージできていれば、もし、性能問題に絡んでいた時にはどう対処するのが良いか、助けになるとはずです (^^)

DIRECT PATH LOADING登場前のOracle Databaseでは気にする必要はなかったのですが、DIRECT PATHが行われるようになってから実行計画のOperationでDIRECT PATHとの区別がつきやすいように追加されたという微かな記憶があります。
（まちがっていたらコメントいただけますと m(_ _)m

ということで、実行計画の見てみましょう。INSERT文なので単純ですw　INSERTのoperationが（裏で）どう行われているかの違いです。
（以下の実行計画ではIASを利用して500MBほどの元表から同一定義の別表へ全データをINSERTしています）

LOAD TABLE CONVENTIONALというoperationが該当部分。　覚えておくと何かの役にはたつと思います！

この実行計画になるように以下のようにNOAPPENDヒントでdirect path writeを抑止しています。ヒントの使い方も覚えておくとなにかのときにや役立ちますよ:)
NO_GATHER_OPTIMIZER_STATISTICSヒントは今回の実行計画には直接関係ないリアルタイム統計の取得を抑止するヒントです。
19cのおそらく後半のリリースから従来型のDMLでもリアルタイム統計が取得されるようになったようです。この例ではじゃまなので抑止。

従来型のDMLでもリアルタイム統計が取得される https://docs.oracle.com/cd/F19136_01/tgsql/optimizer-statistics-concepts.html#GUID-769E609D-0312-43A7-9581-3F3EACF10BA9

Real-Time Statistics https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

INSERT
  /*+
    MONITOR
    NOAPPEND
    NO_GATHER_OPTIMIZER_STATISTICS
  */
INTO 
  hoge2
SELECT
  *
FROM
  hoge
;

では、つづいて、direct path writeが動作した場合は該当部分のoperationはどうなるでしょうか？　:)

LOAD AS SELECT に変わったoperationが該当部分です！　operation は　LOAD AS SELECT　ですが、待機イベントは direct path write です。
どのようなoperationがどのような待機イベントに繋がるのかっていうのもおぼえておくと何かの役に立ちますよ ;)

実行したINSERT文のヒントがNOAPPPENDからAPPENDに変えてあることに注目。この例ではdirect path writeをヒントで強制しています。

INSERT
  /*+
    MONITOR
    APPEND
    NO_GATHER_OPTIMIZER_STATISTICS
  */
INTO 
  hoge2
SELECT
  *
FROM
  hoge
;

最後に、NO_GATHER_OPTIMIZER_STATISTICS ヒントを外してリアルタイム統計取得が動いた場合にはどのようなoperationになるかみてみましょう。

OPTIMIZER STATISTICS GATHERING というoperationが現れました。これは19cの後半で追加された従来型DMLでのリアルタイム統計取得が動作した場合も現れるとマニュアルに記載されているoperationとも同一です。
direct path writeなのかによらず、リアルタイム統計取得の動作有無を確認するためにはこのoperationの有無をチェックする必要があります！

INSERT
  /*+
    MONITOR
    APPEND
  */
INTO 
  hoge2
SELECT
  *
FROM
  hoge
;

なかなかよい、レントゲンコレクションが撮れたな　:)

---

Related article on Mac De Oracle
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 20 / MINUS
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan

SQL Macros / ほぼ理解した、つもり

SQL Macrosの使い道、ほぼ理解した:)

現在提供されている　Live SQL 20.4.2(19c 19.8.0.0.0)で実行可能であることを確認<

Oracle DatabaseのSQLマクロを検証する
https://qiita.com/nakaie/items/75358c3138328dd985c4

SQL Macros - Creating parameterised views
https://livesql.oracle.com/apex/livesql/file/tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html<

他のRDBMSからの移行では効果ありそうな気はするが、DWH系かなぁ。MySQLやPostgreSQLから移行してくるのは多くはなさそうだし。
その逆となると色々考え混むわけだが、新機能なのでそこまで影響なさそうな気もする。

昔関わったプロジェクトで、PL/SQL禁止されていた記憶はある。そのようなプロジェクトだと即、禁止されそうな機能ではあるが、単純にSQL文が内部展開されるのだとすると書き換えは容易にかもしれない。内部的にどうなるのか現物が出てきてたら詳しく見ておきたいところ。

個人的に、こいつの使い道は、既存UDFの高速化などがメインになるのかなと、遠ーくをみて妄想しているところ:)
特にデータ量が膨大になればなるほど此の手のが効いてくるわけで。

実行計画は,SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 おまけ#3

この実行計画がなにを行っているか見抜けたかたは, 前日のエントリーを読んだか, RDFVIEWを利用したRDF Graphを試したことがある方ぐらいだと思います.
RDF Semantic Graph「RDF 超入門」

RDFトリプルをRelational表にマップしてダイレクトにアクセスする方式なのですが, ご存じない方には何のこっちゃというのも仕方ないと思います.
3,4年前ぐらいに, なんとなーく流れ的に関わることになって, 寝る暇も惜しんでRDFトリプルストア (実態はオブジェクト表だったはず. 違ったらコメント頂けますと幸いです) とこのRDFVIEWのSQLチューニング方法を調べていたもんですw

そのころは余裕なさすぎてブログにも書けずじまいでしたが, 良い機会なのでRDFVIEWを問い合わせた実行計画をレントゲン写真として載せておきます.
詳細は前日の準備段階のエントリーを読んでいただけると良いかと思いますが, 知らない用語とかいきなりでてきているので, なんとなーくそんなもんかなーぐらいの理解よいと思います.

RDFVIEWのを問い合わせるSQL文は, SEM_MATCH()を利用するのが特徴で, RDFVIEW最大の特徴であるRelational表を直接よみながら, 最終的に RDFトリプルを返すという点にあります.
R2RML: RDB to RDF Mapping Language / W3Cあたりを読んでもらうと想像できるのではないかと思います. 読んで最初にきづくのは,それに関わっているのは Oracle社の方だったりしてるので納得感があります.

ということで、
SQLは以下のとおり. 関わったことがない方はこれまた見たこともない　SEM_MATCH()関数が大量のパラメータを取って呼び出されていることが見えるぐらいですね。
裏では, SEM_MODEL()で指定したRDF Graphのモデルに対応したRelational表のEMPLOYEES表からEMPLOYEE_IDを元に, FIRST_NAME, LAST_NAME, MANAGER_IDの3列を, それぞれをトリプルとして返すようなマッピングになっています.

SELECT
	s
	, p
	, o
FROM
	TABLE (
		SEM_MATCH (
			'{?s ?p ?o}'
			, SEM_MODELS('TEST_MODEL1')
			, null
			, null
			, null
			, null
			, ' '
			, null
			, null
			, 'RDFUSER'
			, 'LOCALNET'
		)
	)
ORDER BY
	s
	,p;

RDFUSER@orcl> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

RDFUSER@orcl> break on index_name skip 1
RDFUSER@orcl> select index_name,column_name from user_ind_columns where table_name='EMPLOYEES' order by 1,2;

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP_DEPARTMENT_IX              DEPARTMENT_ID

EMP_EMAIL_UK                   EMAIL

EMP_EMP_ID_PK                  EMPLOYEE_ID

EMP_JOB_IX                     JOB_ID

EMP_MANAGER_IX                 MANAGER_ID

EMP_NAME_IX                    FIRST_NAME
                               LAST_NAME

では, 実行計画を読み解いてみましょう.

EMPLOYEES表からEMPLOYEE_IDを元に, FIRST_NAME, LAST_NAME, MANAGER_IDの3列と説明しましたが, それぞれの列に索引が存在しているため, Index Only Scanで索引のみをアクセスし,
Id=7,8,9でEMPLOYEE_IDをEMP_EMP_ID_PK索引から, MANAGER_IDをEMP_MANAGER_IX索引から EMPLOYEE_IDとMANAGER_IDの2列からなら行を結合で作り出しています.
Id=11,12,13で同じく, EMPLOYEE_IDとEMP_NAME_IX索引から, EMPLOYEE_IDとLAST_NAMEの2列からなる行を結合で作り出してます.
Id=15,16,17でも, EMPLOYEE_IDとEM_NAME_IX索引から, EMPLOYEE_IDとFAST_NAMEの2列からなる行を結合でつくりだしています.

これらの動きから, トリプルを作成するために, EMPLOYEE_ID列, MANAGER_ID, FAST_NAME, FIRST_NAME列ぞれぞれを取得するために複数回索引にアクセスしています.
もし索引がなかったら, それぞれの列を取得するために, 複数回全表走査を行うだろうということは容易に想像できます. これらの動きは、RDFVIEWの特性の一つになっています. Relational表に格納されたデータを即刻RDFトリプルとして参照したいという目的のために性能にはある程度目をつぶっている姿が見えてきます.
列持ちのデータをトリプルという, ある意味, 行持ちのデータへ縦横変換しているわけですから仕方ない動作ではあります.

データの新鮮さよりも性能を, というケースでは, ロードという作業は必要になりますがトリプルストアを利用したほうが有利にはなります. トリプルストアをアクセスする実行計画は気が向いたら載せるかもしれません.
覚えておいてほしいことは, Id=19やPredicate InformationにRDF_RRのようなオブジェクトとUNION ALLのVIEWが1~3個登場したらRDFVIEWだろうということです. 治療が必要になった場合などには役にたつかもしれません.

-----
それでは, みなさま, 良いお年をお迎えください.

---

previously on Mac De Oracle
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 20 / MINUS
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は,SQL文のレントゲン写真だ！　Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)

RDFナレッジ・グラフ / 備忘録 19c

5年ぐらい前に触ったことがある程度で、すっかり忘れてしまった。しばらくぶりで思い出してパタパタしてみたら、Apache Jenaも含めていろいろ変わっていたので備忘録

RDFナレッジ・グラフ開発者ガイド RDFナレッジ・グラフの概要

R2RML: RDB to RDF Mapping Language

A Direct Mapping of Relational Data to RDF

いろいろ思い出しながら....

$ sqlplus sys@orclcdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 28 00:51:52 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col namespace for a30
SQL> col value for a40
SQL> col description for a50
SQL> set linesize 400
SQL> set tab off
SYS@orclcdb> select * from MDSYS.RDF_PARAMETER;

NAMESPACE           ATTRIBUTE         VALUE         DESCRIPTION
------------------- ----------------- ------------- --------------------------------------------------
COMPONENT           RDFCTX            INSTALLED     Semantic (Text) Search component
COMPONENT           RDFOLS            INSTALLED     RDF Optional component for OLS support
MDSYS               SEM_VERSION       19.1.0.0.0    VALID

SYS@orclcdb> r
  1  SELECT namespace, attribute, value FROM mdsys.rdf_parameter
  2    WHERE namespace='MDSYS'
  3    AND attribute IN ('FLOAT_DOUBLE_DECIMAL',
  4                      'XSD_TIME', 'XSD_BOOLEAN',
  5*                     'DATA_CONVERSION_CHECK')

no rows selected

SYS@orclcdb> conn system@orcl
Enter password: 
Connected.
SYSTEM@orcl> create tablespace rdf_users datafile 'rds_users01.dbf' size 128m reuse autoextend on next 64m maxsize unlimited segment space management auto;

Tablespace created.

SYSTEM@orcl> create user rdfuser identified by hogehoge default tablespace rdf_users temporary tablespace temp;

User created.

SYSTEM@orcl> grant create view to rdfuser;

Grant succeeded.

SYSTEM@orcl> grant unlimited tablespace to rdfuser;

Grant succeeded.

SYSTEM@orcl> grant unlimited tablespace to mdsys;

Grant succeeded.

SYSTEM@orcl> grant select any dictionary to rdfuser;

Grant succeeded.

SYSTEM@orcl> grant connect, resource to rdfuser;

Grant succeeded.

SYSTEM@orcl> execute sem_apis.create_sem_network('RDF_USERS', network_owner=>'RDFUSER', network_name=>'LOCALNET');

PL/SQL procedure successfully completed.


SYSTEM@orcl> conn rdfuser@orcl
Enter password: 
Connected.
RDFUSER@orcl> 

RDFUSER@orcl> l
  1  create table r2rmlview_nt_staging_tab (
  2  	rdf$stc_sub varchar2(4000) not null
  3  	,rdf$stc_pred varchar2(4000) not null
  4  	,rdf$stc_obj varchar2(4000) not null
  5  )
  6* nologging
RDFUSER@orcl> /

Table created.

RDFUSER@orcl> l
  1  create table rdfview_export_tab (
  2          rdf$stc_sub varchar2(4000) not null
  3          ,rdf$stc_pred varchar2(4000) not null
  4          ,rdf$stc_obj varchar2(4000) not null
  5  )
  6* nologging
RDFUSER@orcl> /

Table created.

RDFでいろいろやるには sga_targetやsga_max_sizeはそれなりに必要なので適当に調整

そして、Apache Jenaのrdfcatもdeprecatedになってて、これまたしばし時代に追いつく作業を....

RDFUSER@orcl> !rdfcat --help
------------------------------------------------------------------
   DEPRECATED: Please use 'riot' instead.
     http://jena.apache.org/documentation/io/#command-line-tools
------------------------------------------------------------------

------------------------------------
DEPRECATED: Please use riot instead.
------------------------------------

Usage: java jena.rdfcat (option|input)*
Concatenates the contents of zero or more input RDF documents.
Options: -out N3 | N-TRIPLE | RDF/XML | RDF/XML-ABBREV
         -n  expect subsequent inputs in N3 syntax
         -x  expect subsequent inputs in RDF/XML syntax
         -t  expect subsequent inputs in N-TRIPLE syntax
         -[no]include  include rdfs:seeAlso and owl:imports
input can be filename, URL, or - for stdin
Recognised aliases for -n are: -n3 -ttl or -N3
Recognised aliases for -x are: -xml -rdf or -rdfxml
Recognised aliases for -t are: -ntriple
Output format aliases: x, xml or rdf for RDF/XML, n, n3 or ttl for N3, t or ntriple for N-TRIPLE
See the Javadoc for jena.rdfcat for additional details.

rdfcatに変えて、riot ってやつをつかわなきゃいけなくなったっぽい。

RDFUSER@orcl> !riot -version
Jena:       VERSION: 3.13.1
Jena:       BUILD_DATE: 2019-10-06T18:57:39+0000
RIOT:       VERSION: 3.13.1
RIOT:       BUILD_DATE: 2019-10-06T18:57:39+0000

ちなみに、SHユーザのEMPLOYEES表や索引をRDFUSERユーザへコピーした上で、以下のような、RDFVIEWとRelational表と列のマッピングをTurtle (Terse RDF Triple Language)で定義した。

RDFUSER@orcl> !cat test_real_rdf_r2rml.ttl
@prefix rr: .
@prefix xsd: .
@prefix ex: .

ex:TriplesMap_Employees
        rr:logicalTable [ rr:tableName "EMPLOYEES" ];
        rr:subjectMap [ 
                rr:template "http://r2rml.com/employees/{EMPLOYEE_ID}";
                rr:class ex:Employees;
        ];

        rr:predicateObjectMap [
                rr:predicate ex:FirstName;
                rr:objectMap [ rr:column "FIRST_NAME" ];
        ];

        rr:predicateObjectMap [
                rr:predicate ex:LastName;
                rr:objectMap [ rr:column "LAST_NAME" ];
        ];

        rr:predicateObjectMap [
                rr:predicate ex:ManagerId;
                rr:objectMap [ rr:column "MANAGER_ID" ];
        ].

5年ぐらい前の記憶ではrdfcatを利用して変換していたが、今は、 riot というコマンドを利用してTurtle(Terse RDF Triple Language)をN-Triplesへ変換するみたいね。

RDFUSER@orcl> !riot --out=N-TRIPLE test_real_rdf_r2rml.ttl > test_real_rdf_r2rml_use_riot.nt

RDFUSER@orcl> !cat test_real_rdf_r2rml_use_riot.nt
_:Bf70c7f0d1b418dc63ad89dbcea313cd1  "EMPLOYEES" .
  _:Bf70c7f0d1b418dc63ad89dbcea313cd1 .
_:Bdd4cf5eb9ecb0b12212d342518513827  "http://r2rml.com/employees/{EMPLOYEE_ID}" .
_:Bdd4cf5eb9ecb0b12212d342518513827   .
  _:Bdd4cf5eb9ecb0b12212d342518513827 .
_:B310abfde7d61aac8f303fbd1f4ba5db8   .
_:Bfe48b8372e4da59d0ce99e0e24b894ad  "FIRST_NAME" .
_:B310abfde7d61aac8f303fbd1f4ba5db8  _:Bfe48b8372e4da59d0ce99e0e24b894ad .
  _:B310abfde7d61aac8f303fbd1f4ba5db8 .
_:B11c76919901c01b44cb0e2507a997c28   .
_:B5474fc7a1b7c7302521175dac3c58028  "LAST_NAME" .
_:B11c76919901c01b44cb0e2507a997c28  _:B5474fc7a1b7c7302521175dac3c58028 .
  _:B11c76919901c01b44cb0e2507a997c28 .
_:Be7008cecff6b130ea15b4ba060cee0fb   .
_:B0f41936e13c4655082ee47f866dc9f61  "MANAGER_ID" .
_:Be7008cecff6b130ea15b4ba060cee0fb  _:B0f41936e13c4655082ee47f866dc9f61 .
  _:Be7008cecff6b130ea15b4ba060cee0fb .

SQL*Loaderを使用したステージング表へのN-Triple形式のデータのロードを参考

RDFUSER@orcl> !cat test_real_rdf_r2rml_nt_load.ctl
UNRECOVERABLE
LOAD DATA 
TRUNCATE
into table r2rmlview_nt_staging_tab
when (1) <> '#'
(
 RDF$STC_sub   CHAR(4000) terminated by whitespace
 "(
CASE 
 WHEN substr(:RDF$STC_sub,1,1)='<' AND substr(:RDF$STC_sub,-1,1)='>' AND
      length(:RDF$STC_sub)>2
      THEN :RDF$STC_sub
 WHEN substr(:RDF$STC_sub,1,2)='_:' AND
      REGEXP_LIKE(:RDF$STC_sub,'^(_:)[[:alpha:]][[:alnum:]]*$')
      THEN :RDF$STC_sub
 WHEN substr(:RDF$STC_sub,1,1) NOT IN ('\"','<','#') AND
      substr(:RDF$STC_sub,-1,1) NOT IN ('\"','>')
      THEN ('<' || SDO_RDF.replace_rdf_prefix(:RDF$STC_sub) || '>')
 WHEN substr(:RDF$STC_sub,1,1)='#'
      THEN SDO_RDF.raise_parse_error(
            'Ignored Comment Line starting with ', :RDF$STC_sub)
 ELSE SDO_RDF.raise_parse_error('Invalid Subject', :RDF$STC_sub)
END
 )",
 RDF$STC_pred  CHAR(4000) terminated by whitespace
 "(
CASE 
 WHEN substr(:RDF$STC_pred,1,1)='<' AND substr(:RDF$STC_pred,-1,1)='>' AND
      length(:RDF$STC_pred)>2
      THEN :RDF$STC_pred
 WHEN substr(:RDF$STC_pred,1,2) != '_:' AND
      substr(:RDF$STC_pred,1,1) NOT IN ('\"','<') AND
      substr(:RDF$STC_pred,-1,1) NOT IN ('\"','>')
      THEN ('<' || SDO_RDF.replace_rdf_prefix(:RDF$STC_pred) || '>')
 ELSE SDO_RDF.raise_parse_error('Invalid Predicate', :RDF$STC_pred)
END
 )",
--
-- right-trimming of WHITESPACEs is reqd for "RDF$STC_obj" 
-- (due to absence of "TERMINATED BY WHITESPACE")
--
-- For ease of editing below replace 
--   "rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13))" with ":xy".
-- and then replace back
--
 RDF$STC_obj   CHAR(4000)
 "(
CASE
 WHEN substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),1,1)='<' AND 
      substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),-1,1)='>' AND
      length(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)))>2
      THEN rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13))
 WHEN substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),1,1)='\"' AND
      substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),-1,1)='\"' AND
      length(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)))>1
      THEN rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13))
 WHEN substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),1,2)='_:' AND
      REGEXP_LIKE(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),
                  '^(_:)[[:alpha:]][[:alnum:]]*$')
      THEN rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13))
 WHEN substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),1,1) 
        NOT IN ('\"','<') AND
      substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),-1,1) 
        NOT IN ('\"','>')
      THEN ('<' || 
            SDO_RDF.replace_rdf_prefix(
             rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13))) || 
            '>')
 WHEN substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),1,1)='\"' AND
      substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),-1,1) 
        NOT IN ('\"','>') AND
      instr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),'\"\@',-1)>1 AND
      REGEXP_LIKE(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),
                  '^\"[[:print:]]*\"\@[[:alpha:]]+(-[[:alnum:]]+)*$')
      THEN rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13))
 WHEN (substr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),1,1)='\"' AND 
       instr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),'\"^^',-1)>1 AND
       (length(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)))-
        (instr(rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)),'\"^^',-1)+4)
       )>0)
       THEN SDO_RDF.pov_typed_literal(
             rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)))
 ELSE SDO_RDF.raise_parse_error(
       'Invalid Object', rtrim(:RDF$STC_obj,'. '||CHR(9)||CHR(10)||CHR(13)))
END
 )"
)

RDFUSER@orcl> exit

$ sqlldr userid=rdfuser@orcl control=test_real_rdf_r2rml_nt_load.ctl data=test_real_rdf_r2rml_use_riot.nt direct=true skip=0 load=1000000 discardmax=0 bad=test_real_rdf_r2rml_nt_load.bad discard=test_real_rdf_r2rml_nt_load.rej log=test_real_rdf_r2rml_nt_load.log errors=1000000
Password:

SQL*Loader: Release 19.0.0.0.0 - Production on Sat Dec 28 02:05:44 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct, LOAD=1000000

Load completed - logical record count 17.

Table R2RMLVIEW_NT_STAGING_TAB:
  17 Rows successfully loaded.

Check the log file:
  test_real_rdf_r2rml_nt_load.log
for more information about the load.

そして、SEM_MATCH()関数を利用してモデルと問い合わせると....

RDFUSER@orcl> @test_real_rdf_cre_rdfview.sql

PL/SQL procedure successfully completed.

RDFUSER@orcl> l
  1  SELECT
  2  	s
  3  	, p
  4  	, o
  5  FROM
  6  	TABLE (
  7  		SEM_MATCH (
  8  			'{?s ?p ?o}'
  9  			, SEM_MODELS('TEST_MODEL1')
 10  			, null
 11  			, null
 12  			, null
 13  			, null
 14  			, ' '
 15  			, null
 16  			, null
 17  			, 'RDFUSER'
 18  			, 'LOCALNET'
 19  		)
 20  	)
 21  ORDER BY
 22  	s
 23  	,p
 24*
RDFUSER@orcl> 
RDFUSER@orcl> @test_query_rdfview.sql

S                                        P                                                  O
---------------------------------------- -------------------------------------------------- ----------------------------------------
http://r2rml.com/employees/100           http://r2rml.com/ns#FirstName                      Steven
http://r2rml.com/employees/100           http://r2rml.com/ns#LastName                       King
http://r2rml.com/employees/100           http://www.w3.org/1999/02/22-rdf-syntax-ns#type    http://r2rml.com/ns#Employees
http://r2rml.com/employees/101           http://r2rml.com/ns#FirstName                      Neena
http://r2rml.com/employees/101           http://r2rml.com/ns#LastName                       Kochhar
http://r2rml.com/employees/101           http://r2rml.com/ns#ManagerId                      100
http://r2rml.com/employees/101           http://www.w3.org/1999/02/22-rdf-syntax-ns#type    http://r2rml.com/ns#Employees
http://r2rml.com/employees/102           http://r2rml.com/ns#FirstName                      Lex
http://r2rml.com/employees/102           http://r2rml.com/ns#LastName                       De Haan
http://r2rml.com/employees/102           http://r2rml.com/ns#ManagerId                      100

・・・中略・・・

http://r2rml.com/employees/205           http://r2rml.com/ns#FirstName                      Shelley
http://r2rml.com/employees/205           http://r2rml.com/ns#LastName                       Higgins
http://r2rml.com/employees/205           http://r2rml.com/ns#ManagerId                      101
http://r2rml.com/employees/205           http://www.w3.org/1999/02/22-rdf-syntax-ns#type    http://r2rml.com/ns#Employees
http://r2rml.com/employees/206           http://r2rml.com/ns#FirstName                      William
http://r2rml.com/employees/206           http://r2rml.com/ns#LastName                       Gietz
http://r2rml.com/employees/206           http://r2rml.com/ns#ManagerId                      205
http://r2rml.com/employees/206           http://www.w3.org/1999/02/22-rdf-syntax-ns#type    http://r2rml.com/ns#Employees

なぜ、そこに、LONG型があるんだ / FAQ

all/dba/user_tab_columns

https://docs.oracle.com/cd/E82638_01/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63

これらのビューは、列の属性関連の情報を持つビューです。
たまに、便利なびゅーではあるのですが、これらのビューをアクセスする使うスクリプトというかPL/SQLでコード書くこともあるのですが、一箇所だけ、使いにくいところがあります。

どこかわかります？

下位互換のためだろうと思われるのですが、一般には推奨されていない　LONG型の列　が残っています。

ご存知だとは思いますが、一般的なガイドだと、CLOBの利用が推奨されています。
下位互換のためだから仕方ないのだとは思うのですが。

LONG型といえば、とにかく制約が多くて、文字列操作を行うにもめんどくさいわけで、実際に利用したい状況になると、うううううっとなることしばしば。

LONG型

で、普段どうやって、その面倒くさいところを回避しているかといえば、CLOBに変換してしまうことがが多いです。
CLOBにしてしまえば、沢山の制約から解放されますしね　:)

以下のような感じで。

SCOTT> l
  1  CREATE TABLE my_dba_tab_columns
  2  AS
  3  SELECT
  4    owner
  5    ,table_name
  6    ,column_name
  7    ,data_type
  8    ,data_type_mod
  9    ,data_type_owner
 10    ,data_length
 11    ,data_precision
 12    ,data_scale
 13    ,nullable
 14    ,column_id
 15    ,TO_CLOB(default_length) AS default_length
 16    ,num_distinct
 17    ,low_value
 18    ,high_value
 19    ,density
 20    ,num_nulls
 21    ,num_buckets
 22    ,last_analyzed
 23    ,sample_size
 24    ,character_set_name
 25    ,char_col_decl_length
 26    ,global_stats
 27    ,user_stats
 28    ,avg_col_len
 29    ,char_length
 30    ,char_used
 31    ,v80_fmt_image
 32    ,data_upgraded
 33    ,histogram
 34    ,default_on_null
 35    ,identity_column
 36    ,sensitive_column
 37    ,evaluation_edition
 38    ,unusable_before
 39    ,unusable_beginning
 40    ,collation
 41  FROM
 42*   dba_tab_columns
SCOTT> /

Table created.

SCOTT> desc dba_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 COLUMN_NAME                               NOT NULL VARCHAR2(128)
 DATA_TYPE                                          VARCHAR2(128)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(128)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(2000)
 HIGH_VALUE                                         RAW(2000)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)
 DEFAULT_ON_NULL                                    VARCHAR2(3)
 IDENTITY_COLUMN                                    VARCHAR2(3)
 SENSITIVE_COLUMN                                   VARCHAR2(3)
 EVALUATION_EDITION                                 VARCHAR2(128)
 UNUSABLE_BEFORE                                    VARCHAR2(128)
 UNUSABLE_BEGINNING                                 VARCHAR2(128)
 COLLATION                                          VARCHAR2(100)

SCOTT> desc my_dba_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 COLUMN_NAME                               NOT NULL VARCHAR2(128)
 DATA_TYPE                                          VARCHAR2(128)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(128)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     CLOB
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(2000)
 HIGH_VALUE                                         RAW(2000)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)
 DEFAULT_ON_NULL                                    VARCHAR2(3)
 IDENTITY_COLUMN                                    VARCHAR2(3)
 SENSITIVE_COLUMN                                   VARCHAR2(3)
 EVALUATION_EDITION                                 VARCHAR2(128)
 UNUSABLE_BEFORE                                    VARCHAR2(128)
 UNUSABLE_BEGINNING                                 VARCHAR2(128)
 COLLATION                                          VARCHAR2(100)

---

db tech showcase 2019もおわり、今年も残すところ 3ヶ月あまり。一年早い. そして。
来週は、開催時期を秋に変更してから2回目の多摩川花火大会。天気がよいといいのですが:)

ではまた。

FAQ / PL/SQL PACKAGEでパプリックスコープを持つ定数をSQL文中で利用するには...

かなーり、ご無沙汰しておりました。（本業でいっぱいいっぱいで、という言い訳はこれぐらいにしてw） 偶に聞かれることがあるので、FAQネタから。 パッケージでパブリックなスコープを持つ定数は無名PL/SQLブロックやパッケージ、プロシージャ、ファンクションでしか参照できないんですよねー 例えば、DBMS_CRYPTOパッケージでHASHファンクションを利用してSH-256を作成したいなーと思って、

39.4 DBMS_CRYPTOのアルゴリズム
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CRYPTO.html#GUID-CE3CF17D-E781-47CB-AEE7-19A9B2BCD3EC

DBMS_CRYPTO.HASH()は関数なのでSQL文から呼びたーい、と以下のような使い方をすると...

SQL> SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual;
SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual
                                         *
ERROR at line 1:
ORA-00904: "DBMS_CRYPTO"."HASH_SH2569": invalid identifier


SQL>
SQL> select DBMS_CRYPTO.HASH_SH256 from dual;
select DBMS_CRYPTO.HASH_SH256 from dual
       *
ERROR at line 1:
ORA-06553: PLS-221: 'HASH_SH256' is not a procedure or is undefined

見事にエラーとなるわけです。 DBMS_CRYPTO.HASH_SH256は、パッケージファンクションではないので...利用可能なのはPL/SQLでのみ。

SQL> set serveroutput on
SQL>
¥SQL>
SQL> begin
  2    dbms_output.put_line('DBMS_CRYPTO.HASH_SH256 : ' || DBMS_CRYPTO.HASH_SH256);
  3  end;
  4  /
DBMS_CRYPTO.HASH_SH256 : 4

PL/SQL procedure successfully completed.

SQL文で活用する為には、ファンクションでラップする必要があります。 以下のように。

SQL> l
  1  CREATE OR REPLACE FUNCTION get_hash_sh256_type
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5    RETURN DBMS_CRYPTO.HASH_SH256;
  6* END;
SQL> /

Function created.

SQL>

冒頭でエラーとなっていたSQL文をDBMS_CRYPTO.HASH_SH256を返すファンクションを使うように書き換えると、 はい、できました。

SQL> l
  1  SELECT
  2    DBMS_CRYPTO.HASH(
  3	     TO_CLOB('hoge')
  4	     , get_hash_sh256_type()
  5    ) AS "SH-256"
  6  FROM
  7*   dual
SQL> /

SH-256
--------------------------------------------------------------------------------
ECB666D778725EC97307044D642BF4D160AABB76F56C0069C71EA25B1E926825

SQL>

---

露店の焼きそばと焼き鳥を食べつつ、晩夏の夏祭りと、涼しい朝晩の気温で熟睡可能な山形より。 では、では。