Re: Weird database hanging

On Oct 29, 2007 9:05 AM, Don Seiler <don_at_seiler.us> wrote:
> It has been suggested that I disable mutexes by setting
> _kks_use_mutex_pin=false. At least one case of the cursor/pin errors
> going away after this. I'd like to hear what everyone else's thoughts
> are.

Just wanted to follow-up with my developments. Oracle support said not to set _kks_use_mutex_pin for now, so I didn't. It seems that we ARE being affected by Bug 4367986 - Bind peeked parallel cursors do not share. This causes the number of cursors to shoot up when parallel query and bind variables are mixed. This query was taken from my production instance just now:

select sql_id, count(*)
from v$sql_shared_cursor
where bind_peeked_pq_mismatch='Y'

SQL_ID COUNT(*)
------------- ----------

f3u64ru922snx        250
3vhyzf6fsyj84          8
ckha07wkfaf8v          2
gdnga6d26vf4g        139

SQL_ID f3u64ru922snx was as high as 489 yesterday when I checked. It is a query that uses 4 bind variables in the where clause and no literals. I was able to duplicate the cursor count going up in one of my development instances by raising my parallel_max_servers to the prod value of 80 (dev was at 16) and telling my developers to "PUNCH THAT S#!+" a la "Hot Fuzz". We didn't get to a point of choking the instance though, perhaps due to a lack of concurrent user OLTP activity.

So I don't have an open-and-shut case for this bug being the cause, but this is the most promising lead I have.

-- 
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l