一条执行时间两天半的sql语句简化(r4笔记第62天)
一条执行时间两天半的sql语句简化(r4笔记第62天)
jeanron100
发布于 2018-03-15 16:10:00
发布于 2018-03-15 16:10:00
周末刚过去，今天来到办公室做例行检查，就发现一条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)对应的执行计划如下，可以看到已经起到了立竿见影的效果了，但是其它部分的优化还需要继续考虑。
Plan hash value: 1066869158
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 362 | | 167K (1)| 00:33:34 | | |
| 1 | SORT GROUP BY | | 1 | 362 | | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 362 | | 167K (1)| 00:33:34 | | |
| 3 | NESTED LOOPS | | 1 | 342 | | 167K (1)| 00:33:33 | | |
|* 4 | HASH JOIN | | 4765 | 1503K| | 165K (1)| 00:33:10 | | |
| 5 | MAT_VIEW ACCESS FULL | TABLE_BPM_STEP | 2131 | 59668 | | 14 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | | | | | | | |
| 7 | NESTED LOOPS | | 4765 | 1372K| | 165K (1)| 00:33:10 | | |
| 8 | NESTED LOOPS | | 1636 | 389K| | 165K (1)| 00:33:02 | | |
| 9 | NESTED LOOPS | | 1450 | 318K| | 164K (1)| 00:32:56 | | |
|* 10 | HASH JOIN | | 1412 | 296K| | 164K (1)| 00:32:55 | | |
|* 11 | MAT_VIEW ACCESS FULL | TABLE_BPM_STEP | 107 | 5885 | | 14 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | | | | | | | |
| 13 | NESTED LOOPS | | 20263 | 3166K| | 164K (1)| 00:32:55 | | |
| 14 | NESTED LOOPS | | 20351 | 2166K| | 162K (1)| 00:32:30 | | |
| 15 | NESTED LOOPS | | 20351 | 1927K| | 160K (1)| 00:32:06 | | |
| 16 | NESTED LOOPS | | 20351 | 1808K| | 160K (1)| 00:32:06 | | |
|* 17 | HASH JOIN | | 20351 | 1689K| 2856K| 160K (1)| 00:32:06 | | |
| 18 | NESTED LOOPS | | | | | | | | |
| 19 | NESTED LOOPS | | 41107 | 2368K| | 136K (1)| 00:27:16 | | |
|* 20 | HASH JOIN | | 41107 | 1886K| | 132K (1)| 00:26:26 | | |
|* 21 | TABLE ACCESS FULL | LOGICAL_DATE | 1 | 18 | | 12 (0)| 00:00:01 | | |
| 22 | PARTITION RANGE ALL | | 36M| 1017M| | 131K (1)| 00:26:24 | 1 | 12 |
| 23 | TABLE ACCESS FULL | CL1_TREATMENT_ACTIVITY | 36M| 1017M| | 131K (1)| 00:26:24 | 1 | 12 |
|* 24 | INDEX UNIQUE SCAN | CSM_ACCOUNT_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 25 | TABLE ACCESS BY INDEX ROWID| CSM_ACCOUNT | 1 | 12 | | 1 (0)| 00:00:01 | | |
| 26 | VIEW | index$_join$_002 | 3785K| 93M| | 17216 (1)| 00:03:27 | | |
|* 27 | HASH JOIN | | | | | | | | |
| 28 | PARTITION RANGE ALL | | 3785K| 93M| | 2609 (1)| 00:00:32 | 1 | 12 |
| 29 | INDEX FAST FULL SCAN | CL1_COLL_ENTITY_PK | 3785K| 93M| | 2609 (1)| 00:00:32 | 1 | 12 |
| 30 | PARTITION RANGE ALL | | 3785K| 93M| | 3194 (1)| 00:00:39 | 1 | 12 |
| 31 | INDEX FAST FULL SCAN | CL1_COLL_ENTITY_1IX | 3785K| 93M| | 3194 (1)| 00:00:39 | 1 | 12 |
|* 32 | INDEX UNIQUE SCAN | AR1_ACCOUNT_PK | 1 | 6 | | 1 (0)| 00:00:01 | | |
|* 33 | INDEX RANGE SCAN | AR1_BILLING_ARRANGEMENT_1IX | 1 | 6 | | 1 (0)| 00:00:01 | | |
|* 34 | INDEX RANGE SCAN | CSM_PAY_CHANNEL_1IX | 1 | 12 | | 1 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | TABLE_BPM_STEP_INST_PK | 1 | | | 1 (0)| 00:00:01 | | |
|* 36 | TABLE ACCESS BY INDEX ROWID | TABLE_BPM_STEP_INST | 1 | 51 | | 1 (0)| 00:00:01 | | |
|* 37 | INDEX RANGE SCAN | AR1_ADDRESS_NAME_1IX | 1 | 10 | | 1 (0)| 00:00:01 | | |
| 38 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 19 | | 1 (0)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | SUBSCRIBER_3IX | 1 | | | 1 (0)| 00:00:01 | | |
|* 40 | INDEX RANGE SCAN | TABLE_BPM_STEP_INST_1UQ | 17 | | | 1 (0)| 00:00:01 | | |
|* 41 | TABLE ACCESS BY INDEX ROWID | TABLE_BPM_STEP_INST | 3 | 153 | | 1 (0)| 00:00:01 | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | CHARGE_DISTRIBUTE | 1 | 19 | | 1 (0)| 00:00:01 | | |
|* 43 | INDEX RANGE SCAN | CHARGE_DISTRIBUTE_3IX | 3 | | | 1 (0)| 00:00:01 | | |
| 44 | PARTITION RANGE ALL | | 1401K| 26M| | 81 (0)| 00:00:01 | 1 | 401 |
|* 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语句的方式来达到大幅度的性能提升。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2015-03-02，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读