oracle性能分析语句

jtbpdffwov

1.cpu负载高的语句
  
select b.sql_text,
a.buffer_gets,
a.executions,
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions),
c.username
from V$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.buffer_gets desc , b.piece
;
select distinct ss from (
select a.sql_text ss,
a.buffer_gets,
a.executions,
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions),
c.username
from V$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
and  to_char(a.LAST_LOAD_TIME,'yyyy-MM-dd hh:mi:ss')  > '2011-01-25 12:00:00'
order by a.disk_reads  desc , b.piece )
select distinct ss from (
select a.sql_text ss
from V$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.buffer_gets desc , b.piece );

2.磁盘IO高的语句
select b.sql_text,
a.disk_reads,
a.executions,
a.disk_reads/decode(a.executions , 0 , 1 , a.executions),
c.username
from v$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address = b.address
order by a.disk_reads desc , b.piece
;