找出高CPU利用率的SQL：

 

寻找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: ]

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