spool cur_sql
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 a.sql_Text,
a.hash_value “HASH” ,
b.username “USR”, a.executions, rows_processed, a.buffer_gets, a.disk_reads, a.sorts, a.users_executing
from v$sqlarea a,dba_users b
where b.username not in (‘SYS’,’SYSTEM’,’DBSNMP’,’VIVEK’, ‘SYSMAN’)
and a.PARSING_SCHEMA_ID = b.user_id
and a.users_executing > 0;
spool off
