昨天開發的一個同事找到我，說寫了一條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")

看來任何細小對的問題都會導致很嚴重的問題，尤其是在資料量太大的情況下，錯誤的放大效應就會很明顯。

透過執行計劃中的CONCATENATION分析sql問題

相關文章