资源占用查询.txt

查找ORACLE系统最耗费系统资源是是什么任务或什么语句

请教，数据库现在非常的慢，我想知道是到底是什么任务（或语句）占用了ORACLE系统的最大资源，用什么方法？ 
用什么方法可以看到到底是什么任务或什么语句占用了ORACLE系统的大部分资源啊？？？？？ 

 你可以查找数据字典 v$sysstat,v$latch v$session 等等来看一看那些占用资源，那些在等待



 check: 
1. v$session_wait, v$system_event, $session_event. 
2. v$sql, check those with most disk gets, buffer gets. etc. 
3. check the system cpu, io.


 查找最耗费系统资源的SQL 
--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 
; 

--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 
;




 select s.sid,s.value "CPU Used" 
from v$sesstat s,v$statname n 
where s.statistic#=n.statistic# and n.name='CPU used by this session' 
and s.value>0 
order by 2 desc; 
能知道哪个sid最消耗资源，前提，timed_statistics=true 
然后从v$sqlarea能找到这个sql在干吗