Are there any sessions blocking other sessions in the DB? Blocking session is the cause of lot of ACTIVE sessions in the DB and represent 2 or more sessions trying to touch the same data. We need to avoid this situation.
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status,
st1.sql_text statment_1,
st2.sql_text statment_2
FROM v$lock l1, v$session s1, v$lock l2, v$session s2, sys.v_$sql st1, sys.v_$sql st2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2
and st1.sql_id = s1.sql_id
and st2.sql_id = s2.sql_id
Are there any long running transactions in the DB? Long running transactions have been the cause of problems with our replication strategy, making shareplex queue a lot of messages in the target DB.
select s.sid
,s.serial#
,s.username
,s.machine
,s.status
,s.lockwait
,t.used_ublk
,t.used_urec
,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr
where s.status = 'ACTIVE'
and s.username = '<schema-name>'
Identifying blocked objects. Blocked objects could be the cause of processes taking lot of time.
SELECT l.sid, s.serial#, l.id1, s.status, s.machine, s.type, uo.object_name, st.sql_text
FROM v$lock l, user_objects uo, v$session s, sys.v_$sql st
WHERE l.TYPE='TM'
and uo.object_id = l.id1
and s.sid = l.sid
and st.sql_id = s.sql_id
Identifying locks in the DB. Locks are expected and are part of the normal oepration of the DB.
select uo.object_name,
case lo.locked_mode
when 0 then
'None'
when 1 then
'Null'
when 2 then
'ROWS_S (SS): Row Share Lock'
when 3 then
'ROW_X (SX): Row Exclusive Table Lock'
when 4 then
'SHARE (S): Share Table Lock'
when 5 then
'S/ROW-X (SSX): Share Row Exclusive Table Lock'
when 6 then
'Exclusive (X): Exclusive Table Lock'
else
'Not Identified'
end as lock_mode,
st.sql_text,
lo.*
from V$LOCKED_OBJECT lo, user_objects uo, v$session s, sys.v_$sql st
where uo.object_id = lo.object_id
and s.sid = lo.session_id
and st.sql_id = s.sql_id