Oracle Wait Event ____oracle

Before finishing an article:

Oracle Wait Events

Http://blog.csdn.net/tianlesoftware/archive/2010/05/31/5635934.aspx

one. Knowledge of waiting events:

1.1 Wait events can be divided into two main categories, that is, idle (IDLE) Wait events and non idle (non-idle) wait events.

1. Idle wait event means that Oracle is waiting for some kind of work, not paying too much attention to this part of the event when diagnosing and optimizing the database.

2. Non-idle wait events are specific to Oracle's activities, which are the waiting events that occur during database tasks or application runs, which need attention and research when adjusting the database.

There are 872 waiting events in Oracle 10g, and 1116 waiting events in 11g. We can view information about waiting events through the V$event_name view.

1.2 View the field structure of the V$event_name view:

sql> desc v$event_name;

is the name empty? Type

----------------------------------------- -------- ---------------

event# number

EVENT_ID number

NAME VARCHAR2 (64)

PARAMETER1 VARCHAR2 (64)

PARAMETER2 VARCHAR2 (64)

PARAMETER3 VARCHAR2 (64)

WAIT_CLASS_ID number

wait_class# number

Wait_class VARCHAR2 (64)

1.3 View Total Wait events:

Sql> Select COUNT (*) from V$event_name;

COUNT (*)

----------

1116

1.4 View the waiting event Category:

/* Formatted on 2010/8/11 16:08:55 (QP5 v5.115.810.9015) * *

SELECT wait_class#,

WAIT_CLASS_ID,

Wait_class,

COUNT (*) as "Count"

From V$event_name

GROUP by wait_class#, wait_class_id, Wait_class

Order BY wait_class#;

wait_class# wait_class_id Wait_class Count

----------- ------------- -------------------- ----------

0 1893977003 Other 717

1 4217450380 Application 17

2 3290255840 Configuration 24

3 4166625743 Administrative 54

4 3875070507 Concurrency 32

5 3386400367 Commit 2

6 2723168908 Idle 94

7 2000153315 Network 35

8 1740759767 User I/O 45

9 4108307767 System I/O 30

Ten 2396326234 Scheduler 7

One 3871361733 Cluster 50

644977587 Queueing 9

1.5 Related several views:

V$session: Represents the beginning of a database activity and is considered a source.

V$session_wait: The view is used to record the waiting condition of the active session in real time, which is the current information.

V$session_wait_history: is a simple enhancement to the v$session_wait, recording the last 10 times of active session waiting.

V$sqltext: When a database bottleneck occurs, you can usually find the session from V$session_wait that is waiting for the resource, through the session's SID, the Federated V$session, and the v$ The SQLText view captures the SQL statements that these sessions are executing.

V$active_session_history: The core of Ash, which records the historical wait information for the active session, sampled once per second, which is recorded in memory and expected to record one hours of content.

Wrh#_active_session_history: V$active_session_history is a storage place in AWR.

V$active_session_history: The information in is refreshed periodically (once per hour) to the load library and is retained for analysis by default for one weeks.

Dba_hist_active_sess_history: The view is a joint presentation of the Wrh#_active_session_history view and several other views, usually through which historical data is accessed.

V$system_event because V$session records dynamic information, is related to the lifecycle of the session, and does not record historical information, Oracle provides view v$system_event to record summary information for all waiting events since the database was started. With this view, users can quickly get a general overview of how the database is running.

two. 33 Common Wait Events

1. Buffer Busy Waits

Essentially, the generation of this wait event only shows that a session is waiting for a buffer (data block), but there are many reasons for this phenomenon. The two common types are:

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

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

The smallest unit of Oracle operations is block, and even if you want to modify a record, you need to manipulate the block where the record is located. When you make changes to this block, other sessions will be blocked from modifying the data on the block (even if other users are not modifying the current user's data), but the data block can be read in a consistent way (from undo). After the current user modifies the block, the exclusive lock added to the data block will be released immediately, so that another session can continue to modify it. Modifying the operation is a very short time, this locking mechanism we call latch.

When a session modifies a block of data, it is accomplished by following these steps:

Get this block of data in an exclusive Way (Latch)

Modify this block of data.

Release latch.

Buffer busy waits wait events are common in the heat of the database, when multiple users frequently read or modify the same block of data, this waiting event occurs. If we wait a long time, we can see it in the Awr or Statspack report.

This wait event has three parameters. To view a few 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#

In the following example, the Query method is the same as this one, so the query for the parameter by other events will not be overly descriptive.

file#: Waits for access to the file ID number of the data block.

Blocks: The data block number that is waiting to be accessed.

ID: Before 10g, this value represents the reason for a wait time, and after 10g, the category that is waiting for the event.

2. Buffer latch

The location of the memory block is recorded in a hash list (cache buffer chains). When a session needs to access a block of data, it first searches the hash list, obtains the address of the block from the list, and accesses the required data block through the address, which Oracle uses a latch to protect its integrity. When a session needs to access this list, a latch is required to ensure that the list does not change in the browsing of the session.

The main reasons for the wait events that generate the buffer latch are:

The Buffer chains is too long, causing the session to search the list for too long, leaving the other sessions in the waiting state.

The same block of data is frequently accessed, which is what we usually call the hot-fast problem.

The buffer chains is too long, we can use multiple buffer pool to create more buffer chains, or use parameter db_block_lru_latches to increase the number of latch, To make it easier for more sessions to get latch, both of these methods can be used at the same time.

This wait event has two parameters:

Latch Addr: The virtual address of a session request Latch in the SGA, the following SQL statement can be used to find its corresponding Latch name according to this address:

SELECT * from V$latch a,v$latchname b where

Addr=latch Addr--Here's the latch addr is the value you see from the Wait event

and a.latch#=b.latch#;

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

 

3. Control file Parallel Write

When there are multiple copies of the control files in the database, Oracle needs to ensure that the information is written synchronously in the various control files, which is a parallel physical process, because the control file is written in parallel, and when such an operation occurs, the controls file parallel is generated. Write waits for an event.

There are many reasons to control file writes frequently, such as:

Log switching is too frequent, causing control file information to be frequently updated accordingly.

There is a bottleneck in system I/O, causing all I/O to wait.

When log switching is too frequent in the system, consider increasing the log file size appropriately to reduce log switching frequency.

When the system has a large number of control file parallel write wait events, you can mitigate I/O contention by storing copies of the control files on different physical disks, such as reducing the number of copies of the controlled files.

This wait event contains three parameters:

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

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

Requests: Write control requested number of I/O.

4. Control file Sequential Read

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

Backing up control files

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

Read the file header information for the control file

Read Control file additional information

This wait event has three parameters:

file#: The file number of the control file to read the information.

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

Blocks: number of blocks of control file data to read.

5. Db file Parallel Read

This is an easily misleading wait event, which is actually not related to parallel operations such as parallel queries and parallel DML. This event occurs when a database is restored, and when some chunks need to be recovered, Oracle reads them from the data file into memory for recovery operations in parallel.

This wait event contains three parameters:

File: The number of files to be read by the operation.

Blocks: The number of blocks of data that the operation needs to read.

Requests: The number of I/O required to perform the operation.

6. Db File Parallel Write

This is a background wait event, it is also not related to the user's parallel operations, it is generated by the background process DBWR, when the background process DBWR to write dirty data on the disk, this wait will occur.

DBWR will write dirty data in parallel to the corresponding data file on disk, and the DBWR will appear before the batch job completes. If this is just a wait event, does not have the very big influence to the user's operation, when accompanies appears the free buffer waits waits the event, explained that at this time the usable space in memory is insufficient, will affect the user's operation, for example affects the user to read the dirty data block into the memory.

When the db file parallel write wait event occurs, you can mitigate this wait by enabling asynchronous I/O to the operating system. When using asynchronous I/O, DBWR does not need to wait until all blocks of data have been written to disk, and it simply waits until the data is written to a percentage to continue the subsequent operation.

This wait event has two parameters:

Requests: The number of I/O required to perform the operation.

Timeouts: timeout to wait.

7. Db file Scattered read

This wait event is often seen in the actual production library, which is a wait event caused by a user action, which occurs when a user issues a SQL operation such as multiple blocks of data per I/O, the most common of which is a full table scan (fts:full table Scan ) and Index quick Scan (iffs:index fast full scan).

The scattered (divergence) in this name may cause many people to think that it reads chunks in a scattered way, but on the contrary, when this wait event occurs, the SQL operation reads the data block sequentially. such as FTS or iffs (if you omit the data blocks that need to be read already in memory).

The scattered here refers to the way in which chunks of read data are stored in memory, and they are read into memory, and are distributed in memory rather than continuous.

This wait event has three parameters:

file#: The file number of the data file that contains the data block to read.

block#: The starting data block number to read.

Blocks: number of blocks of data to read.

8. Db file Sequential Read

This wait event is also common in actual production libraries, which occurs when Oracle needs to read only a single block of data per I/O. The most common cases have indexed access (except Iffs), rollback operations, ROWID access to data in the table, rebuilding control files, and dump files on headers.

Nor does the sequential refer to Oracle's sequential access to data, which, like db file scattered read, refers to the data blocks that are read sequentially stored in memory.

This wait event has three parameters:

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

block#: The starting data block number to read.

Blocks: The number of blocks of data 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 (for example, checkpoint occurs).

When this wait event is apparent, it is important to consider whether the data files in the database are too large, causing Oracle to spend a long time doing all the file header update operations (checkpoint).

This wait event has three parameters:

file#: The file number of the data file that contains the data block that needs to be updated.

block#: The data block number that needs to be updated.

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

Direct Path Read

This wait event occurs when a session reads a block of data directly into the PGA rather than the SGA, which is usually the data that is private to the session, so there is no need to put the SGA as the shared data because it makes no sense. This data is typically derived from data from a temporary segment, such as SQL's sorted data in a session, data generated during parallel execution, and sorted data generated by the hash join,merge Join, because the data is meaningful only for the SQL operations of the current session. So there's no need to put it in the SGA.

When the direct path read wait event occurs, it means that there is a large amount of temporary data generated on the disk, such as sorting, parallel execution, and so on. Or it means that there is not enough free space in the PGA.

This wait event has three parameters:

Descriptor Address: A pointer to a direct read I/O that the current session is waiting for.

The oldest I/O data block address in descriptor addresses.

Block CNT: The number of valid buffer numbers involved in the descriptor address context.