SQL優化引出的問題（二）
新接手了一個資料庫環境，有使用者抱怨速度慢，經過簡單的檢查，找到了一個問題SQL語句。
這裡繼續尋找導致問題的真正原因。
SQL優化引出的問題（一）：http://yangtingkun.itpub.net/post/468/458343
上面一篇文章找到的問題，並通過使用HINT解決了問題，但是並沒有找到問題的真正原因，而有時候瞭解問題的原因比解決問題更加重要。
首先要說明的是，表的建立很不規範，表上居然沒有建立主外來鍵的關係，只是主鍵列上建立了唯一索引。
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_DRUG', 'INF_PRODUCT_PROPERTY')
3 AND CONSTRAINT_TYPE IN ('P', 'R');
no rows selected
SQL> SELECT DISTINCT CONSTRAINT_TYPE FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_DRUG', 'INF_PRODUCT_PROPERTY');
C
-
C
索引的建立也有問題：
SQL> SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_DRUG', 'INF_PRODUCT_PROPERTY');
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- ------------------------- --------------- ---------------
INF_DRUG INF_DRUG_HAOCAI_FLAG HAOCAI_FLAG 1
INF_DRUG INF_DRUG_NAME DRUG_NAME 1
INF_DRUG INDEX_DRUG_ID DRUG_ID 1
INF_DRUG INF_DRUG_DRUG_CODE DRUG_CODE 1
INF_PRODUCT INF_PRODUCT_ENABLE_FLAG ENABLE_FLAG 1
INF_PRODUCT INF_PRODUCT_ID PRODUCT_ID 1
INF_PRODUCT INF_DRUG_ID DRUG_ID 1
INF_PRODUCT INF_FACTORY_ID FACTORY_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PROD_ID PRODUCT_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PLAT_ID PLAT_ID 1
INF_PRODUCT_PROPERTY INF_PROPERTY_ID PROPERTY_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PLAT PLAT_ID 1
INF_PRODUCT_PROPERTY INF_PRODUCT_PLAT PRODUCT_ID 2
13 rows selected.
一些BTREE索引建立在選擇度極差的列上：
SQL> SELECT INDEX_NAME, INDEX_TYPE, DISTINCT_KEYS FROM USER_INDEXES
2 WHERE INDEX_NAME IN ('INF_DRUG_HAOCAI_FLAG', 'INF_PRODUCT_ENABLE_FLAG');
INDEX_NAME INDEX_TYPE DISTINCT_KEYS
------------------------- --------------------------- -------------
INF_DRUG_HAOCAI_FLAG NORMAL 3
INF_PRODUCT_ENABLE_FLAG NORMAL 2
應該說，這些設計上的問題從一定程度上導致了Oracle無法產生優秀的執行計劃。但是這些並不是問題的真正原因。即使沒有主外來鍵關係，即使建立了不合適的索引，Oracle通過分析統計資訊也應該將不合理的執行計劃排除掉。
即使Oracle沒有選擇最優的計劃，也不應該選擇效率如此之差的計劃，顯然Oracle這裡存在問題，或者是Oracle對統計資訊的判斷有誤，或者統計資訊的收集存在問題。
再次檢查執行計劃：
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
9 FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG C
10 WHERE B.PLAT_ID=59
11 AND A.ENABLE_FLAG='1'
12 AND A.PRODUCT_ID = B.PRODUCT_ID
13 AND A.DRUG_ID = C.DRUG_ID
14 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
17 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
18 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
19 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
20 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
21 ) A
22 WHERE ROWNUM <= 40
23 )
24 WHERE ROW_NUM >= 31
25 ;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
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")
26 rows selected.
從第6步看，Oracle認為INF_PRODUCT全表掃描的代價僅為1，從而導致了問題的產生。
但是觀察統計資訊，沒有發現異常的統計資訊：
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM USER_TABLES
2 WHERE TABLE_NAME IN ('INF_PRODUCT', 'INF_PRODUCT_PROPERTY', 'INF_DRUG');
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
INF_DRUG 58535 691
INF_PRODUCT 61344 502
INF_PRODUCT_PROPERTY 495212 7090
看來只有懷疑Oracle的CBO了。
為了更好的進行測試，將產品環境的3張表匯出到本地測試環境，分別匯入10.2.0.3和10.2.0.1兩個環境中。而出現問題的資料庫版本為10203。
先看10203中的表現：
SQL> CONN YANGTK/YANGTK@YTK
已連線。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
9 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
10 WHERE B.PLAT_ID=59
11 AND A.ENABLE_FLAG='1'
12 AND A.PRODUCT_ID = B.PRODUCT_ID
13 AND A.DRUG_ID = C.DRUG_ID
14 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
17 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
18 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
19 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
20 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
21 ) A
22 WHERE ROWNUM <= 40
23 )
24 WHERE ROW_NUM >= 31
25 ;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 820377798
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 4602 | 62 (2)| 00:00:01 |
|* 1 | VIEW | | 39 | 4602 | 62 (2)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 39 | 3627 | 62 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 32 | 2688 | 30 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL| INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| INF_PRODUCT | 1 | 22 | 0 (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")
已選擇26行。
問題重現了，執行計劃中的第6步COST出奇的小，導致了Oracle選擇了全表掃描作為執行計劃，而這裡如果認為的改為INDEX，則代價為：
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT /*+ ORD_ORDERED NO_EXPAND USE_NL(A C) USE_NL(B) FULL(C) INDEX(A INF_DRUG_ID) */
9 A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
10 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
11 WHERE B.PLAT_ID=59
12 AND A.ENABLE_FLAG='1'
13 AND A.PRODUCT_ID = B.PRODUCT_ID
14 AND A.DRUG_ID = C.DRUG_ID
15 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
17 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
18 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
19 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
20 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
21 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
22 ) A
23 WHERE ROWNUM <= 40
24 )
25 WHERE ROW_NUM >= 31
26 ;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4033682336
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 249 (0)| 00:00:03 |
|* 1 | VIEW | | 40 | 4720 | 249 (0)| 00:00:03 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 249 (0)| 00:00:03 |
| 4 | NESTED LOOPS | | 32 | 2688 | 217 (0)| 00:00:03 |
| 5 | TABLE ACCESS FULL | INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| INF_PRODUCT | 1 | 22 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INF_DRUG_ID | 2 | | 1 (0)| 00:00:01 |
|* 8 | 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 (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("A"."DRUG_ID"="C"."DRUG_ID")
8 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")
已選擇27行。
可以看到，使用索引來進行連線的代價是3，從而導致第一層NESTED LOOP的代價為217，遠遠超過了剛才全表掃描的代價。
現在看看10.2.0.1中的情況：
SQL> CONN YANGTK/YANGTK@YTK102
已連線。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
9 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
10 WHERE B.PLAT_ID=59
11 AND A.ENABLE_FLAG='1'
12 AND A.PRODUCT_ID = B.PRODUCT_ID
13 AND A.DRUG_ID = C.DRUG_ID
14 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
15 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
17 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
18 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
19 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
20 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
21 ) A
22 WHERE ROWNUM <= 40
23 )
24 WHERE ROW_NUM >= 31
25 ;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2045796448
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 139 (0)| 00:00:02 |
|* 1 | VIEW | | 40 | 4720 | 139 (0)| 00:00:02 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 139 (0)| 00:00:02 |
| 4 | NESTED LOOPS | | 32 | 2688 | 107 (0)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| INF_PRODUCT | 52835 | 1135K| 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | INF_PRODUCT_ENABLE_FLAG | | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| INF_DRUG | 1 | 62 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | INDEX_DRUG_ID | 1 | | 0 (0)| 00:00:01 |
|* 9 | 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 - 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")
已選擇29行。
在10.2.0.1中，Oracle採用了不同的執行計劃，且不論這個執行計劃是否是最優的，最起碼它避免了NESTED LOOP中被驅動表的全表掃描。
僅從這一點看，這個執行計劃就滿足要求了。那麼通過HINT使得10201中出現剛剛10203中的問題執行計劃，並檢查這個時候INF_PRODUCT的全表掃描的代價：
SQL> EXPLAIN PLAN FOR
2 SELECT /*+ FIRST_ROWS */*
3 FROM
4 (
5 SELECT ROWNUM ROW_NUM, A.*
6 FROM
7 (
8 SELECT /*+ ORD_ORDERED NO_EXPAND USE_NL(A C) USE_NL(B) FULL(C) FULL(A) */
9 A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
10 FROM INF_DRUG C, INF_PRODUCT A, INF_PRODUCT_PROPERTY B
11 WHERE B.PLAT_ID=59
12 AND A.ENABLE_FLAG='1'
13 AND A.PRODUCT_ID = B.PRODUCT_ID
14 AND A.DRUG_ID = C.DRUG_ID
15 AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0
16 OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0
17 OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0
18 OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0
19 OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0
20 OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0
21 OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0)
22 ) A
23 WHERE ROWNUM <= 40
24 )
25 WHERE ROW_NUM >= 31
26 ;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 820377798
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 4720 | 13658 (8)| 00:02:44 |
|* 1 | VIEW | | 40 | 4720 | 13658 (8)| 00:02:44 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 40 | 3720 | 13658 (8)| 00:02:44 |
| 4 | NESTED LOOPS | | 32 | 2688 | 13626 (8)| 00:02:44 |
| 5 | TABLE ACCESS FULL| INF_DRUG | 58535 | 3544K| 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| INF_PRODUCT | 1 | 22 | 118 (7)| 00:00:02 |
|* 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")
已選擇26行。
SQL> EXPLAIN PLAN FOR SELECT * FROM INF_PRODUCT;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3581118231
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61344 | 6769K| 120 (7)| 00:00:02 |
| 1 | TABLE ACCESS FULL| INF_PRODUCT | 61344 | 6769K| 120 (7)| 00:00:02 |
---------------------------------------------------------------------------------
已選擇8行。
這個執行計劃中的代價才是一個合理的值，對比10201和10203兩種不同的情況，基本上可以確定10203中CBO存在bug，導致計算NESTED LOOP中的全表掃描代價過低。
根據上面的問題描述，到metalink中搜尋了一下，結果發現Doc ID: Note:4878299.8描述了這個問題，這個patch影響的版本為10104和10203，Oracle在10.2.0.4和11.1.0.6中解決了這個bug。
Oracle給出的臨時解決方法正是使用INDEX的HINT。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-218030/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化引出的問題（一）SQL優化
- sql優化講課中引出的各種問題！SQL優化
- SQL優化--not in和or出的問題SQL優化
- 數值最優化—優化問題的解(二)優化
- 對sql語句的優化問題SQL優化
- 資料庫sql的優化問題的面試題資料庫SQL優化面試題
- 一個SQL效能問題的優化探索SQL優化
- MYSQL 阿里的一個sql優化問題MySql阿里優化
- mysql 刪表引出的問題MySql
- 從一條問題SQL優化看SQL TransformationSQL優化ORM
- oracle效能問題：sql語句優化OracleSQL優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- 優化Oracle with全表掃描的問題(二)優化Oracle
- 優化案例--重建索引引發的sql效能問題優化索引SQL
- 一個SQL效能問題的優化探索（二）(r11筆記第38天)SQL優化筆記
- sql優化專題SQL優化
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- SQL優化（二）（聯合索引的使用）SQL優化索引
- 遠端執行緒注入引出的問題執行緒
- 記錄一次SQL函式和優化的問題SQL函式優化
- 踩坑CBO，解決那些坑爹的SQL優化問題SQL優化
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- Mysql 優化——分析表讀寫和sql效率問題MySql優化
- 凸優化問題優化
- 效能優化問題優化
- SQL優化案例-正確的使用索引（二）SQL優化索引
- MySQL之SQL優化詳解（二）MySql優化
- 荷蘭國旗問題引出的三色排序排序
- 【調優】設計問題還是優化問題？優化
- SQL最佳化問題SQL
- go的編譯優化問題Go編譯優化
- 每秒執行6000的簡單SQL優化（二）SQL優化
- Oracle某行系統SQL優化案例(二)OracleSQL優化
- Sql優化（二） 快速計算Distinct CountSQL優化
- SQL*Loader 筆記 （二） 效能優化SQL筆記優化
- sql優化 面試必問【簡答】SQL優化面試
- Oracle 調優確定存在問題的SQLOracleSQL
- 由“香蕉”引出的字串匹配演算法的問題字串匹配演算法