PARALLEL_MAX_SERVERS on a DWH DB

[mike9]

Hi,

I'm working on a DWH project using Oracle 9iR2 on a Sun Solaris server (20 CPUs). In average we got about 10 concurrent long runing queries with a degree of parallelism of 4. But it can also be that we got up to 20 long runing queries at the same time. Most of the process related to this queries are on IO wait (in most cases at least 30% of the CPUs are idle) .

What I was wondering is how big you would set PARALLEL_MAX_SERVERS on such a system?


Thanks for any feedback
Mike

[DaPi]

It will be a function of RAID level, number of physical disks and how the data is distributed across those disks. (In worst case you may even want to decrease the degree of parallelism . . . )

[mike9]

Hi DaPi,

Thx for your feedback. A problem that we have is that on the server side we have allways a high IO/Wait.

Here the output of the Unix top utility:
CPU states: 27.5% idle, 34.1% user, 3.8% kernel, 34.6% iowait, 0.0% swap

So because the IO seems to be pretty busy I think that it doesnt make to much sense to have much more parallel process.

Mike

[slimdave]

Have you run DBMS_STATS.GATHER_SYSTEM_STATS at all?

[mike9]

yes, but we use gather_table_stats.

[slimdave]

Originally posted by mike9
yes, but we use gather_table_stats.

Don't confuse GATHER_SYSTEM_STATS with GATHER_SCHEMA_STATS or GATHER_DATABASE_STATS ... it's entirely different. It gathers statistics on system workload.

[mike9]

It seems that I just learned something :-)

will check it with the dba