How to see progress of long running sql statements? (Oracle)

From now and then, you are asked by your teammates about when his/her sql statements will finish. Here is a script that shows an estimation. It can be used for any long running sqls including RMAN jobs as well.

Sometimes this estimation might not be accurate. The elapsed_seconds might change to zero or a few seconds frequently for some long running jobs. That means the job is progressing and it is not hang.

prompt ====================================================
prompt == Start of check_longops.sql
prompt ====================================================

prompt ====================================================
prompt == Check Long Operation notes
prompt == These are just Estimations and sometimes not accurate.
prompt ==
prompt ==
prompt ====================================================

SET PAGES 100
SET LINE 200
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A20
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN message FORMAT a80
COLUMN opname FORMAT a20
column username format a20

SELECT s.sid,
       sl.username,
       opname,
       MESSAGE,
       s.serial#,
       s.machine,
       ROUND (sl.elapsed_seconds / 60) || ':' || MOD (sl.elapsed_seconds, 60)
           elapsed,
       ROUND (sl.time_remaining / 60) || ':' || MOD (sl.time_remaining, 60)
           remaining,
       DECODE (totalwork, 0, 0, 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 sofar <> totalwork;

 prompt ====================================================
 prompt == End of check_longops.sql
 prompt ====================================================

How to find the sessions that are using most CPUs? (Oracle)

Here is a handy script I usually use.

check_cpu.sql

prompt ====================================================
prompt == Start of check_cpu.sql
prompt ====================================================

prompt ====================================================
prompt == Check CPU notes
prompt == OS level CPU usage can be monitor by top or topas
prompt == Where you should be able to pin point the top
prompt == process ID
prompt == 
prompt ====================================================
prompt ====================================================
prompt == Show top 20 CPU usage sessions
prompt ====================================================
set lines 300
set pages 300
col machine format a20
col program format a30
col name format a30
select * from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20;


prompt ====================================================
prompt == Check Sql Text related to above sql_id
prompt ====================================================
SET LONG 100000 
select sql_id, SQL_TEXT from v$SQL where SQL_ID in (
select sql_id from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20
);

prompt ====================================================
prompt == End of check_cpu.sql
prompt ====================================================