1、SQL ordered by Gets
select *
from (select substr(sql_text, 1, 40) sql,
buffer_gets,
executions,
buffer_gets / executions “Gets/Exec”,
hash_value,
address
from v$sqlarea
where buffer_gets > 0
and executions > 0
order by buffer_gets desc)
where rownum <= 10;

2、SQL ordered by Reads
select *
from (select substr(sql_text, 1, 40) sql,
disk_reads,
executions,
disk_reads / executions “Reads/Exec”,
hash_value,
address
from v$sqlarea
where disk_reads > 0
and executions > 0
order by disk_reads desc)
where rownum <= 10;

3、SQL ordered by Executions
select *
from (select substr(sql_text, 1, 40) sql,
executions,
rows_processed,
rows_processed / executions “Rows/Exec”,
hash_value,
address
from v$sqlarea
where executions > 0
order by executions desc)
where rownum <= 10;

4、SQL ordered by Parse Calls
select *
from (select substr(sql_text, 1, 40) sql,
parse_calls,
executions,
hash_value,
address
from v$sqlarea
where parse_calls > 0
order by parse_calls desc)
where rownum <= 10;

5、Running Time top 10 sql
select *
from (select t.sql_fulltext,
(t.last_active_time –
to_date(t.first_load_time, ‘yyyy – mm – dd hh24 :mi :ss‘)) * 24 * 60,
disk_reads,
buffer_gets,
rows_processed,
t.last_active_time,
t.last_load_time,
t.first_load_time
from v$sqlarea t
order by t.first_load_time desc)
where rownum < 10;