Oracle waits for events and solutions

View v$session_wait can be used to view the current wait events for the system and information about the resources that correspond to the wait events, thus identifying the type of bottlenecks and their objects. V$session_wait's P1, p2, and P3 tell us the exact meaning of the wait event, and the content is different depending on the event, and here are some descriptions of how some common wait events are handled and how to locate Hotspot objects and blocking sessions.

<1> db file scattered read DB files distributed (too many indexed reads, full table scan-----Adjustment code, putting small tables into memory)
This situation usually shows the wait associated with the full table scan. When full-table scans are limited to memory, they rarely enter a contiguous buffer, but are dispersed throughout the buffer memory. If the number is large, it indicates that the table cannot find an index, or only a limited index can be found. Although performing a full table scan under certain conditions may be more efficient than an index scan, it is a good idea to check whether these full-table scans are necessary if such a wait occurs. Because full table scanning is placed in the cold junction (cold End) of the LRU (Least recently used, least recently applicable) list, you should try to store smaller tables to avoid repeating them again and again.
==================================================
The p1text=file#,p1 of this type of event is File_id,p2 is block_id, and the Hotspot object (table or index) can be determined by dba_extents
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 sequential reads   (poor table Connection order-----Adjustment code, especially table joins)
This event typically shows a single block of reads (such as index reads). When this number of waits is large, it is possible to display tables with poor connection order or to index them without selection. For a large number of transactional, well-tuned systems, this value is mostly normal, but in some cases it may imply a problem in the system. You should associate this wait statistic with known issues in the Statspack report, such as less efficient SQL. Check the index scan to ensure that each scan is necessary and check the connection order of the multiple table connections. Db_cache_size is also the determining factor for these waiting frequencies. Problematic hash Area (Hash-area) connections should appear in PGA memory, but they also consume a lot of memory, resulting in a lot of waiting in sequential reads. They may also appear in the form of direct path read/write Waits.
===================================================
The p1text=file#,p1 of this type of event is File_id,p2 is block_id, by Dba_ Extents can 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 buffers wait   (increase db_cache_size, speed checkpoint, adjust code)
This wait indicates that the system is waiting for a buffer in memory, Because there is no buffer space available in memory. If all SQL is tuned, this wait may indicate that you need to increase the db_buffer_cache. Releasing a buffer wait may also indicate that an unchecked SQL causes the data to overflow with buffer memory with an index block, and there is no buffer for specific statements waiting for the system to process. This typically indicates that a significant amount of DML (insert/update/delete) is being performed, and that the database writer (DBWR) is not writing fast enough, and the buffer memory may be filled with multiple versions of the same buffer, resulting in very low efficiency. To solve this problem, you may want to consider increasing checkpoints, taking advantage of more DBWR processes, or increasing the number of physical disks.

<4> buffer busy waits buffers busy waiting (in buffer hot block)
This is to wait for a buffer to be used in unshared mode, or to be read into buffer memory. Buffer busy waits should not be greater than 1%. Check the buffer Wait Statistics section (or V$waitstat):
A, if you wait for the field header, increase the number of groups in the Free list (freelist), or increase the distance between pctused and Pctfree.
B, if waiting in the fallback segment (undo) head block, you can increase the rollback segment (rollback segment) to solve the buffer problem;
C, if waiting in the fallback segment (undo) non-head block, you need to reduce the drive consistent read the data density in the table, or increase db_cache_size;
D, if waiting in the data block, you can move the data to another block to avoid the "hot" data block, increase the free list in the table or use the LMT table space;
E, if you wait in the index block, you should rebuild the index, split the index, or use a reverse key index.
To prevent buffer busy waits associated with blocks, 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 performing DML (insert/update/delete), Oracle DBWR writes information to the block, including all users who are "interested" in the block State (the transaction table of Interest, ITL). To reduce the wait in this area, you can increase the Initrans, which creates space in the block, allowing you to use multiple ITL slots. You can also increase the pctfree in the table where the block is located (when the number of slots established according to the specified Initrans is insufficient, this enables the amount of ITL information to reach the specified number of Maxtrans).

