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