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 


 

 

 


 

 

 
 

Your Oracle cpu_count is important

Oracle Tips by Burleson Consulting
April 12, 2003
Don Burleson

The number of CPUs on your Oracle server has an important bearing on the default settings for several important Oracle parameters.  In Oracleand beyond, Oracle is becoming more aware of his external environment, and Oracle adjusts the default values of Oracle parameters based upon the characteristics of the server hardware.

Let?s examine the role of the cpu_count on the performance of your Oracle database.  As we probably know, Oracle parallel query is heavily influenced by the number of processors, and full-table scans can be hugely improved by running them in parallel, normally by using N-1 parallel processes (where N=the number of CPUs on your dedicated Oracle server).

Here are some of the parameters that Oracle sets at install time, based upon your cpu_count:

  • fast_start_parallel_rollback

  • db_block_lru_latches

  • parallel_max_servers

  • log_buffer

Let?s take a close look at how the number of CPUs influences these parameters.

fast_start_parallel_rollback

This parameter governs the degree of parallelism (DOP) for the recovery of parallel DML or parallel DDL when you have a system crash.  When Oracle crashes (e.g. due to a power failure), Oracle detects ?in-flight? updates during the warmstart phase of re-starting the Oracle instance.

Some long-running DML statements may run for hours, and take a long-time to recover at startup time.  Parallel DML recovery will dramatically speed-up the time required to re-start your Oracle database after an instance crash. The default value is 2 times the number of CPU of your system, but some DBAs recommend setting this value to 4 times the cpu_count.


db_block_lru_latches (undocumented starting in Oracle)

A too-small value for db_block_lru_latches normally manifests itself with a high LRU latch contention waits in your STATSPACK report. The default value is one-half the cpu_count on your dedicated server.  Oracle recommends that db_block_lru_latches never exceed (cpu_count * 2 * 3) or (db_block_buffers / 50), whichever is higher.

There is a problem with this computation whenever you have multiple buffer pools (KEEP, RECYCLE) because you cannot govern the number of latches assigned to each data buffer pool.  This default value for this may be too small if your db_writers is greater than 1.  Many savvy OracleDBAs running multiple data buffers (e.g. db_32k_cache_size) recommend re-setting this undocumented parameter to the recommended maximum value.


parallel_max_servers

This parameter controls the maximum number of OPQ factotum processes (p000, p001) that are spawned during a parallel query when parallel_automatic_tuning is enabled. As we know, Oracle parallel query is most commonly used in data warehouse applications where there are lot?s of legitimate large-table full-table scans, and the DBA has set parallel_automatic_tuning to allow Oracle to determine the best degree of parallelism for a query.  The default value for parallel_max_servers is 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.


log_buffer

Oracle documentation recommends that the maximum size for the log_buffer be either 500k, or (128K * 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.

Even though Oracle does not recommend a log_buffer greater than one meg, I have seen numerous shops where increasing log_buffer beyond one meg greatly improved throughput and relieved undo contention.

The log_buffer is one of the most misunderstood of the Oracle RAM region parameters, and there are several common mistakes:

  • The log_buffer has been set too high (greater than 20 meg), causing performance problems because the writes will be performed synchronously because of the large size (log sync wait events are high).
     

  • The log_buffer is not a multiple of the db_block_size.  In Oraclewith multiple block sizes, the log_buffer should be a multiple of 2048 bytes.

In sum, Oracle is becoming more aware of it?s server environment, and it is the job of the savvy Oracle DBA to properly monitor and adjust these parameters to ensure optimal CPU usage and fast response time.  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.

Many related topics are covered in my upcoming new book Oracle Tuning: The Definitive Reference by Rampant TechPress.  This book will be printed in just a few weeks, and is available at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm



 

 

��  
 
 
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