AIX下 Oracle10G 耗cpu处理

问题现像：oracle主机cpu过高，95%， 导至前端连接掉线或无法连接，时通时断。
快速处理就是重启数据库,然后通过ash 和awr查找问题：

ASH查询
SQL> @?/rdbms/admin/ashrpt.sql

AWR查询：
SQL> @?/rdbms/admin/awrrpt.sql

ADD报告
SQL> @?/rdbms/admin/addmrpt.sql

 

 

实时查看：

1.先topas再c，查看哪些进程耗cpu,找出pid
如查到 10879036 这个pid耗cpu
2.通过下面方法查，系统的pid在oracle里叫spid

1. nmon   t  查看哪些进程耗CPU
2. 根据系统进程号（Oracle下为spid，用以下语句查:

select a.username, a.machine, a.sid, a.serial#, a.status, c.DISK_READS, c.sql_text
from v$session a, v$process b, v$sqlarea c
where b.spid='1573542' and b.addr=a.paddr and a.sql_address=c.address(+);

--查v$process,查看addr 和消耗的pga
select SPID,pid,addr,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from gv$process where spid in (10879036);

--通过spid查sid
select s.sid,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=11534438;

--通过sid查询sql_text
select sql_text from v$session s,v$sqltext q where s.sql_hash_value =q.hash_value and s.sql_address=q.address and s.sid=1584;

看看哪些语句耗cpu

 

--查找最近一分钟内，最消耗CPU的SQL语句
SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
--ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
--ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'ON CPU'
AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
-- ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

--查找最近一分钟内，最消耗CPU的会话：
SELECT SESSION_ID, COUNT(*)
FROM V$ACTIVE_SESSION_HISTORY V
WHERE V.SESSION_STATE = 'ON CPU'
AND V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)
GROUP BY SESSION_ID
ORDER BY COUNT(*) DESC;

--查找最近一分钟内，最消耗I/O的SQL语句：
SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
--ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
--ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'WAITING'
AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
AND ASH.WAIT_CLASS = 'USER I/O'
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
--ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

 

--查找最近一分钟内，最消耗资源的会话：
SELECT ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.USER_ID,
ASH.PROGRAM,
SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 0)) "CPU",
SUM(DECODE(ASH.SESSION_STATE, 'WAITING', 1, 0)) -
SUM(DECODE(ASH.SESSION_STATE,
'WAITING',
DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
0)) "WAITING",
SUM(DECODE(ASH.SESSION_STATE,
'WAITING',
DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
0)) "IO",
SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) "TOTAL"
FROM V$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1));