查看: 4444|回复: 16

[性能调整] 一条耗时较高,表数据达四亿的语句求优化

[复制链接]
论坛徽章:
10
九尾狐狸
日期:2006-04-12 17:38:17会员2006贡献徽章
日期:2006-04-17 13:46:34ITPUB元老
日期:2006-04-17 20:13:232011新春纪念徽章
日期:2011-02-18 11:43:35
发表于 2011-5-13 10:40 | 显示全部楼层 |阅读模式
select swjg.mc_j, zspm.mc_j, count(distinct bb.nsgrnbm)
    from db_ttxx.t_sxx_dwjfdjxx djxx,
         DB_djxx.t_dj_jgnxx            jg,
         db_ttxx.t_sxx_fxzmxsbb bb,
         db_xtwh.t_dm_gy_zspm  zspm,
         db_xtwh.t_dm_gy_swjg  swjg
   where djxx.nsrxxx = jg.nsrxxx
     and djxx.jfdwnbm = bb.jfdwnbm
     and jg.hsjg_dm = '244010300'
     and bb.lr_sj >= to_date('2011-5-6','YYYY-MM-DD')
     and bb.lr_sj < to_date ('2011-5-7','YYYY-MM-DD')
     and bb.zsxm_dm = '66'
     and zspm.zsxm_dm = '66'
     and bb.zspm_dm = zspm.zspm_dm
     and djxx.swgljg_dm = swjg.swjg_dm
   group by swjg.mc_j, zspm.mc_j;

这是10046 level 12后得出的结果:   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       19     34.89    1214.91     216187     326475          0         256
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21     34.89    1214.91     216187     326475          0         256

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

        
|   0 |   
|   1 |     256  SORT GROUP BY
|*  2 |   64097   FILTER
|*  3 |   64097    HASH JOIN
|*  4 |   72724     HASH JOIN
|*  5 |     161      TABLE ACCESS FULL T_DM_GY_ZSPM
|*  6 |   72724      HASH JOIN
|*  7 |  136082       HASH JOIN
|   8 |  243190        TABLE ACCESS BY INDEX ROWID T_DJ_jgnxx
|*  9 |  243190         INDEX RANGE SCAN I_DJ_jgnxx_HSJ (object id 94776)
|  10 | 3613241        TABLE ACCESS FULL T_sxx_DWJFDJXX
|* 11 | 4212491       TABLE ACCESS BY GLOBAL INDEX ROWID T_sxx_FXZMXSBB PARTITION: ROW LOCATION ROW LOCATION
|* 12 | 4212569        INDEX RANGE SCAN I_sxx_FXZMXSBB_LRSJ (object id 7457699)
|  13 |    5864     TABLE ACCESS FULL T_DM_GY_SWJG
  
  Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      19        0.00          0.00
  db file sequential read                    167747        0.88       1044.85
  buffer busy waits                               1        0.00          0.00
  latch free                                      1        0.00          0.00
  db file scattered read                       8479        0.23        137.93
  SQL*Net message from client                    19        0.00          0.09
********************************************************************************


