[20151203]一條sql語句的優化.txt
[20151203]一條sql語句的優化.txt
--很久沒有看生產系統的awr報表，昨天下午看了一下，我非常討厭看生產系統的awr報表，看著開發寫的sql語句有時候心情會非常不好。
--實際上拿生產系統的sql語句寫blog非常困難，別人沒有你的環境(即使表述出來，也要浪費很長的篇幅)，寫出來的東西大部分僅僅自
--己讀得懂。
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=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;
--執行計劃如下：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5a4qbg8wx7zz2, child number 0
-------------------------------------
select /* test 18rkuzabjzj2f */ /*+ gather_plan_statistics */
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 =:N1
Plan hash value: 2599026261
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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)| | 4163 |00:00:00.22 | 32194 | | | |
|* 1 | HASH JOIN | | 1 | 3 | 99 | 8487 (1)| 00:01:42 | 4163 |00:00:00.22 | 32194 | 3886K| 3886K| 286K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
| 5 | JOIN FILTER USE | :BF0000 | 1 | 98 | 2352 | 8485 (1)| 00:01:42 | 4163 |00:00:00.22 | 32191 | | | |
|* 6 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 98 | 2352 | 8485 (1)| 00:01:42 | 4163 |00:00:00.22 | 32191 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F04857F1
3 - SEL$F04857F1 / EMR_DJMX@SEL$1
4 - SEL$F04857F1 / EMR_DJMX@SEL$1
6 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
4 - access("EMR_DJMX"."XMID"=:N1)
6 - 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")))
42 rows selected.
--僅僅顯示查詢的欄位不同。做一點小小的說明：EMR_DJMX, emr_djfl屬於應用的字典表都很小。僅僅CP_YZMX表很大，目前250M。
-- CP_YZMX.XMDM 漢語拼音轉義過來就是 專案程式碼。
-- CP_YZMX.XMMC 表示的就是專案名稱。
-- CP_YZMX 表示 CP表示臨床路徑 YZMX表示 醫囑明細
--很明顯第1條語句會出現出一串一樣的名稱，它輸出的全部做這個專案程式碼的專案名稱。第2條也是類似的情況。
--其實更讓我吃驚的是EMR_DJMX表裡面竟然沒有相關的xmmc(專案名稱)的欄位。
--這樣的查詢語句隨著表CP_YZMX表變大，查詢會越來越慢。
2.優化:
--也許有人會講像第1條語句建立CP_YZMX.XMDM的索引不就ok了嗎？當然加上CP_YZMX.XMMC建立的符合索引更佳(實際上僅僅建立
--CP_YZMX.XMDM索引根本沒用,因為這個欄位選擇性很差，我看統計僅僅28xx個不同的值),仔細想想好像是快的,但是隨著CP_YZMX記錄的
--增加,索引掃描的範圍會越來越大。
--如果真正優化我覺得應該寫成：
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=1;
--當然還給建立符合索引 CP_YZMX.XMDM +CP_YZMX.XMMC. 或者CP_YZMX.XMDM。
--我明顯感覺開發資料結構存在問題，不知道為什麼字典表裡面沒有xmmc(專案名稱)欄位，也許還有其它字典儲存這個資訊。
3.接著看第2條語句：
--先看執行計劃：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fqd21c2q20vvf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
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 =:N1
Plan hash value: 1980913021
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8488 (100)| | 0 |00:00:00.01 | 2 | | | |
|* 1 | HASH JOIN | | 1 | 2 | 62 | 8488 (1)| 00:01:42 | 0 |00:00:00.01 | 2 | 2345K| 2345K|37888 (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 7 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 0 | 1 | 8 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | JOIN FILTER USE | :BF0000 | 0 | 98 | 686 | 8485 (1)| 00:01:42 | 0 |00:00:00.01 | 0 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | CP_YZMX | 0 | 98 | 686 | 8485 (1)| 00:01:42 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / EMR_DJMX@SEL$1
6 - SEL$1 / EMR_DJMX@SEL$1
7 - SEL$1 / EMR_DJFL@SEL$1
8 - SEL$1 / EMR_DJFL@SEL$1
10 - SEL$1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 24024
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_DJFL"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
6 - access("EMR_DJMX"."XMID"=:N1)
7 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
10 - 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")))
--我這裡查詢結果是0，這也是這句話隱藏很深的原因，大部分情況下都是沒有結果(邏輯讀很小)，因為這個表記錄的是臨床路徑的醫囑明細，許多項
--目不是臨床路徑的專案(看來dba熟悉業務也很重要)，如果換成前面的525，再看看執行計劃。
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fqd21c2q20vvf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
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 =:N1
Plan hash value: 1980913021
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8488 (100)| | 4163 |00:00:00.19 | 32196 | | | |
|* 1 | HASH JOIN | | 1 | 2 | 62 | 8488 (1)| 00:01:42 | 4163 |00:00:00.19 | 32196 | 2981K| 2981K| 265K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 7 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 9 | JOIN FILTER USE | :BF0000 | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 4163 |00:00:00.19 | 32191 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 4163 |00:00:00.19 | 32191 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--查詢返回4163條，全部是一樣的內容。
--最先想到後面加入rownum=1的條件，執行計劃如下：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5nrjjgv8rw3zf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
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 =:N1 and
rownum=1
Plan hash value: 2932374829
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 3204 (100)| | 1 |00:00:00.19 | 32179 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.19 | 32179 | | | |
| 2 | MERGE JOIN | | 1 | 3 | 159 | 3204 (1)| 00:00:39 | 1 |00:00:00.19 | 32179 | | | |
| 3 | MERGE JOIN | | 1 | 41 | 902 | 3201 (1)| 00:00:39 | 1 |00:00:00.19 | 32176 | | | |
| 4 | SORT JOIN | | 1 | 37 | 259 | 3197 (1)| 00:00:39 | 1 |00:00:00.19 | 32170 | 142K| 142K| 126K (0)|
|* 5 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 37 | 259 | 3197 (1)| 00:00:39 | 4163 |00:00:00.19 | 32170 | 1025K| 1025K| |
|* 6 | SORT JOIN | | 1 | 40 | 320 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 6 | 73728 | 73728 | |
| 7 | TABLE ACCESS STORAGE FULL FIRST ROWS| EMR_DJFL | 1 | 40 | 320 | 3 (0)| 00:00:01 | 40 |00:00:00.01 | 6 | 1025K| 1025K| |
|* 8 | SORT JOIN | | 1 | 1 | 16 | 3 (34)| 00:00:01 | 1 |00:00:00.01 | 3 | 73728 | 73728 | |
| 9 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 10 | 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$1
5 - SEL$1 / CP_YZMX@SEL$1
7 - SEL$1 / EMR_DJFL@SEL$1
9 - SEL$1 / EMR_DJMX@SEL$1
10 - SEL$1 / EMR_DJMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
6 - access("EMR_DJFL"."DJID"="CP_YZMX"."DJID")
filter("EMR_DJFL"."DJID"="CP_YZMX"."DJID")
8 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
filter(("CP_YZMX"."XMDM"="EMR_DJMX"."XMID" AND "EMR_DJMX"."DJID"="EMR_DJFL"."DJID"))
10 - access("EMR_DJMX"."XMID"=:N1)
--說明：我們的系統使用硬體是exadata，第1次看到Operation =TABLE ACCESS STORAGE FULL FIRST ROWS,邏輯讀並不小32170,32170*8/1024=251M.
--改變連線順序，加入提示：/*+ leading (EMR_DJMX , emr_djfl , CP_YZMX ) */
Plan hash value: 2276510683
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6912 (100)| | 1 |00:00:00.02 | 3727 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.02 | 3727 | | | |
|* 2 | HASH JOIN | | 1 | 2 | 62 | 6912 (1)| 00:01:23 | 1 |00:00:00.02 | 3727 | 2981K| 2981K| 282K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 8 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 9 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 10 | JOIN FILTER USE | :BF0000 | 1 | 80 | 560 | 6909 (1)| 00:01:23 | 1 |00:00:00.02 | 3722 | | | |
|* 11 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 80 | 560 | 6909 (1)| 00:01:23 | 1 |00:00:00.02 | 3722 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--邏輯讀減少不少。實際上注意看CP_YZMX邏輯讀3722，也就是邏輯讀讀到3772時，找到滿足條件的記錄，還可以發現一個奇怪的現象：
--前面的 TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX cost= 3197,而後面的cost=6909,當然主要差異在 E-Rows 不同,前面37，
--而後者80。
--再仔細看顯示欄位根本沒有CP_YZMX表的欄位，查詢修改如下：
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
FROM EMR_DJMX, emr_djfl
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND EXISTS
(SELECT 1
FROM CP_YZMX
WHERE CP_YZMX.XMDM = :1
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND emr_djfl.DJID = CP_YZMX.DJID);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8112tdu600w31, child number 0
-------------------------------------
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX,
emr_djfl WHERE EMR_DJMX.DJID = emr_djfl.Djid AND EXISTS
(SELECT 1 FROM CP_YZMX WHERE
CP_YZMX.XMDM = :N1 AND CP_YZMX.xmdm =
EMR_DJMX.XMID AND emr_djfl.DJID = CP_YZMX.DJID)
Plan hash value: 130981308
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8488 (100)| | 1 |00:00:00.03 | 3727 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 1 | 31 | 8488 (1)| 00:01:42 | 1 |00:00:00.03 | 3727 | 2981K| 2981K| 282K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 7 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 9 | JOIN FILTER USE | :BF0000 | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 1 |00:00:00.03 | 3722 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 1 |00:00:00.03 | 3722 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
5 - SEL$5DA710D3 / EMR_DJMX@SEL$1
6 - SEL$5DA710D3 / EMR_DJMX@SEL$1
7 - SEL$5DA710D3 / EMR_DJFL@SEL$1
8 - SEL$5DA710D3 / EMR_DJFL@SEL$1
10 - SEL$5DA710D3 / CP_YZMX@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CP_YZMX"."XMDM"="EMR_DJMX"."XMID" AND "EMR_DJFL"."DJID"="CP_YZMX"."DJID")
6 - access("EMR_DJMX"."XMID"=:N1)
7 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
10 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."XMDM","CP_YZMX"."DJID")))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."XMDM","CP_YZMX"."DJID")))
50 rows selected.
--實際上如果不用CP_YZMX表，查詢可以修改如下：
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX, emr_djfl WHERE EMR_DJMX.DJID = emr_djfl.Djid and
EMR_DJMX.XMID=:N1;
SYSTEM@192.168.99.105:1521/dbcn> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sjt49mk99kk3, child number 0
-------------------------------------
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX,
emr_djfl WHERE EMR_DJMX.DJID = emr_djfl.Djid and EMR_DJMX.XMID=:N1
Plan hash value: 582457811
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 3 (100)| | 1 |00:00:00.01 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
|* 4 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1025K| 1025K| |
|* 5 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 6 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / EMR_DJMX@SEL$1
4 - SEL$1 / EMR_DJMX@SEL$1
5 - SEL$1 / EMR_DJFL@SEL$1
6 - SEL$1 / EMR_DJFL@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMR_DJMX"."XMID"=:N1)
5 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
--僅僅6個邏輯讀。
--當然要把邏輯讀進一步降低，還要建立CP_YZMX.XMDM的垃圾索引。當然我的優化建立在修改結果集的情況。
總結:
--1.每次看生產系統的awr報表心情總是不好，一些有問題的語句awr看不到，像第2條，如果僅僅關注邏輯讀很難發現第2條語句存在問題。因為大部分查詢返回0行。
--2.資料結構存在問題，也許還有1個表儲存專案名稱。
--3.開發寫sql語句到底想一下沒有，看著這樣開發團隊，真令人失望。
--4.在完成後才發現第1個還可以這樣寫：
SELECT CP_YZMX.XMMC
FROM CP_YZMX
WHERE CP_YZMX.XMDM = :1 and rownum=1
AND EXISTS
(SELECT 1
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)；
--甚至後面的exists都不要，真不知道開發要表達的意思，只能說明開發寫sql思路混亂。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1852195/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181114]一條sql語句的優化.txtSQL優化
- [20201210]sql語句優化.txtSQL優化
- [20200320]SQL語句優化的困惑.txtSQL優化
- [20200324]SQL語句優化的困惑2.txtSQL優化
- SQL語句優化SQL優化
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- sql語句效能優化SQL優化
- MYSQL SQL語句優化MySql優化
- 優化 SQL 語句的步驟優化SQL
- [20231114]如何知道一條sql語句涉及到那些表.txtSQL
- MySQL之SQL語句優化MySql優化
- [20210205]警惕toad下優化直方圖相關sql語句.txt優化直方圖SQL
- 一條sql語句的執行過程SQL
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- 第45期：一條 SQL 語句最佳化的基本思路SQL
- [20211224]vim外掛格式化sql語句.txtSQL
- [20211231]vim自動格式化sql語句.txtSQL
- 一條SQL更新語句是如何執行的？SQL
- 一條SQL更新語句是如何執行的SQL
- 一條update SQL語句是如何執行的SQL
- 優化 JS 條件語句的 5 個技巧優化JS
- [20220117]超長sql語句.txtSQL
- [20201105]再分析sql語句.txtSQL
- [20240607]PL/SQL中sql語句的註解.txtSQL
- SQL語句優化的原則與方法QOSQL優化
- Sql語句本身的優化-定位慢查詢SQL優化
- 一條更新的SQL語句是如何執行的？SQL
- [20240320]空格與sqlpus的sql語句.txtSQL
- [20210205]警惕toad下優化直方圖相關sql語句3.txt優化直方圖SQL
- PL/SQL 條件控制語句SQL
- 一條 SQL 查詢語句是如何執行的？SQL
- 一條SQL語句在MySQL中如何執行的MySql
- [20220331]如何調整sql語句.txtSQL
- [20240409]為什麼一條sql語句在例項2執行要慢的分析.txtSQL
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- [20211221]分析sql語句遇到的問題.txtSQL
- MySQL 52個SQL效能優化策略SQL語句彙總MySql優化