oracle消耗资源,oracle捕捉消耗cpu资源进程

很多时候我们的服务器可能会经历CPU消耗100%的性能问题.

排除系统的异常，这类问题通常都是因为系统中存在性能低下甚至存在错误的SQL语句, 消耗了大量的CPU所致.

使用top监控数据库进程

top - 14:27:12 up 58 days, 13:26,  4 users,  load average: 0.27, 0.35, 0.36

Tasks: 262 total,   1 running, 261 sleeping,   0 stopped,   0 zombie

Cpu(s):  1.5% us,  0.4% sy,  0.0% ni, 98.0% id,  0.1% wa,  0.0% hi,  0.0% si

Mem:  32906904k total, 11103516k used, 21803388k free,   272816k buffers

Swap: 24579408k total,        0k used, 24579408k free,  9181336k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

26117 oracle    15   0 5757m 2.9g 2.9g S   49  9.2  40:20.73 oracle

26115 oracle    16   0 5757m 3.9g 3.9g S    2 12.4  70:31.67 oracle

7760 root      16   0  532m  19m 9436 S    0  0.1  39:13.60 crsd.bin

21172 oracle    16   0 5797m  71m  37m S    0  0.2   4:50.59 oracle

25853 oracle    16   0 5753m 174m 168m S    0  0.5   0:39.75 oracle

13424 oracle    15   0 5751m 403m 400m S    0  1.3   0:04.43 oracle

6901 oracle    16   0 36192 2104 1496 S    0  0.0   0:00.61 sshd

寻找CPU使用过量的session ，找出高CPU利用率的SQL：

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;

Enter value for pid: 26117

old   9: WHERE c.spid = '&pid'))

new   9: WHERE c.spid = '26117'))

SQL_TEXT

-----------------------------------------------------------------------------------------------------

select * from(     select a.*,rownum rn from (select max(a.YWSJ)

YWSJ,max(a.CLLINK_DM) CLLINK_DM,a.WSBH,a.WSDM,a.CUST_ID,a.CUST_

NAME,a.DY_DM,a.DY_MC,a.CUST_TELS,a.CUST_MOBILE,a.YYSJ,a.SEATCODE

,a.SDSJ,a.CBCS,a.THEID,a.THENAME,a.SWJG_DM,a.SWJG_MC,a.LSRYID,a.

LSRYNAME,a.DDSTATE,a.SEND_TYPE,a.SNAME,a.SPOSTNUM,a.STEL,a.SMOBI

LE,a.SADDRESS,a.ISSAVE,a.COMM_TEL,a.LSSJ,a.SL,a.FY,a.SFPSJ,a.FPH

M,a.YJHM,a.MONTH,a.ISBR,a.MSGFLAG,a.LINETEL,a.SJRADDRESS,A.MAILT

YPE,A.YWTYPE,A.GETTYPE,A.NUMFORM. from(SELECT DISTINCT T.*,s.ywsj

, s.cllink_dm ,B.MAILTYPE,B.YWTYPE,B.GETTYPE,B.NUMFORM. FROM etc_

ls_ywxx s,ETC_YWSYWS T ,etc_ems B  WHERE T.WSBH = B.ID  AND b.ID

= s.wsbh AND T.WSBH=s.WSBH  AND T.ISSAVE = '0' AND  T.WSDM = '1

002'  AND  1 = 1  AND(T.SWJG_DM = '111200' OR s.PDJG_DM ='111200

') AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  A

ND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND

1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 =

1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  A

ND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1   AND

1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 = 1  AND  1 =

1  AND  T.LINETEL LIKE '%'||'15010110829'||'%'  AND  1 = 1  AND

T.SDSJ >= '2009-05-07'  AND  T.SDSJ < '2009-08-06'  ) a group

by a.WSBH,a.WSDM,a.CUST_ID,a.CUST_NAME,a.DY_DM,a.DY_MC,a.CUST_TE

LS,a.CUST_MOBILE,a.YYSJ,a.SEATCODE,a.SDSJ,a.CBCS,a.THEID,a.THENA

ME,a.SWJG_DM,a.SWJG_MC,a.LSRYID,a.LSRYNAME,a.DDSTATE,a.SEND_TYPE

,a.SNAME,a.SPOSTNUM,a.STEL,a.SMOBILE,a.SADDRESS,a.ISSAVE,a.COMM_

TEL,a.LSSJ,a.SL,a.FY,a.SFPSJ,a.FPHM,a.YJHM,a.MONTH,a.ISBR,a.MSGF

LAG,a.LINETEL,a.SJRADDRESS,A.MAILTYPE,A.YWTYPE,A.GETTYPE,A.NUMFO

RM order by SDSJ DESC)a     where rownum <=20)     where rn >0

27 rows selected.

这段代码就是当前正在疯狂消耗CPU的资源.

接下来需要进行的工作就是找出这段代码的问题，看是否可以通过优化提高其效率，减少资源消耗