Oracleの空コミット負荷はどのくらいか - kagamihogeの日記の続き。これを書いたときに芽生えた疑問があった。それは、あるセッションがDMLを伴うトランザクションをしているとき、それとは別のセッションが空コミットをしたら前者のトランザクションに影響を与えないのだろうか？ というもの。とはいえ、空コミットはREDOログを生成しない時点で影響が出ないと考えられるが、せっかくなので実際んとこどうなのかやってみる。
環境
やること
あるセッションは100万件INSERTプラス最後にCOMMIT1回のトランザクション処理をし、もう片方のセッションは空コミットを無限ループさせる。これで前者のトランザクションの実行時間が変化するかどうかを確認する。
計測用プログラムと準備
INSERT先のテーブルは下記のように準備する。また、計測開始前にテーブルを削除＆再作成する。
DROP TABLE insert_to_million_rows_table PURGE; CREATE TABLE insert_to_million_rows_table ( column1 VARCHAR2(20) );
さらに、計測開始前にバッファキャッシュのクリアと手動でログスイッチをしておく。
ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM SWITCH LOGFILE;
100万件INSERTするPL/SQLはこんな感じ。
create or replace PROCEDURE INSERT_TO_MILLION_ROWS_PROC AS BEGIN FOR I IN 1..1000000 LOOP INSERT INTO INSERT_TO_MILLION_ROWS_TABLE(COLUMN1) VALUES(DBMS_RANDOM.STRING('X', 20)); END LOOP; COMMIT; END INSERT_TO_MILLION_ROWS_PROC;
無限に空コミットするPL/SQLはこんな感じ。なお、バリエーションとして三種類異なる状態を試行する。三種類とは、SELECTのみ・COMMITのみ・SELECT + COMMIT、で無限ループさせることを指す。
create or replace PROCEDURE INFINITE_SELECT_DUAL AS INTO_1 INT; BEGIN WHILE (1=1) LOOP SELECT 1 INTO INTO_1 FROM DUAL;--COMMITのみのとき、この行をコメントアウトする COMMIT;--SELECTのみのとき、この行をコメントアウトする END LOOP; END INFINITE_SELECT_DUAL;
実行時間の比較
それぞれの組み合わせで、100万件INSERTするPL/SQLの実行時間は下記のとおり。なお、下記のINSERT項目は、無限ループ無しでINSERTするPL/SQLを流したときの実行時間である。
| 種類（秒） | 1 | 2 | 3 |
|---|---|---|---|
| INSERT | 125.871 | 125.639 | 125.444 |
| SELECT | 374.757 | 319.533 | 311.776 |
| COMMIT | 251.733 | 253.953 | 271.188 |
| SELECT + COMMIT | 555.201 | 402.370 | 472.566 |
感想
上記の時間だけを見ると空コミットによって遅くなっているようにも見えなくも無いが、実際には無限ループのCPU負荷が高いのでその分だけ遅くなっているだけ、と予測できる。その理由について考えていく。
まず、statspackレポートのCOMMITやREDO関連のところを見てみる。
| SELECT + COMMIT | INSERT | |
|---|---|---|
| user commits | 3 | 3 |
| redo size | 263,216,744 | 263,102,976 |
| redo synch writes | 4 | 3 |
| log file parallel write | 324 | 256 |
| log switches (derived) | 6 | 5 |
門外不出のOracle現場ワザ 第5章の空コミットのコラムでは、空コミットは実際のコミットやREDOの生成は行わない、との記載がある。そして、上記のstatspackレポートはそのことの裏付けになっている。つまり、このエントリでの実行時間の差は空コミットによるものではない、と考えられる。
というわけで、無限ループがCPUを浪費しているから実行時間に差がでているだけ、となる。空コミットをしようがしまいが、別のセッションがCPUを浪費しまくっていれば、他のセッションが影響を受けるのは当然である。SELECTだけの方がCOMMITだけよりも時間がかかっているのがその証拠といえる。逆を言えば、SELECT 1 FROM DUAL;より空コミットの方が負荷は軽い、とも言える。
空コミットはCOMMITやREDO生成しないが、ごくごく僅かながらCPUリソースを消費する。その消費はどのくらいかというと、無限ループで延々と空コミットをするぐらいになってようやく差が見られる程度のものでしかない。そのことにしても、無限ループによってCPUを無駄に食うことの方が大きく、無限ループ中での処理が空コミットかどうかのウェイトは低いものとなる。
よって結論としては、空コミットの負荷はほとんど無く、少なくともCOMMITやREDOの性能に影響を与えることは無い。ただし、無限ループで空コミットし続けるほど猛烈な負荷がかかる場合にはその限りではないが、現実的にそういうことはほとんど無いと考えられる。
