Re: PARALLEL QUERY

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Mon, 29 Oct 2007 00:08:21 +0100
Message-ID: <5bf701c819b7$7d5681b0$3e02a8c0@ADLA>

Hi Deepak,

> ... the way you determine whether Oracle > will use parallel execution for a query or not.

the first decision is IMHO between the full scan or index access of the table. If you prefer the full scan (the usual case of parallel scenario) use the FULL hint. The PARALLEL hint will be (to the best of my knowledge) considered by the optimizer as there is no alternative cheaper path. Partition pruning is the benefit you profit in this case.

Index access in some cases (you don't state if the index is partitioned) may be performed in parallel as well, you ought to use PARALLEL_INDEX hint to activate it.

If the optimizer decision will be used effectively in the runtime depends on the parameter setting (e.g. parallel_automatic_tuning, FORCE PARALLEL) and/or the current load of your system.

Regards,

Jaromir D.B. Nemec

Original Message ----- From: DBA Deepak To: jonathan_at_jlcomp.demon.co.uk Cc: oracle-l_at_freelists.org Sent: Sunday, October 28, 2007 6:37 PM Subject: Re: PARALLEL QUERY

Hi Jonathan,

The original query involves only one big table(~20M) rows.
There is no primary key on the table but there exists a composite index.
The table is list partitioned on a column into 4 partitions.
Columns involved in the said composite index are NOT NULL and the partition key column is also not null.

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 - 18:08:21 CDT