執行計劃中的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- ORACLE DML執行計劃頻繁變更導致業務響應極慢問題的處理Oracle
- MySQL8.0 view導致的效能問題MySqlView
- WebMagic多執行緒導致註解失效問題Web執行緒
- ant design 中，使用dva/fetch 設定導致無法從後臺匯出excel的問題Excel
- shell 中的 set -e 導致的退出問題
- 多執行緒中自定義執行緒池與shiro導致的許可權錯亂問題解決執行緒
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 多執行緒引起的效能問題分析執行緒
- SQLSERVER中得到執行計劃的方式SQLServer
- ANALYZE導致的阻塞問題分析
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 多次執行mysql_fetch_array()的指標歸位問題探討MySql指標
- 關於物件導向的方法並行執行的問題物件並行
- 關於如何解決IDEA中同一個src下多個類中之一執行時自動報錯其他類中的問題導致想要執行的類無法正常執行的問題的解決思路Idea
- MySQL 因資料型別轉換導致執行計劃使用低效索引MySql資料型別索引
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 在https中引入http資源所導致的問題HTTP
- UINavigationController 全域性右劃導致介面假死問題解決UINavigationController
- [20210114]toad檢視真實執行計劃問題.txt
- pymysql 非執行緒安全導致的故障.MySql執行緒
- parallelStream中的執行緒安全問題Parallel執行緒
- Faiss使用多執行緒出現的效能問題AI執行緒
- [20210926]並行執行計劃疑問.txt並行
- MySql中執行計劃如何來的——Optimizer TraceMySql
- 執行計劃-1：獲取執行計劃
- Json序列化與反序列化導致多執行緒執行速度和單執行緒執行速度一致問題JSON執行緒
- 記一次鎖使用不當導致Dubbo執行緒阻塞問題執行緒
- 壞程式碼導致的效能問題大賞：CPU佔用飆到了900%！
- golang slice使用不慎導致的問題Golang
- CAS導致的ABA問題及解決
- 分散式鎖導致的超賣問題分散式
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 模板方法中的執行緒安全問題執行緒
- lambda中stream執行緒安全的問題執行緒
- 在Oracle中，如何得到真實的執行計劃？Oracle
- SqlServer的執行計劃如何分析？SQLServer