[20181114]一条sql语句的优化.txt

--//很久不看生产系统的sql语句,看这些东西心情会很不好,昨天看了一条sql语句.

--//这类错误很常见,自己写出来:

1.环境:

SYSTEM@192.168.31.8:1521/hrp430> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

2.抽取语句生成执行脚本如下:

--//生成执行脚本如下:

$ cat 4vdargkxgmjqt.sql4

variable N1 NUMBER

variable N2 VARCHAR2(32)

variable N3 VARCHAR2(32)

variable SYS_B_0 VARCHAR2(32)

variable SYS_B_1 NUMBER

variable N4 NUMBER

variable N5 VARCHAR2(32)

variable N6 VARCHAR2(32)

variable SYS_B_2 VARCHAR2(32)

variable SYS_B_3 NUMBER

begin

:N1 := 18737588;

:N2 := '121';

:N3 := 'NULL';

:SYS_B_0 := '3865';

:SYS_B_1 := 0;

:N4 := 18737588;

:N5 := '121';

:N6 := 'NULL';

:SYS_B_2 := '3865';

:SYS_B_3 := 0;

end;

/

set termout off

set sqlblanklines on

alter session set current_schema=XXXXXX_YYY;

alter session set statistics_level=all;

SELECT  /* test 4vdargkxgmjqt */

        /*+ gather_plan_statistics */

      SBXH

  FROM MS_GHMX

 WHERE     (SELECT MAX (ghsj)

              FROM ms_ghmx

             WHERE     BRID = :N1

                   AND (KSDM = :N2 OR KSDM = :N3 OR YSDM = :"SYS_B_0")

                   AND THBZ = :"SYS_B_1") = ghsj

       AND BRID = :N4

       AND (KSDM = :N5 OR KSDM = :N6 OR YSDM = :"SYS_B_2")

       AND THBZ = :"SYS_B_3";

set termout on

set sqlblanklines off

@dpc '' ''

rollback;

Plan hash value: 4229624801

-------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name                 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                      |      1 |        |       |    13 (100)|          |      1 |00:00:00.01 |      40 |

|*  1 |  TABLE ACCESS BY INDEX ROWID   | MS_GHMX              |      1 |      1 |    32 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |      40 |

|*  2 |   INDEX RANGE SCAN             | I_MS_GHMX_GHSJ_YS_JZ |      1 |      1 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |      39 |

|   3 |    SORT AGGREGATE              |                      |      1 |      1 |    26 |            |          |      1 |00:00:00.01 |      35 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| MS_GHMX              |      1 |      1 |    26 |     9   (0)| 00:00:01 |      3 |00:00:00.01 |      35 |

|*  5 |      INDEX RANGE SCAN          | I_MS_GHMX_BRID_GHSJ  |      1 |      6 |       |     3   (0)| 00:00:01 |     41 |00:00:00.01 |       3 |

-------------------------------------------------------------------------------------------------------------------------------------------------

--//id=4,逻辑读35,而且因为brid表示病人ID,这样表ms_ghmx(挂号明细表)里面的信息关于这个字段记录的全部查询出来,导致累积存在大量的物理读.

--//当然我这里执行多次,已经没有物理读.你想像一下假设一个老病号这样来医院看病,在这个表中记录上百次一点不奇怪.我当前病人有41次记录.

--//实际上查询仅仅满足条件的ghsj(挂号时间)最大的记录,根本不需要遍历相关记录.

SELECT sbxh

  FROM (  SELECT brid, ghsj, SBXH

            FROM ms_ghmx

           WHERE     BRID = :N1

                 AND (KSDM = :N2 OR KSDM = :N3 OR YSDM = :"SYS_B_0")

                 AND THBZ = :"SYS_B_1"

        ORDER BY ghsj DESC)

 WHERE ROWNUM = 1;

Plan hash value: 1401260886

------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                     |      1 |        |       |     9 (100)|          |      1 |00:00:00.01 |       4 |

|*  1 |  COUNT STOPKEY                 |                     |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|   2 |   VIEW                         |                     |      1 |      1 |    13 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|*  3 |    TABLE ACCESS BY INDEX ROWID | MS_GHMX             |      1 |      1 |    32 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|*  4 |     INDEX RANGE SCAN DESCENDING| I_MS_GHMX_BRID_GHSJ |      1 |      6 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

------------------------------------------------------------------------------------------------------------------------------------------------

--//你可以发现这样查询根本不需要查询全部brid=:N1的记录,也不需要取出全部相关记录.大部分前面几条基本满足需求(一般病人可能一天挂几个科室医生的号).

--//直接返回了.这样查询基本不会有物理读.

--//实际上这类的错误在开发中反反复复,这么就一点不长进呢?可悲可叹...