執行計劃中的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. 參見
哲學中說存在即合理，肯定是在特定的場景中使用才有一定的意義，主要在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操作在這個場景中是不合適的。能夠用相關的索引掃描或者臨時表來代替都是不錯的選擇。
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. 參見
哲學中說存在即合理，肯定是在特定的場景中使用才有一定的意義，主要在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操作在這個場景中是不合適的。能夠用相關的索引掃描或者臨時表來代替都是不錯的選擇。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1674488/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 執行計劃的偏差導致的效能問題
- 完美的執行計劃導致的效能問題
- 執行計劃問題導致處理速度時快時慢的問題
- 執行計劃變化導致CPU負載高的問題分析負載
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- Grant許可權導致執行計劃失效
- ORACLE DML執行計劃頻繁變更導致業務響應極慢問題的處理Oracle
- mysql的執行計劃快取問題MySql快取
- 統計資訊不正確導致執行計劃的錯誤選擇
- RLM$SCHDNEGACTION 執行導致負載問題負載
- MySQL8.0 view導致的效能問題MySqlView
- 11G的SORT GROUP BY NOSORT導致錯誤執行計劃
- 資料庫統計資訊不更新導致的效能問題資料庫
- 通過執行計劃中的CONCATENATION分析sql問題SQL
- 透過執行計劃中的CONCATENATION分析sql問題SQL
- Java中Collection和Iterator介面Java
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 執行計劃錯誤導致系統負載高負載
- 一個執行計劃解析的小問題分析
- 解決TOAD中執行計劃顯示報錯的問題
- WebMagic多執行緒導致註解失效問題Web執行緒
- Oracle資料庫導致效能問題的可能原因Oracle資料庫
- memlock過低導致的資料庫效能問題資料庫
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 一條執行4秒的sql語句導致的系統問題SQL
- SCHEDULER呼叫XDB程式導致效能問題
- 看懂Oracle中的執行計劃Oracle
- 統計資訊不準確導致執行計劃走了笛卡爾積
- 一次ORACLE SQL謂詞跨界導致的執行計劃不準OracleSQL
- 交流（1）-- 執行計劃錯誤問題
- Oracle訪問表的執行計劃Oracle
- zt_導回awr報告中的歷史執行計劃
- oracle中執行計劃中的cardinalityOracle
- SQLSERVER中得到執行計劃的方式SQLServer
- MySQL中in（常量列表）的執行計劃MySql
- ant design 中，使用dva/fetch 設定導致無法從後臺匯出excel的問題Excel
- 找出導致db file scattered read等待事件發生的SQL及其執行計劃事件SQL