Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: PARALLEL QUERY

Re: PARALLEL QUERY

From: DBA Deepak <oracle.tutorials_at_gmail.com>
Date: Sun, 28 Oct 2007 23:07:24 +0530
Message-ID: <75aa80160710281037k3b26165dtf8dfe5c121d4c33f@mail.gmail.com>


Hi Jonathan,

Would be great if you can share with us the way you determine whether Oracle will use parallel execution for a query or not.

My query is very straight forward as below.

select * from big_table bt
where
<predicates involving the composite index columns> AND <predicate involving the partition key column>;

e.g.,

 select * from big_table bt
where
c1=12 AND
 c2='A' AND
 c3='A123' AND
c4 in (0,1); -- c4 partition key column

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.

Thanks in advance.

Regards,

Deepak

On 10/28/07, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> Looking back through the thread, the only information I have
> about the query is that it is:
>
> select /*+ parallel(bt,4) */ from big_table bt;
>
> How many tables are there in your real query, and how many indexes
> are there available, and are any of the tables partitioned and which
> columns are declared not null, and how many lines is the serial
> execution plan that you get.
>
>
> 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>
> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Cc: <oracle-l_at_freelists.org>
> Sent: Saturday, October 27, 2007 9:16 PM
> Subject: Re: PARALLEL QUERY
>
>
> > Hi Jonathan,
> >
> > Thanks again for your nice explanation. But I had a little difficulty in
> > understanding the following....
> >
> >> think about ANY executions that might NOT do a parallel tablescan on
> table
> > XXX and make it impossible for them to happen.
> >
> > Would be great if you can kindly elaborate on the above.
> >
> > 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.
> >
> > Please excuse me for any kind of ignorance.
> >
> > Regards,
> >
> > Deepak
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards,

Deepak
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Oct 28 2007 - 12:37:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US