查看: 4909|回复: 12

大数据量sql优化

[复制链接]
论坛徽章:
6
生肖徽章2007版:鼠
日期:2007-11-28 15:47:16辩论纪念章
日期:2010-11-15 09:41:55ITPUB社区12周年站庆徽章
日期:2013-10-08 14:53:15ITPUB社区12周年站庆徽章
日期:2013-10-08 14:56:08ITPUB社区12周年站庆徽章
日期:2013-10-08 14:59:19ITPUB社区12周年站庆徽章
日期:2013-10-08 15:00:34
发表于 2010-8-11 11:09 | 显示全部楼层 |阅读模式
请教大家一个问题:sql如下,
SELECT a.rpt_dt,
       a.rgn_no,
       a.org_no,
       a.hdbz,
       a.ckdm,
       a.kzbz,
       a.fdll,
       a.svtm,
       b.itvl_no,
       a.bz,
       COUNT(1) AS cus_cout
FROM (SELECT
       c.rpt_dt,
       c.rgn_no,
       c.org_no,
       c.bz,
       c.cus_no,
       DECODE(GROUPING(c.hdbz), 1, '#', c.hdbz) AS hdbz,
       DECODE(GROUPING(c.ckdm), 1, '#', c.ckdm) AS ckdm,
       DECODE(GROUPING(c.kzbz), 1, '#', c.kzbz) AS kzbz,
       DECODE(GROUPING(c.fdll), 1, '#', c.fdll) AS fdll,
       DECODE(GROUPING(c.svtm), 1, 8888, c.svtm) AS svtm,
       SUM(c.je)  
      FROM table c
      WHERE c.rgn_no = '45129'
            AND c.rpt_dt = '20100731'
            and c.org_no='8130'
            AND c.cls_flg = '0'
            AND c.bz = '001'
      GROUP BY c.rpt_dt,
               c.rgn_no,
               c.org_no,
               c.bz,
               c.cus_no,
               ROLLUP(c.hdbz, c.ckdm, c.kzbz),
               ROLLUP(c.fdll),
               ROLLUP(c.svtm)) a,
     CBS_CDE_RPTAMTDIS b
WHERE a.bkrmb_bal > b.amt_min
      AND a.bkrmb_bal <= b.amt_max
GROUP BY a.rpt_dt,
         a.rgn_no,
         a.org_no,
         a.hdbz,
         a.ckdm,
         a.kzbz,
         a.fdll,
         a.svtm,
         b.itvl_no,
         a.bz
select count(1)
FROM table c
      WHERE c.rgn_no = '45129'
            AND c.rpt_dt = '20100731'  数据量为5000万。

select count(1)
FROM table c
      WHERE c.rgn_no = '45129'
            AND c.rpt_dt = '20100731'
           and c.org_no='8130'     数据量为100万。
select count(1)
      FROM table c
      WHERE c.rgn_no = '45129'
            AND c.rpt_dt = '20100731'
            and c.org_no='8130'
            AND c.cls_flg = '0'
            AND c.bz = '001'  数据量为50万。
TABLE是按rtp_dt range 分区,rgn_no list分区。
create unique index XPK_FCT_AGT_SAVINF on TABLE(RPT_DT, RGN_NO, ORG_NO, SYS_FLG, AC_NO) LOCAL;
不用索引需要执行5分钟,用到索引需要时间更长。
执行计划:

[ 本帖最后由 star_guan2008 于 2010-8-11 11:11 编辑 ]
执行计划(不用索引).gif
执行计划(用索引).gif
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-8-11 11:12 | 显示全部楼层
统计信息有明显问题

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2010-8-11 11:16 | 显示全部楼层
到底是不是用 索引嘛。。。看看基数 啊 集群因子啊 这些。可能不适合用索引。。
统计下 统计信息。。

你的头像 恶心死了

使用道具 举报

回复
论坛徽章:
6
生肖徽章2007版:鼠
日期:2007-11-28 15:47:16辩论纪念章
日期:2010-11-15 09:41:55ITPUB社区12周年站庆徽章
日期:2013-10-08 14:53:15ITPUB社区12周年站庆徽章
日期:2013-10-08 14:56:08ITPUB社区12周年站庆徽章
日期:2013-10-08 14:59:19ITPUB社区12周年站庆徽章
日期:2013-10-08 15:00:34
 楼主| 发表于 2010-8-11 11:24 | 显示全部楼层
请问什么问题?谢谢。
原帖由 棉花糖ONE 于 2010-8-11 11:12 发表
统计信息有明显问题

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-8-11 11:24 | 显示全部楼层
先做分析
gather_table_stats(user ,'C',cascade=>ture)

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-8-11 11:26 | 显示全部楼层
这种场景先别考虑马上分析统计信息,根据业务数据特点,加hint,不要老惦记着走索引和全表,连接方式的选择产生的影响更大

使用道具 举报

