More work on parse time bug

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

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply