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.
Here are my earlier rants on the same problem: 9b4m3fr3vf9kn and an4593dzvqr4v, SQLT Data Explosion.
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.
– Bobby
