I wasn’t sure if people would find a post on a bug that interesting. If you don’t have the same bug I’m not sure what you would get out of it. But, it is what I’m working on.
So, I worked with Oracle support and they became convinced we were hitting this base bug:
16864042: KKPOSAGEPROPSCBK() MAY CAUSE UNNECESSARY EXADATA PERFORMANCE REGRESSION
It made sense to me because this was the base bug for another bug:
Bug 16694856 : EXADATA: SUBPARTITION WITH ‘LIBRARY CACHE LOCK’ ON RAC + INDEX OPERATION
16694856 has similar symptoms to what we have in production namely a lot of 9b4m3fr3vf9kn and an4593dzvqr4v internal query executions. Anyway, Oracle development looked at the information I uploaded and concluded we had indeed hit bug 16864042 and produced a back ported patch for our release which is great.
So, I’ve applied the patch on dev and qa but I can’t reproduce the problem anywhere but prod so I won’t really know if the patch helps until it goes all the way through. I built this script as a test of the bug:
drop table executions_before; create table executions_before as select sum(executions) before_executions from v$sql where sql_id = 'an4593dzvqr4v'; explain plan for select count(*) from OURPROBLEMTABLE; select after_executions-before_executions from (select sum(executions) after_executions from v$sql where sql_id = 'an4593dzvqr4v'),executions_before;
On dev and qa before and after the patch it produces the same results. It returns a value > 0 once and then afterwards looks like this every time it is run:
AFTER_EXECUTIONS-BEFORE_EXECUTIONS ---------------------------------- 0
But on prod every time it is run it returns a value > 0:
AFTER_EXECUTIONS-BEFORE_EXECUTIONS ---------------------------------- 1224
So, I’m hoping that after I apply the patch prod will start behaving like dev and qa and it will start returning 0 after the first run like in dev and qa. At least that would be a quick test of the prod patch once it is in.
Anyway, just thought I would share my pain. Not sure what to get out of this except that you can’t always duplicate a bug you are seeing on prod on a dev/test system.