Re: "gc buffer busy" wait Cause ?

  • From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
  • To: VIVEK_SHARMA@xxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 14 Mar 2007 13:14:08 -0700 (PDT)

Vivek - some responses in line:

Qs What is the Cause of “gc buffer busy” wait? Is it always due to a DML i.e. 
“INSERT / UPDATE” or can it be due to a “SELECT” too?
==> As mentioned a few days ago by Mladen 
(//, anything 
gc% means it's a RAC issue;

NOTE - IDX_LOGIN_TABLE (on session_id), IDX_LOGIN_VAL_ITEMS_ (on session_id), 
indexes belong to Tables which maintain unique session information of each 
logged in Application User.
==> Small and hot tables/indexes I presume - a deadly combination. Quite likely 
your index root is sizzling hot, or you have an index based on a sequence 

Qs Are dedicated server processes on the Different RAC nodes attempting to 
“concurrently” do DML on the SAME BLOCK/ROW of the Respective Table & hence 

==> yes (or at least, concurrent access to same BLOCK - can't be sure about 
whether one row/index entry or many in the same block).

Possible actions

- buy Gopal's Oracle Wait Interface book (I'll be asking for a commission soon 
- other RAC books have been mentioned before on this list (eg Julian Dyke's).
- If all else fails, RTFM:the RAC performance guide - eg:

"The contention-oriented wait event statistics indicate that a block was 
received which was pinned by a session on another node, was deferred because a 
change had not yet been flushed to disk or because of high concurrency, and 
therefore could not be shipped immediately. A buffer may also be busy locally 
when a session has already initiated a cache fusion operation and is waiting 
for its completion when another session on the same node is trying to read or 
modify the same data. High service times for blocks exchanged in the global 
cache may exacerbate the contention, which can be caused by frequent concurrent 
read and write accesses to the same data."


- which blocks are being hit - random or all the same? v$session_wait gives you 
the exact block - try monitoring that
- consider reverse key indexes if sequence-generated login ids are used (this 
at least is easy to test, and easy to back out) and/or increase sequence cache 
size (eg to 1000 or more so nodes tend to insert into different leaf blocks).
- partition these tables and/or move to hash or index organised tables and 
deliberately reduce rows/block; this won't work if contention is really at row 
- if possible, see if these tables are really necessary. Why is session 
information maintained in a table; can't it be maintained in the application, 
for example? 

Another thing to consider - partition your session pool (if you have one) so 
that in normal operation a single user's transactions are almost always 
executed on the same instance. See
 for more on this (in the context of OC4J - but the same principle applies to 
any session pool using RAC). 


Regards Nigel

Other related posts: