发表于:2006.02.18 17:24
分类: Oracle
出处：http://xsb.itpub.net/post/419/38835
---------------------------------------------------------------
23/08/2005 17:24 FP 寻找CPU使用过量的session ，找出高CPU利用率的SQL：
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC;
查看该sql语句的执行计划（发现是全表扫）；
分析这张表：analyze table t1 compute statistics for all indexed columns;
再查看该sql语句的执行计划（rowid扫）；
再查看OS的CPU利用率，显著下降。[Ref: http://www.eygle.com/case/how.to.getsql.which.cost.most.CPU.htm ]
SELECT V.SID, V.VALUE, B.USERNAME, B.OSUSER, B.TERMINAL, B.MODULE
FROM V$STATNAME S, V$SESSTAT V, V$SESSION B
WHERE S.NAME = 'CPU used by this session'
AND V.STATISTIC# = S.STATISTIC#
AND V.SID = B.SID
AND V.VALUE > 0
ORDER BY 2 DESC