select s.sql_text,
s.sql_fulltext,
s.sql_id,
round(elapsed_time / 1000000 / (case
when (executions = 0 or nvl(executions, 1) = 1) then 1 else executions end),2) "执行时间'S'",
s.executions "执行次数",
s.optimizer_cost "COST",
s.sorts,
s.module, --连接模式（jdbc thin client：程序）
-- s.locked_total,
s.physical_read_bytes "物理读",
-- s.physical_read_requests "物理读请求",
s.physical_write_requests "物理写",
-- s.physical_write_bytes "物理写请求",
s.rows_processed "返回行数",
s.disk_reads "磁盘读",
s.direct_writes "直接路径写",
s.parsing_schema_name,
s.last_active_time
from gv$sqlarea s
where round(elapsed_time / 1000000 / (case when (executions = 0 or nvl(executions, 1) = 1) then 1 else executions end),2) > 5 --100 0000微秒=1s
--and s.parsing_schema_name = user
and to_char(s.last_load_time, 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD')
and s.command_type in (2, 3, 5, 6, 189)
order by "执行时间'S'" desc;

ITPub博客

查询执行慢的SQL语句