set lines 200 feed off
col text for a120
col exec for 9999999
var n number
exec :n := &1
Prompt Resumo de consultas ativas - Leituras Físicas e Lógicas (Absoluto e por Execucao)
SELECT executions exec, round("LFporEx") "LFporEx", sql_id, text
FROM
(
SELECT sql.sql_id, substr(sql.sql_text,1,120) text, sql.executions, sql.disk_reads/decode(sql.executions,0,1,sql.executions) "LFporEx"
FROM v$sqlarea sql
join (SELECT distinct sql_id FROM v$session WHERE status='ACTIVE') s on (s.sql_id = sql.sql_id)
--WHERE sql.executions > 0
order by sql.disk_reads/decode(sql.executions,0,1,sql.executions) desc
)
WHERE rownum <= :n;

SELECT executions exec, round("LFisicas") "LFisicas", sql_id, text
FROM
(
SELECT sql.sql_id, substr(sql.sql_text,1,120) text, sql.executions, sql.disk_reads "LFisicas"
FROM v$sqlarea sql
join (SELECT distinct sql_id FROM v$session WHERE status='ACTIVE') s on (s.sql_id = sql.sql_id)
--WHERE sql.executions > 0
order by sql.disk_reads desc
)
WHERE rownum <= :n;

SELECT executions exec, round("LLporEx") "LLporEx", sql_id, text
FROM
(
SELECT sql.sql_id, substr(sql.sql_text,1,120) text, sql.executions executions, sql.buffer_gets/decode(sql.executions,0,1,sql.executions) "LLporEx"
FROM v$sqlarea sql
join (SELECT distinct sql_id FROM v$session WHERE status='ACTIVE') s on (s.sql_id = sql.sql_id)
--WHERE sql.executions > 0
order by sql.buffer_gets/decode(sql.executions,0,1,sql.executions) desc
)
WHERE rownum <= :n;

SELECT executions exec, round("LLogicas") "LLogicas", sql_id, text
FROM
(
SELECT sql.sql_id, substr(sql.sql_text,1,120) text, sql.executions executions, sql.buffer_gets "LLogicas"
FROM v$sqlarea sql
join (SELECT distinct sql_id FROM v$session WHERE status='ACTIVE') s on (s.sql_id = sql.sql_id)
--WHERE sql.executions > 0
order by sql.buffer_gets desc
)
WHERE rownum <= :n;

set feed off