Oracle wait event

 

Wait for the event source to start

The concept of wait events is introduced from ORACLE 7.0.12, with roughly 100 wait events. In ORACLE 8.0, this number has increased to approximately 150, with approximately 220 events in ORACLE 8I and about 400 waiting events in ORACLE 9IR2. in ORACLE 10GR2, there are about 874 waiting events.

Although different versions and components may have different numbers of wait events, you can query the V $ EVENT_NAME view to obtain these wait events:

SQL> select * from v $ version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

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

COUNT (*)

----------

872

ORACLE wait events can be divided into two types, namely IDLE (IDLE) Wait events and non-IDLE (NON-IDLE) Wait events.

1). idle waiting events refer to events that ORACLE is waiting for a job. When diagnosing and optimizing databases, you don't need to pay too much attention to these events.

2) Non-idle wait events are specific to ORACLE activities, which refer to the waiting events that occur during database tasks or application running. These waiting events need to be paid attention to and researched when adjusting the database.

Let's take a look at the main categories of ORACLE 10GR2 and the number of various types of wait events:

SQL> select wait_class #, wait_class_id, wait_class, count (*) as "count"

2 from v $ event_name

3 group by wait_class #, wait_class_id, wait_class

4 order by wait_class #;

WAIT_CLASS # WAIT_CLASS_ID WAIT_CLASS count

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

0 1893977003 Other 588

1 4217450380 Application 12

2 3290255840 Configuration 23

3 4166625743 Administrative 46

4 3875070507 Concurrency 24

5 3386400367 Commit 1

6 2723168908 Idle 62

7 2000153315 Network 26

8 1740759767 User I/O 17

9 4108307767 System I/O 24

10 2396326234 Scheduler 2

11 3871361733 Cluster 47

12 rows selected.

Common idle events include:

• Dispatcher timer

• Lock element cleanup

• Null event

• Parallel query dequeue wait

• Parallel query idle wait-Slaves

• Pipe get

• PL/SQL lock timer

• Pmon timer-pmon

• Rdbms ipc message

• Slave wait

• Smon timer

• SQL * Net break/reset to client

• SQL * Net message from client

• SQL * Net message to client

• SQL * Net more data to client

• Virtual circuit status

• Client message

Some common non-idle wait events include:

• Db file scattered read

• Db file sequential read

• Buffer busy waits

• Free buffer waits

• Enqueue

• Latch free

• Log file parallel write

• Log file sync

Summary of several views:

V $ SESSION indicates the beginning of the database activity, which is regarded as the source.

The V $ SESSION_WAIT view records the waiting status of active sessions in real time, which is the current information.

V $ SESSION_WAIT_HISTORY is a simple enhancement to V $ SESSION_WAIT and records the last 10 waits of active sessions.

V $ ACTIVE_SESSION_HISTORY is the core of ASH, used to record the historical wait information of the Active SESSION, sampling once per second, this part of the content is recorded in the memory, the expected value is to record the content of an hour.

WRH # _ ACTIVE_SESSION_HISTORY is the storage location of V $ ACTIVE_SESSION_HISTORY in AWR.

The information in V $ ACTIVE_SESSION_HISTORY is regularly refreshed to the Server Load balancer every hour, and is retained for analysis by one week by default.

The DBA_HIST_ACTIVE_SESS_HISTORY view is a joint presentation of WRH # _ ACTIVE_SESSION_HISTORY view and several other views. It is usually used to access historical data.

V $ SYSTEM_EVENT because the V $ SESSION records dynamic information and is related to the SESSION lifecycle, it does not record historical information, therefore, ORACLE provides the view V $ SYSTEM_EVENT to record the summary of all wait events of the database since it was started. With this view, you can quickly obtain the overall situation of database operations.

V $ SQLTEXT when there is a bottleneck in the database, you can usually find the sessions waiting for resources from V $ SESSION_WAIT, through the session sid, combined with the V $ SESSION and V $ SQLTEXT views, you can capture the SQL statements that are being executed by these sessions.

 

Important wait events

1. Db file sequential read (sequential reading of data files)

Db file sequential read is a very common I/O-related wait event. It usually displays read operations related to a single data block. In most cases, this wait is recorded when an index block is read or when a data block is read through the index.

This wait event has three parameters: P1, P2, and P3. P1 represents the absolute file number of the file to be read by Oracle, p2 indicates the starting data Block number that Oracle reads from this file. P3 indicates the number of blocks read. Generally, this value is 1, indicating that a single Block is read.

SQL> select name, parameter1, parameter2, parameter3 from v $ event_name where name = 'db file sequential read ';

NAME PARAMETER1 PARAMETER2 PARAMETER3

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

Db file sequential read file # block # blocks

 

If this wait event is significant, it may indicate that there is a problem with the table connection sequence in the Multi-Table connection, and the driver table may not be properly used; or there may be a problem with the use of indexes, not always the best choice for indexing.

In most cases, records can be obtained more quickly through indexing. Therefore, it is common to wait for a large event for a database with standardized encoding and well-adjusted data. However, in many cases, using indexes is not the best choice. For example, if you read a large amount of data from a large table, full table scanning may be significantly faster than index scanning, therefore, during development, you should note that index scanning should be avoided for such queries.

Beginning with Oracle 9iR2, Oracle introduced the new feature of collecting segment-level statistical information. There are 11 types of collected statistical information:

Select * from v $ segstat_name;

In Oracle 10gR2, this type of statistics is increased to 15.

For databases in CBO mode, statistical information should be collected in a timely manner so that SQL can select the correct execution plan to avoid execution errors caused by obsolete statistical information.

2. Db file scattered read (discrete data file reading)

SQL> select * from v $ event_name where name = 'db file scattered read ';

EVENT # EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3

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

188 506183215 db file scattered read file # block # blocks

The V $ EVENT_NAME view shows that the event has three parameters, representing the file number, the starting data block number, and the number of data blocks.

The starting data block number plus the number of data blocks, which means that the Oracle session is waiting for the completion of multiple consecutive read operations. This operation may be related to the continuous reading of Full table scan or quick Full Index Scan. Based on experience, a large number of db file scattered read waits may indicate application problems or missing indexes.

In the diagnosis process of the actual environment, you can use the v $ session_wait view to find the session wait, and then find the problematic SQL and other root causes in conjunction with other views, so as to fundamentally solve the problem. When this wait event is significant, you can also use the v $ session_longops dynamic performance view for diagnosis. This view records transactions that run for a long time (more than 6 seconds.

Starting from Oracle 9i, Oracle adds a new view V $ SQL _PLAN to record the execution plan of SQL statements in the current system Library Cache. You can find the problematic SQL statements through this view.

Using the V $ SQL _PLAN view, you can obtain a large amount of useful information:

Obtain the full table scan object

Select distinct object_name, object_owner from v $ SQL _plan p Where p. operation = 'table access' and p. options = 'full' and object_owner = 'sys ';

Obtain the object for full index Scan

Select distinct object_name, object_owner from v $ SQL _plan p Where p. operation = 'index' and p. options = 'full scan' and object_owner = 'sys ';

Use the combination of V $ SQL _PLAN and V $ SQLTEXT to obtain the SQL statement for full table scan.

Select SQL _text from v $ sqltext t, v $ SQL _plan p Where t. hash_value = p. hash_value And p. operation = 'table access' And p. options = 'full' Order by p. hash_value, t. piece;

3. Direct path read/write (Direct path read/write)

Direct path reading usually occurs when Oracle directly reads data to PGA. This reading does not need to go through SGA. The three parameters of the direct path read wait event are: file # (absolute file number), first block #, and number of blocks.

This type of reading is usually used in the following scenarios:

Disk sorting IO operations

Concurrent query of subordinate Processes

Pre-read operation

The most common case is the first case. In the DSS System, it is normal to have a lot of Direct path read, but in the OLTP system, a significant Direct path read usually means that the system application has problems, this results in a large number of disk sorting and reading operations.

Direct path writing usually occurs in Oracle Directly Writing data from PGA to data files or temporary files. This write operation can bypass SGA. The three parameters of the direct path write wait event are: file # (absolute file number), first block #, and number of blocks.

This type of reading is usually used in the following scenarios:

Direct path Loading

Parallel DML operations

Disk sorting

Write the Uncached "LOB" segment, and then record it as direct path write (lob) waiting

Most common direct path writes are caused by disk sorting. For this write wait, find the data files with the most frequent I/O operations (if there are too many sort operations, it is likely to be temporary files), distribute the load, and speed up the write operations.

If the system has too many disk sorts, temporary tablespace operations will occur frequently. In this case, you can consider allocating different temporary tablespace for different users and using multiple temporary files, write Data to different disks or bare devices to reduce competition and improve performance.

 

Log File wait

SQL> select name from v $ event_name where name like '% log % ';

NAME

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

Log switch/archive

Log file sequential read

Log file single write

Log file parallel write

Log buffer space

Log file switch (checkpoint incomplete)

Log file switch (archiving needed)

Log file switch (clearing log file)

Switch logfile command

Log file switch completion

Log file sync

STREAMS capture process waiting for archive log

12 rows have been selected.

4. Log File Switch (Log File Switch)

Log File Switch: When a Log File is switched, LGWR needs to disable the current Log group and Switch to the next Log group during the database Switch, all Database DML operations are paused until the switchover is complete.

Log File Switch contains two subevents:

1. log file switch (achiving needed), that is, log switching (archiving required)

When this wait event occurs, it is often because when the log Group is fully written and the previous log needs to be overwritten, it is found that the log archiving is not complete. Because Redo cannot be written, the database will be paused when the wait occurs.

This wait may indicate that the I/O is faulty, the archiving process is slow to write, or the log group is not properly set. For different reasons, you can consider the following solutions:

You can consider increasing the log file and adding a log group;

Move archive files to a fast disk;

Adjust the log_archive_max_processes parameters;

2. log file switch (checkpoint incomplete), that is, log switch (check that power is not completed)

When all the log groups are full. LGWR tries to overwrite a log file. If the database fails to write the dirty data protected by the log file (the check point is not completed), this waits for the event to appear. When this wait occurs, the database will also be paused.

This wait event usually indicates that DBWR writing speed is too slow or I/O problems exist. To solve this problem, you may need to consider adding an additional DBWR or adding a log group or log file size.

 

5. Log File Sync (Log File Sync)

When a user submits or rolls back data, LGWR writes session redo logs from the log buffer to the redo log. LGWR notifies the user process after completing the task. The Log File Sync process must wait until the process is completed successfully. For a Rollback operation, this event records the time when the Rollback command line is issued by the user.

If you wait too much, it may indicate that the write efficiency of LGWR is low, or the system submits too frequently. To solve this problem, you can use the log file parallel write wait event, User Commits, User Rollback, and other statistical information to observe the number of submissions or Rollback times.

Possible solutions include:

1 ). improve LGWR performance and try to use a fast disk. Do not store the redo log file on the RAID 5 disk. RAID5 may cause high performance loss for systems frequently written, you can consider using a file system to directly input/output data, or using a raw device to improve the write performance.

2). Batch submission;

3). Use NOLOGGING, UNRECOVERABLE, and other options as appropriate.

6. Log File Single Write

This event is only related to the header block of the Log file. It usually occurs when a new group member is added and the Log switch is promoted. Write a single header block because the part of the header block information is the file number, each file is different. The operation to update the log file header is completed in the background and rarely waits.

7. Log File Parallel Write

Writing Redo records from Log Buffer to Log files mainly refers to regular write operations (relative to Log File Sync ). If a Log Group contains multiple members, write operations are performed concurrently when the Log Buffer is flushed. In this case, the wait event may occur.

Although this write operation is processed in parallel, it will not be completed until all I/O operations have completed the write operation (If your disk supports asynchronous IO or using IO SLAVE, so even if there is only one redo log file member, this wait may occur ).

This parameter is compared with the log file sync time to measure the write cost of log file. It is usually referred to as the synchronization cost rate.

8. Log Buffer Space (Log Buffer Space)

This kind of wait occurs when the database generates logs faster than the LGWR write speed, or when the log switching is too slow. When this wait occurs, it usually indicates that the Redo log buffer is too small. To solve this problem, you can increase the size of the log file or the size of the log buffer.

