Subject: Re: PARALLEL QUERY

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 29 Oct 2007 08:28:12 -0000
Message-ID: <043001c81a05$a550be30$0200a8c0@Primary>

>> 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_at_gmail.com>
>>
>>
>> > 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.
>> >
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 29 2007 - 03:28:12 CDT