注册登录
提 问
English中文版
互联网服务db2 9.5db2 9.7

SQL Performance 问题

有个SQL Statement的问题, 使用db2advis没有任何建议, 所有涉及的4各表都有primary key. 可是运行时间过长.
select distinct  n.NOTE_AGENCY as Agency, n.DISPLAY_NOTE_TYPE_CD as NoticeType, n.NOTE_NO as NoticeNumber,
       n.INFRINGEMENT_TS as OffenceTime,
       date(n.CREATION_TS) as NoticeEnteringDate,
       max((case when n.UPDATED_TS >  i.UPDATED_TS then date(n.UPDATED_TS) else date(i.UPDATED_TS) end)) as UpdatingDate,
       d.DRIVERS_LICENCE_NO as LicenceNo,
       d.BIRTH_DT as DoB,
       d.GENDER_CD as Gender,
       d.IDNT_TYPE_CD as IDENTITY_TYPE,
       n.notice_id
from PIPS.NOTICE n
inner join PIPS.INFRINGEMENT i on (i.NOTICE_ID=n.NOTICE_ID)
inner join PIPS.IDENTITY d on (d.PIB_ID=n.PIB_ID)
left outer join PIPS.COURT_CASE c on (c.NOTICE_ID=i.NOTICE_ID and c.IFMT_NUMBER=i.IFMT_NUMBER)
where
          n.NOTE_TYPE_CD in (1,3,4,5,10) AND
         ( ((date(n.CREATION_TS)>'2014-08-05-00.00.00.000000') and (date(n.CREATION_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(i.CREATED_TS)>'2014-08-05-00.00.00.000000') and (date(i.CREATED_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(n.UPDATED_TS)>'2014-08-05-00.00.00.000000') and (date(n.UPDATED_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(i.UPDATED_TS)>'2014-08-05-00.00.00.000000') and (date(i.UPDATED_TS)<='2014-08-06-00.00.00.000000')) or
          ((i.ACTION_DT>'2014-08-05-00.00.00.000000') and (i.ACTION_DT<='2014-08-06-00.00.00.000000')) or
          ((c.OUTCOME_DT>'2014-08-05-00.00.00.000000') and (c.OUTCOME_DT<='2014-08-06-00.00.00.000000')) )
         AND(i.IFMT_STATUS_CD in (1,2,3,7,8,9,10,11)
or (i.IFMT_STATUS_CD = 6 and c.outcome_type_cd = 26 )
or (i.IFMT_STATUS_CD in (4, 5) and ( c.outcome_type_cd is null or c.outcome_type_cd in (6,17,18,26,38,39)))
    )
AND n.NOTE_TYPE_CD not in(8, 9)
and n.note_status_cd > 0
    group by n.notice_id, NOTE_AGENCY, n.DISPLAY_NOTE_TYPE_CD, note_no,INFRINGEMENT_TS,n.CREATION_TS,d.DRIVERS_LICENCE_NO,d.BIRTH_DT,d.GENDER_CD,d.IDNT_TYPE_CD;

使用db2expln后, 确实用的是TABLE SCAN, 所以建了几个另外的index, 建完index后access plan也确实走了INDEX SCAN, 但是时间还是老样子! A/P请参见两个附件

ape_2.bmp



下面是几个INDEX
CREATE UNIQUE INDEX "PIPS    "."XP1_INFRINGEMENT" ON "PIPS    "."INFRINGEMENT"
                ("NOTICE_ID" ASC,
                 "IFMT_NUMBER" ASC) INCLUDE
                ("UPDATED_TS" ASC,
                "CREATED_TS" ASC,
                "ACTION_DT" ASC,
                "IFMT_STATUS_CD" ASC)
                ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "PIPS    "."XP1_IDENTITY" ON "PIPS    "."IDENTITY"
                ("PIB_ID" ASC)
                INCLUDE ("DRIVERS_LICENCE_NO" ASC,
                "BIRTH_DT" ASC,
                "GENDER_CD" ASC,
                "IDNT_TYPE_CD" ASC)
                ALLOW REVERSE SCANS;

drop index pips.XP1_IDENTITY;
CREATE UNIQUE INDEX "PIPS    "."XP2_IDENTITY" ON "PIPS    "."IDENTITY"
                ("PIB_ID" ASC,
                "DRIVERS_LICENCE_NO" ASC,
                "BIRTH_DT" ASC,
                "GENDER_CD" ASC,
                "IDNT_TYPE_CD" ASC
                )
                ALLOW REVERSE SCANS;


CREATE UNIQUE INDEX "PIPS    "."XP1_NOTICE" ON "PIPS    "."NOTICE"
                ("NOTICE_ID" ASC)
                INCLUDE (
                 "NOTE_AGENCY"             ASC,
                 "DISPLAY_NOTE_TYPE_CD"    ASC,
                 "NOTE_NO"                 ASC,
                 "INFRINGEMENT_TS"         ASC,
                 "CREATION_TS"             ASC,
                 "UPDATED_TS"              ASC,
                 "PIB_ID"                  ASC,
                 "NOTE_TYPE_CD"            ASC,
                 "NOTE_STATUS_CD"          ASC)
                ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "PIPS    "."XP1_COURT_CASE" ON "PIPS    "."COURT_CASE"
                ("NOTICE_ID" ASC,
                 "IFMT_NUMBER" ASC)
                INCLUDE (
                "OUTCOME_DT" ASC,
                "OUTCOME_TYPE_CD" ASC)
                ALLOW REVERSE SCANS;
DROP INDEX PIPS.XP1_COURT_CASE;
CREATE INDEX "PIPS    "."XP2_COURT_CASE" ON "PIPS    "."COURT_CASE"
                ("OUTCOME_DT" ASC)
                ALLOW REVERSE SCANS;
CREATE INDEX "PIPS    "."XP3_COURT_CASE" ON "PIPS    "."COURT_CASE"
                ("OUTCOME_TYPE_CD" ASC)
                ALLOW REVERSE SCANS;



runstats on TABLE "PIPS    "."INFRINGEMENT"  with distribution and detailed index all;
runstats on TABLE "PIPS    "."IDENTITY"  with distribution and detailed index all;
runstats on TABLE "PIPS    "."NOTICE"  with distribution and detailed index all;
runstats on TABLE "PIPS    "."COURT_CASE"  with distribution and detailed index all;
关注1
评论
参与23

22同行回答
|
按赞同排序
按时间排序

baowei1003baowei1003软件开发工程师天逸金融
学习一下，显示全部
学习一下，收起
银行 · 2014-09-29
浏览1725
评论
mdkiimdkii软件开发工程师bocn
你是用的最后一个SQL吗？那请把最后一个SQL的db2batch 和 db2exfmt的结果贴上来看看。执行一下以下语句。db2batch -d -i complete -o e yes p 5 r 5 -f xxx.sql db2exfmt -d -1把上述两个命令的输出发出来看看。...显示全部
你是用的最后一个SQL吗？那请把最后一个SQL的db2batch 和 db2exfmt的结果贴上来看看。
执行一下以下语句。
db2batch -d -i complete -o e yes p 5 r 5 -f xxx.sql
db2exfmt -d -1
把上述两个命令的输出发出来看看。收起
银行 · 2014-09-03
浏览1727
评论
steve25steve25数据库管理员IBM
回复 20# mdkii    谢谢, 辛苦, 不过还是老样子!显示全部
回复 20# mdkii


   谢谢, 辛苦, 不过还是老样子!收起
互联网服务 · 2014-09-02
浏览1702
评论
mdkiimdkii软件开发工程师bocn
如果上面的逻辑没有问题，还可以改写得更激进点试一下： select n.NOTE_AGENCY as Agency, n.DISPLAY_NOTE_TYPE_CD as NoticeType, n.NOTE_NO as NoticeNumber,        n.INFRINGEMENT_TS as OffenceTime,        date(n.CREATION_TS)...显示全部
如果上面的逻辑没有问题，还可以改写得更激进点试一下：
select n.NOTE_AGENCY as Agency, n.DISPLAY_NOTE_TYPE_CD as NoticeType, n.NOTE_NO as NoticeNumber,
       n.INFRINGEMENT_TS as OffenceTime,
       date(n.CREATION_TS) as NoticeEnteringDate,
       max((case when n.UPDATED_TS >  i.max_updated_ts then date(n.UPDATED_TS) else date(i.max_updated_ts) end)) as UpdatingDate,
       d.DRIVERS_LICENCE_NO as LicenceNo,
       d.BIRTH_DT as DoB,
       d.GENDER_CD as Gender,
       d.IDNT_TYPE_CD as IDENTITY_TYPE,
       n.notice_id
from PIPS.NOTICE n
inner join PIPS.IDENTITY d on (d.PIB_ID=n.PIB_ID)
inner join
(  select a.NOTICE_ID,
          case when (a.CREATED_TS>'2014-08-05-00.00.00.000000') and (a.CREATED_TS<='2014-08-06-00.00.00.000000')
            then 1 else 0 end as CREATED_TS_FLAG,
          case when (a.ACTION_DT>'2014-08-05-00.00.00.000000') and (a.ACTION_DT<='2014-08-06-00.00.00.000000')
            then 1 else 0 end as ACTION_DT_FLAG,
          case when (a.UPDATED_TS>'2014-08-05-00.00.00.000000') and (a.UPDATED_TS<='2014-08-06-00.00.00.000000')
            then 1 else 0 end as UPDATED_TS_FLAG,
          case when (b.OUTCOME_DT>'2014-08-05-00.00.00.000000') and (b.OUTCOME_DT<='2014-08-06-00.00.00.000000')
            then 1 else 0 end as OUTCOME_DT_FLAG,
          max(UPDATED_TS) as max_updated_ts
     from PIPS.INFRINGEMENT a
     left outer join PIPS.COURT_CASE b on (b.NOTICE_ID=a.NOTICE_ID and b.IFMT_NUMBER=a.IFMT_NUMBER)
     where (((a.CREATED_TS>'2014-08-05-00.00.00.000000') and (a.CREATED_TS<='2014-08-06-00.00.00.000000')) or 
            ((a.UPDATED_TS>'2014-08-05-00.00.00.000000') and (a.UPDATED_TS<='2014-08-06-00.00.00.000000')) or
            ((a.ACTION_DT>'2014-08-05-00.00.00.000000')  and (a.ACTION_DT<='2014-08-06-00.00.00.000000')) or
            ((b.OUTCOME_DT>'2014-08-05-00.00.00.000000') and (b.OUTCOME_DT<='2014-08-06-00.00.00.000000')))
      and (a.IFMT_STATUS_CD in (1,2,3,7,8,9,10,11)
          or (a.IFMT_STATUS_CD = 6 and b.outcome_type_cd = 26 )
          or (a.IFMT_STATUS_CD in (4, 5) 
          and ( b.outcome_type_cd is null or b.outcome_type_cd in (6,17,18,26,38,39))))
        group by 
          a.NOTICE_ID,
          case when (a.CREATED_TS>'2014-08-05-00.00.00.000000') and (a.CREATED_TS<='2014-08-06-00.00.00.000000')
            then 1 else 0 end,
          case when (a.ACTION_DT>'2014-08-05-00.00.00.000000') and (a.ACTION_DT<='2014-08-06-00.00.00.000000')
            then 1 else 0 end,
          case when (a.UPDATED_TS>'2014-08-05-00.00.00.000000') and (a.UPDATED_TS<='2014-08-06-00.00.00.000000')
            then 1 else 0 end,
          case when (b.OUTCOME_DT>'2014-08-05-00.00.00.000000') and (b.OUTCOME_DT<='2014-08-06-00.00.00.000000')
            then 1 else 0 end ) i on (i.NOTICE_ID=n.NOTICE_ID)
 where   n.NOTE_TYPE_CD in (1,3,4,5,10) AND
         (((date(n.CREATION_TS)>'2014-08-05-00.00.00.000000') and (date(n.CREATION_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(n.UPDATED_TS)>'2014-08-05-00.00.00.000000') and (date(n.UPDATED_TS)<='2014-08-06-00.00.00.000000')) or
          (i.CREATED_TS_FLAG = 1) or (i.UPDATED_TS_FLAG = 1) or (i.ACTION_DT_FLAG = 1) or (i.OUTCOME_DT_FLAG = 1)) 
  AND n.NOTE_TYPE_CD not in(8, 9)
  and n.note_status_cd > 0
 group by n.notice_id, NOTE_AGENCY, n.DISPLAY_NOTE_TYPE_CD, note_no,INFRINGEMENT_TS,n.CREATION_TS,d.DRIVERS_LICENCE_NO,d.BIRTH_DT,d.GENDER_CD,d.IDNT_TYPE_CD;
注：这些SQL都没有经过测试，请严格测试正确性后再使用。收起
银行 · 2014-09-01
浏览1698
评论
mdkiimdkii软件开发工程师bocn
仔细研究了一下你的SQL，如果真的是 因为 NOTICE_ID 有重复造成的话，可以尝试改写一下SQL： select n.NOTE_AGENCY as Agency, n.DISPLAY_NOTE_TYPE_CD as NoticeType, n.NOTE_NO as NoticeNumber,        n.INFRINGEMENT_TS as OffenceTime,    &...显示全部
仔细研究了一下你的SQL，
如果真的是 因为 NOTICE_ID 有重复造成的话，可以尝试改写一下SQL：
select n.NOTE_AGENCY as Agency, n.DISPLAY_NOTE_TYPE_CD as NoticeType, n.NOTE_NO as NoticeNumber,
       n.INFRINGEMENT_TS as OffenceTime,
       date(n.CREATION_TS) as NoticeEnteringDate,
       max((case when n.UPDATED_TS >  i.UPDATED_TS then date(n.UPDATED_TS) else date(i.UPDATED_TS) end)) as UpdatingDate,
       d.DRIVERS_LICENCE_NO as LicenceNo,
       d.BIRTH_DT as DoB,
       d.GENDER_CD as Gender,
       d.IDNT_TYPE_CD as IDENTITY_TYPE,
       n.notice_id
from PIPS.NOTICE n
inner join PIPS.IDENTITY d on (d.PIB_ID=n.PIB_ID)
inner join
(  select a.NOTICE_ID
          a.CREATED_TS
          a.ACTION_DT,
          a.UPDATED_TS,
          b.OUTCOME_DT,
     from PIPS.INFRINGEMENT a
     left outer join PIPS.COURT_CASE b on (b.NOTICE_ID=a.NOTICE_ID and b.IFMT_NUMBER=a.IFMT_NUMBER)
     where (((a.CREATED_TS>'2014-08-05-00.00.00.000000') and (a.CREATED_TS<='2014-08-06-00.00.00.000000')) or 
          ((a.UPDATED_TS>'2014-08-05-00.00.00.000000') and (a.UPDATED_TS<='2014-08-06-00.00.00.000000')) or
          ((a.ACTION_DT>'2014-08-05-00.00.00.000000') and (a.ACTION_DT<='2014-08-06-00.00.00.000000')) or
          ((b.OUTCOME_DT>'2014-08-05-00.00.00.000000') and (b.OUTCOME_DT<='2014-08-06-00.00.00.000000')))
      and (i.IFMT_STATUS_CD in (1,2,3,7,8,9,10,11)
          or (a.IFMT_STATUS_CD = 6 and b.outcome_type_cd = 26 )
          or (a.IFMT_STATUS_CD in (4, 5) 
          and ( b.outcome_type_cd is null or b.outcome_type_cd in (6,17,18,26,38,39))))
        group by 
          a.NOTICE_ID
          a.CREATED_TS
          a.ACTION_DT,
          a.UPDATED_TS,
          b.OUTCOME_DT ) i on (i.NOTICE_ID=n.NOTICE_ID)
 where   n.NOTE_TYPE_CD in (1,3,4,5,10) AND
         (((date(n.CREATION_TS)>'2014-08-05-00.00.00.000000') and (date(n.CREATION_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(i.CREATED_TS)>'2014-08-05-00.00.00.000000') and (date(i.CREATED_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(n.UPDATED_TS)>'2014-08-05-00.00.00.000000') and (date(n.UPDATED_TS)<='2014-08-06-00.00.00.000000')) or
          ((date(i.UPDATED_TS)>'2014-08-05-00.00.00.000000') and (date(i.UPDATED_TS)<='2014-08-06-00.00.00.000000')) or
          ((i.ACTION_DT>'2014-08-05-00.00.00.000000') and (i.ACTION_DT<='2014-08-06-00.00.00.000000')) or
          ((i.OUTCOME_DT>'2014-08-05-00.00.00.000000') and (i.OUTCOME_DT<='2014-08-06-00.00.00.000000')) ) 
  AND n.NOTE_TYPE_CD not in(8, 9)
  and n.note_status_cd > 0
 group by n.notice_id, NOTE_AGENCY, n.DISPLAY_NOTE_TYPE_CD, note_no,INFRINGEMENT_TS,n.CREATION_TS,d.DRIVERS_LICENCE_NO,d.BIRTH_DT,d.GENDER_CD,d.IDNT_TYPE_CD;
这段SQL，我无法测试，你可以验证一下正确与否，如果结果正确，看看性能是否会提高。收起
银行 · 2014-08-30
浏览1771
评论
xu5762173xu5762173数据库管理员Ess
回复 5# zhenda     连续两次去查询相同的操作时,buffpool中已经有第一次的数据了, 所以第二次会很快.使用过程中有感觉db2batch很不靠谱么?显示全部
回复 5# zhenda


    连续两次去查询相同的操作时,buffpool中已经有第一次的数据了, 所以第二次会很快.

使用过程中有感觉db2batch很不靠谱么?收起
证券 · 2014-08-29
浏览1769
评论
vlifevlife数据库管理员NULL
回复 4# zhenda 分析执行计划问题 ，不需要sql 的具体执行时间。分析sql语句的执行问题。db2exfmt的输出最全面，这个可以看出sql是否已经最优执行。走索引的计划未必就是好的执行计划。显示全部
回复 4# zhenda
分析执行计划问题 ，不需要sql 的具体执行时间。

分析sql语句的执行问题。db2exfmt的输出最全面，这个可以看出sql是否已经最优执行。
走索引的计划未必就是好的执行计划。收起
IT其它 · 2014-08-29
浏览1711
评论
steve25steve25数据库管理员IBM
回复 13# zhmwang 已经有PIB_ID的索引了, 这个是对于notice table. 至于identity table, PIB_ID是primary key.CREATE INDEX "PIPS    "."X3PIBD19" ON "PIPS    "."NOTICE"                ("PI...显示全部
回复 13# zhmwang

已经有PIB_ID的索引了, 这个是对于notice table. 至于identity table, PIB_ID是primary key.
CREATE INDEX "PIPS    "."X3PIBD19" ON "PIPS    "."NOTICE"
                ("PIB_ID" ASC)
                DISALLOW REVERSE SCANS;

能解释一下什么叫做"在索引加上过滤条件字段"吗?收起
互联网服务 · 2014-08-28
浏览1709
评论
mdkiimdkii软件开发工程师bocn
如果是9.7以上，可以把 mon_get_pkg_cache_stmt的结果贴上来。里面的时间统计可以让你看到慢在哪里。显示全部
如果是9.7以上，可以把 mon_get_pkg_cache_stmt的结果贴上来。
里面的时间统计可以让你看到慢在哪里。收起
银行 · 2014-08-27
浏览1702
评论
mdkiimdkii软件开发工程师bocn
如果NOTICE_ID 有重复，那么很可能中间结果数据膨胀。sort的压力比较大。另外，你的SQL可以稍微改写一下：AND n.NOTE_TYPE_CD not in(8, 9) 这个条件可以不要。date(n.CREATION_TS) 这个DATE函数可以拿掉，这样减轻点优化器的压力。请把 db2exfmt的结果发上来，图片信息不够。...显示全部
如果NOTICE_ID 有重复，那么很可能中间结果数据膨胀。sort的压力比较大。
另外，你的SQL可以稍微改写一下：
AND n.NOTE_TYPE_CD not in(8, 9) 这个条件可以不要。
date(n.CREATION_TS) 这个DATE函数可以拿掉，这样减轻点优化器的压力。
请把 db2exfmt的结果发上来，图片信息不够。收起
银行 · 2014-08-27
浏览1726
评论

提问者

steve25
steve25
数据库管理员IBM
擅长领域： 数据库，关系型数据库，新核心系统
关注59
回答126
评论204

相关问题

  • db2diag日志中出现rc = -2146500507，这个是什么原因？
  • DB2 Connect Server 对应是是哪个lic文件？
  • 一个表最多可以创建多少个分区？有限制吗？
  • DB2StopMain 和sqeAppServices::ExecuteStopForce ，如何解读？
  • db2lfr 某个日志，提示busy？

    • 问题状态

  • 发布时间：2014-08-20
  • 关注会员：1 人
  • 问题浏览：17895
  • 最近回答：2014-09-29

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广