DMLごとのUNDO生成量の確認
今回はオラクルにおける、DML( INSERT / UPDATE / DELETE )ごとに生成されるUNDO（rollbackによるDML操作の取り消し）ブロックの数が違うことを確認した。
概要
たまに開発者から 「UPDATEってDELETEしてINSERTなんでしょ？だったらプログラムを綺麗に書くためにUPDATEの代わりにDELETE+INSERTにしたいんだけど」 って声を聞いたりする。それでよかったらUPDATEなんてそもそも存在しないんじゃないかとか思って調べて欲しいところではあるが、そんな時にDBAたる者、どういった実害があるのかちゃんと説明しておきたい。DBを守ってあげないといけない。
DMLの操作を取り消すためにUNDOという領域がある。端的に言えば、操作前のレコードの情報を格納しておいて、rollbackされた場合にレコードの値を戻すための領域。UNDOの生成量の観点からみると「DELETE + INSERT」よりも「UPDATE」の方がDBにとって良い。それを実際に確認したというのが今回の話。
なお、このことは以下の本に記されている。
環境
oracle 12c ( Single Instance )
確認１：DMLごとのUNDO生成量
UNDO生成量の確認方法
UNDOブロック数は以下のsqlで確認することができる。
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
/
各DMLの実行後にこのクエリを実行することで生成されたUNDOブロック数を確認する。
表の作成
create table testuser.t2
as
select object_name c1, object_name c2
from all_objects
5 /
表が作成されました。
SQL> desc testuser.t2;
名前 NULL? 型
----------------------------------------- -------- ----------------------------
C1 NOT NULL VARCHAR2(128)
C2 NOT NULL VARCHAR2(128)
-- UNDOブロックは作成されていないことの確認
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
確認
INSERT
SQL> insert into testuser.t2 select object_name, object_name from all_objects;
89539行が作成されました。
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
USED_UBLK
----------
34
SQL> commit;
コミットが完了しました。
-- コミット後はUNDOブロックが消えていることを確認
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
UPDATE
SQL> update testuser.t2 set c2 = lower(c2);
179078行が更新されました。
SQL>
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
USED_UBLK
----------
3163
SQL> commit;
コミットが完了しました。
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
DELETE
SQL> delete testuser.t2;
179078行が削除されました。
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
USED_UBLK
----------
3481
SQL> commit;
コミットが完了しました。
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
TRUNCATE
念のためTRUNCATEした際にはUNDOが生成されないことも確認しておく。
SQL>
SQL> truncate table testuser.t2;
表が切り捨てられました。
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
結果
| INSERT | UPDATE | DELETE | |
|---|---|---|---|
| 生成されたUNDOブロック数 | 34 | 3163 | 3481 |
| 順位(UNDO生成量が多い順) | 3 | 2 | 1 |
以上の結果から、UNDO生成量はDELETE, UPDATE, INSERTの順で多いことが確認できた。
一般に、この順番でUNDO生成量は多い。 というのもinsertの場合はUNDOとしてDELETEするためにレコードごとのrowid情報のみ、UPDATEの場合はUNDOとして更新されるカラムの値情報のみを保持すれば良いが、DELETEの場合はUNDOとしてレコード全カラムの値を保持する必要があるためである。
確認２：カラム更新もインデックスの有無によりUNDO生成量が変わる
次にインデックスが貼られているかどうかによって、当該カラムの更新により生成されるUNDOの量が変わることを確認する。
表の作成
create table testuser.t
as
select object_name unindexed, object_name indexed
from all_objects
5 /
表が作成されました。
-- indexedカラムにインデックスを作成
SQL> create index testuser.t_idx on testuser.t(indexed);
索引が作成されました。
SQL>
SQL> exec dbms_stats.gather_table_stats('TESTUSER','T');
PL/SQLプロシージャが正常に完了しました。
SQL>
インデックスのないカラムを更新
SQL> update testuser.t set unindexed = lower(unindexed);
89536行が更新されました。
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
USED_UBLK
----------
1584
SQL>
SQL> commit;
コミットが完了しました。
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
インデックスのあるカラムを更新
SQL> update testuser.t set indexed = lower(indexed);
89536行が更新されました。
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
USED_UBLK
----------
3590
SQL>
SQL>
SQL> commit;
コミットが完了しました。
SQL>
select used_ublk
from v$transaction
where addr = ( select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum=1
)
)
10 /
レコードが選択されませんでした。
以上の結果から、インデックスが貼られているカラムを更新する場合の方がUNDO生成量が多いことが確認できた。更新によってインデックスのメンテナンスが行われるため、それのUNDOも必要になるということが理由のようだ。
まとめ
DMLごとのUNDOの生成量は以下の順で多い。
- DELETE
- UPDATE
- INSERT
というわけで、開発者から「UPDATEとDELETE + INSERTって何が違うの？」と聞かれたら、UNDOの生成量が違うこと、後者の場合はUNDO生成量が多くなりDBへのパフォーマンスに影響する、ゆえにUPDATEが使用できる場合はUPDATEを使用すべしと答えれば良いだろう。また、「インデックスが貼られているカラムかどうかでもUNDO生成量は変わるんだよ」というプチ情報をプチかませば「さすがDBA詳しいですな」という声も聞けるかも。