Mani's Blog

June 29, 2011

Monitoring Long running SQL in Oracle

Filed under: Oracle — mani @ 6:14 pm

How much work is done:

SELECT USERNAME, sid, to_char(start_time,’hh24:mi:ss’) stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;

Here is the sample output:

 

USERNAME                  SID STIME                                       MESSAGE                       PERCENT
—————————— ———- ————————————————————————— —————————————- ——-
SYS                     3231 17:45:18                                      Sort Output:  : 322468 out of 1612271 Bl     20.00
ocks done

How long the query is running:

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status=’ACTIVE’
and type <>’BACKGROUND’
and last_call_et> 60
order by sid,serial#,q.piece ;

Here is the sample output:

 

USERNAME                  SID    SERIAL# MINS_RUNNING SQL_TEXT
—————————— ———- ———- ———— —————————————————————-
SYS                         3231    1814   21.0666667 CREATE table “XN_MSG”.mv_internal_msg_DATA
(
“ID”,
“RECIPIENT_ID

SYS                     3231    1814   21.0666667 S”,
“BODY”,
CONSTRAINT MV_PK_INTERNAL_MSG_DATA PRIMARY KEY (ID))

SYS                     3231    1814   21.0666667
organization index overflow nologging parallel 16
partition by

SYS                     3231    1814   21.0666667 hash(ID) partitions 16 store in (xn_msg)
tablespace XN_M

SYS                     3231    1814   21.0666667 SG
AS select /*+ PARALLEL */
“ID”,
“RECIPIENT_IDS”,
“BOD

SYS                     3231    1814   21.0666667 Y”
from xn_msg.internal_msg_DATA
WHERE ROWNUM < 10000000

Create a free website or blog at WordPress.com.