cpu开销很大，查到对应sql

最新推荐文章于 2025-06-13 02:41:15 发布

ltoper

最新推荐文章于 2025-06-13 02:41:15 发布

阅读量934

点赞数

分类专栏：数据库

数据库专栏收录该内容

27 篇文章

订阅专栏

本文探讨了服务器CPU使用率100%的问题，并通过使用top监控发现性能低下SQL语句导致的问题。提供了查找并优化高CPU利用率SQL代码的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很多时候我们的服务器可能会经历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 >027 rows selected.