<5> latch free (waiting for latch free)
Latch is a low-level queueing mechanism (which is precisely referred to as a mutually exclusive mechanism) to protect the shared memory structure in the system global region (SGA). Latch is like a memory lock that gets and releases quickly. Latch is used to prevent shared memory structures from being accessed concurrently by multiple users. If latch is not available, the latch release failure is logged. Most latch issues are related to the following operations: You cannot use a state variable (library cache latch), duplicate generation problem (duplicate allocation latch), buffer memory contention (buffer store LRU chain), and a "hot" block (buffer memory chain) in buffer memory. There are also some latch waiting for bugs (program errors), and if this is the case, you can check the bug report on Metalink.
The Hotspot object for the event can be found through the following statement, where the &AMP;2 value is the field in P1RAW,X$BH in V$session_wait hladdr indicates which cache buffer chain latch The block buffer can be v$ Latch_children locates which segment are hot 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 (unbound variable---bound variable, adjust shared_pool_size)
This latch is used every time that SQL or PL/e stored procedures, packages, functions, and triggers are executed. This latch is also used frequently in parse operations.
* Redo Copy (Increase _log_simultaneous_copies parameter)
The redo copy latch is used to redo the record from the PGA to the Redo log buffer copy.
* Redo Allocation (Minimize Redo generation, avoid unnecessary commits)
This latch is used to allocate space in the redo log buffer, which can be used to mitigate competition with nologging.
* Row Cache Objects (increase shared pool)
Data dictionary competition. Excessive parsing.
* Cache buffers Chains (_db_block_hash_buckets should be enlarged or set to prime number)
The "overheating" data block causes the memory buffer chain latch to compete.
* Cache buffers Lru Chain (adjust SQL, set db_block_lru_latches, or use multiple buffer pools)
The memory buffer LRU chain latch is used when the LRU (least used) chain of all memory buffer blocks is scanned. Too small memory buffers, too large memory buffer throughput, excessive in-memory sorting operations, DBWR speed to keep up with workloads, and so on, can cause this latch to compete.

<6> Enqueue
Enqueue is a locking mechanism that protects shared resources. This locking mechanism protects shared resources, such as the data in a record, to prevent two of people from updating the same data at the same time. The enqueue includes a queueing mechanism, the FIFO (first in and out) queueing mechanism. Note: Oracle's latch mechanism is not FIFO. Enqueue waits are usually referred to as St Enqueue, HW Enqueue, TX4 Enqueue, and TM Enqueue.
A, ST Enqueue is used for space management and dictionary Management of table space allocation. Use LMT, or try to pre-allocate the zone, or at least make the next area larger than the table space managed by the problematic dictionary.
B, HW Enqueue used with the high water mark of the segment; Manually assigning areas avoids this wait.
C, TX4 Enqueue is the most common enqueue wait, usually the result of one of the following three problems:
The first problem is the duplicate index in the unique index, which requires a commit/rollback (rollback) operation to release Enqueue.
The second problem is multiple updates to the same image index segment. Because a single bitmap segment may contain multiple row addresses (ROWID), when multiple users try to update the same segment, you need to perform a commit or rollback operation to release enqueue.
The third and most likely problem is that multiple users update the same block at the same time. Block-level locking occurs if there is no free ITL slot. This can be easily avoided by increasing the initrans and/or Maxtrans to allow multiple ITL slots, or by increasing the Pctfree values on the table.
D, TM enqueue are generated during DML to avoid using DDL on affected objects. If you have foreign keywords, be sure to index them to avoid this common locking problem.

<7> Log buffer space (write redo slow-----Increase Log_buffer,redo log file onto fast disk)
This wait occurs when log buffer writes to the redo log (redo log) more slowly than LGWR writes, or when log switch is too slow. To solve this problem, you can increase the size of the log file, or increase the size of the log buffer, or use a disk that has a faster write speed. You might even consider using solid-state disks because they are very high speed.

<8> log file switch logfile conversion (archive slow-----Increase or expand redo log)
There are two types of situations:
A, log file switch (archiving needed)
Log switching is due to a circle of logs, but the log archive is not completed, usually IO has serious problems, can increase the log file and increase the log group, adjust the log_archive_max_processes
B, log file switch (checkpoint incomplete)
Log switching is due to the fact that the checkpoint in the log group that is used by the logging group is not completed, usually the IO has serious problems, can increase the log file and increase the log group

