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

Wait events for Oracle RAC include the following categories:

1.Block-Related Wait Events

2.Message-Related Wait Events

3.Contention-Related Wait Events

4.Load-Related Wait Events

Block-Related Wait Events

The main wait events for block-related waits are:

Gc current block 2-way

Gc current block 3-way

Gc cr block 2-way

Gc cr block 3-way

*******************

The required data was not found in node1's cache, so there was a cross-node fusion cache to get data from node2's Cache.

Excessive waiting on gc current block 2-way or gc current block 3-way wait events is usually due to either (a) an inefficient execution plan, resulting in a large number of block accesses

Or (b) the application data similarity (application affinity) is not implemented. If object access is localized, consider implementing application affinity (similarity of application data).

*********************

The block-related wait event statistics indicate that a block was received as either the result of a 2-way or a 3-way message, that is, the block was sent from either the resource master requiring 1 message and 1 transfer, or was forwarded to a third node from which it was sent, requiring 2 messages and 1 block transfer.

If the average wait times are acceptable and no interconnect or load issues can be diagnosed, then the accumulated time waited can usually be attributed to a few SQL statements which need to be tuned to minimize the number of blocks accessed.

The column CLUSTER_WAIT_TIME in V$SQLAREA represents the wait time incurred by individual SQL statements for global cache events and will identify the SQL which may need to be tuned.

Message-Related Wait Events

The main wait events for message-related waits are:

Gc current grant 2-way

Gc cr grant 2-way

*********************

If the requested block does not reside in any buffer, you need to request master to read the data on the physical disk, resulting in physical reads and writes. You will encounter gc cr grant 2-way and gc current grant 2-way wait events.

*********************

The message-related wait event statistics indicate that no block was received because it was not cached in any instance. Instead a global grant was given, enabling the requesting instance to read the block from disk or modify it.

If the time consumed by these events is high, then it may be assumed that the frequently used SQL causes a lot of disk in the event of the cr grant O (in the event of the cr grant) or that the workload inserts a lot of data and needs to find and format new blocks frequently (in the event of the current grant).

Contention-Related Wait Events

The main wait events for contention-related waits are:

Gc current block busy

Gc cr block busy

Gc buffer busy acquire/release

******************

It is generally concurrent reading and writing, and there is a competition for resources among the session. It is necessary to wait for other session to write the modified data to the redo log before returning control to other session. If one competition does not end and other competition increases, there will be an avalanche effect and a sharp decline in system performance.

Busy events (Busy events) indicate that LMS performs additional work to deal with concurrency-related issues.

******************

The contention-related wait event statistics indicate that a block was received which was pinned by a session on another node, was deferred because a change had not yet been flushed to disk or because of high concurrency, and therefore could not be shipped immediately. A buffer may also be busy locally when a session has already initiated a cache fusion operation and is waiting for its completion when another session on the same node is trying to read or modify the same data. High service times for blocks exchanged in the global cache may exacerbate the contention, which can be caused by frequent concurrent read and write accesses to the same data.

Load-Related Wait Events

The main wait events for load-related waits are:

Gc current block congested

Gc cr block congested

***************

If the LMS process does not process the request within 1 millisecond after receiving it, the LMS process marks the response as: the block is experiencing a congestion-related wait event.

There are many reasons for blocking related wait events, for example, the LMS process is flooded with a large number of requests from the global cache. The LMS process is experiencing scheduling delays in CPU, and the LMS process has encountered another kind of resource exhaustion (such as memory).

Typically, the LMS process runs at the real-time CPU scheduling priority, so the delay for CPU scheduling will be minimal. A large number of such waits for this event indicate a sudden surge in global cache requests and the inability of the LMS process to process them quickly. Lack of server memory may also lead to paging of LMS processes, affecting the performance of the global cache.

You can check why the LMS process does not process the request effectively.

It is the lack of hardware that requires additional hardware resources, the most common of which is only node. You can also consider upgrading the hardware.

**************

The load-related wait events indicate that a delay in processing has occurred in the GCS, which is usually caused by high load, CPU saturation and would have to be solved by additional CPUs, load-balancing, off loading processing to different times or a new cluster node.For the events mentioned, the wait time encompasses the entire round trip from the time a session starts to wait after initiating a block request until the block arrives.

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.