CCS数据库突然卡顿问题解决-徐铭

问题描述：

2022年10月11日星期二

阿里云 云监控 发出告警

【云监控】云服务器ECS发生告警

* 用户名: 四川铁骑力士实业有限公司
* 用户ID: 1653417493215928
* 地域: 华北2(北京)
* 实例名称: [CCS-ORACLE-PRD1](http://cloudmonitornext.console.aliyun.com/productMonitorChart/category=ecs&region=cn-beijing&dimension=%7B%22instanceId%22%3A%22i-2ze3u71xuet4pe9rcglv%22%7D)
* 实例ID: i-2ze3u71xuet4pe9rcglv
* 报警规则: [cpu使用率报警](http://cloudmonitornext.console.aliyun.com/app-group/196930104/alarmRule?ruleName=cpu%E4%BD%BF%E7%94%A8%E7%8E%87%E6%8A%A5%E8%AD%A6" \t "_blank)
* 报警时间: 2022-10-11 08:30:50
* 应用分组: [CCS正式服务器(196930104)](https://cloudmonitornext.console.aliyun.com/app-group/196930104/overview)
* 内网IP: 10.7.8.222
* IP组: 10.7.8.222
* 监控指标: (Agent)cpu.user
* 报警条件: 平均值>=95%
* 当前值: 99.43%
* 持续时间: 8小时38分钟

进入阿里云控制台 /监控

![](/media/202210//1665479302.5390887.png)

告知CCS开发和产品经理，获知目前系统稍显卡顿但能正常使用，且并未执行特定定时任务

生成awr报告，发现有异常sql

![](/media/202210//1665479302.5699136.png)

立即将SQL发送给开发人员，开发人员收到异常sql后，进行了修改并重新发版

发版后发现CPU并未降下来，

再次生成awr报告，发现该sql依然在执行，

![](/media/202210//1665479302.597665.png)

通过SQL\_ID找到该会话和详细信息，发现数据库正在执行该语句的会话有157个

select a.sid,

a.username,

a.machine,

a.module,

a.event,

a.sql\_id,

round(decode(c.executions,0,buffer\_gets,buffer\_gets/executions)) exec\_gets,

a.ROW\_WAIT\_OBJ# object\_id,

a.BLOCKING\_INSTANCE||'\_'||a.blocking\_session holder,

a.FINAL\_BLOCKING\_INSTANCE||'\_'||a.FINAL\_BLOCKING\_SESSION final,

to\_char(LAST\_CALL\_ET) seconds,

a.p1 || '\_' || a.p2 || '\_' || a.p3 param,

b.spid,

trunc(b.PGA\_USED\_MEM / 1024 / 1024,2) as PGA\_USE,

c.sql\_text

from v$session a, v$process b,v$sql c

where a.paddr = b.addr(+)

and a.status = 'ACTIVE'

and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and

a.wait\_class = 'Idle')

and a.sql\_id=c.sql\_id(+)

and a.sql\_child\_number=c.CHILD\_NUMBER(+)

and c.SQL\_ID='96h3qakng330c'

order by a.sql\_id, a.machine;

![](/media/202210//1665479302.6290874.png)

生成kill语句：

select 'alter system kill session '''||a.sid||','||a.SERIAL#||''';'

from v$session a, v$process b,v$sql c

where a.paddr = b.addr(+)

and a.status = 'ACTIVE'

and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and

a.wait\_class = 'Idle')

and a.sql\_id=c.sql\_id(+)

and a.sql\_child\_number=c.CHILD\_NUMBER(+)

and c.SQL\_ID='96h3qakng330c'

order by a.sql\_id, a.machine;

执行kill语句：

alter system kill session '4256,18793';

alter system kill session '2188,6798';

alter system kill session '4614,8850';

alter system kill session '4740,18284';

alter system kill session '7391,49704';

alter system kill session '377,63978';

alter system kill session '498,34854';

alter system kill session '5584,46460';

alter system kill session '868,12005';

alter system kill session '7147,1599';

alter system kill session '260,52729';

alter system kill session '4851,31667';

alter system kill session '129,21345';

alter system kill session '2795,17632';

alter system kill session '3513,6452';

alter system kill session '736,11967';

alter system kill session '983,53481';

alter system kill session '2430,43773';

alter system kill session '7386,60931';

alter system kill session '739,60701';

alter system kill session '1713,65025';

alter system kill session '2431,52944';

alter system kill session '2544,35772';

alter system kill session '252,2864';

alter system kill session '742,30399';

alter system kill session '2318,18559';

alter system kill session '3284,61467';

alter system kill session '5094,3366';

alter system kill session '6675,44658';

alter system kill session '2068,36120';

alter system kill session '2434,64405';

alter system kill session '2673,32142';

alter system kill session '4726,55702';

alter system kill session '5209,794';

alter system kill session '373,64483';

alter system kill session '607,55697';

alter system kill session '1831,54296';

alter system kill session '2791,50410';

alter system kill session '3407,2225';

alter system kill session '5817,6936';

alter system kill session '503,12671';

alter system kill session '744,48510';

alter system kill session '851,35793';

alter system kill session '1215,38639';

alter system kill session '1462,6393';

alter system kill session '1833,28488';

alter system kill session '5828,51582';

alter system kill session '2194,14989';

alter system kill session '3523,58035';

alter system kill session '4127,21777';

alter system kill session '4730,52652';

alter system kill session '5949,2717';

alter system kill session '6421,57009';

alter system kill session '6674,22946';

alter system kill session '1334,44771';

alter system kill session '3277,27956';

alter system kill session '6544,45752';

alter system kill session '6905,41691';

alter system kill session '7637,15338';

alter system kill session '378,35838';

alter system kill session '611,55140';

alter system kill session '984,28308';

alter system kill session '1216,63525';

alter system kill session '1347,32579';

alter system kill session '1708,53854';

alter system kill session '2310,8505';

alter system kill session '2558,40990';

alter system kill session '2785,20044';

alter system kill session '3397,41787';

alter system kill session '3889,41494';

alter system kill session '5208,19985';

alter system kill session '5452,2868';

alter system kill session '5831,10716';

alter system kill session '6053,26065';

alter system kill session '6178,58073';

alter system kill session '6423,38438';

alter system kill session '6790,24851';

alter system kill session '7635,8072';

alter system kill session '137,15960';

alter system kill session '1108,10953';

alter system kill session '1352,2854';

alter system kill session '2062,52540';

alter system kill session '3157,37857';

alter system kill session '3634,9427';

alter system kill session '4011,40341';

alter system kill session '5571,46121';

alter system kill session '6300,22992';

alter system kill session '6784,57801';

alter system kill session '12,38608';

alter system kill session '2439,8514';

alter system kill session '973,62865';

alter system kill session '2786,33107';

alter system kill session '850,53640';

alter system kill session '3878,7900';

alter system kill session '855,30997';

alter system kill session '1213,50647';

alter system kill session '4981,44157';

alter system kill session '6904,13299';

alter system kill session '741,47714';

alter system kill session '7263,18261';

alter system kill session '3879,16392';

alter system kill session '6429,34935';

alter system kill session '6792,18878';

alter system kill session '1700,40584';

alter system kill session '1828,64336';

alter system kill session '4604,18199';

alter system kill session '5703,35662';

alter system kill session '6426,31334';

alter system kill session '7388,60773';

alter system kill session '4368,47371';

alter system kill session '4844,34891';

alter system kill session '5334,2959';

alter system kill session '1104,52713';

alter system kill session '2665,61338';

alter system kill session '3405,27385';

alter system kill session '4117,35372';

alter system kill session '6308,5548';

alter system kill session '139,44309';

alter system kill session '3151,61225';

alter system kill session '4008,48211';

alter system kill session '4360,38873';

alter system kill session '4610,46248';

alter system kill session '4853,4876';

alter system kill session '5221,54757';

alter system kill session '6545,59050';

alter system kill session '16,43307';

alter system kill session '1467,50569';

alter system kill session '1591,29819';

alter system kill session '2308,48189';

alter system kill session '5813,54632';

alter system kill session '977,10050';

alter system kill session '3283,19404';

alter system kill session '4245,53530';

alter system kill session '5459,52615';

alter system kill session '6302,2868';

alter system kill session '7158,63561';

alter system kill session '130,40355';

alter system kill session '4254,34260';

alter system kill session '6188,22816';

alter system kill session '6909,44136';

alter system kill session '4251,49579';

alter system kill session '5217,20306';

alter system kill session '7156,49646';

alter system kill session '859,4810';

alter system kill session '4252,62062';

alter system kill session '4357,22908';

alter system kill session '4607,60833';

alter system kill session '6052,29716';

alter system kill session '250,18371';

alter system kill session '864,36898';

alter system kill session '3644,52502';

alter system kill session '3996,6467';

alter system kill session '6550,15915';

alter system kill session '1458,56433';

alter system kill session '3409,56298';

alter system kill session '4376,25924';

执行完KILL语句后，cpu恢复正常，卡顿问题解决。

![](/media/202210//1665479302.707464.png)