spool badsql5
set lines 132
set pages 300
col USR format a14
col PAR heading “Parsing ID” format 9999999
col ELA heading “ELP time(Sec)” format 9999999.0
col HASH heading “Hash Value”
col USR heading “Parsing User”
col T heading “SQL Text having Elapsed time > 1 sec”
break on HASH on USR on ELA skip 1
select to_char(a.sql_fullText) T,
(elapsed_time/1000000) “ELA”,
a.hash_value “HASH” ,
b.username “USR”, a.executions, rows_processed, a.last_load_time, a.buffer_gets, a.disk_reads, a.sorts
from v$sql a,dba_users b
where (a.elapsed_time/1000000)>1
and b.username not in (‘SYS’,’SYSTEM’,’DBSNMP’,’VIVEK’, ‘SYSMAN’)
and a.PARSING_SCHEMA_ID = b.user_id
and a.elapsed_time =(select max(elapsed_time) from v$sql g where g.hash_value = a.hash_value)
order by elapsed_time desc;
spool off