Re: Weird database hanging

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_at_structureddata.org> wrote:
> On 1/3/08, Don Seiler <don_at_seiler.us> 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
--
http://www.freelists.org/webpage/oracle-l