Another possible cause is the bottleneck in disk I/O. You can consider using a disk with a faster write speed. You can use bare devices to store log files as allowed to improve writing efficiency. In general systems, the lowest standard is not to store log files and data files together, because generally, log files are only written and not read, and separated for storage can improve performance, use raid 10 instead of RAID 5 disk to store log files.

9. Enqueue (queue waiting)

Enqueue is a lock mechanism to protect shared resources. This locking mechanism protects shared resources to avoid data corruption due to concurrent operations. For example, it protects a row of records by locking to avoid simultaneous updates by multiple users. Enqueue uses a queuing mechanism, that is, FIFO (first-in-first-out), to control resource usage.

Enqueue is a collection of lock events. If the wait event in the database is significant, you need to further track which category of lock causes the database wait.

SQL> select name, wait_class from v $ event_name where name like '% enq %' and rownum <11;

-- A lot of records are recorded here and only the first 10 records are retrieved.

NAME WAIT_CLASS

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

Enq: PW-flush prewarm B Application

Enq: RO-contention Application

Enq: RO-fast object reu Application

Enq: KO-fast object che Application

Enq: MV-datafile move Administrative

Enq: TM-contention Application

Enq: ST-contention Configuration

Enq: TX-row lock conten Application

Enq: TX-allocate ITL en Configuration

Enq: TX-index contentio Concurrency

10 rows have been selected.

 

10. Latch Free (Latch release)

Latch Free is usually called a Latch release. This name is often misunderstood. In fact, you should add a "WAIT (WAIT)" before it. When the database waits for this, A process is Waiting for a Latch to be released, that is, Waiting Latch Free.

Latch is a low-level queuing (Serial) mechanism used to protect the shared memory structure in SGA. Latch is like a memory lock that is quickly acquired and released. It is used to prevent the shared memory structure from being accessed by multiple users at the same time.

If latch is unavailable, latch release failure is recorded ). There are two types related to latches:

1) immediately.

2) You can wait.

If a process tries to obtain a latch in immediate mode, and the latch is held by another process, if the latch cannot be available, then the process will not wait to get the latch. It will continue to perform another operation.

Most latch problems are related to the following operations:

It is not very good to use the library cache latch, redo allocation latch, and cache buffers LRU chain ), and cache buffers chain in buffer cache ).

Generally speaking, if we want to design a failed system without considering variable binding, this condition is enough. For systems with high heterogeneity, the consequence of not binding variables is extremely serious.

In addition, there are some latch waiting to be related to bugs. You should pay attention to the announcement of Metalink-related bugs and the release of patches. This issue should be investigated when latch miss ratios is greater than 0.5%.

The latch mechanism of Oracle is competition. Its processing is similar to the CSMA/CD in the network. All user processes compete for latch and are willing to wait for the latch of the willing-to-wait type, if a process does not obtain latch in its first attempt, it will wait and try again. If it fails to obtain latch after _ spin_count contention, the process is transferred to sleep state, it lasts for a specified period of time, then wakes up again, and repeats the previous steps in sequence. in 8i/9i, the default value is _ spin_count = 2000.

If the SQL statement cannot be adjusted, in version 8.1.6 or later, Oracle provides a new initialization parameter: CURSOR_SHARING can force variable binding on the server by setting CURSOR_SHARING = force. Setting this parameter may cause some side effects. For Java programs, there are related bugs. Specific applications should pay attention to the Metalink bug announcement.

11. Free Buffer-Release the Buffer

This wait event indicates that the system is waiting for available space in the memory, which indicates that there is no Free memory space in the current Buffer. If the application is well designed, the SQL writing specification, and the variables are fully bound, this wait may indicate that the Buffer Cache settings are too small, and you may need to increase DB_BUFFER_CACHE.

The Free Buffer wait may indicate that the writing speed of DBWR is not enough, or there is a serious competition on the disk. You can consider adding checkpoints, using more DBWR processes, or increasing the number of physical disks, distribute loads and balance IO.

12. Buffer Busy-Buffer Busy

