RAC Database Hanging with “gc buffer busy acquire” and “gc buffer busy request” Wait Events

There is a RAC database hanging for a short period of time with high “gc buffer busy acquire” and “gc buffer busy request” wait events by sql id =”4vs91dcv7u1p6″:

insert into sys.aud$( sessionid, entryid, statement, ntimestamp#, 
userid, userhost, terminal, action#, returncode, obj$creator, 
obj$name, auth$privileges, auth$grantee, new$owner, new$name, 
ses$actions, ses$tid, logoff$pread, logoff$lwrite, logoff$dead, 
comment$text, spare1, spare2, priv$used, clientid, sessioncpu, 
proxy$sid, user$guid, instance#, process#, xid, scn, auditid, 
sqlbind, sqltext, obj$edition, dbid) values(:1, :2, :3, 
SYS_EXTRACT_UTC(SYSTIMESTAMP), :4, :5, :6, :7, :8, :9, :10, :11, 
:12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, 
:25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36)

AWR report shows AUD$ table is the segment with the highest “Global Cache Buffer Busy” wait event:

CAUSE

With AUDIT_TRAIL set for database (AUDIT_TRAIL=db), and the AUD$ and FGA_LOG$ tables located in a dictionary segment space managed SYSTEM tablespace, “gc” wait events are sometimes observed during heavy periods of database logon activity.

SOLUTION

Move AUD$ and  FGA_LOG$ tables onto an ASSM tablespace like SYSAUX. On production, make sure to run it during off peak hours.

set pagesize 120
set linesize 180
SELECT T.TABLE_NAME, TS.SEGMENT_SPACE_MANAGEMENT 
FROM DBA_TABLES T, DBA_TABLESPACES TS 
WHERE TS.TABLESPACE_NAME = T.TABLESPACE_NAME 
AND T.TABLE_NAME = 'AUD$';

TABLE_NAME  SEGMEN
----------- ------
AUD$        MANUAL

select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT 
from DBA_TABLESPACES 
where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME  SEGMEN
---------------- -----
SYSAUX           AUTO

select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME 
from dba_lobs 
where OWNER='SYS' and TABLE_NAME='AUD$';

TABLE_NAME   TABLESPACE_NAME    SEGMENT_NAME
------------ ------------------ ---------------------------
AUD$         SYSTEM             SYS_LOB0000000384C00040$$
AUD$         SYSTEM             SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name 
from dba_segments where segment_name in ( 
select INDEX_NAME from dba_indexes where OWNER='SYS' 
and TABLE_NAME='AUD$');

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME 
------------------------ ------------- -----------------
SYS_IL0000000384C00041$$ LOBINDEX      SYSTEM 
SYS_IL0000000384C00040$$ LOBINDEX      SYSTEM

Move AUD$ table:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.

Move FGA_LOG$ table if needed:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.
select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME 
from dba_lobs 
where OWNER='SYS' and TABLE_NAME='AUD$';

TABLE_NAME   TABLESPACE_NAME    SEGMENT_NAME
------------ ------------------ ---------------------------
AUD$         SYSAUX             SYS_LOB0000000384C00040$$
AUD$         SYSAUX             SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name 
from dba_segments where segment_name in ( 
select INDEX_NAME from dba_indexes where OWNER='SYS' 
and TABLE_NAME='AUD$');

SEGMENT_NAME            SEGMENT_TYPE    TABLESPACE_NAME 
------------------------ ------------- -----------------
SYS_IL0000000384C00041$$ LOBINDEX       SYSAUX 
SYS_IL0000000384C00040$$ LOBINDEX       SYSAUX 

select count(*) from dba_objects where status!='VALID';

COUNT(*)
----------
0
Advertisements
This entry was posted in GI and RAC, Performance Tunning and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.