[20231207]開發不應該這樣寫sql4.txt
[20231207]開發不應該這樣寫sql4.txt
--//最近在最佳化sql語句,發現另外一種風格,實際上以前也遇到過,感覺這就像一種病,會傳染只要一個這樣寫後面的要麼跟進要麼
--//不改。我覺得開發應該感謝exadata,不然我們的生產系統估計會垮掉。
1.環境:
XXXXXX> @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
2.問題語句:
XXXXXX> @ sql_id ag76s7zum6z3b
--SQL_ID = ag76s7zum6z3b
SELECT MZ.BRID AS PATIENT_ID,
TO_CHAR(GH.SBXH) AS OUTPATIENT_ID,
:"SYS_B_0" AS INHOSPITAL_ID, JZLS.JZXH AS VISIT_ID, MZ.MZHM AS CARD_NO,
GH.GHSJ AS VISIT_TIME, MZ.BRXM AS PATIENT_NAME, MZ.BRXB AS PATIENT_SEX,
MZ.CSNY AS PATIENT_BIRTHDATE, MZ.SFZH AS IDENTITY_CARD_ID,
:"SYS_B_1" AS PATIENT_TYPE,
(SELECT GY_DMZD.DMMC FROM XXXXXX_YYY.GY_DMZD WHERE GY_DMZD.DMLB = :"SYS_B_2" AND GY_DMZD.DMSB = MZ.MZDM) AS PATIENT_NATION,
(SELECT GY_SSXWH.NAME FROM XXXXXX_YYY.GY_SSXWH WHERE GY_SSXWH.SBXH = MZ.XZZ_SQS) AS FAMILY_ADDRESS_PROVINCE,
(SELECT GY_SSXWH.NAME FROM XXXXXX_YYY.GY_SSXWH WHERE GY_SSXWH.SBXH = MZ.XZZ_S) AS FAMILY_ADDRESS_CITY,
MZ.LXDZ AS FAMILY_ADDRESS_DETAIL, MZ.LXDH AS MOBILE_PHONE,
GY.KSDM AS DEPART_CODE, GY.KSMC AS DEPART_NAME,
KS.KSDM AS SUB_DEPART_CODE, KS.KSMC AS SUB_DEPART_NAME,
(SELECT CSZ FROM GY_XTCS WHERE CSMC = :"SYS_B_3") AS HOS_ID
FROM XXXXXX_YYY.MS_BRDA MZ
LEFT JOIN XXXXXX_YYY.MS_GHMX GH
ON MZ.BRID = GH.BRID
LEFT JOIN XXXXXX_YYY.MS_GHKS KS
ON GH.KSDM = KS.KSDM
LEFT JOIN XXXXXX_YYY.GY_KSDM GY
ON KS.MZKS = GY.KSDM
LEFT JOIN XXXXXX_YYY.YS_MZ_JZLS JZLS
ON JZLS.GHXH = GH.SBXH
WHERE ((:card_no = :"SYS_B_4" OR :card_no IS NULL) OR MZ.MZHM = :card_no)
AND ((:patient_id = :"SYS_B_5" OR :patient_id IS NULL) OR MZ.BRID = :patient_id)
AND ((:patientName = :"SYS_B_6" OR :patientName IS NULL) OR MZ.BRXM = :patientName)
AND ((:patientSex = :"SYS_B_7" OR :patientSex IS NULL) OR MZ.BRXB = :patientSex)
AND ((:deptName = :"SYS_B_8" OR :deptName IS NULL) OR GY.KSMC = :deptName);
--//我做了格式化處理,原始程式程式碼就一行。
--//可以看出開發的本意,就是帶入任意引數都可以查詢。可惜oracle 最佳化器沒有這麼智慧,無法選擇合理的執行路徑。
--//根據輸入選擇合適的索引,導致選擇全部掃描。
SYS@192.168.100.141:1621/dbcn/dbcn1> @ seg2 %.MS_BRDA
SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
1656 XXXXXX_YYY MS_BRDA TABLE XXXXXX_YYY 211968 52 852001
--//1.6G.
--//執行計劃如下:
Plan hash value: 1015797529
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 60543 (100)| | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | GY_DMZD | 1 | 20 | 2 (0)| 00:00:01 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_GY_DMZD | 1 | | 1 (0)| 00:00:01 | 1025K| 1025K| |
| 3 | TABLE ACCESS BY INDEX ROWID | GY_SSXWH | 1 | 13 | 2 (0)| 00:00:01 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_GY_SSXWH | 1 | | 1 (0)| 00:00:01 | 1025K| 1025K| |
| 5 | TABLE ACCESS BY INDEX ROWID | GY_SSXWH | 1 | 13 | 2 (0)| 00:00:01 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_GY_SSXWH | 1 | | 1 (0)| 00:00:01 | 1025K| 1025K| |
| 7 | TABLE ACCESS BY INDEX ROWID | GY_XTCS | 1 | 18 | 2 (0)| 00:00:01 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_GY_XTCS | 1 | | 1 (0)| 00:00:01 | 1025K| 1025K| |
| 9 | NESTED LOOPS OUTER | | 805 | 123K| 60543 (1)| 00:12:07 | | | |
|* 10 | FILTER | | | | | | | | |
|* 11 | HASH JOIN RIGHT OUTER | | 687 | 99615 | 58034 (1)| 00:11:37 | 2782K| 2782K| 1588K (0)|
| 12 | TABLE ACCESS STORAGE FULL | GY_KSDM | 1099 | 24178 | 7 (0)| 00:00:01 | 1025K| 1025K| |
|* 13 | HASH JOIN RIGHT OUTER | | 687 | 84501 | 58027 (1)| 00:11:37 | 2596K| 2596K| 1573K (0)|
| 14 | TABLE ACCESS STORAGE FULL | MS_GHKS | 429 | 11583 | 5 (0)| 00:00:01 | 1025K| 1025K| |
| 15 | NESTED LOOPS OUTER | | 687 | 65952 | 58022 (1)| 00:11:37 | | | |
|* 16 | TABLE ACCESS STORAGE FULL | MS_BRDA | 92 | 6532 | 57498 (1)| 00:11:30 | 1025K| 1025K| |
| 17 | TABLE ACCESS BY INDEX ROWID| MS_GHMX | 8 | 200 | 10 (0)| 00:00:01 | | | |
|* 18 | INDEX RANGE SCAN | IDX_MS_GHMX_BRID | 8 | | 2 (0)| 00:00:01 | 1025K| 1025K| |
| 19 | TABLE ACCESS BY INDEX ROWID | YS_MZ_JZLS | 1 | 12 | 4 (0)| 00:00:01 | | | |
|* 20 | INDEX RANGE SCAN | I_YS_MZ_JZLS_GHXH | 1 | | 2 (0)| 00:00:01 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------
SYS@192.168.100.141:1621/dbcn/dbcn1> @ bind_cap ag76s7zum6z3b :card_no
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- --------- -------- ---------- ------------------- --------------- ------------
ag76s7zum6z3b 0 YES :CARD_NO 5 32 2023-12-06 09:54:09 VARCHAR2(32) 90377195
1 YES :CARD_NO 5 32 2023-12-05 11:29:35 VARCHAR2(32) 02666713
2 YES :CARD_NO 5 32 2023-12-06 19:33:57 VARCHAR2(32) 91544379
SYS@192.168.100.141:1621/dbcn/dbcn1> @ bind_cap_awr ag76s7zum6z3b ''
no rows selected
--//這樣語句在awr歷史表還沒有記錄。可以發現在共享池抓到的sql語句都是帶入card_no引數的。
--//我多次提過不要這樣寫sql語句,這不是在學校寫家庭作業,這是生產系統!!這類語句在生產系統還有一大堆,真不知道現在的畢業生
--//如何畢業的。
來自 “ ITPUB部落格 ” ,連結:https://blog.itpub.net/267265/viewspace-2999834/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20220121]開發不應該這樣寫sql2.txtSQL
- [20220124]開發不應該這樣寫sql3.txtSQL
- [20220109]開發不應該這樣寫SQL語句.txtSQL
- 寫這段程式碼的人該不該被開除?
- markdown裡的微積分中dt應該這樣寫
- 面試?或許你應該這樣面試
- 作為一個IT技術開發,應該樹立這樣一個信念!
- Clipped.js | Webpack 應該這樣用JSWeb
- MVP設計模式應該這樣掌握MVP設計模式
- mac 鍵盤應該這樣改鍵Mac
- 一份優秀的資料分析報告應該這樣寫
- 轉行做Java開發應該怎樣學習?Java
- MySQL備份指令碼,應該這麼寫MySql指令碼
- 現在寫 PHP,你應該知道這些PHP
- “掃蕩”、“自動尋路”這樣的簡化遊戲設計該不該存在?遊戲設計
- Yann LeCun:未來的AI晶片應該這樣做Yann LeCunAI晶片
- 年終了,程式設計師應該這樣談加薪!程式設計師
- 企業通關必備,iPaaS應該這樣搭建
- 谷歌重回中國的姿勢應該是這樣谷歌
- 教育小程式原始碼開發熱度不減,教培機構應該抓住這個機遇原始碼
- PHP做api開發離不開簽名驗證,我這樣設計PHPAPI
- 如果開發一個綜合性專案應該怎樣準備
- 當代前端應該怎麼寫這個hello world?前端
- 這就是我心目中獨立遊戲應該有的樣子遊戲
- Python就該這樣學?Python
- 像我這樣的人搞程式開發
- Python遠端登陸伺服器應該這樣玩Python伺服器
- 關於 JavaScript 中的特殊函式,你應該這樣看JavaScript函式
- Remoting服務端和客戶端程式該這樣模式來寫REM服務端客戶端模式
- SQL Story(十)————遊標的應該與不應該 (轉)SQL
- 跨境電商app應該如何開發?APP
- Laravel 路由這樣寫 "{article}"Laravel路由
- 還在為找資料而發愁嗎?看完這篇應該再也不會了
- 這樣delete居然不走索引delete索引
- 為什麼我們應該像蓋房子那樣寫軟體?
- 這樣的開源應用你相信它嗎?
- vue3+ts+vite2環境變數應該這樣使用VueVite變數
- 作為遊戲開發商,應該怎樣去提升遊戲的變現能力?遊戲開發