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

Today, monitoring has been reporting waiting events exception, check the database is basically gc buffer busy acquire waiting events. This waiting event has never been contacted before, and I have specially learned about it today. Reference documentation: Oracle Mos 1. Briefly define that this wait event is only applicable to RAC environments, similar to "buffer busy" waits in non-RAC environments. This occurs when a session is waiting to access a block that is being applied and held by another session and cannot be shared. Multiple sessions may be queued for the same block. In versions 11.1 and earlier, this type of wait was classified as "gc buffer busy" wait. Since Oracle 11.2, "gc buffer busy" waits are divided into two new waiting categories: gc buffer busy acquiregc buffer busy releasegc buffer busy acquire: when session 1 attempts to request access to a remote instance (remote instance) buffer, but session 1 already has another session 2 on the same instance requesting access to the same buffer and has not completed it, then session 1 is waiting for gc buffer busy acquire.

Gc buffer busy release: before the local instance session 1, a remote instance session 2 has requested access to the same buffer of the local instance, and has not completed it, so session 1 of the local instance is waiting for gc buffer busy release.

General reasons High contention in particular HOT blocks of the objectsOther waits like "gc block busy" and "enq: TX-row lock contentionHigh network latency or a problem with network

Busy server or active paging/swapping due to low free memory

Individual waits- (for waits seen in GV$SESSION_WAIT)

P1 File # P2 Block # P3 Mode requested/mode held/block classSECONDS_IN_WAIT Amount of time waited for the current eventfile# This is the file# of the file that Oracle is trying to read from.block# This is the starting block number in the file from where Oracle starts reading the blocks.blocks This parameter specifies the number of blocks that Oracle is rying to Read from the file# starting at block#Inst_id instance numberTo determine the root blocker for sessions waiting on the gc wait events use the below options1.system state dump at cluster level2. Oratop displays waiters/blockers3. V$wait_chains can be used to find the root blocker for sessions that are blocked,Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1) 4. Using v$hang_info, v$hang_session_info, etc5. Oracle Hang Manager (Doc ID 1534591.1) Using the above information we can find the sessions waiting for specific gc events with their final blockers at instance level

System Wide wait- (for waits seen in V$SYSTEM_EVNET)

If it takes a long time to wait for the buffer, you need to determine which segment is contended based on the following: SELECT inst_id, sid, event, wait_class, P1, P2, P3 Mode requested / mode held / block class, seconds_in_wait FROM gv$session_wait WHERE event LIKE'gc buffer%' From the previous output, using the data in P1 and P2, you can get the relevant object information using the following command: SELECT segment_name FROM dba_extents WHERE file_id = & file AND & block BETWEEN block_id AND block_id + blocks-1 AND ROWNUM = 1; troubleshooting 1) High contention for specific HOT blocks due to too many index block splits or right growing indexes with keys generated from the sequence. Buffer busy will accompany this frequently. If the problem persists, you can use the System Wide wait- (for waiting seen in V$SYSTEM_EVNET) instructions to look for hot blocks. Or get the problem segment from the Segments by Global Cache Buffer Busy reported by AWR during the problem period.

2) gc block busy, enq: TX-row lock contention, and other waits may affect blocking sessions or LMS processes.

If there are other waits that may slow down the holder of the block, solving the problem is a top priority, because gc buffer busy acquire/release may just be a side effect of the wait. Check the Top 10 Foreground Events by Total Wait Time section of the AWR report to see if other waits significantly affect database performance.

3) High network latency or network problems

Issue "ping-s 10000" and perform a network check according to the document (How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1)) for RCA with past problems, check the OSWatcher for ping delay time. The AWR report will contain Interconnect Ping Latency Stats, which is also useful for checking network latency.

The netstat in OSWatcher and the Nic&Protocol section of the CHM output provide information about the health of the network.

4) busy server or active page scheduling / swapping (due to insufficient available memory)

Check the vmstat output or CHM output to see if the server is busy or heavy paging / swapping. For RCA with past problems, check the CHM or OSWatcher output.

5) 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. About whether select will lead to gc buffer busy acquire: queries generally request buffer in shared mode, but if buffer is not in buffer cache, then IO is required to read buffer into memory. This process requires exclusive mode. If a large number of other session also request to query the buffer (request in shared mode), then buffer will be waiting, and buffer cache may not be large enough. If the block of the query request has been modified, the query needs to access the CR block. In order to reconstruct the CR block, you need to read the corresponding undo block. If the undo block is not in the buffer, you need IO to read the undo block into memory. If there are a large number of queries accessing this CR block, then there will be buffer busy waiting. 6) data cross-accesses RAC database between nodes, and the same data is requested 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.

7) Oracle Bug

4. Possible solutions for high contention and hot blocks: Solution is to reorganize the index in a way to avoid the contention or hot spots using the below optionsI. Global Hash partition the indexCREATE INDEX hgidx ON tab (C1 and c2) GLOBAL PARTITION BY HASH (C1 and c2) (PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4) II. Recreate the index as reverse key index (not suitable for large table, could require buffer cache increased accordingly) III. If index key is generated from a sequence, increase cache size of the sequence and make the sequence'no order' if application supports it.Refer the doc link: http://docs.oracle.com/database/121/VLDBG/GUID-BF3F38E1-62BB-4EE3-86C1-A2EF8A258B1F.htm#VLDBG1089

For enq: TX-row lock contention:

Mode 4-Related to ITL waits reports from AWR or uses the following SQL to find segments with higher ITL waits: SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME = 'ITL waits' AND VALUE > 0 ORDER BY VALUE; increase the inittransvalue Mode 6-Primarily due to application issue of these high ITL waiting segment: this is an application problem and requires application developers to investigate the SQL statements involved. The following documents may be helpful for further study: Note:102925.1-Tracing sessions: waiting on an enqueueNote:179582.1-How to Find TX Enqueue Contention in RAC or OPSNote:1020008.6-SCRIPT: FULLY DECODED LOCKINGNote:62354.1-TX Transaction locks-Example wait scenariosNote:224305.1-Autonomous Transaction can cause locking

How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1)

How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1). Pdf

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.