Buffer Busy Waits : Common Causes, Diagnosis, and Actions

A session that reads or modifies a buffer in the SGA must first acquire the cache buffers chains latch and traverse the buffer chain until it finds the necessary buffer header. Then it must acquire a buffer lock or a pin on the buffer header in shared or exclusive mode, depending on the operation it intends to perform. Once the buffer header is pinned, the session releases the cache buffers chains latch and performs the intended operation on the buffer itself. If a pin cannot be obtained, the session waits on the buffer busy waits wait event. This wait event does not apply to read or write operations that are performed in sessions’ private PGAs.

Keep the following key thoughts in mind when dealing with the buffer busy waits event.

• The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.
• buffer busy waits indicate read/read, read/write, or write/write contention.
• The appropriate action to take depends on the reason encoded in the P3 parameter.
  
  
  The following pieces of information are essential for diagnosing the buffer busy waits problem when it is the leading bottleneck that slows a process down.
  
  
• The primary reason code that represents why a process fails to get a buffer pin.
• The class of block that the buffer busy waits wait event is for.
• The SQL statement that is associated with the buffer busy waits event.
• The segment that the buffer belongs to.

A session may fail to get a pin on a buffer header for various reasons. The reasons are represented by a set of codes. Up to Oracle9i Database, a process that waits on the buffer busy waits event publishes the reason code in the P3 parameter of the wait event.

