Search Oracle Related Sites

Friday, April 29, 2011

AWR TOP 5 Timed Event Analysis - Global Cache Wait Events

The Global Cache Service is the controlling process that implements Cache Fusion. It maintains the block mode for blocks in the global role. It is responsible for block transfers between instances. The Global Cache Service employs various background processes such as the Global Cache Service Processes (LMSn) and Global Enqueue Service Daemon (LMD)


Global cache (“gc” ) events and statistics Indicate that Oracle searches the cache hierarchy to find data fast as “normal” as an IO ( e.g. db file sequential read )

GC events tagged as “busy” or “congested” consuming a significant amount of database time should be investigated. At first, assume a load or IO problem on one or several of the cluster nodes.


  

 
 
 
 
 
All Global Cache Events will follow the following format:


RAC wait events are grouped in a category called “Cluster Wait Class” characterized as Current or CR.

• Current - blocks read into memory for the first time
• Consistent Read (CR) - denotes block for read access

The following wait events shows that the remotely cached blocks were shipped to the local Instance without having been busy, pinned or requiring a log flush. In a simple way buffer requests and received for read or write

gc current block 2-way
gc current block 3-way
gc cr block 2-way
gc cr block 3-way

GC cr/current block congested
  • Repeated requests by foreground processes, not serviced by LMS
  • Indicates LMS not able to keep up
  • Queue lengths & scheduling delays in OS, can cause LMS delays

GC cr/current block busy

  •  Delay for some reason, before block sent to requestor

 GC current grant busy

  •  Permission to access the block granted, but blocked by other requests ahead of it

 GC cr/current block request

  • Wait time, cr or current block is being retrieved 
How do we intrepet the AWR further for the GC Wait Event problems.

     
    Check where most of the time in the database is spend (“Top 5 Timed Events” )
    Check whether gc events are “busy”, “congested”  --  Check the avg wait time  --  Drill down the AWR further for finding the top wait events --  SQL with highest cluster wait time  --- Segment Statistics with highest block transfers
    .
Interconnect issues must be fixed first


If IO wait time is dominant , fix IO issues

At this point, performance may already be good

Fix “bad” plans

Fix serialization

Fix schema 

No comments: