Answer: Unless your database is a Decision Support System (DSS)
or a Data Warehouse application, I always turn-on
parallelism only at the SQL statement level, using a
parallel hint. Remember,
parallelism ONLY works for large-table-full-table scans and
OLTP databases like to use indexes instead of parallel
query.
There are many pitfalls with "automatic" parallelism,
most notably, it influences the cost-based SQL optimizer
into thinking that full-scans are "cheaper" than index scans.
The pre 11g default
value for parallel_max_servers is the number of
CPUs on your server.
The value for the parallel_max_servers parameter
is derived from the values of cpu_count,
pga_aggregate_target (sga_target, memory target), and
parallel_threads_per_cpu.
Starting in Oracle 11.2 and beyond there is a new method
to compute the default value for
parallel_max_servers
.
parallel_max_servers =
parallel_threads_per_cpu * cpu_count *
concurrent_parallel_users * 5
You can change parallel_max_servers at the system level
but you cannot change parallel_max_servers for an individual
session.
SQL>
alter system set
parallel_max_servers =
36 scope=both;
System altered.
SQL> alter
session set parallel_max_servers = 32;
alter session set
parallel_max_servers = 32
*
ERROR at line 1:
ORA-02248: invalid
option for ALTER SESSION
parallel_max_servers and performance
This parameter
controls the maximum number of OPQ factotum processes (p000,
p001) that are spawned during a parallel query when
parallel_automatic_tuning is enabled. As we know,
Oracle parallel
query is most commonly used in data warehouse applications
where there are lot’s of legitimate large-table full-table
scans, and the DBA has set parallel_automatic_tuning to
allow Oracle to determine the best degree of parallelism for
a query.
If you are running multiple instances on the same
server, this default may be too high, in which case you will
see excessive server paging and high CPU utilization. The
degree of parallelism is also dependent upon the number of
partitions in the target table, so parallel_max_servers
should be set high enough to allow Oracle to choose the best
number of parallel query slaves for each query.
Again, it is imperative to remember
that parallelism (including parallel_max_servers) only
matters for operations that invoke large-table full-table
scans, and well-indexed OLTP databases do not need to be
concerned with parallel query except for specific SQL
statements.
Note that the Servers Max statistic matches the
parallal_max_servers initialization parameter. The Servers
Highwater statistic shows the highest number of concurrent
slave processes in use at any one time.
If the
Servers Highwater value is near or equal to the Servers Max
value, consider increasing the parallal_max_servers
initialization parameter.
The Buffers Current Total
value shows the maximum number of buffers in the PX message
pool. The total number of buffers in use is Buffers Current
Total – Buffers Current Free.
The maximum number of
concurrent buffers used in the life of the instance is shown
in the Buffers HWM value. If the Buffers HWM value is close
to the Buffers Current Total value, this is another
indicator that the appropriate SGA components, Shared Pool
or Large Pool, need to be increased.
Many times
when tuning applications, it is often beneficial to identify
session details. Since this chapter is devoted to parallel
operations, it is useful to determine the Query Coordinator
process and its parallel slaves.
