Oracle wait events and Solutions

The view v $ session_wait can be used to view the current wait events of the system and the information about the resources corresponding to the wait events, so as to determine the type of the bottleneck and its objects. The P1, P2, and P3 values of V $ session_wait tell us the specific meaning of the wait event, and the content varies according to the event, the following describes how to handle common wait events and how to locate hotspot objects and block sessions.

<1> dB file scattered read dB file distributed reading (too many index reads, full table scans ----- adjust the code to put small tables into the memory)
This usually shows the wait related to the full table scan. When full table scans are restricted to memory, they seldom enter the continuous buffer, but are scattered throughout the buffer memory. If the number is large, it indicates that the table cannot find the index, or only a limited index can be found. Although performing a full table scan under a specific condition may be more effective than an index scan, if such a wait occurs, it is best to check whether these full table scans are necessary. Because full table scan is placed on the cold end (cold end) of the LRU (least recently used, which is least applicable recently) list, we recommend that you store small tables as much as possible, to avoid reading them again and again.
========================================================== ============
P1text = file # for this type of event, P1 is file_id, P2 is block_id, and dba_extents can be used to determine the hotspot object (table or index)
Select owner, segment_name, segment_type
From dba_extents
Where file_id = & file_id
And & block_id between block_id and block_id + & blocks-1;
========================================================== ============

