Re: PARALLEL QUERY
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
- Date: Sat, 27 Oct 2007 19:59:13 +0100
Deepak,
Your first comment is (I believe) correct -
the parallel hint merely reduces the effective execution cost of the query
Your conclusion, though, is not valid
Does this mean we should ideally NOT provide parallel hint as it merely
reduces the cost for determining the most efficient execution plan?
I tried to write down a more appropriate conclusion - but it got very
wordy; so I'll just state the strategy.
Under ANY circumstances, what you are trying to do with hints
is to restrict the optimizer to having just ONE possible path through
its own codebase. If you don't use enough hints, or don't use the
hints properly, then the optimizer may find a way of doing something
you didn't want, despite obeying all your hints at the appropriate points
in its working.
So, if you use the PARALLEL(XXX) hint, think about ANY executions that
might NOT do a parallel tablescan on table XXX and make it impossible for
them to happen.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, October 27, 2007 5:53 PM
Subject: Re: PARALLEL QUERY
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>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
