BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.


# 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)*/


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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter