データパッチにおけるUPDATE vs INSERT方式について調べてみた～その2 ～

前回は過去に行ったトラブル対応で気になっていたことで調べてみたいことの背景を説明だったので、今回はその続きとなる検証方法について纏めた。

検証方法

Oracle Database 19c (19.3.0) on Linux x86_64環境に、総件数100万件のデータを格納したテーブルを用意する。そのうち約50万件のレコードについて処理済みフラグが未処理(flg = 0)なテストデータを用意し、UPDATE方式、Exchange Partition + INSERT方式でデータパッチを実施し、アクセスブロック数などを比較してみる。

比較にあたって、以下の情報を各データパッチ方式実行時に取得する。

SQL実行時のSQLトレース
→ ブロックアクセス数および実行時間確認用
V$TRANSACTIONビューのubablk列
→ UNDOデータ生成量確認用

ちなみに、AWRレポートを比較することも考えたが、100万件という簡易なスケールの検証であることから、インスタンス全体の情報を収集してしまうAWRではノイズ(他の内部処理)を多分に含んでしまうことが考えられたので、今回は使用しないこととした。

参考までにSQLトレースを取得するコマンドは以下の通り。データパッチを実施するSQL*Plus上で実行する。

-- SQLトレースの取得開始
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

～データパッチSQLの実行～

-- SQLトレースの取得終了
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQLトレースを可読化したレポートの生成(tkprof)コマンドは、OSのコマンドラインから以下を実行する。

% tkprof トレースファイル名 レポートファイル名
※トレースファイルは通常、初期化パラメータdiagnostic_destで指定したディレクトリ配下の
　diag/rdbms/<DB名>/<SID名>/trace/<SID名>_ora_<プロセスID>.trc

V$TRANSACTIONビューの問い合わせSQLは、以下の通り。

SQL> SELECT ubablk * 8 / 1024 MB FROM v$transaction t, v$session s
       WHERE s.sid = <データパッチを実行するセッションID> AND s.saddr = t.ses_addr;

ubablk列は当該トランザクションで生成されたUNDOブロック数を示す。今回db_block_sizeが8KBだったので、8 / 1024でMB換算している。

データパッチを実行するセッションIDは、データパッチを実行するSQL*Plus上で、

SQL> SELECT SYS_CONTEXT('USERENV', 'SID')  FROM DUAL;

をデータパッチ前に実行して取得する。その後にデータパッチを実行するSQLを実行して、commitする前にV$TRANSACTIONビューを問い合わせ、ubablk列を確認する。

また説明が長くなったので、検証結果は次回。

参考：検証で使用したテーブルのDDL

-- データパッチ対象テーブル
create table tab01(
 id number(10) not null,
 ym char(6) not null,
 flg char(1),
 val number(10),
 memo char(50)
) tablespace users
partition by range (ym) (
 partition pt_202301 values less than ('202302'),
 partition pt_202302 values less than ('202303'),
 partition pt_202303 values less than ('202304'),
 partition pt_202304 values less than ('202305'),
 partition pt_202305 values less than ('202306')
);
create unique index idx01tab01 on tab01(id, ym) local tablespace users;
alter table tab01 add constraint pk_tab01 primary key (id, ym) using index idx01tab01;

-- Exchange Partition用空テーブル
create table tab02(
 id number(10) not null,
 ym char(6) not null,
 flg char(1),
 val number(10),
 memo char(50)
) tablespace users;
create unique index idx01tab02 on tab02(id, ym) tablespace users;
alter table tab02 add constraint pk_tab02 primary key (id, ym) using index idx01tab02;

参考：検証実行手順

検証は2つのターミナル(SQL*Plus)を用意して、下記のように実行した。

<<ターミナル1>>
$ sqlplus /nolog

-- 検証用ユーザでDB接続
SQL> conn testuser/*****

-- セッションID情報の取得
SQL> SELECT SYS_CONTEXT('USERENV', 'SID')  FROM DUAL;

-- SQLトレースの取得開始
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- データパッチSQLの実行
(UPDATE方式の場合) SQL> UPDATE tab01 SET flg = 1 WHERE flg = 0 and ym = '202302';
(INSERT方式の場合) SQL> ALTER TABLE tab01 EXCHANGE PARTITION pt_202302 WITH TABLE tab02 INCLUDING INDEXES WITHOUT VALIDATION;
                   SQL> INSERT /*+ APPEND */ INTO tab01 partition(pt_202302) SELECT id, ym, 1, val, memo FROM tab02;

<<ターミナル2>>
$ sqlplus /nolog

-- 管理者などV$ビューを参照できるユーザでDB接続
SQL> conn / as sysdba

-- UNDO生成量の確認
SQL> SELECT ubablk * 8 / 1024 MB FROM v$transaction t, v$session s WHERE s.sid = <セッションID> AND s.saddr = t.ses_addr;

<<ターミナル1>>
-- トランザクションの終了
SQL> commit;

-- SQLトレースの取得終了
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

-- SQL*Plusの終了
SQL> exit

-- トレースファイルの確認 ※占有環境なので最新のトレースファイルにSQLトレースが出力されている想定
$ ls -altr <初期化パラメータdiagnostic_dest>/diag/rdbms/<DB名>/<SID名>/trace/*_ora_*.trc |tail -n 20

-- tkprofによるSQLトレースの成型
$ tkprof <上記で確認したトレースファイル名> <出力ファイル名>