Re: Parallel_max servers on 9.2 experiences?

garyl wrote:
> Repeating a DW query on a 29m row fact table joining to 5 dim tables
> on a Sun 450 (3CPU x 2gb x 8 disks). The fact table is partitioned by
> range of dates (weeks)and is paralleled. I have the (test)box all to
> myself.
>
> With max_parallel_servers = 2, I get good performance on small
> (1-2week) but poor performance on large queries(bet you knew that!).
>
> With parallel_max_servers = 12, I get good large query performance but
> very poor small query performance. There is a 3-4s delay before the
> fact table is scanned. The small query performance gets poor starting
> with parallel_max_servers = 3 and worse with increasing numbers.
>
> I've tried indexes (btree & bitmap) and stats are computed. Same
> times.
> Statspack doesn't show anything useful (to me) when different
> parallel_max_servers are used.
>
> So, can anyone give me a clue on how to analyze this or is there a
> workaround?
>
> TIA - Gary

Make sure you do *not* process the small tables in parallel. There's an overhead in parallel processing, that causes the performance for small tables to drop.
Afaik, hints are the only way to accomplish this, but that is pre-9i experience...

Frank Received on Tue Feb 11 2003 - 13:34:31 CST