Subject: Re: PARALLEL QUERY
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Oct 2007 08:28:12 -0000
>> In my original post in this thread Oracle completely ignored the parallel
scan
>> of the table even though the explain plan showed that it had lesser cost.
>>
>> As per your explanation I had restricted/limited Oracle optimizer
(through hints)
>> to follow parallel processing approach.
So you can make Oracle follow the parallel query path you want when you
use the correct set of hints. So what you're asking is "how do you work out
whether Oracle will execute a parallel query when it isn't forced into it by
the correct set of hints".
If it is too time consuming to elaborate here then can you please point me
to any document which mentions the algorithm that Oracle follows to decide
whether to use parallel execution or not.
To decide whether or not the optimizer should produce a parallel
path for your query - first read my book on how the optimizer does
its arithmetic. Unfortunately the bit about parallel and partitioned
adjustments will only appear if I ever manage to write volume 3, and
I'm still stuck trying to find time to finish volume 2, but there are a few
comments on parallel query in volume 1.
Then check your query and the object statistics to see if the chosen
plan makes sense according to the descriptions I've given; then you
might look at the 10053 trace to see where your arithmetic and
Oracle's arithmetic diverge. Look especially at the TABLE and
INDEX stats, and in 10.2 look for textual clues like
"using composite statistics", "allowing for partition skew".
One particular point: you have a partitioned table, specifically LIST
partitioned, and you are operating with an IN LIST on the partition
key. There are a few odd errors (to my mind) in the optimizer code
that deals with partitioned tables, leaving you with a mixture of table
level and partition level stats being used to produce an execution plan -
one of key points of LIST partitioning is that the optimizer can recognise
at optimisation time which partitions (hence how many) will be visited,
and adjust the cost, so the path for
pt_key in (0,1)
may be different from the path for
pt_key in (0,10)
depending on whether the two values are in the same partition or not.
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>
> In my original post in this thread Oracle completely ignored the parallel
> scan of the table even though the explain plan showed that it had lesser
> cost. As per your explanation I had restricted/limited Oracle optimizer
> (through hints) to follow parallel processing approach.
>
If it is too time consuming to elaborate here then can you please point me
to any document which mentions the algorithm that Oracle follows to decide
whether to use parallel execution or not.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Subject: Re: PARALLEL QUERY
