查看: 3441|回复: 14

[性能调整] 求救--优化查询

[复制链接]
论坛徽章:
1
蛋疼蛋
日期:2011-07-27 11:38:54
发表于 2011-4-25 13:08 | 显示全部楼层 |阅读模式
INSERT
   INTO PMRT.TB_RPT_OPER_EXP_D
   (
       DEAL_DATE                         --处理日期
      ,REGION_CODE                 --地市代码
      ,REGION_DESC                 --地市描述
      ,CITY_CODE                      --区县代码
      ,CITY_DESC                      --区县描述
      ,BUSN_BRAND_CODE       --营业品牌代码
      ,BUSN_BRAND_DESC        --营业品牌描述
      ,USER_CERT_NO              --用户证号
      ,CUST_NAME                    --客户名称
      ,USER_TYPE_CODE           --用户属性代码
      ,USER_TYPE                     --用户属性
      ,MAIN_ASST_ID                --主副端标识
      ,MAIN_ASST_DESC           --主副端描述
      ,ACT_PACK_CODE            --销售品代码
      ,ACT_PACK_DESC            --销售品描述
      ,END_TIME                       --授权截止时间
      ,CUST_ADDR                    --客户地址
      ,CNTCT_PHONE               --联系电话
      ,CNTCT_MOBILE              --联系人手机
      ,IC_CARD_NO                 --IC卡编号
      ,BOX_NO                        --机顶盒编号
   )
   SELECT
      '||v_thisyyyymmdd||'          --处理日期
      ,T1.REGION_CODE          --地市代码
      ,T2.REGION_DESC          --地市描述
      ,T1.CITY_CODE              --区县代码
      ,T3.ORG_DESC               --区县描述
      ,T1.BUSN_BRAND_CODE       --营业品牌代码
      ,T4.BUSN_BRAND_DESC       --营业品牌描述
      ,T1.PHONE_NO               --用户证号
      ,T1.CUST_NAME             --客户名称
      ,SUBSTR(t1.user_attr_code,1,2)          --用户属性代码
      ,T6.TYPE_NAME                           --用户属性
      ,case when t1.BAK_FIELD2='0' or t1.BAK_FIELD2='1' or t1.BAK_FIELD2='2' then t1.BAK_FIELD2
       else '3'
       end         AS MAIN_SUB_ID             --主副端标识
      ,case when t1.BAK_FIELD2='0' or t1.BAK_FIELD2 is null then '主端'
           when t1.BAK_FIELD2='1' or t1.BAK_FIELD2='2' then '副端'||trim(t1.BAK_FIELD2)
          else '副端2以上'
       end             AS MAIN_SUB_DESC           --主副端描述  
      ,T1.PRODT_CODE            --销售品
      ,T5.offername             --销售品描述
      ,MAX(TO_CHAR(T1.FAV_END_TIME,'YYYYMMDD')) --授权截止时间
      ,T1.CUST_ADDR             --客户地址
      ,T1.CNTCT_PERSN_PHONE     --联系电话
      ,T1.CNTCT_PERSN_MOB_PHONE --联系人手机
      ,T1.IC_CARD_NO            --IC卡编号
      ,T1.BOX_NO                --机顶盒编号
   FROM PMRT.TMP_RPT_OPER_EXP_D T1
   LEFT JOIN PMID.TB_MID_CDE_REGION T2
   ON T1.REGION_CODE=T2.REGION_CODE
   LEFT JOIN PMID.TB_MID_CDE_CITY T3
   ON T1.REGION_CODE=T3.REGION_CODE AND T1.CITY_CODE=T3.ORG_CODE
   LEFT JOIN PMID.TB_MID_CDE_BUSN_BRAND T4
   ON T1.BUSN_BRAND_CODE=T4.BUSN_BRAND_CODE
   LEFT JOIN pods.tb_cde_pdt_offer T5
   ON T1.PRODT_CODE=T5.offerid
   LEFT JOIN  (SELECT DISTINCT OWNER_CODE,TYPE_NAME FROM SLOGINOWNERTYPE ) t6
   ON SUBSTR(t1.user_attr_code,1,2) = t6.OWNER_CODE
   GROUP BY T1.REGION_CODE           --地市代码
      ,T2.REGION_DESC           --地市描述
      ,T1.CITY_CODE                --区县代码
      ,T3.ORG_DESC                --区县描述
      ,T1.BUSN_BRAND_CODE       --营业品牌代码
      ,T4.BUSN_BRAND_DESC       --营业品牌描述
      ,T1.PHONE_NO                --用户证号
      ,T1.CUST_NAME               --客户名称
      ,SUBSTR(t1.user_attr_code,1,2)          --用户属性代码
      ,T6.TYPE_NAME                           --用户属性
      ,case when t1.BAK_FIELD2='0' or t1.BAK_FIELD2='1' or t1.BAK_FIELD2='2' then t1.BAK_FIELD2
            else '3'
            end                  --主副端标识
      ,case when t1.BAK_FIELD2='0' or t1.BAK_FIELD2 is null then '主端'
            when t1.BAK_FIELD2='1' or t1.BAK_FIELD2='2' then '副端'||trim(t1.BAK_FIELD2)
            else '副端2以上'
           end                               --主副端描述  
      ,T1.PRODT_CODE            --销售品代码
      ,T5.offername                  --销售品描述
      ,T1.CUST_ADDR               --客户地址
      ,T1.CNTCT_PERSN_PHONE          --联系电话
      ,T1.CNTCT_PERSN_MOB_PHONE --联系人手机
      ,T1.IC_CARD_NO                         --IC卡编号
      ,T1.BOX_NO                                 --机顶盒编号

这是一个很平常的查询插入语句,但是由于TMP_RPT_OPER_EXP_D表的数据量很大(有4000w+的数据量)造成查询效率十分低下,需要60多分钟才能完成 。TMP_RPT_OPER_EXP_D之前没有建索引,其执行计划为:
INSERT STATEMENT, GOAL = ALL_ROWS                        2233423        32895339        9934392378
HASH GROUP BY                        2233423        32895339        9934392378
  HASH JOIN RIGHT OUTER                        83091        32895339        9934392378
   TABLE ACCESS FULL        DBCUSTADM        SLOGINOWNERTYPE        15        17        578
   HASH JOIN RIGHT OUTER                        82116        21285219        5704438692
    TABLE ACCESS FULL        PODS        TB_CDE_PDT_OFFER        4        322        28014
    HASH JOIN RIGHT OUTER                        81491        19657375        3557984875
     TABLE ACCESS FULL        PMID        TB_MID_CDE_CITY        3        92        1840
     HASH JOIN RIGHT OUTER                        80915        19657375        3164837375
      TABLE ACCESS FULL        PMID        TB_MID_CDE_REGION        3        92        2300
      HASH JOIN RIGHT OUTER                        80338        19657375        2673403000
       TABLE ACCESS FULL        PMID        TB_MID_CDE_BUSN_BRAND        3        4        56
       TABLE ACCESS FULL        PMRT        TMP_RPT_OPER_EXP_D        79762        19657375        2398199750
请高手给予指教,该如何建索引,才能提升效率。
ps:对TMP_RPT_OPER_EXP_D的region_code做了索引测试,但是执行计划还是全表扫描。
select速率为12:24;
select+insert速率为62:14;

[ 本帖最后由 president2010 于 2011-4-25 14:57 编辑 ]
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-4-25 13:19 | 显示全部楼层
4000W要取2000W,当然走全表. 最后要插入3200W。要70分钟?

是否可以确认最终插入多少? 以及4000W的表到底取多少数据?

使用道具 举报

回复
论坛徽章:
1
蛋疼蛋
日期:2011-07-27 11:38:54
 楼主| 发表于 2011-4-25 13:26 | 显示全部楼层

回复 #2 tom_fans 的帖子

TMP_RPT_OPER_EXP_D 数据量:33081691
最终插入数据量:14459355

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2011-4-25 13:39 | 显示全部楼层
原帖由 president2010 于 2011-4-25 13:26 发表
TMP_RPT_OPER_EXP_D 数据量:33081691
最终插入数据量:14459355






那去的数据量大约是大于43%了, 那对该表使用IFS没有任何问题!


使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-4-25 13:43 | 显示全部楼层
查询速度多少? 先不管插入。

使用道具 举报

回复
论坛徽章:
1
蛋疼蛋
日期:2011-07-27 11:38:54
 楼主| 发表于 2011-4-25 14:58 | 显示全部楼层

回复 #5 tom_fans 的帖子

我刚才测试了一下,查询的速率为12:24,select+insert 60多分钟

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
18
迷宫蛋
日期:2011-06-01 15:32:28蛋疼蛋
日期:2011-10-27 13:21:25ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41ITPUB社区千里马徽章
日期:2013-06-09 10:15:342014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31
发表于 2011-4-25 15:01 | 显示全部楼层
INSERT  /*+ append */的呢?

使用道具 举报

回复
论坛徽章:
1
蛋疼蛋
日期:2011-07-27 11:38:54
 楼主| 发表于 2011-4-25 15:09 | 显示全部楼层

回复 #7 nmgzw 的帖子

insert /*+append*/  效率没怎么提升。

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-4-25 15:12 | 显示全部楼层
你这个计划是从ORACLE取出来,还是通过autotrace?

用10046跟踪,把10046文件提出来给我们看。

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-4-25 15:12 | 显示全部楼层
速度相差这么大,绝对不是什么append的问题。 肯定是中间哪个环节出了问题。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表