Re: Weird database hanging

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
--
http://www.freelists.org/webpage/oracle-l