一条delete语句的调优(r4笔记第86天)

今天刚上上班,就接到客户的邮件,说生产环境中执行某一条delete sql语句的时间超过了3个小时。最后客户无奈取消了这次数据清理,准备今天在申请时间重做。所以希望我在下午之前能够调优一下sql语句。 我拿到sql语句。是一个简单的delete语句,这个表是一个分区表,表中的数据大约有6亿条,要删除的数据大概有900多万条。

delete event where cycle_code = 25 and cycle_month = 2 and cycle_year = 2015 and customer_id = 5289835; 先来看看执行计划

Plan hash value: 2439880320
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                    |                 |  3238K|   135M|   404K  (1)| 01:20:52 |       |       |
|   1 |  DELETE                             |       EVENT     |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR          |                 |  3238K|   135M|   404K  (1)| 01:20:52 |   241 |   261 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID|       EVENT     |  3238K|   135M|   404K  (1)| 01:20:52 |   241 |   261 |
|*  4 |     INDEX RANGE SCAN                |       EVENT_1UQ |  1370K|       | 40255   (1)| 00:08:04 |   241 |   261 |
-----------------------------------------------------------------------------------------------------------------------

发现走了索引扫描,看起来性能也不会差到哪去啊? 从整体来看,从6亿条记录中删除900多万条数据,走索引扫描感觉感觉确实是不错的选择。 首先查看了表的分区规则和基本的数据分布情况, 分区规则是基于cycle_code,cycle_month,sub_partition_id这三个字段,从查询条件来看,cycle_code,cycle_month刚好就是分区字段。

TABLE_NAME           PARTITION PARTITION_COUNT COLUMN_LIST                    PART_COUNTS SUBPAR_COUNT STATUS
-------------------- --------- --------------- ------------------------------ ----------- ------------ ------
EVENT              RANGE                 721 CYCLE_CODE,CYCLE_MONTH,SUB_PAR TITION_ID           3            0 VALID                        

数据分布的情况如下,根据分区逻辑,数据只可能在这20个分区中。

partition_name  high_value   tablespace_name       num_rows
C25_M2_S1    25, 2, 5        DATAH01              84246910
C25_M2_S2    25, 2, 10       DATAH01               3427570
C25_M2_S3    25, 2, 15       DATAH01               3523431
C25_M2_S4    25, 2, 20       DATAH01               3988140
C25_M2_S5    25, 2, 25       DATAH01               2700687
C25_M2_S6    25, 2, 30       DATAH01               2477792
C25_M2_S7    25, 2, 35       DATAH01               2490349
C25_M2_S8    25, 2, 40       DATAH01              11755212
C25_M2_S9    25, 2, 45       DATAH01               3184953
C25_M2_S10   25, 2, 50       DATAH01               2656802
C25_M2_S11   25, 2, 55       DATAH01               4434668
C25_M2_S12   25, 2, 60       DATAH01               2776079
C25_M2_S13   25, 2, 65       DATAH01               2949885
C25_M2_S14   25, 2, 70       DATAH01               2837790
C25_M2_S15   25, 2, 75       DATAH01               6285172
C25_M2_S16   25, 2, 80       DATAH01               2743439
C25_M2_S17   25, 2, 85       DATAH01               3574228
C25_M2_S18   25, 2, 90       DATAH01               3600820
C25_M2_S19   25, 2, 95       DATAH01               7415434
C25_M2_S20   25, 2, 100      DATAH01               3446285

有了这些信息,发现收获还是不小的,我写了一个脚本,来嵌入customer_id这个字段,来查看每个分区中需要删除的数据情况,结果发现第一个分区有8千多万条数据,查询的时间很长,最后竟然没有数据可以删除,其它的分区测试的时候执行速度都很快。 分区C25_M2_S8中的要删除的数据有9百多万,其它分区都没有匹配的数据,从数据层面,我是没法确定这些分区一定没有可能插入新数据的。 所以分析了上面的情况,我对分区C25_M2_S1做了特殊处理,按照执行计划是走索引扫描的,因为查询条件的范围有点大,还没有匹配的数据,所以我尝试走全表扫描,开启了并行,经过测试,发现速度还是很快的,基本在1分钟左右就能够很快过滤出数据来。 所以从数据层面我提供的语句如下,把最大的分区放在了最后处理。

