|
selectsum(pct_bufgets) "percent"
from (selectrank()over (orderby buffer_getsdesc)as rank_bufgets,
to_char(100* ratio_to_report(buffer_gets)over (),'999.99') pct_bufgets
from v$sqlarea)
where rank_bufgets <11;
--查看脏块
select objd,count(*)
from v$bh
where dirty='Y'
groupby objdorderbycount(*);
--查看oracle操作系统进程
select *from v$processwhere backgroundisnull;
--查看操作系统进程对应的sql语句
select sql_textfrom v$sql
where sql_idin
(select sql_idfrom gv$session
where paddrin (select addrfrom gv$processwhere program ='ORACLE.EXE (SHAD)'));
--查看热点对象
--查看每个session占用cpu情况
select ss.sid,se.SERIAL#,se.command,ss.valueCPU ,se.username,se.program
from v$sesstatss, v$session se
where ss.statistic#in
(select statistic#
from v$statname
wherename ='CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
orderbyCPUdesc;
--查看pid对应的sql语句
select s.sid,w.event, w.wait_time, w.seq#, q.sql_text
from v$session_wait w, v$session s, v$process p, v$sqlarea q
where s.paddr=p.addr
--and s.sid=146
and s.SERIAL#=6488
and s.sql_address=q.address;
?
|