Oracle Parallel Query
Oracle Tips by Burleson Consulting
Oracle has implemented parallel query
features that allow a query to effectively parallelize queries with
both symmetric multiprocessing (SMP) and massively parallel
processing (MPP) architectures. Using these parallel features on a
massively parallel machine, it is possible to read a one-gigabyte
table with subsecond response time. Let's begin with a review of
Note: Oracle parallel query works only
with databases that perform full-table scans. A well-tuned online
transaction database will seldom perform full-table scans and will
not benefit from Oracle parallel query.
As I stated,
Oracle parallel query will only work with queries that perform a
full-table scan, and it is very important that the Remote DBA understand
that indexes are the enemy of parallel query. To invoke parallel
query, you must also force the SQL optimizer to perform a full-table
scan. Hence, it follows that Oracle parallel query will only improve
queries that must read the majority of the data blocks in a table.
Oracle parallel query achieves improved speed
because multiple processes can be directed to read a table. Parallel
query works best on servers that have multiple CPUs because multiple
CPUs allow for simultaneous queries. A later section in this chapter
will show you how to see how many CPUs you have on your database
Starting with Oracle release 7.2, you can
partition a SQL query into subqueries and dedicate separate
processors to each one. Here's how it works: Instead of having a
single query server to manage the I/O against the table, parallel
query allows the Oracle query server to dedicate many processes to
simultaneously access the whole table (see Figure 10-1).
Figure 1: An Oracle
parallel query with full-table scan
Let’s take a look at the techniques for invoking
parallel query and determining the optimal degree of parallelism.
Invoking Oracle Parallel Query
There are several init.ora parameters
to be 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.
Permanent parallelism (Not recommended for
customer parallel degree 35;
Single query parallelism
FULL(emp) PARALLEL(emp, 35) */
Note the use of the double hints in the preceding
query. Most Oracle Remote DBAs always use the full hint with the
parallel hint because they are both required to use Oracle
WARNING: It is very dangerous to enable
Oracle parallel query for a table with the alter table
command. Once a table is marked for parallel query, the CBO may
change the execution plan for existing queries to use parallel
full-table scans instead of index scans. This well-intentioned
mistake has crippled many databases, since queries that used to run
fast with indexes will now use a full-table scan.
Most Oracle Remote DBAs identify those tables that
perform full-table scans and then add hints to specify the degree of
parallelism for the query. This way, all full-table scans against
the tables will invoke Oracle parallel query. It can be very
dangerous to turn on parallelism with the alter table command
because the CBO may change other queries' execution plans to perform
full-table scans based on the new setting for parallelism. This
could adversely affect hundreds of SQL statements.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.