単純なSQL（insert into values）が遅い

暑さの続くある日、性能分析の依頼を受けました。

リリース準備中の新本番環境で、構成は以下の通りです。

Oracle Restart(SIHA) EE 12.1.0.2
AIX Version 7.1

依頼内容は「月初の処理に時間が掛かっており、運用上の重大課題になっている。改善余地および、改善案を検討いただきたい」というものです。

図解入門よくわかる最新Oracleデータベースの基本と仕組み第4版 アーキテクチャと機能から学ぶOracle12c入門 （How-nual　visual　guide　book） [ 水田巴 ]

依頼と共に、問題の発生している時間帯のAWRレポートが送られて来ました。

性能分析の常として、問題が発生していない時間帯も、比較対象として送って貰います。

その結果、判ったのは問題の発生する時間帯は処理件数が多く、発生しない時間帯は件数が少ない、1件あたりの処理時間はどちらもほぼ同じ、という事でした。

つまり、月初が特に遅い訳ではなく、いつも遅いが、件数が多い時のみ問題が顕在化しているという事です。

対象の処理を見てみると、同じテーブルに対してinsert,update,delete,selectを繰り返しており、1実行あたりの対象件数は1件（多くても数件）となっていました。

パフォーマンス劣化でありがちな原因は実行計画の変化なのですが・・・。

対象となっているSQLが、どれもとても単純です。

updateとselectはいくつかの条件で絞っているので実行計画が変化する余地は多少、ありそうですが、deleteとinsertは以下のようなシンプルなものです。

SQL> delete from TAB_1 where COL1=:b0;
SQL> insert into TAB_1(col1,col2,col3・・・) values (:b0, :b1, :b2, ・・・);

deleteの方は条件句のカラムにインデックスの有無で性能が変わりそうですが、insertの方は一つの実行計画しか存在しないシンプルさです。

こんな単純なDMLが遅いのであれば実行計画の問題ではなさそうで、過剰に作成されたインデックスが悪さをしているのかも知れません。

そこでmetadata_onlyでエクスポートを取得してもらい、インデックス定義等を確認しました。

 それで判明したのは

• deleteの条件句のカラムに単独インデックスあり
• 対象テーブルに多数のインデックスあり（テーブルによっては10以上）
• 対象テーブルにinsertトリガー、updateトリガー、deleteトリガーあり
• テーブルのカラムにシーケンスを使用しているものがある

こうなるとdeleteはこれ以上、早くなる余地が無いように思えます。

一方のinsertですが、検証環境でテストしたところ、多数のインデックスやinsertトリガーがあっても、新本番環境より遥かに高速に処理されました。

こんな単純なSQLがこれ以上、早くなるのか・・・？と悩んだのですが、AWRに気になる点がありました。（一部抜粋） 

Physical Reads	Executions	Reads per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Text
21,785	7,785	2.80	0.22	324.39	3.01	67.49	insert into TAB_P...
13,469	7,785	1.73	0.13	234.86	3.74	58.50	insert into TAB_H...

insert文なのに、妙に物理読み込みが多いのです。

insert  select であれば分かりますが、insert into values形式のinsertでこれだけ大量の物理readがあるなんて、一体、何を読んでいるのか・・・？

という事でMOS(MyOracleSupport)で調べたところ、以下のドキュメントに行き当たりました。

ASSMの表領域に対する DELETE, INSERT 同時実行のパフォーマンス(KROWN:104321) (ドキュメントID 1732527.1)

ASSM*1の表領域に存在するテーブルに対し、一つのセッションで DELETE を実行している状態（commitする前）で別のセッションからINSERT を実行すると、仕様上の制約により物理読み込みが増えてINSERT のパフォーマンスが大きくダウンするという現象です。

具体的には、ASSMでは空き領域をビットマップで管理していますが、deleteが実行されるとすぐにビットマップ情報が更新され、insert処理はこの情報を元に空きブロックにアクセスするが、delete処理がcommitされていない為に書き込みが行えず、別のブロックを探しに行く為にアクセスされるブロックが増大する、というものです。

早速、検証環境で試してみます。

結果、確かに物理readが発生し、ASSMではない手動セグメント管理の表領域に移動させる事で物理readが低減しました。

が、新本番環境ほどの性能の変化は見られません。

再度、テストを行ったところ、手動セグメント管理の表領域に移動させなくとも、ASSMの表領域内でmoveしただけで物理readが低減しました。

