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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-217988/