Oracle Wait Events and Solution

Different Wait Events and Solution in Oracle

-        List of popular events

-        Eradication

List of some of wait events:

-          Buffer busy wait

-          DB file sequential read

-          Enq: TX – row lock contention

-          Enq : TM – index contention

-          Row cache lock wait

-          Read by other session

1) 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.

Getting more information about buffer busy waits

To get more information about the SQL statement being executed and the block being waited for, trace the session or gather data from V$SESSION V$SESSION_WAIT (or just V$SESSION in 10g and higher):

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;

>> P1 - file number of the data file containing the block being waited for

>> P2 - block number of the block being waited for

>> P3 - this wait is called from many different sections of Oracle code and each uses their own reason code which differ among versions.

To determine the object being waited for, use the P1 (file_number) and P2 (block_number) information from the above query:

SELECT  owner , segment_name , segment_type

FROM  dba_extents

WHERE  file_id = &FileNumber

AND  &BlockNumber BETWEEN block_id AND block_id + blocks -1;

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:

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;

Fixing buffer busy waits :

Once the database object is known, consider the following causes of contention and their solutions.

a) Undo Header - If using Automatic Undo Management (AUM), increase the size of the undo tablespace. If not using AUM, add more rollback segments.

b) Undo Block - If using AUM, increase size of the undo tablespace. If not using AUM, increase rollback segment sizes.

c) Data Block- Data blocks are the blocks that hold the row data in a table or index. The typical problem is that multiple sessions are requesting a block that is either not in cache or in an incompatible mode (read by other session in 10g and higher).

d) Tune inefficient queries that read too many blocks into the buffer cache. These queries could flush out blocks that may be useful for other sessions in the buffer cache. By tuning queries, the number of blocks that need to be read into the cache is minimized, reducing aging out of the existing "good" blocks in the cache.

e) Resolve Hot Blocks - If the queries above consistently return the same block or set of blocks, this is considered a hot block scenario. Delete some of the hot rows and insert them back into the table. Most of the time, the rows will be placed in a different block. The DBA may need to adjust PCTFREE and/or PCTUSED to ensure the rows are placed into a different block. Also talk with the developers to understand why a set of blocks are hot.

f) Place Table in Memory - Cache the table or keep the table in the KEEP POOL. When multiple sessions are requesting the blocks that reside in the disk, it takes too much time for a session to read it into the buffer cache. Other session(s) that need the same block will register 'buffer busy wait'. If the block is already in buffer cache, however, this possibility is eliminated. Another alternative is to increase the buffer cache size. A larger buffer cache means less I/O from disk. This reduces situations where one session is reading a block from the disk subsystem and other sessions are waiting for the block.

g) Fix Low Cardinality Indexes - Look for ways to reduce the number of low cardinality indexes, i.e. an index with a low number of unique values that could result in excessive block reads. This can especially be problematic when concurrent DML operates on table with low cardinality indexes and cause contention on a few index blocks.

h) Adjust PCTFREE/PCTUSED or use ASSM - When sessions insert/delete rows into/from a block, the block must be taken out of the freelist if the PCTFREE threshold reached. When sessions delete rows from a block, the block will be put back in the freelist if PCTUSED threshold is reached. If there are a lot of blocks coming out of the freelist or going into it, all those sessions have to make that update in the freelist map in the segment header. A solution to this problem is to create multiple freelists. This will allow different insert streams to use different freelists and thus update different freelist maps. This reduces contention on the segment header block. You should also look into optimizing the PCTUSED/PCTFREE parameters so that the blocks don't go in and out of the freelists frequently. Another solution is to use ASSM which avoids the use of freelists all together.

i) Increase Extent Size - If extents are too small, Oracle must constantly allocate new extents causing contention in the extent map

2) Db File Sequential Read :

There are two things you can do to these waits: optimize the SQL statement or reduce the average wait time.

The db file sequential read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10g, this wait event falls under the User I/O wait class. Keep the following key thoughts in mind when dealing with the db file sequential read wait event.

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

Common Causes, Diagnosis, and Actions :

The db file sequential read wait event is initiated by SQL statements (both user and recursive) that perform single-block read operations against indexes, rollback (or undo) segments, and tables (when accessed via rowid), control files and data file headers. This wait event normally appears as one of the top five wait events, according to systemwide waits.

Physical I/O requests for these objects are perfectly normal, so the presence of the db file sequential read waits in the database does not necessarily mean that there is something wrong with the database or the application. It may not even be a bad thing if a session spends a lot of time on this event. In contrast, it is definitely bad if a session spends a lot of time on events like enqueue or latch free. This is where this single-block read subject becomes complicated. At what point does the db file sequential read event become an issue? How do you define excessive? Where do you draw the line? These are tough questions, and there is no industry standard guideline. You should establish a guideline for your environment. For example, you may consider it excessive when the db file sequential read wait represents a large portion of a process response time. Another way is to simply adopt the nonscientific hillbilly approach—that is, wait till the users start screaming.

