oracle 查看数据库性能,oracle查看SQL性能

--查看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/，如需转载，请注明出处，否则将追究法律责任。