將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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle左外連線、右外連線、完全外連線以及(+)號用法Oracle
- sql 內連線和外連線SQL
- MYSQL語法：左連線、右連線、內連線、全外連線MySql
- MySQL筆記3——內連線/外連線、多表連線MySql筆記
- Vscode外掛：將程式碼連線到IDE中的文件VSCodeIDE
- Oracle優化案例-用left join代替反連線 not in not exists（十）Oracle優化
- 內聯，左外聯，右外聯，全連線，交叉連線 的區別
- 【Django】將Django模板引擎更改為Jinja2Django
- 3.DQL資料查詢語言（內連線，外連線，自連線）
- Mysql——內外連線，事物，索引MySql索引
- EXISTS、IN、NOT EXISTS、NOT IN(zt)
- MYSQL學習筆記23： 多表查詢（自連線內連線+左右外連線）MySql筆記
- MySQL連線控制外掛介紹MySql
- mysql外連線有哪些型別MySql型別
- win10怎樣更改連線屬性_win10怎麼更改網路屬性Win10
- PostgreSQL優化案例之 反連線與外連線等價改寫SQL優化
- [20180808]exists and not exists.txt
- 如何將MacBook連線到電視？Mac
- Vmware虛擬機器連線外網虛擬機
- 內外網同時連線解決
- 關於外連線和where條件
- 無法連線java visualvm外掛中心JavaLVM
- 對線面試官：SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析面試SQL
- 更改Atom外掛安裝位置
- win10 mstsc連線特別慢怎麼更改_win10 mstsc連線特別慢解決方法Win10
- 通過外來鍵連線多個表
- Winodws同時連線內網和外網內網
- ADFS 部署資料庫AlwaysOn後應用端的連線字串更改資料庫字串
- mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢MySql
- exists()、not exists() 、in()、not in()用法以及效率差異
- sql：delete if exists還是drop if exists?SQLdelete
- Windows 11中如何將游標更改為暗模式的方法教程Windows模式
- git連線華為雲DevCloudGitdevCloud
- 高效安全協作，為博雅智匯連線海內外提供無障礙通訊
- 連線位置國外的遠端桌面，使用anydesk
- XMind怎麼更改線條顏色？XMind更改線條顏色的方法
- SQL改寫系列九：外連線轉內連線的常見場景與錯誤SQL
- MySQL 連線為什麼掛死了？MySql
- MySQL 連線為什麼掛死了MySql