Re: Weird database hanging

  • From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
  • To: don@xxxxxxxxx
  • Date: Thu, 3 Jan 2008 09:01:12 -0800

On 1/3/08, Don Seiler <don@xxxxxxxxx> 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
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l


Other related posts: