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優化
- 一個SQL效能問題的優化探索SQL優化
- MYSQL 阿里的一個sql優化問題MySql阿里優化
- 從一條問題SQL優化看SQL TransformationSQL優化ORM
- SQL優化--not in和or出的問題SQL優化
- 對sql語句的優化問題SQL優化
- 資料庫sql的優化問題的面試題資料庫SQL優化面試題
- 記錄一次SQL函式和優化的問題SQL函式優化
- mysql 刪表引出的問題MySql
- oracle效能問題：sql語句優化OracleSQL優化
- 優化案例--重建索引引發的sql效能問題優化索引SQL
- python安裝MySQL庫引出的一些問題PythonMySql
- 從Linux2.6.8核心的一個TSO/NATbug引出的網路問題排查觀點(附一個skb的優化點)Linux優化
- sql優化專題SQL優化
- SQL優化（一）SQL優化
- 一個sql的優化SQL優化
- 基於mysql資料庫 關於sql優化的一些問題MySql資料庫優化
- 記一個效能優化問題優化
- 一道題引出的Event LoopOOP
- java面試一日一題：如何優化sqlJava面試優化SQL
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 遠端執行緒注入引出的問題執行緒
- 關於一個使用者SQL慢查詢問題的分析及優化SQL優化
- 踩坑CBO，解決那些坑爹的SQL優化問題SQL優化
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- SQL優化--用各種hints優化一條SQLSQL優化
- Mysql 優化——分析表讀寫和sql效率問題MySql優化
- 一個SQL優化SQL優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- 凸優化問題優化
- 效能優化問題優化
- 一個SQL效能問題的優化探索（二）(r11筆記第38天)SQL優化筆記
- 荷蘭國旗問題引出的三色排序排序
- 數值最優化—優化問題的解(二)優化
- 【調優】設計問題還是優化問題？優化
- oracle優化一例之sql優化Oracle優化SQL
- 一個SQL語句的優化SQL優化