Data Centers

Take advantage of multiple CPUs with Oracle Parallel Query

It is not unusual for a dedicated Oracle server to have multiple CPUs. Learn to take advantage of parallel processing and increase query performance using the Oracle Parallel Query feature. Get an overview of how best to implement OPQ in your enterprise.


One of the latest trends is for systems to have more and more CPUs inside a single server. Using symmetric multiprocessing (SMP) servers, it is not uncommon for an Oracle server to have 8, 16, or 32 CPUs, along with many gigabytes of RAM for the Oracle SGA regions.

Oracle has kept pace with these changes and offers a wealth of facilities to take advantage of multiple CPUs. Starting with Oracle8i, Oracle implemented parallelism in virtually every database function, including SQL access (full-table scans), parallel data manipulation, and parallel recovery. The challenge for Oracle professionals is to configure their databases to use as many of the CPUs as possible.

One of the best ways to implement parallelism in an Oracle environment is to use Oracle Parallel Query (OPQ). I'll discuss how OPQ works and how you can use it to improve response time in large full-table scans, to invoke parallel rollbacks, and more.

Using OPQ
When Oracle has to perform a legitimate, large, full-table scan, OPQ can make a dramatic difference in the response time. Using OPQ, Oracle partitions the table into logical chunks as shown in Figure A.

Figure A
An OPQ partitioned table


Once the table has been partitioned into pieces, Oracle fires off parallel query slaves (sometimes called factotum processes), and each slave simultaneously reads a piece of the large table. Upon completion of all slave processes, Oracle passes the results back to a parallel query coordinator, which will reassemble the data, perform a sort if required, and return the results back to the end user. OPQ can give you almost infinite scalability, so very large full-table scans that used to take many minutes can now be completed with sub-second response times.

OPQ is heavily influenced by the number of processors involved, and full-table scans can be hugely improved by running them in parallel, with the optimum normally achieved by using N-1 parallel processes (where N=the number of CPUs on your dedicated Oracle server).

It’s also very important to note that Oracle9i can detect the external environment, including the specific number of CPUs on your server. At install time, Oracle 9i examines the number of CPUs on your server, sets a parameter called cpu_count, and uses the cpu_count for the default values of several important initialization parameters. These initialization parameters influence the way Oracle processes internal queries.

The following are some of the parameters that Oracle sets at install time, based upon the cpu_count:
  • fast_start_parallel_rollback
  • parallel_max_servers
  • log_buffer
  • db_block_lru_latches

Let’s take a closer look at how the number of CPUs influences these parameters.

The fast_start_parallel_rollback parameter
One exciting new area of Oracle parallelism is the ability to invoke parallel rollbacks in cases of system crashes. In those rare cases when an Oracle database crashes, Oracle automatically detects in-flight transactions and rolls them back at startup time. This is called a parallel warmstart, and Oracle uses the fast_start_parallel_rollback parameter to govern the degree of parallelism for in-flight transactions based on the cpu_count.

Parallel data-manipulation-language (DML) recovery will dramatically speed up the time required to restart your Oracle database after an instance crash. The default value is two times the number of CPUs in your system, but some DBAs recommend setting this value to four times the cpu_count.

The parallel_max_servers parameter
One significant enhancement within Oracle is the ability to automate the degree of parallelism for OPQ. Because Oracle is aware of the number of CPUs on your server, Oracle will automatically allocate the appropriate number of slave processes to maximize the response time of your parallel queries. Of course, there are other external factors, such as the use of table partitioning and the layout of your disk I/O subsystem, but setting the parallel_max_servers parameter will give Oracle a reasonable idea of the best degree of parallelism for your system, based on cpu_count.

Because Oracle parallel is heavily dependent on the number of CPUs on your server, the default value for parallel_max_servers is set to the number of CPUs on your server. If you are running multiple instances on the same server, this default may be too high, in which case you will see excessive server paging and high CPU utilization. The degree of parallelism is also dependent upon the number of partitions in the target table, so parallel_max_servers should be set high enough to allow Oracle to choose the best number of parallel query slaves for each query.

The log_buffer parameter
The log_buffer defines the amount of RAM reserved for immediate writing of redo log information, and this parameter is influenced by cpu_count. Oracle recommends that the maximum size for the log_buffer be either 500 KB, or 128 KB multiplied by cpu_count, whichever is greater. The number of CPUs is important to the value of log_buffer, because multiple log writer (LGWR) processes may be spawned by Oracle to asynchronously offload the redo information.

The log_buffer is one of the most misunderstood of the Oracle RAM region parameters, and there are several common configuration mistakes:
  • The log_buffer has been set too high (i.e., greater than 1 MB), causing performance problems because the writes will be performed synchronously as a result of the large size (i.e., log sync wait events are high).
  • The log_buffer is not a multiple of the db_block_size. In Oracle9i, with multiple block sizes, the log_buffer should be a multiple of 2048 bytes.

The db_block_lru_latches parameter
The number of LRU latches is used internally within the Oracle database to govern blocking within the Oracle database buffers, and this is heavily dependent upon the number of CPUs on your server.

Many savvy Oracle9i DBAs running multiple data buffers (e.g., db_32k_cache_size) recommend resetting this undocumented parameter to the recommended maximum value. The db_block_lru_latches parameter was heavily used in Oracle8i but has become an undocumented parameter starting with Oracle9i because Oracle now sets a reasonable default value based on the number of CPUs on your database.

The default value for db_block_lru_latches is one-half the cpu_count on your dedicated server (e.g., only one Oracle database on the server). Oracle recommends that db_block_lru_latches never exceed cpu_count multiplied by 2 multiplied by 3, or db_block_buffers divided by 50, whichever is higher.

There is a problem with this computation whenever you have multiple buffer pools (e.g., KEEP, RECYCLE) because you cannot govern the number of latches assigned to each data buffer pool. This default value may be too small if your db_writers parameter is greater than 1.

Move to server consolidation
The Oracle database is always improving, and the ability to detect the cpu_count and base parameters settings upon the external server environment is an important enhancement to Oracle software.

As more Oracle systems migrate to SMP, these derived Oracle parameters are even more important as Oracle customers undertake server consolidation and move dozens of databases onto giant servers with 32 or 64 CPUs.

Editor's Picks

Free Newsletters, In your Inbox