バインドミスマッチの罪 [オプティマイザ]
最近の現場でcursor: mutex X待機によるSQL遅延の問題に遭遇した。cursor: mutex XはSQLのハードパース時に排他ロックを獲得する際に待ちが発生したことを示す。通常、同じSQLは1度ハードパースされれば2回目からはそれが再利用されるため、ハードパースが多発することはない。しかし、今回のケースでは、バインドミスマッチにより、同じSQLでもカーソルの共有がされず、大量のハードパースが発生していることがわかった。今回はこのバインドミスマッチが如何に罪深いのかについて考えてみたい。
なお、先に結論を書いておくと、今回の教訓としては、JavaでOracleを使う場合は以下２点に注意、ということである。
・DATE型カラムに対してTIMESTAMP型でなくDATE型でバインドすること
・NULL値の場合にカラムの型でバインドすること(setNullにjava.sql.Types.NULLを渡してはいけない)
そして、それを怠った場合の代償として、バインドミスマッチが発生するカラム数に応じて、2のべき乗で子カーソルが爆発的に増加する事象が発生する。
◇バインドミスマッチによる子カーソル増加の問題
バインドミスマッチとは、バインド変数を利用しているSQL、DMLにおいて、バインド変数の型の不一致で共有プール上のカーソルが共有されない事象を指す。例えば、あるテーブルにDATE型カラムがあり、これを更新するUPDATE文があったとしよう。バインド変数を使っていれば、UPDATE文にどのような値が入ってもSQLIDは同じであり、カーソルは共有されることを期待する。しかし、ここで、DATE型に対し、DATE型でバインドするのと、TIMESTAMP型でバインドするのでは、SQLIDは同じでもOracle内部では異なるカーソル（厳密には１つの親カーソルに対し、２つの子カーソル）として管理される。これがバインドミスマッチである。
実際にバインドミスマッチの発生している状況を見てみよう。EMP表にはc3,c4のDATE型カラムがある。update文でc3,c4それぞれにDATE型とTIMESTAMP型の2つのパターンでバインドし、4通りの組み合わせで実行する。結果、このSQLの子カーソル数(VERSION_COUNT)が4になる。つまり、バインド変数を使い同じSQLを実行しているにも関わらず、カーソルが共有されず、それぞれハードパースされているのだ。本来バインドミスマッチが発生しなければ、VERSION_COUNTは1になるはずである。なお、手元の環境はOracle19.3である。
子カーソルが4個になることくらいは大した問題でないと思うかもしれない。しかし、これが様々なカラムで発生すると、1カラムで2通りの組み合わせ（＝カーソル数）、2カラムでは2^2=4通り、3カラムでは2^3=8通り、、、と2のべき乗で増加するため、親カーソルの下に子カーソルが爆発的に大量にできてしまう。
では、子カーソルが増えることによる問題は何か。SQL実行の度にハードパースやカーソルの検索にCPUを消費する、ライブラリキャッシュを消費するといった問題は言うまでもない。しかし最も大きな問題は、ハードパース多発によってSQLの同時実行性が下がり、SQLの性能が著しく劣化（ハング）することだろう。カーソルが共有されている状況では、トランザクションはカーソルを共有ロック(cursor: mutex S)するので、競合は発生しない。しかし、ハードパースはカーソルを排他ロック(cursor: mutex X)する。このためハードパースが多発すると、SQLの実行がシリアライズされてしまう。したがって、SQLの同時実行性を保つためには、ハードパースを減らさなければならず、そのためにはカーソルが共有されるようにしなければならない。
子カーソルが共有されない理由は多数あるが、v$sql_shared_cursorを確認すればその理由を確認することができる。下記はバインドミスマッチの例であるが、子カーソルのbind_mismatch列がYになっていることがわかる。
またreason列に、明確にBind mismatchと記載されている。
さらに、v$sql_bind_captureでバインドミスマッチがどのバインド変数で発生しているかが確認できる。この場合はポジション3と4でDATEとTIMESTAMPのミスマッチが発生していることが確認できる。
なお、バインドミスマッチ以外にも子カーソルが共有されない理由は多数ある。詳細は（Troubleshooting: High Version Count Issues(Doc ID 296377.1))が参考になるだろう。
◇バインドミスマッチとの闘い その１～NUMBER型カラムにVARCHAR2型バインドの例
私がこの問題に初めて遭遇したのは、2016年頃であろうか、特定の時間帯に発生するcursor: mutex Xとcursor: mutex Sの待機によるSQL遅延、CPU高騰の事象を追いかけていた時である。テーブルは50カラム程度のNUMBER型であり、それに対するDML文で多数のバインドミスマッチが発生し、子カーソルが増加（数百を超えるオーダー）していたのである。原因はJavaのAP（正確にはMyBatis経由のJDBC）が、NULL値をVARCHAR2でバインドしていたためであった。OracleのJDBCドライバはNULLの場合に型を明示的に設定しないとデフォルトVARCHAR2型でバインドする。このため、値が入っているとき（NUMBER型でバインド）とNULL値のとき（VARCHAR2でバインド）でバインドミスマッチが発生したのである。論理的には子カーソル数は、50カラムで2^50、すなわち(2^10)^5=~(10^3)^5=10^15=1000兆にもなる。
この問題を回避するためには、MyBATISのSQLを記載するxmlファイルに、JDBC型の情報を明示的に付加する必要がある。MyBatisのドキュメントの下記の部分に記載があるように、NULLが入る可能性のあるカラムに対しては、jdbcTypeを設定すればよい。
---
Mapper XML Files
... But there are a lot of other features of parameter maps.
First, like other parts of MyBatis, parameters can specify a more specific data type.
#{property,javaType=int,jdbcType=NUMERIC}
Like the rest of MyBatis, the javaType can almost always be determined from the parameter object, unless that object is a HashMap. Then the javaType should be specified to ensure the correct TypeHandler is used.
NOTE The JDBC Type is required by JDBC for all nullable columns, if null is passed as a value. You can investigate this yourself by reading the JavaDocs for the PreparedStatement.setNull() method.
...
Despite all of these powerful options, most of the time you'll simply specify the property name, and MyBatis will figure out the rest. At most, you'll specify the jdbcType for nullable columns.
#{firstName}
#{middleInitial,jdbcType=VARCHAR}
#{lastName}
---
MyBatisのJDBCの使い方の実装を確認した訳ではないのであくまで想像であるが、MyBatisは型指定されていないバインド変数にnullが来た場合は、setNullを以下のようにjava.sql.Types.NULLで渡しているのではないか。この場合、OracleのJDBCドライバはVARCHAR2(32)型としてバインドしてしまう(※)ため、NULLとそうでない場合のバインドミスマッチが発生する。
if (i==null) ps.setNull(col_pos,java.sql.Types.NULL); else ps.setInt(col_pos, i);
※Troubleshooting cursor: mutex X Due to High Version Count on the Insert Statements with Binds using JDBC (Doc ID 1469726.1)
一方、MyBatisのjdbcTypeに型を明示的に指定すると、setNullの型を以下のように明示的に指定することができ、OracleへNUMBER型としてバインドすることができるのではないか。これなら、バインドミスマッチは発生しない。
if (i==null) ps.setNull(col_pos,java.sql.Types.INTEGER); else ps.setInt(col_pos i);
上記はNUMBER型カラムでバインドミスマッチが発生した例であるが、同じことはDATE型カラムでもNULLの入る型なら発生するはずである。唯一、VARCHAR2カラムだけは（JDBCのnullのデフォルトがVARCHAR2のため）この問題が顕在化することはない。
◇バインドミスマッチとの闘い その２～DATE型カラムにTIMESTAMP型バインドの例
今回私が遭遇した事例は、50カラム程度のDATE型があるテーブルに対するDMLであった。これもNULLの場合はDATE型、そうでない場合はTIMESTAMP型でバインドしていることが原因でバインドミスマッチが発生していた。javaのソース(JDBC)を確認すると、DATE型カラムに対して、NULLの場合はDATE型、そうでない場合はTimestamp型でバインドするコードになっていた。以下は状況を再現したサンプルAPである。java.sql.Timestamp型のvがnullなら、setNullでDATE型でバインド、そうでない場合はsetTImestampでTIMESTAMP型でバインドしている。
if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ps.setTimestamp(col_pos, v);
このようなコーディングがされている理由は後述するとして、本来どのようにDATE型をバインドすべきだろうか。javaのコーディングについては素人ではあるが、JDBC developer's guideを読む限りは、vをoracle.sql.DATE型として、以下のようなコーディングをすればよい。手元の環境で確認した限り、きちんとDATE型バインドされるようになった。
if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ((OraclePreparedStatement)ps).setOracleObject(col_pos, v);
※参考：11.4.6 The setObject and setOracleObject Methods
しかし、個人的にはこのコーディングはOracleに寄りすぎている感も否めない。vをjava.sql.Date型とし、以下のように記述する方がJDBC的には自然かもしれない。これでも、DATE型バインドされる。
if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ps.setDate(col_pos, v);
setDateは時分秒を設定できないものと思っていたが、12.1.0.1からJDBCドライバの仕様が変わっており、java.sql.Dateに設定された時分秒も、setDateできちんとDATE型に変換されるようになっているそうである（Doc ID 1944845.1）。なお、11.2.0.4より前では、時分秒は"00:00:00"となるのが仕様である。
◇JDBCのDATE型のバインドの歴史
今回、上記その２の事例でOracleのDATE型に対するJDBCのバインドについて調べる中で、その経緯を根深さに驚かされた。
おそらくこのAPはTIMESTAMP型がないOracle8の頃に作られたのであろう。OracleのDATE型は時分秒を持つが、JavaのDate型は日付のみしか持たない。このため、DATE型に対してjavaのTimestamp型でバインドするのは一般的なコーディングであったのだろう。このときは、以下のようにDATE型とTimestamp型はJDBCで透過的にマッピングされていた。このため、上記のようなコーディングでも、DATE型バインドに統一され、バインドミスマッチは発生しない。
Oracle:DATE <- -> java:Timestamp
しかし9iR2でOracleにTimestamp型が導入される。このときOracleは以下のようにJDBCのマッピングを変更してしまった。これは（Oracleも認めているが）明らかな誤りである。例えば時分秒を持つDATE型カラムをSELECTすると、時分秒を持たないjavaのDate型に変換されてしまうため、時分秒が欠落してしまう。この結果、多くのJDBCを使うAPにおいて非互換の問題を発生させることとなった。
Oracle:DATE <- -> java:Date ★
Oracle:TIMESTAMP <- -> java:Timestamp
このため、Oracleは従来のマッピングをさせるV8CompatibleというフラグをJDBCドライバに導入した。これを以下のようにtrueに設定すると、JDBCはOracle8iのときと同様にDATE型とTimestamp型を透過的に変換するようになる。おそらく9iに更改した経験のあるシステムでは、WebLogic等のAPサーバにこの設定を入れて非互換を回避したに違いない。
java -Doracle.jdbc.V8Compatible="true" MyApp
Oracle:DATE <- -> java:Timestamp ★V8Compatible
Oracle:TIMESTAMP -> java:Timestamp
Oracleがこの問題を本格的に対処したのは11.1である。mapDateToTimestamp（デフォルト値がtrue）が導入され、以下のようにOracleのDATE型はjavaのTimestampにデフォルトでマッピングされるようになった。このとき、Oracleはこの問題は解決されたこと、そしてOracle8iのサポートが終了したことを理由に、V8Compatibleの使用は強く非推奨とし、サポートを止めたのである。そして、19cとなった今現在も、このマッピングは基本的に変わっていない。
Oracle:DATE <- java:Date
Oracle:DATE -> java:Timestamp ★mapDateToTimestamp
Oracle:TIMESTAMP <- -> java:Timestamp
OracleがV8Compatibleを実際に機能から外したのは恐らく11.2である。その根拠は、以下のMOSのドキュメントに11.2ドライバでV8Compatibleが機能しないことが示されている。
JDBC 11g: Timestamp から Date へのマッピング (Doc ID 1933596.1)
また、間接的ではあるが、JDBC developer's guideを時系列で比較すると、11.2でDATE型に対するJDBCの型マッピングがoracle.sql.DATE一択に変更されていることから、ここで大きなコードに修正が入った可能性を示唆している。
Valid SQL-JDBC Datatype Mappings
SQL datatypes: DATE Can be materialized as these Java types:
◆9.2.0
oracle.sql.DATE
java.sql.Date
java.sql.Time
java.sql.Timestamp
◆10.1～11.1
oracle.sql.DATE
java.sql.Date
java.sql.Time
java.sql.Timestamp
java.lang.String
◆11.2～19
oracle.sql.DATE
上記が正しい場合、V8Compatibleに依存していたAPは、11.2を境にJavaのTimestamp型がDATE型でなくTIMESTAMP型にマッピングされるよう挙動が変わる。これが、冒頭のバインドミスマッチが発生するようになった原因であると考えられる。
◇DATE型にTIMESTAMP型バインドしたときの影響
この問題がバインドミスマッチ以外にどのような影響を及ぼす可能性があるのか。１つが上記DocID 1933596.1で示された結果不正となる事例である。例えば、DATE型カラムHIREDATEを持つ空のEMP表があり、以下のようにHIREDATEに同じ値をバインドしINSERTとSELECTを実行するとする。期待する結果はINSERTされたレコードが1件、SELECTで件数として返却されることである。
INSERT INTO EMP (... HIREDATE) VALUES (... ?);
SELECT COUNT(*) WHERE HIREDATE = ?
ここで、HIREDATEをsetTimestampでバインドすると、INSERTではOracleはTIMESTAMP型を暗黙的型変換しDATE型に変換する。このとき、小数点以下の秒は削除してカラムに格納される。次にSELECTのWHERE句を同様にsetTimestampでバインドする。このとき、OracleはDATE型とTIMESTAMP型は直接比較できないため、HIREDATEカラムをTIMESTAMP型に暗黙的型変換をする。ここでバインド変数側に秒以下の値が入っていると、WHERE句の条件が真にならず、件数が0件で返ってくるということが起こる。この問題は、V8Compatibleが有効に機能していれば、setTimestampはDATE型としてバインドされるため発生しない。
もう一つ考えられるのは、TIMESTAMP型への暗黙的型変換によりDATE型の索引が使われなくなるDocID 1557194.1の問題である。WHERE句のバインドにおいて、DATE型カラムの条件をTIMESTAMP型でバインドすると、DATE型カラムの方にTO_TIMESTAMPによる変換が発生し、このDATE型カラムに張られた索引が使われない、という事象である。上記の例ではHIREDATEのINDEX RANGE SCANを期待したところ、実行計画のpredicate部にTO_TIMESTAMP(HIREDATE)が出てしまい、EMPのフルスキャンになってしまう、ということが想定される。
Poor Performance With Query Called From JDBC Using Bind Variables Compared To A DATE Column (Doc ID 1557194.1)
なお、この問題はTIMESTAMP型とDATE型を直接比較できるように12.2で修正され、11gにもパッチが提供されているようである。これもV8Compatibleが有効に機能していれば発生しない問題である。
◇BIND_LENGTH_UPGRADEABLEによる子カーソル増加の問題
先にも述べた通り子カーソルが共有されない原因はバインドミスマッチだけではない。しかし、バインドミスマッチが如何に罪深いか、BIND_LENGTH_UPGRADEABLEと比較してみよう。
VARCHAR2をバインドするとき、バインド変数のバッファ長に応じて以下の4つのパターンでバインドする。例えば、(1)の文字列長でバインドした後、それより大きい(2)のパターンでバインドすると、カーソルは共有されずハードパースが走り、新たな子カーソルが作成される。これがBIND_LENGTH_UPGRADEABLEである。
(a)0～32バイト →VARCHAR2(32)でバインド
(b)33～128バイト →VARCHAR2(128)でバインド
(c)129～2000バイト →VARCHAR2(2000)でバインド
(d)2001～4000バイト →VARCHAR2(4000)でバインド
実際に2つのVARCHAR2カラムc1,c2をバインドするDMLを4回実行する例を見てみよう。JDBCで2つのVARCHAR2(40)型カラム(c1,c2)を持つEMP表のレコードを以下の条件で更新する。手元の環境では19.3、DBのNLS_CHARACTERSETがAL32UTF8のためか、Javaで8バイトまでがVARCHAR2(32)でバインドされる。
(1)c1="123", c2="123"
(2)c1="123456789", c2="123"
(3)c1="123", c2="123456789"
(4)c1="123456789", c2="123456789"
(5)c1="123456789012345678901234567890123", c2="123456789"
(6)c1="123456789", c2="123456789012345678901234567890123"
(7)c1="123456789012345678901234567890123", c2="123456789012345678901234567890123"
実行した結果、v$sql_bind_captureは以下の通り、子カーソルが5個確認できる。
上記の結果から、以下のような動きになっていたと考えられる。
(1)でハードパース★。c1はVARCHAR2(32), c2はVARCHAR2(32)でバインドされる
(2)はハードパース★。c1はVARCHAR2(128), c2はVARCHAR2(32)でバインドされる。
(3)はハードパース★。c1はVARCHAR2(128), c2はVARCHAR2(128)でバインドされる。
(4)は(3)のカーソルが共有されソフトパース
(5)はハードパース★。c1はVARCHAR2(2000), c2はVARCHAR2(128)でバインドされる。
(6)はハードパース★。c1はVARCHAR2(2000), c2はVARCHAR2(2000)でバインドされる。
(7)は(6)のカーソルが共有されソフトパース
1つのVARCHAR2カラムに対して、最大4つのバリエーションの子カーソルが発生する可能性があると考えると、4^カラム数だけの子カーソルが発生し、バインドミスマッチより組み合わせが増えるのではないかと考えてしまうかもしれない。しかし上記の動きから考えると、実際はアップグレードされたバッファサイズは大きくなる1方向であり、一度大きい型でバインドされればそれ以下の型でバインドされることはない。これは3x3の格子を左下から右上まで移動するのに経由した座標の数に等しいと考えると、3x2+1=7となる。したがって、一般的にはカラム数Nに対し、BIND_LENGTH_UPGRADEABLEにより発生する最大の子カーソル数はたかだか3N+1となる。カラム数の増加に対し、バインドミスマッチが如何に問題を引き起こしやすいか理解できるだろう。
また、BIND_LENGTH_UPGRADEABLEにより子カーソルが作られた場合、v$sql.IS_SHAREABLEが最新のものだけがYとなる。古い子カーソルは共有されることがないようすべてNとなり、優先的なパージ対象となることも特筆すべき点だろう。
◇バインドミスマッチの罪
以上のことから、バインドミスマッチの罪深さをまとめると、以下の通りである。
・カラム数Nに対し2^Nのオーダーで爆発的に子カーソルが増える
・ハードパースが多発し、大量の子カーソルの排他制御のため同時実行性が低下する
・すべての子カーソルでIS_SHAREABLEがYのためパージされにくい
・共有プールを無駄に使用する
・ハードパースや子カーソルの検索でCPUを消費する
・データバリエーション、多重度の組み合わせで遅延が顕在化するため、試験での検出や問題の再現が困難
・AP修正は概して広範囲にわたり、実質的に修正が困難
したがって、この問題を防ぐには適切なコーディン規約を作ることは言うまでもないが、それにも増して重要なのは、Oracleを使うJava技術者にこの事実を広く知っていただきたいということである。
そして冒頭で述べたように、どのようなフレームワークを用いるにしてもJavaでOracleを使う場合は、以下のことに注意することを心からお勧めする。
・DATE型カラムに対して(TIMESTAMP型でなく)DATE型でバインドすること
・NULL値の場合にカラムの型でバインドすること(setNullにjava.sql.Types.NULLを渡してはいけない)
以上
2020/5/25追記
今回、JDBCとiBatis・myBatisのJavaのフレームワーク経由でOracleに接続する場合のバインドミスマッチについて述べたが、アーキテクチャ上、他のJavaのフレームワークでも同様の事象は発生し得ると考えられる点、注意が必要だろう。
例えばnodes.jsのOracleのドライバは、DMLではDATE型やTIMESTAMP型をTIMESTAMP WITH LOCAL TIME ZONEとしてバインドする。小規模なプログラムでは問題ないだろうが、大規模な開発では意識しておく必要があるだろう。
参考：Working with Dates Using the Oracle Node.js Driver
逆にPro*CではDMLは一般的にVARCHARでバインドし、SQL側でTO_DATEするのが一般的であるため、この問題が発生するケースは稀と思われる。
参考：Pro*C/C++ Programmer's Guide 19c
以上
なお、先に結論を書いておくと、今回の教訓としては、JavaでOracleを使う場合は以下２点に注意、ということである。
・DATE型カラムに対してTIMESTAMP型でなくDATE型でバインドすること
・NULL値の場合にカラムの型でバインドすること(setNullにjava.sql.Types.NULLを渡してはいけない)
そして、それを怠った場合の代償として、バインドミスマッチが発生するカラム数に応じて、2のべき乗で子カーソルが爆発的に増加する事象が発生する。
◇バインドミスマッチによる子カーソル増加の問題
バインドミスマッチとは、バインド変数を利用しているSQL、DMLにおいて、バインド変数の型の不一致で共有プール上のカーソルが共有されない事象を指す。例えば、あるテーブルにDATE型カラムがあり、これを更新するUPDATE文があったとしよう。バインド変数を使っていれば、UPDATE文にどのような値が入ってもSQLIDは同じであり、カーソルは共有されることを期待する。しかし、ここで、DATE型に対し、DATE型でバインドするのと、TIMESTAMP型でバインドするのでは、SQLIDは同じでもOracle内部では異なるカーソル（厳密には１つの親カーソルに対し、２つの子カーソル）として管理される。これがバインドミスマッチである。
実際にバインドミスマッチの発生している状況を見てみよう。EMP表にはc3,c4のDATE型カラムがある。update文でc3,c4それぞれにDATE型とTIMESTAMP型の2つのパターンでバインドし、4通りの組み合わせで実行する。結果、このSQLの子カーソル数(VERSION_COUNT)が4になる。つまり、バインド変数を使い同じSQLを実行しているにも関わらず、カーソルが共有されず、それぞれハードパースされているのだ。本来バインドミスマッチが発生しなければ、VERSION_COUNTは1になるはずである。なお、手元の環境はOracle19.3である。
SQL> desc scott.emp Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NOT NULL NUMBER C2 VARCHAR2(40) C3 VARCHAR2(40) C4 DATE C5 DATE SQL> select sql_id, version_count, last_load_time, sql_text from v$sqlarea where sql_id='0hrt1s7pkz2qf'; SQL_ID VERSION_COUNT LAST_LOAD_TIME SQL_TEXT ------------- ------------- -------------------- ---------------------------------------- 0hrt1s7pkz2qf 4 20200426 00:40:35 UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 SQL> select sql_id, child_number, last_load_time, is_shareable, sql_text from v$sql where sql_id='0hrt1s7pkz2qf'; SQL_ID CHILD_NUMBER LAST_LOAD_TIME I SQL_TEXT ------------- ------------ -------------------- - ---------------------------------------- 0hrt1s7pkz2qf 0 2020-04-26/00:40:35 Y UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 1 2020-04-26/00:40:35 Y UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 2 2020-04-26/00:40:35 Y UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 3 2020-04-26/00:40:35 Y UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5
子カーソルが4個になることくらいは大した問題でないと思うかもしれない。しかし、これが様々なカラムで発生すると、1カラムで2通りの組み合わせ（＝カーソル数）、2カラムでは2^2=4通り、3カラムでは2^3=8通り、、、と2のべき乗で増加するため、親カーソルの下に子カーソルが爆発的に大量にできてしまう。
では、子カーソルが増えることによる問題は何か。SQL実行の度にハードパースやカーソルの検索にCPUを消費する、ライブラリキャッシュを消費するといった問題は言うまでもない。しかし最も大きな問題は、ハードパース多発によってSQLの同時実行性が下がり、SQLの性能が著しく劣化（ハング）することだろう。カーソルが共有されている状況では、トランザクションはカーソルを共有ロック(cursor: mutex S)するので、競合は発生しない。しかし、ハードパースはカーソルを排他ロック(cursor: mutex X)する。このためハードパースが多発すると、SQLの実行がシリアライズされてしまう。したがって、SQLの同時実行性を保つためには、ハードパースを減らさなければならず、そのためにはカーソルが共有されるようにしなければならない。
子カーソルが共有されない理由は多数あるが、v$sql_shared_cursorを確認すればその理由を確認することができる。下記はバインドミスマッチの例であるが、子カーソルのbind_mismatch列がYになっていることがわかる。
SQL> select sql_id,child_number,bind_mismatch,reason from v$sql_shared_cursor where sql_id='0hrt1s7pkz2qf'; SQL_ID CHILD_NUMBER B REASON ------------- ------------ - -------------------------------------------------------------------------------- 0hrt1s7pkz2qf 0 N <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(8)</reas 0hrt1s7pkz2qf 1 Y <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(8)</reas 0hrt1s7pkz2qf 2 Y <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(8)</reas 0hrt1s7pkz2qf 3 Y <ChildNode><ChildNumber>3</ChildNumber><ID>39</ID><reason>Bind mismatch(8)</reas
またreason列に、明確にBind mismatchと記載されている。
<ChildNode> <ChildNumber>3</ChildNumber> <ID>39</ID> <reason>Bind mismatch(8)</reason>★ <size>4x8</size> <bind_position>0000000200000000 </bind_position> <original_oacflg>0000000300000000 </original_oacflg> <original_oacdty>000000b400000000 </original_oacdty> <new_oacdty>0000000c00000000 </new_oacdty> </ChildNode>
さらに、v$sql_bind_captureでバインドミスマッチがどのバインド変数で発生しているかが確認できる。この場合はポジション3と4でDATEとTIMESTAMPのミスマッチが発生していることが確認できる。
SQL> select sql_id,child_number,position,datatype_string,precision,scale from v$sql_bind_capture where sql_id='0hrt1s7pkz2qf' order by 2,3; SQL_ID CHILD_NUMBER POSITION DATATYPE_STRING PRECISION SCALE ------------- ------------ ---------- -------------------- ---------- ---------- 0hrt1s7pkz2qf 0 1 VARCHAR2(128) 0hrt1s7pkz2qf 0 2 VARCHAR2(2000) 0hrt1s7pkz2qf 0 3 DATE 0hrt1s7pkz2qf 0 4 DATE 0hrt1s7pkz2qf 0 5 NUMBER 0hrt1s7pkz2qf 1 1 VARCHAR2(128) 0hrt1s7pkz2qf 1 2 VARCHAR2(2000) 0hrt1s7pkz2qf 1 3 TIMESTAMP 9 0hrt1s7pkz2qf 1 4 DATE 0hrt1s7pkz2qf 1 5 NUMBER 0hrt1s7pkz2qf 2 1 VARCHAR2(128) 0hrt1s7pkz2qf 2 2 VARCHAR2(2000) 0hrt1s7pkz2qf 2 3 DATE 0hrt1s7pkz2qf 2 4 TIMESTAMP 9 0hrt1s7pkz2qf 2 5 NUMBER 0hrt1s7pkz2qf 3 1 VARCHAR2(128) 0hrt1s7pkz2qf 3 2 VARCHAR2(2000) 0hrt1s7pkz2qf 3 3 TIMESTAMP 9 0hrt1s7pkz2qf 3 4 TIMESTAMP 9 0hrt1s7pkz2qf 3 5 NUMBER
なお、バインドミスマッチ以外にも子カーソルが共有されない理由は多数ある。詳細は（Troubleshooting: High Version Count Issues(Doc ID 296377.1))が参考になるだろう。
◇バインドミスマッチとの闘い その１～NUMBER型カラムにVARCHAR2型バインドの例
私がこの問題に初めて遭遇したのは、2016年頃であろうか、特定の時間帯に発生するcursor: mutex Xとcursor: mutex Sの待機によるSQL遅延、CPU高騰の事象を追いかけていた時である。テーブルは50カラム程度のNUMBER型であり、それに対するDML文で多数のバインドミスマッチが発生し、子カーソルが増加（数百を超えるオーダー）していたのである。原因はJavaのAP（正確にはMyBatis経由のJDBC）が、NULL値をVARCHAR2でバインドしていたためであった。OracleのJDBCドライバはNULLの場合に型を明示的に設定しないとデフォルトVARCHAR2型でバインドする。このため、値が入っているとき（NUMBER型でバインド）とNULL値のとき（VARCHAR2でバインド）でバインドミスマッチが発生したのである。論理的には子カーソル数は、50カラムで2^50、すなわち(2^10)^5=~(10^3)^5=10^15=1000兆にもなる。
この問題を回避するためには、MyBATISのSQLを記載するxmlファイルに、JDBC型の情報を明示的に付加する必要がある。MyBatisのドキュメントの下記の部分に記載があるように、NULLが入る可能性のあるカラムに対しては、jdbcTypeを設定すればよい。
---
Mapper XML Files
... But there are a lot of other features of parameter maps.
First, like other parts of MyBatis, parameters can specify a more specific data type.
#{property,javaType=int,jdbcType=NUMERIC}
Like the rest of MyBatis, the javaType can almost always be determined from the parameter object, unless that object is a HashMap. Then the javaType should be specified to ensure the correct TypeHandler is used.
NOTE The JDBC Type is required by JDBC for all nullable columns, if null is passed as a value. You can investigate this yourself by reading the JavaDocs for the PreparedStatement.setNull() method.
...
Despite all of these powerful options, most of the time you'll simply specify the property name, and MyBatis will figure out the rest. At most, you'll specify the jdbcType for nullable columns.
#{firstName}
#{middleInitial,jdbcType=VARCHAR}
#{lastName}
---
MyBatisのJDBCの使い方の実装を確認した訳ではないのであくまで想像であるが、MyBatisは型指定されていないバインド変数にnullが来た場合は、setNullを以下のようにjava.sql.Types.NULLで渡しているのではないか。この場合、OracleのJDBCドライバはVARCHAR2(32)型としてバインドしてしまう(※)ため、NULLとそうでない場合のバインドミスマッチが発生する。
if (i==null) ps.setNull(col_pos,java.sql.Types.NULL); else ps.setInt(col_pos, i);
※Troubleshooting cursor: mutex X Due to High Version Count on the Insert Statements with Binds using JDBC (Doc ID 1469726.1)
一方、MyBatisのjdbcTypeに型を明示的に指定すると、setNullの型を以下のように明示的に指定することができ、OracleへNUMBER型としてバインドすることができるのではないか。これなら、バインドミスマッチは発生しない。
if (i==null) ps.setNull(col_pos,java.sql.Types.INTEGER); else ps.setInt(col_pos i);
上記はNUMBER型カラムでバインドミスマッチが発生した例であるが、同じことはDATE型カラムでもNULLの入る型なら発生するはずである。唯一、VARCHAR2カラムだけは（JDBCのnullのデフォルトがVARCHAR2のため）この問題が顕在化することはない。
◇バインドミスマッチとの闘い その２～DATE型カラムにTIMESTAMP型バインドの例
今回私が遭遇した事例は、50カラム程度のDATE型があるテーブルに対するDMLであった。これもNULLの場合はDATE型、そうでない場合はTIMESTAMP型でバインドしていることが原因でバインドミスマッチが発生していた。javaのソース(JDBC)を確認すると、DATE型カラムに対して、NULLの場合はDATE型、そうでない場合はTimestamp型でバインドするコードになっていた。以下は状況を再現したサンプルAPである。java.sql.Timestamp型のvがnullなら、setNullでDATE型でバインド、そうでない場合はsetTImestampでTIMESTAMP型でバインドしている。
if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ps.setTimestamp(col_pos, v);
このようなコーディングがされている理由は後述するとして、本来どのようにDATE型をバインドすべきだろうか。javaのコーディングについては素人ではあるが、JDBC developer's guideを読む限りは、vをoracle.sql.DATE型として、以下のようなコーディングをすればよい。手元の環境で確認した限り、きちんとDATE型バインドされるようになった。
if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ((OraclePreparedStatement)ps).setOracleObject(col_pos, v);
※参考：11.4.6 The setObject and setOracleObject Methods
しかし、個人的にはこのコーディングはOracleに寄りすぎている感も否めない。vをjava.sql.Date型とし、以下のように記述する方がJDBC的には自然かもしれない。これでも、DATE型バインドされる。
if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ps.setDate(col_pos, v);
setDateは時分秒を設定できないものと思っていたが、12.1.0.1からJDBCドライバの仕様が変わっており、java.sql.Dateに設定された時分秒も、setDateできちんとDATE型に変換されるようになっているそうである（Doc ID 1944845.1）。なお、11.2.0.4より前では、時分秒は"00:00:00"となるのが仕様である。
◇JDBCのDATE型のバインドの歴史
今回、上記その２の事例でOracleのDATE型に対するJDBCのバインドについて調べる中で、その経緯を根深さに驚かされた。
おそらくこのAPはTIMESTAMP型がないOracle8の頃に作られたのであろう。OracleのDATE型は時分秒を持つが、JavaのDate型は日付のみしか持たない。このため、DATE型に対してjavaのTimestamp型でバインドするのは一般的なコーディングであったのだろう。このときは、以下のようにDATE型とTimestamp型はJDBCで透過的にマッピングされていた。このため、上記のようなコーディングでも、DATE型バインドに統一され、バインドミスマッチは発生しない。
Oracle:DATE <- -> java:Timestamp
しかし9iR2でOracleにTimestamp型が導入される。このときOracleは以下のようにJDBCのマッピングを変更してしまった。これは（Oracleも認めているが）明らかな誤りである。例えば時分秒を持つDATE型カラムをSELECTすると、時分秒を持たないjavaのDate型に変換されてしまうため、時分秒が欠落してしまう。この結果、多くのJDBCを使うAPにおいて非互換の問題を発生させることとなった。
Oracle:DATE <- -> java:Date ★
Oracle:TIMESTAMP <- -> java:Timestamp
このため、Oracleは従来のマッピングをさせるV8CompatibleというフラグをJDBCドライバに導入した。これを以下のようにtrueに設定すると、JDBCはOracle8iのときと同様にDATE型とTimestamp型を透過的に変換するようになる。おそらく9iに更改した経験のあるシステムでは、WebLogic等のAPサーバにこの設定を入れて非互換を回避したに違いない。
java -Doracle.jdbc.V8Compatible="true" MyApp
Oracle:DATE <- -> java:Timestamp ★V8Compatible
Oracle:TIMESTAMP -> java:Timestamp
Oracleがこの問題を本格的に対処したのは11.1である。mapDateToTimestamp（デフォルト値がtrue）が導入され、以下のようにOracleのDATE型はjavaのTimestampにデフォルトでマッピングされるようになった。このとき、Oracleはこの問題は解決されたこと、そしてOracle8iのサポートが終了したことを理由に、V8Compatibleの使用は強く非推奨とし、サポートを止めたのである。そして、19cとなった今現在も、このマッピングは基本的に変わっていない。
Oracle:DATE <- java:Date
Oracle:DATE -> java:Timestamp ★mapDateToTimestamp
Oracle:TIMESTAMP <- -> java:Timestamp
OracleがV8Compatibleを実際に機能から外したのは恐らく11.2である。その根拠は、以下のMOSのドキュメントに11.2ドライバでV8Compatibleが機能しないことが示されている。
JDBC 11g: Timestamp から Date へのマッピング (Doc ID 1933596.1)
また、間接的ではあるが、JDBC developer's guideを時系列で比較すると、11.2でDATE型に対するJDBCの型マッピングがoracle.sql.DATE一択に変更されていることから、ここで大きなコードに修正が入った可能性を示唆している。
Valid SQL-JDBC Datatype Mappings
SQL datatypes: DATE Can be materialized as these Java types:
◆9.2.0
oracle.sql.DATE
java.sql.Date
java.sql.Time
java.sql.Timestamp
◆10.1～11.1
oracle.sql.DATE
java.sql.Date
java.sql.Time
java.sql.Timestamp
java.lang.String
◆11.2～19
oracle.sql.DATE
上記が正しい場合、V8Compatibleに依存していたAPは、11.2を境にJavaのTimestamp型がDATE型でなくTIMESTAMP型にマッピングされるよう挙動が変わる。これが、冒頭のバインドミスマッチが発生するようになった原因であると考えられる。
◇DATE型にTIMESTAMP型バインドしたときの影響
この問題がバインドミスマッチ以外にどのような影響を及ぼす可能性があるのか。１つが上記DocID 1933596.1で示された結果不正となる事例である。例えば、DATE型カラムHIREDATEを持つ空のEMP表があり、以下のようにHIREDATEに同じ値をバインドしINSERTとSELECTを実行するとする。期待する結果はINSERTされたレコードが1件、SELECTで件数として返却されることである。
INSERT INTO EMP (... HIREDATE) VALUES (... ?);
SELECT COUNT(*) WHERE HIREDATE = ?
ここで、HIREDATEをsetTimestampでバインドすると、INSERTではOracleはTIMESTAMP型を暗黙的型変換しDATE型に変換する。このとき、小数点以下の秒は削除してカラムに格納される。次にSELECTのWHERE句を同様にsetTimestampでバインドする。このとき、OracleはDATE型とTIMESTAMP型は直接比較できないため、HIREDATEカラムをTIMESTAMP型に暗黙的型変換をする。ここでバインド変数側に秒以下の値が入っていると、WHERE句の条件が真にならず、件数が0件で返ってくるということが起こる。この問題は、V8Compatibleが有効に機能していれば、setTimestampはDATE型としてバインドされるため発生しない。
もう一つ考えられるのは、TIMESTAMP型への暗黙的型変換によりDATE型の索引が使われなくなるDocID 1557194.1の問題である。WHERE句のバインドにおいて、DATE型カラムの条件をTIMESTAMP型でバインドすると、DATE型カラムの方にTO_TIMESTAMPによる変換が発生し、このDATE型カラムに張られた索引が使われない、という事象である。上記の例ではHIREDATEのINDEX RANGE SCANを期待したところ、実行計画のpredicate部にTO_TIMESTAMP(HIREDATE)が出てしまい、EMPのフルスキャンになってしまう、ということが想定される。
Poor Performance With Query Called From JDBC Using Bind Variables Compared To A DATE Column (Doc ID 1557194.1)
なお、この問題はTIMESTAMP型とDATE型を直接比較できるように12.2で修正され、11gにもパッチが提供されているようである。これもV8Compatibleが有効に機能していれば発生しない問題である。
◇BIND_LENGTH_UPGRADEABLEによる子カーソル増加の問題
先にも述べた通り子カーソルが共有されない原因はバインドミスマッチだけではない。しかし、バインドミスマッチが如何に罪深いか、BIND_LENGTH_UPGRADEABLEと比較してみよう。
VARCHAR2をバインドするとき、バインド変数のバッファ長に応じて以下の4つのパターンでバインドする。例えば、(1)の文字列長でバインドした後、それより大きい(2)のパターンでバインドすると、カーソルは共有されずハードパースが走り、新たな子カーソルが作成される。これがBIND_LENGTH_UPGRADEABLEである。
(a)0～32バイト →VARCHAR2(32)でバインド
(b)33～128バイト →VARCHAR2(128)でバインド
(c)129～2000バイト →VARCHAR2(2000)でバインド
(d)2001～4000バイト →VARCHAR2(4000)でバインド
実際に2つのVARCHAR2カラムc1,c2をバインドするDMLを4回実行する例を見てみよう。JDBCで2つのVARCHAR2(40)型カラム(c1,c2)を持つEMP表のレコードを以下の条件で更新する。手元の環境では19.3、DBのNLS_CHARACTERSETがAL32UTF8のためか、Javaで8バイトまでがVARCHAR2(32)でバインドされる。
(1)c1="123", c2="123"
(2)c1="123456789", c2="123"
(3)c1="123", c2="123456789"
(4)c1="123456789", c2="123456789"
(5)c1="123456789012345678901234567890123", c2="123456789"
(6)c1="123456789", c2="123456789012345678901234567890123"
(7)c1="123456789012345678901234567890123", c2="123456789012345678901234567890123"
実行した結果、v$sql_bind_captureは以下の通り、子カーソルが5個確認できる。
SQL> select sql_id,child_number,position,datatype_string,precision,scale from v$sql_bind_capture where sql_id='0hrt1s7pkz2qf' order by 2,3; SQL_ID CHILD_NUMBER POSITION DATATYPE_STRING PRECISION SCALE ------------- ------------ ---------- -------------------- ---------- ---------- 0hrt1s7pkz2qf 0 1 VARCHAR2(32) 0hrt1s7pkz2qf 0 2 VARCHAR2(32) 0hrt1s7pkz2qf 0 3 DATE 0hrt1s7pkz2qf 0 4 DATE 0hrt1s7pkz2qf 0 5 NUMBER 0hrt1s7pkz2qf 1 1 VARCHAR2(128) 0hrt1s7pkz2qf 1 2 VARCHAR2(32) 0hrt1s7pkz2qf 1 3 DATE 0hrt1s7pkz2qf 1 4 DATE 0hrt1s7pkz2qf 1 5 NUMBER 0hrt1s7pkz2qf 2 1 VARCHAR2(128) 0hrt1s7pkz2qf 2 2 VARCHAR2(128) 0hrt1s7pkz2qf 2 3 DATE 0hrt1s7pkz2qf 2 4 DATE 0hrt1s7pkz2qf 2 5 NUMBER 0hrt1s7pkz2qf 3 1 VARCHAR2(2000) 0hrt1s7pkz2qf 3 2 VARCHAR2(128) 0hrt1s7pkz2qf 3 3 DATE 0hrt1s7pkz2qf 3 4 DATE 0hrt1s7pkz2qf 3 5 NUMBER 0hrt1s7pkz2qf 4 1 VARCHAR2(2000) 0hrt1s7pkz2qf 4 2 VARCHAR2(2000) 0hrt1s7pkz2qf 4 3 DATE 0hrt1s7pkz2qf 4 4 DATE 0hrt1s7pkz2qf 4 5 NUMBER
上記の結果から、以下のような動きになっていたと考えられる。
(1)でハードパース★。c1はVARCHAR2(32), c2はVARCHAR2(32)でバインドされる
(2)はハードパース★。c1はVARCHAR2(128), c2はVARCHAR2(32)でバインドされる。
(3)はハードパース★。c1はVARCHAR2(128), c2はVARCHAR2(128)でバインドされる。
(4)は(3)のカーソルが共有されソフトパース
(5)はハードパース★。c1はVARCHAR2(2000), c2はVARCHAR2(128)でバインドされる。
(6)はハードパース★。c1はVARCHAR2(2000), c2はVARCHAR2(2000)でバインドされる。
(7)は(6)のカーソルが共有されソフトパース
1つのVARCHAR2カラムに対して、最大4つのバリエーションの子カーソルが発生する可能性があると考えると、4^カラム数だけの子カーソルが発生し、バインドミスマッチより組み合わせが増えるのではないかと考えてしまうかもしれない。しかし上記の動きから考えると、実際はアップグレードされたバッファサイズは大きくなる1方向であり、一度大きい型でバインドされればそれ以下の型でバインドされることはない。これは3x3の格子を左下から右上まで移動するのに経由した座標の数に等しいと考えると、3x2+1=7となる。したがって、一般的にはカラム数Nに対し、BIND_LENGTH_UPGRADEABLEにより発生する最大の子カーソル数はたかだか3N+1となる。カラム数の増加に対し、バインドミスマッチが如何に問題を引き起こしやすいか理解できるだろう。
また、BIND_LENGTH_UPGRADEABLEにより子カーソルが作られた場合、v$sql.IS_SHAREABLEが最新のものだけがYとなる。古い子カーソルは共有されることがないようすべてNとなり、優先的なパージ対象となることも特筆すべき点だろう。
SQL> select sql_id, child_number, last_load_time, is_shareable, sql_text from v$sql where sql_id='0hrt1s7pkz2qf' SQL_ID CHILD_NUMBER LAST_LOAD_TIME I SQL_TEXT ------------- ------------ -------------------- - ---------------------------------------- 0hrt1s7pkz2qf 0 2020-04-25/22:06:49 N UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 1 2020-04-25/22:06:49 N UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 2 2020-04-25/22:06:49 N UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 3 2020-04-25/22:06:49 N UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 0hrt1s7pkz2qf 4 2020-04-25/22:06:49 Y UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5
◇バインドミスマッチの罪
以上のことから、バインドミスマッチの罪深さをまとめると、以下の通りである。
・カラム数Nに対し2^Nのオーダーで爆発的に子カーソルが増える
・ハードパースが多発し、大量の子カーソルの排他制御のため同時実行性が低下する
・すべての子カーソルでIS_SHAREABLEがYのためパージされにくい
・共有プールを無駄に使用する
・ハードパースや子カーソルの検索でCPUを消費する
・データバリエーション、多重度の組み合わせで遅延が顕在化するため、試験での検出や問題の再現が困難
・AP修正は概して広範囲にわたり、実質的に修正が困難
したがって、この問題を防ぐには適切なコーディン規約を作ることは言うまでもないが、それにも増して重要なのは、Oracleを使うJava技術者にこの事実を広く知っていただきたいということである。
そして冒頭で述べたように、どのようなフレームワークを用いるにしてもJavaでOracleを使う場合は、以下のことに注意することを心からお勧めする。
・DATE型カラムに対して(TIMESTAMP型でなく)DATE型でバインドすること
・NULL値の場合にカラムの型でバインドすること(setNullにjava.sql.Types.NULLを渡してはいけない)
以上
2020/5/25追記
今回、JDBCとiBatis・myBatisのJavaのフレームワーク経由でOracleに接続する場合のバインドミスマッチについて述べたが、アーキテクチャ上、他のJavaのフレームワークでも同様の事象は発生し得ると考えられる点、注意が必要だろう。
例えばnodes.jsのOracleのドライバは、DMLではDATE型やTIMESTAMP型をTIMESTAMP WITH LOCAL TIME ZONEとしてバインドする。小規模なプログラムでは問題ないだろうが、大規模な開発では意識しておく必要があるだろう。
参考：Working with Dates Using the Oracle Node.js Driver
逆にPro*CではDMLは一般的にVARCHARでバインドし、SQL側でTO_DATEするのが一般的であるため、この問題が発生するケースは稀と思われる。
参考：Pro*C/C++ Programmer's Guide 19c
以上
2020-04-27 04:31