You can easily discover which session has high TIME_WAITED on the db file sequential read wait event from the V$SESSION_EVENT view. The TIME_WAITED must be evaluated with the LOGON_TIME and compared with other nonidle events that belong to the session for a more accurate analysis. Sessions that have logged on for some time (days or weeks) may accumulate a good amount of time on the db file sequential read event. In this case, a high TIME_WAITED may not be an issue. Also, when the TIME_WAITED is put in perspective with other nonidle events, it prevents you from being blindsided. You may find another wait event which is of a greater significance. Based on the following example, SID# 192 deserves your attention and should be investigated:

sql >

select b.sid,

       nvl(substr(a.object_name, 1, 30),

           'P1=' || b.p1 || 'P2=' || b.p2 || 'P3=' || b.p3) "Object_Name",

       a.subobject_name,

       a.object_type

  from dba_objects a, v$session_wait b, x$bh c

 where c.obj = a.object_id(+)

   and b.p1 = c.file#(+)

   and b.p2 = c.dbablk(+)

   and b.event = 'db file sequential read'

UNION

select b.sid,

       nvl(substr(a.object_name, 1, 30),

           'P1=' || b.p1 || 'P2=' || b.p2 || 'P3=' || b.p3) "Object_Name",

       a.subobject_name,

       a.object_type

  from dba_objects a, v$session_wait b, x$bh c

 where c.obj = a.object_id(+)

   and b.p1 = c.file#(+)

   and b.p2 = c.dbablk(+)

   and b.event = 'db file sequential read';

3) Enq: TX - row lock contention :

A wait for the Oracle TX enqueue in mode 6 (row lock contention) is a common enqueue wait, and occurs when a transaction tries to update or delete rows that are currently locked by another transaction.

A wait for the TX enqueue in mode 6 (P1 = 1415053318, P1RAW = 54580006) is the most common enqueue wait. (In Oracle Database 10g/ 11g, the wait event name is enq: TX—row lock contention.) This indicates contention for row-level lock. This wait occurs when a transaction tries to update or delete rows that are currently locked by another transaction. This usually is an application issue. The waiting session will wait until the blocking session commits or rolls back its transaction. There is no other way to release the lock. (Killing the blocking session will cause its transaction to be rolled back.)

The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:

Whenever you see an enqueue wait event for the TX enqueue, the first step is to find out who the blocker is and if there are multiple waiters for the same resource by using the following query. If the blocking session is an ad-hoc process, then the user may be taking a break. In this case, ask the user to commit or roll back the transaction. If the blocking session is a batch or OLTP application process, then check to see if the session is still “alive.” It may be a live Oracle session, but its parent process may be dead or hung. In this case, chances are you will have to kill the session to release the locks. Be sure to confirm with the application before killing a production process.

Drilling the issue :

SQL>

select /*+ ordered */

a.sid "Blocker_SID",a.username "UserName",a.serial#,a.logon_time,

b.type,b.lmode "Mode_Held",b.ctime "Time_Held",c.sid "Waiter_SID",c.request,c.ctime "Time_Waited"

from v$lock b,v$enqueue_lock c, v$session a

where a.sid=b.sid

and b.id1=c.id1(+)

and b.id2=c.id2(+)

and c.type(+)='TX'

and b.type='TX'

and b.block=1

order by b.ctime,c.ctime;

You can discover the resource that is being competed for. The resource ID is available in the V$LOCK.ID1 column of the DML lock (TM) for that transaction. It is also available in the V$SESSION.ROW_WAIT_OBJ# of the waiting session. The following query retrieves the resource of the TX enqueue wait:

select c.sid "Waiter_SID",a.object_name,a.object_type

from dba_objects a, v$session b, v$session_wait c

where (a.object_id=b.row_wait_obj# OR a.data_object_id=b.row_wait_obj#)

and b.sid=c.sid

and chr(bitand(c.p1,-16777216)/16777215)||chr(bitand(c.p1,16711680)/65535) = 'TX'

and c.event='enquue';

Don’t forget to extract the SQL statement that is executed by the waiting session as well as the SQL statement that is executed by the blocking session. These statements will give you an idea of what the waiting session is trying to do and what the blocking session is doing. They are also important points of reference for the application developers so that they can quickly locate the modules. (By the way, the SQL statement that is currently being executed by the blocking session is not necessarily the statement that holds the lock. The statement that holds the lock might have been run a long time ago.)

4) Enq: TM - index contention :

Waits on enq: TM - contention in Oracle indicate there are unindexed foreign key constraints. In this article, I examine how foreign key constraints relate to this wait event and how to tune for this event.

Recently, I was assisting my company to diagnose sessions waiting on the "enq: TM - contention" event. The blocked sessions were executing simple INSERT statements similar to:

INSERT INTO supplier VALUES (:1, :2, :3);Waits on enq: TM - contention indicate there are unindexed foreign key constraints. Reviewing the SUPPLIER table, we found a foreign key constraint referencing the PRODUCT table that did not have an associated index. This was also confirmed by the Top Objects feature of Ignite for Oracle because all the time was associated with the PRODUCT table. We added the index on the column referencing the PRODUCT table and the problem was solved.

