Oracle查找消耗CPU资源Oracle认证考试

文章作者 100test 发表时间 2009:08:07 17:16:52
来源 100Test.Com百考试题网

"tbbnc"> 　　很多时候我们的服务器可能会经历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
　　------------------------------------------------------------
　　0select * from( 0select a.*,rownum rn from (0select 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

　　编辑特别推荐:

　　oracle认证考试费用

　　Oracle的入门心得

　　) 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 0selected.
　　这段代码就是当前正在疯狂消耗CPU的资源。
　　接下来需要进行的工作就是找出这段代码的问题，看是否可以通过优化提高其效率，减少资源消耗。