 |
|
Setting Automatic Parallelism
Oracle Tips by Burleson Consulting |
Oracle parallel query allows you to control
the number of parallel query slave processes that service a table.
Oracle parallel query processes can be seen on the server because
background processes will start when the query is serviced. These
factotum processes are generally numbered from P000 through Pnnn.
For example, if our server is on AIX, we can create a script to
gather the optimal degree of parallelism and pass this argument to
the SQL.
parallel_query.ksh
#!/bin/ksh
# Get the number of CPUs
num_cpu=`lsdev –C|grep mem|wc –l`
optimal_parallelism=`expr $num_cpu`-1
sqlplus system/manager<<!
select /*+ FULL(employee_table)
PARALLEL(employee_table, $optimal_parallelism)*/
employee_name
from
employee_table;
exit
!
Resource Contention and Oracle Parallel
Query
There are several sources of contention in
Oracle parallel query. As already mentioned, Oracle parallel query
works best on servers that have multiple CPUs, but we can often see
disk contention when the whole table resides on the same physical
disk. In short, the use of Oracle parallel query can precipitate
several external bottlenecks. These include:
-
Overloaded processors This
is normally evidenced when the vmstat run queue values exceed the
number of CPUs on the server.
-
Disk enqueues When
multiple processes compete for data blocks on the same disk,
I/O-related slowdowns may occur on the disk I/O subsystem. Disk
enqueues are evidenced by high activity from the UNIX iostat
utility and from the wait (wa) column of the AIX vmstat utility.
-
Increased RAM usage The
parallel sorting feature of Oracle8 may increase the demands on
the server RAM memory. This is because each parallel query process
can allocate storage in the size sort_area_size in RAM to
manage the sort.
Let’s explore things that we can do to
prevent contention-related slowdowns when using Oracle parallel
query. To be most effective, the table should be partitioned onto
separate disk devices, such that each process can do I/O against its
segment of the table without interfering with the other simultaneous
query processes. However, the client/server environments of the
twenty-first century rely on RAID or a logical volume manager (LVM),
which scrambles data files across disk packs in order to balance the
I/O load. Consequently, full utilization of parallel query involves
"striping" a table across numerous data files, each on a separate
device. It is also important to note that large contiguous extents
can help the query coordinator break up scan operations more
efficiently for the query servers. Even if your system uses RAID or
a logical volume manager (such as Veritas), you can still realize
some performance gains from using parallel query. In addition to
using multiple processes to retrieve the table, the query manager
will also dedicate numerous processes to simultaneously sort the
result set (see Figure 10-3).
Figure 3: Increased
RAM memory demands with Oracle parallel query
As you know, a RAM memory overload can cause
swapping on the database server. Because of the parallel sorting
feature, it is also a good idea to beef up the memory on the
processor. We may also see the tablespace fall short when using
parallel query and parallel DML. Here is an example of the error:
SQL> alter
session enable parallel dml;
Session altered.
SQL> insert /*+ parallel(customer, 6) */ into customer;
2 select /*+ full(c) parallel(c, 6) */
3 from customer c;
ERROR at line 3:
ORA-12801: error signaled in parallel query server P000
ORA-01652: unable to extend temp segment by 128000 in tablespace
CUSTOMER_TS
Disk Contention with Oracle Parallel Query
Many Remote DBAs are surprised to note that Oracle
parallel query does not always improve the speed of queries where
the whole table resides on a single physical disk.
The data retrieval for a table on a single
disk will not be particularly fast, since all of the parallel
retrieval processes may be competing for a channel on the same disk.
But each sort process has its own sort area (as determined by the
sort_area_size init.ora parameter), so the sorting of the result
set will progress very quickly. In addition to full-table scans and
sorting, the parallel query option also allows for parallel
processes for merge joins and nested loops.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
