Question

怎样查找oracle数据库中最耗时的SQL语句

Answer 1

这个需要sys'权限查看v$session_longops和v$sqlarea。
因为CPU和磁盘读写非常占时间，就看这两个吧：
CPU：
select *
from (select v.sql_id,
v.child_number,

v.sql_text,
v.elapsed_time,

v.cpu_time,
v.disk_reads,

rank() over(order by v.cpu_time desc) elapsed_rank

from v$sql v) a
where elapsed_rank <= 10;
磁盘：
select *
from (select v.sql_id,
v.child_number,

v.sql_text,
v.elapsed_time,

v.cpu_time,
v.disk_reads,

rank() over(order by v.disk_reads desc) elapsed_rank

from v$sql v) a
where elapsed_rank <= 10;

Answer 2

抓AWR报告，然后从报告里面找耗时SQL