exhaust_cpu.txt

解决oracle服务器占用大CPU的进程清理

1、用vmstat查到CPU消耗很大，导致idle只有10-20%
2、用top命令查看消耗最大的pid，为了避免出错，不要在此时进行kill操作
3、到sqlplus里进行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 = '11045'))
ORDER BY piece ASC
/


SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),TO_CHAR(
SYSDATE,'MM','NLS_CALENDAR=Gregorian'),TO_CHAR(SYSDATE,'DD','NLS
_CALENDAR=Gregorian')   FROM X$DUAL


4、进行kill工作
5、也可以查看pid和serial#，用oracle命令将该进程kill掉

select pid,serial#,username from v$process where spid='11045';

exec dbms_system.set_sql_trace_in_session(77,62,true);