当前位置: 开发笔记 > 数据库 > 正文

数据库服务器CPU突然持续100%后自动下降原因诊断

作者：Hancl | 来源：互联网 | 2018-06-08 11:32

1、CPU接近100%nmon数据8月5日在9：209：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降2、原因分析结果总述2.1持续时间与恢复方式此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复2.2原因分析

1、CPU接近100% nmon数据 8月5日在9：209：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降 2、原因分析结果总述 2.1 持续时间与恢复方式此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复 2.2 原因分析

1、CPU接近100% nmon数据

8月5日在9：20—9：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降

2、原因分析结果总述

2.1 持续时间与恢复方式

此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复

2.2 原因分析总述：

经过分析，原因为：4条SQL语句ORACLE优化器对LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE表的基数数据评估发生了巨大的差错，导致选择了错误的执行计划，消耗大量的CPU资源

2.3 错误执行计划估算数据与正确执行计划估算数据对比

此处为选择一条最严重的SQL语句为例，其它语句原因相同

错误执行计划基数估算值

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)
0	SELECT STATEMENT				315 (100)
1	COUNT STOPKEY
2	VIEW		1	180	315 (2)
3	SORT ORDER BY STOPKEY		1	151	315 (2)
4	HASH UNIQUE		1	151	314 (1)
5	FILTER
6	NESTED LOOPS OUTER		1	151	313 (1)
7	NESTED LOOPS		1	86	35 (0)
8	TABLE ACCESS BY INDEX ROWID	LB_T_XXXJECT_PROVIDER	1	61	34 (0)
9	INDEX RANGE SCAN	IDX_LB_T_XXXJECT_PROVIDER_003	183		3 (0)
10	TABLE ACCESS BY INDEX ROWID	LA_XXCKAGE	1	25	1 (0)
11	INDEX UNIQUE SCAN	PK_LA_XXCKAGE	1		0 (0)
12	VIEW PUSHED PREDICATE	LB_T_XXXVIDER	1	65	278 (1)
13	MERGE JOIN OUTER		1	64	278 (1)
14	TABLE ACCESS BY INDEX ROWID	XXCC_SUPPLIER	1	45	146 (0)
15	INDEX FULL SCAN	PK_XXCC_SUPPLIER	1		145 (0)
16	SORT JOIN		17998	333K	132 (2)
17	VIEW		17998	333K	131 (1)
18	SORT GROUP BY		17998	544K	131 (1)
19	TABLE ACCESS FULL	XXCC_SUPPLIER_CONTACT	30058	909K	130 (0)

正确执行计划基数估算值

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)
0	SELECT STATEMENT				64460 (100)
1	COUNT STOPKEY
2	VIEW		224K	38M	64460 (1)
3	SORT ORDER BY STOPKEY		224K	28M	64460 (1)
4	HASH UNIQUE		224K	28M	58849 (1)
5	FILTER
6	HASH JOIN OUTER		224K	28M	53237 (1)
7	NESTED LOOPS
8	NESTED LOOPS		347	29842	528 (0)
9	TABLE ACCESS BY INDEX ROWID	LB_T_XXXJECT_PROVIDER	347	21167	181 (0)
10	INDEX RANGE SCAN	IDX_PROJECT_PROVIDER_COMB1	182		4 (0)
11	INDEX UNIQUE SCAN	PK_LA_XXCKAGE	1		0 (0)
12	TABLE ACCESS BY INDEX ROWID	LA_XXCKAGE	1	25	1 (0)
13	VIEW	LB_T_XXXVIDER	9125K	409M	52700 (1)
14	HASH JOIN OUTER		9125K	556M	52700 (1)
15	TABLE ACCESS FULL	XXCC_SUPPLIER	26139	1148K	404 (0)
16	VIEW		6283K	113M	52287 (1)
17	SORT GROUP BY		6283K	185M	52287 (1)
18	TABLE ACCESS FULL	XXCC_SUPPLIER_CONTACT	10M	309M	148 (9)
cardinality feedback used for this statement

说明上面统计信息与实际数据存在非常大的差异，是引起执行计划错误的真正原因

2.4 错误执行计划与正确执行计划CPU资源消耗差异巨大对比

此处为选择一条最严重的SQL语句为例，其它语句比例相近

SQL ID: bj75p9188y410

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	3990363694	4,585,425	9	33676	33677
2	6178145	2,838	2	33677	33677
3	2354817963	1,461	1	33677	33677