这是explain plan for 得出的结果:
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                               |  Name               | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                     |  2346 |   309K|   136K|       |       |
|   1 |  SORT GROUP BY                          |                     |  2346 |   309K|   136K|       |       |
|*  2 |   FILTER                                |                     |       |       |       |       |       |
|*  3 |    HASH JOIN                            |                     |  2346 |   309K|   136K|       |       |
|*  4 |     HASH JOIN                           |                     |  2333 |   223K|   136K|       |       |
|*  5 |      TABLE ACCESS FULL                  | T_DM_GY_ZSPM        |    19 |   494 |     5 |       |       |
|*  6 |      HASH JOIN                          |                     | 12594 |   885K|   136K|       |       |
|*  7 |       HASH JOIN                         |                     | 36083 |  1444K| 20434 |       |       |
|   8 |        TABLE ACCESS BY INDEX ROWID      | T_DJ_jgnxx          | 35823 |   629K| 16064 |       |       |
|*  9 |         INDEX RANGE SCAN                | I_DJ_jgnxx_HSJ      | 35823 |       |   176 |       |       |
|  10 |        TABLE ACCESS FULL                | T_sxx_DWJFDJXX       |  3554K|    77M|  3387 |       |       |
|* 11 |       TABLE ACCESS BY GLOBAL INDEX ROWID| T_sxx_FXZMXSBB       |  1240K|    36M|   115K| ROWID | ROW L |
|* 12 |        INDEX RANGE SCAN                 | I_sxx_FXZMXSBB_LRSJ  |   229 |       |  8600 |       |       |
|  13 |     TABLE ACCESS FULL                   | T_DM_GY_SWJG        |  5860 |   211K|    13 |       |       |
---------------------------------------------------------------------------------------------------------------


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

   2 - filter(TO_DATE(:Z,:Z)<TO_DATE(:Z,:Z))
   3 - access("DJXX"."SWGLJG_DM"="SWJG"."SWJG_DM")
   4 - access("BB"."ZSPM_DM"="ZSPM"."ZSPM_DM")
   5 - filter("ZSPM"."ZSXM_DM"=:Z)
   6 - access("DJXX"."JFDWNBM"="BB"."JFDWNBM")
   7 - access("DJXX"."nsrxxx"="JG"."nsrxxx")
   9 - access("JG"."HSJG_DM"=:Z)
  11 - filter("BB"."ZSXM_DM"=:Z)
  12 - access("BB"."LR_SJ">=TO_DATE(:Z,:Z) AND "BB"."LR_SJ"<TO_DATE(:Z,:Z))
   

耗时大概在20分钟左右, T_sxx_FXZMXSBB  数据量大概四亿多,分区表.
T_sxx_DWJFDJXX 的主键是nsrxxx ,搞不明白,为什么
7 - access("DJXX"."nsrxxx"="JG"."nsrxxx")
做的hash join 却利用不了 T_sxx_FXZMXSBB 的主键索引
T_DJ_jgnxx   的主键也是nsrxxx

6 - access("DJXX"."JFDWNBM"="BB"."JFDWNBM")
这个字段也是这两个表的主键

我用hint强行让他们走主键的索引,反而三个多小时都出不了结果,得到ora-01555

请问如何优化?
论坛徽章:
120
现任管理团队成员
日期:2011-05-07 01:45:08乌索普
日期:2019-02-14 23:54:04
发表于 2011-5-13 10:53 | 显示全部楼层
格式化以下

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                               |  Name               | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                     |  2346 |   309K|   136K|       |       |
|   1 |  SORT GROUP BY                          |                     |  2346 |   309K|   136K|       |       |
|*  2 |   FILTER                                |                     |       |       |       |       |       |
|*  3 |    HASH JOIN                            |                     |  2346 |   309K|   136K|       |       |
|*  4 |     HASH JOIN                           |                     |  2333 |   223K|   136K|       |       |
|*  5 |      TABLE ACCESS FULL                  | T_DM_GY_ZSPM        |    19 |   494 |     5 |       |       |
|*  6 |      HASH JOIN                          |                     | 12594 |   885K|   136K|       |       |
|*  7 |       HASH JOIN                         |                     | 36083 |  1444K| 20434 |       |       |
|   8 |        TABLE ACCESS BY INDEX ROWID      | T_DJ_jgnxx          | 35823 |   629K| 16064 |       |       |
|*  9 |         INDEX RANGE SCAN                | I_DJ_jgnxx_HSJ      | 35823 |       |   176 |       |       |
|  10 |        TABLE ACCESS FULL                | T_sxx_DWJFDJXX       |  3554K|    77M|  3387 |       |       |
|* 11 |       TABLE ACCESS BY GLOBAL INDEX ROWID| T_sxx_FXZMXSBB       |  1240K|    36M|   115K| ROWID | ROW L |
|* 12 |        INDEX RANGE SCAN                 | I_sxx_FXZMXSBB_LRSJ  |   229 |       |  8600 |       |       |
|  13 |     TABLE ACCESS FULL                   | T_DM_GY_SWJG        |  5860 |   211K|    13 |       |       |
---------------------------------------------------------------------------------------------------------------

