Oracle-07445 [kgghash]: Oracle BUG causes the process to be killed when updating the LOB Field

Oracle-07445 [kgghash]: Oracle BUG causes the process to be killed when updating the LOB Field

Symptom:
The user reports that a DB connection is accidentally killed. The following information is found in the alert Log during troubleshooting:
Mon Apr 22 14:49:58 2013
Exception [type: SIGSEGV, Address not mapped to object] [ADDR: 0x2B1733615000] [PC: 0x777EE03, kgghash () + 367]
Errors in file/oracle/app/11gR1/diag/rdbms/portal/trace/portal2_ora_19188.trc (incident = 186505 ):
ORA-07445: Exception error: core dump [kgghash () + 367] [SIGSEGV] [ADDR: 0x2B1733615000] [PC: 0x777EE03] [Address not mapped to object] [] <
Incident details in:/oracle/app/11gR1/diag/rdbms/portal/incident/incdir_181_5/portal2_ora_19188_i181_5.trc
Mon Apr 22 14:55:08 2013
Thread 2 advanced to log sequence 4042
Current log #12 seq #4042 mem #0: + portalDG/portal/onlinelog/group_12.292.796513605
Mon Apr 22 14:55:08 2013
SUCCESS: diskgroup ARCHDG was mounted
Mon Apr 22 14:55:14 2013
SUCCESS: diskgroup ARCHDG was dismounted
Mon Apr 22 15:00:01 2013
Process 0x0x2147ed628 appears to be hung while dumping <== Process killed
Current time = 1955749344, process death time = 1955689185 interval = 60000
Attempting to kill process 0x0x2147ed628 with OS pid = 19188
OSD kill succeeded for process 0x2147ed628
At the same time, the alert Log in the segment does have the KILL process information, and before the process is killed, the process has a ORA-07445 error reported, it is estimated that the Oracle BUG.
View portal2_ora_19188_i181_5.trc log information:

Dump file/oracle/app/11gR1/diag/rdbms/portal/incident/incdir_181_5/portal2_ora_19188_i181_5.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
And Real Application Testing options
ORACLE_HOME =/oracle/app/11gR1/db
System name: Linux
Node name: PORTAL02
Release: 2.6.18-194. el5
Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine: x86_64
Instance name: portal 2
Redo thread mounted by this instance: 2
Oracle process number: 213
Unix process pid: 19188, image: oracle @ PORTAL02


* ** 2013-04-22 14:49:58. 322
* ** Session id: (1403.45735) 14:49:58. 322
* ** Client id: () 14:49:58. 322
* ** Service name: (portal) 14:49:58. 322
* ** Module name :( JDBC Thin Client) 14:49:58. 322
* ** Action name :() 14:49:58. 322
 
Dump continued from file:/oracle/app/11gR1/diag/rdbms/portal/portal2o3/trace/portal2_ora_19188.trc
ORA-07445: Exception error: core dump [kgghash () + 367] [SIGSEGV] [ADDR: 0x2B1733615000] [PC: 0x777EE03] [Address not mapped to object] []

========= Dump for incident 186505 (ORA 7445 [kgghash () + 367]) ======
----- Beginning of Customized Incident Dump (s )-----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR: 0x2B1733615000] [PC: 0x777EE03, kgghash () + 367]
Registers:
% Rax: 0x000000001605399e % rbx: 0 x 0000000000000000% rcx: 0x000000009df422ed
% Rdx: 0x00000000895e2309% rdi: 0x00002b1733615000% rsi: 0x00000000096d62a0
% Rsp: 0x00007fffb42e8a20% rbp: 0x00007fffb42e8a30% r8: 0x0000000000000000
% R9: 0x00000000c55f2e0a % r10: 0x000000000777ee03% r11: 0x000000002eeb4000
% R12: 0 x 0000000219382310% r13: 0x0000000125d120e0% r14: 0x00002b1733541e50
% R15: 0x0000000160c68fd0% rip: 0x000000000777ee03% efl: 0x0000000000010293
Kgghash () + 356 (0x777edf8) movzbl 0xa (% rdi), % esi
Kgghash () + 360 (0x777edfc) shl $0x10, % esi
Kgghash () + 363 (0x777edff) add % esi, % eax
Kgghash () + 365 (0x777ee01) jmp 0x777edb7
> Kgghash () + 367 (0x777ee03) movzbl (% rdi), % esi
Kgghash () + 370 (0x777ee06) add % esi, % edx
Kgghash () + 372 (0x777ee08) jmp 0x777ed19
Kgghash () + 377 (0x777ee0d) add (% rdi), % edx
Kgghash () + 379 (0x777ee0f) add 0x4 (% rdi), % ecx

