Oracle Lock and Enqueue info .....
"enq: TX - row lock contention" due to lock
col SQL_TEXT format a50
col OBJECT format a15
col EVENT format a35
SELECT sid, event, wait_time_micro / 1000 time_ms, blocking_session, object_type || ': ' || object_name object, sql_text
FROM v$session s
LEFT OUTER JOIN v$sql USING (sql_id)
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
WHERE event LIKE 'enq: %';
Sess
ID EVENT TIME_MS BLOCKING_SESSION OBJECT SQL_TEXT
------- ----------------------------------- ---------- ---------------- --------------- --------------------------------------------------
37 enq: TX - row lock contention 2,601,852 17 TABLE: ANUJ update anuj set sal=1001 where EMPNO=7369
WITH sql_app_waits AS
(SELECT sql_id, SUBSTR(sql_text, 1, 80) sql_text,application_wait_time/1000 app_time_ms,elapsed_time,
ROUND(application_wait_time * 100 / elapsed_time, 2) app_time_pct,
ROUND(application_wait_time * 100 /SUM(application_wait_time) OVER (), 2) pct_of_app_time,
RANK() OVER (ORDER BY application_wait_Time DESC) ranking
FROM v$sql
WHERE elapsed_time > 0 AND application_wait_time>0 )
SELECT sql_text, app_time_ms, app_time_pct,pct_of_app_time
FROM sql_app_waits
WHERE ranking <= 10
ORDER BY ranking ;
SQL_TEXT APP_TIME_MS APP_TIME_PCT PCT_OF_APP_TIME
-------------------------------------------------- ----------- ------------ ---------------
update anuj set sal=1001 where EMPNO=7369 2625757.49 100 100
select distinct a.sid "waiting sid",d.sql_text "waiting SQL",a.ROW_WAIT_OBJ# "locked object",a.BLOCKING_SESSION "blocking sid",c.sql_text "SQL from blocking
session" from v$session a, v$active_session_history b, v$sql c, v$sql d
where
a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#
WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program, h.module, h.action, object_name,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits, username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited)) OVER (), 2) pct_of_time
FROM v$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,object_name, sql_text, username)
SELECT lock_type,module, username, object_name, time_ms, pct_of_time, sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;
OBJ NAME or
LOCK MODULE Username TRANS_ID TIME_MS PCT_OF_TIME SQL_TEXT
---- ------------ -------- ------------ ---------- ----------- --------------------------------------------------
PR SYS KET$_AUTOTAS 2,165 100.00 SELECT INSTANCE_NAME, HOST_NAME, NVL(GVI_STARTUP_T
IME, SYSTIMESTAMP) - INTERVAL '1' SECOND AS SHUTDO
WN_TIME FROM (SELECT RRI.INSTANCE_NAME AS INSTANCE
_NAME, RRI.HOST_NAME AS HOST_NAME, FROM_TZ(RRI.STA
RTUP_TIME, '+00:00') AS RRI_STARTUP_TIME, DBMS_HA_
ALERTS_PRVT.INSTANCE_STARTUP_TIMESTAMP_TZ(GVI.STAR
TUP_TIME) AS GVI_STARTUP_TIME FROM RECENT_RESOURCE
_INCARNATIONS$ RRI LEFT OUTER JOIN GV$INSTANCE GVI
ON GVI.INSTANCE_NAME = RRI.RESOURCE_NAME WHERE RR
I.RESOURCE_TYPE = 'INSTANCE' AND :B2 = RRI.DB_UNIQ
UE_NAME AND :B1 = RRI.DB_DOMAIN) WHERE GVI_STARTUP
_TIME IS NULL OR GVI_STARTUP_TIME > RRI_STARTUP_TI
ME GROUP BY INSTANCE_NAME, HOST_NAME, GVI_STARTUP_
TIME
TX SQL*Plus ANUJ ANUJ 0 .00 update anuj set sal=1001 where EMPNO=7369
col USERNAME for a20
col OBJECT_NAME for a20
col SQL_TEXT for a70 wrap
col MODULE for a15
WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM gv$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN gv$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action, object_name, sql_text, username)
SELECT lock_type,module, username, object_name, time_ms,pct_of_time,
sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;
col sid format a10
WITH sessions AS
(SELECT /*+materialize*/ sid, blocking_session, row_wait_obj#, sql_id FROM v$session)
SELECT LPAD(' ', LEVEL*3 ) || sid sid, object_name,substr(sql_text,1,40) sql_text
FROM sessions s
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions) OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL;
Sess OBJ NAME or
ID TRANS_ID SQL_TEXT
---------- ------------ ----------------------------------------------------------------------
17
37 ANUJ update anuj set sal=1001 where EMPNO=736
