SQL优化引出的问题（一）

新接手了一个数据库环境，有用户抱怨速度慢，经过简单的检查，找到了一个问题SQL语句。

由于问题和这个SQL本身关系很紧密，因此无法通过其他例子来进行模拟，而且即使将SQL尽量简化的工作也很难进行，因为可能去掉部分条件问题就消失了。

因此只能在保留问题的条件下尽量的简化SQL，最终问题SQL如下：

SQL> SET AUTOT TRACEITPUB个人空间(_'uP/Z#\
SQL> SET TIMING ONITPUB个人空间0oRC.W*qOt4eG
SQL> SELECT /*+ FIRST_ROWS */*ITPUB个人空间*r;z%d~wi$s^c~q
  2  FROM
5U;W9_$dVFRV f0  3  (ITPUB个人空间G.y"I.XC+pR
  4     SELECT ROWNUM ROW_NUM, A.*ITPUB个人空间P
['|O3x\:}|7@
  5     FROM
Ty2_1`,dnz0  6     (ITPUB个人空间qTkz4W)v/n
  7             SELECT A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
E3iuS{%{-W aL0  8             FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG C ITPUB个人空间 x'e6z;j4c;g5I
  9             WHERE B.PLAT_ID=59
`A8i{F$~#Y&d Pr Kl0 10             AND A.ENABLE_FLAG='1' ITPUB个人空间-V(AE}!P#[
 11             AND A.PRODUCT_ID = B.PRODUCT_ID
u*H4Y)s&@#rF\~ZO0 12             AND A.DRUG_ID = C.DRUG_ID  ITPUB个人空间O5X2O P&XhY
 13             AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0 ITPUB个人空间 o$c jbd\ Q8j%reV
 14                     OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0 ITPUB个人空间Gv3G:~V!]:xd
 15                     OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0 ITPUB个人空间nI2\J3I n
 16                     OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0 ITPUB个人空间~TbdG[8_)q
 17                     OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0 
$Db;L,s+US th$]B0 18                     OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0 ITPUB个人空间C1[-Oq"\0u9m
 19                     OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0) 
'AH7F7G V$F&l,V,X0 20     ) A ITPUB个人空间6p'}T2c'b*_ W&H
rGN
 21     WHERE ROWNUM <= 40
S*ZD%j2XjkU}:p0 22  )
6GHU g7zr?\0 23  WHERE ROW_NUM >= 31ITPUB个人空间 Dd+f._;`;z
 24  ;

10 rows selected.

Elapsed: 00:00:19.12

Execution Plan
k{j"^$L;j0----------------------------------------------------------ITPUB个人空间(@ YDN+R5QY,p
Plan hash value: 820377798

------------------------------------------------------------------------------------------
/h&g^_5_U4Z0| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间4m P*B}K G3w;BG
------------------------------------------------------------------------------------------ITPUB个人空间%U d&@-g.Z#[W/^5{[
|   0 | SELECT STATEMENT      |                  |    39 |  4602 |   113   (1)| 00:00:02 |
'K_k*X8E8}v0|*  1 |  VIEW                 |                  |    39 |  4602 |   113   (1)| 00:00:02 |ITPUB个人空间E^"N9MSP
|*  2 |   COUNT STOPKEY       |                  |       |       |            |          |
-P0i&A2J aCO0|   3 |    NESTED LOOPS       |                  |    39 |  3627 |   113   (1)| 00:00:02 |ITPUB个人空间p5K#|0Gh]#Q
|   4 |     NESTED LOOPS      |                  |    32 |  2688 |    81   (2)| 00:00:02 |ITPUB个人空间o q3`3b[
|   5 |      TABLE ACCESS FULL| INF_DRUG         | 58535 |  3544K|     2   (0)| 00:00:01 |ITPUB个人空间c-?+u[J\Z
|*  6 |      TABLE ACCESS FULL| INF_PRODUCT      |     1 |    22 |     1   (0)| 00:00:01 |
,R+sY+_NWzA0|*  7 |     INDEX RANGE SCAN  | INF_PRODUCT_PLAT |     1 |     9 |     1   (0)| 00:00:01 |ITPUB个人空间-D&v j~ }U)d
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间 T9h"y)cnl"H3p!_E~D
---------------------------------------------------

   1 - filter("ROW_NUM">=31)
W4i1I;IN6NJ0   2 - filter(ROWNUM<=40)
?^OG)G]LKi"U0   6 - filter("A"."ENABLE_FLAG"='1' AND "A"."DRUG_ID"="C"."DRUG_ID" ANDITPUB个人空间 ifJ;N'v%b^7T6}O
              (INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0ITPUB个人空间/os9IY%Y|M
              OR INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0ITPUB个人空间^2c]#T#U#h8x)|
              OR INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 ORITPUB个人空间%WT-P;GT
              INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 OR INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0))
t e,oZr5k0   7 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")

ITPUB个人空间yv6p*Ds'`
StatisticsITPUB个人空间4RO:[&vrT
----------------------------------------------------------
;d`9MV'h7CX7}0          0  recursive calls
:zd\+i[F`(|,F:i,X0          0  db block getsITPUB个人空间3T:gfmbq:gl9}S9h
     835381  consistent getsITPUB个人空间%i)s"qIV4X
          0  physical reads
#^l3},G9`.d|A VF0          0  redo size
[!B0I_Ai0       1010  bytes sent via SQL*Net to clientITPUB个人空间;SK7i5d|(n.X#w
        492  bytes received via SQL*Net from clientITPUB个人空间aG$|AZ I6k)U
          2  SQL*Net roundtrips to/from client
d.F7\3Ca0~m0E+V0          0  sorts (memory)
YB'|0~k o&d$PV0          0  sorts (disk)
Wb |8K(p)I0|f.?h7P0         10  rows processed

一个简单的3张表的关联分页查询，居然用了将近20秒的时间，这是很不正常的。从统计信息也可以看到，逻辑读居然有83万。

检查是什么问题导致查询效率低下。从执行计划中，很容易就找到了问题所在，INF_PRODUCT表作为NESTED LOOP的被驱动表，居然选择了全表扫描。

检查INF_PRODUCT表中的记录数：

SQL> SET AUTOT OFFITPUB个人空间5a8ZC
A5]0?Z7v
SQL> SELECT COUNT(*) FROM INF_PRODUCT;

  COUNT(*)ITPUB个人空间Rb!l4\)y7w
----------
(f8}zZ$RG0     61356

Elapsed: 00:00:00.03

表中记录有6W多条，而Oracle错误的选择了INF_PRODUCT作为被驱动表，且没有使用索引。这实际上构成了一个笛卡儿积。这也是这个SQL效率低的根本原因。

问题定位了，下面就需要进行两方面的工作，一是解决问题，二是找到造成问题的根本原因。

由于时间紧迫，首先找到问题的解决方法，然后再去定位问题的原因。

最简单的解决方法莫过于使用HINT来改变当前SQL的执行计划，这种方法的好处是不会对其他的SQL产生影响。

SQL> SET AUTOT TRACE
{T9u!B _&CO2g0SQL> SELECT /*+ FIRST_ROWS */*
T7l-\'V N*?h)K0  2  FROMITPUB个人空间;I[o\~*x{ _l}F0ze
  3  (ITPUB个人空间yF2HnW)Pe
K v
  4     SELECT ROWNUM ROW_NUM, A.*
WJ5R'w7a!r0  5     FROMITPUB个人空间(T[$^_d.^5X8OU
  6     (
+N+}Ym,b `!U$`0  7             SELECT /*+ INDEX(A) */ A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATE
/bP\3{T(gk3X8r0  8             FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG C 

w!bx!DU-L7m1e0  9             WHERE B.PLAT_ID=59
'w @'yB6v
b
~0 10             AND A.ENABLE_FLAG='1' 
5kh;@
Ai&R+q(D`0 11             AND A.PRODUCT_ID = B.PRODUCT_IDITPUB个人空间,s8sN(~y%N
 12             AND A.DRUG_ID = C.DRUG_ID  ITPUB个人空间gF-\ H$Qv4wE"Y4w
 13             AND (INSTR(UPPER(C.DRUG_NAME), '阿') <> 0 
mW+M(O7v-l4H0 14                     OR INSTR(UPPER(C.ENGLISH_NAME), '阿') <> 0 
Y!o~)A8n(C%a.I0 15                     OR INSTR(UPPER(C.WUBI_CODE), '阿') <> 0 
}N*`%{*gW$qz\0 16                     OR INSTR(UPPER(C.PINYIN_CODE), '阿') <> 0 
[-hO)vR0 17                     OR INSTR(UPPER(A.PRODUCT_NAME), '阿') <> 0 ITPUB个人空间1cb7Vul
 18                     OR INSTR(UPPER(A.PINYIN_CODE), '阿') <> 0 
HHT!P2\PUF0 19                     OR INSTR(UPPER(A.WUBI_CODE), '阿') <> 0) 
-~:wv%Mv\0 20     ) A ITPUB个人空间,bM MMbNV:hi
 21     WHERE ROWNUM <= 40
"s-a#l8W:p}VV0 22  )
6j_h%A0M,k3_m(d%O0 23  WHERE ROW_NUM >= 31ITPUB个人空间~)t@E8`nX&Qeqs
 24  ;

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
'Nnn+y e X]0----------------------------------------------------------
._TDpvh1k1c0Plan hash value: 2045796448

-------------------------------------------------------------------------------------------
ID7C
y?Nxv0| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)|
xW4t-A$`0--------------------------------------------------------------------------------------------ITPUB个人空间
yc ~'hv0X-kH-@
|   0 | SELECT STATEMENT                |                         |    40 |  4720 |   139   (0)|
D)?E9XY?]+N-EA0|*  1 |  VIEW                           |                         |    40 |  4720 |   139   (0)|ITPUB个人空间
Ye5CJS
|*  2 |   COUNT STOPKEY                 |                         |       |       |            |
)Wc.N@@:pi o3]0|   3 |    NESTED LOOPS                 |                         |    40 |  3720 |   139   (0)|
k5vBZ X0|   4 |     NESTED LOOPS                |                         |    32 |  2688 |   107   (0)|
Y mn8~5GNv"|h0|   5 |      TABLE ACCESS BY INDEX ROWID| INF_PRODUCT             | 52835 |  1135K|     3   (0)|ITPUB个人空间3e]Km.X
Oqy._j t
|*  6 |       INDEX RANGE SCAN          | INF_PRODUCT_ENABLE_FLAG | 52835 |       |     1   (0)|ITPUB个人空间0}8v:X'U,Mz^)n
|*  7 |      TABLE ACCESS BY INDEX ROWID| INF_DRUG                |     1 |    62 |     1   (0)|ITPUB个人空间xU*I6]#e-P^Y8Dl
|*  8 |       INDEX UNIQUE SCAN         | INDEX_DRUG_ID           |     1 |       |     0   (0)|ITPUB个人空间1{2P,s6w&fK
|*  9 |     INDEX RANGE SCAN            | INF_PRODUCT_PLAT        |     1 |     9 |     1   (0)|ITPUB个人空间T7~Vklg*E u J2[
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间 G9bfWmE/x)ul
---------------------------------------------------

   1 - filter("ROW_NUM">=31)ITPUB个人空间kZMjauk~
   2 - filter(ROWNUM<=40)ITPUB个人空间0pm V|(bd[]
   6 - access("A"."ENABLE_FLAG"='1')ITPUB个人空间
N8{;jR4`f
   7 - filter(INSTR(UPPER("C"."DRUG_NAME"),'阿')<>0 OR INSTR(UPPER("C"."ENGLISH_NAME"),'阿')<>0 ORITPUB个人空间/du^1aIEC_x
              INSTR(UPPER("C"."WUBI_CODE"),'阿')<>0 OR INSTR(UPPER("C"."PINYIN_CODE"),'阿')<>0 OR
?&Dh;Z2R@d0              INSTR(UPPER("A"."PRODUCT_NAME"),'阿')<>0 OR INSTR(UPPER("A"."PINYIN_CODE"),'阿')<>0 ORITPUB个人空间s~fl/Ye}+K@
              INSTR(UPPER("A"."WUBI_CODE"),'阿')<>0)ITPUB个人空间7w4O O;B:z,[#\}d
   8 - access("A"."DRUG_ID"="C"."DRUG_ID")ITPUB个人空间a1sL!TPH
   9 - access("B"."PLAT_ID"=59 AND "A"."PRODUCT_ID"="B"."PRODUCT_ID")

ITPUB个人空间9W{)woD*n^$?(O_
Statistics
UHy7jCA0----------------------------------------------------------
Gq3F9[/i K/O0S+}0          0  recursive calls

B-p^
yX2J2d/w0          0  db block gets
+{YX!Qqr+Uf0       2568  consistent getsITPUB个人空间9SD6N7MF.[|
          0  physical reads

e e*\7J1i8vTb4|thr0          0  redo size
n*I(G4^7OK(P0       1201  bytes sent via SQL*Net to client
"g(AQ0J3Jy0        492  bytes received via SQL*Net from client
M,A0XlvL"i1w j0          2  SQL*Net roundtrips to/from clientITPUB个人空间a0K @aG-n^ \
          0  sorts (memory)
CY!u$|@0          0  sorts (disk)ITPUB个人空间#J#r Ur}o(S!l
         10  rows processed

使用HINT后，问题SQL的执行时间从19秒下降到了0.1秒，逻辑读从83W下降到了2500。