Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Because the segment space management of the tablespace of the AUD$ and FGA_LOG$ tables is not automatic, a “gc buffer busy acquire” wait event can be seen during database connections.
In this case, the AUD$ and FGA_LOG$ tables must be moved to a tablespace with automatic segment space management or, the AUD$ table truncated.
Find the Cause of the gc buffer busy acquire
You can find the operation that caused the wait event with the following query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> SELECT instance_number, sql_opname,event, p1, p2, p3, current_obj#, COUNT (*) cnt FROM dba_hist_active_sess_history WHERE sample_time BETWEEN TIMESTAMP '2019-05-13 00:00:00' AND TIMESTAMP '2019-05-13 10:30:00' AND wait_class = 'Cluster' GROUP BY instance_number, event, sql_opname,p1, p2, p3, current_obj# ORDER BY cnt DESC; INSTANCE_NUMBER SQL_OPNAME EVENT P1 P2 P3 CURRENT_OBJ# CNT --------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- ------------ ---------- 2 INSERT gc buffer busy acquire 1 12878 1 384 268395 1 INSERT gc buffer busy acquire 1 12878 1 384 262336 2 SELECT gc buffer busy acquire 1 12878 1 384 2460 2 SELECT gc current request 1 12878 33554433 384 2460 1 INSERT gc current request 1 12878 33554433 384 2423 2 SELECT gc buffer busy acquire 94 2 13 -1 123 2 SELECT gc current request 94 2 16777229 -1 105 |
As you can see, the object with the highest count value from the records in the result set is AUD$.
1 2 3 4 5 | SQL> select OWNER,OBJECT_NAME from dba_objects where object_id=384; OWNER OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- SYS AUD$ |
In this case, the tablespaces of the AUD $ and FGA_LOG $ tables need to be moved to new tablespace.
You can use the following article to move the tablespace of the AUD$ and FGA_LOG$ tables.
“How To Move AUD$ and FGA_LOG$ to a Different Tablespace”
