將not exists更改為外連線
主機CPU一直100%，其中有一條SQL，每秒同時有15程式正在執行，並且效能還不好，要想降低CPU，就得先把這條SQL搞定，估計搞定這條SQL，CPU大概可以降到70%以下。
歡迎大家加入ORACLE超級群：17115662 免費解決各種ORACLE問題,以後BLOG將遷移到
原始SQL的執行效能如下：
| SQL> SELECT BILLFLOW_ID, PACKAGE_ID, FILE_CNT, BILLING_CYCLE_ID 2 FROM (select * from B_FILE_PACKAGE ORDER BY CREATED_DATE) a 3 where not exists (select * 4 from B_PACKAGE_STATE_TRANS b 5 where b.package_id = a.package_id 6 and b.process_id = 11081) 7 and A.STATE = 'RDY' 8 AND BILLFLOW_ID in (6, 25) 9 and rownum < 1000;
Execution Plan ---------------------------------------------------------- Plan hash value: 2380269418
---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 999 | 57942 | | 5752 (1)| 00:01:10 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | NESTED LOOPS ANTI | | 1000 | 58000 | | 5752 (1)| 00:01:10 | | 3 | VIEW | | 5666 | 254K| | 82 (2)| 00:00:01 | | 4 | SORT ORDER BY | | 1304K| 41M| 70M| 18767 (2)| 00:03:46 | |* 5 | TABLE ACCESS FULL| B_FILE_PACKAGE | 1304K| 41M| | 7086 (3)| 00:01:26 | |* 6 | INDEX UNIQUE SCAN | PK_B_PACKAGE_STATE_TRANS | 1113K| 12M| | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter(ROWNUM<1000) 5 - filter(("B_FILE_PACKAGE"."BILLFLOW_ID"=6 OR "B_FILE_PACKAGE"."BILLFLOW_ID"=25) AND "B_FILE_PACKAGE"."STATE"='RDY') 6 - access("B"."PACKAGE_ID"="A"."PACKAGE_ID" AND "B"."PROCESS_ID"=11081)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3905407 consistent gets 0 physical reads 0 redo size 991 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 12 rows processed |
這裡可以看到B_FILE_PACKAGE表走的全表掃描，整個邏輯讀達到了400W，CPU不高才怪
下面我們建立一個組合索引，並且手動指定表的連線方式，可以看到效能提高了很多，邏輯讀下降到2W，但是覺得還是有點偏高，因為型別的SQL語句，每S有20個程式同時在執行。
| SQL> SELECT BILLFLOW_ID, PACKAGE_ID, FILE_CNT, BILLING_CYCLE_ID 2 FROM (select /*+ index(c) */ 3 * 4 from system.B_FILE_PACKAGE c 5 ORDER BY CREATED_DATE) a 6 where not exists (select /*+ use_hash(b) swap_join_inputs(b) */ 7 * 8 from system.B_PACKAGE_STATE_TRANS b 9 where b.package_id = a.package_id 10 and b.process_id = 11081) 11 and A.STATE = 'RDY' 12 AND BILLFLOW_ID in (6, 25) 13 and rownum < 1000;
no rows selected
Elapsed: 00:00:07.66
Execution Plan ---------------------------------------------------------- Plan hash value: 3575369339
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 130 | | 53727 (1)| 00:10:45 | |* 1 | COUNT STOPKEY | | | | | | | |* 2 | HASH JOIN RIGHT ANTI | | 1 | 130 | 60M| 53727 (1)| 00:10:45 | |* 3 | INDEX FAST FULL SCAN| B_PACKAGE_STATE_PACKAGE_ID | 1662K| 41M| | 3131 (2)| 00:00:38 | | 4 | VIEW | | 2072K| 102M| | 41285 (1)| 00:08:16 | | 5 | SORT ORDER BY | | 2072K| 169M| 215M| 41285 (1)| 00:08:16 | | 6 | INLIST ITERATOR | | | | | | | |* 7 | INDEX RANGE SCAN | B_FILE_PACK_ALL | 141 | | | 4 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter(ROWNUM<1000) 2 - access("B"."PACKAGE_ID"="A"."PACKAGE_ID") 3 - filter("B"."PROCESS_ID"=11081) 7 - access(("C"."BILLFLOW_ID"=6 OR "C"."BILLFLOW_ID"=25) AND "C"."STATE"='RDY')
Note ----- - dynamic sampling used for this statement
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 24503 consistent gets 0 physical reads 0 redo size 536 bytes sent via SQL*Net to client 481 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed |
下面降not exists更改成外連線的方式，其實這裡也可以不用更改為外連線的方式，也可以在上面的SQL中直接使用use_nl的方式來實現。
| SQL> select * from (SELECT a.BILLFLOW_ID, a.PACKAGE_ID, a.FILE_CNT, a.BILLING_CYCLE_ID FROM system.B_FILE_PACKAGE a, system.B_PACKAGE_STATE_TRANS b where b.package_id(+) = a.package_id and b.process_id = 11081 and A.STATE = 'RDY' AND a.BILLFLOW_ID in (6, 25) and b.package_id is null order by a.created_date) where rownum < 1000;
no rows selected
Elapsed: 00:00:00.11
Execution Plan ---------------------------------------------------------- Plan hash value: 3347545174
------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 52 | 6 (17)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 52 | 6 (17)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY| | 1 | 43 | 6 (17)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 43 | 5 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | B_PACKAGE_STATE_PACKAGE_ID | 1 | 12 | 2 (0)| 00:00:01 | | 6 | INLIST ITERATOR | | | | | | |* 7 | INDEX RANGE SCAN | B_FILE_PACK_ALL | 1 | 31 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter(ROWNUM<1000) 3 - filter(ROWNUM<1000) 5 - access("B"."PACKAGE_ID" IS NULL AND "B"."PROCESS_ID"=11081) filter("B"."PROCESS_ID"=11081) 7 - access(("A"."BILLFLOW_ID"=6 OR "A"."BILLFLOW_ID"=25) AND "A"."STATE"='RDY' AND "B"."PACKAGE_ID"="A"."PACKAGE_ID")
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 536 bytes sent via SQL*Net to client 481 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed |
此SQL語句已經達到了最佳化的目標，等待開發上線後的效果。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27677261/viewspace-1170607/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- not exists改為外連線
- exists改為內連線
- exists和連線方式
- 使用表連線替代exists
- mysql 左連線，右連線，內連結，exists等MySql
- 外連線轉換為內連線的情況
- Oracle內連線、外連線、右外連線、全外連線小總結Oracle
- 【SQL】Oracle的內連線、左外連線、右外連線及全外連線SQLOracle
- Oracle左外連線、右外連線、完全外連線以及(+)號用法Oracle
- Oracle 左外連線、右外連線、全外連線小總結Oracle
- 內連線、外連線
- SQL的四種連線:內連線 左外連線 右外連線 全連線SQL
- 深入理解SQL的四種連線-左外連線、右外連線、內連線、全連線SQL
- sql 內連線和外連線SQL
- 外連線與連線順序
- 內連線、外連線總結
- SQL SERVER 自連線、外連線SQLServer
- oracle外連線Oracle
- 例項解析外連線 內連線 自連線 全連線
- MYSQL語法：左連線、右連線、內連線、全外連線MySql
- MySQL筆記3——內連線/外連線、多表連線MySql筆記
- sql內連結，外連線SQL
- mysql左外連線MySql
- sybase的外連線
- Oracle 表連線方式詳解（外連結、內連線、自連線）Oracle
- sql和hql中join語句區別,以及hibernate中內連線，迫切內連線，左外連線，迫切左外連線,右外連線的區別（合集）...SQL
- iOS wifi連線外設iOSWiFi
- mysql 外連線總結MySql
- 多表外連線的使用
- oracle 外連線的使用Oracle
- InfoSet中左外連線
- not in 用外連線實現
- 外連線(outer join)示例
- 資料庫外連線，自然連線，內連線，條件連線，等值連線關係及詳解資料庫
- Oracle優化案例-用left join代替反連線 not in not exists（十）Oracle優化
- 轉載：內連線與外連線的區別
- 你真的會玩SQL嗎？內連線、外連線SQL
- 內聯，左外聯，右外聯，全連線，交叉連線 的區別