Re: Weird database hanging

  • From: "Don Seiler" <don@xxxxxxxxx>
  • To: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Thu, 3 Jan 2008 11:20:03 -0600

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 10.2.0.3, and we're still on 10.2.0.2.  It *is* also fixed
in the not-yet-released 10.2.0.4 patchset.

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

Don.

On Jan 3, 2008 11:01 AM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
> 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
>



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


Other related posts: