一条执行时间两天半的sql语句简化(r4笔记第62天)

周末刚过去，今天来到办公室做例行检查，就发现一条sql语句已经执行234841秒(65小时），已经两天多了。查看了一下对应的Undo资源消耗，发现这个语句是最消undo资源的语句，一个sql语句执行这么长时间，同时对于cpu,IO都是极大的消耗。查看了undo中sql语句的占用情况。select *from ( select maxqueryid,round(sum(undoblks )*8/1024) consumed_size_MB from v$undostat group by maxqueryid order by consumed_size_MB desc ) where rownum<50;MAXQUERYID CONSUMED_SIZE_MB------------- ----------------4ad8ypr3nf6vm 113178ftmvqxfzq1fv0 70343通过sql_monitor抓取的执行情况如下：IO Requests发送了近97M次，差不多有751.2G了。

对应的sql语句如下：SELECT trim(TO_CHAR(COUNT(distinct coll.entity_id), '000000000')) FROM cl1_coll_entity coll, table_bpm_step_inst bpm, table_bpm_step, ar1_account, csm_account, csm_pay_channel, (SELECT account_id FROM ar1_aged_trial_balance, logical_date, cl1_coll_entity WHERE aged_type = 'D' AND group_type = 'B' AND status = 'EFF' AND logical_date.logical_date_type = 'R' AND TRUNC(logical_date - due_date) >= 0 --and logical_date.expiration_date is null AND account_id = entity_id GROUP BY account_id) ar1_aged_trial_balance, (SELECT account_id, due_date FROM (SELECT ar1_aged_trial_balance.*, -- FROM (SELECT ar1_aged_trial_balance.account_id,ar1_aged_trial_balance.due_date, ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY due_date) AS RANK FROM ar1_aged_trial_balance WHERE status = 'EFF' AND aged_type = 'D' AND group_type = 'B') WHERE RANK = 1) due_detail, customer, subscriber, ar1_billing_arrangement, ar1_address_name, charge_distribute WHERE coll.entity_id(+) = csm_account.ban AND coll.proc_inst_id = bpm.parent2proc_inst AND bpm.step2step = table_bpm_step.objid AND bpm.status = 30 AND coll.entity_id = ar1_account.account_id AND csm_account.ban = csm_pay_channel.ban AND ar1_account.account_id = ar1_aged_trial_balance.account_id AND csm_account.customer_id = customer.customer_id AND csm_account.customer_id = subscriber.customer_id AND ar1_account.account_id = ar1_billing_arrangement.account_id AND ar1_account.account_id = ar1_address_name.account_id AND ar1_address_name.address_type = 'ACC' AND coll.entity_id = due_detail.account_id AND subscriber.trx_id = charge_distribute.trx_id AND subscriber.subscriber_no = charge_distribute.agreement_no AND charge_distribute.target_pcn = csm_pay_channel.pym_channel_no AND csm_account.ban = csm_pay_channel.ban AND EXISTS (SELECT cl1_treatment_activity.entity_id FROM cl1_treatment_activity, table_bpm_step_inst, table_bpm_step WHERE cl1_treatment_activity.step_id = table_bpm_step_inst.objid AND table_bpm_step_inst.step2step = table_bpm_step.objid AND table_bpm_step.NAME LIKE '%IVR%' AND table_bpm_step_inst.status = 65 AND TO_DATE(TO_CHAR(cl1_treatment_activity.activity_date, 'YYYYMMDD'), 'YYYYMMDD') = (SELECT logical_date FROM logical_date WHERE logical_date_type = 'R' AND expiration_date IS NULL) AND cl1_treatment_activity.entity_id = csm_account.ban)

对应的执行计划如下：| 39 | TABLE ACCESS FULL | AR1_AGED_TRIAL_BALANCE | 25M| 585M| | 184K (2)| 56 | TABLE ACCESS FULL | AR1_AGED_TRIAL_BALANCE | 25M| 488M| | 184K (2) (SELECT account_id FROM ar1_aged_trial_balance, logical_date, cl1_coll_entity WHERE aged_type = 'D' AND group_type = 'B' AND status = 'EFF' AND logical_date.logical_date_type = 'R' AND TRUNC(logical_date - due_date) >= 0 and logical_date.expiration_date is null AND account_id = entity_id GROUP BY account_id) ar1_aged_trial_balance, (SELECT account_id, due_date FROM (SELECT ar1_aged_trial_balance.*, ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY due_date) AS RANK FROM ar1_aged_trial_balance WHERE status = 'EFF' AND aged_type = 'D' AND group_type = 'B') WHERE RANK = 1) due_detail

-- AND coll.entity_id = due_detail.account_id

子查询ar1_aged_trial_balance的account_id字段和ar1_account字段关联，而另外一个子查询due_detail的account_id字段和coll.entity_id关联，结合这个条件 coll.entity_id = ar1_account.account_id

从这个思路来看，因为子查询ar1_aged_trial_balance和due_detail没有其它的关联条件，唯一需要用到的信息就是account_id

aged_type = 'D' AND group_type = 'B' AND status = 'EFF' -- ar1_aged_trial_balance, /* (SELECT account_id, due_date FROM (SELECT ar1_aged_trial_balance.*, -- FROM (SELECT ar1_aged_trial_balance.account_id,ar1_aged_trial_balance.due_date, ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY due_date) AS RANK FROM ar1_aged_trial_balance WHERE status = 'EFF' AND aged_type = 'D' AND group_type = 'B') WHERE RANK = 1) due_detail,*/-- AND ar1_account.account_id = ar1_aged_trial_balance.account_id-- AND coll.entity_id = due_detail.account_id and exists( (SELECT 1 FROM ar1_aged_trial_balance--, logical_date--, cl1_coll_entity WHERE aged_type = 'D' AND group_type = 'B' AND status = 'EFF' -- AND logical_date.logical_date_type = 'R' AND TRUNC(tmp_logical_date.logical_date - due_date) >= 0 --and logical_date.expiration_date is null --added AND account_id = coll.entity_id --GROUP BY account_id )

总体cost 在167K，完整的执行计划可以查看原文。|* 46 | INDEX RANGE SCAN | AR1_AGED_TRIAL_BALANCE_2IX | 12 | | | 80 (0)| 00:00:01 | 1 | 401 |

通过这个例子，我们可以看到，一个看似很复杂的sql语句，如果对症下药，抓住性能瓶颈，就可能通过简化sql语句的方式来达到大幅度的性能提升。