Re: "gc buffer busy" wait Cause ?

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 (http://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

--
http://www.freelists.org/webpage/oracle-l