This is typical in applications that spawn multiple concurrent threads or sessions, and each one executes the same query that goes after the same data set. In this case, you can check the sessions’ logon time in the V$SESSION view, and chances are you will find them only a few seconds apart. When multiple sessions request the same data block that is not in the buffer cache, Oracle is smart enough to prevent every session from making the same operating system I/O call. Otherwise, this can severely increase the number of system I/Os. Instead, Oracle allows only one of the sessions to perform the actual I/O, while others wait for the block to be brought into the buffer cache. The other sessions wait for the block on the buffer busy waits event and the session that performs the I/O waits on the db file sequential read (or the db file scattered read) wait event. You will notice that the buffer busy waits and the db file sequential read events share the same P1 (file#) and P2 (block#) values. 

Reason code 220 indicates there are multiple sessions trying to concurrently modify different rows within the same block that is in the buffer cache. This symptom is typical in applications with high DML concurrency. Unfortunately, a block can be pinned by only one process at any one time. The other concurrent processes must wait on the buffer busy waits wait event until the first change is complete. This is a good thing; otherwise the block will be corrupted.

If the majority of the buffer busy waits wait events are centered on data blocks (class #1) and the reason code is 130, this shows the application runs multiple sessions that query the same data set at the same time. (You will only know this if you query the V$SESSION_WAIT view repeatedly or trace the session with the 10046 event or use the data sampling methods) This is an application issue. There are three things you can do to minimize this problem:

• Reduce the level of concurrency or change the way the work is partitioned between the parallel threads.
• Optimize the SQL statement to reduce the number of physical and logical reads.
• Increase the number of FREELISTS and FREELIST GROUPS.

From our experience, it is very difficult to get the application to reduce the level of concurrency. It may not be a good idea because it limits scalability. However, there are differences between scalability and a blind attempt by the application to improve performance by spawning multiple sessions. So far, SQL tuning has worked wonderfully to reduce the occurrences of buffer busy waits. Check the SQL execution plan and optimize the SQL statement to use the most effective join method and access paths that reduce the number of physical and logical reads.

If the majority of the buffer busy waits wait events are centered on data blocks and the reason code is 220, this indicates there are multiple sessions performing DML on the same object at the same time. In addition, if the database block size is large (for example, 16K and above), it can only intensify this symptom as larger blocks generally contain more rows per block. There are three things you can do to minimize this problem:

• Reduce the level of concurrency or change the portioning method.
• Reduce the number of rows in the block.
• Rebuild the object in another tablespace with a smaller block size (Oracle9i Database and above).

Again, as mentioned earlier, it may not be practical to limit scalability by reducing the level of concurrency.

If the data blocks belong to tables or indexes, then consider rebuilding the objects to reduce the number of rows per block and spread the data over a larger number of blocks. For example, you can rebuild a table or an index with a higher PCTFREE. In some cases, we have rebuilt indexes with PCTFREE as high as 50 percent. The downside to this is that index range scans and index fast full scans will be slower. You can also alter the table to minimize the number of rows per block with the ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK command. Starting in Oracle9i Database, you can move or rebuild the object in another tablespace with a smaller block size. While these actions can minimize the buffer busy waits problem, they will definitely increase full table scans time and disk space utilization. As the saying goes, there is no such thing as a free lunch.

If the majority of the buffer busy waits wait events are centered on data segment headers (that is, the table or index segment header and not the undo segment header), this usually means some tables or indexes in the database have high segment header activities. Processes visit segment headers for two main reasons—to get or modify the process FREELISTS information and to extend the high watermark (HWM). There are three things you can do to minimize this problem:

• Increase the number of process FREELISTS and FREELIST GROUPS of the identified object.
• Make sure the gap between PCTFREE and PCTUSED is not too small.
• Make sure the next extent size is not too small.

The first step is to identify the segment name and type. They can be identified from the P1 and P2 parameters of the buffer busy waits wait event. Then you can alter the object to increase the number of process FREELISTS. If time and opportunity permit, you should also increase the number of FREELIST GROUPS by recreating the object. You should create all data segments with a minimum of two FREELIST GROUPS, by default—even in a single-instance database.
If you do not want to mess with FREELISTS and FREELIST GROUPS, you can rely on the Automatic Segment Space Management (ASSM) feature to scatter the incoming data from the insert statements.

Finally, you should check the next extent size of the identified segment. A high insert rate combined with a small next extent size can cause frequent insertion of new entries into the extent map located in the segment header. Consider altering or rebuilding the object with a larger next extent size. If the object resides in a locally managed tablespace, consider moving the object into a reasonable uniform-size locally managed tablespace.

If the majority of the buffer busy waits wait events are centered on undo segment headers, this indicates there are either too few rollback segments in the database or their extent sizes are too small, causing frequent updates to the segment headers. If you use the system-managed undo introduced in Oracle9i Database, you shouldn’t have to deal with this problem as Oracle will create additional undo segments according to demand.

If the majority of the buffer busy waits wait events are centered on undo blocks, this usually means there are multiple concurrent sessions querying data that is being updated at the same time. Essentially the query sessions are fighting for the read consistent images of the data blocks. This is an application issue and there is nothing amiss in the database. The problem should go away when the application can run the query and DML at different times.

Oracle maintains a number of instance-level statistics on buffer busy waits. These statistics can give you a rough idea of what you are dealing with, but the information may not be specific enough for you to formulate a corrective action. They are mentioned here for the sake of completeness.

The view X$KCBWAIT (kernel cache buffer wait) is the base view for the V$WAITSTAT view, which keeps track of buffer busy waits contentions by block class. The class with the highest count deserves your attention, but unless you are also monitoring the buffer busy waits symptom at a lower level, you don’t have a clear direction to proceed. Let’s say the data block class has the highest count of all. Which segment was affected most, and why were the sessions unable to pin the buffers? Did they fail to get the pins while attempting to read or change the blocks? Unfortunately, Oracle does not keep track of buffer busy waits by block class and reason code. We hope someday Oracle will provide a matrix of buffer busy waits by SQL statement, segment name, and block class.

How do I tune to reduce the log file sync wait events?

Commit is not complete until LGWR writes log buffers including commit redo recods to log files. In a nutshell, after posting LGWR to write, user or background processes waits for LGWR to signal back with 1 sec timeout. User process charges this wait time as ‘log file sync’ event.

Root causes of ‘log file sync’, essentially boils down to few scenarios and following is not an exhaustive list, by any means!

1. LGWR is unable to complete writes fast enough for one of the following reasons:

• Disk I/O performance to log files is not good enough. Even though LGWR can use asynchronous I/O, redo log files are opened with DSYNC flag and buffers must be flushed to the disk (or at least, written to disk array cache in the case of SAN) before LGWR can mark commit as complete.

• LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve for CPU too. This will lead to slower response from LGWR, increasing ‘log file sync’ waits. After all, these system calls and I/O calls must use CPU. In this case, ‘log file sync’ is a secondary symptom and resolving root cause for high CPU usage will reduce ‘log file sync’ waits.

• Due to memory starvation issues, LGWR can be paged out. This can lead to slower response from LGWR too.

• LGWR is unable to complete writes fast enough due to file system or unix buffer cache limitations.

2. LGWR is unable to post the processes fast enough, due to excessive commits. It is quite possible that there is no starvation for cpu or memory and I/O performance is decent enough. Still, if there are excessive commits, then LGWR has to perform many writes/semctl calls and this can increase ‘log file sync’ waits. This can also result in sharp increase in redo wastage’ statistics’.

3. IMU undo/redo threads. With Private strands, a process can generate few Megabytes of redo before committing. LGWR must write generated redo so far and processes must wait for ‘log file sync’ waits, even if redo generated from other processes is small enough.

4. LGWR is suffering from other database contention such as enqueue waits or latch contention. For example, we have seen LGWR freeze due to CF enqueue contention. This is a possible scenario however unlikely.

5. Various bugs. Oh, yes, there are bugs introducing unnecessary ‘log file sync’ waits.

It is worthwhile to understand and identify root cause and resolve it.

• First make sure, ‘log file sync’ event is indeed a major wait events.
• Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 (and usually it is).

SELECT   sid,
           event,
           time_waited,
           time_waited_micro
    FROM   v$session_event
   WHERE   sid = 3
ORDER BY   3 desc

SQL> / 

SID EVENT                               TIME_WAITED TIME_WAITED_MICRO
--- ----------------------------------- ----------- -----------------
  3 rdbms ipc message                       2889367        2.8894E+10
  3 log file parallel write                  295343        2953429267
  3 LGWR wait for redo copy                     843           8425950
  3 log file single write                        67            674960
  3 control file parallel write                  47            471217
  3 latch free                                    6             60546
  3 control file sequential read                  4             39457
  3 log file sequential read                      2             17374
  3 direct path write                             2             23908
  3 direct path read                              0                14

10 rows selected.

It is worth to note that v$session_event is a cumulative counter from 
instance startup and hence, this can be misleading. Difference between 
two snapshots from this view, for the same session, can be quite useful

• If excessive redo size is root cause, redo size can be reduced

• Solid State Disk devices also can be used if redo size is extreme. In that case, it is also preferable to decrease redo size
• If commit rate is higher, then decreasing commits is correct step but, in few case, if that is not possible, increasing priority of LGWR (using nice) or increasing priority class of LGWR to RT might provide some relief.

• If I/O bandwith is an issue, then doing anything other than improving 
  I/O bandwidth is not useful. Switching to file systems providing better 
  write throughput is one option. RAW devices are another option. Reducing 
  # of log file members in a group is another option as it reduces # of 
  write calls. But, this option comes with a cost. 
  

Ways to optimize sorting in Oracle

A sort area in memory is used to sort records before they are written out to disk. Increasing the size of this memory by increasing the value of the initialization parameters SORT_AREA_SIZE or PGA_AGGREGATE_TARGERT, lets you sort more efficiently. SORT_AREA_SIZE is a dynamic parameter that can be set at either the session level using an ALTER SESSION command or system wide using an ALTER SYSTEM command. PGA_AGGREGATE_TARGET is an initialization parameter that defines the total size of all working areas, including the sort area, for all sessions. This parameter is available in Oracle 9i and 10g.


Sorting is performed by the following statements:

• CREATE INDEX
• GROUP BY
• ORDER BY
• INTERSECT
• MINUS
• UNION
• DISTINCT
• Unindexed table joins
• Some correlated subquerie

If the data being sorted does not fit in memory, Oracle must sort it in small runs. As each run is completed, Oracle stores the data in temporary segments on disk. After all of the runs have completed, Oracle merges the data to produce the sorted data. This is less efficient than doing the entire sort in memory.

In general, try to allocate as much space in memory as possible for SORT_AREA_SIZE (assuming that your entire sort can fit into memory). When defined, this parameter will cause memory to be allocated from the Process Global Area (PGA) unless the database is using Oracle’s Shared Server architecture (called Multi Threaded Sever (MTS) prior to Oracle9i). If the Oracle Shared Server architecture is being used, then the sort memory will be allocated from the large pool so long as the large pool is allocated. The sort memory will be allocated alternatively from the User global area (UGA) in the shared_pool if the large pool is not available. 

Consider using a two megabyte SORT_AREA_SIZE when your sorted data exceeds 100 megabytes in size. Because SORT_AREA_SIZE is allocated per user, increasing this parameter can exhaust memory very quickly if a large number of users are logged on. You also need to make sure that the temporary segments to which the sort operation will write its output (if it runs out of memory) are large enough, with appropriately sized INITIAL and NEXT extents. Again, the PGA_AGGREGATE_TARGET method of allocating working areas helps with this problem.


To find out whether sorting is affecting performance in your system, monitor the sorting disk activity in your system and then adjust accordingly. One good way to do this is to define a separate tablespace for temporary tables. By watching the I/O rate on the temporary tablespaces, you can detect how frequently the sort process failed to perform the entire sort in memory. You can monitor the V$SYSSTAT table to observe memory and disk activity.
Below is an example of querying the V$SYSSTAT table:

SQL> select name, value from v$sysstat where name like 'sort%';

      NAME                                                         VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                         1658
sorts (disk)                                                              2
sorts (rows)                                                        2232346

The "sorts(memory)" statistic shows the total number of sort operations that could be performed completely within the sort buffer in memory, without using the temporary tablespace segments. The "sorts(disk)" statistic shows the number of sort operations that could not be performed in memory. (Note that this number does not represent the total number of times a temporary table extent was written by a sort process.) Out of a total of 1293 sorts, only two required disk usage.


Using dedicated temporary tablespaces (created via the create temporary tablespace command) can provide some performance improvements with regards to sorting. This is because the temporary sort segments, once used, will not be removed but will instead be reused by the next process that needs to do a sort to disk. This eliminates the overhead of having to remove and recreate sort segments. This also means that your temporary tablespace may appear to contain segments in it most of the time. This is perfectly natural.
Also, consider in Oracle 9i and later, assigning a default temporary tablespace to the database. This has the benefit of allowing you to assign users to a temporary tablespace other than SYSTEM automatically when the user is created. Sorts occurring in the SYSTEM tablespace can badly fragment the tablespace and can cause other performance related problems.


Your goal should be to always reduce or eliminate sorts completely. Again, this starts at the application layer. Here is a list of suggested things you can look for in application code, which might be causing unneeded sort operations. While these different operations may well be needed, it’s a good idea to review your code and make sure.

1. Avoid using the DISTINCT clause unless necessary.
2. Use the UNION ALL clause in place of the UNION clause unless duplicates need to be eliminated.
3. Try to use HASH JOINS instead of SORT MERGE JOINS. The use of hints will cause the optimizer to choose this join.
4. Use appropriate index hints to avoid sorts.
5. The cost based optimizer will try to avoid a sort operation when the FIRST_ROWS hint is used.
6. Make sure that your SQL query is taking advantage of the best available indexing options.
7. Review operational SQL code for unneeded sort operations, such as order by clauses.

Follow these suggestions if your monitoring shows that you have a sorting problem:

1. Check if the sort is really necessary. Has an index been inadvertently overlooked? Can a SQL statement be structured more efficiently?
    
2. Increase the value of the SORT_AREA_SIZE parameter. Because this increase applies to all user processes, this is likely to consume a lot of memory. Make sure you don't increase the value of SORT_AREA_SIZE to the point where you have little free memory. The maximum allowable value is system-dependent. If you are using the PGA_AGGREGATE_TARGET method of work area allocation, then check the V$PGASTAT view to ensure that this parameter is set correctly.
    
3. Verify your temporary tablespace extents are large enough. Make sure that you specify large enough table extents (in the INITIAL and NEXT parameters on the CREATE statement) to allow SORT_AREA_SIZE of memory to be written to disk without having to throw multiple extents. Make your temporary segments a minimum of SORT_AREA_SIZE + 1 block. It is a very good idea to use Locally Managed Tablespaces with the UNIFORM extent allocation method for your temporary tablespaces. This way, you ensure that the extent sizes are the same for every sort segment.
    
4. Let users who require larger sorts use a temporary tablespace with larger INITIAL and NEXT default tablespace storage parameters. This is a less likely, but possible, alternative which will help reduce the degree of dynamic extension. For example, during daily online transaction processing hours, set your default settings to:
   

   ALTER TABLESPACE temp_tspace DEFAULT STORAGE
   (INITIAL 260K  NEXT 260K  PCTINCREASE 0);

   
   For overnight processing, you might set the default storage to:
   

   ALTER TABLESPACE temp_tspace DEFAULT STORAGE
   (INITIAL 5M  NEXT 5M  PCTINCREASE 0);

   
   If you are using Locally Managed Tablespaces, then you will not be able to modify the default storage clause as shown above. Instead, create two Locally Managed temporary tablespaces, one with smaller extents and the other with larger extents. Instead of altering the default storage clause information above, you would modify the default temporary tablespace for the entire database (in Oracle 9i and 10g). For overnight processing, you would issue a command Similar to the following:
   

   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE big_temp;

5. Set your INITIAL and NEXT extent sizes to one block plus a multiple of the sort area size. This will achieve minor improvements in response times. Assuming that you have a DB_BLOCK_SIZE of 4K and a SORT_AREA_SIZE of 64K, you may consider any of the following sizes or a higher size, depending on your requirements:
   

   4K  +  (1 * 64K)  =   68K
   4K  +  (2 * 64K)  =  130K
   4K  +  (3*  64K)  =  196K
   4K  +  (4 * 64K)  =  260K

   If you are using Locally Managed Tablespaces, set the uniform extent size to one of the above values.
   
6. Make sure to use the SORT_AREA_RETAINED_SIZE. Oracle will restore the sort area available to user processes to the size specified in this parameter if it believes that the sort area data will not be referenced in the near future. This will save memory. If memory is tight, we highly recommend that you take advantage of this feature by setting your SORT_AREA_RETAINED_SIZE to half the SORT_AREA_SIZE. For example, you might set:
   

   SORT_AREA_SIZE           = 131072   (128K)
   SORT_AREA_RETAINED_SIZE  = 65536     (64K)

7. Use the TEMPORARY type when creating tablespaces. This tablespace type is tuned especially for sorting and will boost your performance. We recommend that you use it as your users' TEMPORARY tablespace.

   

ORACLE PARAMETERS TO IMPROVE CURSOR PROCESSING PERFORMANCE

For applications where literals are being used instead of bind variables, leading to unnecessary hard parsing, the parameter cursor_sharing can be used.  The performance of such applications is primarily influenced by the hard parse necessary.  By default, Oracle will behave doing a hard parse for each new SQL statement.  If, however, these SQL statements could be shared had the literal(s) been replaced by a bind variable, setting the parameter to one of the non-default values will change this behavior.  Doing this will bring
some of the benefit of coding applications with bind variables instead of literals.

The cursor_sharing parameter can be set to one of three values:

• exact  SQL statements are only shared if they are exactly identical. This is the default

• force  If SQL statements only differ in literal values, they will be shared as if the literals had been bind variables.  This will be done unconditionally.

• similar  Causes cursor sharing to take place when this is known not to have any impact on optimization.

The parameter can be set at the system level using the alter system command or at the session level using the alter session command.

It is highly recommended not to write applications dependent on this parameter, and to use the parameter only when needed for existing applications incorrectly using literals. 

Applications identified by repeated (soft) parse of identical SQL statements.  On the Oracle server side, this has the implication that the same values repeatedly are assigned to the server side information about cursors.  This behavior can be modified by allowing the server to keep information available for frequently parsed SQL statement, at the expense of the need to lookup such SQL statements. 
The parameter session_cached_cursors can be used to do exactly that:  If set to an integer value, the Oracle server engine will attempt to keep that many cursors in each session parsed and ready for execution.  Appropriate values for the session_cached_cursors parameter depend on the Oracle release. 
In Oracle9i Release 2 and later, values as high as several hundreds can be used, in earlier releases, values above 10 to 20 are not likely to be useful as more CPU usage has a tendency to negate the effect of increased scalability using the parameter. 

Generate monitoring scripts for all indexes

select 'alter index '||index_name||' monitoring usage' from user_indexes where
      index_type=’NORMAL’;

Script to identify queries having full table scan

SELECT   sql_text
    FROM   v$sql
   WHERE   (address, hash_value, child_number) IN
                 (SELECT   address, hash_value, child_number
                    FROM   v$sql_plan
                   WHERE   operation = 'TABLE ACCESS' AND options = 'FULL')
ORDER BY   sql_text

Fourthelephant Insider for Oracle ! the best alternative for Quest Spotlight on Oracle

Insider is a real-time performance tuning solution for Oracle. It allows you to monitor all aspects of Oracle internal workings and presents this in a revolutionary and sleek user interface.

Oracle Instance View
Oracle Instance view is a detailed real-time snapshot of an Oracle database showing performance and other critical information for a single Oracle instance

Oracle Alert Log
Whenever an error is discovered in the Oracle alert log an alert is shown on instance view and it can be opened in the separate window.

System Configuration
Configuration subview shows other important Oracle database and instance configuration data as well as licensing, options and NLS parameters.

 System Statistics Chart
System statistics charts subview allows to select any number of Oracle statistics and add them to a drill-down chart. A month worth of statistics history can be analyzed.

Enterprise View
Enterprise view is the starting point where you add, remove and arrange the items you would like to monitor, view some basic information about them, enable and disable items, view and configure alerts.

Oracle Context Menu
From Oracle Context Menu you can start and stop Oracle instance, start SQL*Plus and perform some other DBA functions.

Availability Report
You can generate an availability report for any server Insider is monitoring.

External Tools
You can easily extend Insider functionality by configuring external tools such as SQL*Plus, ssh or SQL Developer right on an Enterprise View item.

Tools Configuration
External tools can be configured with various variables, such as host name, oracle username etc.

100+ Configurable Alerts
You can disable alerts for all databases, you can disable them for a particular database and you can change alerts thresholds.

Alerts MiniHelp
 Alerts come with a small description and/or recomendation on how to fix a problem

Operating System View
Operating system overview is a detailed real-time snapshot of an OS server showing performance and other critical information for a single OS.

Processes
Processes view shows a list of operating system processes currently running.

Storage View
Operating system storage view shows the drives (mounts) and space usage statistics.

Sessions
Operating system sessions view shows all currently connected operating system users and the details of their connection such as login time, idle time and IP address.

Secure Shell
 Shell view is a special type of view in that it allows you to login to you operating system host using your login and password you entered when adding an Enterprise view item and execute any command that your user account is configured to access.