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

What this article shares to you is about the common waiting events of oracle, which the editor thinks is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Buffer busy waits

In essence, this wait event only indicates that a session is waiting for a Buffer (data block), but there are many reasons for this phenomenon. The two common ones are:

When a session view modifies a data block, but that block is being modified by another session.

When a session needs to read a block of data, but that block is being read into memory by another session.

In the new version, the second case has been isolated and replaced by read by other session.

Buffer busy waits wait events are common when there is a hot time in the database, which occurs when multiple users frequently read or modify the same data block. If the waiting time is long, we can see it in the AWR or statspack report.

This wait event has three parameters. To see how many parameters we can use the following SQL:

SQL > select name, parameter1, parameter2, parameter3 from v$event_name where name='buffer busy waits'

NAME PARAMETER1 PARAMETER2 PARAMETER3

--

Buffer busy waits file# block# class#

2.Buffer latch

The location of data blocks in memory is recorded in a hash list (cache buffer chains). When a session needs to access a data block, it first searches the hash list, obtains the address of the data block from the list, and then accesses the desired data block through this address. The list Oracle uses a latch to protect its integrity. When a session needs to access the list, you need to get a Latch. Only in this way can you ensure that the list will not change during the session's browsing.

The main reasons for waiting events for buffer latch are:

The Buffer chains is too long, causing the session to take too long to search this list, leaving other sessions waiting.

The same data blocks are accessed frequently, which is what we usually call the hot fast problem.

This wait event has two parameters:

Latch addr: the virtual address of the latch requested by the session in SGA, and its corresponding Latch name can be found according to this address through the following SQL statement:

Select * from v$latch a dint vault latchname b where addr=latch addr and a.latch#=b.latch#

The index value in the chain#: buffer chains hash list, and when the value of this parameter is equal to s 0xfffffff, the current session is waiting for a LRU latch.

3.Control file parallel write

When there are multiple copies of control files in the database, Oracle needs to ensure that the information is written to each control file synchronously, which is a parallel physical operation process, because it is called control file writing in parallel. When such an operation occurs, a control file parallel write wait event occurs.

There are many reasons why control files are written frequently, such as:

-- Log switching is so frequent that the control file information needs to be updated frequently accordingly.

-- there is a bottleneck in the system Ipicuro, which causes all Ihambos to wait.

This wait event contains three parameters:

-- Files: the number of control files to be written by Oracle.

-- Blocks: the number of blocks written to the control file.

-- Requests: the number of times the control request is written.

4.Control file sequential read

This wait event occurs when the database needs to read the information on the control file, because the information of the control file is written sequentially, so it is also read sequentially, so it is called the sequential read of the control file. it often occurs in the following situations:

-- backup control files

-- Information sharing of control files between different instances in RAC environment

-- read the header information of the control file

-- read other information from the control file

This wait event has three parameters:

-- the file number of the control file to be read by File#:.

-- Block#: reads the starting block number of the control file information.

-- Blocks: the number of control file blocks that need to be read.

5.Db file parallel read

This is a very misleading wait event, which actually has nothing to do with parallel operations (such as parallel queries, parallel DML). This event occurs during database recovery, and when there are blocks of data that need to be recovered, Oracle reads them from the data file into memory in parallel for recovery.

This wait event contains three parameters:

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

-- Blocks: the number of data blocks to be read for the operation.

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

6.Db file parallel write

This is a background wait event, which also has nothing to do with the parallel operation of the user. It is generated by the background process DBWR, which occurs when the background process DBWR wants to write dirty data to disk. DBWR writes dirty data in batches to the corresponding data files on disk in parallel, and DBWR will have this wait event before the batch job completes. If only this wait event does not have much impact on the user's operation, when the free buffer waits wait event occurs, the available space in memory is insufficient, and this will affect the user's operation, such as affecting the user to read dirty data blocks into memory. When a db file parallel write wait event occurs, the wait can be mitigated by enabling the operating system's asynchronous Imax O. When using asynchronous iCandle O, DBWR no longer needs to wait until all data blocks are written to disk, it only needs to wait until the data is written to a percentage before it can proceed with subsequent operations.

This wait event has two parameters:

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

-- Timeouts: the timeout for waiting.

