Oracle Top 10 most resource intensive queries

Oracle most resource intensive queries


set pagesize 200
select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value,
sql_id
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.executions,0,1,a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 - round(100 * a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value,
a.sql_id
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM','SYSMAN','ORACLE_OCM','DBSNMP','MDSYS','XDB','EXFSYS')
order by 3 desc)
where rownum < 11


-- SYSMAN
-- The SYSMAN user represents the Enterprise Manager super admin account.
-- This EM admin can create and modify other EM admin accounts as well as admin the database
-- -- instance itself.

-- DBSNMP
-- The DBSNMP user is used by EM to monitor the database. EM uses this account to access
-- performance stats about the database.
-- The DBSNMP credentials sometimes referred to as the monitoring credentials.
/



default user list

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml