[20151221]sql語句優化.txt
[20151221]sql語句優化.txt
--自從發現開發亂用distinct以後，連結http://blog.itpub.net/267265/viewspace-1871989/
--我看sql語句特別注意連線多個表,但是顯示僅僅一個表的情況,上個星期五，發現一條：
sql_id=dpdk3xfd6cvky
SELECT EMR_DJMX.ZSFL
FROM MS_YJ01, L_LIS_SQDMX, EMR_DJMX
WHERE MS_YJ01.YJXH IN ( :"SYS_B_00"
, :"SYS_B_01"
, :"SYS_B_02"
, :"SYS_B_03"
, :"SYS_B_04"
, :"SYS_B_05"
, :"SYS_B_06"
, :"SYS_B_07"
, :"SYS_B_08"
, :"SYS_B_09"
, :"SYS_B_10"
, :"SYS_B_11"
, :"SYS_B_12"
, :"SYS_B_13"
, :"SYS_B_14"
, :"SYS_B_15"
, :"SYS_B_16"
, :"SYS_B_17"
, :"SYS_B_18"
, :"SYS_B_19")
AND MS_YJ01.KDRQ >= TO_DATE ( :"SYS_B_20", :"SYS_B_21")
AND EMR_DJMX.ZSFL IS NOT NULL
AND EMR_DJMX.ZSFL <> :"SYS_B_22"
AND EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID
AND MS_YJ01.SQID = L_LIS_SQDMX.DOCTREQUESTNO
AND (MS_YJ01.FPHM IS NULL OR MS_YJ01.FPHM = :"SYS_B_23")
AND MS_YJ01.ZFPB = :"SYS_B_24"
AND MS_YJ01.ZXPB = :"SYS_B_25"
GROUP BY EMR_DJMX.ZSFL
UNION ALL
SELECT EMR_DJMX.ZSFL
FROM (SELECT DISTINCT MS_YJ01.sqid
FROM ms_yj01
WHERE MS_YJ01.YJXH IN ( :"SYS_B_26"
, :"SYS_B_27"
, :"SYS_B_28"
, :"SYS_B_29"
, :"SYS_B_30"
, :"SYS_B_31"
, :"SYS_B_32"
, :"SYS_B_33"
, :"SYS_B_34"
, :"SYS_B_35"
, :"SYS_B_36"
, :"SYS_B_37"
, :"SYS_B_38"
, :"SYS_B_39"
, :"SYS_B_40"
, :"SYS_B_41"
, :"SYS_B_42"
, :"SYS_B_43"
, :"SYS_B_44"
, :"SYS_B_45")
AND MS_YJ01.KDRQ >= TO_DATE ( :"SYS_B_46", :"SYS_B_47")
AND MS_YJ01.ZFPB = :"SYS_B_48"
AND MS_YJ01.ZXPB = :"SYS_B_49"
AND (MS_YJ01.FPHM IS NULL OR MS_YJ01.FPHM = :"SYS_B_50"))
,L_LIS_SQDMX
,EMR_DJMX
WHERE EMR_DJMX.ZSFL = :"SYS_B_51"
AND EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID
AND SQID = L_LIS_SQDMX.DOCTREQUESTNO;
--僅僅關注union all上部分，下面一段也一樣。為了測試方便，我帶入引數改寫如下：
SELECT EMR_DJMX.ZSFL
FROM MS_YJ01,
L_LIS_SQDMX,
EMR_DJMX
WHERE MS_YJ01.YJXH in ('10591737','10591736') and
MS_YJ01.KDRQ >= TO_DATE('19-12-2015 00:00:00','DD-MM-YYYY HH24:MI:SS') AND
EMR_DJMX.ZSFL is not null AND
EMR_DJMX.ZSFL <>9 AND
EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID AND
MS_YJ01.SQID = L_LIS_SQDMX.DOCTREQUESTNO AND
( MS_YJ01.FPHM is null or MS_YJ01.FPHM = 'NULL' ) and
MS_YJ01.ZFPB = 0 and
MS_YJ01.ZXPB = 0
group by EMR_DJMX.ZSFL;
--in 我僅僅寫兩個。
--這個明明是exists的經典應用案例，開發為了避免重複，破天荒的使用group by(這次沒有使用distinct^_^) 。正確的寫法如下：
SELECT EMR_DJMX.ZSFL
FROM EMR_DJMX
WHERE EXISTS
(SELECT 1
FROM MS_YJ01, L_LIS_SQDMX
WHERE MS_YJ01.YJXH IN ('10591737', '10591736')
AND MS_YJ01.KDRQ >=
TO_DATE
(
'19-12-2015 00:00:00'
,'DD-MM-YYYY HH24:MI:SS'
)
AND EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID
AND MS_YJ01.SQID = L_LIS_SQDMX.DOCTREQUESTNO
AND (MS_YJ01.FPHM IS NULL OR MS_YJ01.FPHM = 'NULL')
AND MS_YJ01.ZFPB = 0
AND MS_YJ01.ZXPB = 0)
AND EMR_DJMX.ZSFL IS NOT NULL
AND EMR_DJMX.ZSFL <> 9 ;
--但是改寫後執行計劃如下：(補充也許可能出現重複，加入distinct)
Plan hash value: 2311536074
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 11 (100)| | 0 |00:00:00.01 | 7108 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 51 | 11 (10)| 00:00:01 | 0 |00:00:00.01 | 7108 | 2053K| 2053K| |
| 2 | NESTED LOOPS | | 1 | 1 | 51 | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 51 | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 44 | 8 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| MS_YJ01 | 1 | 1 | 33 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |
|* 6 | INDEX RANGE SCAN | IDX_MS_YJ01_KDRQ | 1 | 2 | | 3 (0)| 00:00:01 | 12647 |00:00:00.01 | 104 | 1025K| 1025K| |
| 7 | TABLE ACCESS BY INDEX ROWID| L_LIS_SQDMX | 0 | 5 | 55 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | IF_L_LIS_SQDMX_DOCREQ_NO | 0 | 5 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 9 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 10 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 0 | 1 | 7 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / MS_YJ01@SEL$1
6 - SEL$1 / MS_YJ01@SEL$1
7 - SEL$1 / L_LIS_SQDMX@SEL$1
8 - SEL$1 / L_LIS_SQDMX@SEL$1
9 - SEL$1 / EMR_DJMX@SEL$1
10 - SEL$1 / EMR_DJMX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("MS_YJ01"."SQID" IS NOT NULL AND ("MS_YJ01"."FPHM" IS NULL OR "MS_YJ01"."FPHM"='NULL') AND "MS_YJ01"."ZFPB"=0 AND "MS_YJ01"."ZXPB"=0 AND
INTERNAL_FUNCTION("MS_YJ01"."YJXH")))
6 - access("MS_YJ01"."KDRQ">=TO_DATE(' 2015-12-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("MS_YJ01"."SQID"="L_LIS_SQDMX"."SYS_NC00021$")
9 - access("EMR_DJMX"."XMID"="L_LIS_SQDMX"."PREHYID")
10 - filter(("EMR_DJMX"."ZSFL" IS NOT NULL AND "EMR_DJMX"."ZSFL"<>9))
--邏輯讀並沒有明顯減少。我才發現oracle錯誤了選擇了索引IDX_MS_YJ01_KDRQ。實際上"MS_YJ01"."YJXH"是主鍵，應該選擇這個索引。
--問題在於這個表很長時間沒有分析(2015/11/23號分析過1次)，
--oracle錯誤的認為("MS_YJ01"."KDRQ">=TO_DATE(' 2015-12-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))的記錄很少。選擇該索引。
--重新分析後問題執行如下：
Plan hash value: 2730452773
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 12 (100)| | 0 |00:00:00.01 | 7 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 20 | 12 (9)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 20 | 12 (9)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |
| 3 | VIEW | VW_SQ_1 | 1 | 1 | 13 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 44 | | | 0 |00:00:00.01 | 7 | 2124K| 2124K| |
| 5 | NESTED LOOPS | | 1 | 1 | 44 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |
| 6 | INLIST ITERATOR | | 1 | | | | | 0 |00:00:00.01 | 7 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| MS_YJ01 | 2 | 1 | 33 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_MS_YJ01 | 2 | 2 | | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 6 | 1025K| 1025K| |
| 9 | TABLE ACCESS BY INDEX ROWID | L_LIS_SQDMX | 0 | 5 | 55 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX RANGE SCAN | IF_L_LIS_SQDMX_DOCREQ_NO | 0 | 5 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 11 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 12 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 0 | 1 | 7 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--像這種語句優化存在一定的不穩定性，我聽過一些人建議刪除列MS_YJ01.KDRQ的統計資訊。
--隨手建立一個指令碼清除日期欄位資訊：
$ cat del_date_stat.sql
prompt del_date_stat.sql owner table_name column_name
DECLARE
distcnt NUMBER;
density NUMBER;
nullcnt NUMBER;
srec DBMS_STATS.statrec;
avgclen NUMBER;
BEGIN
DBMS_STATS.get_column_stats
(
ownname => '&&1'
,tabname => '&&2'
,colname => '&&3'
,distcnt => distcnt
,density => density
,nullcnt => nullcnt
,srec => srec
,avgclen => avgclen
);
DBMS_STATS.delete_column_stats
(
ownname => '&&1'
,tabname => '&&2'
,colname => '&&3'
);
DBMS_STATS.set_column_stats
(
ownname => '&&1'
,tabname => '&&2'
,colname => '&&3'
,distcnt => distcnt
,density => density
,nullcnt => nullcnt
,srec => NULL
,avgclen => avgclen
);
END;
/
--通過以上處理後發現邏輯讀明顯下降到XX。即使in裡面很多邏輯讀最大180.(而原來基本3XXXX多)
SYSTEM@192.168.99.105:1521/dbcn> @tab_lh portal_his MS_YJ01 KDRQ
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT
------------ ---------- ----------- - ------------ ---------- ----------- -------------------- -------------------- ---------- ----------- ------------------- --------- -------------
KDRQ DATE 7 Y 4620980 2.1640E-07 7871866 2012-06-22 01:20:31 2015-12-21 08:43:36 0 1 2015-12-21 08:45:45 NONE
1 row selected.^_^!!!
--按照上面清除日期的最大與最小值後如下:
SYSTEM@192.168.99.105:1521/dbcn> @tab_lh portal_his MS_YJ01 KDRQ
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT
------------ ---------- ----------- - ------------ ---------- ----------- -------------------- -------------------- ---------- ----------- ------------------- --------- ------------
KDRQ DATE 7 Y 4620980 2.1640E-07 -- :: -- :: 0 1 2015-12-21 09:21:15 NONE
1 row selected.^_^!!!
--以後如何控制呢？自己還給仔細考慮，不過開發確實在編寫sql語句上存在問題，我基本可以猜測把需要的表列出來寫出連線條件，發現
--重複，不是distinct，就是使用group by避免重複。難道不會用exists嗎？
--我自己也在思考這個問題，如果我沒有即使發現，團隊的其他人應該發現這種情況，我已經發現開發存在這個毛病，人家寫好的語句，
--根本不考慮合理不合理，直接借用過來。
--我沒什麼沒及時發現？我給自己的理由是我自己出現點總是從第3方的角度考慮優化，這些不外乎建立索引，使用sql profile以及spm
--來穩定執行計劃。而沒有上來考慮sql語句的執行效率與演算法，有點失策。。。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1875828/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL語句優化SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- [20131204]sql語句優化.txtSQL優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- SQL語句的優化SQL優化
- 求助：SQL語句優化SQL優化
- [20201210]sql語句優化.txtSQL優化
- MySQL之SQL語句優化MySql優化
- SQL語句優化（轉載）SQL優化
- 常用SQL語句優化技巧SQL優化
- Oracle之sql語句優化OracleSQL優化
- SQL 語句的優化方法SQL優化
- 優化 SQL 語句的步驟優化SQL
- 一個SQL語句的優化SQL優化
- Oracle SQL語句優化之UNIONOracleSQL優化
- SQL語句操作符優化SQL優化
- 關於sql語句的優化SQL優化
- SQL語句優化技術分析SQL優化
- SQL語句優化方法30例SQL優化
- 一條sql語句的優化SQL優化
- sql語句的優化案例分析SQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- 資料庫效能優化之SQL語句優化資料庫優化SQL
- 淺談mysql配置優化和sql語句優化MySql優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 對sql語句的優化問題SQL優化
- oracle效能問題：sql語句優化OracleSQL優化
- SQL語句優化方法30例(轉)SQL優化
- SQL語句優化－－十條經驗SQL優化
- 優化SQL 語句 in 和not in 的替代方案優化SQL
- ORACLE SQL語句優化技術分析OracleSQL優化
- 通過SQL PROFILE自動優化SQL語句SQL優化