7.Db file scattered read

This wait event, which is often seen in the actual production database, is a wait event caused by a user action. This wait event occurs when a user issues a SQL operation such as a SQL operation that needs to read multiple data blocks each time. The two most common cases are full table scan (FTS: Full Table Scan) and index fast scan (IFFS: index fast full scan).

The scattered in this name may lead many people to think that it reads the data block in the way of scattered. In fact, on the contrary, when this wait event occurs, the SQL operation reads the data block sequentially, such as FTS or IFFS mode (if you ignore the situation that the data block that needs to be read already exists in memory). Scattered here refers to the way in which read blocks of data are stored in memory. After they are read into memory, they exist in memory in a decentralized manner rather than contiguously.

This wait event has three parameters:

-- the file number of the data file in which the data block is to be read by File#:.

-- the starting block number to be read by Block#:.

-- Blocks: the number of blocks to read.

8.Db file sequential read

This wait event, which is also common in the actual production library, occurs when Oracle needs to read only a single block of data at a time. The most common cases are index access (in addition to IFFS), rollback operations, ROWID access to table data, reconstruction of control files, DUMP of file headers, and so on. Sequential here does not mean that Oracle accesses data sequentially. Like db file scattered read, it means that read blocks of data are stored in memory in a continuous manner.

This wait event has three parameters:

The data block to be read by File#: is locked in the file number of the data file.

-- the starting block number to be read by Block#:.

-- Blocks: the number of blocks to read (this should be equal to 1).

9.Db file single write

This wait event usually occurs only in one case, when Oracle updates the header information of the data file (such as when a Checkpoint occurs). When this wait event is obvious, you need to consider whether the number of data files in the database is so large that it takes a long time for Oracle to checkpoint all the headers.

This wait event has three parameters:

-- File#: needs to update the file number of the data file where the data block is located.

-- Block#: requires updated block numbers.

-- Blocks: the number of blocks that need to be updated (usually equal to 1).

10.Direct path read

This wait event occurs when the session reads the data block directly into the PGA rather than the SGA, which is usually private to the session, so it doesn't need to be put into the SGA as shared data, because it doesn't make sense. This data usually comes from temporary data, such as the sorted data of SQL in a session, the data generated during parallel execution, and the sorted data generated by Hash Join,merge join, because these data are only meaningful to the SQL operation of the current session, so they do not need to be put in the SGA. When the direct path read wait event occurs, it means that a large amount of temporary data is generated on the disk, such as sorting, parallel execution, and so on. Or it means that there is not enough free space in PGA.

This wait event has three parameters:

-- Descriptor address: a pointer to a direct read I Descriptor address O that the current session is waiting for.

-- First dba: the oldest I-First dba O block address in the descriptor address.

-- Block cnt: the number of valid buffer involved in the descriptor address context.

11.Direct path write

This wait event is the opposite of direct path read, where the session writes some data directly from the PGA to a disk file without going through the SGA.

This usually occurs when:

-- sort using temporary tablespaces (out of memory)

-- Direct loading of data (using append to load data)

Parallel DML operations.

This wait event has three parameters:

-- Descriptor address: a pointer to a direct I Descriptor address O.

-- First dba: the oldest I-First dba O block address in the descriptor address.

-- Block cnt: the number of valid buffer involved in the descriptor address context.

12.Enqueue

The word Enqueue is actually another description of lock. When we find a long enqueue wait event in the AWR report, it indicates that there are blocking and waiting in the database, and you can associate the enqueue activity section of the AWR report to determine which lock has experienced a long wait.

This wait event has two parameters:

-- Name: the name and type of enqueue.

-- Mode: mode of enqueue.

13.Free buffer waits

When a session reads blocks of data from disk into memory, it needs to find free memory space in memory to store those blocks. This wait occurs when there is no free space in memory. In addition, there is another situation in which a session needs to construct an image of a data block at a certain time when doing a consistent read, and memory is required to store these newly constructed data blocks. This wait event will also occur if such a memory block cannot be found in memory.

When a serious free buffer waits wait event occurs in the database, the possible reasons are:

-- data buffer is too small, resulting in insufficient free space

There is so much dirty data in memory that DBWR cannot write the dirty data to disk in time to free up space.

