© 2023 NTT DATA Corporation バインドミスマッチの罪 JPOUG Tech Talk Night #7 2023年9月13日 株式会社NTTデータ 高橋 一裕

© 2023 NTT DATA Corporation 2 自己紹介 • DBスペシャリストとして難しめのSIプロジェクトのDB担当 • 技術領域はOracle、Exadata、OCI • 現場の経験を共有してDBAの生活の質を向上したい • OracleACEとしてコミュニティ活動 MOSC (My Oracle Support Community) コミュニティ活動

© 2023 NTT DATA Corporation 3 本日お伝えしたいこと • Oracleカーソル共有の仕組み • バインドミスマッチの問題とその解析、対処方法 • AP開発における留意点

© 2023 NTT DATA Corporation 4 Oracleのカーソル共有の仕組み • SQLが実行されると、共有プール上にSQLのハッシュ値（SQLID）毎にカーソルが作成される • 同じSQLならカーソルが再利用され、高価なSQLの解析処理（ハードパース）を回避できる • 全く同じSQLでないとカーソルは再利用されないので、ハードパースを回避するために、バインド変数を使う Oracle 共有プール（ライブラリキャッシュ） 子カーソル （実行計画など） 親カーソル ・・・ ◆SQL1 →SQLID: abc SELECT count(*) FROM EMP WHERE SAL < 10000; サーバプロセス SQL3 (sqlid:ghi) #1 SQL1 (sqlid:abc) #1 SQL2 (sqlid:def) #1 ◆SQL2 →SQLID: def SELECT count(*) FROM EMP WHERE SAL < 20000; ◆SQL3 →SQLID: ghi SELECT count(*) FROM EMP WHERE SAL < :b; バインド変数 :b=10000 :b=20000 :b=30000 … 同じSQLでないと SQLIDが一致しな いので、ハードパー スしてしまう バインド変数を使え ば、変数が異なる 値でもSQLIDは一 致するので、カーソ ルを再利用できる SQLが同じでないと SQLIDが変わるの で、ハードパースが 走ってしまう

© 2023 NTT DATA Corporation 5 バインド変数使っていれば大丈夫!? • バインド変数を利用しても、カーソル共有されず、ハードパースが発生する場合がある • 構文の意味や環境により、さまざまな理由がありうる 例） • スキーマが異なる • 既存の統計が一致しない • 言語の設定が一致しない（NLS_LANGなど） • OPTIMIZER_MODE(FIRST_ROWS/ALL_ROWSなど)の不一致 • ・・・ • カーソル共有されない理由の１つに、「バインドミスマッチ」がある [1]:SQL Tuning Guide, 3 SQL Processing https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-processing.html#GUID-873A7B2C-CD17-428A-8AE2-5B08906E45FB ★ ★ライブラリキャッシュ上にハードパース 済みのSQLIDが既に存在しても、構 文の意味や環境によってカーソルが共 有できないと判断されれば、ソフトパー スにならない。

© 2023 NTT DATA Corporation 6 バインドミスマッチの問題 • バインド変数の型の不一致でカーソルが共有されない事象 • バインド変数利用していても、ハードパースが多発してしまうことがある • ハードパースが発生すると、排他ロックによるmutex待ちだけでなく、子カーソル増加でメモリやCPUを消費してしまう • 子カーソルが極端に増えると性能に悪影響を及ぼす（HIGH VERSION COUNT問題:参考[2]） Oracle 共有プール（ライブラリキャッシュ） ◆SQL4 →SQLID: jkl UPDATE … SET( col1=:col1, col2=:col2 …) :col1 ←‘AAA’:VARCHAR2型 :col2 ← 123: NUMBER型 ・・・ サーバプロセス #2 #n SQL4 (sqlid:jkl) #1 ・・・ サーバプロセス 遅延 ②対応する親カーソルがあれば、再 利用可能な子カーソルを探す（カー ソル数が多いと時間がかかる） ④ハードパース中は別トランザク ションからの当該SQLはmutex待 ちでブロックされ、性能遅延する ①SQLを解析し、対応する親 カーソルがなければ、親カーソ ルと子カーソルを１つずつ作成 #n+1 ③再利用可能な子カーソルがなけれ ば、ハードパースして子カーソルを追加 作成。この際に排他ロックを獲得 （mutex X) ◆SQL4 →SQLID: jkl UPDATE … SET( col1=:col1, col2=:col2 …) :col1 ←‘BBB’:VARCHAR2型 :col2 ← NULL: VARCHAR2型 ・・・ サーバプロセス サーバプロセス [2]: Troubleshooting: High Version Count Issues (Doc ID 296377.1)

© 2023 NTT DATA Corporation 7 バインドミスマッチの確認方法（１／２） • AWRレポートのSQL ordered by version count • v$sqlareaのVERSION_COUNT 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 2 20200426 00:40:35 UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 SQL ordered by Version Count DB/Inst: X/x1 Snaps: 17039-17041 Count Executions SQL Id -------- ------------ ------------- 100 N/A abc PDB: XXXDB UPDATE … 特定の時間帯で子カーソル（COUNT） が多いSQLを特定できる ※STATSPACKでも確認できる SQLID毎に子カーソル数 （version count）を確認できる

© 2023 NTT DATA Corporation 8 バインドミスマッチの確認方法（２／２） • v$sql_shared_cursorのREASON • v$sql_bind_captureのDATATYPE_STRING等 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 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 039Bind mismatch(8)139Bind mismatch(8)

© 2023 NTT DATA Corporation 9 バインドミスマッチの発生例 • INSERT処理の遅延例。STATSPACKにカーソルの排他制御待ちを表す待機イベントが顕著に発生 • INSERT文に50個以上のNUMBER型カラム • NUMBER型とVARCHAR2型でバインドミスマッチ発生 • 原因はJavaのAP（MyBatis）でnull値の型を指定していなかった（null値のときにVARCHAR2型バインド） • sqlMap.xmlにjdbcTypeを指定することで対処 0 5,000 10,000 15,000 20,000 25,000 0 2 4 6 8 10 13 15 17 19 21 23 1 3 5 7 9 11 14 16 18 20 22 0 2 4 6 8 10 13 15 17 19 21 23 1 3 5 7 9 11 14 16 18 20 22 28 29 30 31 12 2016 LP32 - SQL*Net more data to client LP32 - log file parallel write LP32 - library cache: mutex X LP32 - kksfbc child completion LP32 - ges lms sync during dynamic remastering a LP32 - gc cr grant 2-way LP32 - Disk file Mirror Read LP32 - db file sequential read LP32 - cursor: pin S wait on X LP32 - cursor: mutex X LP32 - cursor: mutex S LP32 - CPU time LP32 - control file sequential read LP32 - control file parallel write INSTANCE_NAME Event B_Y B_MO B_D B_H 31 0 5,000 10,000 15,000 20,000 25,000 0 2 4 6 8 10 13 15 17 19 21 23 1 3 5 7 9 11 14 16 18 20 22 0 2 4 6 8 10 13 15 17 19 21 23 1 3 5 7 9 11 14 16 18 20 22 28 29 30 31 12 2016 LP32 - SQL*Net more data to client LP32 - log file parallel write LP32 - library cache: mutex X LP32 - kksfbc child completion LP32 - ges lms sync during dynamic remastering a LP32 - gc cr grant 2-way LP32 - Disk file Mirror Read LP32 - db file sequential read LP32 - cursor: pin S wait on X LP32 - cursor: mutex X LP32 - cursor: mutex S LP32 - CPU time LP32 - control file sequential read LP32 - control file parallel write INSTANCE_NAME Event B_Y B_MO B_D B_H 31 STATSPACKレポートのTop5待機イベントの推移 Cursor: mutex X Cursor: mutex S CPU INSERT INTO XXX (…) VALUES( #ID#, #COL1#, #COL2#, … #COL50#) INSERT INTO XXX (…) VALUES( #ID#, #COL1:NUMERIC#, #COL2:NUMERIC#, … #COL50:NUMERIC#) jdbcTypeで明示的に型 を指定 ◆sqlMap.xmlの記述イメージ 50カラムでNULL値の場 合にバインドミスマッチ

© 2023 NTT DATA Corporation 10 カラム数とバインドミスマッチによる子カーソル数の関係 • バインドミスマッチが発生するカラム数が増加すると、爆発的に子カーソルが増加する • テーブルのnカラムでバインドミスマッチが発生 → 子カーソルが2^n個発生する可能性がある • 先の例だと2^50=~1000兆（※） 1カラムでバインドミスマッチ → 子カーソルが2個 COL1:NUMBER COL1:VARCHAR2 2カラムでバインドミスマッチ → 子カーソルが4個 COL1:NUMBER、COL2:NUMBER COL1:VARCHAR2、COL2:NUMBER COL1:NUMBER 、COL2:VARCHAR2 COL1:VARCHAR2、COL2:VARCHAR2 10カラムでバインドミスマッチ → 子カーソルが2^10＝1024個! （※）実際にOracleで作成可能な子カーソル数には限界があり_cursor_obsolete_thresholdで制御されています（11gR2では1024、19cでは8192等）

© 2023 NTT DATA Corporation 11 バインドミスマッチの対処方法 • AP改修（実害なければ経過観察） • 子カーソルの削減は有効な対処 • オン中なら業務影響が局所化できる案２のカーソルフラッシュがオススメ 影響 内容 対処方法 項番 • 全SQLをフラッシュするためSQLの特定は不要 • 共有プールが大きいと処理に時間がかかる可能性が ある • 全SQLに排他ロックがかかるため業務影響が大きい 共有プールをフラッシュし全SQLをフラッシュする ※alter system flush shared_pool 共有プールのフラッシュ １ • 局所的な対処で業務影響なし（利用していない子 カーソルのみパージされオンライン中に実施可能） • 対象SQLIDが特定できる場合のみ可能 特定のカーソルのみをフラッシュ ※dbms_shared_pool.purge カーソルフラッシュ ２ • 切替ノードが多く、切り替えタイミングの調整が困難 APサーバからDB接続先をRACの待機系へ切り 替え DB系切り替え ３ select version_count ver_cnt,sql_id,address,hash_value,substr(sql_text,1,40) sql_text from v$sqlarea where sql_id =‘0hrt1s7pkz2qf’; VER_CNT SQL_ID ADDRESS HASH_VALUE SQL_TEXT ------- --------------- ---------------- ---------- ------------------------------- 291 0hrt1s7pkz2qf 00000011DAE9FC38 3374573111 UPDATE … ★上記コマンドの実行結果からADDRESSとHASH_VALUEを確認し、コマンドを実行する。 exec sys.dbms_shared_pool.purge('

,','C'); 例）exec sys.dbms_shared_pool.purge('00000011DAE9FC38,3374573111','C');

© 2023 NTT DATA Corporation 12 AP開発での考慮点 • コーディング規約の拡充と、単体試験で確認するプロセスを追加する • DBのカラム型に対し、SQLのバインド型が適切であることを確認（v$sql_bind_capture） 歯止め策 確認観点 工程 Java(JDBC)コーディング規約に、SQLをバインドする際の注意点として 下記内容を追加する ①DBのカラム型と同じ型でバインドすること ※DATE型にTIMESTAMP型でバインドしない ②値がある場合とNULLの場合いずれも同じ型でバインドすること DBのカラム型と、SQLのバインド型 が同じとなる設計であること 機能設計・コーディング 単体試験前に左記観点を追加し、実環境にて実際にDB側のバインド型 を確認し、設計通りの結果が得られていることを確認する DBのカラム型と、SQLのバインド型 が実機上同じとなること 単体試験 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 1 20200419 21:46:29 UPDATE /* BINDTEST */ emp SET c2 = :1 , c3 = :2 , c4=:3 , c5=:4 WHERE c1 = :5 SQL> select sql_id, child_number,position,datatype_string,precision,scale,max_length from v$sql_bind_capture where sql_id= ‘0hrt1s7pkz2qf '; SQL_ID CHILD_NUMBER POSITION DATATYPE_STRING PRECISION SCALE MAX_LENGTH ------------- ------------ ---------- -------------------- ---------- ---------- ---------- 0hrt1s7pkz2qf 0 1 VARCHAR2(128) 128 0hrt1s7pkz2qf 0 2 VARCHAR2(128) 128 0hrt1s7pkz2qf 0 3 DATE★ 7 0hrt1s7pkz2qf 0 4 DATE★ 7 0hrt1s7pkz2qf 0 5 NUMBER 22 DATE型カラムに対して、DATE型で バインドされていることを v$sql_bind_captureで確認

© 2023 NTT DATA Corporation 13 Javaでの留意点 • MyBatisのMapper XMLファイルにjdbcTypeを指定すること • 値としてnullが入る場合はJDBC型の指定は必須（参考[3]） • 特にバインドするカラム数の多いSQLは要注意 • JDBCについてはsetNULLとDATE型バインドに注意すること • setNullに適切な型を指定すること（java.sql.Types.NULLだとVARCHAR2型でバインドされてしまう） • DATE型カラムに対してはsetDateでバインドすること(setDateで時分秒も入る) （参考[４]） ◆悪いDATE型バインドのjavaコード例 if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.NULL); else ps.setTimestamp(col_pos, v); update EMP set SAL = #{sal} where EMPNO = #{empno} update EMP set SAL = #{sal, jdbcType=NUMERIC} where EMPNO = #{empno} [3] Mapper XML ファイル: https://mybatis.org/mybatis-3/ja/sqlmap-xml.html [4] Oracle JDBC Driver 11.2.0.4 から移行した後、JDBC 12c では java.sql.Date に Timestamp が追加される (Doc ID 948974.1) ◆良いDATE型バインドのjavaコード例 if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ps.setDate(col_pos, v);

© 2023 NTT DATA Corporation 14 まとめ • バインドミスマッチの問題と対処方法、AP開発における留意点をお話しました • バインドミスマッチの性能問題は、対処が困難なため、AP開発時に考慮が必要です • DBAだけでなく、Oracleを使うJava技術者にもこの事実を広く知っていただきたい 【参考】:オラクルデータベースの技術メモ ～バインドミスマッチの罪 https://tech-oracle.blog.ss-blog.jp/2020-04-27

© 2023 NTT DATA Corporation

© 2023 NTT DATA Corporation 16 【参考】Oracleデータ型とjdbcTypeの関係 • JDBC開発者ガイド（参考[4]）を参照 標準Java型 JDBC型コード (jdbcType) SQLデータ型 (オラクルデータ型) java.lang.String java.sql.Types.CHAR CHAR java.lang.String java.sql.Types.VARCHAR VARCHAR2 java.math.BigDecimal java.sql.Types.NUMERIC NUMBER java.sql.Date java.sql.Types.DATE DATE javal.sql.Timestamp java.sql.Types.TIMESTAMP TIMESTAMP Oracleデータ型とjdbcTypeの対応（例） [4]JDBC開発者ガイド 11 Oracleデータへのアクセスと操作 https://docs.oracle.com/cd/F19136_01/jjdbc/accessing-and-manipulating-Oracle-data.html#GUID-02FC3ADD-B0C6-4B9D-9320-0AB722906B05