Parallel_max servers on 9.2 experiences?

From: garyl <gjlively_at_tampabay.rr.com>
Date: 10 Feb 2003 10:54:29 -0800
Message-ID: <1bcc7f54.0302101054.1c471a57@posting.google.com>

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 Received on Mon Feb 10 2003 - 12:54:29 CST