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

Home -> Community -> Usenet -> c.d.o.server -> Re: Parallel querying with partitioned table

Re: Parallel querying with partitioned table

From: <yong321_at_yahoo.com>
Date: Sat, 23 Jun 2007 21:45:08 -0700
Message-ID: <1182660308.546819.120970@z28g2000prd.googlegroups.com>


On Jun 24, 4:09 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> This is not a trivial question.
>
> In principle:
> A parallel query against a partitioned table
> will use one slave per partition if the query
> is thought to span multiple partitions, and it
> can use all slaves on a single partition if the
> query is thought to target just one partition.
>
> Unfortunately, this is NOT strictly true. It is
> possible for the optimizer to decide to use
> parallelism at degree M when accessing N
> partitions. Sometimes this can lead to very
> inefficient, brute-force, processing when a
> more efficient path is available. This can be
> a particular problem with multi-table joins
> that should be partition-wise joins.
>
> You may be better off leaving the tables
> defined as non-parallel and adding explicit
> parallel hints to the code for critical queries.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users'
> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

[I caught you top-post, Jonathan!]

Would setting parallel_adaptive_multi_user to false get us closer to the M=N goal? Documentation says there's no way to tell Oracle to use block range granules instead of partition granules, or the other way. Any comment?

Unrelated. Why is the much touted parallel_automatic_tuning deprecated in 10g? Is it against Oracle's trend of increasing auto-tuning, or it's because all the implied changes (message size doubled etc) already default?

Yong Huang Received on Sat Jun 23 2007 - 23:45:08 CDT

Original text of this message

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