通過執行計劃中的CONCATENATION分析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 |
--------------------------------------------------------------------------------------------------------------------------------------------
首先就是檢視是不是邏輯上出現了明顯的問題，這個時候索引的影響已經沒那麼重要了。
我們來推敲一下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 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
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")
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1403180/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- Oracle sql執行計劃OracleSQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- Oracle 通過註釋改變執行計劃Oracle
- (4) MySQL中EXPLAIN執行計劃分析MySqlAI
- 如何檢視SQL的執行計劃SQL
- explain執行計劃分析AI
- SqlServer的執行計劃如何分析？SQLServer
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- SQL中rownum和order by的執行順序的問題SQL
- Slave SQL執行緒與PXB FTWRL死鎖問題分析SQL執行緒
- 通過幾個問題深入分析Vue中的keyVue
- 【YashanDB知識庫】繫結引數，同一個sql多個執行計劃的問題SQL
- 【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃SQLOracle
- PostgreSQL 查詢當前執行中sql的執行計劃——pg_show_plans模組SQL
- DB2執行計劃分析DB2
- 來自靈魂的拷問——知道什麼是SQL執行計劃嗎？SQL
- 利用SSIS在SQL Azure中執行計劃任務（下）KCSQL
- 利用SSIS在SQL Azure中執行計劃任務（上）DJSQL
- 分析執行計劃優化SQLSQL語句處理的過程(轉)優化SQL
- Java之執行緒安全問題的3種處理方式（通過執行緒同步）Java執行緒
- 透過空間佔用和執行計劃瞭解SQL Server的行儲存索引SQLServer索引
- [20210114]toad檢視真實執行計劃問題.txt
- SQLServer統計監控SQL執行計劃突變的方法SQLServer
- SQLSERVER中得到執行計劃的方式SQLServer
- [20210926]並行執行計劃疑問.txt並行
- 執行計劃-1：獲取執行計劃
- Oracle - 執行過的SQL、正在執行的SQL、消耗資源最多的SQLOracleSQL
- mysql 執行計劃索引分析筆記MySql索引筆記
- SQL語句中的AND和OR執行順序問題SQL
- 檢視SQL執行計劃的幾種常用方法YQSQL
- Oracle資料庫關於SQL的執行計劃(轉)Oracle資料庫SQL
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI