开发者社区> jeanron100> 正文

并行查询缓慢的问题分析

简介: 今天,数据迁移组的同事问我一个问题,说他们现在需要在迁移库中查看一些数据,但是查看的时候速度很慢,想让我们看看是不是数据库端出了什么问题。因为数据迁移的一些准备工作刻不容缓,所以需要我尽快进行分析和解决。
+关注继续查看
今天,数据迁移组的同事问我一个问题,说他们现在需要在迁移库中查看一些数据,但是查看的时候速度很慢,想让我们看看是不是数据库端出了什么问题。因为数据迁移的一些准备工作刻不容缓,所以需要我尽快进行分析和解决。
拿到问题之后,首先分析系统的负载和资源使用情况,这台数据迁移的服务器使用top命令的结果看起来没有什么异常。
top - 21:31:23 up 101 days, 21:48,  3 users,  load average: 3.87, 3.62, 3.58
Tasks: 693 total,   1 running, 691 sleeping,   0 stopped,   1 zombie
Cpu(s):  3.0%us,  0.6%sy,  0.0%ni, 92.7%id,  3.6%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  264139924k total, 216960164k used, 47179760k free,  9794620k buffers
Swap: 377487328k total,     1828k used, 377485500k free, 139006708k cached
为了进一步验证,使用sar,vmstat都进行了查看,资源空闲率都在90%以上,没有发现什么异常的资源使用。
然后查看系统进程,一般资源利用率较高的一些进程都会通过 top过滤出来,但是简单观察了一下,没有发现大量的进程,从数据库端来看,使用的session也不够多,大概100个左右。
从这个层面来看,似乎可能是客户端的网络延迟导致的问题,在之前碰到过类似的问题,但是我们不能把这种猜测的结果最终反馈给客户。
我们看看同事提出的问题,他们运行的查询是使用了Hint /*+parallel */ 来启用并行查询,但是似乎并行没有生效或者启用,导致他们的查询响应速度很慢,所以从这个角度来看,问题可能出在并行的使用上。怎么定位对应的session和sql_id,同事把使用的并行联系起来呢,
其实还是有一些脚本可以方便我们的查询。
使用如下的脚本,能够迅速的定位到并行的session,以及这些session正在运行的sql_id
sqlplus -s $DB_CONN_STR@$SH_DB_SID set verify off
set line 200
set pages 200
col sess_id format a15
col osuser format a15
col machine format a20
col program format a25
col username format a15
col sql_id format a30      
select pxsess.sid||','||pxsess.serial# sess_id,sess.username,sess.osuser,sess.machine,sess.program,pxsess.qcsid,pxsess.qcserial#,pxsess.degree,pxsess.server#,pxsess.req_degree ,sess.sql_id from v\$px_session pxsess,v\$session sess where pxsess.sid=sess.sid ;      
EOF

别看脚本这么简单,效果还是很明显的。通过结果我们可以清晰的看到现在有一个并行查询,是通过toad来出发的。请求的parallel是64,但是实际得到了50个并行度。查询中使用了几部分并行相关的子查询,目前的情况下,启用了100个并行。
SESS_ID         USERNAME        OSUSER          MACHINE              PROGRAM                        QCSID  QCSERIAL#     DEGREE    SERVER# REQ_DEGREE SQL_ID
--------------- --------------- --------------- -------------------- ------------------------- ---------- ---------- ---------- ---------- ---------- ------

