SQL優化引出的問題（一）
新接手了一個資料庫環境，有使用者抱怨速度慢，經過簡單的檢查，找到了一個問題SQL語句。
由於問題和這個SQL本身關係很緊密，因此無法通過其他例子來進行模擬，而且即使將SQL儘量簡化的工作也很難進行，因為可能去掉部分條件問題就消失了。
因此只能在保留問題的條件下儘量的簡化SQL，最終問題SQL如下：
SQL> SET AUTOT TRACE
SQL> SET TIMING ON
SQL> SELECT /*+ FIRST_ROWS */*
2 FROM
3 (
4 SELECT ROWNUM ROW_NUM, A.*
5 FROM
6 (
7 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
8 FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG C
9 WHERE B.PLAT_ID=59
10 AND A.ENABLE_FLAG='1'
11 AND A.PRODUCT_ID = B.PRODUCT_ID
12 AND A.DRUG_ID = C.DRUG_ID
13 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
14 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
16 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
17 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
18 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
19 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
20 ) A
21 WHERE ROWNUM <= 40
22 )
23 WHERE ROW_NUM >= 31
24 ;
10 rows selected.
Elapsed: 00:00:19.12
Execution Plan
----------------------------------------------------------
Plan hash value: 820377798
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 4602 | 113 (1)| 00:00:02 |
|* 1 | VIEW | | 39 | 4602 | 113 (1)| 00:00:02 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 39 | 3627 | 113 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 32 | 2688 | 81 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL| INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| INF_PRODUCT | 1 | 22 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - filter("A"."ENABLE_FLAG"='1' AND "A"."DRUG_ID"="C"."DRUG_ID" AND
(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0
OR INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0
OR INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR
INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0))
7 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
835381 consistent gets
0 physical reads
0 redo size
1010 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
一個簡單的3張表的關聯分頁查詢，居然用了將近20秒的時間，這是很不正常的。從統計資訊也可以看到，邏輯讀居然有83萬。
檢查是什麼問題導致查詢效率低下。從執行計劃中，很容易就找到了問題所在，INF_PRODUCT表作為NESTED LOOP的被驅動表，居然選擇了全表掃描。
檢查INF_PRODUCT表中的記錄數：
SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM INF_PRODUCT;
COUNT(*)
----------
61356
Elapsed: 00:00:00.03
表中記錄有6W多條，而Oracle錯誤的選擇了INF_PRODUCT作為被驅動表，且沒有使用索引。這實際上構成了一個笛卡兒積。這也是這個SQL效率低的根本原因。
問題定位了，下面就需要進行兩方面的工作，一是解決問題，二是找到造成問題的根本原因。
由於時間緊迫，首先找到問題的解決方法，然後再去定位問題的原因。
最簡單的解決方法莫過於使用HINT來改變當前SQL的執行計劃，這種方法的好處是不會對其他的SQL產生影響。
SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */*
2 FROM
3 (
4 SELECT ROWNUM ROW_NUM, A.*
5 FROM
6 (
7 SELECT /*+ INDEX(A) */ A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
8 FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG C
9 WHERE B.PLAT_ID=59
10 AND A.ENABLE_FLAG='1'
11 AND A.PRODUCT_ID = B.PRODUCT_ID
12 AND A.DRUG_ID = C.DRUG_ID
13 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
14 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
16 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
17 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
18 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
19 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
20 ) A
21 WHERE ROWNUM <= 40
22 )
23 WHERE ROW_NUM >= 31
24 ;
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2045796448
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 139 (0)|
|* 1 | VIEW | | 40 | 4720 | 139 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 139 (0)|
| 4 | NESTED LOOPS | | 32 | 2688 | 107 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| INF_PRODUCT | 52835 | 1135K| 3 (0)|
|* 6 | INDEX RANGE SCAN | INF_PRODUCT_ENABLE_FLAG | 52835 | | 1 (0)|
|* 7 | TABLE ACCESS BY INDEX ROWID| INF_DRUG | 1 | 62 | 1 (0)|
|* 8 | INDEX UNIQUE SCAN | INDEX_DRUG_ID | 1 | | 0 (0)|
|* 9 | INDEX RANGE SCAN | INF_PRODUCT_PLAT | 1 | 9 | 1 (0)|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM">=31)
2 - filter(ROWNUM<=40)
6 - access("A"."ENABLE_FLAG"='1')
7 - filter(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0 OR
INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0 OR
INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR
INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0)
8 - access("A"."DRUG_ID"="C"."DRUG_ID")
9 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2568 consistent gets
0 physical reads
0 redo size
1201 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
使用HINT後，問題SQL的執行時間從19秒下降到了0.1秒，邏輯讀從83W下降到了2500。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-217988/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 記錄一次SQL函式和優化的問題SQL函式優化
- 資料庫sql的優化問題的面試題資料庫SQL優化面試題
- mysql 刪表引出的問題MySql
- [20181119]使用sql profile優化問題.txtSQL優化
- 從閉包引出來的一系列問題
- python安裝MySQL庫引出的一些問題PythonMySql
- sql優化專題SQL優化
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 基於mysql資料庫 關於sql優化的一些問題MySql資料庫優化
- 記一個效能優化問題優化
- java面試一日一題：如何優化sqlJava面試優化SQL
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- SQL最佳化問題SQL
- Oracle 調優確定存在問題的SQLOracleSQL
- MySQL SQL優化案例(一)MySql優化
- 凸優化問題優化
- 數值最優化—優化問題的解(二)優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- [20231029]使用cygwin調式bash shell引出的問題.txt
- 荷蘭國旗問題引出的三色排序排序
- Oracle優化案例-緊急處理一條sql引起cpu使用率99%的問題（十六）Oracle優化SQL
- go的編譯優化問題Go編譯優化
- sql優化 面試必問【簡答】SQL優化面試
- Next.js-頁面重複渲染引出的水合問題JS
- 面試三輪我倒在了一道sql題上——sql效能優化面試SQL優化
- 斜率優化（凸包優化）DP問題acm優化ACM
- Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）Oracle優化索引SQL
- [20181114]一條sql語句的優化.txtSQL優化
- 記一次Prometheus代理效能優化問題Prometheus優化
- 【Oracle】Oracle wrong result一則（優化器問題）Oracle優化
- pl/sql developer的一個小問題SQLDeveloper
- [20211210]優化遇到的奇怪問題.txt優化
- MySQL之SQL優化詳解（一）MySql優化
- SQL優化的方法論SQL優化
- 03-凸優化問題優化
- 資料庫優化 - SQL優化資料庫優化SQL
- sql優化之邏輯優化SQL優化
- SQL SERVER優化SQLServer優化