Oracle common wait events and processing methods _oracle

Reading notes db file scattered read DB, db file sequential read Db,free buffer waits,log buffer space,log file Switch,log file sync
We can view the current waiting events in the system and the information about the resources corresponding to the waiting events, so that the types of bottlenecks and their objects are determined by view v$session_wait. V$session_wait's P1, p2, p3 tell us what it means to wait for the event, and the content is different depending on the event, and some of the common wait events and how to locate hot objects and block sessions are described below.
<1> db file scattered read DB files read (too many index reads, full table scan-----Adjust code, put small table 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 the contiguous buffer area, but are dispersed throughout the buffer memory. If this number is large, it means that the table cannot find the index, or only a limited index can be found. Although it may be more efficient to perform a full table scan under certain conditions 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 scans are placed in the Lengduan (cold end) of LRU (least recently Used, least recently) list, you should store smaller tables as much as possible to avoid repeatedly reading them again and again.
==================================================
The p1text=file#,p1 of this kind of event is File_id,p2 is block_id, the Hot 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 read (table join order is poor-----Adjust code, especially table joins)
This event usually shows a read of a single block, such as index reads. When the number of such waits is large, it may show that the table's connection order is poor or that it is indexed without any choice. For a large number of transactional, well tuned systems, this value is mostly normal, but in some cases it may indicate a problem in the system. You should associate this waiting statistic with known issues in the Statspack report, such as inefficient 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 determinant of how often these wait to occur. Problematic hash Area (Hash-area) connections should appear in PGA memory, but they also consume large amounts of memory, resulting in a large number of waits during sequential reads. They may also appear in the form of a direct path read/write wait.
===================================================
The p1text=file#,p1 of this kind of event is File_id,p2 is block_id, the Hot 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;
==================================================
<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 db_buffer_cache. Freeing buffer waits may also indicate that an unchecked SQL causes data to overflow buffer memory with an indexed block, and no buffer is left for specific statements waiting for system processing. This usually means that a considerable amount of DML (insert/update/delete) is being performed, and 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 address this problem, you may need to consider increasing checkpoints, taking advantage of more DBWR processes, or increasing the number of physical disks.
<4> buffer busy waits buffers busy waiting (buffer hot block)
This is to wait for a buffer that is not shared, or is being read into the buffer memory. Buffer busy wait should not be greater than 1%. Check the buffer Wait Statistics section (or V$waitstat):
A, if the wait is in the field header, increase the number of free list (freelist) groups, or increase the distance between pctused and Pctfree.
B, if waiting in the fallback segment (undo) head block, you can solve the buffer problem by increasing the rollback segment (rollback segment);
C, if the wait is in the fallback section (undo) is not the head block, you need to reduce the driver of the table in the same read data density, or increase db_cache_size;
D, if the wait is in the data block, you can move the data to another block to avoid this "hot" block, add a free list in the table or use LMT tablespace;
E, if the wait is in the index block, you should rebuild the index, split the index, or use the Reverse key index.
To prevent buffer-related buffers from being busy waiting, you can also use smaller blocks: In this case, there are fewer records in a single block, so this block is less "busy". When a DML (insert/update/delete) is performed, Oracle DBWR writes information to the block, including all users interested in the block state (ITL, the transaction table of Interest). To reduce the waiting in this area, you can increase the Initrans, which creates space in the block so that you can use multiple ITL slots. You can also increase the pctfree in the table in which the block is located (when the number of slots built according to the specified Initrans is insufficient, this allows the amount of ITL information to reach Maxtrans specified).
<6> Enqueue
Enqueue is a locking mechanism for protecting shared resources. This locking mechanism protects shared resources, such as data in records, to prevent two of people from updating the same data at the same time. Enqueue includes a queuing mechanism, FIFO (first-in first out) queuing mechanism. Note: Oracle's latch mechanism is not FIFO. Enqueue wait usually refers to St Enqueue, HW Enqueue, TX4 Enqueue, and TM Enqueue.
A, ST Enqueue for Space Management and dictionary management of the allocation of table space. Use LMT, or try to precompile a zone, or at least make the next area larger than the table space managed by the problematic dictionary.
B, HW Enqueue is used in conjunction with the high water mark of the segment, and the manual allocation area can avoid this wait.
C, TX4 Enqueue is the most common enqueue waiting, usually the result of one of the following three problems:
The first problem is a duplicate index in a unique index, which requires a commit/rollback (rollback) operation to release the Enqueue.
The second issue is the multiple updates to the same-figure index segment. Because a single bitmap segment may contain multiple row addresses (ROWID), you need to perform a commit or rollback operation to release enqueue when multiple users try to update the same segment.
The third and most likely problem is that multiple users update the same block at the same time. Block-level locking occurs if there are no free ITL slots. This can be easily avoided by increasing initrans and/or Maxtrans to allow multiple ITL slots, or by increasing the Pctfree value on the table.
D, TM enqueue are generated during DML to avoid using DDL for the affected objects. If there are 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 on fast disk)
This wait occurs when the log buffer writes the redo log (redo log) at a slower rate than the LGWR write speed, or when the log switch is too slow. To resolve this problem, you can increase the size of the log file, or increase the log buffer size, or use a disk that is faster to write. You can even consider using solid-state disks because they are very high speed.
<8> log file switch logfile conversion (archive slow-----Add or enlarge redo log)
There are two kinds of situations:
A, log file switch (archiving needed)
When the log is switched because the journal group is looping around but the log file has not been completed, usually there is a serious problem with IO, you can increase the log files and increase the log group, adjust the log_archive_max_processes
B, log file switch (checkpoint incomplete)
When the log is switched, due to the fact that the checkpoint in the log group used in a circle but will be used has not yet been completed, there is usually a serious problem with IO, which can increase the log file and increase the log group
<9> log file Sync logfile Synchronization (submit too frequently----bulk submissions)
When a user commits to notify LGWR to write a log but LWGR is busy, the possible cause is a commit too often or LGWR write log time too long (possibly because of a log IO size 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 conflict caused by Log_buffer; Place log files on high speed disks
<10> Library Cache Pin
This event usually occurs when a session is running pl/sql,view,types and so on, and another session performs recompiling the object, which adds a shared lock to it and then gives it a lock, so that the wait will appear on the session in which it is locked. 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, you can find the related object, its SQL statement is as follows
(That is, the P1raw in the v$session_wait is associated with the Kglhdadr in the X$kglob)
Select Kglnaown,kglnaobj from X$kglob
where Kglhdadr = (select P1raw from v$session_wait
where event= ' Library cache pin '
--Identify the SID of the blocking who caused 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
--Identify the SQL statement that the blocking 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 blocked person
In this way, you can find the source of the library cache pin wait and address the resulting performance problem.
<11> Library Cache Lock
This event is usually caused by the execution of multiple DDL operations, namely, the addition of a exclusive lock on the library cache object and the addition of an exclusive lock to it from another session, so that the wait is generated in the second session. You can find its corresponding object in the base table X$kgllk.
--Query the SID, session user, locked object that caused 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, can also be viewed directly from the v$locked_objects, but no above statement visually based on the SID can be detected in the V$process PID, and then kill or other processing.
<5> latch free (waiting for latch free)
Latch is a low-level queuing mechanism (they are precisely called mutually exclusive mechanisms) to protect the shared memory structure in the system global Zone (SGA). Latch is like a memory lock that is quickly fetched and freed. Latch is used to prevent shared memory structures from being accessed by multiple users at the same time. If latch is unavailable, a latch release failure is recorded. Most latch issues are related to the inability to use a state variable (the library cache latch), a recurring problem (repeat allocation latch), a buffer storage competition problem (buffer store LRU chain), and a "hot" block (buffer memory chain) in the buffer memory. There are also some latch waiting to be related to bugs (program errors), and if this is the case, check the bug report on Metalink.
The Hotspot object for this event can be found by using the following statement, where the &AMP;2 value is a field hladdr in the P1RAW,X$BH in v$session_wait indicating which cache buffer chain latch The block buffer can be passed v$ Latch_children locate 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---binding variable, adjusting shared_pool_size)
This latch is used whenever SQL or pl/sql stored procedures, packages, functions, and triggers are executed. This latch is also used frequently in parse operations.
* Redo Copy (Increase _log_simultaneous_copies parameters)
Redo copy latch is used to redo records from the PGA to the Redo log buffer copy.
* Redo allocation (minimize Redo build, avoid having to submit)
This latch is used to allocate space in the redo log buffer, which can be used to mitigate competition by 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 "hot" block of data creates a memory buffer chain latch competition.
* Cache buffers Lru Chain (adjust SQL, set db_block_lru_latches, or use multiple buffer pools)
The memory buffer LRU chain latch is used to scan the LRU (least recently used) chain of all memory buffer blocks. Too small memory buffers, too large memory buffer throughput, sort operations in excess memory, DBWR speed to keep up with workloads, etc. can cause this latch competition.
<12> db file Parallel write
The latency associated with the DBWR process typically represents an I/O capability problem. It is usually related to the number of I/O slaves configured for multiple DBWR processes or DBWU. It could also mean I/O competition on the device.
<13> db File Single write
Represents a wait that is related to the file header write operation when a checkpoint occurs. This is usually related to the disorder of the file number when the checkpoint synchronizes the header of the data file.
<14> Direct path read and direct path write
Represents a wait that is related to direct I/O reading. Direct path read appears when you read data directly to PGA memory. This type of read request typically takes the form of sort io (when the sort cannot be completed in memory), parallel slave queries, or read-ahead requests. This wait is usually related to I/O capability or I/O competition.
<15> Free Buffer Inspected
Indicates that waiting for a process to find large enough intrinsic space when data is read into the data-resizing buffer. This kind of waiting indicates that the data adjustment buffer is small.
<16> Library Cache Load Lock
Indicates that a wait occurred while loading the object into the library cache. Such events typically represent overloaded or loaded statement overloading or loading, possibly due to SQL statements not shared or shared pool area compilation.
<17> log file Parallel write
Indicates waiting for LGWR to request I/O to the operating system until IO is complete. It may occur before the trigger LGWR write in cases such as 3 seconds, 1/3, 1MB, DBWR. This occurrence usually indicates that the log file has an I/O competition or that the file is on a slower drive
<18> log File Single write
Indicates that a wait occurred while writing the header block of the log file. Generally occurs when a checkpoint occurs.
<19> Transaction
Indicates that a wait has occurred for a blocking rollback operation
<20> Undo Segment Extension
Represents a dynamic extension that is waiting for a rollback segment. This means that the volume of transactions may be too large, and that it may mean that the bedding size of the rollback segment is not optimal, and the minextents is set too small. Consider reducing transactions, or using a larger number of rollback segments.