执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。
sql语句比较长,是由几个Union连接起来的子查询。
xxxxx
UNION
  SELECT /*+ leading (ar1_creditid_tab ar1_unapplied_credit) use_nl (ar1_creditid_tab ar1_unapplied_credit) */
           UNIQUE
           0,
           MAX (uc.credit_id) credit_id,
           0,
           0,
           0,
           SUM (uc.unapplied_amount) allocated_amount,
           TO_DATE ('') due_date,
           'Unapplied',
           '0',
           transaction_id
    FROM   ar1_unapplied_credit uc,
           (SELECT   COLUMN_VALUE AS credit_id
              FROM   table(SELECT   CAST (:5 AS ar1_numberarray_tp) credit_id
                             FROM   DUAL)) ar1_creditid_tab
   WHERE       uc.reversal_trans_id IS NULL
           AND uc.credit_id = ar1_creditid_tab.credit_id
           AND uc.partition_id = NVL (:6, 0)
           AND uc.credit_type LIKE :7
GROUP BY   uc.transaction_id

执行计划如下所示,可以看到资源消耗还是很高的。
Plan hash value: 3920442503


-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                            |     2 |   184 |   368K (12)| 01:13:48 |       |       |
|   1 |  SORT UNIQUE                               |                            |     2 |   184 |   368K (12)| 01:13:48 |       |       |
|   2 |   UNION-ALL                                |                            |       |       |            |          |       |       |
|   3 |    HASH GROUP BY                           |                            |     1 |   145 |   325K  (1)| 01:05:04 |       |       |
|   4 |     NESTED LOOPS                           |                            |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                          |                            |     1 |   145 |   325K  (1)| 01:05:04 |       |       |
|   6 |       NESTED LOOPS                         |                            |     1 |   130 |   325K  (1)| 01:05:03 |       |       |
|   7 |        NESTED LOOPS                        |                            |     1 |    80 |   325K  (1)| 01:05:03 |       |       |
|   8 |         NESTED LOOPS                       |                            |   606 | 27876 |   325K  (1)| 01:05:03 |       |       |
|   9 |          VIEW                              |                            |  8168 |   103K|    19   (0)| 00:00:01 |       |       |
|  10 |           COLLECTION ITERATOR PICKLER FETCH|                            |  8168 | 16336 |    19   (0)| 00:00:01 |       |       |
|  11 |            FAST DUAL                       |                            |     1 |       |     2   (0)| 00:00:01 |       |       |
|  12 |          PARTITION RANGE MULTI-COLUMN      |                            |     1 |    33 |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 13 |           TABLE ACCESS BY LOCAL INDEX ROWID| AR1_CREDIT_DEBIT_LINK      |     1 |    33 |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 14 |            INDEX RANGE SCAN                | AR1_CREDIT_DEBIT_LINK_1IX  |     1 |       |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 15 |         TABLE ACCESS BY GLOBAL INDEX ROWID | AR1_CHARGE_GROUP           |     1 |    34 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 16 |          INDEX UNIQUE SCAN                 | AR1_CHARGE_GROUP_PK        |     1 |       |     1   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS BY GLOBAL INDEX ROWID  | AR1_INVOICE                |     1 |    50 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 18 |         INDEX UNIQUE SCAN                  | AR1_INVOICE_PK             |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 19 |       INDEX UNIQUE SCAN                    | AR1_BILLING_ARRANGEMENT_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
|  20 |      TABLE ACCESS BY INDEX ROWID           | AR1_BILLING_ARRANGEMENT    |     1 |    15 |     1   (0)| 00:00:01 |       |       |
|  21 |    HASH GROUP BY                           |                            |     1 |    39 | 43675   (1)| 00:08:45 |       |       |
|* 22 |     HASH JOIN                              |                            |     1 |    39 | 43673   (1)| 00:08:45 |       |       |
|  23 |      VIEW                                  |                            |  8168 |   103K|    19   (0)| 00:00:01 |       |       |
|  24 |       COLLECTION ITERATOR PICKLER FETCH    |                            |  8168 | 16336 |    19   (0)| 00:00:01 |       |       |
|  25 |        FAST DUAL                           |                            |     1 |       |     2   (0)| 00:00:01 |       |       |
|  26 |      PARTITION RANGE MULTI-COLUMN          |                            |  3191 | 82966 | 43654   (1)| 00:08:44 |KEY(MC)|KEY(MC)|
|* 27 |       TABLE ACCESS FULL                    | AR1_UNAPPLIED_CREDIT       |  3191 | 82966 | 43654   (1)| 00:08:44 |KEY(MC)|KEY(MC)|
-----------------------------------------------------------------------------------------------------------------------------------------
而性能瓶颈就在于一个全表扫描。

对于这条语句来说,从执行计划来看,在第24行出现了一个操作是COLLECTION ITERATOR PICKLER FETCH,相对比较陌生,查看了下,是对一个集合对象中的成员进行迭代取值,而这种操作在OTN中查看,被有些人评价为很糟糕的一种实现。
THE ABSOLUTELY WORSE THING (other than an ORA-00600 or ORA-3113) that you can see. 参见https://community.oracle.com/thread/1009301?tstart=0
哲学中说存在即合理,肯定是在特定的场景中使用才有一定的意义,主要在xml type的场景中会有所应用。这个场景肯定是不相关的。
我们把问题进行简化,即排除其它的Union 子查询过滤,定位到其中的一个子查询,因为只有这个子查询使用到了AR1_UNAPPLIED_CREDIT 这个表
我们来看看这个子查询的执行计划情况。
SELECT /*+ leading (ar1_creditid_tab ar1_unapplied_credit) use_nl (ar1_creditid_tab ar1_unapplied_credit) */
           UNIQUE
           0,
           MAX (uc.credit_id) credit_id,
           0,
           0,
           0,
           SUM (uc.unapplied_amount) allocated_amount,
           TO_DATE ('') due_date,
           'Unapplied',
           '0',
           transaction_id
    FROM   ar1_unapplied_credit uc,
           (SELECT   COLUMN_VALUE AS credit_id
              FROM   table(SELECT   CAST (:5 AS ar1_numberarray_tp) credit_id
                             FROM   DUAL)) ar1_creditid_tab
   WHERE       uc.reversal_trans_id IS NULL
           AND uc.credit_id = ar1_creditid_tab.credit_id
           AND uc.partition_id = NVL (:6, 0)
           AND uc.credit_type LIKE :7
GROUP BY   uc.transaction_id

执行计划如下,可见访问路径能够复现。
Plan hash value: 981834188
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |     1 |    39 | 43674   (1)| 00:08:45 |       |       |
|   1 |  HASH GROUP BY                       |                      |     1 |    39 | 43674   (1)| 00:08:45 |       |       |
|*  2 |   HASH JOIN                          |                      |     1 |    39 | 43673   (1)| 00:08:45 |       |       |
|   3 |    VIEW                              |                      |  8168 |   103K|    19   (0)| 00:00:01 |       |       |
|   4 |     COLLECTION ITERATOR PICKLER FETCH|                      |  8168 | 16336 |    19   (0)| 00:00:01 |       |       |
|   5 |      FAST DUAL                       |                      |     1 |       |     2   (0)| 00:00:01 |       |       |
|   6 |    PARTITION RANGE MULTI-COLUMN      |                      |  3191 | 82966 | 43654   (1)| 00:08:44 |KEY(MC)|KEY(MC)|
|*  7 |     TABLE ACCESS FULL                | AR1_UNAPPLIED_CREDIT |  3191 | 82966 | 43654   (1)| 00:08:44 |KEY(MC)|KEY(MC)|
-----------------------------------------------------------------------------------------------------------------------------

细看这条sql语句,其中有一个子查询有些陌生,使用到了嵌套表。
     (SELECT   COLUMN_VALUE AS credit_id
              FROM   table(SELECT   CAST (:5 AS ar1_numberarray_tp) credit_id
                             FROM   DUAL)) ar1_creditid_tab
对于这方面,自己也想开发讨教了下。大概知道了原委。
首先定义的type是number类型。
SQL> desc ar1_numberarray_tp
 ar1_numberarray_tp TABLE OF NUMBER
然后可以嵌入多个值,比如我们类似向数组传入100,200,用sql语句就是下面的形式,得到的结果还是type
SQL> SELECT   CAST (ar1_numberarray_tp(100,200) AS ar1_numberarray_tp) credit_id
                                 FROM   DUAL;
