Thanks for the question, shi.
Asked: August 27, 2018 - 8:09 am UTC
Last updated: August 28, 2018 - 6:32 am UTC
Version: 9i
Viewed 1000+ times
You Asked
Hi,
Recently I faced a problem , my 9i database had a latch free wait event on statpack report
I found a SQL consume almost full of my shared pool
All of the hash value are same ,So I query v$sql_shared_cursor view to find out why
Then I got INST_DRTLD_MISMATCH column's value is Y which mens:
Insert direct load does not match the existing child cursor
the SQL is :
INSERT INTO FWCATNS_STG_SWIN@DEVFWCP (CONTROLLOT, SWIN, SWINDESCRIPTION, STAGE) SELECT TRIM(A.CONTROL), TRIM(SWINCODE), TRIM(SWINDESC), TRIM(SWINSTAGE) FROM FWCATNS_STG_CPSWIN_SAP A, (SELECT CONTROL, MAX(SAPTIMESTAMP) AS SAPTIMESTAMP FROM FWCATNS_STG_CPSWIN_SAP WHERE FLAG = 'M' GROUP BY CONTROL) B WHERE A.CONTROL = B.CONTROL AND A.SAPTIMESTAMP = B.SAPTIMESTAMP AND A.FLAG = 'M'
Then I got confused, there is no /*+appned*/ hint on it
after searched,I got no idea about that, please give me advise or direction
Thanks
and Connor said...
That is most likely a bug. Potential candidates are:
Bug 10111765 High version count on queries with NOAPPEND hint due to INST_DRTLD_MISMATCH
Bug 21293600 - cursor sharing failed with inst_drtld_mismatch when using xa
Bug 9944129 - SQL not shared due to INST_DRTLD_MISMATCH with global transaction
Bug 9380377 INSERT / MERGE child cursor leak due to INST_DRTLD_MISMATCH
But 9i ... really ? Time to upgrade man...
Is this answer out of date? If it is, please let us know via a Comment