つまり、insert、deleteを繰り返す事で断片化していたテーブルが、moveによって断片化解消したので、それに伴って物理readも減少したのです。

そうなると気になるのが「テーブルのカラムにシーケンスを使用しているものがある」という点です。

insert対象のテーブルは複数ありますが、その全てでシーケンスを使用しており、シーケンスを使用したカラムを先頭に持つインデックスが作成されています。

BTreeインデックスは値によって格納されるリーフ・ブロックが決められる為、シーケンスのように増えていく一方の値が先頭カラムにあると、そのインデックスは一番右の（値が大きいデータが格納される）ブロックのみが分割され、どんどん右に増えていくイメージになります。

一方、deleteを実行してもブロック全てが空にならない限りそのリーフ・ブロックは解放されないので、insert、deleteを繰り返す事で断片化し、ブロック数が増えてゆきます。

性能劣化の原因が断片化であるかどうかを確認する為、セグメント・アドバイザの実行結果を送ってもらいました。

その結果、問題の処理で対象となる複数のテーブルと、そのテーブルに付随するインデックスで500MBから1GB以上の断片化による無駄な領域がある事が判明しました。

インデックスと違ってテーブルはdeleteで空いた領域は再利用される筈なのですが、それについては以下のドキュメントに説明がありました。

Space Used By Tables Are Not Being Re-used With Repeat Delete/insert Operation (ドキュメントID 1601805.1)

領域管理アルゴリズムでは、空き領域を探す際に速さを重視するので、必ずしもすべての空き領域をチェックせずに、（再利用可能な空き領域があっても）領域拡張を行う、という内容です。

この結果、テーブルに対するinsert、deleteが繰り返される事で断片化が発生します。

断片化が原因でテーブルのフルスキャンやインデックス・レンジスキャンの性能劣化が発生するのは当然ですし、insertについても性能影響がある事が検証環境で確認できています。

insert into values形式のinsert処理なのに一体、何を読んでいるのか…？の答えは、書き込み可能な空き領域を探してブロックアクセスを行っていた、という事になりますね。

後は新本番環境でテーブルおよびインデックスの断片化を解消し、再度テストを行って性能改善する事を確認してもらえば今回のタスクは完了です。

・・・なのですが。

人手が足りないので再テストは行えないと言われてしまいました・・・

なので、断片化が発生するとあらかじめ判っているテーブル・インデックスについては解消手段を講じる事を提案し、性能改善についてはリリース後に確認する運びとなりました。

副産物として、新環境の領域見積もりは現行環境の表領域使用量をベースに見積もられていたのですが、断片化解消により必要領域がかなり減る事が判ったので、再見積もりとなり、表領域のリサイズを実施する事となりました。

その時、利用した小技が以前の記事で紹介したものになります。 

bismarc256.hateblo.jp

後日譚になりますが、本番リリース後の問題の処理の性能を確認してみました。

リリース前 

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Text
324.39	7,785	0.04	1.38	3.01	67.49	insert into TAB_P...
242.95	1,187	0.20	1.04	6.10	33.25	delete from TAB_R ...
234.86	7,785	0.03	1.00	3.74	58.50	insert into TAB_H...
228.64	1,194	0.19	0.97	1.64	88.85	update TAB_F_RP...
199.68	7,786	0.03	0.85	8.26	13.04	update TAB_CM set STAT=:b0...
197.64	12,927	0.02	0.84	6.05	7.72	insert into TAB_R ...

 リリース後

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Text
120.36	24,576	0.00	2.51	22.01	37.88	insert into TAB_P...
90.90	2,150	0.04	1.90	17.31	54.79	delete from TAB_R ...
125.61	24,566	0.01	2.62	21.62	39.77	insert into TAB_H...
97.22	2,167	0.04	2.03	5.60	89.75	update TAB_F_RP...
125.02	24,575	0.01	2.61	35.05	5.37	update TAB_CM set STAT=:b0...
57.83	19,268	0.00	1.21	31.24	9.45	insert into TAB_R ...

 insert、update、delete共に明らかに速くなっています。

また、insert時の物理readも大幅に減少していました。

リリース前にテストできなかったので多少の不安はありましたが、無事に性能向上していて安心しました。

と言っても、リリース前に全くテストされていなかった処理については別の問題が起きたのですが、それはまた別の話です・・・

*1:自動セグメント管理。9i以降のデフォルト