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