--查看CPU消耗高的SQL
select a.sid,
       a.serial#,
       spid,
       status,
       substr(a.program,1,40) prog,
       a.machine,
       a.terminal,
       osuser,
       value/60/100 value,
       sq.sql_text
from v$session a,v$process b,v$sesstat c,v$sqlarea sq
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr and a.SQL_ADDRESS = sq.ADDRESS and a.SQL_HASH_VALUE = sq.HASH_VALUE order by value desc;

--查看总消耗时间最多的前10条SQL语句
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.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

--查看CPU消耗时间最多的前10条SQL语句
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;

--查看消耗磁盘读取最多的前10条SQL语句
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;

ITPub博客

oracle查看SQL性能

最新文章