Re: parallel query

In article <792260653snz_at_jlcomp.demon.co.uk>, Jonathan_at_jlcomp.demon.co.uk says...
>I'm not entirely convinced about this, but I'm not an O/S specialist,
>so would someone comment on the sense or stupidity of the following
>argument:
> Scenario: Single CPU
> Table: Spread across several discs
> Degree of parallelism Matches number of discs
>
It depends on the kinds of queries you anticipate and the amount of idle CPU you have available. From the Tune Oracle Applications training manual:

 For sort-intensive queries, the degree of parallelism should approach the  maximum degree your system can handle. For scan-intensive queries, the degree  of parallelism should be closer to the total number of disk heads on the drives  containing the table's files.

 For multiple-user systems, divide the maximum degree of parallelism your system  can support among the total number of concurrent users. You may want to  consider limiting parallel query activity to times when the OLTP activity on your  system is low.  

 Parallel Query works best on the folloing systems: SMPs, massively parallel  systems, clustered or loosely coupled systems, systems with the capacity to  dedicate resources for query processing. Parallel Query can aid data-intensive  applications (decision support), reporting applications requiring processing large  amounts of data, and systems requiring frequent loading and indexing of large  amounts of data.

 Optimal performance can only be achieved if your system is not heavily loaded  and I/O is evenly distributed.

I admit that I find it confusing. Now throw in Multi-Threaded Server.... I'm waiting until I have time on an SMP system before I experiment with this.

In previous replies in this thread, note that you can enable the instance to use parallel query in init.ora or individual tables to use it (ALTER TABLE emp PARALLEL (DEGREE n) or ALTER TABLE emp PARALLEL (DEGREE DEFAULT) to use the default degree of parallelism in init.ora).

-- 
Lisa Roderick
Applications Systems Engineering Performance Group   \ Opinions are mine,
Digital Equipment Corporation                                             \   not those of my
Merrimack, NH                                                                        \    employer.
lisa.roderick._at_mko.mts.dec.com