An index should be considered for rebuilding under any of the following conditions:
* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
Fast Index Rebuild.
The REBUILD option of the ALTER INDEX statement is executed to quickly rebuild an index using the existing index instead of the table:
alter index emp_idx1 rebuild parallel
tablespace user_tblspc1
storage (pctincrease 0);
Index altered.
Any Modifications to the STORAGE clause can be made at this time and the parallel option can also be used if parallelism is enabled in your database.
Using the REBUILD option of the ALTER INDEX statement for quickly rebuilding an index requires the existing index instead of the table. So necessary space should be available to store both the indexes during the REBUILD operation.
You can also REBUILD online clause to allow DML operations on the table. Index Rebuild online is not applicable for Bitmap indexes or for indexes that enfore referential Integirity Constraints.
This code genenates a script to rebuld all the indexes with height greater than 3 for all the users except for SYS user. The script needs to be executed from SYS schema. This can also be executed from individual schemas by minor changes. If you need to exclude indxes of some other users that can also be added/changed in cGetIdx cursor. To execute this code from any schema other than SYS. Execute on DBMS_SQL must be granted from SYS(not system) to the the specified user.
Indexes with height less than three (3) should not be rebuilded. If you want to go for indexes with less heights that can also be customized by changing the height parameter.
Set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
Friday, January 2, 2009
Automatic index rebuild and reorganizing
----------------------------------------------------
-- Automatic index rebuild and reorganizing
--
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation
--
-- Written by perb@episerver
----------------------------------------------------
declare @sql nvarchar(1000)
declare @indexname nvarchar(255)
declare @tablename nvarchar(255)
declare @fragmentation float
declare @msg nvarchar(512)
declare @dbid int
declare @indexCount int
set nocount on
set @dbid=db_id()
set @indexCount = 0
declare c CURSOR FOR
Select 'ALTER INDEX ' + i.name + ' ON ' + object_name(d.object_id) + CASE WHEN avg_fragmentation_in_percent>30 THEN ' REBUILD' ELSE ' REORGANIZE' END as [sql],
convert(decimal(5,2), avg_fragmentation_in_percent) fragmentation,object_name(d.object_id),i.name
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where d.avg_fragmentation_in_percent > 10
order by avg_fragmentation_in_percent desc
select N'See "Messages" tab for progress!' as Info
raiserror (N'Reading index fragmentation..',0,1) WITH NOWAIT
raiserror (N' ',0,1) WITH NOWAIT
open c
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
while @@FETCH_STATUS = 0
begin
SET @msg = N'Found fragmented index..'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Name: ' + @indexname
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Table: ' + @tablename
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Fragmentation: ' + cast(@fragmentation as nvarchar) + '%s'
raiserror (@msg,0,1,'%') WITH NOWAIT
exec sp_executesql @sql
set @indexCount = @indexCount + 1
SET @msg = N' Defrag done!'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' '
raiserror (@msg,0,1) WITH NOWAIT
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
end
close c
deallocate c
SET @msg = N'--------------------------------'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N'Found and defragged ' + cast(@indexCount as nvarchar) + N' index(es)'
raiserror (@msg,0,1) WITH NOWAIT
-- Automatic index rebuild and reorganizing
--
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation
--
-- Written by perb@episerver
----------------------------------------------------
declare @sql nvarchar(1000)
declare @indexname nvarchar(255)
declare @tablename nvarchar(255)
declare @fragmentation float
declare @msg nvarchar(512)
declare @dbid int
declare @indexCount int
set nocount on
set @dbid=db_id()
set @indexCount = 0
declare c CURSOR FOR
Select 'ALTER INDEX ' + i.name + ' ON ' + object_name(d.object_id) + CASE WHEN avg_fragmentation_in_percent>30 THEN ' REBUILD' ELSE ' REORGANIZE' END as [sql],
convert(decimal(5,2), avg_fragmentation_in_percent) fragmentation,object_name(d.object_id),i.name
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where d.avg_fragmentation_in_percent > 10
order by avg_fragmentation_in_percent desc
select N'See "Messages" tab for progress!' as Info
raiserror (N'Reading index fragmentation..',0,1) WITH NOWAIT
raiserror (N' ',0,1) WITH NOWAIT
open c
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
while @@FETCH_STATUS = 0
begin
SET @msg = N'Found fragmented index..'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Name: ' + @indexname
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Table: ' + @tablename
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Fragmentation: ' + cast(@fragmentation as nvarchar) + '%s'
raiserror (@msg,0,1,'%') WITH NOWAIT
exec sp_executesql @sql
set @indexCount = @indexCount + 1
SET @msg = N' Defrag done!'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' '
raiserror (@msg,0,1) WITH NOWAIT
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
end
close c
deallocate c
SET @msg = N'--------------------------------'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N'Found and defragged ' + cast(@indexCount as nvarchar) + N' index(es)'
raiserror (@msg,0,1) WITH NOWAIT
Wednesday, December 31, 2008
Identify the blocking session
Identify the blocking session
Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.
SQL> select * from v$lock ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1
.... .... ... ... .... .... .... .... .... ....
Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.
In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.
To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
SQL> /
SID 'ISBLOCKING' SID
---------- ------------- ----------
422 IS BLOCKING 479
1 row selected.
Even better, if we throw a little v$session into the mix, the results are highly readable:
SQL> select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 )
1 row selected.
There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.
Lock type and the ID1 / ID2 columns
In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.
The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)
There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.
Lock Modes
You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1
Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.
You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.
TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0
Identifying the locked object
Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.
SQL> select object_name from dba_objects where object_id=88519 ;
OBJECT_NAME
--------------
TSTLOCK
Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.
Identifying the locked row
We can get this information from v$session by looking at the v$session entry for the blocked session:
SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2* from v$session where sid=479 ;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
88519 16 171309 0
This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:
SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA
And, of course, this lets us inspect the row directly.
SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;
FOO BAR
--- ---
1 a
Conclusion
We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.
Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.
SQL> select * from v$lock ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1
.... .... ... ... .... .... .... .... .... ....
Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.
In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.
To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
SQL> /
SID 'ISBLOCKING' SID
---------- ------------- ----------
422 IS BLOCKING 479
1 row selected.
Even better, if we throw a little v$session into the mix, the results are highly readable:
SQL> select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 )
1 row selected.
There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.
Lock type and the ID1 / ID2 columns
In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.
The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)
There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.
Lock Modes
You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1
Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.
You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.
TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0
Identifying the locked object
Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.
SQL> select object_name from dba_objects where object_id=88519 ;
OBJECT_NAME
--------------
TSTLOCK
Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.
Identifying the locked row
We can get this information from v$session by looking at the v$session entry for the blocked session:
SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2* from v$session where sid=479 ;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
88519 16 171309 0
This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:
SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA
And, of course, this lets us inspect the row directly.
SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;
FOO BAR
--- ---
1 a
Conclusion
We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.
How do you find out who is locking a specific row in a table?
1- Select the sid and other desired columns from v$session where lockwait is not null
2- Select * from v$lock where sid = the sid(s) from the prior step
3- Look at the ID1 column for the requested but not held lock
4- Select * from v$lock where ID1 = the ID1 value returned in the prior step
5- Select from v$session for the sid found in step 4 that has the lock
At this point you can also use the sid to query against v$sql or any of the other v$ tables that contains SQL statements to see the SQL being executed by the blocking session if the v$session table shows a status of ACTIVE for this session. SQL does not normally display for INACTIVE sessions or sessions executing DDL.
Here is a series of sample SQL scripts to perform items 1 – 5:
Find all lock waited sessions:
set echo off
rem
rem filename: session_locked.sql
rem SQL*Plus script to display selected sessions and related process infor-
rem mation for all Oracle sessions blocked from processing due to a lock
rem held by another session.
rem
rem 11/27/95 s3527 m d powell new script
rem 19991207 Mark D Powell Chg headings to reflect Oracle terminology
rem
set verify off
column machine format a08 heading "APPL.|MACHINE"
column sid format 99999
column serial# format 99999
column spid format 99999 heading "ORACLE|SERVER|PROCESS"
column process format 99999 heading "USER|APPL.|PROCESS"
column username format a12
rem
select
s.username, s.status, s.sid, s.serial#,
p.spid, s.machine, s.process, s.lockwait
from v$session s, v$process p
where s.lockwait is not null
and s.paddr = p.addr
/
Find lock information based on SID of lock waited session:
set echo off
rem
rem filename: lock_sid.sql
rem SQL*Plus script to display Oracle dml locks held by an Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem
set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9
REM
REM If type = TM then id1 = object id
REM TX rbs number and slot
REM id2 = wrap number
REM
select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where sid = &session
/
Find holder and other waiters for a lock ID:
set echo off
rem
rem filename: lock_find.sql
rem SQL*Plus script to display all Oracle sessions holding or attempting
rem to obtain a particular lock (id1) on an object.
rem
rem 11/27/95 s3527 m d powell new script
rem 04/28/98 dbawrk m d powell + comments on value of id1 and id2
rem
set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9
rem
rem If type = TM then id1 = object id
rem TX rbs number and slot
rem id2 = wrap number
rem
select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where id1 = '&lockno'
/
Find the SQL for the sid
set echo off
rem
rem filename: sql_user.sql
rem SQL*Plus script to display the sql being executed for a particular
rem Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem
set verify off
select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid
/
Just change the where clause in the first query from where lockwait is not null to where s.sid = &session_id and you have the code for session_sid.sql
Once you know the blocking session you now need to determine if the blocking session is a run away session, the user has gone to lunch or left for the day, or is a sign of a system problem. That task is beyond this article, which I hope will give you a basic introduction to lock blocker determination and tracking.
2- Select * from v$lock where sid = the sid(s) from the prior step
3- Look at the ID1 column for the requested but not held lock
4- Select * from v$lock where ID1 = the ID1 value returned in the prior step
5- Select from v$session for the sid found in step 4 that has the lock
At this point you can also use the sid to query against v$sql or any of the other v$ tables that contains SQL statements to see the SQL being executed by the blocking session if the v$session table shows a status of ACTIVE for this session. SQL does not normally display for INACTIVE sessions or sessions executing DDL.
Here is a series of sample SQL scripts to perform items 1 – 5:
Find all lock waited sessions:
set echo off
rem
rem filename: session_locked.sql
rem SQL*Plus script to display selected sessions and related process infor-
rem mation for all Oracle sessions blocked from processing due to a lock
rem held by another session.
rem
rem 11/27/95 s3527 m d powell new script
rem 19991207 Mark D Powell Chg headings to reflect Oracle terminology
rem
set verify off
column machine format a08 heading "APPL.|MACHINE"
column sid format 99999
column serial# format 99999
column spid format 99999 heading "ORACLE|SERVER|PROCESS"
column process format 99999 heading "USER|APPL.|PROCESS"
column username format a12
rem
select
s.username, s.status, s.sid, s.serial#,
p.spid, s.machine, s.process, s.lockwait
from v$session s, v$process p
where s.lockwait is not null
and s.paddr = p.addr
/
Find lock information based on SID of lock waited session:
set echo off
rem
rem filename: lock_sid.sql
rem SQL*Plus script to display Oracle dml locks held by an Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem
set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9
REM
REM If type = TM then id1 = object id
REM TX rbs number and slot
REM id2 = wrap number
REM
select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where sid = &session
/
Find holder and other waiters for a lock ID:
set echo off
rem
rem filename: lock_find.sql
rem SQL*Plus script to display all Oracle sessions holding or attempting
rem to obtain a particular lock (id1) on an object.
rem
rem 11/27/95 s3527 m d powell new script
rem 04/28/98 dbawrk m d powell + comments on value of id1 and id2
rem
set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9
rem
rem If type = TM then id1 = object id
rem TX rbs number and slot
rem id2 = wrap number
rem
select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where id1 = '&lockno'
/
Find the SQL for the sid
set echo off
rem
rem filename: sql_user.sql
rem SQL*Plus script to display the sql being executed for a particular
rem Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem
set verify off
select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid
/
Just change the where clause in the first query from where lockwait is not null to where s.sid = &session_id and you have the code for session_sid.sql
Once you know the blocking session you now need to determine if the blocking session is a run away session, the user has gone to lunch or left for the day, or is a sign of a system problem. That task is beyond this article, which I hope will give you a basic introduction to lock blocker determination and tracking.
Tuesday, December 30, 2008
Oracle enqueue wait tips
There are many types of Oracle enqueues, here is a sample of the most common types:
· CF enqueue – The CF enqueue is a Control File enqueue (a.k.a. enq: CF – contention) and happens during parallel access 6to the control files. The CF enqueue can be seen during any action that requires reading the control file, such as redo log archiving, redo log switches and begin backup commands.
· CI Enqueue - The CI enqueue is the Cross Instance enqueue (a.k.a. enq: US - contention) and happens when a session executes a cross instance call such as a query over a database link.
· FB enqueue – This is the Format Block enqueue, used only when data blocks are using ASSM (Automatic Segment Space Management or bitmapped freelists). As we might expect, common FB enqueue relate to buffer busy conditions, especially since ASSM tends to cause performance problems under heavily DML loads.
· HV enqueue – The HV enqueue (a.k.a. enq: HV - contention) is similar to the HW enqueue but for parallel direct path INSERTs.
· HW enqueue – The HW High Water enqueue (a.k.a. enq: HW – contention) occurs when competing processing are inserting into the same table and are trying to increase the high water mark of a table simultaneously. The HW enqueue can sometimes be removed by adding freelists or moving the segment to ASSM.
· KO enqueue – The KO enqueue (a.k.a. enq: KO - fast object checkpoint) is seem in Oracle STAR transformations and high enqueue waits can indicate a sub-optimal DBWR background process.
· PE enqueue – The PE enqueue (a.k.a. enq: PE – contention) is the Parameter Enqueue, which happens after “alter system” or “alter session” statements.
· PS enqueue – The PS enqueue is the Parallel Slave synchronization enqueue (a.k.a enq: PS – contention), which is only seen with Oracle parallel query. The PS enqueue happens when pre-processing problems occur when allocating the factotum (slave) processes for OPQ.
· RO Enqueue – The RO enqueue is the Reuse Object enqueue and is a cross-instance enqueue related to truncate table and drop table DDL operations.
· SQ enqueue – The SQ enqueue is the Sequence Cache enqueue (a.k.a. enq: SQ – contention) is used to serialize access to Oracle sequences.
· SS enqueue – The SS enqueue is the Sort Segment enqueue (a.k.a. enq:SS – contention) and these are related to the sorting of large result sets.
· SS enqueue – The SS enqueues are Sort Segment enqueues (a.k.a. enq: SS – contention), and occur when a process is performing a large sort operation.
· ST enqueue – The ST enqueue can be seen in a partitioned environment when a large number of partitions are created simultaneously.
· TC enqueue – The TC enqueue is related to the DBWR background process and occur when “alter tablespace” commands are issued. You will also see the TC enqueue when doing parallel full-table scans where rows are accessed directly, without being loaded into the data buffer cache.
· TM enqueue – The TM enqueue related to Transaction Management (a.k.a. enq: TM - contention) and can be seen when tables are explicitly locked with reorganization activities that require locking of a table.
· TQ enqueue – The TQ enqueue is the Queue Table enqueue (a.k.a. enq: TQ - DDL contention) and happens during Data ump (export import) operations.
· TS enqueue – The TS enqueue is the temporary segment enqueue (a.k.a. enq: TS – contention) and these enqueues happen during disk sort operations.
· TT enqueue – The TT enqueue (a.k.a. enq: TT – contention) is used to avoid deadlocks in parallel tablespace operations. The TT enqueue can be seen with parallel create tablespace and parallel point in time recovery (PITR)
· TX runqueue – The TX enqueue is the transaction enqueue (a.k.a. enq: TX – contention) and is commonly related to buffer busy waits, in conditions where multiple transaction attempt to update the same data blocks.
enq: TX - row lock contention
enq: TX - allocate ITL entry
enq: TX - row lock contention
· UL enqueue – The UL enqueue is a User Lock enqueue (a.k.a. enq: UL – contention) and happens when a lock is requested in dbms_lock.request. The UL enqueue can be seen in Oracle Data Pump.
· US Enqueue - The US enqueue happens with Oracle automatic UNDO management was undo segments are moved online and offline.
· CF enqueue – The CF enqueue is a Control File enqueue (a.k.a. enq: CF – contention) and happens during parallel access 6to the control files. The CF enqueue can be seen during any action that requires reading the control file, such as redo log archiving, redo log switches and begin backup commands.
· CI Enqueue - The CI enqueue is the Cross Instance enqueue (a.k.a. enq: US - contention) and happens when a session executes a cross instance call such as a query over a database link.
· FB enqueue – This is the Format Block enqueue, used only when data blocks are using ASSM (Automatic Segment Space Management or bitmapped freelists). As we might expect, common FB enqueue relate to buffer busy conditions, especially since ASSM tends to cause performance problems under heavily DML loads.
· HV enqueue – The HV enqueue (a.k.a. enq: HV - contention) is similar to the HW enqueue but for parallel direct path INSERTs.
· HW enqueue – The HW High Water enqueue (a.k.a. enq: HW – contention) occurs when competing processing are inserting into the same table and are trying to increase the high water mark of a table simultaneously. The HW enqueue can sometimes be removed by adding freelists or moving the segment to ASSM.
· KO enqueue – The KO enqueue (a.k.a. enq: KO - fast object checkpoint) is seem in Oracle STAR transformations and high enqueue waits can indicate a sub-optimal DBWR background process.
· PE enqueue – The PE enqueue (a.k.a. enq: PE – contention) is the Parameter Enqueue, which happens after “alter system” or “alter session” statements.
· PS enqueue – The PS enqueue is the Parallel Slave synchronization enqueue (a.k.a enq: PS – contention), which is only seen with Oracle parallel query. The PS enqueue happens when pre-processing problems occur when allocating the factotum (slave) processes for OPQ.
· RO Enqueue – The RO enqueue is the Reuse Object enqueue and is a cross-instance enqueue related to truncate table and drop table DDL operations.
· SQ enqueue – The SQ enqueue is the Sequence Cache enqueue (a.k.a. enq: SQ – contention) is used to serialize access to Oracle sequences.
· SS enqueue – The SS enqueue is the Sort Segment enqueue (a.k.a. enq:SS – contention) and these are related to the sorting of large result sets.
· SS enqueue – The SS enqueues are Sort Segment enqueues (a.k.a. enq: SS – contention), and occur when a process is performing a large sort operation.
· ST enqueue – The ST enqueue can be seen in a partitioned environment when a large number of partitions are created simultaneously.
· TC enqueue – The TC enqueue is related to the DBWR background process and occur when “alter tablespace” commands are issued. You will also see the TC enqueue when doing parallel full-table scans where rows are accessed directly, without being loaded into the data buffer cache.
· TM enqueue – The TM enqueue related to Transaction Management (a.k.a. enq: TM - contention) and can be seen when tables are explicitly locked with reorganization activities that require locking of a table.
· TQ enqueue – The TQ enqueue is the Queue Table enqueue (a.k.a. enq: TQ - DDL contention) and happens during Data ump (export import) operations.
· TS enqueue – The TS enqueue is the temporary segment enqueue (a.k.a. enq: TS – contention) and these enqueues happen during disk sort operations.
· TT enqueue – The TT enqueue (a.k.a. enq: TT – contention) is used to avoid deadlocks in parallel tablespace operations. The TT enqueue can be seen with parallel create tablespace and parallel point in time recovery (PITR)
· TX runqueue – The TX enqueue is the transaction enqueue (a.k.a. enq: TX – contention) and is commonly related to buffer busy waits, in conditions where multiple transaction attempt to update the same data blocks.
enq: TX - row lock contention
enq: TX - allocate ITL entry
enq: TX - row lock contention
· UL enqueue – The UL enqueue is a User Lock enqueue (a.k.a. enq: UL – contention) and happens when a lock is requested in dbms_lock.request. The UL enqueue can be seen in Oracle Data Pump.
· US Enqueue - The US enqueue happens with Oracle automatic UNDO management was undo segments are moved online and offline.
Solving Waits on "enq: TM - contention"
Solving Waits on "enq: TM - contention"
by Dean R. on Jul 14 2008 at 4:55 AM in Wait Time Analysis
Recently, I was assisting one of our customers of Ignite for Oracle trying 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.
Cause
After using Ignite for 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 the Problem
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.
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
)
ORDER BY table_name, column_position;
by Dean R. on Jul 14 2008 at 4:55 AM in Wait Time Analysis
Recently, I was assisting one of our customers of Ignite for Oracle trying 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.
Cause
After using Ignite for 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 the Problem
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.
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
)
ORDER BY table_name, column_position;
What is enq: TX - row lock contention
What is enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
Subscribe to:
Posts (Atom)