查看: 4881|回复: 25

[性能调整] 哪位童鞋帮忙看看这个查询该怎么优化. 多谢

[复制链接]
论坛徽章:
0
发表于 2009-8-12 16:01 | 显示全部楼层 |阅读模式
1pub币
这里CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM是个大表, 有近600万行. 其他的表都不算大.

SELECT
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_GRP_DISP_NAM,
  CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM,
  sum(CNR.TB_CNR021_FCT_CNCSSN.NET_CNCSSN_AMT),
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_LN_CD,
  CNR.TB_CNR011_DIM_PROD_FMLY.MFG_SITE_NAM,
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_FMLY_NAM,
  CNR.TB_CNR014_DIM_BRAND.BRAND_CD,
  CNR.TB_CNR014_DIM_BRAND.BRAND_NAM,
  CNR.TB_CNR013_DIM_WRNT_STS.WRNT_STS_CD,
  CNR.TB_CNR015_DIM_CNCSSN_CTGY.CNCSSN_CTGY_CD,
  CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW,
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_GRP_NAM
FROM
  CNR.TB_CNR011_DIM_PROD_FMLY,
  CNR.TB_CNR021_FCT_CNCSSN,
  CNR.TB_CNR014_DIM_BRAND,
  CNR.TB_CNR013_DIM_WRNT_STS,
  CNR.TB_CNR015_DIM_CNCSSN_CTGY,
  CU.TB_COM02111_DIM_TIME
WHERE
  ( CNR.TB_CNR011_DIM_PROD_FMLY.PROD_FMLY_ID=CNR.TB_CNR021_FCT_CNCSSN.PROD_FMLY_ID  )
  AND  ( CNR.TB_CNR014_DIM_BRAND.BRAND_ID=CNR.TB_CNR021_FCT_CNCSSN.BRAND_ID  )
  AND  ( CNR.TB_CNR021_FCT_CNCSSN.WRNT_STS_ID=CNR.TB_CNR013_DIM_WRNT_STS.WRNT_STS_ID  )
  AND  ( CNR.TB_CNR021_FCT_CNCSSN.CNCSSN_CTGY_ID=CNR.TB_CNR015_DIM_CNCSSN_CTGY.CNCSSN_CTGY_ID  )
  AND  ( CNR.TB_CNR021_FCT_CNCSSN.RPT_ID = (select wkly_rpt_id from cnr.tb_cnr005_rep_ctrl)  )
  AND  ( CNR.TB_CNR021_FCT_CNCSSN.CALENDAR_DT=CU.TB_COM02111_DIM_TIME.CALENDAR_DT  )
  AND  ( CNR.TB_CNR021_FCT_CNCSSN.CNCSSN_UNV_FLG='Y'  )
  AND
  CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW  >  200901  AND
  CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW  <  200929
GROUP BY
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_GRP_DISP_NAM,
  CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM,
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_LN_CD,
  CNR.TB_CNR011_DIM_PROD_FMLY.MFG_SITE_NAM,
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_FMLY_NAM,
  CNR.TB_CNR014_DIM_BRAND.BRAND_CD,
  CNR.TB_CNR014_DIM_BRAND.BRAND_NAM,
  CNR.TB_CNR013_DIM_WRNT_STS.WRNT_STS_CD,
  CNR.TB_CNR015_DIM_CNCSSN_CTGY.CNCSSN_CTGY_CD,
  CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW,
  CNR.TB_CNR011_DIM_PROD_FMLY.PROD_GRP_NAM;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               |  Name                      | Rows| Bytes |TempSpc| Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            | 376K|    41M|       |  8681 |       |       |
