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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 
 

Oracle Instance tuning:  Examining the internals

Oracle Tips by Burleson Consulting

 Keeping your Oracle database tuned for peak performance requires an understanding of a number of issues. In my previous article, I explained how to tune the server and network.

Once you've addressed those tasks, the next place to look is within the Oracle instance itself. An Oracle instance consists of the memory region of the database, called the System Global Area or SGA, and the Oracle background processes. Once the instance has been tuned, we look at tuning individual objects (tables and indexes) within the database, and finally we tune the SQL statements that are run against the database.

If you haven't read the first two articles in this series, you can still catch up. The first article, "Oracle tuning: Start by looking at the big picture," provides an overview of Oracle tuning. The second, "Oracle tuning: Monitoring hardware with UNIX tools," looks at tuning the database server.


Oracle initialization parameters

This instance-tuning phase of Oracle tuning examines the overall database and the instance-wide parameters that affect performance.

With many hundreds of Oracle initialization parameters, it's important for the database administrator (DBA) to focus on those parameters that have the most impact on the performance of the Oracle database. Oracle has a huge number of parameters in the Oracle initialization files (init.ora) that control the overall configuration of the Oracle instance. While there are dozens of init.ora parameters that affect performance, these are some of the most important:
  • shared_pool_size—The memory region allocated for the library cache and internal control structures
  • db_block_size—The size of each data block
  • db_cache_size—The number of data buffers to allocate for the instance
  • sort_area_size—The amount of RAM reserved for each user to perform sorting operations
  • optimizer_mode—The default mode in which all SQL statements will be optimized for an execution plan
  • db_file_multiblock_read_count—The parameter that controls the number of blocks that are read asynchronously in full-table scan operations

We have to remember that all Oracle instances are generally constrained by I/O operations. I/O is the single most dramatic component of Oracle performance, and any initialization parameters that can be used in order to reduce I/O will improve the performance of the database.

For example, the db_block_size and db_cache_size initialization parameters are very important in terms of reducing I/O. As a general rule, the larger the Oracle block size, the more the database can read in a single disk I/O and the less I/O contention the system will have.

  • Shared pool delays—Delays in the shared pool are most commonly caused by large amounts of nonreusable SQL in the library cache. When a new SQL statement is parsed, Oracle will check in the library cache to see if the SQL statement has already been processed by an earlier task. This checking can slow down SQL possessing, especially for a database whose SQL is not reusable.
     
  • Buffer busy waits—Buffer busy waits occur when a data block is in the buffer but can't be accessed because another task has a lock on the data block. This is most frequently caused by concurrent insert or update activity against a table that does not have multiple freelists defined.
     
  • Buffer hit ratio—This metric is a measure of the effectiveness of the Oracle data block buffer. The higher the buffer hit ratio, the more frequently Oracle found a data block in memory and avoided an I/O.  2013 Update: Since the days of Oracle 7 when Oracle Corporation recommended keeping the buffer cache hit ratio above a fixed threshold, people are now recognizing the limitations of ratio analysis.  Please read: Is the Oracle buffer hit ratio a useless metric for monitoring and tuning?
When tuning an Oracle instance, most DBAs begin by taking overall views of the performance of the database. In Oracle8 through Oracle9i, most DBAs use the Oracle STATSPACK utility to collect, store, and report on performance information.

For details about capturing and plotting database STATSPACK information, see my article "Oracle Database Trend Analysis Using STATSPACK" in Oracle magazine.

Now that we have a high-level understanding of the main areas of instance tuning, let's examine the tuning of the Oracle data buffers.

Data buffer tuning

The tuning of the Oracle data buffers is an important aspect of Oracle tuning because the data buffer has a direct impact on disk I/O. Oracle provides an in-memory cache for data blocks that have been retrieved by earlier SQL requests. When a request is made to Oracle to retrieve data, Oracle will first check the internal memory structures to see if the data is already in the buffer. If the block is in memory, Oracle reads the data from the RAM buffer and avoids doing unnecessary disk I/O.

It would be ideal if you could create one buffer for each database page, ensuring that Oracle would read each block only once. With Oracle8i and the very large memory (VLM) features, it's now possible to specify a data buffer that's large enough to hold an entire multigigabyte database, but most large databases do not have enough RAM to allow for the full caching of data pages.

In Oracle8i, we have three buffer pools for holding data blocks:

  • DEFAULT pool—Used for all data blocks that are not marked for the KEEP or RECYCLE pools
     
  • KEEP pool—Reserved for tables and indexes that are used very frequently
     
  • RECYCLE pool—Reserved for data blocks that are read when performing large, full-table scans
Because most Oracle databases do not have enough RAM to cache the whole database, the data buffers manage the data blocks to reduce disk I/O. Oracle utilizes a least recently used algorithm to determine which database pages are to be flushed from memory.

As I mentioned earlier, the measure of the effectiveness of the data buffer is called the data buffer hit ratio. This ratio computes the likelihood that a data block is present in the data buffer when the block is requested. The more data blocks that are found in the buffer, the higher the data buffer hit ratio. Oracle recommends that all databases exhibit a data buffer hit ratio of at least 90 percent.

It's important to note that the data buffer hit ratio is an elapsed-time measurement. If you use the Oracle STATSPACK utility to compute the data buffer hit ratio over short intervals (every five minutes), you will see that the buffer hit ratio varies from 50 percent to 100 percent, depending upon the type of SQL requests that are being processed. To see the average buffer hit ratio since the database was started, you can run the Oracle dictionary queries shown below


This will show you the average data buffer hit ratio since your database was started.

Many Oracle shops will keep their buffer hit ratio information in the STATSPACK tables and plot it to show trends in the effectiveness of the data buffer to reduce I/O. Below is an example of a plot of STATSPACK data for the data buffer hit ratio.



Multiblock reads and Oracle

The db_block_size parameter can have a dramatic impact on system performance. In general, db_block_size should never be set to less than 8K, regardless of the type of application. Even online transaction processing systems (OLTP) will benefit from using 8K blocks, while systems that perform many full-table scans will benefit from even larger block sizes. Depending on the operating system, Oracle can support up to 16K block sizes. Systems that perform full-table scans can benefit from this approach.

In addition, note the relationship between db_block_size and the db_file_multiblock_read_count parameter. Note that the db_file_multiblock_read_count has been automated in 10gr2 and beyond. 

Starting in Oracle 10g release 2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.  For more details, see my notes on
10gR2 automatically tuned multi-block reads.

At the physical level in UNIX, Oracle always reads in a minimum of 64K blocks. Therefore, the values of db_file_multiblock_read_count and db_block_size should be set such that their product is 64K. For example, the following table shows the recommended settings based upon the db_block_size:

Blocksize db_file_multiblock_read_count
4K 16
8K 8
16K 4
32K 2



Note that the block size for an Oracle database can be changed when the database is reorganized. Eventually, all Oracle databases should be compressed (export/import) to reduce fragmentation. At that point, it becomes trivial to alter the value of db_block_size.

Because the db_cache_size is expressed in the number of blocks, increasing the size of db_block_size will increase the size of the Oracle SGA. The values of db_block_size are multiplied by the value of db_cache_size to determine the total amount of memory to allocate for Oracle's I/O buffers.

 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

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

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.