错误执行计划CPU消耗是最优执行计划CPU消耗的348倍

2.5 问题自动修复原因

ORACLE11G 的新功能cardinality feedback可以在上次运行完成后，得到上一次运行的基数真正结果，智能的调整后面语句运行时的执行计划，通过cardinality feedback功能得到准确基数数据后调整的执行计划，会给出下面提示：

cardinality feedback used for this statement

3、数据库时间与AWR快照对应信息

此在列出时间与AWR快照对应信息的原因为后续分析依赖时间与快照的关系，展示阶段性数据

序号	snap_id	BEGIN_INTERVAL_TIME	END_INTERVAL_TIME
1	33675	05-8月 -13 08.30.41.054	05-8月 -13 09.00.09.786
2	33676	05-8月 -13 09.00.09.786	05-8月 -13 09.30.10.502
3	33677	05-8月 -13 09.30.10.502	05-8月 -13 10.00.26.364
4	33678	05-8月 -13 10.00.26.364	05-8月 -13 10.30.18.791
5	33679	05-8月 -13 10.30.18.791	05-8月 -13 11.00.24.540

4、总体原因具体分析

8月5日9：00—10：00AWR报告分析

SQL ordered by CPU Time

NO	CPU Time (s)	Executions	CPU per Exec (s)	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Text
1	2,927.66	12	243.97	4,589.72	63.79	0.00	bj75p9188y410	*select from ( select distinc...**
2	1,771.52	3,820	0.46	2,660.05	66.60	0.00	gwz243zx18jk0	SELECT * FROM PUB_STRU_TYPE_RE...
3	1,687.68	6	281.28	1,981.37	85.18	0.00	gmfktzfsd6hj3	*select from ( select row_.,...*
4	1,320.77	18	73.38	2,050.40	64.42	0.00	1d44jc4at7xt6	select count(0) from ( select ...
5	870.33	745	1.17	1,288.11	67.57	0.00	4x0jsx8xmrq3c	select count(1) rcount from ( ...
6	856.20	63	13.59	1,096.32	78.10	0.00	1ztsa8nc1arn1	SELECT DISTINCT RFX2.rootId F...
7	752.07	429	1.75	1,145.77	65.64	0.00	9rpn6kmf9vwwh	select p.package_id, p.package...
8	729.02	94	7.76	853.01	85.46	0.00	bq1x40gqtd1r3	SELECT DISTINCT RFX2.rootId F...
9	683.45	12	56.95	1,538.29	44.43	37.32	f760vj05hjpww	SELECT DISTINCT RFX1.rootId F...
10	595.38	2	297.69	632.09	94.19	0.00	fh86uz0ch9z9w	select count(0) from ( select ...

分析：

（1）上述标红色语句共四条，其中第4条和第10条其实为同一条SQL语句

（2）上述四条标红色SQL语句分析为最消耗CPU资源的语句

（3）上述四条标红色SQL语句在出问题前都没有运行，基本都集中在9：00以后开始运行

（4）上述标红色语句的问题有共同特性，都调用了LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE

（5）上述标红色语句都是因为优化器对LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE表的基数数据评估发生了巨大的差错，导致选择了错误的执行计划，消耗大量的CPU资源，导致CPU接近100%

（6）上述标红色语句都是在几次错误选择后有效的利用了ORACLE11g的新特性cardinalityfeedback功能，最终得到准确的基数数据，自行修正了执行计划

5、问题语句逐条剖析

5.1 第一条：SQL_ID：bj75p9188y410

SQL ID: bj75p9188y410

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	3990363694	4,585,425	9	33676	33677
2	6178145	2,838	2	33677	33677
3	2354817963	1,461	1	33677	33677

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：00—10：00之间，共发生9次错误的执行计划

（3）在前面发生9次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在9：30-10：00之间，自动纠正了执行计划

5.2 第二条：SQL_ID：gmfktzfsd6hj3

SQL ID: gmfktzfsd6hj3

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	617277444	2,602,874	6	33677	33678
2	2335536944	2,725	2	33678	33678
3	687995303	1,437	1	33678	33678

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：30—10：30之间，共发生6次错误的执行计划

（3）在前面发生6次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在10：00-10：30之间，自动纠正了执行计划

5.3 第三条：SQL_ID：1d44jc4at7xt6

SQL ID: 1d44jc4at7xt6

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	3265929876	2,029,525	4	33676	33677
2	2949667951	19,116	13	33676	33677
3	1227972422	1,761	1	33676	33677

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：00—10：00之间，共发生4次错误的执行计划，其中

（3）在前面发生4次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在9：30-10：00之间，自动纠正了执行计划

5.4 第四条：SQL_ID：fh86uz0ch9z9w

SQL ID: fh86uz0ch9z9w

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	3265929876	1,247,089	4	33676	33681
2	2949667951	2,624	2	33681	33681
3	1227972422	1,291	1	33681	33681

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：00—11：30之间，共发生4次错误的执行计划，其中两次发生在9：00-9：30间

（3）在前面发生4次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在11：00-11：30之间，自动纠正了执行计划

6、引发执行计划错误原因分析

6.1 表统计信息统计历史

6.1.1 LB_T_XXXJECT_PROVIDER

序号	用户名	表名	分析历史时间
1	BIDPRO	LB_T_XXXJECT_PROVIDER	11-8月 -13 20.06.16.630512
2	BIDPRO	LB_T_XXXJECT_PROVIDER	03-8月 -13 20.22.23.332654
3	BIDPRO	LB_T_XXXJECT_PROVIDER	26-7月 -13 22.18.08.386638

在8月5日开标前，该表已经有10天未统计

6.1.2 LA_XXCKAGE

序号	用户名	表名	分析历史时间
1	BIDPRO	LA_XXCKAGE	15-8月 -13 20.01.28.232128
2	BIDPRO	LA_XXCKAGE	09-8月 -13 20.04.35.224700
3	BIDPRO	LA_XXCKAGE	26-7月 -13 20.08.49.666682

在8月5日开标前，该表已经有10天未统计

6.1.3 XXCC_SUPPLIER

序号	用户名	表名	分析历史时间
1	BIDPRO	XXCC_SUPPLIER	18-8月 -13 20.13.21.031834
2	BIDPRO	XXCC_SUPPLIER	10-8月 -13 20.07.04.740643
3	BIDPRO	XXCC_SUPPLIER	31-7月 -13 22.00.39.107474
4	BIDPRO	XXCC_SUPPLIER	22-7月 -13 22.01.26.170018

在8月5日开标前，该表已经有5天未统计

6.1.4 XXCC_SUPPLIER_CONTACT

序号	用户名	表名	分析历史时间
1	BIDPRO	XXCC_SUPPLIER_CONTACT	17-8月 -13 20.03.30.834585
2	BIDPRO	XXCC_SUPPLIER_CONTACT	09-8月 -13 22.03.30.402420
3	BIDPRO	XXCC_SUPPLIER_CONTACT	26-7月 -13 22.07.06.696581

在8月5日开标前，该表已经有10天未统计

6.2 表数据变化分析

6.2.1 8月5日前最后一次统计时间至8月5日时的block_changes数量

序号	表名	最后一次统计时间	block_changes
1	LB_T_XXXJECT_PROVIDER	26-7月 -13 22.18	158560
2	LA_XXCKAGE	26-7月 -13 20.08	168224
3	XXCC_SUPPLIER	31-7月 -13 22.00	608
4	XXCC_SUPPLIER_CONTACT	26-7月 -13 22.07	576

6.3 错误执行计划预估数据量与真正数据量差异对比

SQL_ID	执行计划对错区分	对表的ROWS评估数
SQL_ID	执行计划对错区分	XXCC_SUPPLIER_CONTACT	XXCC_SUPPLIER	LA_XXCKAGE	LB_T_XXXJECT_PROVIDER
bj75p9188y410	错误执行计划	30058	1	1	1
bj75p9188y410	正确执行计划	10M	26139	1	347
gmfktzfsd6hj3	错误执行计划	30058	1	1	1
gmfktzfsd6hj3	正确执行计划	10M	26139	1	347
1d44jc4at7xt6	错误执行计划	30058	1	1	1
1d44jc4at7xt6	正确执行计划	10M	26139	1	347
fh86uz0ch9z9w	错误执行计划	30058	1	1	1
fh86uz0ch9z9w	正确执行计划	7763K	26139	1	347

7、解决方案建议

建议对上述四条发生执行计划错误的SQL语句，采用SQL_PROFILE对执行计划进行固定，可以避免下次开标时出现同样的问题

推荐阅读

sql
每天收获一点点Hadoop概述

一、Hadoop来历Hadoop的思想来源于Google在做搜索引擎的时候出现一个很大的问题就是这么多网页我如何才能以最快的速度来搜索到，由于这个问题Google发明 ... [详细]

蜡笔小新 2023-12-14 18:58:01
sql
SQL日志收缩及截断方法详解

本文详细介绍了SQL日志收缩的方法，包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时，还介绍了截断日志的原理和注意事项，包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法，可以有效减小逻辑日志的大小，提高数据库的性能。 ... [详细]

蜡笔小新 2023-12-14 18:23:25
sql
adg架构设置及其在企业数据治理中的应用

本文介绍了adg架构设置在企业数据治理中的应用。随着信息技术的发展，企业IT系统的快速发展使得数据成为企业业务增长的新动力，但同时也带来了数据冗余、数据难发现、效率低下、资源消耗等问题。本文讨论了企业面临的几类尖锐问题，并提出了解决方案，包括确保库表结构与系统测试版本一致、避免数据冗余、快速定位问题等。此外，本文还探讨了adg架构在大版本升级、上云服务和微服务治理方面的应用。通过本文的介绍，读者可以了解到adg架构设置的重要性及其在企业数据治理中的应用。 ... [详细]

蜡笔小新 2023-12-14 13:05:22
sql
Hibernate基础映射

在说Hibernate映射前，我们先来了解下对象关系映射ORM。ORM的实现思想就是将关系数据库中表的数据映射成对象，以对象的形式展现。这样开发人员就可以把对数据库的操作转化为对 ... [详细]

蜡笔小新 2023-12-14 10:57:47
sql
postman测试登录后的接口_使用postman进行接口测试的方法(测试用户管理模块)

本文介绍了使用postman进行接口测试的方法，以测试用户管理模块为例。首先需要下载并安装postman，然后创建基本的请求并填写用户名密码进行登录测试。接下来可以进行用户查询和新增的测试。在新增时，可以进行异常测试，包括用户名超长和输入特殊字符的情况。通过测试发现后台没有对参数长度和特殊字符进行检查和过滤。 ... [详细]

蜡笔小新 2023-12-14 10:29:45
sql
MysqlDump_mysqldump全库备份相关知识详解

本文详细介绍了MysqlDump和mysqldump进行全库备份的相关知识，包括备份命令的使用方法、my.cnf配置文件的设置、binlog日志的位置指定、增量恢复的方式以及适用于innodb引擎和myisam引擎的备份方法。对于需要进行数据库备份的用户来说，本文提供了一些有价值的参考内容。 ... [详细]

蜡笔小新 2023-12-14 10:03:27
join
使用 Ubuntu 中的 Python 获取浏览器历史记录

使用Ubuntu中的Python获取浏览器历史记录原文: ... [详细]

蜡笔小新 2023-12-14 08:57:59
sql
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
sql
Oracle中tnsnames.ora的作用和配置方法

本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取，用于解析LOCAL_LISTENER，并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例，并展示了listener.ora文件的内容。 ... [详细]

蜡笔小新 2023-12-14 07:44:06
sql
Spring特性实现接口多类的动态调用详解

本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]

蜡笔小新 2023-12-14 03:24:19
sql
Java String与StringBuffer的区别及其应用场景

本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]

蜡笔小新 2023-12-13 19:21:06
sql
Oracle分析函数first_value()和last_value()的用法及原理

本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]

蜡笔小新 2023-12-13 19:07:23
sql
MyBatis错题分析解析及注意事项

本文对MyBatis的错题进行了分析和解析，同时介绍了使用MyBatis时需要注意的一些事项，如resultMap的使用、SqlSession和SqlSessionFactory的获取方式、动态SQL中的else元素和when元素的使用、resource属性和url属性的配置方式、typeAliases的使用方法等。同时还指出了在属性名与查询字段名不一致时需要使用resultMap进行结果映射，而不能使用resultType。 ... [详细]

蜡笔小新 2023-12-13 18:40:17
sql
ASP.NET Tips: 获取插入记录的ID的方法详解

本文详细介绍了在ASP.NET中获取插入记录的ID的几种方法，包括使用SCOPE_IDENTITY()和IDENT_CURRENT()函数，以及通过ExecuteReader方法执行SQL语句获取ID的步骤。同时，还提供了使用这些方法的示例代码和注意事项。对于需要获取表中最后一个插入操作所产生的ID或马上使用刚插入的新记录ID的开发者来说，本文提供了一些有用的技巧和建议。 ... [详细]

蜡笔小新 2023-12-13 17:03:18
sql
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新 2023-12-13 15:15:30

Hancl

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章