<9> log file sync log files sync (commit too often----bulk commit)
When the user commits to notify LGWR to write the log but LWGR is busy, the probable cause is that the commit is too frequent or lgwr one time to write the log too long (perhaps because the log IO size is too large), can adjust _log_io_size, combined with Log_buffer , making (_log_io_size*db_block_size) *n = Log_buffer, which avoids and increases the log_buffer conflict; Place the log file on the high-speed disk

<10> Library Cache pin
This event usually occurs when an object such as Pl/sql,view,types is running in the preceding session, and another session executes to recompile the object. That is, a shared lock is added to the object, and then the lock is added to it, so that the wait will appear on the session where the lock is added. P1,P2 can be associated with X$KGLPN and X$kglob tables
X$kglob (Kernel Generic library Cache Manager Object)
X$KGLPN (Kernel Generic Library Cache Manager Object Pins)
--query X$kglob to find the relevant Object with the SQL statement as follows
(that is, the P1raw in v$session_wait is related to X$kglob in Kglhdadr)
Select Kglnaown,kglnaobj from X$kglob
where Kglhdadr = (select P1raw from v$session_wait
where event= ' library Cache pin ')
--to isolate the SID of the blocked person who caused the wait event
Select Sid from X$kglpn, V$session
where KGLPNHDL in
(select P1raw from V$se Ssion_wait
where wait_time=0 and event like ' Library Cache pin% ')
and kglpnmod <> 0
and v$session.saddr=x$ Kglpn.kglpnuse
--Isolate the SQL statement that the blocker is executing
Select Sid,sql_text
from V$session, V$sqlarea
where v$session.sql_ Address=v$sqlarea.address
and sid=< the Sid> of the blocker;
This allows you to find the root cause of the library cache pin wait to resolve the resulting performance issue.

<11> Library Cache Lock
This event is usually caused by the execution of multiple DDL operations, after an exclusive lock is added to the library cache object and an exclusive lock is added to it from another session, so that the second session generates a wait. You can find its corresponding object in the base table X$kgllk.
--Query the SID, session user, locked object of the blocked person that caused 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, can also be viewed directly from the v$locked_objects, but no above statement intuitively according to the SID can be detected in the V$process PID, and then kill or other processing.

<12> db file Parallel write
The wait associated with the DBWR process generally represents a problem with the I/O capability. This is usually related to the number of I/O slaves configured for multiple DBWR processes or DBWU. Of course it can mean I/O competition exists on the device.

<13> db File Single write
Represents a wait that is related to a file header write operation when a checkpoint occurs. This is usually related to the disorder of the file number when the checkpoint synchronizes the data file header.

<14> Direct path read and direct path write
Represents a wait associated with a direct I/O read. Direct path read appears when the data is read directly to the PGA memory. This type of read request is typically used as: Sort io (when sorting cannot be done in memory), parallel slave queries or pre-read requests, etc. This wait is usually related to I/O capability or I/O competition.

<15> Free Buffer Inspected
Indicates that waiting for the process to find enough intrinsic space when the data is being read into the data adjustment buffer usually this kind of wait indicates that the data adjustment buffer is small.

<16> Library Cache Load Lock
Represents a wait when an object is mounted to the library cache. This event usually represents a heavy load or load on a payload, possibly due to a SQL statement that is either not shared or the shared pool area is small.

<17> log file Parallel write
Represents waiting for LGWR to request I/O from the operating system until the IO is completed. This can occur in cases where the LGWR write is triggered, such as 3 seconds, 1/3, 1MB, or DBWR. This event usually indicates that the log file has an I/O competition or a slow drive on which the file resides

<18> log File Single write
Indicates that a wait has occurred while writing the header block of a log file. Typically occurs when a checkpoint occurs.

<19> Transaction
Indicates a wait for a blocking rollback operation has occurred

<20> Undo Segment Extension
Represents a dynamic extension that waits for a rollback segment. This indicates that the possible transaction volume is too large, and it also means that the size of the minextents may not be optimal, and the setting is small. Consider reducing transactions, or use a larger number of rollback segments.

Oracle waits for events and solutions