ALERT: LATCH FREE And FREE_BUFFER_WAITS Cause Performance Degradation/Hang (文档 ID 176129.1)

Performance Degradation/Hang Caused By “LATCH FREE” And “FREE_BUFFER_WAITS”
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Versions Affected
~~~~~~~~~~~~~~~~~
   Bug:1967363 - Affects 8.1.5.x, 8.1.6.x, 8.1.7.0, 8.1.7.1, 8.1.7.2
                 and 9.0.1.0, 9.0.1.1 and 9.0.1.2
                 Fixed in 8.1.7.3, 9.0.1.3 and 9.2

   Bug:2079526 - Affects 8.1.7.0 - 8.1.7.3 inclusive, 9.0.1.0, 9.0.1.1
                 and 9.0.1.2
                 Fixed in 8.1.7.4, 9.0.1.3 and 9.2

Platforms Affected
~~~~~~~~~~~~~~~~~~
   GENERIC

Description
~~~~~~~~~~~
   Bug:2079526 - FREE BUFFER WAITS DURING CHECKPOINT
   If the  number of dirty buffers that need to be written to disk is 
   larger than the number that dbwr can write per batch, this bug may 
   cause degradation in database performance.  The wait events in 
   V$session_wait or statspack will show lots of waits on 'latch free'
   (for "cache buffers chain" latches) and/or waits on 'free buffer 
   waits'.


   Bug:1967363 - "CACHE BUFFERS CHAINS" LATCH CONTENTION AFTER UPGRADE TO 8.1 / 9.0
   Queries involving index scan in 8.1 and onwards can cause constant 
   unpinning and pinning of the index root block after each fetch. 
   This causes lots of extra buffer cache gets and other resource 
   usage.


Likelihood of Occurrence
~~~~~~~~~~~~~~~~~~~~~~~~
   Both bugs are likely to have an impact on high usage OLTP systems
   on which there is a lot of DML activity.

   To check if this issue may be affecting you, use STATSPACK or
   BSTAT/ESTAT reports to see if there are significant waits for
   "free buffer waits" and/or if there is contention for the
   "cache buffers chains" latches.

Possible Symptoms
~~~~~~~~~~~~~~~~~

 No error messages are reported.  The database performance will degrade
 with any of these symptoms:
   - sporadic slow-down
   - users experience very slow response time
   - sessions are waiting for latch free and free buffer waits.

 The following queries will help you to confirm the problem(s).

Possible symptoms for bug 2079526
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Querying V$SESSION_WAIT during the problem period will show 
 significant "latch free" waits and "free buffer waits".

   eg:

      select count(*), event from v$session_wait
      where state='WAITING' group by event order by 1 desc;

   COUNT(*) EVENT
 ---------- ------------------------------
        204 latch free
        118 SQL*Net message from client
        105 free buffer waits
         48 buffer busy waits
         26 enqueue
         14 row cache lock
         12 rdbms ipc message
          4 write complete waits
          1 pmon timer

 The "latch free" waits will be for the "cache buffers lru chain" and 
 "cache buffers chains" latches.  This can be confirmed by querying the
 P2 column of V$SESSION_WAIT for the latch waits.

 eg:
      select count(*), name latchname from v$session_wait, v$latchname
      where event='latch free' and state='WAITING' and p2=latch#
      group by name order by 1 desc;

   COUNT(*) LATCHNAME
 ---------- ------------------------------
        128 cache buffers lru chain
         76 cache buffers chains


Possible symptoms for BUG:1967363
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 There is no simple way to assess the impact of this bug.
 The sympoms are best demonstrated by way of an example

Consider the following Example...


 If "METIN_IDX" is a heavily used index in the system then a query like
 the one below shows the latch activity for the "child buffers chain"
 child latch which covers the hash chain for the index root block.

 Look for the GETS for this child latch after running the application
 for a while with and without the fix:

    select /*+ ordered */ l.CHILD#,l.ADDR,l.GETS,l.MISSES,l.SLEEPS
    from sys.dba_extents e, sys.x$bh x, sys.v$latch_children l
    where 
    e.SEGMENT_NAME = 'METIN_IDX' and e.EXTENT_ID = 0 and
    x.FILE# = e.FILE_ID and x.DBABLK=e.BLOCK_ID+1 and
    x.HLADDR = l.ADDR;

  (Note: The query above is a simple example which assumes that the
   root index block is the second block in the first extent. This may not 
   be the case if the segment header is more than one block)

 
  Output without the fix:

          CHILD# ADDR             GETS       MISSES       SLEEPS
    ------------ -------- ------------ ------------ ------------
             554 82949F74       20,743            0            0

  Output after the fix has been applied:

          CHILD# ADDR             GETS       MISSES       SLEEPS
    ------------ -------- ------------ ------------ ------------
             554 82949F74           74            0            0


Workaround
~~~~~~~~~~
  None

Patches
~~~~~~~
  Apply the 8.1.7.4 patchset or upgrade to 9.0.1 and apply the 9.0.1.3 patchset.  

  If the patchset is not available for your platform, contact Oracle
  Support Services to find out if a one-off patch is available.

  Instructions for Downloading Patchsets from MetaLink

  1. Login to MetaLink - http://metalink.oracle.com
  2. Choose Patches from the Menu.
  3. Input 2079526 or 1967363 inthe Patch Number field.
  4. Click the Submit button.

References
~~~~~~~~~~
  Bug:2079526 FREE BUFFER WAITS DURING CHECKPOINT
  Bug:1967363 "CACHE BUFFERS CHAINS" LATCH CONTENTION AFTER UPGRADE TO 8.1.7


Modification History
~~~~~~~~~~~~~~~~~~~~
  05-Mar-2002  Corrected fixed in to 9.0.1.3 and removed bad formating
  12-Mar-2002  Corrected misspelling of performance
  01-Jul-2002  Updated the example's output.

______________________________________________________________________________
                                                      Oracle Support