Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Monitoring Database Writer Contention in
Oracle8i and Oracle

Oracle Tips by Burleson

As we know, the “write requests” statistic has been removed from the v$sysstat view. It has been removed because the use of multiple buffer pools and asynchronous DBWR processes now mean that the number of write requests is no longer useful. Hence, we must look at new v$sysstat statistics for monitoring the database writer.

Oracle8i now provides the free buffer wait statistic in stats$buffer_pool_statistics. This statistic stores the number of times a free buffer was requested in the SGA, but a block was not available. A free buffer is a buffer that is not currently being used by other database users. If accumulated time for the free buffer wait event is too high then you can consider adding database writer processes.

rpt_dbwr_alert_8i.sql (post Oracle 8.0.5 only)

-- Written by Donald K. Burleson   1/25/01

set pages 999;
column c1 heading "Dirty Queue Length"   format 9,999.99
column c2 heading "Free Buffer Requests" format 999,999,999
column c3 heading "DBWR checkpoints"     format 999,999
column mydate heading 'Yr.  Mo Dy  Hr.'  format a16

select distinct
   to_char(snap_time,'yyyy-mm-dd HH24') mydate,
   a.value                              c1,
   b.free_buffer_wait                   c2,
   c.value                              c3
   stats$sysstat                 a,
   stats$buffer_pool_statistics  b,
   stats$sysstat                 c,
   stats$snapshot                sn
   sn.snap_id = a.snap_id
   sn.snap_id = b.snap_id
   sn.snap_id = c.snap_id
and = 'summed dirty queue length'
and = 'DBWR checkpoints'
   a.value > 100
   b.free_buffer_wait > 0

Here is a listing from this script.  This will alert us when the DBWR processes may be experiencing a problem.

Yr.  Mo Dy  Hr.  Summed dirty queue   Free Buffer Wait   DBWR checkpoints                 
---------------- -------------------- ----------------- ----------------                           
2001-11-14 08                     .00           505,818                0                           
2001-11-14 08                     .00           505,690                0                           
2001-11-15 07                     .00           391,550                0                           
2001-11-16 07                     .00           392,046                0                           
2001-11-17 19                     .00           395,216                1                           
2001-11-20 09                     .00            38,096                0                           
2001-11-20 10                     .00            56,626                0                           
2001-11-20 11                     .00            59,272                1                           
2001-11-20 12                     .00            59,409                1                            
2001-11-20 13                     .00            61,707                2                           
2001-11-20 14                     .00            67,078                2                           
2001-11-20 15                     .00            81,833                3                           
2001-11-20 16                     .00            82,594                4                           
2001-11-20 17                     .00            83,143                4                            
2001-11-20 18                     .00            84,641                4                           
2001-11-20 19                     .00            84,696                4                           
2001-11-20 20                     .00           692,868                5                           
2001-11-20 21                     .00           692,929                5                           
2001-11-21 00                     .00             2,626                0                            
2001-11-21 01                     .00           387,783                0                           
2001-11-21 02                     .00           389,422                0                           
2001-11-21 03                     .00           389,495                0                           
2001-11-21 04                     .00           390,562                0                           
2001-11-21 05                     .00           391,884                0                            
2001-11-21 06                     .00           392,445                0               

Now that we understand DBWR, let's look into another important area, the detection and correction of buffer busy waits.

Tuning the Shared Pool

The shared pool component of the Oracle SGA is primarily used to store shared SQL cursors, SQL source and execution plans, stored procedures, and session information, as well as to function as a RAM cache for the data dictionary and library. The following section will look at the relevant parameters that govern the shared pool and show how STATSPACK can be used to monitor and help tune the shared pool region of RAM.

The size of the shared pool is controlled with the shared_pool_size Oracle parameter. This parameter is most often adjusted in response to poor statistics for library cache hits or high SQL reparsing within the library cache, but the shared pool is unlike any other Oracle structure.

There are numerous times when making the shared pool smaller will dramatically improve performance. In fact, many Oracle instances with nonreusable SQL are configured with shared_pool_size. Optimally, this setting should be large enough to retain all the reusable library cache objects plus enough to accommodate the maximum total of concurrent nonsharable object sizes.

On the surface, it is counterintuitive that the shared pool performance will get worse as the size increases. In all other areas of Oracle memory, more RAM memory will always result in better performance. The explanation is that memory buffers usually assists performance by eliminating physical disk access. However, in the case of the shared pool and literal SQL, an inverted CPU-memory trade-off develops where more memory actually results in larger data structures to manage with more management overhead due to the nonsharing of library cache objects. This overhead is CPU intensive, so with enough SQL pressure, the system becomes CPU bound and performance suffers drastically.