AR1_NUMBERARRAY_TP(100, 200)
这个时候结合起来,就得到了一个结果集。
SQL> SELECT   COLUMN_VALUE AS credit_id
              FROM   table(SELECT   CAST (ar1_numberarray_tp(100,200) AS ar1_numberarray_tp) credit_id
                                 FROM   DUAL);
       100
       200

明白了这点,就能基本定位问题了,看来这条sql语句功能还是传入对应的id,做了一个类似的行列转换
这个时候如果再能够进行简化。

SELECT   COLUMN_VALUE AS credit_id
              FROM   table(SELECT   CAST (ar1_numberarray_tp(100,200) AS ar1_numberarray_tp) credit_id
                                 FROM   DUAL);
简化为:

  (SELECT   :1 as credit_id from dual )
性能如何呢?
看看执行计划,可以看到资源消耗极低。比预想中要好得多。
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                            |     2 |   158 |    60  (25)| 00:00:01 |       |       |
|   1 |  SORT UNIQUE                               |                            |     2 |   158 |    60  (25)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                |                            |       |       |            |          |       |       |
|   3 |    HASH GROUP BY                           |                            |     1 |   132 |    47   (5)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                           |                            |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                          |                            |     1 |   132 |    45   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                         |                            |     1 |   117 |    44   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                        |                            |     1 |    67 |    43   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS                       |                            |     1 |    33 |    42   (0)| 00:00:01 |       |       |
|   9 |          FAST DUAL                         |                            |     1 |       |     2   (0)| 00:00:01 |       |       |
|  10 |          PARTITION RANGE MULTI-COLUMN      |                            |     1 |    33 |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 11 |           TABLE ACCESS BY LOCAL INDEX ROWID| AR1_CREDIT_DEBIT_LINK      |     1 |    33 |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 12 |            INDEX RANGE SCAN                | AR1_CREDIT_DEBIT_LINK_1IX  |     1 |       |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 13 |         TABLE ACCESS BY GLOBAL INDEX ROWID | AR1_CHARGE_GROUP           |     1 |    34 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 14 |          INDEX UNIQUE SCAN                 | AR1_CHARGE_GROUP_PK        |     1 |       |     1   (0)| 00:00:01 |       |       |
|  15 |        TABLE ACCESS BY GLOBAL INDEX ROWID  | AR1_INVOICE                |     1 |    50 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 16 |         INDEX UNIQUE SCAN                  | AR1_INVOICE_PK             |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 17 |       INDEX UNIQUE SCAN                    | AR1_BILLING_ARRANGEMENT_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
|  18 |      TABLE ACCESS BY INDEX ROWID           | AR1_BILLING_ARRANGEMENT    |     1 |    15 |     1   (0)| 00:00:01 |       |       |
|  19 |    HASH GROUP BY                           |                            |     1 |    26 |    12  (17)| 00:00:01 |       |       |
|  20 |     NESTED LOOPS                           |                            |     1 |    26 |    10   (0)| 00:00:01 |       |       |
|  21 |      FAST DUAL                             |                            |     1 |       |     2   (0)| 00:00:01 |       |       |
|  22 |      PARTITION RANGE MULTI-COLUMN          |                            |     1 |    26 |     8   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 23 |       TABLE ACCESS BY LOCAL INDEX ROWID    | AR1_UNAPPLIED_CREDIT       |     1 |    26 |     8   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 24 |        INDEX RANGE SCAN                    | AR1_UNAPPLIED_CREDIT_1IX   |     1 |       |     8   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
-----------------------------------------------------------------------------------------------------------------------------------------

和开发进一步沟通,得到的反馈是可以从业务上进行简化和改造。
可以把原来的
SELECT   COLUMN_VALUE AS credit_id
              FROM   table(SELECT   CAST (ar1_numberarray_tp(100,200) AS ar1_numberarray_tp) credit_id
                                 FROM   DUAL);
改进为:
(select CREDIT_ID from ar1_payment WHERE ACCOUNT_ID = :1)