The waiting event indicates that the system is waiting for a buffer in the unrecoverable able mode, or that it is currently being read into the buffer cache. Generally, the Buffer Busy Wait should not be greater than 1%. Check the buffer wait Statistics Section (or V $ WAITSTAT) to see if the waiting is in the Segment Header ). If yes, you can consider adding a Free List (freelist for Oracle8i DMT) or adding a freelist groups (in many cases, this adjustment is immediate. Before 8.1.6, this freelists parameter cannot be dynamically modified; in versions 8.1.6 and later, to dynamically modify feelists, you must set COMPATIBLE to at least 8.1.6 ).

If this wait is in the undo header, you can add a rollback segment to solve the buffer issue. If the data is waiting on the undo block, we may need to check the relevant applications to reduce large-scale consistent reading, or to reduce the data density in the table of consistent reading (consistent read) or increase DB_CACHE_SIZE.

If you are waiting for a data block, you can consider moving frequently accessed tables or data to another data block or for a larger range of distribution (you can increase the pctfree value and expand the data distribution, reduce competition) to avoid this "Hotspot" data block, or you can consider adding a free list in the table or using a Locally Managed tablespace (Locally Managed Tablespaces ).

If you are waiting for the index block, you should consider re-indexing, splitting the index, or using the reverse key index. To prevent data block-related buffering and waiting, you can also use smaller blocks: in this case, there are fewer records in a single block, therefore, this block is not so "busy"; or you can set a larger pctfree to expand the physical distribution of data and reduce hot competition between records.

When executing DML (insert/update/delete), Oracle writes information to the data block. For data tables with concurrent access to multiple transactions, competition and waiting for ITL may occur, to reduce this wait, you can add initrans and use multiple ITL slots. In Oracle9i, a new concept is introduced: ASSM (Segment Space Management Auto ). Using this new feature, Oracle uses bitmap to manage space usage.

The combination of ASSM and LMT completely changes the storage mechanism of Oracle. The bitmap freelist can reduce the buffer busy wait (buffer busy wait). This problem was a serious problem in earlier versions of Oracle9i.

Oracle claims that ASSM significantly improves the performance of DML concurrent operations, because different parts of the bitmap can be used at the same time, thus eliminating the serialization of searching for the remaining space. Based on the test results of Oracle, the use of the bitmap freelist will eliminate competition for all segment headers (resources) and achieve ultra-fast concurrent insert operations. Among Oracle9i, Buffer Busy wait is no longer common!

13. control file parallel write-control file parallel write

This event may occur when the server process updates all control files. If the wait time is short, you do not need to consider it. If the wait time is long, check whether the physical disk I/O storing the control file has a bottleneck.

Multiple control files are identical copies for images to improve security. For business systems, multiple control files should be stored on different disks. Generally, three are sufficient. If there are only two physical hard disks, the two control files are acceptable. Saving multiple control files on the same disk is meaningless. To reduce the waiting time, consider the following methods:

Reduce the number of control files (to ensure security)

If the system supports asynchronous IO

Transfer control files to physical disks with low I/O burden

14. If the control file sequential read/control file single write control file has a problem with the I/O of a single control file, the two events will occur. If the waiting time is obvious, check a single control file to check whether the storage location has an I/O bottleneck.

15. direct path write-direct path write wait occurs, and the system waits to confirm that all unfinished asynchronous I/O has been written to the disk.

For this write wait, we should find the data files with the most frequent I/O operations (if there are too many sort operations, it is likely to be temporary files), scattered load, accelerate write operations.

If the system has too many disk sorts, temporary tablespace operations will occur frequently. In this case, you can use Local to manage the tablespace, divide it into multiple small files, and write it to different disks or bare devices.

16. Idle Event-Idle Event

In general, idle waiting means that the system waits for nothing or waits for user requests or responses. Generally, we can ignore these waiting events. Idle events can be queried in the stats $ idle_event table.

Let's take a look at the main idle waiting events of the system. We should have a general impression on these events. If the Top 5 waiting events are mostly these events, in general, your system is relatively idle.

Note: organize from Network

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

Blog: http://blog.csdn.net/tianlesoftware