long running query session longops ....

long running query session longops


--   http://anuj-singh.blogspot.com/2014_08_25_archive.html

set linesize 300 pagesize 1000
COLUMN instance_name FORMAT a10 			HEADING 'Instance'
COLUMN sid 						HEADING 'Oracle|SID'
COLUMN serial_num 					HEADING 'Serial|#'
--COLUMN opname FORMAT a30 				HEADING 'RMAN|Operation'
COLUMN start_time 		FORMAT a18 		HEADING 'Start|Time'
COLUMN totalwork 					HEADING 'Total|Work'
COLUMN sofar 						HEADING 'So|Far'
COLUMN pct_done 					HEADING 'Percent|Done'
COLUMN elapsed_seconds 					HEADING 'Elapsed|Seconds'
COLUMN time_remaining 					HEADING 'Seconds|Remaining'
COLUMN 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
col TOTALWORK for 999999999999
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 1=1
	--  and sofar < totalwork
    -- and sql_id='g75x2hpgkgqcg'  <<<< for this sql 
      order by start_time desc
	  )
/


-- from Web
set pages 500 lines 500
col kill        for a15
col message 	for a45
col totalwork 	for a20
col target 		for a27
col operation 	for a35
select 
''''||l.sid ||','|| l.serial#||',@'||l.inst_id ||'''' kill
     , case when l.time_remaining > 0 or l.sofar < l.totalwork then 'Yes' end as "Active?"
     , l.opname as operation
     , l.totalwork || ' ' || l.units as totalwork
     , nvl(l.target,l.target_desc) as target
     , l.start_time
     , round(100 * l.sofar/greatest(l.totalwork,1),1) as "Complete %"
     , rtrim(rtrim(ltrim(ltrim(numtodsinterval(l.elapsed_seconds,'SECOND'),'+0'),' '),'0'),'.') as elapsed
     , ltrim(ltrim
       ( cast(numtodsinterval(l.elapsed_seconds * greatest(nullif((l.totalwork - l.sofar)/nullif(l.sofar,0),0),0),'SECOND')
              as interval day(2) to second(0))
       ,'+0'),' ') as remaining
     , case
           when l.sofar >= l.totalwork then l.last_update_time
           else sysdate + numtodsinterval( l.elapsed_seconds * greatest(nullif((l.totalwork - l.sofar)/nullif(l.sofar,0),0),0),'SECOND')
       end as est_completion
 , l.sql_id
     , l.sql_address
     , l.sql_hash_value
	  , message
from gv$session_longops l
where 1=1
--and :sid in (sid,qcsid)
--and  l.start_time >= to_date(:logon_time,'dd/mm/yyyy hh24:mi:ss')
-- and sofar != totalwork
order by l.start_time desc
;

                                                                                                                                          

set linesize 300 pagesize 300
column username       cle
column sid            cle
column lock_type      cle
column MODE_HELD      cle
column MODE_REQUESTED cle
column LOCK_ID1       cle
column LOCK_ID2       cle


col sid 			for 99999
col serial# 		for 9999999
col machine 		for a30
col program 		for a30 trunc
col progress_pct 	for 99999999.00
col elapsed 		for a10
col remaining 		for a10
col kill 			for a15
SELECT 
''''||s.sid||','||s.serial#||',@'||s.inst_id||''''  kill ,
--s.sid,
--    s.serial#,
       s.sql_id,
       s.machine,
       s.program,
       lpad(TRUNC(sl.elapsed_seconds/60),3,' ') || ':' || lpad(MOD(sl.elapsed_seconds,60),2,' ') elapsed,
       lpad(TRUNC(sl.time_remaining/60),3,' ')  || ':' || lpad(MOD(sl.time_remaining,60),2,' ') remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   gv$session s, gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
 and s.inst_id     = sl.inst_id 
and    sl.time_remaining > 0
order by 6
/