Re: PARALLEL QUERY
- From: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sat, 27 Oct 2007 22:23:57 +0530
Hi Jonathan,
Thanks a lot for your valuable input. But is the parallel hint merely
reduces the effective execution cost of the query as against the other hints
like index hint where the optimizer does not bother about the cost of the
resultant execution plan and biased to use the index mentioned in the hint.
Does this mean we should ideally NOT provide parallel hint as it merely
reduces the cost for determining the most efficient execution plan?
Please help me in understanding the things here...
Regards,
Deepak
On 10/17/07, jonathan@xxxxxxxxxxxxxxxxxx <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
>
>
> Hints are not suggestions - they direct the optimizer through
> (or away from) a specific code path, or direct it to adjust
> some of the arithmetic.
>
> The problem with hints is that they are badly documented, hardly
> anyone really knows what they mean, and nobody has any idea about
> what special case code might exist that affects the way that a
> hint is followed.
>
> In this case we have very little to go on: the OP does not tell
> us what the query was selecting, nor the execution path that was
> actually taken. However:
>
> To the best of my knowledge the /*+ parallel(alias, degree) */ hint
> does NOT tell the optimizer to use parallel execution, it merely tells
> the optimizer to divide the cost of a tablescan on 'alias' by 'degree'
> (allowing for the effect of the _optimizer_percent_parallel in general
> and a fixed 0.9 scaling factor in 10g specifically) and then follow the
> consequences.
>
> It is perfectly feasible that the optimizer found a serial index access
> path that was cheaper than the 'tablescan cost / 4' dictated by the hint.
>
>
> Regards
> Jonathan Lewis
>
>
>
> > Subject: RE: PARALLEL QUERY
> > Date: Tue, 16 Oct 2007 06:58:36 -0500
> > From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
> >
> > Deepak,
> >
> > More than likely the optimizer determined that a parallel query with
> just 4 PX slaves is not more beneficial than a serial scan, whereas 16
> slaves is more beneficial. I've never seen this before, but then again our
> databases are configured to maximize parallel performance, so any parameter
> related to parallelism is set to a larger than average value.
> >
> > To validate what the optimizer is doing, set event 10053 for each query
> and review the optimizer's decisions.
> >
> > Also, remember that hints are just that, a hint or suggestion, not a
> "force".
> >
> > Dave
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards,
Deepak
Oracle DBA
Other related posts:
