Performance Tuning Basics: Oracle Common Wait events

Just an excerpt from the Documentation/Note as a ready reference who reads this post.

Oracle Wait events can be divided into two categories , that is idle (IDLE) to wait for events and non-idle (NON-IDLE) .

1) Idle wait event represents ORACLE is waiting on some work at the time of diagnosis and optimize the database, without much attention to this part of the event.

2) non-idle wait event dedicated for ORACLE activities, waiting for those waiting for an event is the time to adjust the database needs attention and study.

In Oracle 10g and 11g to wait for the event in 1116 . By v $ event_name view to see waiting for the event.

You can check v$event_name view field structure:

SQL> desc v $ event_name;

Name is 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)

To view waiting for the total number of events:

SQL> select count (*) from v $ event_name;

COUNT (*)

----------

1116

To View to wait for the event classification:

SQL> SELECT wait_class #

wait_class_id ,

WAIT_CLASS ,

COUNT ( * ) AS "count"

FROM the v $ event_name

The GROUP BY wait_class # wait_class_id , wait_class

ORDER BY wait_class # ;

WAIT_CLASS-# WAIT_CLASS_ID WAIT_CLASS count

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

0 1,893,977,003 Other 717

1 4,217,450,380 the Application 17

2 3290255840 the Configuration 24

4166625743 administrative 54

4 3,875,070,507 Concurrency 32

3386400367 Commit 2

6 2,723,168,908 the Idle 94

7 2,000,153,315 Network 35

8 1,740,759,767 the User I / O 45

9 4,108,307,767 System I / O 30

10 2396326234 Scheduler 7

11 3,871,361,733 Cluster 50

12 644 977 587 Queueing 9

Event Related Views:-

V$SESSION : representatives of the beginning of the database activity, as originated.

The V$SESSION_WAIT : view for real-time recording activities SESSION wait, current information.

The V$SESSION_WAIT_HISTORY : is V $ SESSION_WAIT of simple enhancements, recording activities SESSION 10 times to wait.

The V$SQLTEXT : database bottlenecks usually can be from the V $ SESSION_WAIT who are waiting for a resource SESSION SESSION The SID the United V $ SESSION and V $ SQLTEXT view can capture these the SESSION is running the SQL statement.

The V$ACTIVE_SESSION_HISTORY : ASH core, for the recorded activity SESSION waiting for information, samples per second, this part of the content is recorded in the memory, the expected value is the contents of the record of one hour.

WRH#_ACTIVE_SESSION_HISTORY : V $ ACTIVE_SESSION_HISTORY in AWR .

V$ACTIVE_SESSION_HISTORY: in will regularly ( once every hour ) to load the library, and the default one week reserved for analysis.

DBA_HIST_ACTIVE_SESS_HISTORY : view is the WRH # _ACTIVE_SESSION_HISTORY view and several other view joint show, usually through access to historical data for this view.

The V $ SYSTEM_EVENT due to the V $ SESSION dynamic information SESSION lifecycle, history does not record information, so the ORACLE database since start to record all waiting for an event summary information. By this view, the user can quickly get a general overview of the database is running.

Common Oracle wait events

Buffer busy waits

Essentially, this wait event generated only as a session waiting for a Buffer (data block), but the cause of this phenomenon is the reason there are many. Two common are:

When a conversation view to modify a data block, but the block of data is modified by another session.

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

Oracle smallest unit operation block ( Block ), even if you want to modify a record, also need this record where the data block to do the operation. When you make changes to the data block, the other sessions will be blocked to make changes to the data in the data block (even if not modified by other users modify current user data), but you can read consistency of this data block ( from Use the undo ). The current user modify this data blocks, will immediately release the exclusive lock in this data block, so that another session can continue to modify it. Modify operation is a very short period of time, such a locking mechanism we called Latch .

When a session to modify a data block is to complete the following steps:

Exclusive access to this data block ( Latch )

Modify the data block.

Release Latch .

Buffer busy waits wait event is common in fast thermal exist in the database, the wait event occurs when frequent multiple users to read or modify the same data block. If the waiting time is very long, we in AWR or statspack report.

This wait event has three parameters. View there are several parameters that we can use the following SQL:

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

In parameter2 PARAMETER3 NAME PARAMETER1

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

buffer busy waits file # block # class #

In the following example, the query and this other event parameter query will not do too many instructions.

File #: waiting for access to the data block where the file id number.

Blocks : waiting for access to the data block number.

ID : 10g before, this value represents a waiting time reasons, 10g , said after waiting for the event category.

The Buffer latch

The storage location of the data in memory block is recorded in a hash list ( cache buffer chains ) which. When a session needs to access a data block, it must first search the hash list, the address of the data block from the list, and then go through the address to access the data block, the list of Oracle uses a latch to protect it integrity. When a session needs to access the list, you need to obtain a Latch , the only way to ensure that this list does not change in this session which.

A buffer latch wait events mainly due to:

Buffer chains too long, resulting in the session search this list takes too long, the other sessions in a wait state.

The same block of data is accessed frequently, what we usually say hot fast.

Generate buffer chains is too long, we can use multiple buffer pool to create more buffer chains , or the parameter DB_BLOCK_LRU_LATCHES to increase latch can get the so that more sessions , these two methods can be used simultaneously .

This wait event has two parameters:

The Latch addr : session application latch in the SGA , through the following SQL statement can be found according to this address it corresponds Latch Name:

select * from v $ latch is a, v $ latchname b where addr = latch addr -- the latch addr is the value that you wait for the event to see and a LATCH # = B LATCH # ;

chain # : the buffer chains hash list index value when the value of this parameter is equal to s 0xfffffff .

Control file parallel write

When a copy of a plurality of control files in the database, Oracle need to ensure the synchronization of information written to the respective control file, which is a parallel physical procedure, called a control file concurrent write, when such an operation occurs, it will generate a control file parallel write wait event.

Control file frequently writes for many reasons, such as:

Log switch too frequently, leading to the control file need frequent updates.

System I / O bottlenecks, all I / O .

When system log switch too often the case, can be considered appropriate to increase the size of the log file to reduce the frequency of log switch.

when the system appears a lot of control file parallel write wait event, for example, by reducing the number of copies of the control file will store a copy of the control file on a different physical disk I / O contention.

This wait event contains three parameters:

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

Blocks : write control file, the number of data blocks.

Requests : write control request number of times.

Control file sequential read

When the database needs to read the information on the control file, the wait for the event, because the control file is a sequential write, sequential read, so called control file sequential read, it often happens in the following situation:

Backup control file

RAC environment

Read the control file header information

Read the control file other information

This wait event has three parameters:

File # : To read the file number of the control file information.

Block # : read the control file information of the initial data block number.

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

Db file parallel read

This is a very easy to mislead wait event actually waiting for the event and parallel operation (such as parallel query, parallel DML ) does not matter. This event occurs when the database recovery, when the number of data blocks need recovery time, Oracle will parallel manner they read from the data file into memory recovery operations.

This wait event contains three parameters:

Files : operation needs to read the file number.

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

Requests : The operation needs to perform I / O times.

Db file parallel write

Wait event which is a background, it is the same, and the parallel operation of the user is not, it is by the background process DBWR when background process DBWR thought to.

DBWR bulk dirty data in parallel is written to the data file on the disk, before this batch job is completed, the DBWR will appear this wait event. If just this one waiting for the event, and do not have much impact on the user's operation, when accompanied by free buffer waits wait event, indicating a lack of available memory space, this time will affect the operation of the user, such as The impact to the user will be dirty data block is read into memory.

Appear db file parallel write wait event can be enabled operating system asynchronous I / O way to alleviate this wait. When using the asynchronous I / O , DBWR longer need to wait until all the data blocks are written to disk all, only need to wait until after the data is written to a percentage, you can continue to follow-up operation.

