轉自：
http://blog.itpub.net/23718752/viewspace-1403180/
昨天開發的一個同僚找到我，說寫了一條sql語句，但是執行了半個小時還沒有執行完，想讓我幫忙看看是怎麼回事。
他大體上給我講了下邏輯，表bl1_rc_rates是千萬級資料量的表，autsu_subscriber 是個臨時表，裡面隻有三百多條資料，bl1_activity_history 表的資料量略小，是百萬級的。
select distinct hist.entity_id, rc.* from bl1_activity_history hist, bl1_rc_rates rc, autsu_subscriber sub
where hist.entity_id = sub.subscriber_no
and hist.customer_id = sub.customer_id
and hist.activity_id = '48'
and hist.entity_id = rc.service_receiver_id
and hist.customer_id = rc.receiver_customer
and rc.service_receiver_id=sub.subscriber_no
and rc.receiver_customer= sub.customer_id
and trunc(hist.activity_date,'dd') = trunc(rc.effective_date,'dd')
and rc.amount > 0
and rc.expiration_date is null or rc.expiration_date > to_date('20141019','yyyymmdd');
先來看看執行計劃吧，一看吓一跳
Plan hash value: 3128694621
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22G| 13T| | 4217M (1)|999:59:59 | | |
| 1 | HASH UNIQUE | | 22G| 13T| 16T| 4217M (1)|999:59:59 | | |
| 2 | CONCATENATION | | | | | | | | |
| 3 | NESTED LOOPS | | 21G| 13T| | 47M (1)|159:35:59 | | |
| 4 | NESTED LOOPS | | 13M| 8211M| | 1393K (1)| 04:38:47 | | |
| 5 | PARTITION RANGE ALL | | 1 | 622 | | 980K (1)| 03:16:02 | 1 | 11 |
|* 6 | TABLE ACCESS FULL | BL1_RC_RATES | 1 | 622 | | 980K (1)| 03:16:02 | 1 | 11 |
| 7 | PARTITION RANGE ALL | | 27M| 622M| | 413K (1)| 01:22:45 | 1 | 11 |
| 8 | TABLE ACCESS FULL | BL1_ACTIVITY_HISTORY | 27M| 622M| | 413K (1)| 01:22:45 | 1 | 11 |
| 9 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | | 3 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS | | | | | | | | |
| 11 | NESTED LOOPS | | 1 | 672 | | 2949 (1)| 00:00:36 | | |
| 12 | NESTED LOOPS | | 1 | 50 | | 2947 (1)| 00:00:36 | | |
| 13 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | | 5 (0)| 00:00:01 | | |
| 14 | PARTITION RANGE ALL | | 1 | 24 | | 2 (0)| 00:00:01 | 1 | 11 |
|* 15 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_ACTIVITY_HISTORY | 1 | 24 | | 2 (0)| 00:00:01 | 1 | 11 |
|* 16 | INDEX RANGE SCAN | BL1_ACTIVITY_HISTORY_3IX | 1 | | | 2 (0)| 00:00:01 | 1 | 11 |
| 17 | PARTITION RANGE ALL | | 1 | | | 2 (0)| 00:00:01 | 1 | 11 |
|* 18 | INDEX RANGE SCAN | BL1_RC_RATES_3IX | 1 | | | 2 (0)| 00:00:01 | 1 | 11 |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_RC_RATES | 1 | 622 | | 2 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------------
這條語句的預計結果又22G rows，執行時間已經沒法估量了。這種問題一看就是一個很好的案例。
首先就是檢視是不是邏輯上出現了明顯的問題，這個時候索引的影響已經沒那麼重要了。
我們來推敲一下where中的過濾條件
hist.entity_id = sub.subscriber_no
and hist.customer_id = sub.customer_id
and hist.entity_id = rc.service_receiver_id
and
and rc.service_receiver_id=sub.subscriber_no
and rc.receiver_customer= sub.customer_id
通過hist. entity_id = sub.subscriber_no和and rc.service_receiver_id=sub.subscriber_no可以推得hist.entity_id=rc.service_receiver_id，在過濾條件中又寫了一遍，
同理hist.customer_id = sub.customer_id和rc.receiver_customer= sub.customer_id可以推得 hist.customer_id = rc.receiver_customer 是以這個條件也是備援的。
我們可以基于表中的資料量來合理的選擇列的關聯。
除了這個問題，還有一個明顯的問題，就是查詢輸出列select distinct hist.entity_id,
既然hist.entity_id和rc.subscriber_no已經是相等的了，就不需要再輸出hist.entity_id然後做distinct運算了。為了突出這個問題的嚴重性，我先不删除備援的過濾條件。隻是删除查詢輸出列中的distinct hist.entity_id
select rc.* from bl1_activity_history hist, bl1_rc_rates rc, autsu_subscriber sub
where hist.entity_id = sub.subscriber_no
and hist.customer_id = sub.customer_id
and hist.activity_id = '48'
and hist.entity_id = rc.service_receiver_id
and hist.customer_id = rc.receiver_customer
and rc.service_receiver_id=sub.subscriber_no
and rc.receiver_customer= sub.customer_id
and trunc(hist.activity_date,'dd') = trunc(rc.effective_date,'dd')
and rc.amount > 0
and rc.expiration_date is null or rc.expiration_date > to_date('20141019','yyyymmdd');
來看看執行計劃
Plan hash value: 1018700604
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21G| 13T| 47M (1)|159:36:35 | | |
| 1 | CONCATENATION | | | | | | | |
| 2 | NESTED LOOPS | | 21G| 13T| 47M (1)|159:35:59 | | |
| 3 | NESTED LOOPS | | 13M| 8211M| 1393K (1)| 04:38:47 | | |
| 4 | PARTITION RANGE ALL | | 1 | 622 | 980K (1)| 03:16:02 | 1 | 11 |
|* 5 | TABLE ACCESS FULL | BL1_RC_RATES | 1 | 622 | 980K (1)| 03:16:02 | 1 | 11 |
| 6 | PARTITION RANGE ALL | | 27M| 622M| 413K (1)| 01:22:45 | 1 | 11 |
| 7 | TABLE ACCESS FULL | BL1_ACTIVITY_HISTORY | 27M| 622M| 413K (1)| 01:22:45 | 1 | 11 |
| 8 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | 3 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 672 | 2949 (1)| 00:00:36 | | |
| 11 | NESTED LOOPS | | 1 | 50 | 2947 (1)| 00:00:36 | | |
| 12 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | 5 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE ALL | | 1 | 24 | 2 (0)| 00:00:01 | 1 | 11 |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_ACTIVITY_HISTORY | 1 | 24 | 2 (0)| 00:00:01 | 1 | 11 |
|* 15 | INDEX RANGE SCAN | BL1_ACTIVITY_HISTORY_3IX | 1 | | 2 (0)| 00:00:01 | 1 | 11 |
| 16 | PARTITION RANGE ALL | | 1 | | 2 (0)| 00:00:01 | 1 | 11 |
|* 17 | INDEX RANGE SCAN | BL1_RC_RATES_3IX | 1 | | 2 (0)| 00:00:01 | 1 | 11 |
|* 18 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_RC_RATES | 1 | 622 | 2 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------
情況相對改善了不少，但是還是有問題的節奏。
這個時候我們來看看執行計劃吧，注意到這個執行計劃有些奇怪，隻有3個表的關聯，但是執行計劃中缺出現了兩個子查詢，對于執行計劃中的CONCATENATION自己比較陌生，就沒有細究。
直接看走一個并行，效果怎麼樣。并行開了4個。從執行計劃來看，情況好了很多，看似可以完成的樣子了。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1282 | 831K| 390K (1)| 01:18:07 | | | | | |
| 1 | CONCATENATION | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 801 | 519K| 271K (1)| 00:54:23 | | | Q1,00 | P->S | QC (RAND) |
| 4 | NESTED LOOPS | | 801 | 519K| 271K (1)| 00:54:23 | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS | | 1 | 638 | 271K (1)| 00:54:23 | | | Q1,00 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 1 | 622 | 271K (1)| 00:54:23 | 1 | 11 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | BL1_RC_RATES | 1 | 622 | 271K (1)| 00:54:23 | 1 | 11 | Q1,00 | PCWP | |
| 8 | PARTITION RANGE ALL | | 1 | 16 | 2 (0)| 00:00:01 | 1 | 11 | Q1,00 | PCWP | |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_ACTIVITY_HISTORY | 1 | 16 | 2 (0)| 00:00:01 | 1 | 11 | Q1,00 | PCWP | |
|* 10 | INDEX RANGE SCAN | BL1_ACTIVITY_HISTORY_3IX | 1 | | 2 (0)| 00:00:01 | 1 | 11 | Q1,00 | PCWP | |
| 11 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | 5 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | NESTED LOOPS | | 481 | 311K| 118K (1)| 00:23:45 | | | | | |
| 13 | NESTED LOOPS | | 1 | 648 | 3764 (1)| 00:00:46 | | | | | |
| 14 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | 5 (0)| 00:00:01 | | | | | |
| 15 | PARTITION RANGE ALL | | 1 | 622 | 2 (0)| 00:00:01 | 1 | 11 | | | |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_RC_RATES | 1 | 622 | 2 (0)| 00:00:01 | 1 | 11 | | | |
|* 17 | INDEX RANGE SCAN | BL1_RC_RATES_3IX | 13 | | 2 (0)| 00:00:01 | 1 | 11 | | | |
| 18 | PARTITION RANGE ALL | | 27M| 415M| 413K (1)| 01:22:45 | 1 | 11 | | | |
|* 19 | TABLE ACCESS FULL | BL1_ACTIVITY_HISTORY | 27M| 415M| 413K (1)| 01:22:45 | 1 | 11 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
但是根據實際的情況，從300條左右的資料中大表中的索引，查取資料應該也沒那麼慢。肯定還是什麼地方不對勁，就檢視了下 CONCATENATION 的解釋
concatenation在sql級别和兩個hint相關，no_expend, no_concat
no_expand提示的說明是
The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
use_concat提示的說明是
The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
當我讀到第二句的時候，我就恍然明白了。
和開發确認過濾條件and rc.expiration_date is null or rc.expiration_date > to_date('20141019','yyyymmdd'); 是不是 期望是 and (rc.expiration_date is null or rc.expiration_date > to_date('20141019','yyyymmdd'));
他一愣，一想确實是這個道理。剩下的事情就簡單了。我們不要并行來看看最終的執行結果。
select rc.* from bl1_rc_rates rc, autsu_subscriber sub,bl1_activity_history hist
where rc.service_receiver_id=sub.subscriber_no
and rc.receiver_customer= sub.customer_id
and rc.amount > 0
and (rc.expiration_date is null or rc.expiration_date > to_date('20141019','yyyymmdd'))
and rc.service_receiver_id=hist.entity_id
and rc.receiver_customer=hist.customer_id
and hist.activity_id = '48'
Plan hash value: 3908327465
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1634 | 1059K| 6706 (1)| 00:01:21 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1634 | 1059K| 6706 (1)| 00:01:21 | | |
| 3 | NESTED LOOPS | | 1634 | 1034K| 3764 (1)| 00:00:46 | | |
| 4 | TABLE ACCESS FULL | AUTSU_SUBSCRIBER | 1634 | 42484 | 5 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 1 | 622 | 2 (0)| 00:00:01 | 1 | 11 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_RC_RATES | 1 | 622 | 2 (0)| 00:00:01 | 1 | 11 |
|* 7 | INDEX RANGE SCAN | BL1_RC_RATES_3IX | 13 | | 2 (0)| 00:00:01 | 1 | 11 |
| 8 | PARTITION RANGE ALL | | 1 | | 2 (0)| 00:00:01 | 1 | 11 |
|* 9 | INDEX RANGE SCAN | BL1_ACTIVITY_HISTORY_3IX | 1 | | 2 (0)| 00:00:01 | 1 | 11 |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_ACTIVITY_HISTORY | 1 | 16 | 2 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("RC"."AMOUNT">0 AND ("RC"."EXPIRATION_DATE" IS NULL OR "RC"."EXPIRATION_DATE">TO_DATE(' 2014-10-19
00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND "RC"."RECEIVER_CUSTOMER"="SUB"."CUSTOMER_ID")
7 - access("RC"."SERVICE_RECEIVER_ID"="SUB"."SUBSCRIBER_NO")
9 - access("RC"."SERVICE_RECEIVER_ID"="HIST"."ENTITY_ID" AND "HIST"."ACTIVITY_ID"='48')
10 - filter("RC"."RECEIVER_CUSTOMER"="HIST"."CUSTOMER_ID")
看來任何細小對的問題都會導緻很嚴重的問題，尤其是在資料量太大的情況下，錯誤的放大效應就會很明顯。