今天刚上上班，就接到客户的邮件，说生产环境中执行某一条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 eve