回复
论坛徽章:
6
生肖徽章2007版:鼠
日期:2007-11-28 15:47:16辩论纪念章
日期:2010-11-15 09:41:55ITPUB社区12周年站庆徽章
日期:2013-10-08 14:53:15ITPUB社区12周年站庆徽章
日期:2013-10-08 14:56:08ITPUB社区12周年站庆徽章
日期:2013-10-08 14:59:19ITPUB社区12周年站庆徽章
日期:2013-10-08 15:00:34
 楼主| 发表于 2010-8-11 11:26 | 显示全部楼层
统计信息是最新的,头像很好玩吧。不使用索引也要5分钟,是不是太慢了?
原帖由 gaopengtttt 于 2010-8-11 11:16 发表
到底是不是用 索引嘛。。。看看基数 啊 集群因子啊 这些。可能不适合用索引。。
统计下 统计信息。。

你的头像 恶心死了

使用道具 举报

回复
论坛徽章:
6
生肖徽章2007版:鼠
日期:2007-11-28 15:47:16辩论纪念章
日期:2010-11-15 09:41:55ITPUB社区12周年站庆徽章
日期:2013-10-08 14:53:15ITPUB社区12周年站庆徽章
日期:2013-10-08 14:56:08ITPUB社区12周年站庆徽章
日期:2013-10-08 14:59:19ITPUB社区12周年站庆徽章
日期:2013-10-08 15:00:34
 楼主| 发表于 2010-8-11 12:15 | 显示全部楼层
我这个就两个表关联啊,我没想到更好的方法啊。
原帖由 棉花糖ONE 于 2010-8-11 11:26 发表
这种场景先别考虑马上分析统计信息,根据业务数据特点,加hint,不要老惦记着走索引和全表,连接方式的选择产生的影响更大

使用道具 举报

回复
论坛徽章:
6
生肖徽章2007版:鼠
日期:2007-11-28 15:47:16辩论纪念章
日期:2010-11-15 09:41:55ITPUB社区12周年站庆徽章
日期:2013-10-08 14:53:15ITPUB社区12周年站庆徽章
日期:2013-10-08 14:56:08ITPUB社区12周年站庆徽章
日期:2013-10-08 14:59:19ITPUB社区12周年站庆徽章
日期:2013-10-08 15:00:34
 楼主| 发表于 2010-8-11 13:35 | 显示全部楼层
有人帮忙优化下吗?

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
16
2009新春纪念徽章
日期:2009-01-04 14:52:282014年新春福章
日期:2014-02-18 16:43:092013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥运会纪念徽章:艺术体操
日期:2012-07-24 12:01:27奥运会纪念徽章:乒乓球
日期:2012-07-23 18:08:47奥运会纪念徽章:铁人三项
日期:2012-06-20 13:06:10ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512011新春纪念徽章
日期:2011-02-18 11:43:332010广州亚运会纪念徽章:游泳
日期:2010-10-19 11:09:06
发表于 2010-8-11 13:53 | 显示全部楼层
10g 以上版本
set serveroutput off
SELECT /*+ gather_plan_statistics */
a.rpt_dt,
       a.rgn_no,
       a.org_no,
       a.hdbz,
       a.ckdm,
       a.kzbz,
       a.fdll,
       a.svtm,
       b.itvl_no,
       a.bz,
       COUNT(1) AS cus_cout
FROM (SELECT
       c.rpt_dt,
       c.rgn_no,
       c.org_no,
       c.bz,
       c.cus_no,
       DECODE(GROUPING(c.hdbz), 1, '#', c.hdbz) AS hdbz,
       DECODE(GROUPING(c.ckdm), 1, '#', c.ckdm) AS ckdm,
       DECODE(GROUPING(c.kzbz), 1, '#', c.kzbz) AS kzbz,
       DECODE(GROUPING(c.fdll), 1, '#', c.fdll) AS fdll,
       DECODE(GROUPING(c.svtm), 1, 8888, c.svtm) AS svtm,
       SUM(c.je)  
      FROM table c
      WHERE c.rgn_no = '45129'
            AND c.rpt_dt = '20100731'
            and c.org_no='8130'
            AND c.cls_flg = '0'
            AND c.bz = '001'
      GROUP BY c.rpt_dt,
               c.rgn_no,
               c.org_no,
               c.bz,
               c.cus_no,
               ROLLUP(c.hdbz, c.ckdm, c.kzbz),
               ROLLUP(c.fdll),
               ROLLUP(c.svtm)) a,
     CBS_CDE_RPTAMTDIS b
WHERE a.bkrmb_bal > b.amt_min
      AND a.bkrmb_bal <= b.amt_max
GROUP BY a.rpt_dt,
         a.rgn_no,
         a.org_no,
         a.hdbz,
         a.ckdm,
         a.kzbz,
         a.fdll,
         a.svtm,
         b.itvl_no,
         a.bz;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last');

10g 以前的 做个10046 的trace 文件传上来。

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


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

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