 |
|
Oracle Parallel Query
and Oracle SQL Tuning
Oracle Tips by
Burleson
|
For Oracle SQL queries that perform large-table
full-table scans, Oracle parallel query can greatly improve the access
speed for data warehouse queries.
There are several init.ora parameters
that are set when using Oracle parallel query. Many of these are
default values and are set by Oracle when your database is created.
Oracle parallel query can be turned on in several ways. You can turn
it on permanently for a table, or you can isolate the parallel query
to a single table.
Method 1 - Permanent Parallelism (not
recommended)
Alter table customer parallel degree 35;
Method 2 - Single-Query Parallelism
select /*+ FULL(emp) PARALLEL(emp, 35) */
emp_name
from
emp;
Note the use of the double hints in the preceding query.
Most Oracle DBAs always use the FULL hint with the PARALLEL hint
because they are both required to use Oracle parallel query.
WARNING: Setting a table for parallel query
with the alter table xxx parallel degree nn command can be very
dangerous to Oracle SQL performance. If you are using the CBO and you
set table-level parallelism, the optimizer may reevaluate the
execution plans for SQL statements and change many statements from
index range scans to full-table scans. This can cause serious
performance degradations for an entire database, and the parallel hint
is a far better choice for implementing parallelism because you will
never have unintended side effects.
Most Oracle DBAs identify those tables that
perform full-table scans and then alter those tables to specify the
degree of parallelism. This way, all full-table scans against the
tables will invoke Oracle parallel query.
Parallel Query init.ora Parameters
There are several important init.ora
parameters that have a direct impact on the behavior of Oracle
parallel query:
-
sort_area_size The higher the
value, the more memory is available for individual sorts on each
parallel process. Note that the sort_area_size parameter
allocates memory for every query on the system that invokes a sort.
For example, if a single query needs more memory, and you increase
the sort_area_size parameter, all Oracle tasks will
allocate the new amount of sort area, regardless of whether they
will use all of the space. It is also possible to dynamically change
sort_area_size for a specific session with the alter
session command. This technique can be used when a specific
transaction requires a larger sort area than the default for the
database.
-
parallel_min_servers This value
specifies the minimum number of query servers that will be active on
the instance. There are system resources involved in starting a
query server, and having the query server started and waiting for
requests will accelerate processing. Note that if the actual number
of required servers is less than the values of
parallel_min_servers, the idle query servers will be consuming
unnecessary overhead, and the value should be decreased.
-
parallel_max_servers This value
specifies the maximum number of query servers allowed on the
instance. This parameter will prevent Oracle from starting so many
query servers that the instance cannot service all of them properly.
-
optimizer_percent_parallel This
parameter defines the amount of parallelism that the optimizer uses
in its cost functions. The default of 0 means that the optimizer
chooses the best serial plan. A value of 100 means that the
optimizer uses each object's degree of parallelism in computing the
cost of a full-table scan operation.
Note: Cost-based optimization will always be
used for any query that references an object with a nonzero degree of
parallelism. Hence, you should be careful when setting parallelism if
your default is optimizer_mode=RULE.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
