Oracle long running SQL ...
long running SQL
long running SQL queries
set pages 300 lines 300
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT
--inst_id,sid, serial#,
''''||sid ||','||serial#||',@'||inst_id ||'''' kill,
sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND sofar<>totalwork
AND time_remaining > 0
/
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A50
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
col kill for a17
SELECT
--s.sid, s.serial#,
''''||s.sid ||','||s.serial#||',@'||s.inst_id ||'''' kill,
s.machine,
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 FROM gv$session s, gv$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND TOTALWORK != 0;
set linesize 500 pagesize 500
col MESSAGE for a50
col "USERNAME| SID,SERIAL#,inst" for a40
col "STARTED|MIN_ELAPSED|REMAIN" for a25
select USERNAME||'| '||''''||sid ||','|| serial#||',@'||inst_id ||'''' "USERNAME| SID,SERIAL#,inst",SQL_ID,round(SOFAR/TOTALWORK*100,2) "%DONE"
,to_char(START_TIME,'DD-Mon HH24:MI')||'| '||trunc(ELAPSED_SECONDS/60)||'|'||trunc(TIME_REMAINING/60) "STARTED|MIN_ELAPSED|REMAIN" ,SQL_ID,MESSAGE
from gv$session_longops
where SOFAR/TOTALWORK*100 <>'100'
and TOTALWORK <> '0'
-- and MESSAGE not like 'RMAN:%'
order by "STARTED|MIN_ELAPSED|REMAIN" desc, "USERNAME| SID,SERIAL#,inst";
set lines 500
col opname format a35
col target format a25
col units format a10
col kill for a17
col message for a20
col kill for a17
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
order by start_time desc)
/
=====
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
;
====
define 1='dgq1rfu9bzasw'
select sql_id,dbms_lob.substr(SQL_FULLTEXT,4000) sql_text from v$sql where sql_id like '%&&1%'
and rownum <2
union all
select sql_id,dbms_lob.substr(sql_text,4000) sql_text from dba_hist_sqltext where sql_id like '%&&1%'
and rownum <2
;
