[20151209]一条sql语句的优化(续).txt

http://blog.itpub.net/267265/viewspace-1852195/

--上次提到其中1条sql语句：

1.环境：

SYSTEM@192.168.99.105:1521/dbcn> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

sql_id=18rkuzabjzj2f

SELECT CP_YZMX.XMMC
  FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND CP_YZMX.XMDM = :1;

--今天检查发现开发修改为：
sql_id=8uj587u1dx435

SELECT CP_YZMX.XMMC
  FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND CP_YZMX.XMDM = :1
       AND ROWNUM = :"SYS_B_0";

--说明: ROWNUM = 1(主要是我们应用存在大量非绑定语句，我把参数cursor_sharing=force).
--实际上开发存在问题，难道没有一个表存在项目代码，项目名称的字典吗？我感觉数据结构存在问题。

Plan hash value: 1969266810
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |      1 |        |       |  3113 (100)|          |      1 |00:00:00.01 |       7 |       |       |          |
|*  1 |  COUNT STOPKEY                          |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       7 |       |       |          |
|*  2 |   HASH JOIN                             |                 |      1 |      1 |    33 |  3113   (1)| 00:00:38 |      1 |00:00:00.01 |       7 |  3886K|  3886K|  270K (0)|
|   3 |    JOIN FILTER CREATE                   | :BF0000         |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID         | EMR_DJMX        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  5 |      INDEX RANGE SCAN                   | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|   6 |    JOIN FILTER USE                      | :BF0000         |      1 |     36 |   864 |  3111   (1)| 00:00:38 |      1 |00:00:00.01 |       4 |       |       |          |
|*  7 |     TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX         |      1 |     36 |   864 |  3111   (1)| 00:00:38 |      1 |00:00:00.01 |       4 |  1025K|  1025K|          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F04857F1
   4 - SEL$F04857F1 / EMR_DJMX@SEL$1
   5 - SEL$F04857F1 / EMR_DJMX@SEL$1
   7 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=:NSYS_B_0)
   2 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
   5 - access("EMR_DJMX"."XMID"=:N1)
   7 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))

--逻辑读7。

如果要是我写，我会改写如下：

SELECT CP_YZMX.XMMC
  FROM CP_YZMX
WHERE     CP_YZMX.XMDM = :N1
       AND ROWNUM = 1
       AND EXISTS
              (SELECT NULL
                 FROM EMR_DJMX, emr_djfl
                WHERE     EMR_DJMX.DJID = emr_djfl.Djid
                      AND emr_djfl.DJID = CP_YZMX.DJID
                      AND CP_YZMX.xmdm = EMR_DJMX.XMID);

--可是执行计划如下：
Plan hash value: 1768383312
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |       |  8487 (100)|          |      1 |00:00:00.22 |   33193 |       |       |          |
|*  1 |  COUNT STOPKEY                         |                 |      1 |        |       |            |          |      1 |00:00:00.22 |   33193 |       |       |          |
|*  2 |   HASH JOIN SEMI                       |                 |      1 |      2 |    66 |  8487   (1)| 00:01:42 |      1 |00:00:00.22 |   33193 |  2827K|  2827K| 1291K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX         |      1 |     98 |  2352 |  8485   (1)| 00:01:42 |  11170 |00:00:00.21 |   33190 |  1025K|  1025K|          |
|   4 |    TABLE ACCESS BY INDEX ROWID         | EMR_DJMX        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  5 |     INDEX RANGE SCAN                   | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$46948216
   3 - SEL$46948216 / CP_YZMX@SEL$1
   4 - SEL$46948216 / EMR_DJMX@SEL$2
   5 - SEL$46948216 / EMR_DJMX@SEL$2
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
   3 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
   5 - access("EMR_DJMX"."XMID"=:N1)

-- 33190*8192/1024/1024=259.296875 M,也就是全表扫描CP_YZMX表。反而比开发写的效果差。
-- 问题在那里呢？问题出在 HASH JOIN SEMI，这样要构件第1个表CP_YZMX的hash，这样必须走全表扫描，看来oracle cbo也太不聪明....实际上输出仅仅要1行。
-- 很明显连接顺序出现了问题？

--抽取执行计划：
>@dpc 7rrpbdfnp78kd outline
Outline Data
-------------
/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$46948216")
      UNNEST(@"SEL$DB49FC34")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$DB49FC34")
      ELIMINATE_JOIN(@"SEL$2" "EMR_DJFL"@"SEL$2")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$46948216" "CP_YZMX"@"SEL$1")
      INDEX_RS_ASC(@"SEL$46948216" "EMR_DJMX"@"SEL$2" ("EMR_DJMX"."XMID"))
      LEADING(@"SEL$46948216" "CP_YZMX"@"SEL$1" "EMR_DJMX"@"SEL$2")
      USE_HASH(@"SEL$46948216" "EMR_DJMX"@"SEL$2")
      END_OUTLINE_DATA
*/

--然后改写如下：下划线~是我改动的提示。
SELECT
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$46948216")
      UNNEST(@"SEL$DB49FC34")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$DB49FC34")
      ELIMINATE_JOIN(@"SEL$2" "EMR_DJFL"@"SEL$2")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$46948216" "CP_YZMX"@"SEL$1")
      INDEX_RS_ASC(@"SEL$46948216" "EMR_DJMX"@"SEL$2" ("EMR_DJMX"."XMID"))
      LEADING(@"SEL$46948216" "EMR_DJMX"@"SEL$2" "CP_YZMX"@"SEL$1" )
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      USE_NL(@"SEL$46948216" "CP_YZMX"@"SEL$1")
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      END_OUTLINE_DATA
  */