This wait event has two parameters:

Requests : The operation needs to perform I / O times.

Timeouts : wait for the timeout.

Db file scattered read

This wait event can be seen frequently in the actual production of the library, which is a user action caused by waiting for an event, when a user issues each I / O needs to read more data blocks like SQL operations will produce this wait event The two most common situation is a full table scan ( FTS : Full Table Scan ) index quick scan ( IFFS : index fast full scan ).

Name scattered ( divergence ) , may lead to a lot of people think it scattered way to read the data block, in fact, just the opposite, When this occurs waiting for the event, the SQL operation reads data sequentially block, such as FTS or IFFS mode (if the need to read a data block already exists in memory are ignored).

The here refers to the read data blocks stored in memory, they are read into the memory, based on a decentralized manner exist in memory, rather than continuous.

This wait event has three parameters:

File # : To read a block of data where the data file number.

Block #: To read the data block number.

Blocks : the number of data blocks need to be read.

Db file sequential read

This wait event is also very common in the actual production library, when Oracle needs of each I / O reads only a single block of data such operation, will produce this wait event. The most common index visit (except for the IFFS ) rollback operation ROWID access the data in the table, rebuild the control file header DUMP .

Here sequential Oracle sequential manner to access data, and db file scattered read , it refers to the reading of data blocks in memory in a continuous manner is stored.

This wait event has three parameters:

File # : To read data block lock file number in the data file.

Block #: To read the data block number.

Blocks : The number of data blocks to be read (here should be equal to 1 ).

Db file single write

This wait event usually occurs only in one case, Oracle update data file header information (such as occurred Checkpoint ).

Obviously this wait event, you need to consider is not in the database, the number of data files too, leading Oracle takes a longer time to do all the file header update operations ( checkpoint ).

This wait event has three parameters:

File #: the need to update the data block where the data file number.

Block # : need to update the data block number.

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

Direct path read

This wait event occurs in the session directly read the data block into the PGA instead SGA , these data being read is generally the session private data, so there is no need to put the SGA as shared data, because to do so without significance. These data are the data from temporary segments, such as a session , the data generated by the middle of the process of parallel execution, and Hash Join , merge join the sort of data, because these data only for the current session SQL meaningful operation, and therefore do not need to be placed SGA .

When the occurred direct path read wait event means that the disk has a large number of temporary data, such as sorting, parallel execution. Or means PGA .

This wait event has three parameters:

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

First DBA: a descriptor ADDRess the oldest I / O data block address.

Block cnt: a descriptor Address in the context involved .

Direct path write

the wait events and direct path read session data from the PGA written directly to disk files, rather than through the SGA .

This situation usually occurs when:

The temporary tablespace Sort (out of memory)

Direct loading of data (using the append method to load data)

Parallel DML operations.

This wait event has three parameters:

The Descriptor address: a pointer to the current session is waiting for a direct I / O.

First DBA: a descriptor ADDRess the oldest I / O data block address.

Block cnt: a descriptor Address in the context involved in the effective buffer .

Enqueue

The Enqueue word lock another description language.

When we in AWR report found , the description of the database blocking and waiting can associated AWR report section to determine what kind of lock long waits.

This wait event parameter:

Name : the enqueue 's is the name and type.

Mode : the enqueue the mode.

You can use the following SQL to view the current session waiting for the enqueue name and type:

SELECT CHR (TO_CHAR (bitand ( p1 , - 16777216 )) / 16777215 ) | | CHR (TO_CHAR (bitand ( p1 , 16.71168 million )) / 65535 ) "Lock" , TO_CHAR (bitand ( p1 , 65535 )) "Mode FROM v $ session_wait WHERE event = '&providetheevent'

Oracle 's enqueue contains the following modes:

Oracle’s enqueue types are:- (v$lock_type)

Free buffer waits

When a session blocks of data read from disk to memory, it needs to find free memory space in the memory to store the data block, this wait occurs when there is no free space in the memory; addition, Another session consistency read tectonic blocks of data at a time before the image ( image ), the need to apply the memory to store these newly constructed block of data memory can not find such a memory block will occur this wait event.

When the database free buffer waits wait event, possible causes are:

(1) data buffer is too small, leading to a lack of free space

(2) , DBWR is unable to timely write these dirty data to disk to free up space

This wait event contains two parameters:

File # : the need to read the data block where the data file number.

Block # : need to read the the data

Latch free

in 10g , 10g later, some of the commonly used latch event has been independent:

SQL> select name from v $ event_name where name like 'latch%' order by 1;

NAME

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

latch activity

latch free

latch: Change Notification Hash table latch

latch: In memory undo latch

latch: MQL Tracking Latch

latch: PX hash array latch

latch: Undo Hint Latch

latch: WCR: processes HT

latch: WCR: sync

latch: cache buffer handles

latch: cache buffers chains

latch: cache buffers lru chain

latch: call allocation

latch: change notification client cache latch

latch: checkpoint queue latch

latch: enqueue hash chains

latch: gc element

latch: gcs resource hash

latch: ges resource hash list

latch: lob segment dispenser latch

latch: lob segment hash table latch

latch: lob segment query latch

latch: messages

latch: object queue header operation

latch: parallel query alloc buffer

latch: redo allocation

latch: redo copy

latch: redo writing

latch: row cache objects

latch: session allocation

latch: shared pool

latch: undo global data

latch: virtual circuit queues

Select 33 rows.

Latch free wait event is in the 10g , but specific Latch waits for events to occur.

This wait event has three parameters:

Address: session wait latch address.

Number : latch number, this number can be from the the v $ latchname view the this latch .

SQL> select * from v $ latchname where latch # = number;

Tries: session the attempts to obtain Latch .

Library cache lock

This waiting time occurs when different users share operation with a database object due to resource contention due to concurrent, for example, when a user is doing on a table , other users if you want to access this table, it will occurred library cache lock wait event, it is to wait until the DDL operation is complete, in order to continue operating.

This event consists of four parameters:

Handle address: the address of the object to be loaded.

Lock address : lock address.

Mode : load data fragments of the object.

The Namespace : load the object in v $ db_object_cache view .

Library cache pin

Wait for events and library cache lock event caused by the concurrent operation of the shared pool. Generally, if the Oracle PL / SQL or view this object recompile need these objects to pin to the shared pool. If the object is unique to other users, it will have to a library cache pin .

This wait event also contains four parameters:

Handle address: the address of the object to be loaded.

Lock address : lock address.

Mode : load data fragments of the object.

The Namespace : load the object in v $ db_object_cache view .

Log file parallel write

The background process LGWR is responsible for the log buffer is written to the REDO file, the to reuse log buffer data. REDO LOG group there are 2 , then the LGWR process will be parallel to the REDO information write to these files.

Database wait event bottleneck, the main reason is disk I / O performance or the REDO file the distribution lead to I / O contention, such as with a group of the REDO members file on the same disk.

This wait event has three parameters:

Files : The operation needs to write to the file number.

Blocks : operation needs to write the number of the data block.

Requests : The operation needs to perform I / O times.

Log buffer space

When log buffer , occurs . If the new database redo log is greater than LGWR write to the disk in the redo log must wait for LGWR to complete writing to disk, LGWR must ensure the redo log is written to disk successfully to redo buffer The information in this section.

If a large number of in the database , you can consider the following methods:

(1) to increase redo buffer .

(2) enhance the disk I / O performance

Log file sequential read

This wait event usually occurs in the redo log information read online example, the ARCH process needs to read the redo log redo log information is written sequentially, so the reading is in accordance with the in a sequential manner to read.

This wait event contains three parameters:

Log # : occurrence wait to read redo log sequence number.

