Oracle TOP SQL

select sql_text,
username,
disk_reads_per_exec,
buffer_gets_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
loads,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time_secs,
cpu_time_secs_per_execute,
elapsed_time_secs,
elapsed_time_secs_per_execute,
address,
hash_value
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,
round ((a.buffer_gets/decode(a.executions,0,1,a.executions)),2) buffer_gets_per_exec,
a.parse_calls,
a.sorts,
a.executions,
a.loads,
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,
round(cpu_time / 1000000,3) cpu_time_secs,
round((cpu_time / 1000000) / decode (a.executions,0,1,a.executions),3) cpu_time_secs_per_execute,
round(elapsed_time / 1000000,3) elapsed_time_secs,
round((elapsed_time / 1000000)/decode(a.executions,0,1,a.executions),3) elapsed_time_secs_per_execute,
address,
hash_value
from sys.v_$sqlarea a, sys.all_users b
where a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM')
order by 3 desc)
where rownum < 15;


SELECT /* SQL Monitor Query for >= 10g */
'(' || s.SID || ',' || s.serial# || ')' "sid/serial",
s.program "pgrm",
s.terminal "term",
s.username "db user",
DECODE (ROUND (s.last_call_et / 60), '0', '< 1', ROUND (s.last_call_et / 60)) "rtime (mins)",
DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers",
DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves",
si.block_changes "sess bchgs",
si.physical_reads "sess preads",
pss_pr."px preads" "child px preads",
si.consistent_gets "sess cgets",
pss_cg."px cgets" "child px cgets",
ss_cpu."sess cpu" "sess cpu",
pss_cpu."px cpu" "child px cpu",
sa.optimizer_cost "curr sql cost",
su."blocks" "temp blocks",
t."used_ublk" "undo blocks",
s.event "wait",
s.seconds_in_wait "wait secs",
s.state "wait state",
s.sql_id "current sql id",
sa.sql_fulltext "sql text",
DBMS_XPLAN.display_cursor (s.sql_id, s.sql_child_number) "sql xplan"
FROM v$session s,
v$sess_io si,
v$sql sa,
(SELECT qcsid, COUNT (DISTINCT server_set) "px oper cnt",
COUNT (*) "px count"
FROM v$px_session
WHERE NOT server_set IS NULL
GROUP BY qcsid, DEGREE) ps,
(SELECT qcsid, SUM (VALUE) "px preads"
FROM v$px_sesstat
WHERE statistic# = 54 AND SID != qcsid
GROUP BY qcsid) pss_pr,
(SELECT qcsid, SUM (VALUE) "px cgets"
FROM v$px_sesstat pss
WHERE statistic# = 50 AND SID != qcsid
GROUP BY qcsid) pss_cg,
(SELECT qcsid, SUM (VALUE) "px cpu"
FROM v$px_sesstat pss
WHERE statistic# = 12 AND SID != qcsid
GROUP BY qcsid) pss_cpu,
(SELECT ss.SID, SUM (ss.VALUE) "sess cpu"
FROM v$sesstat ss
WHERE statistic# = 12
GROUP BY ss.SID) ss_cpu,
(SELECT t.ses_addr, SUM (t.used_ublk) "used_ublk"
FROM v$transaction t
GROUP BY t.ses_addr) t,
(SELECT su.session_addr, SUM (su.blocks) "blocks"
FROM v$sort_usage su
GROUP BY su.session_addr) su
WHERE s.sql_address = sa.address
AND s.sql_hash_value = sa.hash_value
AND s.saddr = su.session_addr(+)
AND s.SID = ps.qcsid(+)
AND s.SID = si.SID(+)
AND s.saddr = t.ses_addr(+)
AND s.SID = pss_pr.qcsid(+)
AND s.SID = pss_cg.qcsid(+)
AND s.SID = pss_cpu.qcsid(+)
AND s.SID = ss_cpu.SID(+)
AND s.TYPE != 'BACKGROUND'
AND s.status = 'ACTIVE'
AND program NOT LIKE ('%(C%') --Eliminate Streams Capture
AND program NOT LIKE ('%(A%') --Eliminate Streams Apply
AND program NOT LIKE ('%(P%') --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;