古くて新しい？ 遅延ブロッククリーンアウト (deferred block cleanout) #8 Tweet
Previously on Mac De Oracle
前回からのつづき（ちょいと寄り道中）
です。
では、とっとと試してみましょうw
バッファキャッシュから溢れ出る程度のデータ量だったら、どうなるのかなーーーー、という検証です。やりたいことは図の通りです。
検証方法を考えていたのですが、自動共有メモリー管理になっているのと、sga_max_size/sga_targetを小さくしすぎるとOracle Databaseが起動しないなど諸々引きそうなので、shared_pool_sizeを大きく設定して、バッファキャッシュに回せるメモリーを減らすことで、バッファキャッシュを小さく、バッファから溢れる程度のデータ量も少なくて済むようにして試してみることにします。
準備段階から書いてます。再現させる環境をどうセットアップしたかっていうことも重要だと思うのですよね。少々長くなっちゃいますが。
検証準備
CDBに接続して初期化パラメータを調整！！
SGAコンポーネントの状況
Database Buffersが、3G以上になってます。検証データ量も多くなってしまうので、これを1GB程度まで下げたいですね。検証時間も節約できますし、最小の手数で検証できるほうが良いですから:)
SYS@orclcdb> show sgaTotal System Global Area 4294963960 bytesFixed Size 9143032 bytesVariable Size 805306368 bytesDatabase Buffers 3472883712 bytesRedo Buffers 7630848 bytesSYS@orclcdb> select 3472883712 / 1024 / 1024 AS "MB" from dual; MB---------- 3312Elapsed: 00:00:00.00
sga_max_size,sga_min_sizeが4GBですが、ここはそのままにします。あまり小さくしすぎると起動しなくなったり。(^^;;;;
SYS@orclcdb> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga boolean FALSElock_sga boolean FALSEpre_page_sga boolean TRUEsga_max_size big integer 4Gsga_min_size big integer 0sga_target big integer 4Gunified_audit_sga_queue_size integer 1048576
自動SGA管理なので、Shared Pool Sizeに大きめの値を設定。
自動SGA管理下で自動管理対象メモリーコンポーネントパラメータに値を設定した場合、その値が下限値となり最低でもその値は確保されるという仕組みを利用します！
SYS@orclcdb> show parameter shared_poolNAME TYPE VALUE------------------------------------ ----------- ------------------------------shared_pool_reserved_size big integer 39426457shared_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.00SYS@orclcdb> alter system set shared_pool_size = 3g scope=both;System altered.Elapsed: 00:00:00.77
Database Buffersがいい感じにシュリンクしました。1GBぐらいになりました。これで進めますよー。
SYS@orclcdb> show sgaTotal System Global Area 4294963960 bytesFixed Size 9143032 bytesVariable Size 3238002688 bytesDatabase Buffers 1040187392 bytesRedo Buffers 7630848 bytesSYS@orclcdb> select 1040187392 / 1024 / 1024 AS "MB" from dual; MB---------- 992Elapsed: 00:00:00.01
ざっくりとブロック数を計算
SYS@orclcdb> select 1040187392 / 8192 AS "blocks" from dual; blocks--------------- 126976Elapsed: 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---------- 400000Elapsed: 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-------------------------- 17777Elapsed: 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---------- 166668Elapsed: 00:00:00.00
なので、遅延ブロッククリーンアウトされると想定される（コミットクリーンアウト分を覗くと）ブロック数は、148,891 blocks 程度にはなりそう。
SYS@orclcdb> select ceil((66667 / 2 * 5) - 17777) AS "blocks" from dual; blocks---------- 148891Elapsed: 00:00:00.01
それに加えて、バッファキャッシュに収まらず、コミットする前にバッファキャッシュから落とされ、ストレージへかきだされてしまうブロック数は、これまた、ざっくり計算すると 39,692 blocks ほどですかね。バッファキャッシュのサイズから全てのブロックは乗り切らないので、最初に読み込まれていたブロックから落とされていくことにはなりますね。。
とはいえ、この検証ではキャッシュ落とされるブロック数は特に気にしてなくて、バッファキャッシュ以上のブロック数が生成されていればいいので、落とされそうなのが確認できればOK.
SYS@orclcdb> select ceil((66667 / 2 * 5) - 126976) AS "blocks" from dual; blocks---------- 39692Elapsed: 00:00:00.00
とりあえず、生成するデータ量（行数）の算出とバッファキャッシュサイズの調整はおわり。
次に、実際にデータを生成してブロック数とセグメントサイズを確認して、実行用スクリプトの調整を行なっておきます。
PDBでテストデータの実サイズの確認
SCOTT@orcl> @droppurge_create_hoge.sql 1* drop table hoge purgeTable 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 selectedElapsed: 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---------- 166667Elapsed: 00:00:05.03SCOTT@orcl> select segment_name,blocks,bytes/1024/1024 AS "MB" from user_segments where segment_name = 'HOGE';SEGMENT_NAME BLOCKS MB------------------------------ ---------- ----------HOGE 172032 1344Elapsed: 00:00:00.17
Scattered read でTable Full Scanできるように少々隠しパラメータを調整しておきますね。念の為。（セッションレベルで調整してます）
セグメントサイズが、1344 MBなので、_very_large_object_threshold は、2048 MBぐらい設定しておけば、Scattered readのまま行けそうですね。
$ cat table_full_scan.sqlalter session set "_serial_direct_read" = never.l/alter session set "_very_large_object_threshold" = 2040.l/!echo set autot trace exp statset autot trace exp statselect * from hoge.l/!echo set autot offset 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 3028sysstat commit cleanouts 3028sysstat consistent gets 274sysstat db block changes 1sysstat no work - consistent read gets 149sysstat physical reads 60
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat commit cleanout failures: block lost 3028sesstat commit cleanouts 3028sesstat 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" = neverSession altered.Elapsed: 00:00:00.00 1* alter session set "_very_large_object_threshold" = 2040Session altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge500000 rows selected.Elapsed: 00:01:03.08Execution 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 processedset 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 241sysstat DBWR thread checkpoint buffers written 241sysstat DBWR undo block writes 1109sysstat cleanouts only - consistent read gets 166667sysstat commit cleanouts 55sysstat commit cleanouts successfully completed 55sysstat consistent gets 377331sysstat db block changes 166904sysstat deferred (CURRENT) block cleanout applications 29sysstat immediate (CR) block cleanout applications 166667sysstat immediate (CURRENT) block cleanout applications 15sysstat no work - consistent read gets 38449sysstat physical reads 160805sysstat physical writes 137551sysstat physical writes from cache 137551sysstat physical writes non checkpoint 137466
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat cleanouts only - consistent read gets 166667sesstat commit cleanouts 6sesstat commit cleanouts successfully completed 6sesstat consistent gets 374538sesstat db block changes 166710sesstat deferred (CURRENT) block cleanout applications 3sesstat immediate (CR) block cleanout applications 166667sesstat immediate (CURRENT) block cleanout applications 1sesstat no work - consistent read gets 36779sesstat physical reads 160513
念の為、今一度、物理読み込みを伴う全表走査を行なって、クリーンアウトされたのか確認してみましょうw（疑い深いw）
もう一度、PDBのSCOTTユーザーで、遅延ブロッククリーンアウト影響有無確認（対象表を全表走査）
redo sizeが 0 なのでクリーンアウトは発生してない。想定通り
SCOTT@orcl> @table_full_scan.sql 1* alter session set "_serial_direct_read" = neverSession altered.Elapsed: 00:00:00.00 1* alter session set "_very_large_object_threshold" = 2040Session altered.Elapsed: 00:00:00.00set autot trace exp stat 1* select * from hoge500000 rows selected.Elapsed: 00:00:33.17Execution 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 processedset autot off
同様に、統計でも確認してみます！
CDBのSYSで統計取得（遅延ブロッククリーンアウト有無確認）
クリーンアウトを示す統計値は上昇していません！！！（うんうんw）
差分のあった統計のみ記載
CDB）システム統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sysstat DBWR checkpoint buffers written 732sysstat commit cleanouts 1sysstat commit cleanouts successfully completed 1sysstat consistent gets 200522sysstat db block changes 13sysstat deferred (CURRENT) block cleanout applications 1sysstat no work - consistent read gets 200403sysstat physical reads 148200sysstat physical writes 68570sysstat physical writes from cache 68570sysstat physical writes non checkpoint 68331
(PDB) SCOTTのセッション統計
SOURCE NAME VALUE------- ------------------------------------------------- --------------------sesstat consistent gets 200414sesstat db block changes 9sesstat no work - consistent read gets 200366sesstat 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
| 固定リンク | 0
コメント