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

This article introduces what are the waiting events in Oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

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 iUniver block address in 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 iUniver block address in 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 (Hemerr) * / 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 renders blockblock last 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 = 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 v$db_object_cache view.

About which waiting events in Oracle are shared here, I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.