* ** 2013-04-22 14:49:58. 339
----- Current SQL Statement for this session (SQL _id = bb8bnr20h93mk )-----
Update oa9.sys _ process set fd_detail =: 1, fd_descriptor =: 2 where fd_id =: 3

----- Call Stack Trace -----
Skdstdst <-ksedst1 <-ksedst <-dbkedDefDump <-ksedmp
<-Ssexhd <-restore_rt <-kgghash <-kkocsCreateBindSet <-kkocsInitAdaptiveCtx
<-Apadrv <-opitca <-pgosf1__kksfulltypecheck <-rpiswu2 <-kksSetBindType
<-Kksfbc <-opiexe <-kpoal8 <-opiodr <-ttcpip
<-Opitsk <-opiino <-opiodr <-opidrv <-sou2o
<-Opimai_real <-ssthrdmain <-main <-libc_start_main <-start

----------------------------------------
Cursor #35 (0x2b1732bb2c28) state = BOUND curiob = 0x2b1733541e50
Curflg = 4c fl2 = 0 par = (nil) ses = 0x214cd7880
----- Dump Cursor SQL _id = bb8bnr20h93mk xsc = 0x2b1733541e50 cur = 0x2b1732bb2c28 -----
Dump Parent Cursor SQL _id = bb8bnr20h93mk phd = 0x21b32bee8 plk = 0x21a12acb8
Sqltxt (0x21b32bee8) = update oa9.sys _ process set fd_detail =: 1, fd_descriptor =: 2 where fd_id =: 3
Hash = d464c012671d8361b5a174b881048e72
Parent = 0x1e7cda730 maxchild = 01 plk = 0x21a12acb8 ppn = n
Cursor instantiation = 0x2b1733541e50 used = 1366613397 exec_id = 0 exec = 0
Child #0 (0x218b3bb40) pcs = 0x1a2dff738
Clk = 0x21ac20ed8 ci = 0x1e737a828 pn = 0x21bd000068 ctx = 0x125d120e0
Kgsccflg = 0 llk [0x2b1733541e58, 0x2b1733541e58] idx = 0
Xscflg = 130672 fl2 = 11100008 fl3 = 2222600 fl4 = 100
----- Bind Byte Code (IN )-----
Opcode = 14 Bind Skip Long
Opcode = 2 Bind Twotask Scalar SQL In (may be out) Copy
Oacdef = 0x1a3438220 Offsi = 48, Offsi = 32
Opcode = 2 Bind Twotask Scalar SQL In (may be out) Copy
Oacdef = 0x1a3438250 Offsi = 48, Offsi = 64
Opcode = 1 Unoptimized
Null Offset
----- Bind Info (kkscoacd )-----
Bind #0
Oacdty = 01 mxl = 4001 (5018) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 03 fl2 = 1000010 frm = 01 csi = 852 siz = 4000 off = 0
Kxsbbbfp = 2b1733614060 bln = 4000 avl = 4001 flg = 25
Value = "WZ3 + "...
Bind #1
Oacdty = 01 mxl = 2000 (1620) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 03 fl2 = 1000010 frm = 01 csi = 852 siz = 2128 off = 0
Kxsbbbfp = 2b17336137d0 bln = 2000 avl = 822 flg = 05
Value = "<process parent =" oa ">
<Initial-node id = "N1" name = "" nextTargetNodeIds = "N2" parent = "oaInitNode">
<Results>
<Result priority = "0" target = "N2"/>
</Results>
</Initial-node>
<Nodes>
<Node id = "N2" name = "Submit" nextTargetNodeIds = "N4" parent = "oaDraftNode">
<Res "...
Bind #2
Oacdty = 01 mxl = 128 (64) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 03 fl2 = 1000010 frm = 01 csi = 852 siz = 0 off = 2000
Kxsbbbfp = 2b1733613fa0 bln = 128 avl = 32 flg = 01
Value = "13e3064a34e8924d0e196e044089dff5"
Frames pfr (nil) siz = 0 efr (nil) siz = 0
Cursor frame dump
Enxt: 2.0x00000060 enxt: 1.0x00000898
Pnxt: 1.0x00000060
Kxscphp = 0x2b1733606828 siz = 1008 inu = 176 nps = 96
Kxscbhp = 0x2b17335a5660 siz = 11456 inu = 10696 nps = 10512

