--查看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博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2124928/，如需转载，请注明出处，否则将追究法律责任。
| 
转播
