Oracle Wait Events And Their Solutions.
Buffer Busy Wait:
This wait event happens when a session tries to access a block in the buffer cache but it can't because the buffer is busy, that is another session is modifying the block and the contents of the block are in flux.
Buffer Busy Wait:
SQL> SELECT s.sql_hash_value, sw.p1 file#, sw.p2 block#, sw.p3 reason
FROM v$session_wait sw, v$session s
WHERE sw.event = 'buffer busy waits'
AND sw.sid = s.sid;
no rows selected
SQL> SELECT owner , segment_name , segment_type
FROM dba_extents
WHERE file_id = &FileNumber
AND &BlockNumber BETWEEN block_id AND block_id + blocks -1; 2 3 4
Enter value for filenumber: 1
old 3: WHERE file_id = &FileNumber
new 3: WHERE file_id = 1
Enter value for blocknumber: 2
old 4: AND &BlockNumber BETWEEN block_id AND block_id + blocks -1
new 4: AND 2 BETWEEN block_id AND block_id + blocks -1
no rows selected
Another query that can be very useful is finding the objects in the entire Oracle database that are suffering from "buffer busy waits". The following query gives the top 10 segments:
SQL> SELECT * FROM (
SELECT owner, object_name, subobject_name, object_type,
tablespace_name, value
FROM v$segment_statistics
WHERE statistic_name='buffer busy waits' and owner not like '%SYS%'
ORDER BY value DESC)
WHERE ROWNUM <=10;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE TABLESPACE_NAME VALUE
-------------------- ------------------------------ ------------------------------ ------------------ ------------------------------ ----------
GSMADMIN_INTERNAL DDLID$ TABLE SYSAUX 0
XDB XDB$ROOT_INFO TABLE SYSAUX 0
XDB XDB$SCHEMA_URL INDEX SYSAUX 0
DB File Sequential Read
The db file sequential read wait event has three parameters:
file#, first block#, and block count.
In Oracle Database 11g, this wait event falls under the User I/O wait class.
The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk.
The two important numbers to look for are the TIME_WAITED and AVERAGE_WAIT by individual sessions.
Significant db file sequential read wait time is most likely an application issue.
This event occurs when a user tries to perform a Physical I/O while waiting for sequential reads from the Buffer cache. This type of situation usually occurs when the data on the table is accessed by using index, not full table scan, as a result of single block reading.
If this event occurs, possible reasons are wrong index usage, index fragmentation, excessive I/O traffic on specific disks. To Solve this problem, Query should use Right index and fragmented indexes should be defragmented with Rebuild Index operation.
When you encounter this wait event, which appears very frequently in AWR and ADDM reports, we cannot always say that there is a problem. However, if this wait event takes place, if the database have ‘Enqueue’ and Latch Free and they are spending too much time, then database should be monitored.
DB File Scattered Read
This wait event occurs getting multiblock of physical blocks that are not physically close to each other (neighbors) into buffer cache Scattered, or during a full scan to the buffer cache. So Db file scattered read is to read multiple blocks I/O during the fast full scan.
A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan. The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other.
Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.
Direct path Read
This event occurs when Oracle Instance query data from the Datafiles asynchronously and puts this data into PGA instead of Buffer Cache in SGA.
This type of event usually occurs during the use of Temporary ( Temp ) Tablespace in the Sorting operations, during the creation of Lob segments, and when multiple sessions Full table scan in parallel.
In order to solve this problem, the memory should be increased, parallel operations should not be done unless required, and pay attention to Lob segments reads.
DB CPU
This event represents the total time spent of the users’ queries on the CPU. Oracle’s Background processes (SMON, PMON ..) are not included in this total time.
If this value is high, it means that the Oracle instance spends most of the time on the CPU. To reduce this wait event, the SQLs in the SQL ordered by CPU section in the AWR report must be TUNE.
Logfile sync
This event is known as the time lost as a result of the LGWR process waiting while users initiate a Transaction Commit or Rollback.
If this wait event is available continuously, I/O performance of the LGWR process is probably poor, or Commit is coming too often by the application. The solution to this problem is not to commit too much, if necessary, and to examine the I/O performance of the disk on which the Redo log files are located, and to use a high performance disk such as an SSD disk if necessary.
Enq: TX – row lock contention
row lock contention: This type of event occurs when a user session is trying to update or delete a row held by another session, which is an application design problem. Normally, when a transaction is finished, commit or rollback must be executed to release related rows.
The solution to this problem is that if the session that holds the row is active, then execute commit statement, if it is not active, kill the session or execute rollback the session.
ARCH wait on SENDREQ
This wait event is the total time taken by the Archiver Processes to archive the Standby in the Dataguard and to write these archives to the local disks.
The main reason why this value is high is that the archives sent to the Standby side arrive late due to the network. To solve this problem, it is necessary to optimize the Network and set the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file to an optimized value (32767).
Gc current block busy
This wait event occurs between the nodes of the Cluster database ( Real Application Cluster ). When a transaction requests a block, that request sent to the master instance. Normally, this request is performed by a cache fusion.
However, in some cases, this block transfer is delayed because the corresponding instance is held by the other instance or because the corresponding transaction records cannot be written to the redo logs immediately, in which case this wait event is triggered.
This can be solved by tune the wait event Log Writer process or Solving network problem between Cluster nodes.
Gc cr block busy-wait
ifference is that while the above event is running in current mode, this wait event runs in CR mode. This can be solved by tune the wait event Log Writer process.Read by Other Session
When a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended for. Once that is known, there are several alternative methods for solving the issue.
When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.
Finding the contentions :
When a session is waiting on the "read by other session" event, an entry will be seen in the v$session_wait system view, which will give more information on the blocks being waited for:
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'read by other session';
If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object. The following query will give the name and type of the object:
SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1;
Eliminating contentions:
Depending on the Oracle database environment and specific performance situation the following variety of methods can be used to eliminate contention:
Tune inefficient queries - This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information you can obtain from the operating system.
Redistribute data from the hot blocks - Deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:
SELECT data_object_id
FROM dba_objects
WHERE owner='&owner' AND object_name='&object';
SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,0) start_rowid
FROM dual;
--rowid for the first row in the block
SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,500) end_rowid FROM dual;
--rowid for the 500th row in the block
SELECT <column_list>
FROM <owner>.<segment_name>
WHERE rowid BETWEEN <start_rowid> AND <end_rowid>
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