Analysis:
ORA-07445 is often caused by ORACLE bugs and found in the document ORA-7445 [kgghash] [ID 310172.1] There are bugs that match our phenomenon:
Bug 13463131 Dump (kgghash) from bind peeking, complete content of BUG 13463131:
Bind peeking may dump in kgghash () when long character datatypes are
Being used.
The stack will typically include the following CALS:
KkocsCreateBindSet-> kgghash
<====== It is consistent with our CASE. The table oa9.sys _ process has a CLOB field, and in our CASE, the call stack is kgghash <-kkocsCreateBindSet <-kkocsInitAdaptiveCtx

The bind variable that causes the dump shows the avl value is greater
The bln (in the cursor dump in the trace ).

Bind #7
Oacdty = 96 mxl = 4001 (22518) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 01 fl2 = 1000010 frm = 01 csi = 873 siz = 4000 off = 0
Kxsbbbfp = 0e42f060 bln = 4000 avl = 4001 flg = 25
 
This situation can happen, for example, using a CLOB column and having
The client bind with CHAR.
<==== Extract the related content of our dump file. We can find that bln = 4000 avl = 4001, which complies with the document description.
Bind #0
Oacdty = 01 mxl = 4001 (5018) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 03 fl2 = 1000010 frm = 01 csi = 852 siz = 4000 off = 0
Kxsbbbfp = 2b1733614060 bln = 4000 avl = 4001 flg = 25 <
Value = "WZ3 + "...
==========================================================
Workaround
Disable bind peeking (set "_ optim_peek_user_binds" = false)
But note that this may cause changes to execution plans so
May not be practical.
<===== The solution provided by Oracle is to disable bind peeking, but the risk is that the SQL Execution Plan may change.
In addition, you can upgrade to 11.2.0.3 before applying patch 13463131.
Note: This fix supersedes fixes of bug 7352414, bug 8763922.



What is "Bind Peeking "?
 
With 9i, a new feature "Bind Peeking" was introduced to try to get around the issues associated with the selectivity of a bind variable.
During hard parsing of a query using bind variables, the binds are "peeked at" and selectivity is generated based on the bind and the underlying column statistics.
This method is mandatory but if the bind selectivity of the initial execution of a statement varied from the selectivity of subsequent executions with different sets of binds this cocould sometimes lead to plans being enerated that were not representative of the general query usage and which stored med poorly.

This feather is controlled by the hidden parameter "_ OPTIM_PEEK_USER_BINDS". the default value is 'true '.

Peeking is enabled if all of the following conditions are met:
*-Optimizer_features_enabled is greater than 8.2
*-There are user binds
*-The cursor is fully bound
*-The cursor is not remote mapped
*-The client is using V8OCI
*-It's a select or DML
*-Peeking is possible for this statement and the statement is being
* Loaded for the first time, or the original cursor was peeked and
* This cursor is being reloaded

For more details, please refer:

Document 387394.1 Query using Bind Variables is suddenly slow
Document 430208.1 Bind Peeking By Example
"_ OPTIM_PEEK_USER_BINDS" [Hidden] Reference Note [ID 201672.1]