Re: Weird database hanging

From: Don Seiler <>
Date: Thu, 3 Jan 2008 11:20:03 -0600
Message-ID: <>

Thanks for the info, Greg. Yeah this table is partitioned, and I've been meaning to revisit the queries we use for them, wrt bind variables and partitioned tables.

I forgot to mention that the plan is to just set parallel_max_servers to 0 for now. The fix from Oracle is a one-off patch that can only be applied to, and we're still on It *is* also fixed in the not-yet-released patchset.

I'm also picturing Doug Burns disappointingly shaking his head at my willy-nilly use of PQs, slowly sliding the brass knuckles on.


On Jan 3, 2008 11:01 AM, Greg Rahn <> wrote:
> On 1/3/08, Don Seiler <> wrote:
> > 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.
> Using PQ with binds can have other adverse effects, specifically if
> the partition key is not provided as a literal. When the partition
> key is a bind, the resulting plan will be a KEY-KEY plan (for
> pstart/pstop) because w/o a literal value the optimizer can not tell
> if there is any partition elimination since the literal value is not
> provided at parse time. This often times results in a "wost case"
> assumption, thus is it possible to have different plans even when the
> bind and literal statements use the same values.
> I would speculate that the overhead of parsing literals when using PQ
> is minimal compared to the side effects it is causing (due to the bug)
> and the potential of suboptimal plans. I personally would never mix
> the two.
> --
> Regards,
> Greg Rahn

Don Seiler
Received on Thu Jan 03 2008 - 11:20:03 CST

Original text of this message