© 2023 NTT DATA Corporation Sin of Bind Mismatch in Oracle JPOUG Tech Talk Night #7 Sep. 13, 2023 NTT data corporation Kazuhiro Takahashi

© 2023 NTT DATA Corporation 2 Introduction • Database specialist • Oracle, Exadata, OCI • Community activities as an Oracle ACE MOSC (My Oracle Support Community) Community activities

© 2023 NTT DATA Corporation 3 What we would like to talk today • How Oracle cursor sharing works • Bind mismatch issues, their analysis, and how to deal with them • Points to keep in mind in AP development

© 2023 NTT DATA Corporation 4 How Oracle cursor sharing works • When SQL is executed, a cursor is created for each SQL hash value (SQLID) on the shared pool • If the SQL is the same, the cursor is reused, avoiding expensive SQL parsing (hard parse) • Cursors are not reused unless the SQL is exactly the same, so bind variables are used to avoid hard parse Oracle Shared pool (Library cache) Child Cursor (Execution Plan) Parent Cursor ・・・ ◆SQL1 →SQLID: abc SELECT count(*) FROM EMP WHERE SAL < 10000; Server Process 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; Bind variables :b=10000 :b=20000 :b=30000 … With bound variables, the SQLIDs will match even if the variables have different values, so the cursor can be reused If SQLs are not the same, the SQLIDs are different and hard parse will run

