High Cpu Time Caused by Insert Values Statement Due To Bind Mismatch (Doc ID 2702429.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• INSERT VALUES statement from JDBC thin client caused high CPU time in EXEC phase, and there was very high Misses in library cache during execute:
  
  
  *** CLIENT DRIVER:(jdbcthin) 2020-06-18T14:20:18.341792+09:00
  *** CONTAINER ID:(4) 2020-06-18T14:20:18.341798+09:00
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse    37158     13.97      16.21          0          0          0           0
  Execute  37160    836.04     850.51          0       8158     322100       74471
  Fetch        0      0.00       0.00          0          0          0           0
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total    74318    850.02     866.72          0       8158     322100       74471
  
  Misses in library cache during parse: 0
  Misses in library cache during execute: 15364 *<<<<<<<<<<----------
  Optimizer mode: ALL_ROWS
  Parsing user id: 106
  Number of plan statistics captured: 37053
  
  Rows (1st) Rows (avg) Rows (max)  Row Source Operation
  ---------- ---------- ----------  ---------------------------------------------------
          0          0          0  LOAD TABLE CONVENTIONAL  <TABLE_NAME> (cr=0 pr=0 pw=0 time=1 us)
• Trace shows high version counts of child cursors created by "Bind mismatch(22)" or "Bind mismatch(8)":
  
  
  $ grep "Bind mismatch(22)" <TRACE_FILE_NAME>.trc|wc -l
  1023
  $ grep "Bind mismatch(8)" <TRACE_FILE_NAME>.trc|wc -l
  1078
  
  
  Trace abstract:
  
  
   ChildNode:  ChildNumber=3526 ID=39 reason=Bind mismatch(22) size=4x4 bind_position=17 original_oacflg=3 original_oacmxl=128 upgradeable_new_oacmxl=2000
   ChildNode:  ChildNumber=3101 ID=39 reason=Bind mismatch(22) size=4x4 bind_position=13 original_oacflg=3 original_oacmxl=32 upgradeable_new_oacmxl=128
   ChildNode:  ChildNumber=3101 ID=39 reason=Bind mismatch(8) size=4x4 bind_position=45 original_oacflg=3 original_oacdty=2 new_oacdty=1
   ChildNode:  ChildNumber=2030 ID=39 reason=Bind mismatch(22) size=4x4 bind_position=13 original_oacflg=3 original_oacmxl=32 upgradeable_new_oacmxl=128
   ChildNode:  ChildNumber=2030 ID=39 reason=Bind mismatch(8) size=4x4 bind_position=23 original_oacflg=3 original_oacdty=2 new_oacdty=1
   ChildNode:  ChildNumber=3523 ID=39 reason=Bind mismatch(8) size=4x4 bind_position=24 original_oacflg=3 original_oacdty=1 new_oacdty=2　
   ChildNode:  ChildNumber=3523 ID=39 reason=Bind mismatch(22) size=4x4 bind_position=17 original_oacflg=3 original_oacmxl=128 upgradeable_new_oacmxl=2000
• Trace shows the data type of the same bind variable changed between NULL and other values:
  
  
  [Previous Execution]
  Bind#25
   oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00 *<<<<<<<<<-- oacdty=01 means Char
   oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=1464
   kxsbbbfp=7f3ff7ce8260  bln=32  avl=00  flg=01
  
  [Current Execution]
  Bind#25
   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 *<<<<<<<<<-- oacdty=02 means Number
   oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=1456
   kxsbbbfp=7f3ff7ce8230  bln=22  avl=04  flg=01
   value=627115
  
   ChildNode:  ChildNumber=3523 ID=39 reason=Bind mismatch(8) size=4x4 bind_position=24 original_oacflg=3 original_oacdty=1 new_oacdty=2 <<<< Data type changed from Char to Number
• Trace shows the character bind buffer length of the same bind variable changed among executions:
  
  
  [Previous Execution]
  Bind#17
   oacdty=01 mxl=128(108) mxlc=00 mal=00 scl=00 pre=00
   oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=928
   kxsbbbfp=7f3ff7ce8010  bln=128  avl=81  flg=01
   value="<Very short bind value>"
  
  [Current Execution]
  Bind#17
   oacdty=01 mxl=128(120) mxlc=00 mal=00 scl=00 pre=00
   oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=928
   kxsbbbfp=7f3ff7ce8018  bln=128  avl=90  flg=01
   value="<Very very long bind value>"
   
   ChildNode:  ChildNumber=3523 ID=39 reason=Bind mismatch(22) size=4x4 bind_position=17 original_oacflg=3 original_oacmxl=128 upgradeable_new_oacmxl=2000 <<<< character bind buffer length changed from 128 to 2000

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.