2015年Oracle认证考试辅导:Oracle查找消耗CPU资源
发布时间：2010/3/10 18:35:30 来源：城市学习网 编辑：MOON
很多时候我们的服务器可能会经历CPU消耗100%的性能问题。
排除系统的异常，这类问题通常都是因为系统中存在性能低下甚至存在错误的SQL语句， 消耗了大量的CPU所致。
使用top监控数据库进程
top14: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：
SQLSELECT /*+ 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的资源。
接下来需要进行的工作就是找出这段代码的问题，看是否可以通过优化提高其效率，减少资源消耗。