8464,14527      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P084)          3388      44837         50         21         64 0p4usuf3c597z
8749,63529      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P085)          3388      44837         50         22         64 0p4usuf3c597z
8,13919         CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P086)          3388      44837         50         23         64 0p4usuf3c597z
290,39511       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P087)          3388      44837         50         24         64 0p4usuf3c597z
568,26681       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P088)          3388      44837         50         25         64 0p4usuf3c597z
853,7499        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P089)          3388      44837         50         26         64 0p4usuf3c597z
1137,5081       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P090)          3388      44837         50         27         64 0p4usuf3c597z
1420,2709       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P091)          3388      44837         50         28         64 0p4usuf3c597z
1694,8533       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P092)          3388      44837         50         29         64 0p4usuf3c597z
1987,3413       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P093)          3388      44837         50         30         64 0p4usuf3c597z
2268,45371      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P094)          3388      44837         50         31         64 0p4usuf3c597z
2822,44037      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P095)          3388      44837         50         32         64 0p4usuf3c597z
3111,46169      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P096)          3388      44837         50         33         64 0p4usuf3c597z
3391,51889      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P097)          3388      44837         50         34         64 0p4usuf3c597z
3670,48247      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P098)          3388      44837         50         35         64 0p4usuf3c597z
3956,33001      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P099)          3388      44837         50         36         64 0p4usuf3c597z
3388,44837      CNVDBO4         xxxxxxx         TIT_C15-USER  TOAD.exe                        3388                                                          
101 rows selected.
如果想查看更多关于sql语句的细节,可以直接在v$sql里面抓取。
这100个并行是一个很明显的问题,但是在如此配置的数据库实例中也不至于把并行资源都用光了吧,使用show parameter parallel一查看,设置的parallel_server_max是100个,这样问题就很明显了。
所以经过简单沟通,把问题的情况说了一下,就把并行资源临时调高了一些,增加到200个并行。
但是增加后查看并行资源的使用,发现马上并行资源就被耗光了。使用了200个,并行资源真是紧俏啊。而且是同一个session,同一个sql语句。
SESS_ID         USERNAME        OSUSER          MACHINE              PROGRAM                        QCSID  QCSERIAL#     DEGREE    SERVER# REQ_DEGREE SQL_ID
--------------- --------------- --------------- -------------------- ------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------
10,52273        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P174)          3388      44837         50         25         50 0p4usuf3c597z
288,2837        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P175)          3388      44837         50         26         50 0p4usuf3c597z
575,1303        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P176)          3388      44837         50         27         50 0p4usuf3c597z
850,5315        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P177)          3388      44837         50         28         50 0p4usuf3c597z
1136,30395      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P178)          3388      44837         50         29         50 0p4usuf3c597z
1421,56411      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P179)          3388      44837         50         30         50 0p4usuf3c597z
1700,36871      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P180)          3388      44837         50         31         50 0p4usuf3c597z
1977,8767       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P181)          3388      44837         50         32         50 0p4usuf3c597z
2263,12971      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P182)          3388      44837         50         33         50 0p4usuf3c597z
2543,5297       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P183)          3388      44837         50         34         50 0p4usuf3c597z
2827,18709      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P184)          3388      44837         50         35         50 0p4usuf3c597z
3112,3789       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P185)          3388      44837         50         36         50 0p4usuf3c597z
3393,51137      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P186)          3388      44837         50         37         50 0p4usuf3c597z
3668,28875      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P187)          3388      44837         50         38         50 0p4usuf3c597z
3952,25365      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P188)          3388      44837         50         39         50 0p4usuf3c597z
4240,18239      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P189)          3388      44837         50         40         50 0p4usuf3c597z
4519,15177      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P190)          3388      44837         50         41         50 0p4usuf3c597z
4799,7997       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P191)          3388      44837         50         42         50 0p4usuf3c597z
5085,44189      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P192)          3388      44837         50         43         50 0p4usuf3c597z
5362,35669      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P193)          3388      44837         50         44         50 0p4usuf3c597z
5649,11419      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P194)          3388      44837         50         45         50 0p4usuf3c597z
5924,44601      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P195)          3388      44837         50         46         50 0p4usuf3c597z
6215,6805       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P196)          3388      44837         50         47         50 0p4usuf3c597z
6491,27213      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P197)          3388      44837         50         48         50 0p4usuf3c597z
6776,57337      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P198)          3388      44837         50         49         50 0p4usuf3c597z
7055,12061      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P199)          3388      44837         50         50         50 0p4usuf3c597z
3388,44837      CNVDBO4         xxxxxxx         TIT_C15-USER  TOAD.exe                        3388                                             0p4usuf3c597z
201 rows selected.
查看sql语句是下面这样的形式。
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT /*+ FULL(s) PARALLEL (s,32) */ COUNT(*) amount
FROM new_resource s
WHERE resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_
id = 1 AND resource_attr_value = 'Y')
AND resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_id
= 11 AND resource_attr_value = 'O')
AND ((resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_
id = 13 AND resource_attr_value = '02') AND resource_id IN(SELECT resource_value_id FROM new_resourc
e_attributes WHERE resource_attribute_id = 15 AND resource_attr_value = '06'))
OR (resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_id
= 13 AND resource_attr_value = '06') AND resource_id IN(SELECT resource_value_id FROM new_resource_
attributes WHERE resource_attribute_id = 15 AND resource_attr_value = '02')))
AND resource_pool_id NOT IN(1,3)
AND resource_status = 'PORT OUT AG'
先不说并行的使用是否高效,但是一个查询动用200个并行就已经很明显是问题了。因为不是生产环境,问题的严重性还不算高,最后 经过确认是客户的一个开发人员在使用,简单沟通了终止了这个并行查询,问题就解决了。
所以通过这个案例可以看到,并行查询缓慢是由于另外一个意料之外的并行查询导致的问题。并行查询可以提高查询速度 但是使用过当就会消耗大量的资源,同时也会影响别人。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
慢与快
我经常跟我的学员这样说:对于学习做游戏这件事情,大家要放平心态,不用着急,我给大家提供一年时间的服务,所以你大可以慢慢来。如果你的时间充足的话,就制定一个为期较短的计划。如果你工作比较忙,时间不够的话,那么就制定一个比较宽松的计划。最重要的是不要停,不要半途而废,不要就此放下。想一下,如果你能够在一年的时间里学会并掌握一门新技能,那也是相当不错的了。
23 0
定位任意时刻性能问题,持续性能分析实践解析
定位任意时刻性能问题,持续性能分析实践解析
103 0
mysql查询优化实战:查询用时一分半降到三毫秒
项目中的课程预约记录查询功能,线下门店反馈说进入到页面需要等2分钟
66 0
软件测试面试题:假设在测试过程中某些事务的响应时间过长,但分析应用服务、数据库以及网络都属于正常现象,问题可能出现的原因有哪些?
软件测试面试题:假设在测试过程中某些事务的响应时间过长,但分析应用服务、数据库以及网络都属于正常现象,问题可能出现的原因有哪些?
118 0
接口性能优化技巧,干掉慢代码!
接口性能优化技巧,干掉慢代码!
228 0
为什么就查了一行数据,执行那么慢?
今天主要介绍一下查了一行数据,为什么慢到人发慌。剖析一下MySQL的底层运行流程!
73 0
有了它们,你再说开发慢,就该揍了(上)
有了它们,你再说开发慢,就该揍了
61 0
有了它们,你再说开发慢,就该揍了
有了它们,你再说开发慢,就该揍了
74 0
性能分析(7)- 未利用系统缓存导致 I/O 缓慢案例
性能分析(7)- 未利用系统缓存导致 I/O 缓慢案例
233 0
记一次并发引起的问题及排查过程
聚合支付系统(第四方支付),协议支付模块一直有个小问题。 商户调用协议支付接口,该模块会调用下层第三方支付渠道的协议支付服务,如果第三方支付渠道没有同步返回支付结果,则协议支付模块会通过定时任务向第三方支付渠道批量第查询支付结果(每查一笔订单就调一次第三方支付渠道,“批量”相当于并发调用第三方支付渠道)
87 0
系统运行缓慢,CPU 100%,以及 Full GC 次数过多问题的排查思路
处理过线上问题的同学基本上都会遇到系统突然运行缓慢,CPU 100%,以及Full GC次数过多的问题。当然,这些问题的最终导致的直观现象就是系统运行缓慢,并且有大量的报警。本文主要针对系统运行缓慢这一问题,提供该问题的排查思路,从而定位出问题的代码点,进而提供解决该问题的思路。
1007 0
分分钟解决MySQL查询速度慢与性能差
分分钟解决MySQL查询速度慢与性能差 一、什么影响了数据库查询速度 1.1 影响数据库查询速度的四个因素 1.2 风险分析 QPS: QueriesPerSecond意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
2529 0
优化技巧:提前if判断帮助CPU分支预测
分支预测 在stackoverflow上有一个非常有名的问题: 为什么处理有序数组要比非有序数组快,可见分支预测对代码运行效率有非常大的影响。
1138 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
文章
问答
视频
相关电子书
更多
MySQL查询优化实战
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载