Common Oracle wait events (1)

Oracle wait events are an important basis and indicator for measuring the operating status of Oracle. The concept of wait events is introduced in Oracle7.0.1.2, with approximately 100 wait events. In Oracle 8.0, this number was increased to approximately 150, with approximately 200 events in Oracle8i and about 360 waiting events in Oracle9i. There are two main types of wait events, namely, idle wait events and non-idle wait events.

Idle events refer to Oracle waiting for some kind of work. When diagnosing and optimizing databases, we don't need to pay too much attention to these events.

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

Non-idle wait events are specific to Oracle activities. They are the waiting events that occur during database tasks or application running. We should pay attention to and study these waiting events when adjusting the database.

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

1. db file scattered read-DB file distributed read

This usually shows the wait related to the full table scan. When the database performs a full table scan, the data will be distributed (scattered) into the Buffer Cache based on performance considerations. If the wait event is significant, it may indicate that no index is created or a proper index is not created for some tables scanned by the full table, we may need to check whether these data tables are correctly set.

However, this wait event does not necessarily mean low performance. In some conditions, Oracle will take the initiative to use full table scanning to replace index scanning to improve performance, which is related to the amount of data accessed, in CBO, Oracle performs more intelligent selection. In RBO, Oracle prefers to use indexes.

Because full table scan is placed on the cold end (cold end) in the LRU (Least Recently Used, Least Recently applied) list, for small data tables with frequent access, you can Cache them to the memory to avoid repeated reads.

When the wait event is significant, you can use the v $ session_longops dynamic performance view to diagnose the event. This view records the tasks that run for a long time (more than 6 seconds, most of the operations may be full table scan (in any case, this part of information is worth our attention ).

2. db file sequential read-DB file sequential reading.

This event usually shows Read operations (such as index reading) related to a single data block ). 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 it may indicate that the table is indexed without selection.

In most cases, we say that records can be obtained more quickly through indexes. Therefore, it is normal to wait for a database with good coding specifications and adjustments. 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, we should note that such queries should be avoided using index scanning.

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

4. 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!