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

2021年9月22日 (水)

古くて新しい? 遅延ブロッククリーンアウト (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の方から挙動を診ていくことにします。手順はざっとこんな感じ
Iasstep

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

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


20210213-150416





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



|

コメント

コメントを書く