© 2023 NTT DATA Corporation 5 Using bind variables is not enough • Hard parse may occur even when using bind variables • There can be many reasons, depending on the semantics or the environment, e.g. • Mismatch of schema • Mismatch of Existing statistics • Mismatch of Language settings (NLS_LANG) • Mismatch of OPTIMIZER_MODE (FIRST_ROWS/ALL_ROWS) • … • One of the reasons is the "bind mismatch" [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 ★ ★Even if a hard parsed SQLID already exists in the library cache, it will not be reused if the cursor is deemed unshared due to semantics or environment

© 2023 NTT DATA Corporation 6 Performance problem due to bind mismatch issue • Cursors are not shared due to type mismatch of bind variable • hard parse may occur even when using bind variables • Not only waiting for mutexes due to exclusive locks, but also increasing the number of child cursors which consumes memory and CPU • Excessive number of child cursors may cause performance degradation (commonly known as HIGH VERSION COUNT issues [2] ) Oracle Shared pool (Library cache) ◆SQL4 ->SQLID: jkl UPDATE … SET( col1=:col1, col2=:col2 …) :col1 <-‘AAA’:VARCHAR2 :col2 <- 123: NUMBER … Server Process #2 #n SQL4 (sqlid:jkl) #1 ・・・ Delay (2) If there is a corresponding parent cursor, search for a reusable child cursor (if there are many cursors, it will scan them) (4) During the hard parse, the same SQL from another transaction is blocked by the mutex, resulting in performance delay. (1) Parses SQL and creates one parent cursor and one child cursor if there is no corresponding parent cursor #n+1 (3) If there are no reusable child cursors, hard parse and create an additional child cursor. At this time, an exclusive lock is acquired (mutex X) ◆SQL4 ->SQLID: jkl UPDATE … SET( col1=:col1, col2=:col2 …) :col1 <-‘BBB’:VARCHAR2 :col2 <- NULL: VARCHAR2 … Server Process [2]: Troubleshooting: High Version Count Issues (Doc ID 296377.1)

© 2023 NTT DATA Corporation 7 How to check bind mismatches • SQL ordered by version count section in AWR report • VERSION_COUNT in v$sqlarea 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 … Can identify SQL with many child cursors (COUNT) in a specific time period. Can also be checked with STATSPACK Number of child cursors (version count) can be checked for each SQLID

© 2023 NTT DATA Corporation 8 How to check bind mismatches • REASON in v$sql_shared_cursor • DATATYPE_STRING in v$sql_bind_capture 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 An example of bind mismatch issue • Example of INSERT process delay, significant waiting events occur for cursor mutex X/S • More than 50 NUMBER type columns in INSERT statement • Bind mismatch occurs between NUMBER and VARCHAR2 types • The cause was not specifying the null value type in Java AP (MyBatis) (When the value is null, it is bound with VARCHAR2 type) • Addressed by specifying jdbcType in sqlMap.xml 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 Top 5 Wait Events in STATSPACK Report 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#) explicitly specify jdbcType for each column ◆sqlMap.xml Binding mismatch in 50 columns

© 2023 NTT DATA Corporation 10 Relationship between the number of columns and child cursors due to bind mismatch • The number of child cursors explodes as the number of columns of bind mismatch increases • Bind mismatch of N columns would create 2^n child cursors • 50 columns make 2^50=~1000 trillion (*) 1 column bind mismatch make 2 child cursors COL1：NUMBER COL1：VARCHAR2 2 column bind mismatch make 4 child cursors COL1：NUMBER、COL2：NUMBER COL1：VARCHAR2、COL2：NUMBER COL1：NUMBER 、COL2：VARCHAR2 COL1：VARCHAR2、COL2：VARCHAR2 2 column bind mismatch make 2^10=1024 child cursors! （※） Actually, there is a limit to the number of child cursors that can be created in Oracle, which is controlled by _cursor_obsolete_threshold (1,024 in 11gR2, 8,192 in 19c)

© 2023 NTT DATA Corporation 11 How to deal with binding mismatches • Fix the application (but usually not an option) • Reduction of child cursors is an effective response • Cursor flash is recommended during online time No. Counter-measure Description Impact １ Flush shared pool Flush all cursors by shared pool flush alter system flush shared_pool • No need to identify SQL as all SQLs are flushed • If the shared pool is large, the process may take a long time • Exclusive lock is applied to all SQLs during flusing ２ cursor flash Flash only specific cursors by dbms_shared_pool.purge • No business impact (only unused child cursors are purged and can be performed while online) • Only possible if the target SQLID can be identified. ３ DB node switchover Switching DB connection from AP server to other node of RAC • It may be difficult to switch all ap servers and adjust switching timing 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 … exec sys.dbms_shared_pool.purge('

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

© 2023 NTT DATA Corporation 12 Considerations in AP development • Add Java (JDBC) coding conventions and a process to be verified in unit testing • Ensure SQL bind type is appropriate for DB column type (v$sql_bind_capture) Development phase Viewpoints to be checked Counter-measure Functional design and coding DB column type and SQL bind type must be the same In Java (JDBC) Coding Conventions (1)Bind with the same type as the DB column type e.g., do not bind to DATE type with TIMESTAMP type (2) Bind with the same type even when null values Unit test Confirm the above in actual environment Check the actual bind type on the DB in the actual environment, and confirm that the results are in accordance with the design 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 Confirm with v$sql_bind_capture that a column of type DATE is bound with type DATE Confirm with v$sqlarea that VERSION_COUNT is 1

© 2023 NTT DATA Corporation 13 Points to keep in mind in Java • Specify jdbcType in the MyBatis Mapper XML file • JDBC type must be specified if null is set as the value [3] • Be especially careful with SQL that has a large number of columns to bind • For JDBC, be careful about setNULL and DATE type bindings • Specifying the appropriate type for setNull (java.sql.Types.NULL will bind VARCHAR2） • Bind with setDate for DATE type columns (setDate will set hours, minutes, and seconds) [4] Bad coding 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 file: https://mybatis.org/mybatis-3/sqlmap-xml.html [4] JDBC 12c Adds Timestamp to java.sql.Date After Upgrading From 11.2.0.4 (Doc ID 1944845.1) Good coding if (Objects.isNull(v)) ps.setNull(col_pos,java.sql.Types.DATE); else ps.setDate(col_pos, v);

© 2023 NTT DATA Corporation 14 Summary • We talked about bind mismatch issues, how to deal with them, and points to keep in mind during AP development • The performance problem of bind mismatch is difficult to deal with and needs to be considered during AP development • Not only DBAs but also Java developers using Oracle should be widely aware of this fact [5]: a technical memo of oracle database ~ Sin of bind mismatch (Japanese) https://tech-oracle.blog.ss-blog.jp/2020-04-27

© 2023 NTT DATA Corporation

© 2023 NTT DATA Corporation 16 Relationship between Oracle data type and jdbcType • JDBC Developer's Guide and Reference SQL data type (Oracle data type) JDBC Type Code (jdbcType) Standard Java Type CHAR java.sql.Types.CHAR java.lang.String VARCHAR2 java.sql.Types.VARCHAR java.lang.String NUMBER java.sql.Types.NUMERIC java.math.BigDecimal DATE java.sql.Types.DATE java.sql.Date TIMESTAMP java.sql.Types.TIMESTAMP javal.sql.Timestamp Correspondence between Oracle data type and jdbcType (example) [6] JDBC Developer's Guide and Reference, 11 Accessing and Manipulating Oracle Data https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/accessing-and-manipulating-Oracle-data.html#GUID- 02FC3ADD-B0C6-4B9D-9320-0AB722906B05