DB version: 11.2.0.4
OS : Oracle Linux 6.5
I want to find all the queries which are running for more than 30 minutes in my 3-node RAC DB. Below is a variant of a query I found in google. Will it accurately return Queries (SQL_IDs) running for more than 30 minutes ?
I am bit confused about whether to use LAST_CALL_ET or SQL_EXEC_START
SELECT sid,serial#,inst_id,process,osuser,schemaname,machine,status,program,sql_id,sql_exec_start,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as LOGGED_ON_MIN,
ROUND(LAST_CALL_ET/60,1) as CURRENT_SQL_MIN
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL
AND TYPE = 'USER'
and ROUND(LAST_CALL_ET/60,1) > 30 -- more than 30 minutes
ORDER BY LAST_CALL_ET DESC ;