[20231207]开发不应该这样写sql4.txt

[20231207]开发不应该这样写sql4.txt

--//最近在优化sql语句,发现另外一种风格,实际上以前也遇到过,感觉这就像一种病,会传染只要一个这样写后面的要么跟进要么
--//不改。我觉得开发应该感谢exadata,不然我们的生产系统估计会垮掉。

1.环境:
XXXXXX> @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

2.问题语句:
XXXXXX> @ sql_id  ag76s7zum6z3b

--SQL_ID = ag76s7zum6z3b

SELECT MZ.BRID AS PATIENT_ID,
       TO_CHAR(GH.SBXH) AS OUTPATIENT_ID,
       :"SYS_B_0" AS INHOSPITAL_ID, JZLS.JZXH AS VISIT_ID, MZ.MZHM AS CARD_NO,
       GH.GHSJ AS VISIT_TIME, MZ.BRXM AS PATIENT_NAME, MZ.BRXB AS PATIENT_SEX,
       MZ.CSNY AS PATIENT_BIRTHDATE, MZ.SFZH AS IDENTITY_CARD_ID,
       :"SYS_B_1" AS PATIENT_TYPE,
       (SELECT GY_DMZD.DMMC FROM XXXXXX_YYY.GY_DMZD WHERE GY_DMZD.DMLB    = :"SYS_B_2" AND GY_DMZD.DMSB = MZ.MZDM) AS PATIENT_NATION,
       (SELECT GY_SSXWH.NAME FROM XXXXXX_YYY.GY_SSXWH WHERE GY_SSXWH.SBXH = MZ.XZZ_SQS) AS FAMILY_ADDRESS_PROVINCE,
       (SELECT GY_SSXWH.NAME FROM XXXXXX_YYY.GY_SSXWH WHERE GY_SSXWH.SBXH = MZ.XZZ_S) AS FAMILY_ADDRESS_CITY,
       MZ.LXDZ AS FAMILY_ADDRESS_DETAIL, MZ.LXDH AS MOBILE_PHONE,
       GY.KSDM AS DEPART_CODE, GY.KSMC AS DEPART_NAME,
       KS.KSDM AS SUB_DEPART_CODE, KS.KSMC AS SUB_DEPART_NAME,
       (SELECT CSZ FROM GY_XTCS WHERE CSMC                                = :"SYS_B_3") AS HOS_ID
  FROM XXXXXX_YYY.MS_BRDA MZ
  LEFT JOIN XXXXXX_YYY.MS_GHMX GH
    ON MZ.BRID = GH.BRID
  LEFT JOIN XXXXXX_YYY.MS_GHKS KS
    ON GH.KSDM = KS.KSDM
  LEFT JOIN XXXXXX_YYY.GY_KSDM GY
    ON KS.MZKS = GY.KSDM
  LEFT JOIN XXXXXX_YYY.YS_MZ_JZLS JZLS
    ON JZLS.GHXH = GH.SBXH
 WHERE ((:card_no  = :"SYS_B_4" OR :card_no IS NULL) OR MZ.MZHM  = :card_no)
   AND ((:patient_id  = :"SYS_B_5" OR :patient_id IS NULL) OR MZ.BRID = :patient_id)
   AND ((:patientName = :"SYS_B_6" OR :patientName IS NULL) OR MZ.BRXM = :patientName)
   AND ((:patientSex  = :"SYS_B_7" OR :patientSex IS NULL) OR MZ.BRXB = :patientSex)
   AND ((:deptName  = :"SYS_B_8" OR :deptName IS NULL) OR GY.KSMC = :deptName);
--//我做了格式化处理,原始程序代码就一行。
--//可以看出开发的本意,就是带入任意参数都可以查询。可惜oracle 优化器没有这么智能,无法选择合理的执行路径。
--//根据输入选择合适的索引,导致选择全部扫描。

SYS@192.168.100.141:1621/dbcn/dbcn1> @ seg2 %.MS_BRDA
    SEG_MB OWNER                SEGMENT_NAME                   SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
      1656 XXXXXX_YYY           MS_BRDA                        TABLE                XXXXXX_YYY                         211968         52     852001
--//1.6G.

--//执行计划如下:
Plan hash value: 1015797529
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |        |       | 60543 (100)|          |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | GY_DMZD           |      1 |    20 |     2   (0)| 00:00:01 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN              | PK_GY_DMZD        |      1 |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|   3 |  TABLE ACCESS BY INDEX ROWID     | GY_SSXWH          |      1 |    13 |     2   (0)| 00:00:01 |       |       |          |
|*  4 |   INDEX UNIQUE SCAN              | PK_GY_SSXWH       |      1 |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|   5 |  TABLE ACCESS BY INDEX ROWID     | GY_SSXWH          |      1 |    13 |     2   (0)| 00:00:01 |       |       |          |
|*  6 |   INDEX UNIQUE SCAN              | PK_GY_SSXWH       |      1 |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|   7 |  TABLE ACCESS BY INDEX ROWID     | GY_XTCS           |      1 |    18 |     2   (0)| 00:00:01 |       |       |          |
|*  8 |   INDEX UNIQUE SCAN              | PK_GY_XTCS        |      1 |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|   9 |  NESTED LOOPS OUTER              |                   |    805 |   123K| 60543   (1)| 00:12:07 |       |       |          |
|* 10 |   FILTER                         |                   |        |       |            |          |       |       |          |
|* 11 |    HASH JOIN RIGHT OUTER         |                   |    687 | 99615 | 58034   (1)| 00:11:37 |  2782K|  2782K| 1588K (0)|
|  12 |     TABLE ACCESS STORAGE FULL    | GY_KSDM           |   1099 | 24178 |     7   (0)| 00:00:01 |  1025K|  1025K|          |
|* 13 |     HASH JOIN RIGHT OUTER        |                   |    687 | 84501 | 58027   (1)| 00:11:37 |  2596K|  2596K| 1573K (0)|
|  14 |      TABLE ACCESS STORAGE FULL   | MS_GHKS           |    429 | 11583 |     5   (0)| 00:00:01 |  1025K|  1025K|          |
|  15 |      NESTED LOOPS OUTER          |                   |    687 | 65952 | 58022   (1)| 00:11:37 |       |       |          |
|* 16 |       TABLE ACCESS STORAGE FULL  | MS_BRDA           |     92 |  6532 | 57498   (1)| 00:11:30 |  1025K|  1025K|          |
|  17 |       TABLE ACCESS BY INDEX ROWID| MS_GHMX           |      8 |   200 |    10   (0)| 00:00:01 |       |       |          |
|* 18 |        INDEX RANGE SCAN          | IDX_MS_GHMX_BRID  |      8 |       |     2   (0)| 00:00:01 |  1025K|  1025K|          |
|  19 |   TABLE ACCESS BY INDEX ROWID    | YS_MZ_JZLS        |      1 |    12 |     4   (0)| 00:00:01 |       |       |          |
|* 20 |    INDEX RANGE SCAN              | I_YS_MZ_JZLS_GHXH |      1 |       |     2   (0)| 00:00:01 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------

SYS@192.168.100.141:1621/dbcn/dbcn1> @ bind_cap ag76s7zum6z3b :card_no
SQL_ID        CHILD_NUMBER WAS NAME      POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- --------- -------- ---------- ------------------- --------------- ------------
ag76s7zum6z3b            0 YES :CARD_NO         5         32 2023-12-06 09:54:09 VARCHAR2(32)    90377195
                         1 YES :CARD_NO         5         32 2023-12-05 11:29:35 VARCHAR2(32)    02666713
                         2 YES :CARD_NO         5         32 2023-12-06 19:33:57 VARCHAR2(32)    91544379

SYS@192.168.100.141:1621/dbcn/dbcn1> @ bind_cap_awr ag76s7zum6z3b ''
no rows selected

--//这样语句在awr历史表还没有记录。可以发现在共享池抓到的sql语句都是带入card_no参数的。
--//我多次提过不要这样写sql语句,这不是在学校写家庭作业,这是生产系统!!这类语句在生产系统还有一大堆,真不知道现在的毕业生
--//如何毕业的。

热门相关:写给鼹鼠先生的情书   现代隐士高手   圣人门徒   在遗忘的时光里重逢   贴身侍卫