CP_YZMX.XMMC
  FROM CP_YZMX
WHERE     CP_YZMX.XMDM = :N1
       AND ROWNUM = 1
       AND EXISTS
              (SELECT NULL
                 FROM EMR_DJMX, emr_djfl
                WHERE     EMR_DJMX.DJID = emr_djfl.Djid
                      AND emr_djfl.DJID = CP_YZMX.DJID
                      AND CP_YZMX.xmdm = EMR_DJMX.XMID);

Plan hash value: 2877850036
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |       |  8490 (100)|          |      1 |00:00:00.01 |       7 |       |       |          |
|*  1 |  COUNT STOPKEY                 |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       7 |       |       |          |
|   2 |   NESTED LOOPS                 |                 |      1 |      1 |    33 |  8490   (1)| 00:01:42 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    SORT UNIQUE                 |                 |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|*  6 |    TABLE ACCESS STORAGE FULL   | CP_YZMX         |      1 |      1 |    24 |  8487   (1)| 00:01:42 |      1 |00:00:00.01 |       4 |  1025K|  1025K|          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$46948216
   4 - SEL$46948216 / EMR_DJMX@SEL$2
   5 - SEL$46948216 / EMR_DJMX@SEL$2
   6 - SEL$46948216 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   5 - access("EMR_DJMX"."XMID"=:N1)
   6 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))

--很明显这样最优，实际上如果查询的:N1 如果再CP_YZMX的表里面特别靠后，这样的逻辑读会很大的。
SELECT DISTINCT xmid
  FROM EMR_DJMX, emr_djfl
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND NOT EXISTS
              (SELECT 1
                 FROM CP_YZMX
                WHERE xmdm = xmid);
....

-- 找到1个xmid=851,带入查询:N1=851。

Plan hash value: 2877850036
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |       |  9046 (100)|          |      0 |00:00:00.19 |   33194 |       |       |          |
|*  1 |  COUNT STOPKEY                 |                 |      1 |        |       |            |          |      0 |00:00:00.19 |   33194 |       |       |          |
|   2 |   NESTED LOOPS                 |                 |      1 |      1 |    33 |  9046   (1)| 00:01:49 |      0 |00:00:00.19 |   33194 |       |       |          |
|   3 |    SORT UNIQUE                 |                 |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|*  6 |    TABLE ACCESS STORAGE FULL   | CP_YZMX         |      1 |      1 |    24 |  9043   (1)| 00:01:49 |      0 |00:00:00.19 |   33191 |  1025K|  1025K|          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$46948216
   4 - SEL$46948216 / EMR_DJMX@SEL$2
   5 - SEL$46948216 / EMR_DJMX@SEL$2
   6 - SEL$46948216 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 631
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   5 - access("EMR_DJMX"."XMID"=:N1)
   6 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND "EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID"))

-- 因为找到不到:N1=851的数据( "CP_YZMX"."XMDM"=851)在CP_YZMX表，导致执行效率很低。
-- 使用开发写的执行计划如下：
Plan hash value: 1969266810
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |      1 |        |       |  3106 (100)|          |      0 |00:00:00.19 |   33193 |       |       |          |
|*  1 |  COUNT STOPKEY                          |                 |      1 |        |       |            |          |      0 |00:00:00.19 |   33193 |       |       |          |
|*  2 |   HASH JOIN                             |                 |      1 |      1 |    33 |  3106   (1)| 00:00:38 |      0 |00:00:00.19 |   33193 |  3886K|  3886K|  265K (0)|
|   3 |    JOIN FILTER CREATE                   | :BF0000         |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID         | EMR_DJMX        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  5 |      INDEX RANGE SCAN                   | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|   6 |    JOIN FILTER USE                      | :BF0000         |      1 |     36 |   864 |  3104   (1)| 00:00:38 |      0 |00:00:00.19 |   33190 |       |       |          |
|*  7 |     TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX         |      1 |     36 |   864 |  3104   (1)| 00:00:38 |      0 |00:00:00.19 |   33190 |  1025K|  1025K|          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F04857F1
   4 - SEL$F04857F1 / EMR_DJMX@SEL$1
   5 - SEL$F04857F1 / EMR_DJMX@SEL$1
   7 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 851
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=:NSYS_B_0)
   2 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
   5 - access("EMR_DJMX"."XMID"=:N1)
   7 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
44 rows selected.

--不是很了解应用的模式，看来要优化这条sql语句建立CP_YZMX.XMDM的垃圾索引才是王道。

--顺便提一下另外的语句：

sql_id=7dys4vnppxu76

SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
  FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND CP_YZMX.XMDM = :1;

--改写这样：
a23u448k9dhmw

SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
  FROM EMR_DJMX
      ,emr_djfl
      ,CP_YZMX
      ,cp_brlj
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND cp_yzmx.brlj = cp_brlj.brlj
       AND CP_YZMX.XMDM = :1
       AND cp_brlj.brid = :2;

--漏掉应该查询另外1个表的cp_brlj.brid(病人ID),真tmd的奇葩!!