A landmark experiment titled “Piranhas in the Pool” by John Beresniewicz (Oracle Internals, 2000) clearly demonstrated this phenomenon and offered an explanation for this behavior. This paper noted that the failure of SQL statements to utilize bind variables directly contributed to poor performance within the shared pool. Conversely, the use of bind variables (resulting in identical SQL) creates many fewer library cache objects and rapid hashing to the matching object. The library cache latch is thus released more quickly, reducing the latching impact of the library cache latch.

cursor_sharing and the Shared Pool

Starting in Oracle8i (8.1.6), Oracle introduced an exciting new Oracle parameter called cursor_sharing. cursor_sharing is designed to help manage the kinds of problems inherent with nonsharable SQL. The cursor_sharing parameter has the following values:

  • FORCE Library cache object matching based on exact SQL (or PL/SQL) text match as in pre-8.1.6 Oracle.

  • EXACT Oracle automatically substitutes bind variables to replace literals in SQL statements before library cache object matching takes place, causing increased sharing of literal SQL.

  • SIMILAR Oracle has this option to detect substantially similar SQL statements that differ only in the values of host variables.

When cursor_sharing is set to FORCE, Oracle adds an extra parsing process that identifies statements that would be equivalent if they did not contain literal values in the SQL. For systems with dynamic SQL with embedded literal values, the cursor_sharing parameter will greatly improve performance.

For example, suppose the following statement was stored in the shared pool:

select * from customer where last_name = 'Burleson' and first_name = 'Don';

With cursor_sharing = FORCE, the following statement will be recognized as identical to the first:

select * from customer where last_name = 'Ault' and first_name = 'Mike';

The cursor_sharing facility will translate the first statement into a host variable equivalent and use its execution plan to execute the second statement:

select * from customer where last_name = :var1 and first_name = :var2;

The effects of this parameter on systems with lots of literal SQL is astounding. Beresniewicz confirmed that using cursor_sharing results in performance advantages similar to those obtained using bind variables. His results confirmed the following performance gains:

  • Reduced library cache impact

  • Negligible shared pool activity

  • Reduced CPU demands

Thus, it is clear that cursor_sharing can be used to significantly enhance the performance of high-volume literal SQL and is a great boon to the DBA saddled with such applications.

John Beresniewicz reached the following conclusions:

  • The library cache and shared pool memory manager are integral components of the Oracle server designed to create efficiencies and thereby improve performance through the caching and reuse of CPU-intensive steps during SQL processing.

  • Applications characterized by high volumes of literal (nonsharable) SQL can compromise these efficiencies, induce additional overhead, and degrade performance.

  • Experimental results confirm that this is the case and support the explanation that contention for the shared pool and library cache latches plays a major role in this problem.

  • The new cursor_sharing initialization parameter introduced in Oracle 8.1.6 addresses and corrects the performance impact of literal SQL by converting it to bind variable format before library cache object matching is undertaken. Results show that this new feature does indeed correct for the performance penalty of literal SQL and promises to be a “silver bullet” for DBAs burdened with pathologically nonsharable SQL.

Now that we see the benefits of cursor_sharing on shared_pool performance, let's look at some STATSPACK scripts that monitor the performance of the shared pool and the library cache.

Instance Event Waits and STATSPACK

There are several tables within STATSPACK that can be used to give us insight into contention problems within the Oracle instance.

The stats$system_event table contains detailed statistics about system events and the time that they have to wait for service. Unfortunately, the stats$system_event table contains internal events such as ipc messages that are not relevant to tuning. Fortunately, the DBA can filter through the events in the stats$system_event table and only report on these events that are interesting to the DBA. These events include:

  • Latch-free waits

  • Enqueue waits

  • Buffer busy waits

  • Log writer waits for redo copy latches

  • SQL*Net communications events

We are interested in finding times when these events experience an excessive amount of waits and time waiting. The following STATSPACK script will identify those events where time waited is greater than 100 or total waits for the event exceed 100:


set pages 999;
set lines 80;

column mydate heading 'Yr.  Mo Dy Hr'     format a13;
column event                              format a30;
column waits                              format 999,999;
column secs_waited                        format 999,999,999;
column avg_wait_secs                      format 99,999;

