Finding Oracle Currently Running Queries

How do you check oracle database for long running queries?

We are performing some load testing on our application which is accessing an Oracle database. I have been given a task to identify the longest running queries during this period. Is there a way for me to identify these queries (including the query text) or stored procedures (just the name of the proc would suffice) by joining some V$ tables? 


Step 1: Execute the query

column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
v$session a, v$process b, v$transaction c,
v$sqlarea s
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) -- running for 15 minutes
order by c.start_time

Step 2: desc v$session

Step 3: select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)

Step 4: select sql_text from v$sqltext where address='XXXXXXXX';

Step 5: select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;


Query V$Session_longops

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 v$session s,
v$session_longops sl
AND s.serial# = sl.serial#

And then run the following query 

SELECT a.sql_text
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &1 --> From above sql
ORDER BY a.piece;

See Also
The Ten Commandments for Fast Queries

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.