Shulou(Shulou.com)06/01 Report--

RAC performance Analysis-gc buffer busy acquire wait event

Overview

-

Gc buffer busy is a common wait event in RAC database. Starting from 11g, gc buffer busy is divided into gc buffer busy acquire and gc buffer busy release.

Gc buffer busy acquire is when session#1 attempts to request access to the remote instance (remote instance) buffer, but there is already another session#2 request to access the same buffer before session#1, and the session#1 waits for gc buffer busy acquire.

Gc buffer busy release waits for gc buffer busy release when session#1 tries to request access to the local instance buffer and discovers that a previous session#2 request from the remote instance to access the buffer has not been completed.

Cause / solution

-

-hotspot block (hot block)

Segments by Global Cache Buffer Busy records frequently visited gc buffer in AWR.

Different solutions can be taken according to the type of hotspot block, such as partitioned table, partitioned index, reverse index, and so on. This is similar to buffer busy waits in a stand-alone database.

-inefficient SQL statement

Inefficient SQL statements can cause unnecessary buffer requests to be accessed, increasing the chances of buffer busy. You can find TOP SQL in AWR. The solution is to optimize SQL statements to reduce buffer access. This is similar to buffer busy waits in a stand-alone database.

-data cross-access.

RAC database, the same data is requested to be accessed on different database instances.

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

-Oracle bug

It is recommended that you install the latest Patch Set and PSU recommended by Oracle.

For Patch set and PSU information, please refer to: Oracle Recommended Patches-- Oracle Database (Doc ID 756671.1)

Case sharing

-

A case of gc buffer busy acquire, to share with you.

-the application side reflects the business processing exception, and the database hang collects hanganalyze on the spot at the first time by DBA (hanganalyze is very important for analyzing database hang)

The command for the RAC database to collect hanganalyze:

SQL > conn / as sysdba

SQL > oradebug setmypid

SQL > oradebug unlimit

SQL > oradebug-g all hanganalyze 3

Through hanganalyze, we can easily see that more than 1000 Chain have similar wait relationships, such as:

Chain 1 Signature:'gc current request'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.