How to analyze the common waiting events in Oracle 01/18 Update SLTechnology News&Howtos

How to analyze the common waiting events in Oracle

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

How to carry out Oracle common waiting event analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1.Library cache pin

This wait event, like library cache lock, is an event caused by concurrent operations that occur in a shared pool. In general, if Oracle is to recompile objects such as PL/SQL or views, you need to pin those objects into the shared pool. If this object is unique to other users at this time, a library cache pin wait will be generated.

This wait event also contains four parameters:

-- Handle address: the address of the object being loaded.

-- Lock address: address of the lock.

-- Mode: the data fragment of the loaded object.

-- Namespace: the namespace name of the loaded object in v$db_object_cache view.

2.Log file parallel write

The background process LGWR is responsible for writing the data from log buffer to the REDO file in order to reuse the data from log buffer. If there are more than two members in each REDO LOG group, the LGWR process writes REDO information to these files in parallel.

If there is a bottleneck of this waiting event in the database, the main reason may be the insufficient performance of disk Imax O or the distribution of REDO files leading to Imax O contention, which is like a group of REDO member files on the same disk.

This wait event has three parameters:

-- Files: the number of files that need to be written to the operation.

-- Blocks: the number of blocks that need to be written for the operation.

-- Requests: the number of times the operation needs to be performed.

3.Log buffer space

The log buffer space wait event occurs when there is no free space in the log buffer to hold the newly generated redo log data. If the number of newly generated redo log in the database is greater than the number of redo log written by LGWR to disk, you must wait for LGWR to finish writing to disk, and LGWR must ensure that redo log writes to disk successfully before you can reuse this information in redo buffer.

If there are a large number of log buffer space wait events in the database, consider the following methods:

-- increase the size of the redo buffer.

-- improve the Istroke O performance of the disk

4.Log file sequential read

This wait event usually occurs when the redo log information is read, such as the online redo archive operation. The ARCH process needs to read the redo log information. Because the redo log information is written sequentially, it is also read sequentially when reading.

This wait event contains three parameters:

-- the sequence number of the redo log read when Log#: waits.

-- the block number read by Block#:.

-- Blocks: the number of blocks read.

5.Log file single write

This wait event occurs when the header of the redo log file is updated, and LGWR updates the redo log file header information when a new log member is added to the log group or when the sequence number of redo log is changed.

This wait event contains three parameters:

-- the number of the redo log group written by Log#:.

-- the block number written by Block#:.

-- Blocks: the number of blocks written.

6.Log file switch (archiving needed)

In archive mode, this wait event occurs when the online log switch (log file switch) is performed and the online log that needs to be switched has not been archived by the archiving process (ARCH). When the online log file switches to the next log, you need to make sure that the next log file has been archived by the archiving process, otherwise that online log information is not allowed to be overwritten (otherwise the archived log information will be incomplete). Such a wait event usually causes the ARCH process to die for some reason, such as when the ARCH process attempts to write an archive file to the destination but fails (media failure or other reasons), the ARCH process dies. If this happens, you can find the relevant error message in the alert log file of the database.

This wait event has no parameters.

7.Log file switch (checkpoint incomplete)

When one online log is switched to the next online log, it is necessary to ensure that the information recorded on the online log to be switched to (such as the redo log generated by some dirty data blocks) is written to disk (checkpoint). The reason for this is that if the information of an online log file is overwritten and the data blocks that rely on these redo information for recovery have not been written to disk (checkpoint), the system down is dropped. Oracle will have no way to restore the instance.

The status of the online log is recorded in the v$log view. Generally speaking, online logs have three states.

-- Active: the information protected in this log has not yet completed checkpoint.

-- Inactive: the information protected in this log has completed checkpoint.

-- Current: current log.

If there are a large number of log file switch (checkpoint incomplete) waiting events in the system, the reason may be that the log files are too small or there are too few log groups, so the solution is to increase the size of log files or the number of log groups.

This wait event has no parameters.

8.Log file sync

This is a wait event caused by a user's session behavior. When a session issues a commit command, the LGWR process writes the redo log generated by the transaction from the log buffer to disk to ensure that the information submitted by the user is securely recorded in the database. After the commit instruction issued by the session, you need to wait for LGWR to successfully write the redo generated by the transaction to disk before you can proceed with the subsequent operation. This wait event is called log file sync.

This wait may occur in the following situations:

-- High submission frequency

The solution is to simply eliminate unnecessary commits, and transactions are units of work. The unit of work should be all successful or all failed.

-- the slow Istroke O subsystem

Higher IO throughput can improve the average wait time for log file sync and log file parallel write events. Frequent commits can mess up the database layout and the IO subsystem. The solution is to put the log files on the bare device or bind them to RAID 0 or RAID 0 instead of RAID 5.

-- too large log buffer

An overly large log buffer may also extend the log file sync wait. Large log buffers reduce the number of background writes, allow LGWR to become lazy, and cause more redo entries to pile up in the log buffer. At the same time, you can adjust the parameter _ LOG_IO_SIZE parameter, whose default value is 1 LOG_IO_SIZE 3 of LOG_BUFFER or 1MB, whichever is smaller. In other words, you can have a larger log buffer, but a smaller _ LOG_IO_SIZE will increase background writes, thus reducing log file sync wait time.

-- too small log buffer

Too small log buffer can also cause log buffer space to wait

-- the number of log groups is not appropriate for log size.

This wait event contains one parameter:

Buffer in Buffer#: redo buffer that needs to be written to disk.

9.SQL*Net break/reset to client

When this wait event occurs, it means that the server is sending a request to disconnect or reset the connection to the client, waiting for a response from the client, usually due to the instability of the server-to-client network.

This wait event contains two parameters:

-- Driver id: the protocol information used by the server and client connections.

-- Breaks: zero means that the server sends a reset message to the client, and non-zero means that the server sends a break message to the client.

10.SQL*Net break/reset to dblink

This wait event is the same as SQL*Net break/reset to client. However, it means that when a database accesses another database through dblink, a session is established between them, and this wait event occurs during the communication between the session. Similarly, if this wait event occurs, the communication problem between the two databases needs to be checked.

This wait event has two parameters:

-- Driver id: the protocol information used by the server and client connections.

-- Breaks: zero means that the server sends a reset message to the client, and non-zero means that the server sends a break message to the client.

11.SQL*Net message from client

This wait event is basically the most common wait event. When a session is established successfully, the client will send a request to the server. After processing the client request, the server will return the result to the client and continue to wait for the request from the client. At this time, a SQL*Net message from client wait event will occur. Obviously, this is an idle wait, and if the client stops sending requests to the server, the server will always be in this waiting event state.

This wait event contains two parameters: