前回は検証方法について記載したため、今回は検証結果について纏めた。
1.検証結果まとめ
検証結果は以下の通り。真面目にやるなら複数回実行して平均値を取って比較とかするところだが、面倒なので今回は1発勝負。
検証の結果、Exchange Partition + INSERT方式の方が早いことが確認できた。
| UPDATE | INSERT | |
|---|---|---|
| 実行時間[s] | 3.49 | 1.98 |
| CPU時間[s] | 3.44 | 1.93 |
| 論理読込ブロック数(query) | 10,557 | 14,640 |
| 論理読込ブロック数(current) | 512,981 | 87,300 |
| UNDO生成量[MB] | 76.8 | 25.6 |
100万件(約100MBのテーブル)の簡易な検証だったので、あまり差が出ないかと思っていたが、結構な差を確認することができた。
顕著なのは、論理読込ブロック数(current)の差だ。UPDATE方式の方が多いというより、INSERT方式の方が少ないという表現が正しいだろう。これは、ダイレクトパスインサートを使用していたことで、バッファキャッシュアクセスが回避されたためだと考えられる。
※論理読込ブロック数(current)は、データブロックを更新するために読込んだ回数みたいなイメージ
UNDOの生成量もそれなりの差分が発生していることが確認できたが、感覚的に、UNDOの生成はその時のDBの内部状態で変動する幅が大きい気がするため、1回だけの測定結果であるので、参考程度にとどめておこうと思う。
2.UPDATE方式の結果
UPDATE方式のSQLトレースおよびV$TRANSACTIONの結果は以下の通り。
2.1 SQLトレースの結果
UPDATE tab01 SET flg = 1 WHERE flg = 0 and ym = '202302' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 1 0 Execute 1 3.43 3.49 20791 10557 512980 499196 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 3.44 3.51 20791 10557 512981 499196 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 77 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 UPDATE TAB01 (cr=10778 pr=20791 pw=0 time=3505652 us starts=1) 499196 499196 499196 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=10397 pr=10393 pw=0 time=444443 us starts=1 cost=2862 size=4500000 card=500000) 499196 499196 499196 TABLE ACCESS FULL TAB01 PARTITION: 2 2 (cr=10397 pr=10393 pw=0 time=391311 us starts=1 cost=2862 size=4500000 card=500000)
2.2 V$TRANSACTIONの結果
MB ---------- 76.84375
3.Exchange Partition + INSERT方式の結果
Exchange Parition + INSERT方式のSQLトレースおよびV$TRANSACTIONの結果は以下の通り。
3.1 SQLトレースの結果
-- Exchange Partition ALTER TABLE tab01 EXCHANGE PARTITION pt_202302 WITH TABLE tab02 INCLUDING INDEXES WITHOUT VALIDATION call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 0.01 1 1 5 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.02 1 1 5 0 -- INSERT～SELECT INSERT /*+ APPEND */ INTO tab01 partition(pt_202302) (id, ym, flg, val, memo) SELECT id, ym, 1, val, memo FROM tab02 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 3 93 0 0 Execute 1 1.93 1.98 10414 14547 87300 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 1.94 1.99 10417 14640 87300 1000000 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 77 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 LOAD AS SELECT TAB01 (cr=14723 pr=10414 pw=10409 time=1991750 us starts=1) 1000000 1000000 1000000 TABLE ACCESS FULL TAB02 (cr=10395 pr=10392 pw=0 time=152728 us starts=1 cost=2862 size=68000000 card=1000000)
3.2 V$TRANSACTIONの結果
MB ---------- 25.59375
4.最後に
感覚的に早いだろうと思っていたExchange Partition+INSERT方式が、実際に速い傾向にあることが確認できた。パッチ対象テーブルに対する業務処理(アクセス)を停止できるシステムであれば、データパッチ時の選択肢として十分取り得ると考えている。
パーティションテーブルでない場合でも、Exchange Partitonの代わりに、テーブルリネームを駆使することで、同様の対応が実施できるだろう。
次の記事：Oracle19cと21cの初期化パラメータの差分について調べてみた ～その1～
