Principles and cases of gc buffer busy acquire and gc buffer busy release, acquirerelease

Principles and cases of gc buffer busy acquire and gc buffer busy release, acquirerelease

Yesterday, there was a database CPU 100% problem in the official environment. The database had 128 CPUs, GB of memory, and the power series. The database was very powerful. It only took one second to read a database of tens of thousands of logical reads. After the problem occurs, adjust the two SQL statements with the highest load to solve the problem. However, for the first time there are two cluster wait events, see metlink for an article:

Share: RAC wait event: gc buffer busy acquire

Overview
---------------------
Gc buffer busy is a common wait event in RAC databases. gc buffer busy starts at 11 GB and is divided into gc buffer busy acquire and gc buffer busy release:
Gc buffer busy acquireWhen session #1 tries to access the remote instance buffer, but before session #1 has another session on the same instance #2 requests to access the same buffer, if it is not completed, session #1 waits for gc buffer busy acquire.
Gc buffer busy releaseSession #2 of a remote instance has accessed the same buffer before session #1 and is not completed. session #1 waits for the gc buffer busy release.
Cause/SOLUTION
---------------------
-Hot block)
In AWR, Segments by Global Cache Buffer Busy records frequently accessed gc buffer.
The solution can be different based on the hotspot block type, such as Partitioned Tables, partition indexes, and reverse indexes. This is similar to buffer busy waits in a standalone database.

-Inefficient SQL statements
Inefficient SQL statements may cause unnecessary buffer to be accessed by requests, increasing the chance of buffer busy. You can find top SQL in AWR. The solution can optimize SQL statements to reduce buffer access. This is similar to buffer busy waits in a standalone database.

-Data cross-Access
RAC database, the same data is requested to access on different database instances.
If the application can be implemented, we recommend that data of different application functions/modules be accessed on different database instances to avoid cross-access of the same data by multiple instances, it can reduce the contention for buffer and avoid gc waiting.

-Oracle bug
We recommend that you install the latest Patch Set and PSU recommended by Oracle.
For information about Patch set and PSU, see Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1)

At that time, thousands of sessions were blocked due to database load.

Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	4531	-15 09:00:06	1137	106.2	2
End Snap:	4533	-15 11:00:05	2728	42.4	2
Elapsed:		119.98 (mins)
DB Time:		72,409.59 (mins)

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	MS (Wait Avg)	% DB time	Wait Class
Gc buffer busy acquire	1,164,631	2324.3 K	1996	53.5	Cluster
DB CPU		200.7 K		4.6
Enq: TX-row lock contention	21,390	179 K	8368	4.1	Application
Rdbms ipc reply	9,488,345	162.2 K	17	3.7	Other
Buffer busy waits	3,899	94.5 K	24243	2.2	Concurrency
Gc buffer busy release	2,288	62.3 K	27219	1.4	Cluster
Log file sync	96,502	50.9 K	528	1.2	Commit
Latch: row cache objects	185,628	47.8 K	258	1.1	Concurrency
Gc current request	20	40.3 K	2.0E + 06	. 9	Cluster
Db file sequential read	4,074,919	30.7 K	8	. 7	User I/O

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code when des the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
• % Total-Elapsed Time as a percentage of Total DB time
• % CPU-CPU Time as a percentage of Elapsed Time
• % IO-User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 84.5% of Total DB Time (s): 4,344,576
• Captured PL/SQL account for 0.2% of Total DB Time (s): 4,344,576

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	% Total	% CPU	% IO	SQL Id	SQL Module	SQL Text
2,295,852.93	1,559	1,472.64	52.84	0.05	0.04	9baga95gnc3qc	JDBC Thin Client	UPDATE BENCH_DONE t set t ....
546,544.31	3,694	147.95	12.58	16.40	0.01	8fcx0qg5xa5wy	JDBC Thin Client	Select id, PLAN_STAT...
234,360.72	1,715	136.65	5.39	0.00	0.00	8w406h1z76j1d	JDBC Thin Client	UPDATE BENCH_DONE t set t ....
216,796.09	895	242.23	4.99	16.60	0.02	Azj8hg391_qyq	JDBC Thin Client	Select count (1) ALL_COUNT, NVL...

SQL ordered by Gets

• Resources reported for PL/SQL code when des the resources used by all SQL statements called by the code.
• % Total-Buffer Gets as a percentage of Total Buffer Gets
• % CPU-CPU Time as a percentage of Elapsed Time
• % IO-User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 26,785,040,521
• Captured SQL account for 47.2% of Total

Buffer Gets	Executions	Gets per Exec	% Total	Elapsed Time (s)	% CPU	% IO	SQL Id	SQL Module	SQL Text
7,426,092,420	3,694	2,010,311.97	27.72	546,544.31	16.4	0	8fcx0qg5xa5wy	JDBC Thin Client	Select id, PLAN_STAT...
2,468,792,930	895	2,758,427.85	9.22	216,796.09	16.6	0	Azj8hg391_qyq	JDBC Thin Client	Select count (1) ALL_COUNT, NVL...
230,823,164	3,586	64,367.87	0.86	11,911.51	12.8	. 1	16mmtrx7rw0fb	JDBC Thin Client	Select dutylogvos0 _. M_DUTY_REC...
228,243,875	1,559	146,404.03	0.85	2,295,852.93	. 1	0	9baga95gnc3qc	JDBC Thin Client	UPDATE BENCH_DONE t set t ....

Conclusion: This event is mainly caused by low-efficiency SQL statements. The logic reads are high and the execution frequency is high. 9baga95gnc3qc the SQL statement last week showed 0.1 million logical reads, which rose to 0.14 million this week. Here, a baseline for this database, a node, executes 1 thousand times in two hours, and the logical reads exceed 0.1 million, optimization is required.