I'm working on designing a history table which will be partitioned as
follows on the SYS_DATE column (system date of the row modification):

HIST_MBR_ADDR
HIST_MBR_ADDR_Q2_2007
HIST_MBR_ADDR_Q3_2007
HIST_MBR_ADDR_Q4_2007
HIST_MBR_ADDR_Q1_2008
HIST_MBR_ADDR_OVERFLOW

The index (SYS_DATE, USER_ACTION, USERNAME ) will be local and partitioned
in partitions similar to the table partitions. Each partition will be in a
separate tablespace (both data and index).

My question is this: Would there be ANY benefits from defining the table as
parallel with a degree equal to the number of partitions or would I be best
served by just defining it as a partitioned table and letting the Oracle
optimizer decide whether to use parallel querying or not?

This will be in an Oracle 10g 10.2.0.3 environment running under Windows
2003 Server, 8 GB of ram and 8 CPUs.

Thanks.

Dereck L. Dietz wrote:
> I'm working on designing a history table which will be partitioned as
> follows on the SYS_DATE column (system date of the row modification):
>
> HIST_MBR_ADDR
> HIST_MBR_ADDR_Q2_2007
> HIST_MBR_ADDR_Q3_2007
> HIST_MBR_ADDR_Q4_2007
> HIST_MBR_ADDR_Q1_2008
> HIST_MBR_ADDR_OVERFLOW
>
> The index (SYS_DATE, USER_ACTION, USERNAME ) will be local and partitioned
> in partitions similar to the table partitions. Each partition will be in a
> separate tablespace (both data and index).
>
> My question is this: Would there be ANY benefits from defining the table as
> parallel with a degree equal to the number of partitions or would I be best
> served by just defining it as a partitioned table and letting the Oracle
> optimizer decide whether to use parallel querying or not?
>
> This will be in an Oracle 10g 10.2.0.3 environment running under Windows
> 2003 Server, 8 GB of ram and 8 CPUs.
>
> Thanks.

If I understand that you are planning to put table segment and index
segments into separate tablespaces ... my question would be why?

If you are planning to parallelize based on the number of partitions,
again, I would ask why? And suggest you read the docs. You don't create
more CPUs when you create more partitions.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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 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


"Dereck L. Dietz" <dietzdl@ameritech.net> wrote in message
news:f1Zai.12091$4Y.9270@newssvr19.news.prodigy.ne t...
> I'm working on designing a history table which will be partitioned as
> follows on the SYS_DATE column (system date of the row modification):
>
> HIST_MBR_ADDR
> HIST_MBR_ADDR_Q2_2007
> HIST_MBR_ADDR_Q3_2007
> HIST_MBR_ADDR_Q4_2007
> HIST_MBR_ADDR_Q1_2008
> HIST_MBR_ADDR_OVERFLOW
>
> The index (SYS_DATE, USER_ACTION, USERNAME ) will be local and
> partitioned
> in partitions similar to the table partitions. Each partition will be in
> a
> separate tablespace (both data and index).
>
> My question is this: Would there be ANY benefits from defining the table
> as
> parallel with a degree equal to the number of partitions or would I be
> best
> served by just defining it as a partitioned table and letting the Oracle
> optimizer decide whether to use parallel querying or not?
>
> This will be in an Oracle 10g 10.2.0.3 environment running under Windows
> 2003 Server, 8 GB of ram and 8 CPUs.
>
> Thanks.
>
>

On Jun 24, 4:09 am, "Jonathan Lewis" <jonat...@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

<yong321@yahoo.com> wrote in message
news:1182660308.546819.120970@z28g2000prd.googlegr oups.com...
>
> [I caught you top-post, Jonathan!]

"Top-posting" wasn't a sin when I started using the newsgroups,
and it's more appropriate for email - so sometimes I forget.

>
> Would setting parallel_adaptive_multi_user to false get us closer to
> the M=N goal?

No.

> Documentation says there's no way to tell Oracle to use
> block range granules instead of partition granules, or the other way.
> Any comment?
>

Where did you find this comment in the documentation, I hadn't
come across it.

For some special cases the pq_distribute() hint - for parallel
joins - will have an effect.

> 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?
>

I hadn't realised that parallel_automatic_tuning was "much
touted". Most of the comments I've heard about it have been
negative - mostly because it sets parallel_adaptive_multi_user
to true when you enable it.

Your guess is as good as mine. The feature may be deprecated
because Oracle wants to leave it 'always on' in future.


> Yong Huang
>


--
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

On Jun 24, 2:25 pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> <yong...@yahoo.com> wrote in message
>
> news:1182660308.546819.120970@z28g2000prd.googlegr oups.com...
>
>
>
> > [I caught you top-post, Jonathan!]
>
> "Top-posting" wasn't a sin when I started using the newsgroups,
> and it's more appropriate for email - so sometimes I forget.
>
>
>
> > Would setting parallel_adaptive_multi_user to false get us closer to
> > the M=N goal?
>
> No.
>
> > Documentation says there's no way to tell Oracle to use
> > block range granules instead of partition granules, or the other way.
> > Any comment?
>
> Where did you find this comment in the documentation, I hadn't
> come across it.
>
> For some special cases the pq_distribute() hint - for parallel
> joins - will have an effect.
>
> > 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?
>
> I hadn't realised that parallel_automatic_tuning was "much
> touted". Most of the comments I've heard about it have been
> negative - mostly because it sets parallel_adaptive_multi_user
> to true when you enable it.
>
> Your guess is as good as mine. The feature may be deprecated
> because Oracle wants to leave it 'always on' in future.
>
> > Yong Huang
>
> --
> 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

Data warehousing guide
http://download-west.oracle.com/docs...23/usingpe.htm
says "There is no way you can enforce a specific granule strategy as
Oracle Database makes this decision internally."

pq_distribute hint at least has some control on whether mapping to
partitions or not. That's good. Thanks.

I'm under the impression that until 10g, Oracle has been recommending
parallel_automatic_tuning. (Forget my comment "much touted".) I didn't
know parallel_adaptive_multi_user was a bad thing.

Yong Huang