Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Use Multiple CPUs with Oracle Parallel Query


July 9, 2015 (Revised May 28, 2005)

 

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.


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 Oracle can detect the server environment, including the specific number of CPUs on your server. At startup time, Oracle examines the number of CPUs on your server and sets a parameter called cpu_count, which is used in the computation of the default values of several other important parameters.

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.


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.

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational