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

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

---