U can but the CPU count will be accounted for Degree of Parallelism

===============================================

If parallelism is switched on for any of the objects in the query, the
next thing to be determined is the degree of parallelism that should be
used.
The rules affecting this and the suitability of the query for
parallelization
depend on the type of query i.e. whether it is a SELECT, UPDATE, DELETE,
INSERT...SELECT or DDL. These rules are documented in detail in chapter 23
"Parallel Execution of SQL Statements" of the Release 2(8.1.6) Concepts
manual.

It is important to note that in 8i, the DOP is calculated differently from
previous versions. In 8i, degree and instances should not be specified
separately,
although for backward compatibility it is possible to use the old syntax
on the
CREATE and ALTER statements.

In 8i, the DOP is calculated as

degree * instances.

This means that a query on an object with degree set to 1 and instances
set to
5, will run in parallel with 5 slaves per set. Pre-8i, the query would
have run
in serial.
This also applies if either degree or instances is set to
'default'. Note the following examples:

a. DEGREE = 1, INSTANCES = DEFAULT, DOP = (1 * DEFAULT) = DEFAULT
b. DEGREE = 3, INSTANCES = DEFAULT, DOP = (3 * DEFAULT) = 3
c. DEGREE = DEFAULT, INSTANCES = DEFAULT, DOP = (DEFAULT * DEFAULT) =
DEFAULT

The DEFAULT Degree Of Parallelism is calculated as:

CPU_COUNT * PARALLEL_THREADS_PER_CPU

Whether the number of slaves requested is actually used depends on the
runtime
environment. There are several factors that affect the runtime
environment.
These are:

1. the parameter PARALLEL_MAX_SERVERS
2. the parameter PARALLEL_MIN_PERCENT
3. the parameter PARALLEL_AUTOMATIC_TUNING

1. PARALLEL_MAX_SERVERS
This is the maximum number of query slaves that can be started in the
instance.
Each slave is only involved in one query at a time. If the maximum number
are
already in use, a new parallel query will have to run in serial. Once a
slave
has finished processing a query, it can be used by other sessions. If it
is not
used for 5 minutes, it will be terminated if the miniumum number of
required
parallel servers, defined by PARALLEL_MIN_SERVERS, already exist.
To see how many are currently running, check v$pq_slave. The status column
is
set to BUSY or IDLE, specifying whether the slave is currently in use.


2. PARALLEL_MIN_PERCENT
This parameter specifies the minimum percentage of slaves that is required
to
run queries in parallel.
If set to 0, it means that there is no minimum number of parallel slaves
required. If set to 100, the query will fail unless all of the requested
slaves
can be found. If set between 0 and 100, the query will fail unless this
percentage of query slaves can be started.

If not enough slaves can be found, the following error is repored :

"ORA-12827 insufficient parallel query slaves available".

3. PARALLEL_AUTOMATIC_TUNING
Setting this parameter to true causes other parameters to be derived.
These are:

PARALLEL_ADAPTIVE_MULTI_USER - will be set to TRUE.

PARALLEL_EXECUTION_MESSAGE_SIZE - will become 4K rather than 2K

PARALLEL_MAX_SERVERS - #CPUs * 10 (but this is platform specific).

==========================================================

HTHU

Ankur Shah
Oracle DBA
DHR-GA
----- Original Message -----
From: "Daniel Kelly" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Wednesday, April 30, 2003 10:41 AM
Subject: Parallel This, Parallel That......


> I am running Oracle 8.1.7 on a single server using NT.
>
> Whenever I see the word "Paralell" in some Oracle
> option, I always assume this is for a multi-CPU or
> multi-server environment. Is this true?
>
> "Parallel query"
> /*+ PARALLEL */ Hint
> Parallel Server
> etc.
>
> Advice was given to make a table "parallel query"
> if the enqueue deadlocks was too high. Since I don't
> have multiple CPUs, I can't do this right?

Oracle LazyDBA home page