|   0 |   
|   1 |     256  SORT GROUP BY
|*  2 |   64097   FILTER
|*  3 |   64097    HASH JOIN
|*  4 |   72724     HASH JOIN
|*  5 |     161      TABLE ACCESS FULL T_DM_GY_ZSPM
|*  6 |   72724      HASH JOIN
|*  7 |  136082       HASH JOIN
|   8 |  243190        TABLE ACCESS BY INDEX ROWID T_DJ_jgnxx
|*  9 |  243190         INDEX RANGE SCAN I_DJ_jgnxx_HSJ (object id 94776)
|  10 | 3613241        TABLE ACCESS FULL T_sxx_DWJFDJXX
|* 11 | 4212491       TABLE ACCESS BY GLOBAL INDEX ROWID T_sxx_FXZMXSBB PARTITION: ROW LOCATION ROW LOCATION
|* 12 | 4212569        INDEX RANGE SCAN I_sxx_FXZMXSBB_LRSJ (object id 7457699)
|  13 |    5864     TABLE ACCESS FULL T_DM_GY_SWJG

使用道具 举报

回复
论坛徽章:
10
九尾狐狸
日期:2006-04-12 17:38:17会员2006贡献徽章
日期:2006-04-17 13:46:34ITPUB元老
日期:2006-04-17 20:13:232011新春纪念徽章
日期:2011-02-18 11:43:35
 楼主| 发表于 2011-5-13 11:46 | 显示全部楼层
请问楼上的兄弟,你是如何格式化的,学习一下

使用道具 举报

回复
论坛徽章:
18
紫蛋头
日期:2011-07-25 08:06:53咸鸭蛋
日期:2011-12-27 11:35:38鲜花蛋
日期:2012-01-11 11:08:36奥运会纪念徽章:射击
日期:2012-09-11 08:56:18奥运会纪念徽章:体操
日期:2012-10-25 09:07:51紫蛋头
日期:2012-12-10 13:46:51灰彻蛋
日期:2013-01-28 14:23:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2011-5-13 13:07 | 显示全部楼层
呵呵 我估計是他拿你貼出來的執行計劃,然後加空格對齊的,呵呵

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2011-5-13 13:24 | 显示全部楼层
看起来像是税务管理系统
需求像是要统计某个人在某天征收某税项的情况

如果不是库表设计有问题的话,就是写SQL没有合理利用库表的字段。。。

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2011-5-13 13:25 | 显示全部楼层
选择宋体,可以对齐SQL输出结果

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
32
生肖徽章2007版:狗
日期:2009-07-20 17:10:18茶鸡蛋
日期:2013-05-23 16:34:43茶鸡蛋
日期:2013-07-19 14:39:22ITPUB社区千里马徽章
日期:2013-08-22 09:58:03雪铁龙
日期:2013-08-23 13:04:11林肯
日期:2013-09-04 14:07:20兰博基尼
日期:2013-09-16 13:45:20马上有钱
日期:2014-05-14 14:07:392014年世界杯参赛球队: 波黑
日期:2014-07-03 13:24:39马上有对象
日期:2014-10-30 14:04:32
发表于 2011-5-13 13:28 | 显示全部楼层
楼主可以先用ORACLE的SQL_TUNING_ADVISOR顾问程序分析一下,看ORACLE给出什么好的优化建议

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期: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
发表于 2011-5-13 13:29 | 显示全部楼层
alter session enforce parallel query

使用道具 举报

回复
论坛徽章:
120
现任管理团队成员
日期:2011-05-07 01:45:08乌索普
日期:2019-02-14 23:54:04
发表于 2011-5-13 13:30 | 显示全部楼层
原帖由 〇〇 于 2011-5-13 13:29 发表
alter session enforce parallel query

别用parallel~要不然那个几亿的会全表扫描

使用道具 举报

回复
论坛徽章:
6
ITPUB季度 技术新星
日期:2011-08-31 15:27:58ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292012新春纪念徽章
日期:2012-01-04 11:57:36咸鸭蛋
日期:2012-03-05 13:10:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00优秀写手
日期:2013-12-18 09:29:09
发表于 2011-5-13 13:34 | 显示全部楼层
原帖由 zergduan 于 2011-5-13 13:30 发表

别用parallel~要不然那个几亿的会全表扫描


why? index range scan有问题吗

使用道具 举报

回复

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

本版积分规则 发表回复

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