break on to_char(snap_time,'yyyy-mm-dd') skip 1;

   to_char(snap_time,'yyyy-mm-dd HH24')           mydate,
   e.total_waits - nvl(b.total_waits,0)           waits,
   ((e.time_waited - nvl(b.time_waited,0))/100) /
   nvl((e.total_waits - nvl(b.total_waits,0)),0)  avg_wait_secs
   stats$system_event b,
   stats$system_event e,
   stats$snapshot     sn
   e.snap_id = sn.snap_id
   b.snap_id = e.snap_id-1
   b.event = e.event
   e.event like 'SQL*Net%'
   e.event in (
      'latch free',
      'LGWR wait for redo copy',
      'buffer busy waits'
   e.total_waits - b.total_waits  > 100
   e.time_waited - b.time_waited > 100

Here is the output from this script. As you can see, someone was running a large task on January 22 that stressed several areas within the shared pool and communications subsystem.

Yr.  Mo Dy Hr EVENT                             WAITS AVG_WAIT_SECS
------------- ------------------------------ -------- -------------
2001-01-22 20 SQL*Net message from client     119,432             1
2001-01-22 20 SQL*Net more data from client       592             0
2001-01-22 20 buffer busy waits                   605             0
2001-01-22 20 enqueue                             826             0
2001-01-22 20 latch free                      128,343             0
2001-01-22 21 SQL*Net message from client      30,249             3
2001-01-22 21 SQL*Net more data from client       253             0
2001-01-22 21 enqueue                             740             0
2001-01-22 21 latch free                       77,247             0

In most cases, the resolution to these problems is to add additional blocks to the shared_pool, but we can always go back to the stats$sql_summary table to see what SQL precipitated the problems. Next, let's look at how STATSPACK can be used to monitor background events within the instance.

Monitoring Background Events with STATSPACK

The stats_bg_event summary table provides a wealth of information regarding important background events. To see the list of events, we query the stats_bg_event summary table.

 1* select distinct event from STATS$BG_EVENT_SUMMARY
SQL> /

buffer busy waits
checkpoint range buffer not saved
control file parallel write
control file sequential read
db file parallel write
db file scattered read
db file sequential read
direct path read
direct path write
file identify
file open
free buffer waits
latch free
library cache pin
log file parallel write
log file sequential read
log file single write
log file switch completion
log file sync
pmon timer
process startup
rdbms ipc message
rdbms ipc reply
smon timer
write complete waits

The next step is to run a STATSPACK query that will report on those events that exceed our predefined threshold. Note that we remove the timer and message events since these are seldom of interest when tuning a database.


set pages 999;
set lines 80;

column mydate heading 'Yr.  Mo Dy Hr'     format a13;
column event                              format a30;
column total_waits    heading 'tot waits' format 999,999;
column time_waited    heading 'time wait' format 999,999;
column total_timeouts heading 'timeouts'  format 9,999;

break on to_char(snap_time,'yyyy-mm-dd') skip 1;

   to_char(snap_time,'yyyy-mm-dd HH24')           mydate,
   e.total_waits - nvl(b.total_waits,0)           total_waits,
   e.time_waited - nvl(b.time_waited,0)           time_waited,
   e.total_timeouts - nvl(b.total_timeouts,0)     total_timeouts
   stats$bg_event_summary     b,
   stats$bg_event_summary     e,
   stats$snapshot     sn
   e.event not like '%timer'
   e.event not like '%message%'
   e.snap_id = sn.snap_id
   b.snap_id = e.snap_id-1
   b.event = e.event
   e.total_timeouts > 50
   e.total_waits - b.total_waits  > 50
   e.time_waited - b.time_waited > 50

Here is the output where we see the time, the event name, the total waits for the event, the time waited, and the total timeouts for the event:

Yr.  Mo Dy Hr EVENT                       tot waits time wait timeouts
------------- --------------------------- --------- --------- --------
2001-12-28 12 buffer busy waits                  52         0       45
2001-01-01 00 buffer busy waits                  62         0       60
2001-01-01 01 buffer busy waits                  53         0       49
2001-01-04 01 buffer busy waits                  52         0       46
2001-01-07 18 rdbms ipc reply                    85         0       54
2001-01-08 17 latch free                         95         0       95
2001-01-17 23 buffer busy waits                  82         0       76
2001-01-17 23 latch free                         79         0       78
2001-01-21 23 latch free                         51         0       51
2001-01-22 13 latch free                         55         0       55
2001-01-22 14 latch free                         52         0       52
2001-01-22 20 latch free                         60       104       60

This report can provide vital clues for areas of contention within our database. This script is incorporated into the generic statspack_alert.sql script, so the DBA is always aware of out-of-bounds conditions inside the database.

Next, let's examine the most important areas of the shared pool, the library cache.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter