Use Multiple CPUs with Oracle Parallel Query

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).

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. The number of CPUs is important to the value of log_buffer, because multiple log writer slaves (ARCH processes) may be spawned by Oracle to asynchronously offload the redo information.

Oracle does not support multiple LGWR *processes*. If it did, there would have to be multiple log buffer pools because each process has it's own address space.  Oracle does support multiple LGWR *slaves*, which are associated with DBWR slaves.

The distinction is that DBWR processes sub-divide the shared pool into different address spaces, and DBWR slaves share the same address space. I don't believe you can configure LGWR slaves, they are configured based on the settings of Oracle DBWR slaves (also, Oracle will use ARCH slaves). So, unless you have DBWR slave processes configured (dbwr_io_slaves), you will never have LGWR slave and you will never have more than one LGWR.

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.