set linesize 200
set timing on
set time on
alter session force parallel dml parallel 16;
delete event partition(C25_M2_S2)   where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
delete event partition(C25_M2_S3)   where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
delete event partition(C25_M2_S4)   where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
。。。。。。。
delete event partition(C25_M2_S20)  where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;
delete /*+ full(rated_event) parallel(rated_event,16) */ event partition(C25_M2_S1)   where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;

事情到此一般就结束了,开发找到我,我们做了进一步的沟通,她根据我提供的脚本提出了一些问题,她从业务层面来做了确认,说数据只会在C25_M2_S8这个分区上,有了业务确认,调优的语句就更加简化了。

set linesize 200
set timing on
set time on
alter session force parallel dml parallel 16;
delete event partition(C25_M2_S8)   where cycle_code=25 and cycle_month=2 and cycle_year=2015 and customer_id=5289835;
commit;

查看执行计划,合理的走了全表扫描,因为分区中有1千多万的记录,删除900多万的数据,走全表扫描还是情理之中的。

Plan hash value: 1742190108
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |             |  9115K|   382M| 19351   (1)| 00:03:53 |       |       |        |      |            |
|   1 |  PX COORDINATOR       |             |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000    |  9115K|   382M| 19351   (1)| 00:03:53 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    DELETE             |       EVENT |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |             |  9115K|   382M| 19351   (1)| 00:03:53 |   248 |   248 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL|       EVENT |  9115K|   382M| 19351   (1)| 00:03:53 |   248 |   248 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------

通过这个例子,我们可以看到原本索引扫描的执行计划看起来很好,但是执行效率却大打折扣,在分析了分区表的分区规则和数据分布情况之后,发现可以把原本700多个分区简化到20个,加上业务层面的确认,本来20个分区的删除可以简化到有一个特定的分区,性能调优在这个时候就是一个接力棒式的工作。问题经过一步一步的分析和确认,也变得清晰起来。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-03-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏微信公众号:小白课代表

不只是软件,在线也可以免费下载百度文库了。

不管是学生,还是职场员工,下载各种文档几乎是不可避免的,各种XXX.docx,XXX.pptx更是家常便饭,人们最常用的就是百度文库,豆丁文库,道客巴巴这些下载...

44730
来自专栏Ken的杂谈

【系统设置】CentOS 修改机器名

18230
来自专栏前端桃园

知识体系解决迷茫的你

最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

22340
来自专栏FSociety

SQL中GROUP BY用法示例

GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

5.2K20
来自专栏腾讯高校合作

【倒计时7天】2018教育部-腾讯公司产学合作协同育人项目申请即将截止!

16020
来自专栏钱塘大数据

中国互联网协会发布:《2018中国互联网发展报告》

在2018中国互联网大会闭幕论坛上,中国互联网协会正式发布《中国互联网发展报告2018》(以下简称《报告》)。《中国互联网发展报告》是由中国互联网协会与中国互联...

13750
来自专栏怀英的自我修炼

考研英语-1-导学

英二图表作文要重视。总体而言,英语一会比英语二难点。不过就写作而言,英语二会比英语一有难度,毕竟图表作文并不好写。

12110
来自专栏钱塘大数据

理工男图解零维到十维空间,烧脑已过度,受不了啦!

让我们从一个点开始,和我们几何意义上的点一样,它没有大小、没有维度。它只是被想象出来的、作为标志一个位置的点。它什么也没有,空间、时间通通不存在,这就是零维度。

34430
来自专栏haifeiWu与他朋友们的专栏

复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

30140
来自专栏腾讯社交用户体验设计

ISUX Xcube智能一键生成H5

51420

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励