Re: Performance problem with partitioned indexes & tables

Aaaah.... how I could of done with this information 3 weeks ago. I found this out today and was about to update the thread when I found you've beat me to it.

OK the details are as follows. The problem was because Oracle was trying to run the query in parallel.

I was told to use the NOPARALLEL option but this didn't work so we then tried setting PARALLEL_MAX_SERVER to zero which worked. Then checking the manuals in more detail we reset the PARALLEL_MAX_SERVER and used the hint NOPARALLEL_INDEX - this also worked. So the actual solution is to set the NOPARALLEL option on the index and then, hey presto -hints aren't necessary. Also the delays are very small (I could measure them easily) so I'm almost certain we would have gone with local indexes.

Interesting point about parallel processing being responsible for the overhead. I suppose this is the waiting for tasks to complete which I presume is only checked so many times per second (possibly 100). Still seems a little big as there should only be one task wait per SQL execution so 10 in all in my test.

I presume the code would be something like

for i = 1 to partitions

    execute part SQL as a thread <<<< some overhead kicking off a thread
next
wait until threads finish <<<< This bit must be the delay aggregate data.

I'll try this out in JAVA and see it shows similar delays.

Anyway, thanks for the reply and hopefully this thread will be useful to someone else in the future.

PS. On a high note, at least I've learnt how to spell parallel so if it is spelt incorrectly it's a typo not a spelling mistake.

PPS I hope you'll forgive me for not spotting your hint in your last reply. I didn't know about the noparallel_index hint. You learn something every day.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1030376327.27290.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> Michael,
>
> Sorry about the delay in the follow-up -
>
> As I suggested in my last post about this issue,
> it is extremely likely that your performance
> change is NOT due to local indexes per se,
> but to the use of parallel execution.
>
> Your run time changes from one or two hundredths
> of a second to around 2 seconds when you switch
> to parallel execution. As a simple rule of thumb - if
> a query runs in a few hundredths of a second serially,
> then it will probably be slower running in parallel - that's
> always been true of parallel execution.
>
> What is the time like if you re-run the desired query
> serially - perhaps using the NOPARALLEL and
> NOPARALLEL_INDEX hints ?
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK Sept, Nov
> USA x 2 November
>
> http://www.jlcomp.demon.co.uk/seminar.html
>
>
>
>
>
> Michael Burden wrote in message
> <8ea7fbb6.0208121354.d57e5af_at_posting.google.com>...
>
> >Execution Plan
> >----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15)
> > 1 0 SORT (AGGREGATE)
> > 2 1 SORT* (AGGREGATE)
> :Q10744000
> > 3 2 PARTITION RANGE* (ALL)
> :Q10744000
> > 4 3 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
> :Q10744000
> > (Cost=4 Card=270 Bytes=4050)
> > 5 4 INDEX* (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQUE)
> >>:Q10744000
> > (Cost=4 Card=270)
>
>
>
Received on Wed Aug 28 2002 - 15:40:05 CDT