执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个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操作在这个场景中是不合适的。能够用相关的索引扫描或者临时表来代替都是不错的选择。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-05-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3198
来自专栏杨建荣的学习笔记

循序渐进调优union相关的sql(r2笔记23天)

今天在生产中发现一条sql语句消耗了大量的cpu资源。使用top -c来查看。 PID USER PR NI VIRT RES SHR S...

3786
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

1863
来自专栏杨建荣的学习笔记

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

3524
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

4094
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

1634
来自专栏杨建荣的学习笔记

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

2247
来自专栏黑泽君的专栏

day43_Oracle学习笔记_02

712
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

3045
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

28411

扫码关注云+社区

领取腾讯云代金券