How to check Oracle database for long running queries

This one shows SQL that is currently "ACTIVE":-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:

select
  object_name, 
  object_type, 
  session_id, 
  type,         -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request, 
  block, 
  ctime         -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99 

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

Try this, it will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.

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

v$session_longops

If you look for sofar != totalwork you'll see ones that haven't completed, but the entries aren't removed when the operation completes so you can see a lot of history there too.

You can check the long-running queries details like % completed and remaining time using the below query:

 SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR, 
 TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" 
 FROM V$SESSION_LONGOPS 
 WHERE OPNAME NOT LIKE '%aggregate%' 
       AND TOTALWORK != 0 
       AND SOFAR <> TOTALWORK;

For the complete list of troubleshooting steps, you can check here:Troubleshooting long running sessions

You can use the v$sql_monitor view to find queries that are running longer than 5 seconds. This may only be available in Enterprise versions of Oracle. For example this query will identify slow running queries from my TEST_APP service:

select to_char(sql_exec_start, 'dd-Mon hh24:mi'), (elapsed_time / 1000000) run_time,
       cpu_time, sql_id, sql_text 
from   v$sql_monitor
where  service_name = 'TEST_APP'
order  by 1 desc;

Note elapsed_time is in microseconds so / 1000000 to get something more readable

select sq.PARSING_SCHEMA_NAME, sq.LAST_LOAD_TIME, sq.ELAPSED_TIME, sq.ROWS_PROCESSED, ltrim(sq.sql_text), sq.SQL_FULLTEXT
  from v$sql sq, v$session se
 order by sq.ELAPSED_TIME desc, sq.LAST_LOAD_TIME desc;