1.Awr 报告抓取问题语句

2. 绑定变量、字符集、格式调整

select a.jlxh,
       b.zyhm,
       b.brch,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.zy_brry b
 where a.brid = b.zyh
   and a.jzlx = 2
   and (a.send_flag = 0)
   and (b.brks = 7025 or 7025 = 0)
   and (select nvl(yzyzt, 0) from bsuser.zy_brry_ryzb where zyh = b.zyh) = 0
   and 5 = 1
 
union all
 
select a.jlxh,
       b.zyhm,
       b.brch,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.zy_brry b
 where a.brid = b.zyh
   and a.jzlx = 2
   and a.send_flag = 0
   and (select nvl(yzyzt, 0) from bsuser.zy_brry_ryzb where zyh = b.zyh) = 1
   and 5 = 2
 
union all
 
select a.jlxh,
       b.mzhm,
       '门诊',
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.ms_brda b
 where a.brid = b.brid
   and a.jzlx = 1
   and a.send_flag = 0
   and 5 = 3
   and ((select instr(ksmc, '急诊')
           from bsuser.gy_ksdm
          where ksdm = a.rec_ksdm) = 0 and
       (select instr(ksmc, '发热')
           from bsuser.gy_ksdm
          where ksdm = a.rec_ksdm) = 0 and
       (to_char(sysdate, 'hh24mi') < '1700' and
       to_char(sysdate, 'hh24mi') > '0800'))
 
union all
 
select a.jlxh,
       b.mzhm,
       (case
        (select instr(ksmc, '急诊') from bsuser.gy_ksdm where ksdm = a.rec_ksdm)
         when 0 then
          '门诊'
         else
          '急诊'
       end) mjz,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.ms_brda b
 where a.brid = b.brid
   and a.jzlx = 1
   and a.send_flag = 0
   and 5 = 4
   and ((select instr(ksmc, '发热')
           from bsuser.gy_ksdm
          where ksdm = a.rec_ksdm) = 0 and
       ((select instr(ksmc, '急诊')
            from bsuser.gy_ksdm
           where ksdm = a.rec_ksdm) > 0 or
       to_char(sysdate, 'hh24mi') >= '1700' or
       to_char(sysdate, 'hh24mi') <= '0800'))
 
union all
 
select a.jlxh,
       b.mzhm,
       '发热门诊' mjz,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.ms_brda b
 where a.brid = b.brid
   and a.jzlx = 1
   and a.send_flag = 0
   and 5 = 5
   and (select instr(ksmc, '发热')
          from bsuser.gy_ksdm
         where ksdm = a.rec_ksdm) > 0;

3. 拆分5 个部分

2.1 第一部分

select a.jlxh,    --选择性100
       b.zyhm,
       b.brch,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.zy_brry b
 where a.brid = b.zyh
   and a.jzlx = 2     --NDV3个
   and (a.send_flag = 0)  --NDV3个
   and (b.brks = 7025 or 7025 = 0)
   and (select nvl(yzyzt, 0) from bsuser.zy_brry_ryzb where zyh = b.zyh) = 0
   and 1 = 1

unique scan 部分为底下嵌套查询部分的zyh ，回表是前面的函数部分，这个部分可以做一个组合索引。接下来看表的关联关系，HLHT_RECDATA 为驱动表，zy_brry 为被驱动表，zy_brry 的关联列部分加索引，综合其他部分，暂时不用做组合索引。看HLHT_RECDATA 的where 部分存在jzlx 和send_flag ，两者选择性都十分差，都是只有3 种数值。看select 部分，jlxh 、jsrq 、rec_typename 、mess ，第一个jlxh 选择性100 ，其他的就不用看了。创建jzlx 、send_flag 、jlxh 的组合索引。

2.2 第二部分

select a.jlxh,
       b.zyhm,
       b.brch,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.zy_brry b
 where a.brid = b.zyh
   and a.jzlx = 2
   and a.send_flag = 0
   and (select nvl(yzyzt, 0) from bsuser.zy_brry_ryzb where zyh = b.zyh) = 1
   and 2 = 2

与上面第一部分几乎相同，索引公用。

2.3 第三部分

select a.jlxh,
       b.mzhm,
       '门诊',
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.ms_brda b
 where a.brid = b.brid
   and a.jzlx = 1
   and a.send_flag = 0
   and 3 = 3
   and (
   (select instr(ksmc, '急诊')
           from bsuser.gy_ksdm
          where ksdm = a.rec_ksdm) = 0 and
      
       (select instr(ksmc, '发热')
           from bsuser.gy_ksdm
          where ksdm = a.rec_ksdm) = 0 and
          
       (to_char(sysdate, 'hh24mi') < '1700' and
       to_char(sysdate, 'hh24mi') > '0800')
       
       )

主要问题存在于中间的nestloop 部分，关系是HLHT_RECDATA 为驱动表，ms_brda 为被驱动表。ms_brda 部分创建brid 索引，HLHT_RECDATA 部分与上面2 个部分保持一致，抛开这些，其他部分在当前场景下，影响非常有限。

2.4 第四部分

select a.jlxh,
       b.mzhm,
       (case
        (select instr(ksmc, '急诊') from bsuser.gy_ksdm where ksdm = a.rec_ksdm)
         when 0 then
          '门诊'
         else
          '急诊'
       end) mjz,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.ms_brda b
 where a.brid = b.brid
   and a.jzlx = 1
   and a.send_flag = 0
   and 4 = 4
   and ((select instr(ksmc, '发热')
           from bsuser.gy_ksdm
          where ksdm = a.rec_ksdm) = 0 and
       ((select instr(ksmc, '急诊')
            from bsuser.gy_ksdm
           where ksdm = a.rec_ksdm) > 0 or
       to_char(sysdate, 'hh24mi') >= '1700' or
       to_char(sysdate, 'hh24mi') <= '0800'))
第四部分与第三部分相同。

2.5 第五部分

select a.jlxh,
       b.mzhm,
       '发热门诊' mjz,
       b.brxm,
       b.csny,
       (case b.brxb
         when 1 then
          '男'
         else
          '女'
       end) xb,
       a.jsrq,
       a.rec_typename,
       a.mess
  from bsuser.HLHT_RECDATA a, bsuser.ms_brda b
 where a.brid = b.brid
   and a.jzlx = 1
   and a.send_flag = 0
   and 5 = 5
   and (select instr(ksmc, '发热')
          from bsuser.gy_ksdm
         where ksdm = a.rec_ksdm) > 0;

第五部分与第三、四部分相同。

4. 整体初步优化完成， sql 从 awr 上消除

db time 下降35%

ITPub博客

oracle基础sql优化案例（一）

最新文章