Finding the root cause of the enq: TM - contention wait event

Oracle's locking feature to find the blocking sessions, we found the real culprit. Periodically, as the company reviewed its vendor list, they "cleaned up" the SUPPLIER table several times a week. As a result, rows from the SUPPLIER table were deleted. Those delete statements were then cascading to the PRODUCT table and taking out TM locks on it.

Reproducing a typical problem that leads to this wait : - A demo

This problem has a simple fix, but I wanted to understand more about why this happens. So I reproduced the issue to see what happens under the covers. I first created a subset of the tables from this customer and loaded them with sample data.

CREATE TABLE supplier

( supplier_id number(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');

INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');

COMMIT;

CREATE TABLE product

( product_id number(10) not null,

product_name varchar2(50) not null,

supplier_id number(10) not null,

CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id)

REFERENCES supplier(supplier_id)

ON DELETE CASCADE );

INSERT INTO product VALUES (1, 'Product 1', 1);

INSERT INTO product VALUES (2, 'Product 2', 1);

INSERT INTO product VALUES (3, 'Product 3', 2);

COMMIT;

I then executed statements similar to what we found at this customer:

User 1: DELETE supplier WHERE supplier_id = 1;

User 2: DELETE supplier WHERE supplier_id = 2;

User 3: INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');

Similar to the customer's experience, User 1 and User 2 hung waiting on "enq: TM - contention". Reviewing information from V$SESSION I found the following:

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type

FROM v$lock l, dba_objects o, v$session s

WHERE UPPER(s.username) = UPPER('&User')

AND l.id1 = o.object_id (+)

AND l.sid = s.sid

ORDER BY sid, type;

Following along with the solution we used for our customer, we added an index for the foreign key constraint on the SUPPLIER table back to the PRODUCT table:

CREATE INDEX fk_supplier ON product (supplier_id);

When we ran the test case again everything worked fine. There were no exclusive locks acquired and hence no hanging. Oracle takes out exclusive locks on the child table, the PRODUCT table in our example, when a foreign key constraint is not indexed.

Sample query to find unindexed foreign key constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

SELECT *

  FROM (SELECT c.table_name, cc.column_name, cc.position column_position

          FROM user_constraints c, user_cons_columns cc

         WHERE c.constraint_name = cc.constraint_name

           AND c.constraint_type = 'R'

        MINUS

        SELECT i.table_name, ic.column_name, ic.column_position

          FROM user_indexes i, user_ind_columns ic

         WHERE i.index_name = ic.index_name)

 /*where table_name not like '%$%'*/ –- use to find application objects

 ORDER BY table_name, column_position;

5) Row Cache Lock Wait :

In order for DDL (Data Definition Language) to execute, it must acquire a row cache lock to lock the Data Dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows.

A closer look at the row cache lock wait event:

Each row cache lock will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. In this sample select from v$rowcache you can find the enqueue types and the type of activity being performed within the dictionary cache.

PARAMETER COUNT GETS GETMISSES MODIFICATIONS

--------------------- ----- ------------ ---------- -------------

dc_free_extents 0 0 0 0

dc_used_extents 0 0 0 0

dc_segments 5927 131379921 4142831 693734

dc_tablespaces 22 188609668 2436 0

dc_tablespace_quotas 12 22779303 3843 0

dc_files 0 165961 22493 21

dc_users 19 145681559 2078 21

dc_rollback_segments 67 3906307 66 232

dc_objects 1927 70725250 2247804 74803

dc_sequences 4 142714 1599 142714

Common row cache enqueue lock types:

The tuning of the row cache lock wait is dependent upon the activity for each of the enqueue types. Of these, the most common are:

•DC_SEQUENCES:  this row cache lock wait may occur during the use of sequences. Tune by checking sequences to see if they have the cache option specified and if that cache value is reflective of the anticipated simultaneous inserts by the application.

•DC_USED_EXTENTS and DC_FREE_EXTENTS: this row cache lock wait may occur during space management operations where tablespaces are fragmented or have inadequate extent sizes. Tune by checking whether tablespaces are fragmented, extent sizes are too small, or tablespaces are managed manually.

•DC_TABLESPACES: this row cache lock wait may occur during the allocation of new extents. If extent sizes are set too low the application may frequently request new extents which could cause contention. Tune by checking for rapidly increasing number of extents.

•DC_OBJECTS: this row cache lock wait may occur during the recompilation of objects. If object compiles are occurring this can require an exclusive lock which will block other activity. Tune by examining invalid objects and dependencies.

Tuning for the row cache lock wait event:

The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. Checking activity within the V$ROWCACHE view is a good place to start for understanding this relationship, as tuning can only be accomplished with analysis of the enqueue type.

If a trace file is available you may also see the following error:

 >> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<

Also realize that the row cache lock wait event may appear more frequently when using RAC. This is because the library cache and the row cache are global in RAC—causing the row cache lock wait to be more pronounced.

6) 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>