有了这些基础保证,再来看看整个sql语句的执行计划。
Plan hash value: 416684901
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                            |    11 |   524 |   254  (49)| 00:00:04 |       |       |
|   1 |  SORT UNIQUE                               |                            |    11 |   524 |   254  (49)| 00:00:04 |       |       |
|   2 |   UNION-ALL                                |                            |       |       |            |          |       |       |
|   3 |    HASH GROUP BY                           |                            |     1 |   144 |   133   (2)| 00:00:02 |       |       |
|   4 |     NESTED LOOPS                           |                            |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                          |                            |     1 |   144 |   131   (0)| 00:00:02 |       |       |
|   6 |       NESTED LOOPS                         |                            |     1 |   129 |   130   (0)| 00:00:02 |       |       |
|   7 |        NESTED LOOPS                        |                            |     1 |    79 |   129   (0)| 00:00:02 |       |       |
|   8 |         NESTED LOOPS                       |                            |     3 |   135 |   128   (0)| 00:00:02 |       |       |
|   9 |          PARTITION RANGE ALL               |                            |     3 |    36 |     9   (0)| 00:00:01 |     1 |    41 |
|  10 |           TABLE ACCESS BY LOCAL INDEX ROWID| AR1_CUSTOMER_CREDIT        |     3 |    36 |     9   (0)| 00:00:01 |     1 |    41 |
|* 11 |            INDEX RANGE SCAN                | AR1_CUSTOMER_CREDIT_3IX    |     3 |       |     8   (0)| 00:00:01 |     1 |    41 |
|  12 |          PARTITION RANGE MULTI-COLUMN      |                            |     1 |    33 |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 13 |           TABLE ACCESS BY LOCAL INDEX ROWID| AR1_CREDIT_DEBIT_LINK      |     1 |    33 |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 14 |            INDEX RANGE SCAN                | AR1_CREDIT_DEBIT_LINK_1IX  |     1 |       |    40   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 15 |         TABLE ACCESS BY GLOBAL INDEX ROWID | AR1_CHARGE_GROUP           |     1 |    34 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 16 |          INDEX UNIQUE SCAN                 | AR1_CHARGE_GROUP_PK        |     1 |       |     1   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS BY GLOBAL INDEX ROWID  | AR1_INVOICE                |     1 |    50 |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 18 |         INDEX UNIQUE SCAN                  | AR1_INVOICE_PK             |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 19 |       INDEX UNIQUE SCAN                    | AR1_BILLING_ARRANGEMENT_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
|  20 |      TABLE ACCESS BY INDEX ROWID           | AR1_BILLING_ARRANGEMENT    |     1 |    15 |     1   (0)| 00:00:01 |       |       |
|  21 |    HASH GROUP BY                           |                            |    10 |   380 |   121   (2)| 00:00:02 |       |       |
|  22 |     NESTED LOOPS                           |                            |       |       |            |          |       |       |
|  23 |      NESTED LOOPS                          |                            |    10 |   380 |   119   (0)| 00:00:02 |       |       |
|  24 |       PARTITION RANGE ALL                  |                            |    10 |   120 |    41   (0)| 00:00:01 |     1 |   201 |
|  25 |        TABLE ACCESS BY LOCAL INDEX ROWID   | AR1_PAYMENT                |    10 |   120 |    41   (0)| 00:00:01 |     1 |   201 |
|* 26 |         INDEX RANGE SCAN                   | AR1_PAYMENT_1IX            |    10 |       |    40   (0)| 00:00:01 |     1 |   201 |
|  27 |       PARTITION RANGE MULTI-COLUMN         |                            |     1 |       |     8   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 28 |        INDEX RANGE SCAN                    | AR1_UNAPPLIED_CREDIT_1IX   |     1 |       |     8   (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 29 |      TABLE ACCESS BY LOCAL INDEX ROWID     | AR1_UNAPPLIED_CREDIT       |     1 |    26 |     8   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------------------------------------------

可以看到性能的提升是非常大的。
通过这个案例,我们可以看到,对于sql调优的很多关键点还是需要和开发配合,从业务上进行支持是很快捷的一种方式。这种调优方式可以从整体的角度来看待这个问题,而不单单是技术角度。这个时候调优工作就会轻松不少,清晰不少。
在定位sql语句的性能瓶颈时,发现全表扫描相关的COLLECTION ITERATOR PICKLER FETCH操作在这个场景中是不合适的。能够用相关的索引扫描或者临时表来代替都是不错的选择。
上一篇:属性名和字段名不一致| 学习笔记


下一篇:一对多的处理| 学习笔记