一条执行时间两天半的sql语句简化

周末刚过去,今天来到办公室做例行检查,就发现一条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了。
Buffer Gets IO Requests Database Time Wait Activity

.

632M

.

97M

.

.

238254s

.

.

100% 






对应的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语句的方式来达到大幅度的性能提升。


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。