Re: Weird database hanging

  • From: "Don Seiler" <don@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 3 Jan 2008 10:13:31 -0600

On Oct 29, 2007 9:05 AM, Don Seiler <don@xxxxxxxxx> 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'
group by sql_id;

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

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

Other related posts: