« 古くて新しい? 遅延ブロッククリーンアウト (deferred block cleanout) #11 | トップページ | 誰がどんな名前のペットを飼っているのかな? その1 / JPOUG Advent Calendar Day 1 »

2021年9月25日 (土)

古くて新しい? 遅延ブロッククリーンアウト (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なので手順も単純になっています)
Ctas_steps




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



|

コメント

コメントを書く