|   1 |  SORT GROUP BY                          |                            | 376K|    41M|    90M|  8681 |       |       |
|*  2 |   HASH JOIN                             |                            | 376K|    41M|       |  2058 |       |       |
|   3 |    TABLE ACCESS FULL                    | TB_CNR015_DIM_CNCSSN_CTGY  |  59 |   590 |       |     2 |       |       |
|*  4 |    HASH JOIN                            |                            | 376K|    37M|       |  2053 |       |       |
|   5 |     TABLE ACCESS FULL                   | TB_CNR014_DIM_BRAND        |   9 |   108 |       |     2 |       |       |
|*  6 |     HASH JOIN                           |                            | 376K|    33M|       |  2048 |       |       |
|   7 |      TABLE ACCESS FULL                  | TB_CNR011_DIM_PROD_FMLY    |1114 | 60156 |       |     5 |       |       |
|*  8 |      HASH JOIN                          |                            | 376K|    13M|       |  2032 |       |       |
|   9 |       TABLE ACCESS BY INDEX ROWID       | TB_COM02111_DIM_TIME       | 115 |  1265 |       |    31 |       |       |
|* 10 |        INDEX RANGE SCAN                 | UX_COM02111_11             |   1 |       |       |     2 |       |       |
|* 11 |       TABLE ACCESS BY GLOBAL INDEX ROWID| TB_CNR021_FCT_CNCSSN       | 296K|  6670K|       |   399 | ROWID | ROW L |
|  12 |        NESTED LOOPS                     |                            |1484K|    39M|       |  1997 |       |       |
|  13 |         TABLE ACCESS FULL               | TB_CNR013_DIM_WRNT_STS     |   5 |    25 |       |     2 |       |       |
|* 14 |         INDEX RANGE SCAN                | IX_CNR021_3                |1484K|       |       |   392 |       |       |
|  15 |        TABLE ACCESS FULL                | TB_CNR005_REP_CTRL         |   1 |     1 |       |     2 |       |       |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TB_CNR021_FCT_CNCSSN"."CNCSSN_CTGY_ID"="TB_CNR015_DIM_CNCSSN_CTGY"."CNCSSN_CTGY_ID")
   4 - access("TB_CNR014_DIM_BRAND"."BRAND_ID"="TB_CNR021_FCT_CNCSSN"."BRAND_ID")
   6 - access("TB_CNR011_DIM_PROD_FMLY"."PROD_FMLY_ID"="TB_CNR021_FCT_CNCSSN"."PROD_FMLY_ID")
   8 - access("TB_CNR021_FCT_CNCSSN"."CALENDAR_DT"="TB_COM02111_DIM_TIME"."CALENDAR_DT")
  10 - access("TB_COM02111_DIM_TIME"."FISCAL_YEAR_WEEK_YYYYWW">=200901 AND
              "TB_COM02111_DIM_TIME"."FISCAL_YEAR_WEEK_YYYYWW"<=200929)
  11 - filter("TB_CNR021_FCT_CNCSSN"."CNCSSN_UNV_FLG"='Y' AND "TB_CNR021_FCT_CNCSSN"."RPT_ID"= (SELECT /*+ */
              "TB_CNR005_REP_CTRL"."WKLY_RPT_ID" FROM "CNR"."TB_CNR005_REP_CTRL" "TB_CNR005_REP_CTRL"))
  14 - access("TB_CNR021_FCT_CNCSSN"."WRNT_STS_ID"="TB_CNR013_DIM_WRNT_STS"."WRNT_STS_ID")

Note: cpu costing is off

36 rows selected.

论坛徽章:
1
2009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-8-12 17:45 | 显示全部楼层
收集相关表和索引的统计信息试试

使用道具 举报

回复
论坛徽章:
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
发表于 2009-8-12 20:00 | 显示全部楼层
看不出很慢

使用道具 举报

回复
论坛徽章:
12
2009新春纪念徽章
日期:2009-01-04 14:52:28优秀写手
日期:2014-06-20 06:00:12蛋疼蛋
日期:2012-02-22 08:52:48ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04生肖徽章2007版:鸡
日期:2009-11-11 13:17:35祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB8周年纪念徽章
日期:2009-09-27 10:21:21生肖徽章2007版:龙
日期:2009-08-09 19:39:29生肖徽章2007版:鸡
日期:2009-08-03 23:22:352009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-8-12 20:24 | 显示全部楼层
数据库哪个版本
感觉sort group by那有问题

使用道具 举报

回复
论坛徽章:
0
发表于 2009-8-12 20:50 | 显示全部楼层
感觉只能在索引和统计信息收集上下功夫了

使用道具 举报

回复
论坛徽章:
9
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44授权会员
日期:2008-02-27 12:51:45生肖徽章2007版:狗
日期:2008-10-21 10:37:532009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:兔
日期:2009-02-17 07:58:08ITPUB8周年纪念徽章
日期:2009-09-27 10:21:212010新春纪念徽章
日期:2010-03-01 11:06:24双黄蛋
日期:2012-03-03 09:43:36
发表于 2009-8-12 21:46 | 显示全部楼层
数据库版本信息补上来;

对着关联字段查看你的索引情况,没有的和开发人员确认下补上;

如果情况紧急可先对表进行分析再看看;

使用道具 举报

回复
论坛徽章:
3
九尾狐狸
日期:2006-04-12 17:47:49ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2009-8-12 23:14 | 显示全部楼层
建议
1. 先收集每个QUERY object的统计信息;
2. 对CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM加并行和full,
/*+ full(CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM) parallel (CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM 8)*/.
3. 察看sort area size, 如果可能,增大它; 如果是10g的话,可以试一下HASH GROUPBY .
4. 如果表的数据变化不大,使用物化视图。

[ 本帖最后由 kl911 于 2009-8-12 23:18 编辑 ]

使用道具 举报

回复
论坛徽章:
55
马上加薪
日期:2014-02-19 11:55:142010广州亚运会纪念徽章:排球
日期:2011-04-27 13:27:19SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:01生肖徽章2007版:兔
日期:2011-01-20 12:58:49
发表于 2009-8-12 23:56 | 显示全部楼层
分析表,看看

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2009-8-13 16:19 | 显示全部楼层
多谢各位. 数据库是9206, 统计信息也是最新的. sort_area_size=10238976. 小了点? 但是这是其他DBA以前设好的, 没充分理由一般不动. 物化视图也是我考虑之一, 但是程序员说不行.

使用道具 举报

回复
论坛徽章:
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
发表于 2009-8-13 16:27 | 显示全部楼层
该语句是运用在OLTP还是OLAP?

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表