Sunday 15 April 2012

Bug 9689310 Excessive child cursors / high VERSION_COUNT / OERI:17059 due to bind mismatch

We have observed High version Count for the below queries in ORCL database

Data Collected:

SQL_ID        MODULE                                                           VERSION_COUNT

92bxfrxwu51v5 oracl@orcl01.xyz.in (TNS V1-V3)                                     17
087g1afdgd4fh oracl@orcl01.xyz.in (TNS V1-V3)                                     12
carh6s6nmqh5w oracl@orcl01.xyz.in (TNS V1-V3)                                     25
b0kxk28kwfhn9 otrans@picggx02.xyz.in (TNS V1-V3)                                     46
ftuhvswznfpv8 otrans@picggx02.xyz.in (TNS V1-V3)                                     17


SqlText ( b0kxk28kwfhn9)
INSERT INTO M_CACHE01_ITEM( MESG_TYP,SITE_ID,JOB_NO,JOB_DT,INV_S
ERNO,ITEM_NO,SCHEME_CD,RITC_CODE,DESC_GD01,DESC_GD02,DESC_GD03,Q
TY_UNITS,QUANTITY,ITEM_RATE,UNIT_OF_RATE,NO_OF_UNITS,PMV,NOTN_NO
,THIRD_PARTY,FILE_NAME,UNIQUE_ID) VALUES (:1,:2,:3,TO_DATE(:4,'Y
YYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:1
6,:17,:18,:19,:20,:21)


Cause:
V$SQL_BIND_METADATA showed that the bind lengths are different among cursors for the same bind variables


Impact:
 High amount of latch: library cache wait event is observed in the database due to high version count


Solution:
Apply Patch  9689310 

No comments:

Post a Comment