当前位置：首页 > news >正文

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

news 来源：原创 2024/5/3 8:41:16

今天开发的同事找到我，让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了，需要尽快进行性能调优。sql语句比较长，是由几个Union连接起来的子查询。xxxxxUNION 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 :7GROUP 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 :7GROUP 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语句就是下面的形式，得到的结果还是typeSQL> 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操作在这个场景中是不合适的。能够用相关的索引扫描或者临时表来代替都是不错的选择。