This wait event contains two parameters:

-- the file number of the data file in which the data block needs to be read by File#:.

-- the block number that Block#: needs to read.

-query blocked statements:

SELECT / * + ORDERED USE_HASH (Hmam R) * /

H.sid hold_sid, holds.username h_user, holds.lockwait h_lockwait, holds.status h_status, holds.module h_module, holds.row_wait_obj# h_obj, holds.row_wait_row# h_row

R.sid wait_sid, waits.username w_user, waits.lockwait w_lockwait, waits.status w_status, waits.module w_module, waits.row_wait_obj# w_obj, waits.row_wait_row# w_row

H.type h_type, h.id1 h_id1, h.id2 h_id2, h.lmode h_lmode, h.request h_request, h.ctime h_ctime, h.block h_block

R.type r_type, r.id1 r_id1, r.id2 r_id2, r.lmode r_lmode, r.request r_request, r.ctime r_ctime, r.block r_block

'alter system kill session''| | holds.sid | |','| | holds.serial# | |

'';-- kill-9'| | nvl (holdp.spid, 'null') killhold

Holdsql.sql_text hsql

Waitsql.sql_text wsql

FROM v$lock h

V$lock r

V$session holds

V$session waits

V$process holdp

V$sqlarea holdsql

V$sqlarea waitsql

WHERE h.BLOCK = 1 AND

R.BLOCK = 0 AND

H.TYPE 'MR' AND

R.TYPE 'MR' AND

H.id1 = r.id1 AND

H.id2 = r.id2 AND

H.sid = holds.sid AND

R.sid = waits.sid AND

Holds.paddr = holdp.addr (+) AND

Holds.sql_address = holdsql.address (+) AND

Holds.sql_hash_value = holdsql.hash_value (+) AND

Waits.sql_address = waitsql.address (+) AND

Waits.sql_hash_value = waitsql.hash_value (+)

14.Latch free

In versions prior to 10g, latch free wait events represented all latch waits, and after 10g, some commonly used latch events have been isolated:

This wait event has three parameters:

-- Address: the latch address that the session is waiting for.

-- Number: latch number, through which information about the latch can be found in the v$latchname view.

15.Library cache lock

This wait time occurs when different users compete for resources caused by concurrent operations on the same database object in the sharing. For example, when a user is doing DDL operations on a table, if other users want to access the table, a library cache lock wait event will occur. It will wait until the DDL operation is completed before continuing the operation.

This event 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 the v$db_object_cache view.

16.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 the v$db_object_cache view.

17.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.

18.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

19.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.

20.Log file single write

This wait event occurs when the header of the redo log file is updated, and LGWR updates the redo log 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.

21.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.

22.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.

23.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.

24.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.

25.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.

26.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:

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

-- # bytes: the number of bytes received by the server from the client message.

27.SQL*Net message from dblink

This wait event is the same as SQL*Net message from client, but it means that when a database accesses another database through dblink, a session is established between them, and this wait event occurs during communication between the sessions.

This wait event is also an idle wait event.

This event contains two parameters:

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

-- # bytes: the number of bytes received by the server through dblink from another server-side message.

28.SQL*Net message to client

This wait event occurs when the server sends a message to the client. When the server sends messages to the client and waits, the possible reason is that the client is too busy to receive the message from the server in time, or the network problem may cause the message to be unable to be sent from the server to the client.

This wait event has two parameters:

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

-- # bytes: the number of bytes the server sends messages to the client.

29.SQL*Net message to dblink

This wait event is the same as SQL*Net message to client, but it is a wait event that occurs between the database server and the server. The reason for this wait may be that the remote server is busy and cannot receive the sent message in time, or the message cannot be sent due to network problems between the servers.

This wait time contains two parameters:

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

-- # bytes: the number of bytes that the server sends to another server through dblink.

30.SQL*Net more data from client

The server waits for the user to send more data to complete the operation, such as a large SQL text, so that a SQL*Net packet cannot be transmitted, so the server will wait for the client to send the whole SQL text for processing, and a SQL*Net more data from client wait event will be generated.

This wait time contains two parameters:

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

-- # bytes: the number of bytes of the message received by the server from the client.

These are the common waiting events in oracle, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.