Re: Weird database hanging
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.
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.
> Greg Rahn
-- Don Seiler http://seilerwerks.wordpress.com ultimate: http://www.mufc.us -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 03 2008 - 11:20:03 CST