How to troubleshoot enq: TX - row lock contention?
Resolving Issues Where 'enq: TX - row lock contention' Waits are Occurring (Doc ID 1476298.1)
NOTE:62354.1 - Waits for 'Enq: TX - ...' Type Events - Transaction (TX) Lock Example Scenarios
NOTE:197057.1 - TX Lock "Transaction Enqueue"
NOTE:1392319.1 - Master Note: Locks, Enqueues and Deadlocks
https://core.rackspace.com/ticket/230131-06072
from Oracle> 20c
Therefore, in order for MAX_IDLE_BLOCKING_TIME to be effective, its limit must be less than the MAX_IDLE_TIME limit.
alter system set max_idle_blocker_time =
set linesize 500 pagesize 300
col object for a35
col kill for a16
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,l.inst_id,substr(l.oracle_username,1,10) ora_user, l.session_id sid,
--,s.serial#,
substr(o.owner||'.'||o.object_name,1,40) object,p.inst_id, p.spid os_pid,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
,machine
,sql_id
, prev_sql_id
,s.status
,s.event
from sys.gv_$locked_object l, dba_objects o, sys.gv_$session s, sys.gv_$process p
where l.object_id = o.object_id
and l.inst_id = s.inst_id
and l.session_id = s.sid
and s.inst_id = p.inst_id
and s.paddr = p.addr(+)
order by l.inst_id
;
select
ROUND(RATIO_TO_REPORT(SUM(1)) OVER () * 100 ,2) PERCENTAGE,
ash.session_type SESS_TYPE,
session_state STATUS,
decode(nvl(sql_id,'-1'),'-1','nonsql','sql') SQL_TYPE,
count(distinct to_char(session_id)|| to_char(session_serial#)) SESS_CNT
from gv$active_session_history ash
where 1=1
and sample_time > sysdate - 30/(24*60)
and ( ( ash.session_state = 'ON CPU' ) or ( ash.session_type != 'BACKGROUND' ) )
group by ash.session_type, ash.session_state, decode(nvl(sql_id,'-1'),'-1','nonsql','sql')
order by count(*)
/
PERCENTAGE SESS_TYPE STATUS SQL_TY SESS_CNT
---------- ---------- ------- ------ ----------
1.06 BACKGROUND ON CPU sql 2
3.19 FOREGROUND ON CPU sql 6
4.26 FOREGROUND WAITING sql 8
91.49 BACKGROUND ON CPU nonsql 17
session info
set linesize 300 pagesize 300
col SQL_TEXT for a50
col kill for a15
col USERNAME for a15
col PLSQL_ENTRY_OBJECT for a20
col PLSQL_ENTRY_SUBPROGRAM for a20
col PLSQL_ENTRY_SUBPROGRAM for a20
col EVENT for a30
SELECT ''''||se.sid ||','|| se.serial#||',@'||se.inst_id ||'''' kill
, username
, ( SELECT max( substr( sql_text , 1, 40 )) SQL_TEXT FROM gv$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
--, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
--, se.*
,se.sql_id
,se.event
FROM gv$session se
WHERE 1=1
AND se.status = 'ACTIVE'
-- AND sid = 369
and plsql_entry_object_id is not null
and username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ;
set linesize 300 pagesize 300
col file# for 9999999
col block# for 9999999999999999
col obj for a18
col type for a9
col lm for 99
col bsid for 99999999
col event for a35
select count(*) cnt,
session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
,CON_ID "CON_ID**"
from gv$active_session_history ash,
all_objects o
where 1=1
and event like 'enq: T%'
and o.object_id (+)= ash.current_obj#
--and SAMPLE_TIME >sysdate -1
and sample_time > sysdate - interval '15' minute
group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION,CON_ID
order by count(*)
/
set linesize 300 pagesize 300
col file# for 9999999
col block# for 9999999999999999
col obj for a18
col type for a9
col lm for 99
col bsid for 99999999
col event for a35
select substr(event,1,30) event, sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
from gv$active_session_history ash,
( select a.object_name,
a.object_id,
decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
from all_objects a, all_indexes i where
a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
where
event like 'enq: TX%'
and o.object_id (+)= ash.current_obj#
order by sample_time
/
alter session set nls_date_format='dd-mm-yyyy HH24:mi';
set linesize 300 pagesize 300
col file# for 9999999
col block# for 9999999999999999
col obj for a18
col type for a9
col lm for 99
col bsid for 99999999
col event for a35
select substr(event,1,30) event, sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#,
CON_ID "Con_id***"
from gv$active_session_history ash,
( select a.object_name,
a.object_id,
decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
from all_objects a, all_indexes i where
a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
where 1=1
and event like 'enq: TX%'
and o.object_id (+)= ash.current_obj#
order by sample_time
/
define 3="TIMESTAMP'2023-07-20 02:00:00'"
define 4="TIMESTAMP'2023-07-20 02:25:00'"
col EVENT for a30
set lines 500 pagesize 300
SELECT * FROM (
SELECT /*+ PARALLEL */
count(*) AS count,
event,user_id, program, module, sql_id
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE 1=1
and sample_time BETWEEN &3 AND &4
--and sample_time > TO_DATE('20-JUL-2023 02.00.00 AM','dd-MON-yy hh:mi:ss AM') AND sample_time < TO_DATE('20-JUL-2023 02.25.00 AM','dd-MON-yy hh:mi:ss AM')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
--and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
-- AND program LIKE 'oracle@%'
GROUP BY event,user_id, program, module, machine, sql_id
ORDER BY count(*) desc
)
WHERE rownum <= 20;
define 3="TIMESTAMP'2023-07-20 02:00:00'"
define 4="TIMESTAMP'2023-07-20 02:25:00'"
alter session set nls_date_format='dd-mm-yyyy HH24:mi';
set linesize 300 pagesize 300
col file# for 9999999
col block# for 9999999999999999
col obj for a28
col type for a20
col lm for 99
col bsid for 99999999
col event for a35
select substr(event,1,30) event, sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
--,CON_ID "Con_id***"
from gv$active_session_history ash,
( select a.object_name,
a.object_id,
decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
from all_objects a, all_indexes i where
a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
where 1=1
and sample_time BETWEEN &3 AND &4
and event like 'enq: TX%'
and o.object_id (+)= ash.current_obj#
order by sample_time
/
set lines 500 pagesize 300
SELECT * FROM (
SELECT /*+ PARALLEL */
count(*) AS count,
user_id, program, module, sql_id
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE 1=1
--and sample_time > TO_DATE('19-FEB-2021 03.00.00 PM','dd-MON-yy hh:mi:ss PM') AND sample_time < TO_DATE('19-FEB-2021 08.00.00 PM','dd-MON-yy hh:mi:ss PM')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
-- AND program LIKE 'oracle@%'
GROUP BY user_id, program, module, machine, sql_id
ORDER BY count(*) desc
)
WHERE rownum <= 20;
col event for a50
select event, count(1)
--from gv$active_session_history
from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
-- and sample_time between to_date('21-FEB-21 01.43.00 PM','dd-MON-yy hh:mi:ss PM') and to_date('21-FEB-21 01.53.00 PM','dd-MON-yy hh:mi:ss PM')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
group by event
order by event;
set lines 500 pagesize 300
col type for a20
select * from (select
ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) "WAITING",
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "IO WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'Concurrency',1,0))) "CONCURRENCY" ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'Application',1,0))) "Application" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash, audit_actions aud
where SQL_ID is not NULL
and ash.sql_opcode=aud.action
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
group by sql_id, SQL_PLAN_HASH_VALUE , aud.name
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 5;
SQL_ID PLAN_HASH TYPE CPU WAITING IO WAIT IO CONCURRENCY Application TOTAL
------------- ---------- -------------------- ---------- ---------- ---------- ---------- ----------- ----------- ----------
1yg0m7skx0az5 3752686039 SELECT 0 2 0 0 0 0 2
c9umxngkc3byq 297230549 SELECT 1 0 0 0 1
crf6mw7zzfx3a 3769242997 SELECT 0 1 0 0 0 0 1
06gfrprr7w0r2 0 SELECT 1 0 0 0 1
set lines 500 pagesize 300
col file# for 99999
col block# for 9999999999
col obj for a40
col type for a9
col lm for 99
col bsid for 9999
col kill for a15
select count(*) cnt,
''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' kill,
--session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
,con_id
from gv$active_session_history ash,all_objects o
where 1=1
and event like 'enq: T%'
--and sample_time between to_timestamp('19.12.2019 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_TIMESTAMP('19.12.2019 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
--and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
and o.object_id (+)= ash.current_obj#
group by event,''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' ,p1,sql_id,current_obj#,object_name,object_type,current_file#, current_block#, blocking_session,con_id
order by count(*)
/
CNT KILL EVENT LM SQL_ID OBJ TYPE FILE# BLOCK# BSID CON_ID
---------- --------------- ----------------------------------- --- ------------- ---------------------------------------- --------- ------ ----------- ----- ----------
1 '767,44197,@2' enq: TM - contention 3 ft7wcqu3hzvca -1 0 0 0
1 '2278,56250,@1' enq: TS - contention 6 0knj9p6tud65h 14313 WRI$_OPTSTAT_SYNOPSIS_HEAD$ TABLE 3 11896 0
1 '951,27043,@1' enq: TM - contention 4 7bfdrz0sh8j39 756715 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PAR 0 0 0
TITION
1 '1330,7304,@2' enq: TM - contention 3 du1vfy7c7zvf2 -1 0 0 0
1 '9,61706,@2' enq: TS - contention 6 4vhtpzwmsprtq 14313 WRI$_OPTSTAT_SYNOPSIS_HEAD$ TABLE 3 11896 0
1 '1328,27787,@2' enq: TM - contention 3 du1vfy7c7zvf2 -1 0 0 0
1 '2271,44441,@2' enq: TQ - DDL contention 6 gvkbyx5x9bcpv -1 0 0 2087 0
1 '2085,11706,@1' enq: TM - contention 6 5qbwywd4h95rk 7236 WRI$_TOPN_METADATA TABLE 3 475521 0
2 '570,10375,@1' enq: TD - KTF dump entries 6 0 4 328 0
5062 '2461,56560,@2' enq: TX - row lock contention 6 5dxca0mh1jq0v 626766 EMP TABLE 1 2660073 2271 0
5178 '1710,5806,@2' enq: TX - row lock contention 6 8sg29582qu79q 626766 EMP TABLE 1 2660073 2271 0
7165 '2279,2798,@2' enq: TX - row lock contention 6 890pztv6udmsk 626766 EMP TABLE 1 2660073 2272 0
7174 '1334,61354,@2' enq: TX - row lock contention 6 8sg29582qu79q 626766 EMP TABLE 1 2660073 2271 0
13 rows selected.
-- with MACHINE
set lines 500 pagesize 300
col file# for 99999
col block# for 9999999999
col obj for a40
col type for a9
col lm for 99
col bsid for 99999999
col kill for a17
col CLIENT_ID for a15
col MACHINE for a45
select count(*) cnt,
''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' kill,
--session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
,CLIENT_ID
,MACHINE
,con_id
from gv$active_session_history ash,dba_objects o
where 1=1
and event like 'enq: T%'
--and sample_time between to_timestamp('10.07.2019 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_TIMESTAMP('10.07.2019 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
and o.object_id (+)= ash.current_obj#
group by event,''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' ,p1,sql_id,current_obj#,object_name,object_type,current_file#, current_block#, blocking_session,CLIENT_ID,MACHINE,con_id
order by count(*)
/
SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 PAGESIZE 0 TRIM ON
SET TRIMSPOOL ON ECHO OFF FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '1yg0m7skx0az5',
type => 'TEXT',
report_level => 'ALL') AS report
FROM dual;
set linesize 300 pagesize 300
col object_name for a20
col sql_text for a70 wrap
select distinct a.sql_id,to_char(a.sample_time,'dd.mm.yyyy hh24:mi:ss') sample_time,a.blocking_session,d.object_name,s.sql_text
from dba_hist_active_sess_history a, gv$sql s, dba_objects d
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.current_obj# = d.object_id
-- and sample_time between to_timestamp('19.12.2019 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_TIMESTAMP('19.12.2019 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
-- AND A.EVENT = 'enq: TX - row lock contention'
order by sample_time desc
var sqlid VARCHAR2(13); begin :sqlid := 'g0bggfqrddc4w'; end; ----- change sql id /
set long 50000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
);
set lines 500 pagesize 300
col SAMPLE_TIME for a27
select t.dbid,
t.sample_id,
t.sample_time,
t.instance_number,
t.event,
t.session_state,
t.c session_count
from (select t.*, rank() over(partition by dbid, instance_number, sample_time order by c desc) r
from (select /*+ parallel 8 */
t.*,
count(*) over(partition by dbid, instance_number, sample_time, event) c,
row_number() over(partition by dbid, instance_number, sample_time, event order by 1) r1
from dba_hist_active_sess_history t
where 1=1
-- and sample_time > to_timestamp('&begin_sample_time', 'yyyy-mm-dd hh24:mi:ss') and sample_time < to_timestamp('&end_sample_time','yyyy-mm-dd hh24:mi:ss')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
) t
where r1 = 1) t
where r < 3
order by dbid, instance_number, sample_time, r;
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- --------------------------- --------------- -------------------------------------------------- ------- -------------
1222414252 111482565 13-APR-21 05.56.08.776 AM 2 RMA: IPC0 completion sync WAITING 1
1222414252 111482575 13-APR-21 05.56.18.794 AM 2 ON CPU 1
1222414252 111482605 13-APR-21 05.56.48.848 AM 2 ON CPU 1
1222414252 111482625 13-APR-21 05.57.08.884 AM 2 RMA: IPC0 completion sync WAITING 1
1222414252 111482645 13-APR-21 05.57.28.916 AM 2 ON CPU 1
1222414252 111482675 13-APR-21 05.57.58.969 AM 2 RMA: IPC0 completion sync WAITING 1
1222414252 111482685 13-APR-21 05.58.08.986 AM 2 ON CPU 1
1222414252 111483035 13-APR-21 06.03.59.798 AM 2 ON CPU 1
select sql_id,event, count(1)
from dba_hist_active_sess_history
where sample_time > to_date('05032015 10:00:00','ddmmrrrr hh24:mi:ss')
and sample_time < to_date('05032015 11:00:00','ddmmrrrr hh24:mi:ss')
and event like '%TM%'
group by sql_id,event
order by 3 desc;
set linesize 300 pagesize 300
select sql_id,event, count(1)
from dba_hist_active_sess_history
where sample_time > to_date('15082024 09:00:00','ddmmrrrr hh24:mi:ss')
and sample_time < to_date('15082024 09:15:00','ddmmrrrr hh24:mi:ss')
--and event like '%TX%'
and EVENT is not null
group by sql_id,event
order by 3 desc;
-- with con_id
col SAMPLE_TIME for a27
define begin_sample_time='2024-08-15 09:05:00'
define end_sample_time ='2024-08-15 09:15:00'
select t.dbid,
t.sample_id,
t.sample_time,
t.instance_number,
t.event,
t.session_state,
t.c session_count
,con_id
,sql_id
from (select t.*, rank() over(partition by dbid, instance_number, sample_time,con_id,sql_id order by c desc) r
from (select /*+ parallel 8 */
t.*,
count(*) over(partition by dbid, instance_number, sample_time, event,con_id,sql_id) c,
row_number() over(partition by dbid, instance_number, sample_time, event ,con_id,sql_id order by 1) r1
from dba_hist_active_sess_history t
where 1=1
and sample_time > to_timestamp('&begin_sample_time', 'yyyy-mm-dd hh24:mi:ss') and sample_time < to_timestamp('&end_sample_time','yyyy-mm-dd hh24:mi:ss')
and event is not null
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
--and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
) t
where r1 = 1) t
where r < 3
order by dbid, instance_number, sample_time, r;
set linesize 250 pagesize 1000 verify off
COLUMN Time FORMAT a20 HEADING 'Sample time'
COLUMN Username FORMAT a12 HEADING 'User name'
COLUMN Session_id FORMAT 99999 HEADING 'Sess ID'
COLUMN sql_id FORMAT a14 HEADING 'SQL ID'
COLUMN program FORMAT a33 HEADING 'Program'
COLUMN module FORMAT a20 HEADING 'Module'
COLUMN event FORMAT a25 HEADING 'Event name'
COLUMN time_waited FORMAT 999,999,999 HEADING 'Time waited'
COLUMN owner1 FORMAT a15 HEADING 'Owner 1'
COLUMN object_name FORMAT a15 HEADING 'Object name 1'
COLUMN owner1 FORMAT a15 HEADING 'Owner 2'
COLUMN p2 FORMAT a15 HEADING 'Object name 2'
SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time, u.username, h.blocking_session block_sid, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name, o1.owner, o1.object_name p2
FROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_OBJECTS o, dba_users u, dba_objects o1
where 1=1
and sample_time > sysdate -1/24
--and sample_time between to_date('begin_date', 'dd/mm/yyyy hh24:mi') and to_date('end_date', 'dd/mm/yyyy hh24:mi')
and session_type != 'BACKGROUND'
and o.object_id (+) = h.CURRENT_OBJ#
and h.p2 = o1.object_id (+)
and time_waited > 0
and u.user_id = h.user_id
--and upper(event) like upper('%EVENT_TO_FIND%')
order by sample_id, sample_time
/
set lines 500 pagesize 300
col inst format 99
col object_name format a20
col spid format a6
col type format a4
col machine format a30
col owner format a20
SELECT TO_CHAR(SYSDATE,'DD/MM/YY HH24:MI:SS') TM, u.username owner, o.object_name object_name, lid.*
FROM (SELECT
s.inst_id inst, s.SID, s.serial#, p.spid, s.blocking_session blocking_sid, s.machine, s.username, NVL (s.sql_id, 0) sqlid, s.sql_hash_value sqlhv,
DECODE (l.TYPE,
'TM', l.id1,
'TX', DECODE (l.request,
0, NVL (lo.object_id, -1),
s.row_wait_obj#
),
-1
) AS object_id,
l.TYPE type,
DECODE (l.lmode,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_held,
DECODE (l.request,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_requested,
l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, s.row_wait_block#,
s.row_wait_row#, s.row_wait_file#
FROM gv$lock l, gv$session s, gv$process p,
(SELECT object_id, session_id, xidsqn
FROM gv$locked_object
WHERE xidsqn > 0) lo
WHERE l.inst_id = s.inst_id
AND s.inst_id = p.inst_id
AND s.SID = l.SID
AND p.addr = s.paddr
AND l.SID = lo.session_id(+)
AND l.id2 = lo.xidsqn(+)) lid,
dba_objects o,
dba_users u
WHERE o.object_id(+) = lid.object_id
AND o.owner = u.username(+)
AND o.object_id <> -1
/
IMP !!!!
If lock in pdbs ---
SELECT A.CON_ID, A.NAME, B.STATUS, A.OPEN_MODE, A.TOTAL_SIZE
FROM V$PDBS A, DBA_PDBS B
WHERE A.CON_ID = B.PDB_ID;
set lines 200
col name for a30
col con_id for 999999
col open_time for a40
col pdb for a30
col name for a8
col pdb_name for a20
select con_id, name, open_mode, restricted, open_time from gv$containers;
select name, cdb, open_mode from v$database;
select name, pdb from v$services order by name;
select pdb_id,pdb_name,con_id,status,logging,force_logging from dba_pdbs order by pdb_name;
select con_id, name, open_mode, restricted from v$pdbs order by name;
select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs
select name, con_id, dbid, con_uid, guid from v$containers order by con_id;
===
set lines 300 pages 999
col blocking for a30
col waiting for a30
col on_object for a30
col waiting_sqlid for a15
col EVENT for a30
SELECT ''''||SES1.sid ||','|| SES1.serial#||',@'||SES1.inst_id ||'''' || '(' || NVL(SES1.USERNAME,'BACKGROUND') || ')' "BLOCKING",SES1.status,SES1.con_id,
NVL(SES1.sql_id,SES1.prev_sql_id) as "BLOCKED_SQLID",
''''||SES2.sid ||','|| SES2.serial#||',@'||SES2.inst_id ||'''' || '(' || NVL(SES2.USERNAME,'BACKGROUND') || ')' "WAITING", SES2.status,SES2.con_id,
SES2.SECONDS_IN_WAIT,
DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#,
SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "LOCKED_ROWID",
SES2.SQL_ID "WAITING_SQLID",
SES2.event
from gv$session ses1, gv$session ses2, dba_objects do
where 1=1
and ses1.sid=ses2.blocking_session
and ses1.inst_id=ses2.inst_id
and ses2.row_wait_obj#=do.object_id
and ses2.blocking_session is not null
order by 1,3;
BLOCKING STATUS CON_ID BLOCKED_SQLID WAITING STATUS CON_ID SECONDS_IN_WAIT ON_OBJECT LOCKED_ROWID WAITING_SQLID EVENT
------------------------------ -------- ---------- ------------- ------------------------------ -------- ---------- --------------- ------------------------------ ------------------ --------------- ------------------------------
'2273,56356,@2'(SCOTT) INACTIVE 0 cdb4mdmvuw9hm '2465,45858,@2'(SCOTT) ACTIVE 0 803 SCOTT.EMP AAAkCvAAHAAAxcMAAA cdb4mdmvuw9hm enq: TX - row lock contention
set linesize 300
col blocking_tree for a70
col sql_text for a60
col event for a34
with l as
(select l1.inst_id, l1.sid
from gv$lock l1, gv$lock l2
where l1.block > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2
and l2.type = 'tx'
and l2.request > 0)
select
lpad(' ', (level - 1) * 2) || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || ' ' || program as blocking_tree,
event,
last_call_et,
blocking_session_status,
s.sql_id,
substr(trim(nvl(sa1.sql_text,sa2.sql_text)), 1, 60) sql_text,
decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid
-- prior status
from gv$session s, gv$sqlarea sa1, gv$sqlarea sa2
where s.sql_id = sa1.sql_id(+)
and s.inst_id = sa1.inst_id(+)
and s.prev_sql_id = sa2.sql_id(+)
and s.inst_id = sa2.inst_id(+)
connect by prior sid = blocking_session
start with (s.inst_id, s.sid) in (select inst_id, sid from L)
/
alter session set nls_date_format='dd-mm-yyyy HH24:mi';
set linesize 300 pagesize 300
col P1TEXT for a15
col P2TEXT for a15
col P3TEXT for a20
col kill for a15
col EVENT for a30
col SAMPLE_TIME for a27
col p3 for 9999999999999999
select distinct '''' || SESSION_ID || ',' || SESSION_SERIAL# ||',@'|| inst_id||'''' kill ,
sample_time,sql_id,event,p1text,p1,p2text,p2,p3text,p3,time_waited ,CON_ID "Con_id***"
from gv$active_session_history
where session_state='WAITING'
and (event like 'library%' or event like 'cursor%' or event like 'enq: TX - row%')
--and SESSION_ID=
;
set pages 300 lines 200
-- Shared Pool
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALL'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'ALLSTATS LAST'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'TYPICAL OUTLINE'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
set pages 300 lines 200
-- AWR
select * from table(dbms_xplan.display_awr('&SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('&SQL_ID',null,DBID,'ALL'));
-- select * from table(dbms_xplan.display_awr('&1',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output
from gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'basic')) t
where sql_id='&SQL_ID'
alter session set container = ; ---------------------------
Then check further
set pagesize 60 linesize 300
col segment_name format a24
col segment_type format a24
SELECT segment_name, segment_type, block_id, blocks FROM dba_extents
WHERE 1=1
and file_id = &file_no
AND ( &block_value BETWEEN block_id AND ( block_id + blocks -1 ) )
/
define __FILE = &1
define __BLOCK = &2
alter session set parallel_force_local=true;
select /*+ parallel(s,4) */ owner,segment_name
from dba_extents s
where file_id = &__FILE
and &__BLOCK between block_id and block_id + blocks - 1
and rownum = 1
;
COLUMN segment_name FORMAT A24
COLUMN segment_type FORMAT A24
SELECT segment_name, segment_type, block_id, blocks
FROM dba_extents
WHERE 1=1
and file_id = &file_no
AND ( &block_value BETWEEN block_id AND ( block_id + blocks ) )
column segment_name format a35
SELECT /*+ RULE */
e.owner || '.' || e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
FROM sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
WHERE 1=1
--and x.hladdr in ( '000000062D06FFF0','000000062D571D50','000000062CBD9D38','000000062CE78AD0' )
AND e.file_id = x.file#
AND x.hladdr = l.addr
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1
ORDER BY x.tch DESC ;
OWNER OBJECT_NAME SUBOBJECT_NAME VALUE
-------------------- ----------------------------------- ---------------------------------------- ----------
SYS EMP 31
SYS WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT_HISTOGRAM_1222414252_32399 18
SYS WRH$_SYSSTAT_PK WRH$_SYSSTAT_1222414252_32423 10
SYS WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT_HISTOGRAM_1222414252_32423 10
SYS WRH$_SYSSTAT_PK WRH$_SYSSTAT_1222414252_32399 8
SYS WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH_MISSES_SUMMARY_1222414252_322 6
77
SYS@TEST1020>
select * from
(select lpad(ltrim(to_char(p2,'XXXXXX')),6,'0')|| '00'|| ltrim(to_char(mod(p3,256),'XX'))|| ltrim(to_char(trunc(p3/256),'XX'))|| '0000' block_xid,
to_char(p2,'XXXXXXXX') p2hex,
to_char(p3,'XXXXXXXX') p3hex,
trunc(p2/65536) usn,
mod(p2,65536) slot,
p3 sqn, xid wait_xid
from gv$active_session_history
where event like 'enq: T%'
order by sample_time desc)
where 1=1
--and rownum < 2
;
alter session set container = ; ---------------------------
SET LINESIZE 300
COL owner FORMAT A20
COL object_name FORMAT A30
COL edition_name FORMAT A15
col temporary for a12
col generated for a12
col secondary for a12
col OBJECT_TYPE for a12
SELECT owner,
object_name,
--subobject_name,
object_id,
data_object_id,
object_type,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') AS last_ddl_time,
status,
temporary,
generated,
secondary,
edition_name
FROM dba_objects
WHERE object_name ='&table_name'
ORDER BY owner, object_name;
set linesize 400 pages 9999 head on
col name format a20
col #dst format 99999999999
col dens format 9.99999
col #null format 9999999999
col avglen format 999999
col histogram format a30
col buckets format 999999 justify right
col num_buckets format 999999
col low_value format a40 justify right
col high_value format a40 justify right
col owner for a20
SELECT
owner,
column_name AS "NAME",
num_distinct AS "#DST",
density AS "DENS",
num_nulls AS "NULLS",
avg_col_len AS "AVGLEN",
histogram,
num_buckets AS Buckets,
low_value,
high_value
FROM dba_tab_col_statistics
WHERE 1=1
--and owner = 'owner'
and table_name = '&table_name'
;
SET LINESIZE 300 VERIFY OFF
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN index_name FORMAT A30
col global_stats for a15
SELECT owner,
index_name,
table_name,
PARTITIONED,
TABLESPACE_NAME,
last_analyzed,
SAMPLE_SIZE,
global_stats,
initial_extent,
ini_trans,
freelists
FROM dba_indexes
WHERE 1=1
AND table_name ='&table_name'
;
#segments have undergone the most row lock waits:
col OWNER for a20
col OBJECT_NAME for a35
col SUBOBJECT_NAME for a40
SELECT owner, object_name, subobject_name, value FROM gv$segment_statistics
WHERE statistic_name='row lock waits'
AND value > 0
ORDER BY 4 DESC;
===
set linesize 500 pagesize 500
col kill for a15
col username for a20
col sess for a20
col machine for a35
col event for a30
col SPID for a15
select /*+ PARALLEL(8) */
k.inst_id,
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,
decode (request, 0, 'Holder: ', ' Waiter: ') || k.sid sess,
final_blocking_session,
ss.con_id,
ss.username,
ss.sql_id,
ss.prev_sql_id,
k.id1,
k.id2,
k.lmode,
k.request,
k.type,
ss.last_call_et,
ss.seconds_in_wait,
ss.machine,
ss.event,
ss.status,
p.spid,
case when request > 0 THEN
CHR (BITAND (p1, -16777216) / 16777215)|| CHR (BITAND (p1, 16711680) / 65535)
ELSE NULL
END "Name",
case when request > 0 then (bitand (p1, 65535)) else null end "Mode",
final_blocking_session_status
from gv$lock k, gv$session ss, gv$process p
where 1=1
and (k.id1, k.id2, k.type) in (select ll.id1, ll.id2, ll.type from gv$lock ll where request > 0)
and k.sid = ss.sid
and k.inst_id = ss.inst_id
and ss.paddr = p.addr
and ss.inst_id = p.inst_id
order by id1, request
;
set linesize 300 pagesize 300
col SQL_TEXT for a100 wrap
select sql_id, sql_text
--, sql_fulltext
from gv$sql
where sql_id = '&sql_id';
set lines 300 pagesize 300
col kill for a70
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ; -- ' kill,username,s.con_id,sql_id,event,sql_id ,prev_sql_id from gv$session s
where sid='&sid';
to remove everything after ";" !!!!
sed 's/;.*/;/' out.txt
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output
from gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'basic')) t
where sql_id='&SQL_ID'
set linesize 300 pagesize 300
col session_waits format a100
SELECT 'Session Info '||''''||sid ||','|| serial#||',@'||inst_id ||'''' ||
CHR(10)||'Con Id ' || con_id ||
CHR(10)||'SQL ID ' || sql_id ||
CHR(10)||'PREV SQL_ID ' || PREV_SQL_ID ||
CHR(10)||'USERNAME: ' || USERNAME ||
CHR(10)||'STATE: ' || STATE ||
CHR(10)||'EVENT: ' || EVENT ||
CHR(10)||'WAIT_TIME: ' || WAIT_TIME ||
CHR(10)||'SECONDS_IN WAIT: ' || SECONDS_IN_WAIT ||
CHR(10)||'WAIT_CLASS: ' || WAIT_CLASS ||
CHR(10)||'P1TEXT: ' || P1TEXT ||
CHR(10)||'P1: ' || P1 ||
CHR(10)||'P2TEXT: ' || P2TEXT ||
CHR(10)||'P2: ' || P2 ||
CHR(10)||'P3TEXT: ' || P3TEXT ||
CHR(10)||'P3: ' || P3 AS SESSION_WAITS
FROM GV$SESSION
WHERE 1=1
-- and USERNAME='XXXX'
AND EVENT LIKE '%TX%'
ORDER BY WAIT_TIME
;
set lines 500
col kill for a16
col p1text for a15
col p2text for a20
col p3text for a15
col SQL_OPNAME for a15
select * from (
select ''''||ash.session_id ||','|| ash.session_serial#||',@'||ash.inst_id ||'''' kill,sql_id,p1,p2,p3,p1text,p2text,p3text,ash.sql_opname,count(*) from gv$active_session_history ash
where ash.event ='enq: TX - row lock contention'
group by ''''||ash.session_id ||','|| ash.session_serial#||',@'||ash.inst_id ||'''' ,sql_id,p1,p2,p3,p1text,p2text,p3text,sql_opname
order by 9 desc
)
where 1=1
-- and rownum<50
;
===
set linesize 300 pagesize 300
col sid for 999999
col machine for a30
select
--ss.inst_id,
kill,
con_id1,
ss.username,
ss.sql_id,
ss.prev_sql_id,
ss.status,
ss.machine,
to_char(sysdate - ( ss.last_call_et / 86400 ), 'dd-mon-yyyy hh24:mi:ss') since,
ss.last_call_et,
To_char(ss.logon_time, 'dd-mon-yyyy HH24:MI:SS') logon_time,
ss.current_queue_duration,
ss.event,
ss.final_blocking_session
FROM gv$session ss
inner join (SELECT DISTINCT ''''||s1.sid ||','|| s1.serial#||',@'||s1.inst_id ||'''' kill,s1.con_id con_id1,s1.inst_id, s1.sid,s1.sql_id sql_id,s1.final_blocking_session
from gv$lock l1, gv$session s1, gv$lock l2, gv$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
and l1.inst_id = s1.inst_id
and l2.inst_id = s2.inst_id
union
select distinct ''''||s2.sid ||','|| s2.serial#||',@'||s2.inst_id ||'''' ,s2.con_id,s2.inst_id, s2.sid,s2.sql_id, s2.final_blocking_session
from gv$lock l1, gv$session s1, gv$lock l2, gv$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
and l1.inst_id = s1.inst_id
and l2.inst_id = s2.inst_id) bb
ON ( ss.inst_id = bb.inst_id
AND ss.sid = bb.sid )
order by kill;
KILL CON_ID1 USERNAME SQL_ID PREV_SQL_ID STATUS MACHINE SINCE LAST_CALL_ET LOGON_TIME CURRENT_QUEUE_DURATION EVENT
------------------------------------------------------------ ---------- -------------------- ------------- ------------- -------- ------------------------------ ----------------------------- ------------ ----------------------------- ---------------------- ------------------------------
FINAL_BLOCKING_SESSION
----------------------
'2655,3265,@2' 0 SCOTT 64tnap91zzj7f gwt13cqujm9up ACTIVE ibrac02.int.smq.datapipe.net 12-nov-2021 04:15:07 597 12-nov-2021 04:15:00 0 enq: TX - row lock contention
2846
'2846,39510,@2' 0 SCOTT 64tnap91zzj7f INACTIVE ibrac02.int.smq.datapipe.net 12-nov-2021 04:12:00 784 12-nov-2021 04:08:43 0 SQL*Net message from client
set lines 500 pagesize 300
col file# for 99999
col block# for 9999999999
col obj for a40
col type for a9
col lm for 99
col bsid for 9999
col kill for a15
select count(*) cnt,
''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' kill,
--session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
,con_id
from gv$active_session_history ash,dba_objects o
where 1=1
and event like 'enq: T%'
--and sample_time between to_timestamp('19.12.2019 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_TIMESTAMP('19.12.2019 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY
--and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute
and o.object_id (+)= ash.current_obj#
group by event,''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' ,p1,sql_id,current_obj#,object_name,object_type,current_file#, current_block#, blocking_session,con_id
order by count(*)
/
=====
Small test for TX lock
My session info ...
first session !!!!!
set sqlprompt "_user _privilege 'on' _date>"
col sid for a40
select 'INSTANCE= '|| sys_context('USERENV', 'INSTANCE') || ' SID = '||sid||' Serial# = '||serial# SID from v$session
where sid in (SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL)
;
SYS AS SYSDBA on 12-11-21 04:07>/
SID
----------------------------------------
INSTANCE= 2 SID = 2846 Serial# = 36256
on 12-11-21 04:08>connect scott/v123
Connected.
select * from dept where DEPTNO=10 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
update dept set DNAME=upper(DNAME) where DEPTNO=10;
1 row updated.
Second Session !!!!
select 'INSTANCE= '|| sys_context('USERENV', 'INSTANCE') || ' SID = '||sid||' Serial# = '||serial# SID from v$session
where sid in (SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL)
SID
----------------------------------------
INSTANCE= 2 SID = 2655 Serial# = 43598
connect scott/v123
update dept set DNAME=upper(DNAME) where DEPTNO=10;
from sys session ..
set linesize 500 pagesize 300
col object for a35
col kill for a16
col sid for 9999999
col ORA_USER for a20
col MACHINE for a30
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,l.inst_id,substr(l.oracle_username,1,10) ora_user, l.session_id sid,
--,s.serial#,
substr(o.owner||'.'||o.object_name,1,40) object,p.inst_id, p.spid os_pid,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
,machine
,sql_id
, prev_sql_id
,s.status
,s.event
from gv$locked_object l, dba_objects o, gv$session s, gv$process p
where l.object_id = o.object_id
and l.inst_id = s.inst_id
and l.session_id = s.sid
and s.inst_id = p.inst_id
and s.paddr = p.addr(+)
and s.USERNAME Not in ( 'SYS ' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by l.inst_id
;
'2846,39510,@2'= first session !!!
KILL INST_ID ORA_USER SID OBJECT INST_ID OS_PID LOCK_MODE MACHINE SQL_ID PREV_SQL_ID STATUS EVENT
---------------- ---------- -------------------- -------- ----------------------------------- ---------- ------------------------ ------------------- ------------------------------ ------------- ------------- -------- ----------------------------------------------------------------
'2655,3265,@2' 2 SCOTT 2655 SCOTT.DEPT 2 9751 ROW EXCLUSIVE ibrac02.int.smq.datapipe.net 64tnap91zzj7f gwt13cqujm9up ACTIVE enq: TX - row lock contention
'2846,39510,@2' 2 SCOTT 2846 SCOTT.DEPT 2 4695 ROW EXCLUSIVE ibrac02.int.smq.datapipe.net 64tnap91zzj7f INACTIVE SQL*Net message from client
set linesize 300
col kill for a16
col username for a20
col event for a30
col sid for 999999
col terminal for a25
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.con_id,username,sql_id,prev_sql_id,s.event event, l.sid,terminal,trunc(id1/power(2,16)) rbs
,bitand(id1,to_number('ffff','xxxx'))+0 slot, id2 seq,
DECODE(l.LMODE, 0,'0-NONE',
1,'1-NULL',
2,'2-ROW SHARE',
3,'3-ROW EXCLUSIVE',
4,'4-SHARE',
5,'5-SHARE ROW EXCLUSIVE',
6,'6-EXCLUSIVE',
NULL) LOCK_MODE
,DECODE(L.REQUEST, 0,'0-NONE',
1,'1-NULL',
2,'2-ROW SHARE',
3,'3-ROW EXCLUSIVE',
4,'4-SHARE',
5,'5-SHARE ROW EXCLUSIVE',
6,'6-EXCLUSIVE',
NULL) REQUEST,
final_blocking_session
from gv$lock l, gv$session s
where 1=1
and l.sid = s.sid
--and s.username = 'SYS'
and l.LMODE=6
and l.type = 'TX'
and s.USERNAME Not in ( 'SYS ' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
;
KILL CON_ID USERNAME SQL_ID PREV_SQL_ID EVENT SID TERMINAL RBS SLOT SEQ LOCK_MODE REQUEST FINAL_BLOCKING_SESSION
---------------- ---------- -------------------- ------------- ------------- ------------------------------ ------- ------------------------- ---------- ---------- ---------- --------------------- --------------------- ----------------------
'2846,39510,@2' 0 SCOTT 64tnap91zzj7f SQL*Net message from client 2846 pts/2 19 10 102252 6-EXCLUSIVE 0-NONE
to kill session !!!
set linesize 300
col kill for a60
col username for a20
col event for a30
col sid for 999999
col terminal for a25
select 'alter system kill session '''||s.sid||','||s.serial#||',@'||s.inst_id||''' immediate ; ' kill
/*
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.con_id,username,sql_id,prev_sql_id,s.event event, l.sid,terminal,trunc(id1/power(2,16)) rbs
,bitand(id1,to_number('ffff','xxxx'))+0 slot, id2 seq,
DECODE(l.LMODE, 0,'0-NONE',
1,'1-NULL',
2,'2-ROW SHARE',
3,'3-ROW EXCLUSIVE',
4,'4-SHARE',
5,'5-SHARE ROW EXCLUSIVE',
6,'6-EXCLUSIVE',
NULL) LOCK_MODE
,DECODE(L.REQUEST, 0,'0-NONE',
1,'1-NULL',
2,'2-ROW SHARE',
3,'3-ROW EXCLUSIVE',
4,'4-SHARE',
5,'5-SHARE ROW EXCLUSIVE',
6,'6-EXCLUSIVE',
NULL) REQUEST,
final_blocking_session
*/
from gv$lock l, gv$session s
where 1=1
and l.sid = s.sid
and l.LMODE=6
and l.type = 'TX'
and s.USERNAME Not in ( 'SYS ' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
;
KILL
------------------------------------------------------------
alter system kill session '2846,39510,@2' immediate ; ----- i.e first session
====
set linesize 500 pages 9999
column owner format a15
column object_name format a25
SELECT
o.owner ,
o.object_name object_name ,
ROW_WAIT_FILE# file# ,
ROW_WAIT_BLOCK# block# ,
ROW_WAIT_ROW# row# ,
COUNT(1)
FROM
gv$session ses,
dba_objects o
WHERE 1=1
and event = 'enq: TX - row lock contention'
AND o.object_id (+)= ses.row_wait_obj#
GROUP BY
o.owner,
o.object_name,
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#
ORDER BY COUNT(1);
SELECT
*
FROM
&owner..&table_name
WHERE
dbms_rowid.rowid_relative_fno(rowid) = &file
AND dbms_rowid.rowid_block_number(ROWID) = &block
AND dbms_rowid.rowid_row_number(ROWID) = &row;
===
set linesize 300 pagesize 300
col BLOCKING_PATH for a25
col kill for a16
col SAMPLE_TIME for a25
col event for a30
SELECT
level,
''''||SESSION_ID ||','|| SESSION_SERIAL#||',@'||INSTANCE_NUMBER ||'''' kill,
sample_time,
session_id blocked_sid,
event,
CONNECT_BY_ROOT blocking_session ultimate_blocker_sid,
sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path
FROM
(-- Blocked Sessions
SELECT
s.session_id,
s.SESSION_SERIAL#,
s.INSTANCE_NUMBER,
s.blocking_session,
s.sql_id,
s.sample_time,
s.event
FROM -- dba_active_sess_history s
DBA_HIST_ACTIVE_SESS_HISTORY s
WHERE 1=1
--and s.dbid = <db_id>
-- AND s.instance_number = <inst_num>
--AND s.snap_id BETWEEN <begin_snap> and <end_snap>
AND s.blocking_session IS NOT NULL
AND s.event IN
('enq: TX - row lock contention'
,'enq: TM - contention'
,'enq: UL - contention'
,'enq: TX - allocate ITL entry')
UNION
-- Blocking Sessions
SELECT
s.session_id,
s.SESSION_SERIAL#,
s.INSTANCE_NUMBER,
s.blocking_session,
s.sql_id,
s.sample_time,
s.event
FROM DBA_HIST_ACTIVE_SESS_HISTORY s
WHERE 1=1
-- and s.dbid = <db_id>
-- AND s.instance_number <inst_num>
--AND s.snap_id BETWEEN <begin_snap> and <end_snap>
AND s.blocking_session IS NULL
AND s.event IN
('enq: TX - row lock contention'
,'enq: TM - contention'
,'enq: UL - contention'
,'enq: TX - allocate ITL entry')
AND EXISTS
(SELECT 'exists'
FROM DBA_HIST_ACTIVE_SESS_HISTORY bs
WHERE 1=1
--and bs.dbid = <db_id>
-- AND bs.instance_number = <inst_num>
-- AND bs.snap_id BETWEEN <begin_snap> and <end_snap>
AND bs.blocking_session = s.session_id
AND bs.sample_time = s.sample_time
AND bs.blocking_session IS NOT NULL
AND bs.event IN
('enq: TX - row lock contention'
,'enq: TM - contention'
,'enq: UL - contention'
,'enq: TX - allocate ITL entry')
)
)
CONNECT BY NOCYCLE PRIOR session_id = blocking_session
AND PRIOR sample_time = sample_time
ORDER BY level DESC, blocked_sid, sample_time;
LEVEL KILL SAMPLE_TIME BLOCKED_SID EVENT ULTIMATE_BLOCKER_SID BLOCKING_PATH
---------- ---------------- ------------------------- ----------- ------------------------------ -------------------- -------------------------
1 '953,10578,@1' 22-APR-22 02.27.53.303 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.28.03.311 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.28.13.320 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.28.23.329 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.28.33.337 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.28.43.486 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.28.53.492 PM 953 enq: TX - row lock contention 1713 /1713/953
1 '953,10578,@1' 22-APR-22 02.29.03.497 PM 953 enq: TX - row lock contention 1713 /1713/953
-- TX lock tree
set linesize 300
set pagesize 500
clear column
col sid format a15
col serial format a10
col os_pid format a10
col username format a12
col osuser format a10
col program format a55
col status format a8
col event format a20
col sql_id format a15
select bl.blocking_tree "SID"
, to_char(ps.serial#) "SERIAL"
, ps.os_pid
, ps.username
, ps.osuser
, ps.program
, ps.status
, ps.event
, ps.sql_id
from
(select rownum rn, blocking_tree,locked from (with locks as (select blocking_session locker , sid locked from v$session
where blocking_session is not null)
SELECT LPAD(locked, LENGTH(locked)+(level*2-2), ' ') blocking_tree, locked
FROM (SELECT * FROM locks
UNION
SELECT NULL, locker
FROM locks
WHERE locker NOT IN (SELECT locked FROM locks))
CONNECT BY PRIOR locked = locker
START WITH locker IS NULL)) bl,
(select s.sid
, s.serial#
, s.username
, s.osuser
, p.spid "OS_PID"
, s.program
, s.status
, s.event
, s.sql_id
from v$session s
, v$process p
Where s.paddr = p.addr
order by status,username) ps
where bl.locked=ps.sid
order by bl.rn
set linesize 300 pagesize 300
define 1='TX'
define 2='1=1'
define 3='sysdate-1'
define 4='sysdate'
COL evh_event HEAD "Wait Event" for A35 TRUNCATE
COL evh_graph HEAD "Estimated|Time Graph" JUST CENTER FOR A12
COL pct_evt_time HEAD "% Event|Time"
COL evh_est_total_sec HEAD "Estimated|Total Sec" FOR 9,999,999.9
COL evh_millisec HEAD "Wait time|bucket ms+" FOR A15 JUST RIGHT
COL evh_sample_count HEAD "Num ASH|Samples"
COL evh_est_event_count HEAD "Estimated|Total Waits" FOR 999,999,999.9
COL first_seen FOR A25
COL last_seen FOR A25
BREAK ON evh_event SKIP 1
SELECT
e.evh_event
, e.evh_millisec
, e.evh_sample_count
, e.evh_est_event_count
, e.evh_est_total_sec
, ROUND ( 100 * RATIO_TO_REPORT(evh_est_total_sec) OVER (PARTITION BY evh_event) , 1 ) pct_evt_time
, '|'||RPAD(NVL(RPAD('#', ROUND (10 * RATIO_TO_REPORT(evh_est_total_sec) OVER (PARTITION BY evh_event)), '#'),' '), 10)||'|' evh_graph
, first_seen
, last_seen
FROM (
SELECT
event evh_event
, LPAD('< ' || CASE WHEN time_waited = 0 THEN 0 ELSE CEIL(POWER(2,CEIL(LOG(2,time_waited/1000)))) END, 15) evh_millisec
, COUNT(*) evh_sample_count
, ROUND(SUM(CASE WHEN time_waited >= 1000000 THEN 1 WHEN time_waited = 0 THEN 0 ELSE 1000000 / time_waited END),1) evh_est_event_count
, ROUND(CASE WHEN time_waited = 0 THEN 0 ELSE CEIL(POWER(2,CEIL(LOG(2,time_waited/1000)))) END * SUM(CASE WHEN time_waited >= 1000000 THEN 1 WHEN time_waited = 0 THEN 0 ELSE 1000000 / time_waited END) * 10 / 1000,1 ) evh_est_total_sec
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
FROM
dba_hist_active_sess_history
-- gv$active_session_history
WHERE
regexp_like(event, '&1')
AND &2
AND sample_time BETWEEN &3 AND &4
AND session_state = 'WAITING' -- not really needed as "event" for ON CPU will be NULL in ASH, but added just for clarity
AND time_waited > 0
GROUP BY
event
, CASE WHEN time_waited = 0 THEN 0 ELSE CEIL(POWER(2,CEIL(LOG(2,time_waited/1000)))) END -- evh_millisec
) e
ORDER BY evh_event , evh_millisec
/
Wait time Num ASH Estimated Estimated % Event Estimated
Wait Event bucket ms+ Samples Total Waits Total Sec Time Time Graph FIRST_SEEN LAST_SEEN
----------------------------------- --------------- ---------- -------------- ------------ ---------- ------------ ------------------------- -------------------------
enq: TX - contention < 8 1 167.2 13.4 .2 | | 2022-09-30 11:00:34 2022-09-30 11:00:34
< 16 9 730.3 116.8 1.9 | | 2022-09-29 22:42:50 2022-09-30 15:01:34
< 32 31 1,211.2 387.6 6.2 |# | 2022-09-30 08:56:43 2022-09-30 18:32:38
< 64 126 2,637.6 1,688.0 27.1 |### | 2022-09-29 20:39:39 2022-09-30 18:32:38
< 128 92 1,091.9 1,397.7 22.4 |## | 2022-09-29 19:22:10 2022-09-30 17:34:23
< 256 10 66.3 169.9 2.7 | | 2022-09-30 05:20:48 2022-09-30 16:39:19
< 512 53 130.7 669.4 10.7 |# | 2022-09-29 21:50:57 2022-09-30 17:46:06
< 1024 46 69.2 708.7 11.4 |# | 2022-09-29 21:50:57 2022-09-30 17:05:36
< 2048 53 53.0 1,085.4 17.4 |## | 2022-09-30 17:05:36 2022-09-30 17:05:36
enq: TX - index contention < 1 12 23,455.3 234.6 3.1 | | 2022-09-29 19:40:34 2022-09-30 17:20:50
< 2 21 15,000.5 300.0 4 | | 2022-09-29 18:36:58 2022-09-30 17:59:59
< 4 20 7,205.6 288.2 3.8 | | 2022-09-29 22:22:25 2022-09-30 17:59:59
< 8 113 18,588.0 1,487.0 19.7 |## | 2022-09-29 18:41:30 2022-09-30 18:31:17
< 16 135 12,480.3 1,996.8 26.4 |### | 2022-09-29 20:00:29 2022-09-30 18:31:17
< 32 72 3,300.0 1,056.0 14 |# | 2022-09-29 22:22:25 2022-09-30 17:59:49
< 64 67 1,727.4 1,105.5 14.6 |# | 2022-09-29 18:44:00 2022-09-30 18:20:45
< 128 84 858.5 1,098.8 14.5 |# | 2022-09-29 20:53:32 2022-09-30 17:44:25
set linesize 400 pagesize 300
col EVENT for a30
col TREE for a30
col kill for a17
select *
from (select
--''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,
a.inst_id, a.sid, a.serial#,
a.sql_id,
a.PREV_SQL_ID,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
where 1=1
and isleaf = 1
order by tree_level asc;
INST_ID SID SERIAL# SQL_ID PREV_SQL_ID EVENT STATUS ISLEAF TREE TREE_LEVEL
---------- ---------- ---------- ------------- ------------- ------------------------------ -------- ---------- ------------------------------ ----------
1 1590 48987 4td2x4j3fwcvf 4td2x4j3fwcvf SQL*Net message from client INACTIVE 1 <- 257@1 <- 1590@1 2
set linesize 300
col USERNAME for a20
col MACHINE for a30
col EVENT for a30
col SESS for a20
col kill for a17
SELECT /*+ RULE */
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,
DECODE (request, 0, 'Holder: ', ' Waiter: ') || k.sid sess,
--k.inst_id,
ss.username,
ss.sql_id,
k.id1,
k.id2,
k.lmode,
k.request,
k.TYPE,
SS.LAST_CALL_ET,SS.SECONDS_IN_WAIT,
SS.SERIAL#,
SS.MACHINE,
SS.EVENT,
ss.status,
P.SPID,
CASE
WHEN request > 0
THEN
CHR (BITAND (p1, -16777216) / 16777215) || CHR (BITAND (p1, 16711680) / 65535)
ELSE
NULL
END "Name",
CASE WHEN request > 0 THEN (BITAND (p1, 65535)) ELSE NULL END "Mode"
FROM GV$LOCK k, gv$session ss, gv$process p
WHERE 1=1
and (k.id1, k.id2, k.TYPE) IN (SELECT ll.id1, ll.id2, ll.TYPE FROM GV$LOCK ll
WHERE request > 0)
AND k.sid = ss.sid
AND K.INST_ID = SS.INST_ID
AND ss.paddr = p.addr
AND SS.INST_ID = P.INST_ID
ORDER BY id1, request;
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||' from Instance '||blocker_instance BLOCKER_PROC,
'Number of waiters: '||num_waiters waiters,
'Final Blocking Process: '||decode(p.spid,null,'<none>',p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,'Program: '||p.program image,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,'Seconds in Wait: '||in_wait_secs Seconds,
'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,'<none>',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
gv$session s,gv$session bs,gv$instance i, gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+)
and wc.sid = s.sid (+)
and wc.sess_serial# = s.serial# (+))
AND (s.final_blocking_instance = bs.inst_id (+)
and s.final_blocking_session = bs.sid (+))
AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,num_waiters DESC)
WHERE ROWNUM < 101;
-- Lock time
set linesize 500 pagesize 300
col SQL_TEXT for a50 wrap
col EVENT for a30
col MIN_SAMPLE_TIME for a27
col MAX_SAMPLE_TIME for a27
col kill for a17
select kill ,event,sql_id, blocker_ses, blocker_ser, user_id,module , (EXTRACT(HOUR FROM Diff) * 3600 + EXTRACT(MINUTE FROM Diff) * 60 + EXTRACT(SECOND FROM Diff)) run_time_sec,
sql_text
from (SELECT
--DISTINCT
'''' || a.SESSION_ID || ',' || a.SESSION_SERIAL# ||',@'|| a.inst_id||'''' kill ,event,
a.sql_id, a.blocking_session blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
a.module,
SQL_EXEC_START,
min(a.sample_time) min_sample_time,
max(a.sample_time) max_sample_time,
(max(a.sample_time) - min(a.sample_time) ) Diff,
s.sql_text
FROM GV$ACTIVE_SESSION_HISTORY a, gv$sql s
WHERE a.sql_id = s.sql_id
AND blocking_session IS NOT NULL
-- AND a.user_id <> 0 -- exclude SYS user
and sample_time >sysdate -1
-- and sample_time between to_timestamp('06.11.2022 04:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_TIMESTAMP('06.11.2022 10:52:59', 'dd.mm.yyyy hh24:mi:ss')
and (event like 'library%' or event like 'cursor%' or event like 'enq: TX - row%') ---<<<<<<
group by
'''' || a.SESSION_ID || ',' || a.SESSION_SERIAL# ||',@'|| a.inst_id||'''' ,event,
a.sql_id,
a.blocking_session ,
a.blocking_session_serial# ,
a.user_id,
s.sql_text,
a.module ,
SQL_EXEC_START
)
where 1=1
--and (EXTRACT(HOUR FROM Diff) * 3600 + EXTRACT(MINUTE FROM Diff) * 60 + EXTRACT(SECOND FROM Diff)) >1 ---- sec
;
====
set linesize 800 pagesize 600 col BLOCKER_USERNAME for a15 col BLOCKER_PREV_SQL_TEXT for a30 col BLOCKER_SQL_TEXT for a30 col BLOCKED_SQL_TEXT for a30 col BLOCKED_USERNAME for a15 col BLOCKED_TABLE_NAME for a15 col BLOCKER_MACHINE for a20 col BLOCKED_WAIT_EVENT_TEXT for a20 col kill for a17 col kill1 for a17 select -- Session causing the block -- blockers.blocker_instance_id as blocker_instance_id, -- blocker.sid as blocker_sid, -- blocker.serial# as blocker_serial#, blocker.con_id, ''''||blocker.sid ||','|| blocker.serial#||',@'||blocker.inst_id ||'''' kill, blocker.username as blocker_username, blocker.status as blocker_status, blocker.machine as blocker_machine, blocker.program as blocker_program, blocker.sql_id as blocker_sql_id, blocker.sql_child_number as blocker_sql_child_number, blocker.prev_sql_id as blocker_prev_sql_id, blocker.prev_child_number as blocker_prev_child_number, ' -> ' as is_blocking, -- Sesssion being blocked blocked.con_id con_id1, ''''||blocked.sid ||','|| blocked.serial#||',@'||blocked.inst_id ||'''' kill1, --blocked.sid as blocked_sid, --blocked.serial# as blocked_serial#, blocked.username as blocked_username, blocked.status as blocked_status, blocked.machine as blocked_machine, blocked.program as blocked_program, blocked.blocking_session as blocked_blocking_session, blocked.sql_id as blocked_sql_id, blocked.sql_child_number as blocked_sql_child_number, sys_obj.name as blocked_table_name, dbms_rowid.rowid_create( rowid_type => 1, object_number => blocked.row_wait_obj#, relative_fno => blocked.row_wait_file#, block_number => blocked.row_wait_block#, row_number => blocked.row_wait_row# ) as blocked_rowid, blockers.wait_id as blocked_wait_id, blockers.wait_event as blocked_wait_event, blockers.wait_event_text as blocked_wait_event_text, blockers.con_id as data_container_id, -- Blocker * Blocked SQL Text blocker_sql.sql_text as blocker_sql_text, blocker_prev_sql.sql_text as blocker_prev_sql_text, blocked_sql.sql_text as blocked_sql_text from gv$session_blockers blockers inner join gv$session blocker on blocker.sid = blockers.blocker_sid and blocker.serial# = blockers.blocker_sess_serial# inner join gv$session blocked on blocked.sid = blockers.sid and blocked.serial# = blockers.sess_serial# inner join sys.obj$ sys_obj on sys_obj.obj# = blocked.row_wait_obj# left outer join gv$sql blocked_sql on blocked_sql.sql_id = blocked.sql_id and blocked_sql.child_number = blocked.sql_child_number left outer join gv$sql blocker_sql on blocker_sql.sql_id = blocker.sql_id and blocker_sql.child_number = blocker.sql_child_number left outer join gv$sql blocker_prev_sql on blocker_prev_sql.sql_id = blocker.prev_sql_id and blocker_prev_sql.child_number = blocker.prev_child_number where blocked.status = 'ACTIVE' ;
set linesize 700 pagesize 300col OS_USER for a20 col INST_SID_PATH for a20 col LEVEL_INST_SID for a20 col ROOT_INST_SID for a20 col KILL_SID for a50 col EVENT for a30 col PROGRAM for a40 col MODULE for a40 col OS_MACHINE for a15 with/*+ materialized +*/ aa as ( select * from gv$session s where 1=1 ) select 'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''' immediate;' as kill_SID, lpad('+', 2 * level - 1) ||t.inst_sid as leveL_inst_sid, level level_flag, sys_connect_by_path(t.inst_sid,'/') as inst_sid_path, connect_by_root t.inst_sid as root_inst_sid, connect_by_isleaf as node_isleaf, '##########' as FLAGFLAG0, t.status, t.event#, t.event, t.program, t.module, t.action, t.client_identifier, t.OSUSER os_USER, t.machine os_machine, t.logon_time logon_time, t.last_call_et last_call_et, 'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''' immediate;' as kill_SID, '##########' as FLAGFLAG/*, t.* */ from ( select gs.inst_id||'-'||gs.sid as inst_sid, gs.blocking_instance||'-'||gs.blocking_session par_inst_sid, gs.* from aa gs where 1=1 and (gs.inst_id,gs.sid ) in ( select s.inst_id,s.sid from aa s where 1=1 and s.BLOCKING_SESSION is not null union all select sw.inst_id,sw.sid from aa sw where 1=1 --and lower(sw.MACHINE) not like '%nnnn%' and (sw.INST_ID,sw.SID) in ( select/*+ hash_sj*/ s.BLOCKING_INSTANCE, s.BLOCKING_SESSION from aa s where 1=1 and s.BLOCKING_SESSION is not null ) ) ) t where 1=1 connect by prior t.inst_sid =t.par_inst_sid order siblings by t.inst_sid ;
SET LINESIZE 500 pagesize 300
COL USERNAME FOR A12
COL OBJECT_NAME FOR A15
COL SID FOR A28
COL LOCK_MODE FOR A10
COL CTIME FOR A20
COL MACHINE FOR A25
col SQL_TEXT for a30
-- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''
SELECT S.USERNAME,
SUBSTR(O.NAME, 1, 15) OBJECT_NAME,
DECODE(LV, 1, 'Holder: ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', 'Waiter: ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''') SID,
DECODE(L.LMODE, 0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(L.LMODE)
) LOCK_MODE,
TRUNC(L.CTIME / 3600) || ':' || TRUNC(MOD(L.CTIME, 3600) / 60) || ':' || MOD(L.CTIME, 60) CTIME,
S.STATUS,
S.MACHINE,
S.SQL_ID,
Q.SQL_TEXT
FROM (SELECT /*+ NO_MERGE */(3-LEVEL) LV,
INST_ID,
SID,
TYPE,
LMODE,
CTIME
FROM (SELECT /*+ NO_MERGE */A.INST_ID,
A.SID,
A.TYPE,
A.LMODE,
A.REQUEST,
CASE
WHEN REQUEST = 0 THEN ID1
END ID1,
CASE
WHEN REQUEST > 0 THEN ID1
END ID3,
A.CTIME
FROM GV$LOCK A
WHERE A.TYPE <> 'MR') START WITH REQUEST > 0 CONNECT BY PRIOR ID3 = ID1) L,
GV$SESSION S,
GV$PROCESS P,
SYS.OBJ$ O,
GV$SQL Q
WHERE L.SID = S.SID
AND L.INST_ID = S.INST_ID
AND S.INST_ID = P.INST_ID(+)
AND S.PADDR = P.ADDR(+)
AND S.ROW_WAIT_OBJ# = O.OBJ#(+)
AND L.CTIME >= 1
AND S.SQL_ID = Q.SQL_ID(+)
GROUP BY DECODE(LV, 1, 'Holder: ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', 'Waiter: ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''), S.INST_ID, S.USERNAME, O.NAME, L.TYPE, L.LMODE, L.CTIME, S.STATUS, S.MACHINE,S.SQL_ID,Q.SQL_TEXT;
====
col process for a15
col terminal for a25
col lmode for a20
col object for a30
set lin 1000
col kill for a18
col USERNAME for a20
col LREQUEST for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.con_id,s.sql_id,
decode(s.process, null, decode(substr(p.username,1,1), '?', upper(s.osuser), p.username), decode(p.username, 'ORACUSR ', upper(s.osuser), s.process)
) process,
nvl(s.osuser, 'SYS ('||substr(p.username,1,4)||')') username,
decode(s.terminal, null, rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
decode(l.type, 'MR', decode(u.name, null,
'DICTIONARY OBJECT', u.name||'.'||o.name),
'TD', u.name||'.'||o.name,
'TM', u.name||'.'||o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE','NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object,
decode(l.type,
-- Long locks major
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks minor
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest ,s.event
from gv$lock l, gv$session s, obj$ o, user$ u, gv$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.inst_id = s.inst_id
and l.id1 = o.obj#(+)
and o.owner# = u.user#(+)
and l.type <> 'MR'
and p.background!=1
UNION ALL /*** LATCH HOLDERS SQL ***/
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',s.con_id,s.sql_id, s.process, s.osuser, s.terminal,'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr),s.event
from gv$process p, gv$session s, gv$latchholder h
where h.pid = p.pid
and h.inst_id = p.inst_id
and p.addr = s.paddr
and p.background!=1
UNION ALL /*** LATCH WAITERS SQL ***/
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',s.con_id,s.sql_id, s.process, s.osuser, s.terminal, 'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait,s.event
from gv$session s, gv$process p, gv$latch l
where latchwait is not null
and p.addr = s.paddr
and p.inst_id = s.inst_id
and p.latchwait = l.addr
and p.background!=1
/
col owner format a15
col object format a30
col object_type format a10
col index_name format a30
col table_name format a30
col lockmode format a20
col event_name format a30 head 'EVENT NAME'
col sql_id format a13
col instance_number format 9999 head 'INST'
set line 200 trimspool on pagesize 60
-- d_date_format set by get_date_range.sql
clear break
break on instance_number skip 1 on sql_id skip 1
with waits as (
select
sh.inst_id instance_number
, sh.blocking_inst_id
, sh.sql_id
, n.name event_name
, chr(bitand(sh.p1,-16777216)/16777215)|| chr(bitand(sh.p1, 16711680)/65535) LOCKNAME
, bitand(sh.p1,65535) lockmode
, sh.current_obj#
, sh.current_file#
, sh.current_block#
from gv$active_session_history sh
join v$event_name n on sh.event_id = n.event_id
where sh.blocking_inst_id is not null
--and sh.event_id = ( select event_id from v$event_name where name like 'enq: TX - row lock contention')
and sh.event_id in ( select event_id from v$event_name where name like 'enq:%')
and sh.current_obj# is not null
and sh.current_obj# > 0
),
itlwaits as (
select distinct
w.instance_number
, w.event_name
, w.sql_id
--, w.lockname
, current_obj#
, current_file#
, current_block#
, count(*) itl_wait_count
from waits w
where 1=1
-- and w.lockname = 'TX'
-- and lockmode = 4 -- ITL
-- in this case just interested in indexes
--and w.event_name = 'enq: TX - index contention'
group by w.instance_number, w.event_name, w.sql_id, current_obj#, current_file#, current_block#
)
select
w.instance_number
, w.sql_id
, w.itl_wait_count
, decode(i.owner,null,t.owner,i.owner) owner
, o.object_type
, decode(i.index_name,null,t.table_name,i.index_name) object
, decode(i.ini_trans,null,t.ini_trans,i.ini_trans) ini_trans
, decode(i.max_trans,null,t.max_trans,i.max_trans) max_trans
, current_file#
, current_block#
from itlwaits w
join dba_objects o on o.object_id = w.current_obj#
left outer join dba_indexes i on i.owner = o.owner
and i.index_name = o.object_name
left outer join dba_tables t on t.owner = o.owner
and t.table_name = o.object_name
order by w.sql_id, w.itl_wait_count
/
col lockmode format a20
col event_name format a40 head 'EVENT NAME'
set line 200 trimspool on
set pagesize 60
set tab off
-- d_date_format set by get_date_range.sql
with waits as (
select
sh.inst_id instance_number
, sh.blocking_inst_id
, sh.sql_id
, n.name event_name
, chr(bitand(sh.p1,-16777216)/16777215)||
chr(bitand(sh.p1, 16711680)/65535) LOCKNAME
, bitand(sh.p1,65535) lockmode
from gv$active_session_history sh
join v$event_name n on sh.event_id = n.event_id
where sh.blocking_inst_id is not null
--and sh.event_id = ( select event_id from v$event_name where name like 'enq: TX - row lock contention')
and sh.event_id in ( select event_id from v$event_name where name like 'enq:%')
)
select
w.instance_number
, w.event_name
--, w.blocking_inst_id
--, w.sql_id
, w.lockname
--, decode(w.lockmode, 4,'ITL',6,'ROWLOCK','UNKNOWN') lockmode
, case w.lockname
when 'TX' then w.lockmode || '-' || decode(w.lockmode, 0,'None', 1,'No Lock', 2,'Row-S (SS)', 3,'Row-X (SX)',4,'ITL', 5, 'S/Row-X (SRX)', 6,'ROWLOCK','UNKNOWN')
when 'TM' then w.lockmode || '-' || decode(w.lockmode, 0,'None', 1,'No Lock', 2,'Row-S (SS)', 3,'Row-X (SX)',4,'Share', 5, 'S/Row-X (SRX)', 6,'Exclusive','UNKNOWN')
else w.lockmode || '-' || decode(w.lockmode, 0,'None', 1,'No Lock', 2,'Row-S (SS)', 3,'Row-X (SX)',4,'Share', 5, 'S/Row-X (SRX)', 6,'Exclusive','UNKNOWN')
end lockmode
, count(*) waitcount
from waits w
group by
w.instance_number
, w.event_name
--, w.blocking_inst_id
--, w.sql_id
, w.lockname
, w.lockmode
order by waitcount
/
INSTANCE_NUMBER EVENT NAME LOCKNAME LOCKMODE WAITCOUNT
--------------- ---------------------------------------- -------- -------------------- ----------
1 enq: PR - contention PR 6-Exclusive 1
1 enq: JG - queue lock JG 6-Exclusive 1
1 enq: WF - contention WF 6-Exclusive 2
1 enq: HW - contention HW 6-Exclusive 3
1 enq: TX - contention TX 4-ITL 3
1 enq: CF - contention CF 4-Share 7
1 enq: RO - fast object reuse RO 6-Exclusive 19
1 enq: CR - block range reuse ckpt CR 6-Exclusive 39
1 enq: CF - contention CF 5-S/Row-X (SRX) 85
9 rows selected.
alter session set nls_date_format='yyyy-mm-dd hh24';
set pagesize 300 linesize 400
def DATINI="TIMESTAMP'2024-09-11 10:00:00'"
def DATFIN="TIMESTAMP'2024-09-11 12:00:00'"
clear breaks
col sidqueue for a20 head "SID|QUEUE"
col COMPLETE_SESSION_ID for a14 head "SESSION_ID"
col USERNAME for a15 wrap
col MODULE for a20 wrap
col EVENT for a20 wrap
col OBJ for a30 wrap
col MINUTOS for 999.9
break on sample_time skip page on level
with ash as (
select a.*,
O.OWNER || '.' || O.OBJECT_NAME as obj,
u.username,
case when blocking_session is NULL then 'N' else 'Y' end IS_WAITING,
case
when
instr(
listagg(blocking_session||'.')
WITHIN GROUP (order by blocking_session)
OVER (partition by sample_id) ,
'.'||session_id||'.'
) = 0
then 'N'
else 'Y'
end as IS_HOLDING,
case
when blocking_session is NULL then NULL
when instr(
listagg(session_id||'.')
WITHIN GROUP (order by session_id)
OVER (partition by sample_id) ,
'.'||blocking_session||'.'
) = 0
then 'N'
else 'Y'
end as IS_BLOCKING_SID_ACTIVE
from dba_hist_active_sess_history a
left join dba_objects o on o.object_id = a.CURRENT_OBJ#
left join dba_users u on u.user_id = a.user_id
where SAMPLE_TIME between &datini and &datfin
),
ash_with_inactive as (
-- I need to include the inactive blocking sessions because ASH does not record INACTIVE
select
sample_id, cast(sample_time as date) as sample_time,
session_id, session_serial#, instance_number,
blocking_session, blocking_session_serial#, blocking_inst_id,
sql_id, sql_exec_start, sql_exec_id, TOP_LEVEL_SQL_ID, XID,
username, module, nvl(event,'On CPU') event,
sysdate + ( (sample_time - min(sample_time) over (partition by session_id, session_serial#, instance_number, event_id, SEQ#)) * 60*60) - sysdate as swait,
obj,
IS_WAITING, IS_HOLDING, 'Y' IS_ACTIVE, IS_BLOCKING_SID_ACTIVE
from ash
UNION ALL
select DISTINCT
sample_id, cast(sample_time as date) as sample_time,
blocking_session as session_id, blocking_session_serial# as session_serial#, blocking_inst_id as instance_number,
NULL as blocking_session, NULL as blocking_session_serial#, NULL as blocking_instance,
NULL as sql_id, NULL as sql_exec_start, NULL as sql_exec_id, NULL as TOP_LEVEL_SQL_ID, NULL as XID,
NULL as username, NULL as module, '**INACTIVE**' as event, NULL as swait, NULL as obj,
'N' as IS_WAITING, 'Y' as IS_HOLDING, 'N' IS_ACTIVE, null as IS_BLOCKING_SID_ACTIVE
from ash a1
where IS_BLOCKING_SID_ACTIVE = 'N'
),
locks as (
select b.*,
listagg(event||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) event_chain,
listagg(username||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) user_chain,
listagg(module||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) module_chain,
listagg(obj||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) obj_chain,
listagg(xid||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) xid_chain,
listagg(session_id||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) sid_chain,
listagg(sql_id||'.') within group (order by lock_level) over (partition by sample_id, top_level_sid) sql_id_chain
from (
select a.*,
rownum rn, level lock_level,
case when level > 2 then lpad(' ',2*(level-2),' ') end ||
case when level > 1 then '+-' end || session_id as sidqueue,
session_id || ',' || session_serial# || '@' || instance_number COMPLETE_SESSION_ID,
CONNECT_BY_ROOT session_id as top_level_sid
from ash_with_inactive a
connect by
prior sample_id = sample_id
and prior session_id = blocking_session
and prior instance_number = blocking_inst_id
and prior session_serial# = blocking_session_serial#
start with
IS_HOLDING = 'Y' and IS_WAITING = 'N'
order SIBLINGS by sample_time, swait desc
) b
)
select
-- sample_id,
sample_time, lock_level, sidqueue, COMPLETE_SESSION_ID,
username, module, xid,
event, swait, OBJ,
sql_id, sql_exec_start, sql_exec_id, top_level_sql_id
from locks
where 1=1
--and event_chain like 'enq:%'
--and user_chain like '%PROD%'
--and module_chain like '%PB%'
--and obj_chain like '%PGM%'
--and xid_chain like '%1300%'
--and sid_chain like '%7799%'
--and sql_id_chain like '%fmxzsfxn0ym%'
order by rn
/
set linesize 400 pagesize 400
COLUMN module format A20
COLUMN sql_opname format A20
COLUMN etime_secs FORMAT 999,999.9
COLUMN etime_mins FORMAT 999,999.9
COLUMN user_id FORMAT 999999
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 99999
COLUMN username FORMAT A25
COLUMN inst_id FORMAT 99
COLUMN sql_opname FORMAT A10
COLUMN sql_id FORMAT A13
COLUMN sql_exec_id FORMAT 9999999999
COLUMN max_temp_mb FORMAT 999,999,999
COLUMN sql_start_time FORMAT A25
COLUMN sql_end_time FORMAT A25
col kill for a17
col event for a30
SELECT
--
ash.con_id,
''''||ASH.session_id ||','|| ASH.SESSION_SERIAL#||',@'||ASH.inst_id ||'''' kill,
ASH.sql_id,
ASH.sql_exec_id,
ASH.sql_opname,
ASH.module,
MIN(sample_time) sql_start_time,
MAX(sample_time) sql_end_time,
ash.event,
((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
MAX(temp_space_allocated)/(1024*1024) max_temp_mb
FROM gv$active_session_history ASH
WHERE 1=1
-- and ASH.session_type = 'FOREGROUND'
AND ASH.sql_id IS NOT NULL
-- and sample_time > sysdate - interval '15' minute
--AND sample_time BETWEEN to_timestamp('11-04-2020 00:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('11-04-2020 02:00', 'DD-MM-YYYY HH24:MI')
--and ASH.sql_id = SQL_ID
GROUP BY ASH.inst_id,
ASH.user_id,
ASH.session_id,
ASH.session_serial#,
ash.con_id,
ASH.sql_id,
ASH.sql_opname,
ASH.sql_exec_id,
ASH.module,
ash.event
HAVING MAX(temp_space_allocated) > 0
;
======
var sqlid VARCHAR2(13);
begin :sqlid := 'g0bggfqrddc4w'; end; ----- change sql id
/
set long 50000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
);
