公众号：杨建荣的学习笔记 · 作者：r4笔记第62天 · 发布：2015-03-02 22:25:37 · 原文链接

周末刚过去，今天来到办公室做例行检查，就发现一条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 113178
ftmvqxfzq1fv0 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)

对应的执行计划如下：
Execution Plan
————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)
————————————————————————————————————————
| 0 | SELECT STATEMENT | | | | | 4432K(100)
| 1 | SORT GROUP BY | | 1 | 372 | |
| 2 | HASH JOIN | | 1 | 372 | | 4432K (3)
| 3 | HASH JOIN | | 1 | 347 | | 4092K (3)
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | 1 | 330 | | 4092K (3)
| 6 | NESTED LOOPS | | 1 | 279 | | 4092K (3)
| 7 | HASH JOIN | | 20202 | 5129K| | 4084K (3)
| 8 | MAT_VIEW ACCESS FULL | TABLE_BPM_STEP | 335 | 27805 | | 16 (0)
| 9 | NESTED LOOPS | | | | |
| 10 | NESTED LOOPS | | 91449 | 15M| | 4084K (3)
| 11 | NESTED LOOPS | | 91865 | 11M| | 4074K (3)
| 12 | NESTED LOOPS | | 81458 | 8511K| | 4050K (3)
| 13 | NESTED LOOPS | | 78938 | 7477K| | 4042K (3)
| 14 | NESTED LOOPS | | 78938 | 6552K| | 4034K (3)
| 15 | HASH JOIN | | 78938 | 6089K| 13M| 4034K (3)
| 16 | NESTED LOOPS | | 166K| 11M| | 184K (1)
| 17 | HASH JOIN | | 166K| 10M| 17M| 184K (1)
| 18 | NESTED LOOPS | | | | |
| 19 | NESTED LOOPS | | 351K| 13M| | 161K (2)
| 20 | PARTITION RANGE ALL | | 351K| 9967K| | 125K (2)
| 21 | TABLE ACCESS FULL | CL1_TREATMENT_ACTIVITY | 351K| 9967K| | 125K (2)
| 22 | TABLE ACCESS FULL | LOGICAL_DATE | 1 | 18 | | 12 (0)
| 23 | INDEX UNIQUE SCAN | CSM_ACCOUNT_PK | 1 | | | 1 (0)
| 24 | TABLE ACCESS BY INDEX ROWID| CSM_ACCOUNT | 1 | 12 | | 1 (0)
| 25 | VIEW | index$_join$_001 | 3562K| 88M| | 16322 (1)
| 26 | HASH JOIN | | | | |
| 27 | PARTITION RANGE ALL | | 3562K| 88M| | 2541 (1)
| 28 | INDEX FAST FULL SCAN | CL1_COLL_ENTITY_PK | 3562K| 88M| | 2541 (1)
| 29 | PARTITION RANGE ALL | | 3562K| 88M| | 3070 (1)
| 30 | INDEX FAST FULL SCAN | CL1_COLL_ENTITY_1IX | 3562K| 88M| | 3070 (1)
| 31 | INDEX UNIQUE SCAN | AR1_ACCOUNT_PK | 1 | 6 | | 1 (0)
| 32 | VIEW | | 3562K| 20M| | 3846K (3)
| 33 | HASH GROUP BY | | 3562K| 135M| 26G| 3846K (3)
| 34 | NESTED LOOPS | | 598M| 22G| | 1651K (4)
| 35 | HASH JOIN | | 13M| 389M| 61M| 233K (1)
| 36 | PARTITION RANGE ALL | | 3562K| 20M| | 2032 (1)
| 37 | INDEX FULL SCAN | CL1_COLL_ENTITY_PK | 3562K| 20M| | 2032 (1)
| 38 | PARTITION RANGE ALL | | 25M| 585M| | 184K (2)
| 39 | TABLE ACCESS FULL | AR1_AGED_TRIAL_BALANCE | 25M| 585M| | 184K (2)
| 40 | INDEX FULL SCAN | LOGICAL_DATE_PK | 44 | 440 | | 1 (0)
| 41 | INDEX RANGE SCAN | AR1_BILLING_ARRANGEMENT_1IX | 1 | 6 | | 1 (0)
| 42 | INDEX RANGE SCAN | CSM_PAY_CHANNEL_1IX | 1 | 12 | | 1 (0)
| 43 | INDEX RANGE SCAN | AR1_ADDRESS_NAME_1IX | 1 | 10 | | 1 (0)
| 44 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 19 | | 1 (0)
| 45 | INDEX RANGE SCAN | SUBSCRIBER_3IX | 1 | | | 1 (0)
| 46 | INDEX UNIQUE SCAN | TABLE_BPM_STEP_INST_PK | 1 | | | 1 (0)
| 47 | TABLE ACCESS BY INDEX ROWID | TABLE_BPM_STEP_INST | 1 | 51 | | 1 (0)
| 48 | TABLE ACCESS BY INDEX ROWID | CHARGE_DISTRIBUTE | 1 | 19 | | 1 (0)
| 49 | INDEX RANGE SCAN | CHARGE_DISTRIBUTE_3IX | 3 | | | 1 (0)
| 50 | INDEX RANGE SCAN | TABLE_BPM_STEP_INST_1UQ | 17 | | | 1 (0)
| 51 | TABLE ACCESS BY INDEX ROWID | TABLE_BPM_STEP_INST | 3 | 153 | | 1 (0)
| 52 | MAT_VIEW ACCESS FULL | TABLE_BPM_STEP | 6698 | 111K| | 16 (0)
| 53 | VIEW | | 25M| 610M| | 340K (1)
| 54 | WINDOW SORT PUSHED RANK | | 25M| 488M| 884M| 340K (1)
| 55 | PARTITION RANGE ALL | | 25M| 488M| | 184K (2)
| 56 | TABLE ACCESS FULL | AR1_AGED_TRIAL_BALANCE | 25M| 488M| | 184K (2)
————————————————————————————————————————
这个语句的主要瓶颈在于AR1_AGED_TRIAL_BALANCE表上，这个表是一个很大的表，数据量都在几十亿级别，走了两个全表扫描，影响可想而知。
首先关注点就集中在了这个大表上，sql语句中是通过两个子查询(ar1_aged_trial_balance和due_detail)来独立引入了这个大表
(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

这个大表的索引字段是account_id，在where条件中和其它的表来关联，输出字段是另外一个表的count()操作。
结合sql语句的情况我分析了一下子查询关联条件：
AND coll.entity_id = ar1_account.account_id
— AND ar1_account.account_id = ar1_aged_trial_balance.account_id

— 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.account_id=due_detail.account_id

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

aged_type = ‘D’
AND group_type = ‘B’
AND status = ‘EFF’
所以从这个层面理解，due_detail这个子查询是有些多余的，因为在这个子查询中进行分析函数过滤了一把，最后需要的account_id所需的过滤条件和ar1_aged_trial_balance还是一致的，显得很多余。
而ar1_aged_trial_balance这个子查询因为没有account_id关联所以还是会走全表扫描，可以结合coll.entity_id做一些更快的操作，即走索引的方式。
可以通过exists来在子查询中进行关联。
修改后的语句如下，标黄的部分是修改的主要部分。
with tmp_logical_date as (SELECT logical_date
FROM logical_date
WHERE logical_date_type = ‘R’
AND expiration_date IS NULL)
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,
— 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,
tmp_logical_date
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 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
)
)
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 null–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’) =tmp_logical_date.logical_date
AND cl1_treatment_activity.entity_id = csm_account.ban)

对应的执行计划如下，可以看到已经起到了立竿见影的效果了，但是其它部分的优化还需要继续考虑。

总体cost 在167K，完整的执行计划可以查看原文。
|* 45 | TABLE ACCESS BY LOCAL INDEX ROWID | AR1_AGED_TRIAL_BALANCE | 1401K| 26M| | 81 (0)| 00:00:01 | 1 | 401 |
|* 46 | INDEX RANGE SCAN | AR1_AGED_TRIAL_BALANCE_2IX | 12 | | | 80 (0)| 00:00:01 | 1 | 401 |
—————————————————————————————————————————————————

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