<2> dB file sequential read dB file sequential reading (the table connection sequence is not good ----- adjust the Code, especially the table connection)
This event usually shows the reading of a single block (for example, index reading ). When the number of such Waits is large, the connection sequence of the table may be poor, or the table can be indexed without selection. For a large number of systems with good transaction processing and adjustment, this value is mostly normal, but in some cases it may imply a problem in the system. You should associate this waiting statistic with known issues in the statspack report (such as low-efficiency SQL statements. Check the index scan to ensure that each scan is necessary and to check the connection sequence of Multi-Table connections. Db_cache_size is also the factor that determines the frequency of these waits. Problematic hash-area connections should appear in PGA memory, but they also consume a large amount of memory, resulting in a large amount of waiting During sequential reading. They may also appear in the form of direct path read/write waits.
========================================================== ==============
P1text = file # for this type of event, P1 is file_id, P2 is block_id, and dba_extents can be used to determine the hotspot object (table or index)
Select owner, segment_name, segment_type
From dba_extents
Where file_id = & file_id
And & block_id between block_id and block_id + & blocks-1;
========================================================== ============

<3> free buffer waits release buffer wait (increase db_cache_size, speed up the checkpoint, and adjust the code)
This wait indicates that the system is waiting for the buffer in the memory because there is no available buffer space in the memory. If all the SQL statements have been optimized, this wait may indicate that you need to increase db_buffer_cache. Releasing the buffer wait may also indicate that no selected SQL statements cause data to overflow the buffer memory with index blocks, and no buffer is left for specific statements waiting for system processing. This usually indicates that a considerable amount of DML (insert, update, and delete) is being executed, and the database writer (dbwr) write speed is not fast enough, buffer memory may be filled with multiple versions of the same buffer, resulting in very low efficiency. To solve this problem, you may need to consider adding checkpoints, taking advantage of more dbwr processes, or increasing the number of physical disks.

<4> buffer busy waits buffer busy waiting (buffer hot block)
This is to wait for a buffer that is used in a non-shared manner or that is being read into the buffer memory. The buffer busy waiting time should not exceed 1%. Check the buffer wait Statistics Section (or V $ waitstat ):
A. If you are waiting for the field header, you should increase the number of groups in the Free List (freelist) or the distance between pctused and pctfree.
B. If you want to wait for the Undo header block, you can add a rollback segment to solve the buffer issue;
C. If you are waiting for a non-header block in the Undo segment, You need to reduce the data density in the table to be read by the driver consistently, or increase db_cache_size;
D. If the data block is waiting, move the data to another data block to avoid this "hot" data block, add a free list in the table, or use the LMT tablespace;
E. If you are waiting for the index block, you should re-create the index, split the index, or use 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, so this block is not so busy ". When you execute DML (insert/update/delete), Oracle dbwr writes information to the block, including all users interested in the block status (transaction tables of interest, ITL ). To reduce the waiting time in this area, you can add initrans, which will create space in the block so that you can use multiple ITL slots. You can also add pctfree In the table where the block is located (when the number of slots created based on the specified initrans is insufficient, the number of ITL information can reach the number specified by maxtrans ).

 

<5> latch free (wait for latch free)
Latch is a low-level queuing mechanism (which is precisely referred to as the mutual exclusion mechanism) used to protect the shared memory structure in the global region of the system (SGA. Latch is like a memory lock that is quickly acquired and released. Latch 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. Most latch problems are related to the following operations: the bond variable (database cache latch) cannot be used, the duplicate generation problem (the duplicate allocation latch), the buffer memory competition problem (the buffer storage LRU chain ), and the "hot" block in the buffer memory (the buffer memory chain ). There are also some latch waits related to bugs (program errors). If you suspect this is the case, you can check the bug reports on Metalink.
The hotspot object of this event can be searched through the following statement, where the & 2 value is p1raw in V $ session_wait, And the hladdr field in x $ BH indicates the cache buffer chain latch on which the block buffer is located, you can use v $ latch_children to locate which segments are hotspot blocks.
========================================================== ==============
Select a. hladdr, A. File #, A. dbablk, A. Tch, A. OBJ, B. object_name
From x $ bh a, dba_objects B
Where (A. OBJ = B. object_id or a. OBJ = B. data_object_id)
And a. hladdr = & 2
Union
Select hladdr, file #, dbablk, tch, OBJ, null
From x $ BH
Where OBJ in
(Select OBJ from x $ BH
Where hladdr = & 2
Minus
Select object_id from dba_objects
Minus
Select data_object_id from dba_objects)
And hladdr = & 2
Order by 4;
========================================================== ================
* ** Latch problems and possible solutions
------------------------------
* Library cache and Shared Pool (variable not bound --- Bind Variable, adjust shared_pool_size)
This latch is also frequently used in. parse operations when you execute SQL or PL/SQL stored procedures, packages, functions, and triggers.
* Redo copy (increase the _ log_simultaneous_copies parameter)
Redo copy latch is used to copy redo records from PGA to the redo log buffer.
* Redo allocation (minimize redo generation to avoid unnecessary submission)
This latch is used to allocate space in the redo log buffer. nologging can be used to reduce competition.
* Row cache objects (increasing the sharing pool)
Data Dictionary competition. Excessive parsing.
* Cache buffers chains (_ db_block_hash_buckets should be increased or set as a prime number)
"Overheated" data blocks cause latch competition in the memory buffer chain.
* Cache buffers LRU chain (adjust SQL, set db_block_lru_latches, or use multiple buffer pools)
The LRU chain latch in the memory buffer zone is used to scan the LRU chain of all memory buffer blocks (least recently used. this latch competition may occur if the memory buffer is too small, the memory buffer throughput is too large, the sorting operations in the memory are too large, and the dbwr speed cannot keep up with the workload.

<6> enqueue
Enqueue is a lock mechanism to protect shared resources. This locking mechanism protects shared resources, such as recorded data, to prevent two people from updating the same data at the same time. Enqueue includes a queuing mechanism, namely, FIFO (first-in-first-out. Note: the latch mechanism of Oracle is not FIFO. Enqueue wait usually refers to St enqueue, HW enqueue, tx4 enqueue and TM enqueue.
A. St enqueue is used to allocate tablespaces for space management and dictionary management. Use LMT, or try to pre-allocate the region, or at least make the next region larger than the tablespace managed by the problematic dictionary.
B. HW enqueue is used together with the high-water mark of the segment; manual allocation of the area can avoid this wait.
C and tx4 enqueue are the most common enqueue waits. They are usually the results of one of the following three problems:
The first problem is the duplicate index in the unique index. You need to execute the commit/rollback operation to release the enqueue.
The second problem is that the same bitmap index segment is updated multiple times. Because a single bitmap segment may contain multiple rowids, when multiple users attempt to update the same segment, You need to submit or roll back to release the enqueue.
The third and most likely problem is that multiple users update the same block at the same time. If there is no free ITL slot, block-level locks will occur. By increasing initrans and/or maxtrans to allow multiple ITL slots, or increasing the pctfree value of the table, you can easily avoid this situation.
D. tm enqueue is generated during DML to avoid using DDL for affected objects. If there are external keywords, you must index them to avoid this common locking problem.

<7> log buffer space: log buffer space (slow write redo ----- increase log_buffer, and put the redo log file on the fast disk)
When the speed of log buffer writing redo logs is slower than that of lgwr, or when the log switch is too slow, this will happen. To solve this problem, you can increase the size of the log file, increase the size of the log buffer, or use a disk with a faster write speed. You can even consider using solid state disks because they are very fast.

<8> Log File switch log file conversion (archive is slow-add or expand redo logs)
There are two scenarios:
A. Log File switch (archiving needed)
When switching logs, log groups are cyclically used, but log archiving is not completed. Generally, Io is a serious problem. You can increase the log file and log group, and adjust log_archive_max_processes.
B. Log File switch (checkpoint incomplete)
When switching logs, the log group uses a loop, but the checkpoints in the log group that will be used are not completed yet, which is usually due to Io serious problems, you can increase log files and add log groups.

<9> Log File sync log file synchronization (too frequent submission-Batch submission)
When a user sends a message to the lgwr to write logs but the lwgr is busy, the possible cause is that the commit is too frequent or the lgwr is too long to write logs at a time (probably because the size of one log Io is too large ), you can adjust _ log_io_size and combine it with log_buffer to make (_ log_io_size * db_block_size) * n = log_buffer, so as to avoid conflicts with increase of log_buffer; place log files on high-speed Disks

<10> library cache pin
This event usually occurs when a session is running objects such as PL/SQL, view, and types, and another session is executed to re-compile these objects. That is, a shared lock is added to the object, then add an exclusive lock to the lock, so that the wait will appear in the session with the exclusive lock. P1 and P2 can be related to tables x $ kglpn and x $ kglob.
X $ kglob (kernel generic library cache manager object)
X $ kglpn (kernel generic library cache manager object pins)
-- Query x $ kglob to find related objects. The SQL statement is as follows:
(Connect p1raw in V $ session_wait to kglhdadr In x $ kglob)
Select kglnaown, kglnaobj from x $ kglob
Where kglhdadr = (select p1raw from V $ session_wait
Where event = 'library cache pin ')
-- Locate the SID of the blocked person that causes the wait event
Select Sid from x $ kglpn, V $ session
Where kglpnhdl in
(Select p1raw from V $ session_wait
Where wait_time = 0 and event like 'library cache pin % ')
And kglpnmod <> 0
And v $ session. saddr = x $ kglpn. kglpnuse
-- Find the SQL statement being executed by the blocker
Select Sid, SQL _text
From v $ session, V $ sqlarea
Where V $ session. SQL _address = V $ sqlarea. Address
And Sid = <blocked Sid>
In this way, you can find the root cause of the "library cache pin" wait to solve the resulting performance problems.

<11> library cache lock
This event is usually caused by executing multiple DDL operations. That is, after an exclusive lock is added to the library cache object, an exclusive lock is added to it from another session, in this way, a wait will be generated in the second session. You can find the corresponding object in the base table x $ kgllk.
-- Query the SID, session user, and locked object that causes the blocking of the wait event
Select B. Sid, A. user_name, A. kglnaobj
From x $ kgllk A, V $ session B
Where a. kgllkhdl in
(Select p1raw from V $ session_wait
Where wait_time = 0 and event = 'library cache lock ')
And a. kgllkmod <> 0
And B. saddr = A. kgllkuse
Of course, you can also view it directly from V $ locked_objects, but without the above statement, you can find the PID in V $ process according to the Sid, and then kill it or perform other processing.

<12> dB file parallel write
The wait related to the dbwr process generally indicates a problem with the I/O capability. it is usually related to the number of configured dbwr processes or the number of I/O slaves of dbwu. of course, it may also mean that there is an I/O competition on the device.

<13> dB file single write
It indicates the waiting time related to the write operation of the file header when the checkpoint occurs. It is usually related to the disorder of the file number when the data file header is synchronized at the checkpoint.

<14> direct path read and direct path write
It indicates the wait related to direct I/O read. when data is directly read to the PGA memory, direct path read appears. this type of read requests is typically used as: Sorting io (when sorting cannot be completed in the memory), parallel slave query or pre-read requests. usually this wait is related to I/O capability or I/O competition.

<15> free buffer inspected
It indicates waiting for the process to find enough internal space when reading data into the data to adjust the cache area. This type of waiting usually indicates that the data adjustment cache area is too small.

<16> library cache load lock
It indicates that a wait occurs when the object is loaded to the database cache. this type of event usually indicates a heavy statement overload or loading with a negative hormone, possibly because the SQL statement is not shared or the area of the Shared Pool is small.

<17> Log File parallel write
Wait for lgwr to request I/O from the operating system until I/O is completed. when lgwr writing is triggered, such as 3 seconds, 1/3, 1 MB, or dbwr writing, it may occur. this event usually indicates that the log file has an I/O competition or the drive where the file is located is slow.

<18> log file single write
It indicates that a wait occurs when the log file header is written. Generally, it occurs when a checkpoint occurs.

<19> transaction
Indicates a wait for the rollback operation to be blocked.

<20> undo segment Extension
It indicates the dynamic expansion waiting for the rollback segment. this indicates that the transaction volume may be too large, it also means that the sleep size of the rollback segment may not be optimal, and minextents is set too small. consider reducing transactions or using rollback segments with a larger number of cells.