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
(//www.freelists.org/archives/oracle-l/02-2007/msg01118.html), 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
index?
==> 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:
http://download-uk.oracle.com/docs/cd/B19306_01/rac.102/b14197/monitor.htm#CHDGDAFG
"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."
Investigate
- 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
level.
- 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
http://preferisco.blogspot.com/2006/04/micro-partitioning-pooled-sessions-in.html
for more on this (in the context of OC4J - but the same principle applies to
any session pool using RAC).
HTH
Regards Nigel
Other related posts:
