[20151209]一條sql語句的優化(續).txt
[20151209]一條sql語句的優化(續).txt
http://blog.itpub.net/267265/viewspace-1852195/
--上次提到其中1條sql語句：
1.環境：
SYSTEM@192.168.99.105:1521/dbcn> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
sql_id=18rkuzabjzj2f
SELECT CP_YZMX.XMMC
FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND CP_YZMX.XMDM = :1;
--今天檢查發現開發修改為：
sql_id=8uj587u1dx435
SELECT CP_YZMX.XMMC
FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND CP_YZMX.XMDM = :1
AND ROWNUM = :"SYS_B_0";
--說明: ROWNUM = 1(主要是我們應用存在大量非繫結語句，我把引數cursor_sharing=force).
--實際上開發存在問題，難道沒有一個表存在專案程式碼，專案名稱的字典嗎？我感覺資料結構存在問題。
Plan hash value: 1969266810
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3113 (100)| | 1 |00:00:00.01 | 7 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 7 | | | |
|* 2 | HASH JOIN | | 1 | 1 | 33 | 3113 (1)| 00:00:38 | 1 |00:00:00.01 | 7 | 3886K| 3886K| 270K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
| 6 | JOIN FILTER USE | :BF0000 | 1 | 36 | 864 | 3111 (1)| 00:00:38 | 1 |00:00:00.01 | 4 | | | |
|* 7 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 36 | 864 | 3111 (1)| 00:00:38 | 1 |00:00:00.01 | 4 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F04857F1
4 - SEL$F04857F1 / EMR_DJMX@SEL$1
5 - SEL$F04857F1 / EMR_DJMX@SEL$1
7 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=:NSYS_B_0)
2 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
5 - access("EMR_DJMX"."XMID"=:N1)
7 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
--邏輯讀7。
如果要是我寫，我會改寫如下：
SELECT CP_YZMX.XMMC
FROM CP_YZMX
WHERE CP_YZMX.XMDM = :N1
AND ROWNUM = 1
AND EXISTS
(SELECT NULL
FROM EMR_DJMX, emr_djfl
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID);
--可是執行計劃如下：
Plan hash value: 1768383312
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8487 (100)| | 1 |00:00:00.22 | 33193 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.22 | 33193 | | | |
|* 2 | HASH JOIN SEMI | | 1 | 2 | 66 | 8487 (1)| 00:01:42 | 1 |00:00:00.22 | 33193 | 2827K| 2827K| 1291K (0)|
|* 3 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 98 | 2352 | 8485 (1)| 00:01:42 | 11170 |00:00:00.21 | 33190 | 1025K| 1025K| |
| 4 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$46948216
3 - SEL$46948216 / CP_YZMX@SEL$1
4 - SEL$46948216 / EMR_DJMX@SEL$2
5 - SEL$46948216 / EMR_DJMX@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
3 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
5 - access("EMR_DJMX"."XMID"=:N1)
-- 33190*8192/1024/1024=259.296875 M,也就是全表掃描CP_YZMX表。反而比開發寫的效果差。
-- 問題在那裡呢？問題出在 HASH JOIN SEMI，這樣要構件第1個表CP_YZMX的hash，這樣必須走全表掃描，看來oracle cbo也太不聰明....實際上輸出僅僅要1行。
-- 很明顯連線順序出現了問題？
--抽取執行計劃：
>@dpc 7rrpbdfnp78kd outline
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$46948216")
UNNEST(@"SEL$DB49FC34")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$DB49FC34")
ELIMINATE_JOIN(@"SEL$2" "EMR_DJFL"@"SEL$2")
OUTLINE(@"SEL$2")
FULL(@"SEL$46948216" "CP_YZMX"@"SEL$1")
INDEX_RS_ASC(@"SEL$46948216" "EMR_DJMX"@"SEL$2" ("EMR_DJMX"."XMID"))
LEADING(@"SEL$46948216" "CP_YZMX"@"SEL$1" "EMR_DJMX"@"SEL$2")
USE_HASH(@"SEL$46948216" "EMR_DJMX"@"SEL$2")
END_OUTLINE_DATA
*/
--然後改寫如下：下劃線~是我改動的提示。
SELECT
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$46948216")
UNNEST(@"SEL$DB49FC34")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$DB49FC34")
ELIMINATE_JOIN(@"SEL$2" "EMR_DJFL"@"SEL$2")
OUTLINE(@"SEL$2")
FULL(@"SEL$46948216" "CP_YZMX"@"SEL$1")
INDEX_RS_ASC(@"SEL$46948216" "EMR_DJMX"@"SEL$2" ("EMR_DJMX"."XMID"))
LEADING(@"SEL$46948216" "EMR_DJMX"@"SEL$2" "CP_YZMX"@"SEL$1" )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USE_NL(@"SEL$46948216" "CP_YZMX"@"SEL$1")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END_OUTLINE_DATA
*/
CP_YZMX.XMMC
FROM CP_YZMX
WHERE CP_YZMX.XMDM = :N1
AND ROWNUM = 1
AND EXISTS
(SELECT NULL
FROM EMR_DJMX, emr_djfl
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID);
Plan hash value: 2877850036
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8490 (100)| | 1 |00:00:00.01 | 7 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 7 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 33 | 8490 (1)| 00:01:42 | 1 |00:00:00.01 | 7 | | | |
| 3 | SORT UNIQUE | | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 6 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 1 | 24 | 8487 (1)| 00:01:42 | 1 |00:00:00.01 | 4 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$46948216
4 - SEL$46948216 / EMR_DJMX@SEL$2
5 - SEL$46948216 / EMR_DJMX@SEL$2
6 - SEL$46948216 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - access("EMR_DJMX"."XMID"=:N1)
6 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))
--很明顯這樣最優，實際上如果查詢的:N1 如果再CP_YZMX的表裡面特別靠後，這樣的邏輯讀會很大的。
SELECT DISTINCT xmid
FROM EMR_DJMX, emr_djfl
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND NOT EXISTS
(SELECT 1
FROM CP_YZMX
WHERE xmdm = xmid);
....
-- 找到1個xmid=851,帶入查詢:N1=851。
Plan hash value: 2877850036
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9046 (100)| | 0 |00:00:00.19 | 33194 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 0 |00:00:00.19 | 33194 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 33 | 9046 (1)| 00:01:49 | 0 |00:00:00.19 | 33194 | | | |
| 3 | SORT UNIQUE | | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 6 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 1 | 24 | 9043 (1)| 00:01:49 | 0 |00:00:00.19 | 33191 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$46948216
4 - SEL$46948216 / EMR_DJMX@SEL$2
5 - SEL$46948216 / EMR_DJMX@SEL$2
6 - SEL$46948216 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - access("EMR_DJMX"."XMID"=:N1)
6 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))
-- 因為找到不到:N1=851的資料( "CP_YZMX"."XMDM"=851)在CP_YZMX表，導致執行效率很低。
-- 使用開發寫的執行計劃如下：
Plan hash value: 1969266810
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3106 (100)| | 0 |00:00:00.19 | 33193 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 0 |00:00:00.19 | 33193 | | | |
|* 2 | HASH JOIN | | 1 | 1 | 33 | 3106 (1)| 00:00:38 | 0 |00:00:00.19 | 33193 | 3886K| 3886K| 265K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
| 6 | JOIN FILTER USE | :BF0000 | 1 | 36 | 864 | 3104 (1)| 00:00:38 | 0 |00:00:00.19 | 33190 | | | |
|* 7 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 36 | 864 | 3104 (1)| 00:00:38 | 0 |00:00:00.19 | 33190 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F04857F1
4 - SEL$F04857F1 / EMR_DJMX@SEL$1
5 - SEL$F04857F1 / EMR_DJMX@SEL$1
7 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 851
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=:NSYS_B_0)
2 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
5 - access("EMR_DJMX"."XMID"=:N1)
7 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
44 rows selected.
--不是很瞭解應用的模式，看來要優化這條sql語句建立CP_YZMX.XMDM的垃圾索引才是王道。
--順便提一下另外的語句：
sql_id=7dys4vnppxu76
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND CP_YZMX.XMDM = :1;
--改寫這樣：
a23u448k9dhmw
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
FROM EMR_DJMX
,emr_djfl
,CP_YZMX
,cp_brlj
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND cp_yzmx.brlj = cp_brlj.brlj
AND CP_YZMX.XMDM = :1
AND cp_brlj.brid = :2;
--漏掉應該查詢另外1個表的cp_brlj.brid(病人ID),真tmd的奇葩!!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1869115/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一條sql語句的優化SQL優化
- 一條SQL語句的優化過程SQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- [20140210]一條sql語句的優化(11g).txtSQL優化
- SQL語句優化－－十條經驗SQL優化
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- 一條update語句的優化探索優化
- 一個SQL語句的優化SQL優化
- SQL語句的優化SQL優化
- 一條sql語句的建議調優分析SQL
- 一條簡單的SQL語句優化-新年新氣象SQL優化
- SQL語句優化SQL優化
- 總結出10條SQL語句優化精髓SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- SQL 語句的優化方法SQL優化
- 一次sql語句優化的反思SQL優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 求助：SQL語句優化SQL優化
- [20170104]一條sql優化.txtSQL優化
- SQL語句優化的34條建議(轉-浪花七八朵 )SQL優化
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條SQL語句的旅行之路SQL
- 優化 SQL 語句的步驟優化SQL
- 關於sql語句的優化SQL優化
- sql語句的優化案例分析SQL優化
- [20201210]sql語句優化.txtSQL優化
- MySQL之SQL語句優化MySql優化