long running SQL ..
set linesize 800 pagesize 300
col perc format 9999999999
col sql_exec_start format a22
col username format a12
col opname format a30
col target format a40
col message format a80
col parallel_coordinator format 9999999999
col fetches format 9999999999
col executions format 9999999999
col loads format 9999999999
col sql_fulltext format a70 wrap
col kill for a16
col progress_pct format 99999999.00
col elapsed format a10
col remaining format a10
select ''''||sl.sid ||','|| sl.serial#||',@'||sl.inst_id ||'''' kill,
decode(sl.totalwork, 0, 0, round((sl.sofar * 100) / sl.totalwork, 2)) as perc,
to_char(sql_exec_start, 'dd.mm.yyyy hh24:mi:ss') as sql_exec_start,
sl.username,
sl.opname,
sl.target,
sl.message,
qcsid as parallel_coordinator,
s.fetches,
s.executions,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
s.loads,
s.sql_id,
dbms_lob.substr(lob_loc => s.SQL_FULLTEXT, amount => 4000) AS SQL_FULLTEXT
from gv$session_longops sl , gv$sql s
where 1=1
and s.address = sl.sql_address
and s.hash_value = sl.sql_hash_value
and s.inst_id = sl.inst_id
-- and sl.start_time > sysdate - &num_days.
--and decode(sl.totalwork, 0, 0, round((sl.sofar * 100) / sl.totalwork, 2)) !=100
-- AND sl.opname not LIKE 'RMAN%'
-- AND sl.opname NOT LIKE '%aggregate%'
order by perc, username,sql_exec_start;
set linesize 300 pagesize 1000
col instance_name FORMAT a10 HEADING 'Instance'
col sid HEADING 'Oracle|SID'
col serial_num HEADING 'Serial|#'
--COLUMN opname FORMAT a30 HEADING 'RMAN|Operation'
col start_time FORMAT a18 HEADING 'Start|Time'
col totalwork HEADING 'Total|Work'
col sofar HEADING 'So|Far'
col pct_done HEADING 'Percent|Done'
col elapsed_seconds HEADING 'Elapsed|Seconds'
col time_remaining HEADING 'Seconds|Remaining'
col done_at format a18 HEADING 'Done|At'
col kill for a15
col event for a30
col program for a20
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill
,a.sql_id
,PREV_SQL_ID
, b.opname opname
, TO_CHAR(b.start_time, 'dd/mm/yy HH24:MI:SS') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork, 0, 0.001, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
,a.event
, DECODE( b.time_remaining, 0, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'dd/mm/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'dd/mm/yy HH24:MI:SS')) done_at
,PROGRAM
FROM gv$session a, gv$session_longops b
where 1=1
and a.sid=b.sid
and a.serial#=b.serial#
and a.inst_id=b.inst_id
-- and a.program LIKE 'rman%' AND b.opname LIKE 'RMAN%' AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY b.start_time;
set lines 500
col opname format a35
col target format a10
col units format a10
col kill for a15
col message for a50 wrap
select * from (
select
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, sql_id,
opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min , sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id, TO_CHAR(sql_exec_start, 'DD-MM-YYYY HH24:MI:SS') sql_exec_start ,message
from gv$session_longops
WHERE sofar < totalwork
-- and sql_id='g75x2hpgkgqcg' <<<< for this sql
order by start_time desc)
/
set lines 500
col kill for a18
col OPNAME for a35
col "%complete" for 999999.99
select
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,OPNAME, (sofar/totalwork)*100 as "%complete", ELAPSED_SECONDS, TIME_REMAINING
from gv$session_longops
where 1=1
and sofar<>totalwork
and totalwork<>0
and SID||SERIAL# in (select SID||SERIAL# from gv$session)
;
set linesize 200 pages 9999
column sid format 9999999
column RT format a10
column ET format a10
column opname format a30
column target format a20
column pct_complete format 09D00 heading '%%%%'
col kill for a17
select
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill ,con_id, sql_id, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') start_time, opname, round((100 * sofar)/totalwork, 2) pct_complete,
( extract(day from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ' ' ||
extract(hour from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
extract(minute from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
round(extract(second from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)))
) ET,
( extract(day from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ' ' ||
extract(hour from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ':' ||
extract(minute from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ':' ||
round(extract(second from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)))
) RT
from gv$session_longops
where time_remaining > 0
;
--top five long running sqls
set linesize 210 pagesize 300
col "Details" for a210
select
(case when elapsed_min>15 then 'CRITICAL - ' else 'WARNING - ' end)||'SESSION('||'con_id-'||con_id||'=>' ||''''||sid ||','|| serial#||',@'||inst_id ||''''||') USER('||username||'/'||osuser||'@'||machine||') PROG-'||program||', SQL-'||sql_id||' ('||elapsed_min||' MINS)'
||event "Details"
--,event
from (
select
ROW_NUMBER() OVER (ORDER BY LAST_CALL_ET DESC) RN,
con_id,
SID, SERIAL#, USERNAME, OSUSER, PROGRAM,
MACHINE, SQL_ID,inst_id, round(LAST_CALL_ET/60) ELAPSED_MIN,event
from gv$session
where status='ACTIVE'
and username is not null
and type <> 'BACKGROUND'
and username not in ('SYS','SYSTEM','GGATE')
and last_call_et/60 > 10 --- 10 min
)
where 1=1
and rn<5
;
