Re: gc buffer busy acquire madness

In addition to what Mark said...

The event "gc buffer busy" is the RAC extension of the "buffer busy wait" event, which means that you are having buffer/block contention between instances, a.k.a. "hot block" contention.

If you can obtain a standard AWR report (preferably for all instances), then look toward the bottom of the report in the section of the report entitled "Segment Statistics", and focus specifically on the section entitled "Segments by Global Cache Buffer Busy", which should identify the segments where the "hot blocks" reside.

If you cannot obtain an AWR report, then a query like...

    col cnt format 999,999,990
    col pct format 990.00
    select p1, p2, count(*) cnt, ratio_to_report(count(*)) over () * 100 pct     from gv$active_session_history
    where event like 'gc buffer busy%'
    group by p1, p2
    order by pct desc;

...would give you an idea of such "hot blocks" recently encountered. You can substitute the view "dba_hist_active_sess_history" for "gv$active_session_history" if you don't have any recent occurrances.

Once you have the P1 and P2 values (i.e. FILE# and BLOCK#) of the "hot blocks", then you can translate them to object names using a query like...

    select * from dba_extents where file_id = <p1-value> and <p2-value>     between block_id and (block_id + (blocks-1));

Hope this helps...

Happy hunting!

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.

   postal   => PO Box 352151, Westminster CO 80035-2151 USA
   email    => Tim_at_EvDBT.com
   mobile   => +1 (303) 885-4526
   blog     => http://www.EvDBT.com/
   LinkedIn => http://www.LinkedIn.com/in/TimGorman
   Twitter  => timothyjgorman
board      -> Rocky Mtn Oracle Users Group (www.RMOUG.org)
board      -> Oracle Developers Tools Users Group (www.ODTUG.com)
board      -> Project SafeGuard (www.PSGHelps.org)
member     -> OakTable Network (www.OakTable.net)

Oracle ACE Director (www.oracle.com/technetwork/community/oracle-ace)

Lost Data? => www.ora600.be/ for info about DUDE...

On 12/9/2013 11:21 AM, Mark Bobak wrote:
> Yes......
>
> Some thoughts/questions that might get you pointed in the right direction.
>
> 'gc buffer busy' usually means you're shipping excessive traffic over
> the interconnect.
>
> Some questions:
>
> Are you (at least attempting to) isolate/partition your workload, so
> that specific work runs on a specific RAC node?
> Are the CPUs really busy? Is there a CPU bottleneck?
> How large is the SGA? Are you using hugepages?
>
> -Mark
>
> From: "Nadeem M. Khan" <nadeem.m.khan_at_gmail.com
> <mailto:nadeem.m.khan_at_gmail.com>>
> Reply-To: "nadeem.m.khan_at_gmail.com <mailto:nadeem.m.khan_at_gmail.com>"
> <nadeem.m.khan_at_gmail.com <mailto:nadeem.m.khan_at_gmail.com>>
> Date: Monday, December 9, 2013 at 12:57 PM
> To: "oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>"
> <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>>
> Subject: gc buffer busy acquire madness
>
> Oracle 11.1.0.7 RAC on Linux with two instances prod1 and prod2
> When one instance is open, the second instance takes 40 minutes to
> open from the mount stage.
> When both instances are open, queries take a long time to complete on
> prod1
> When only one instance is open, queries run normally.
>
> My top 5 waits are:
>
> DAY EVENT_NAME TOTAL_WAIT
> ---------
> ----------------------------------------------------------------
> ----------
> 09-DEC-13 gc buffer busy acquire 265912
> 09-DEC-13 gc cr multi block request 106042
> 09-DEC-13 read by other session 54826
> 09-DEC-13 gc cr block lost 9314
> 09-DEC-13 DB CPU 5584
> 08-DEC-13 gc cr multi block request 151110
> 08-DEC-13 library cache load lock 9010
> 08-DEC-13 DB CPU 5014
> 08-DEC-13 gc cr block lost 1668
> 08-DEC-13 control file sequential
> read 1110
> 07-DEC-13 gc cr multi block request 172730
>
> Has anyone seen this before? Thanks in advance.

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