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

Thats pretty much to be expected. Getting 12 processes sync'd up for a smash-and-dash at a query takes a little time to get going.

hth
connor Received on Wed Feb 12 2003 - 02:31:43 CST