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