Block # : number of data blocks read.

Blocks : read the number of data blocks.

Log file single write

This wait for the events to update the redo log file when adding new members log log group or redo log sequence number change the LGWR will update the redo log file header information.

This wait event contains three parameters:

Log # : write the redo log group number.

Block # : write data block number.

Blocks : write the number of the data block.

Log file switch (archiving needed)

archive mode, the wait for the events in the online log switch ( log file switch ), the need to switch archiving process ( ARCH ) archive after. When the online log file to switch to the next log, the need to ensure that the next log file archiving process has been archived finished, otherwise not allowed to overwrite the online log information (otherwise it will lead to incomplete archive log information).

This wait event is usually due to some reason ARCH process dies, such as the the ARCH process tries to write to an archive file to the destination, but without success (media failure or other reasons), when the ARCH process will die. If this happens, in the database alert log file related error messages.

This wait event has no parameters.

Log file switch (checkpoint incomplete)

To switch to the online journal of record information (such as some of the dirty data blocks when an online log switch to an online log, you must ensure that If an online log file is overwritten, and rely on these redo information to do to restore the data block has not yet been written to disk ( checkpoint ), down out, Oracle will no way for instance recovery.

V $ log view records online logs state. Generally speaking, the online journal of three states.

Active: log protected above information is not yet complete checkpoint .

Inactive : the log protection above information to complete the checkpoint .

Current : the current log.

Oracle instance recovery, will use the state of the current and Active log instance recovery.

If a large number of system to wait for the event, the reason may be that the log file is too small or too few log groups, so the solution is to increase the size of the log file or to increase the number of log group.

This wait event has no parameters.

Log file sync

This is a user session behavior caused by waiting for an event when a session to issue a commit command, the the LGWR process this transaction will be generated redo log from the log buffer which is written to disk, in order to ensure that the information submitted by the user is recorded to safely database.

Session issue the commit command, you need to wait for LGWR to generate this transaction redo successfully written to disk before they can proceed with a follow-up operation, the wait event called log file sync .

A large number of system .

Such the wait events usually occurred in the OLTP system. OLTP system, there are many small transactions, if these transactions are frequently submitted, may cause a large number of log file sync wait event.

This wait event contains a parameter:

Buffer #: redo buffer in the need to be written to the disk buffer .

SQL * Net break / reset to client

When this wait event, a disconnect or reset the connection request is sent to the client, the server side is waiting for the response of the customers, the usual reasons cause the server to the client's network instability.

This wait event contains two parameters:

Driver id: server and client connections use protocol information.

BREAK ? : zero indicates the server to the client sends a reset ( reset ) information, non-zero indicates the server side to the client sends a disconnect ( break ) messages.

SQL * Net break / reset to dblink

The wait for the event, and SQL * Net break / reset to client the same. But it said the database via dblink , a session is established between them, this waiting for events in the communication process between sessions Similarly, if there is this waiting for an event, you need to check the two database between the communication problem.

This wait event has two parameters:

Driver id: server and client connections use protocol information.

BREAK ? : zero indicates the server to the client sends a reset ( reset ) information, non-zero indicates the server side to the client sends a disconnect ( break ) messages.

SQL * Net message from client

This wait event is basically the most common wait event. After a session to establish successful, the client will send to the server-side requests the server-side processing finished client requests return to the client, and continue to wait for the client's request, this time generates SQL * Net message for from client to wait events.

Obviously, this is an idle wait, if the client is no longer send the request to the server-side, server-side has been in the waiting for the event status.

This wait event contains two parameters:

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

# Bytes: number of bytes that the server receives messages from clients.

SQL * Net message from dblink

This wait event and SQL * Net message from client the same, but it said the database through dblink to access another database, they will establish a session, the waiting events in the communication process between sessions.

This wait event is also an idle waiting for the event.

This event consists of two parameters:

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

# Bytes: server-side by dblink received a message from another server-side.

SQL * Net message to client

This waiting for events in the server-side when the message is sent to the client. The possible reason is that the client when the server side to send a message to the client wait too busy, not be able to receive the message sent by the server-side, or network problems may cause the message can not be sent to the client from the server side.

This wait event has two parameters:

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

# Bytes: the number of bytes that the server side to send a message to the client.

SQL * Net message to dblink

This wait event and SQL * Net message to the client the same between network issues cause the message can not be sent.

This waiting time contains two parameters:

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

# Bytes: server-side through dblink sent to another server message bytes.

SQL * Net more data from client

Server-side waits for the user to send more data in order to complete the operation, such as a large SQL text, resulting in a SQL * Net packets can not complete the transfer, so the server side will wait for the client to the SQL text sent me in to do the processing, this time will generate a SQL * Net more data from client to wait for the event.

This waiting time contains two parameters:

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

# Bytes: number of bytes that the server-side message received from the client.

SQL * Net more data from dblink

In a distributed transaction SQL distributed in different databases perform remote database after conclusion of the results through dblink SQL database in the waiting for data from other database by dblink returns The processing time for a long time on a remote database, or have a large number of result sets need to return, or network performance problems will have SQL * Net more data from dblink wait event, it means local database need to wait until all the data from the remote processing is completed through dblink pass back, before they can continue in the machine to perform operations.

This waiting time contains two parameters:

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

# Bytes: server-side through dblink sent to another server message bytes.

SQL * Net more data to client

When too much data needs to be distributed to client server-side, may generate SQL * Net more data to client wait event server was unable to timely information or deal with the results sent to the client, or it may be caused by network issues, it will also produce this waiting.

This waiting time contains two parameters:

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

# Bytes: the number of bytes that the server side to send a message to the client.

SQL * Net more data to dblink

Waiting for events, and SQL * Net more data to client wait time is basically the same, just waiting to happen in a distributed transaction, namely the local database needs to be more data is through dblink . Due to sending too much data or network performance problems, there will be a SQL * Net more data to dblink wait event.

This waiting time contains two parameters:

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

# Bytes: server-side through dblink sent to another server message bytes.

DFS Lock Handle

DFS stands for distributed file system is an ancient name, associated with cluster file system operations, in a Lock manager supplied by vendors in Oracle Parallel Server Environment (prior name for RAC). But, this wait event has morphed and is now associated with waits irrelevant to database files also.

This will occur in RAC environment, possible with sequences especially when you have sequences + cache + Ordered set.

As RAC is multi instance environment, the values of the sequences need to be synchronized as they are need to be ordered.

For example:-

Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)

Session 2 on node-A: nextval –> 102

Session 1 on node-B: nextval -> 103 (DFS Lock handle)

Session 1 on node-B: nextval –> 104

Session 1 on node-A: nextval -> 105 (DFS Lock handle)

Session 1 on node-A: nextval -> 106 (more selects)

Session 1 on node-A: nextval –> 998

Session 1 on node-B: nextval -> 999 (DFS Lock handle)

Session 1 on node-B: nextval -> 1000 (CR read)

if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the sequence.

Ref:- http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/

row cache lock

In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out is when we see the row cache lock wait.

Each row cache lock will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. In this sample select from v$rowcache you can find the enqueue types and the type of activity being performed within the dictionary cache.

Row cache lock will be associated with specific enqueue type (described in v$lock_type) on a dictionary row. Check the following view can provide you the fair idea of dictionary cache aka row cache activity.

View: V$rowcache

In RAC Environments this can appear as the library and dictionary cache’s are global and has to be to synchronized. Further more, this event can appear if the sequences specified as nocache in RAC environments.

ENQ: SQ Contention

The SQ enqueue is the Sequence Cache enqueue (a.k.a. enq: SQ – contention) is used to serialize access to Oracle sequences.

Especially when specified cache+noorder  attribute in RAC environments

…..