Re: serious performance downgrade on partitioned table.

I don't think the thing you are describing is something that can be 'resolved' .

If you run a query in parallel, then Oracle has to allocate a number of parallel query slaves, decide how to use them, and co-ordinate their activity. It is inevitable that this will add an overhead to the actual execution cost of the query itself.

If the query is supposed to perform extremely quickly anyway and only acquires data from one partition in a partitioned table, then there is no payback for the overhead. As with so many features - PX should be used only where it is appropriate.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


billiauk wrote in message ...


>Jessica

>

>There is a known side-effect of local partitioned indexes that I don't

>believe is resolved until 8.1.7.3 (someone can correct me on that if

>they wish). The basic problem is parallel query. For an improvement in

>performance you will need to use the NOPARALLEL_INDEX hint to

>serialize access to your local indexes. I'm not sure of the actual

>mechanics, but it appears that parallel index access is killing the

>performance of index lookups. Using the hint will get you much closer

>to where you need to be i.e. almost as quick as "normal" B*Tree

>indexes. I've experienced this problem myself in 8.1.6 implementations

>and subsequently read some Metalink threads about it and resolved it

>in exactly the way I specify (of course until we patch up to

>8.1.7.3...)

>

>HTH

>

>Adrian