Parallel query is suitable for a certain class of large problems: very large problems
that have no other solution. Parallel query is my last path of action for solving a
performance problem; it's never my first course of action.
Parallel Query Settings
I will not discuss the physical setup of parallel query operations. That topic is well
covered in both the Oracle Concepts Guide and Data Warehousing Guide. As I mentioned
earlier, my current favorite way to set up parallelism in Oracle is using the automatic
tuning option first introduced in Oracle8i Release 2 (version 8.1.6):
PARALLEL_AUTOMATIC_TUNING = TRUE. With this setting, the other parallel settings are
automatically set. Now, all I need to do is set the PARALLEL option on the table (not
PARALLEL
, just PARALLEL) and Oracle will, when appropriate, parallelize certain
operations on that table for me. The degree of parallelism (how many processes/threads
will be thrown at a problem) will be decided for me and vary over time as the load on the
system varies. I have found that, for most cases, this achieves my desired goal, which is
usually to get the best performance, with the least amount of work, in a manner that is
most manageable. Setting a single parameter is a great way to get there.
For the novice user wanting to play with parallel query for the first time, parallel
automatic tuning is a good way to get started. As you develop an understanding of what
parallel query does and how it does it, try tweaking some of the other parallel settings:
PARALLEL_ADAPTIVE_MULTI_USER Controls whether the degree of parallelism should vary
over time as the load on the system does; should the algorithm for assigning resources
"adapt" to the increase in load.
PARALLEL_EXECUTION_MESSAGE_SIZE Sets the size of the message buffers used to pass
information back and forth between the processes executing the parallel query.
PARALLEL_INSTANCE_GROUP Applies only to Oracle RAC configurations (Oracle Parallel
Server, OPS, in Oracle8i and earlier). Allows you to restrict the number of instances
that will be used to perform a parallel operation (as opposed to the number of processes
an instance will use).
PARALLEL_MAX_SERVERS Sets the maximum number of parallel query slaves (like dedicated
servers but for parallel operations) your instance will ever have.
PARALLEL_MIN_PERCENT Useful if you would like to receive an error message when you
request a specific degree of parallelism but insufficient resources exist to satisfy that
request. You might use this to ensure that a process that takes days unless it gets what
it wants doesn't run unless it gets what it wants.
PARALLEL_MIN_SERVERS Sets the number of servers to start when the instance starts and
to keep started permanently. Otherwise, you may need to start the parallel processes in
response to a query.
PARALLEL_THREADS_PER_CPU Determines the default degree of parallelism and contributes
to the adaptive parallelism algorithms, to determine when to back off on the amount of
parallel resources.
RECOVERY_PARALLELISM For crash recovery, sets how many parallel threads should be
used. This setting can be used to speed up the recovery from an instance crash.
FAST_START_PARALLEL_ROLLBACK Sets how many processes would be available to perform a
parallel rollback after the recovery takes place. This would be useful on systems where
many long-running transactions are constantly processing (which would need
correspondingly long rollback times in the event of an instance crash).设置了parallel_automatic_tuning=true能解决大部分的问题。在table后面直接加parallel即可。对于dml使用下面的方法alter session enable parallel dml;
is a prerequisite for parallem DML, without it - won't matter WHAT parameters you have set.
for SELECT, the parallel parameters in the init.ora plus - parallel on the table, parallel hint --
would be enough. |
|
|
|
转自 