查看: 4188|回复: 20

SQL优化,请大家帮忙看看!谢谢

[复制链接]
认证徽章
论坛徽章:
89
生肖徽章2007版:鼠
日期:2009-03-10 21:19:05生肖徽章2007版:牛
日期:2008-01-02 17:35:53生肖徽章2007版:虎
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2009-10-14 09:31:39生肖徽章2007版:龙
日期:2009-03-10 21:14:14生肖徽章2007版:蛇
日期:2009-03-16 10:41:01生肖徽章2007版:马
日期:2009-10-21 16:45:13生肖徽章2007版:羊
日期:2009-03-24 07:32:42生肖徽章2007版:猴
日期:2008-01-02 17:35:53生肖徽章2007版:鸡
日期:2008-01-02 17:35:53
发表于 2009-8-11 15:42 | 显示全部楼层 |阅读模式
SELECT csmv.icnum, sem.class_id, unit.semester_id, department.speciality_id,
       unit.department_id, csmv.course_id, csmv.begintime, csmv.endtime
FROM topbox_coursestudy_m_view csmv,topbox_courseware cw,topbox_unit unit,topbox_department department,topbox_semester sem
where cw.ID(+) = csmv.course_id                                                                                                            
and unit.ID(+) = cw.unit_id
and department.ID(+) = unit.department_id
and sem.ID(+) = department.semester_id

47222973 rows selected.

Elapsed: 00:12:15.23

Execution Plan
----------------------------------------------------------
Plan hash value: 957649342

------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                           |    47M|  4905M| 95395   (4)| 00:19:05 |
|*  1 |  HASH JOIN RIGHT OUTER   |                           |    47M|  4905M| 95395   (4)| 00:19:05 |
|   2 |   TABLE ACCESS FULL      | TOPBOX_SEMESTER           |   141 |  1269 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER  |                           |    47M|  4500M| 94841   (4)| 00:18:59 |
|   4 |    TABLE ACCESS FULL     | TOPBOX_DEPARTMENT         |  1081 | 17296 |     5   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER |                           |    47M|  3780M| 94285   (3)| 00:18:52 |
|   6 |     TABLE ACCESS FULL    | TOPBOX_UNIT               |  7112 |   118K|    13   (0)| 00:00:01 |
|*  7 |     HASH JOIN RIGHT OUTER|                           |    47M|  3015M| 93720   (3)| 00:18:45 |
|   8 |      TABLE ACCESS FULL   | TOPBOX_COURSEWARE         | 22727 |   266K|    97   (2)| 00:00:02 |
|   9 |      MAT_VIEW ACCESS FULL| TOPBOX_COURSESTUDY_M_VIEW |    47M|  2475M| 93072   (2)| 00:18:37 |
------------------------------------------------------------------------------------------------------

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

   1 - access("SEM"."ID"(+)="DEPARTMENT"."SEMESTER_ID")
   3 - access("DEPARTMENT"."ID"(+)="UNIT"."DEPARTMENT_ID")
   5 - access("UNIT"."ID"(+)="CW"."UNIT_ID")
   7 - access("CW"."ID"(+)="CSMV"."COURSE_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    3557739  consistent gets
     408883  physical reads
          0  redo size
4231011027  bytes sent via SQL*Net to client
   34630647  bytes received via SQL*Net from client
    3148200  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   47222973  rows processed

请大家看看这个SQL,还能如何优化一下

SQL> select count(*) from topbox_semester;                                                                                   

  COUNT(*)
----------
       141

SQL> select count(*) from topbox_department;                                                                                 

  COUNT(*)
----------
      1081

SQL> select count(*) from topbox_unit;                                                                                       

  COUNT(*)
----------
      7112

SQL> select count(*) from topbox_courseware;                                                                                 

  COUNT(*)
----------
     22727

optimizer_index_cost_adj              100
optimizer_mode                               ALL_ROWS   
db_file_multiblock_read_count      16
cursor_sharing                                 EXACT

下面是TOPBOX_COURSESTUDY_M_VIEW里的查询语句和执行计划
select   cst.icnum as icnum, cst.courseware_id as course_id,
    to_char (min (decode (state, 1, studytime, sysdate)),'yyyy-mm-dd hh24:mi:ss') begintie,                                                                                          
    to_char (min (decode (state, 0, studytime, sysdate)),'yyyy-mm-dd hh24:mi:ss') endtie                                                                                             
from topbox_coursestudy cst
group by cst.icnum, cst.courseware_id;

47222973 rows selected.

Elapsed: 00:12:37.41

Execution Plan
----------------------------------------------------------
Plan hash value: 2005262610

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |   105M|  2320M|       |   895K  (3)| 02:59:03 |
|   1 |  HASH GROUP BY     |                    |   105M|  2320M|  7315M|   895K  (3)| 02:59:03 |
|   2 |   TABLE ACCESS FULL| TOPBOX_COURSESTUDY |   105M|  2320M|       |   160K  (3)| 00:32:01 |
-------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       4260  recursive calls
          0  db block gets
     714969  consistent gets
     979455  physical reads
          0  redo size
3232328540  bytes sent via SQL*Net to client
   34630647  bytes received via SQL*Net from client
    3148200  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   47222973  rows processed
   
SQL> select count(*) from topbox_coursestudy;                                                                                

  COUNT(*)
----------
105798090   

COURSEWARE_ID,ICNUM,STATE,TOPBOXCODE上有索引

现在对表已经做了统计信息的收集

请大家帮忙看下有什么优化建议吗?
论坛徽章:
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
发表于 2009-8-11 15:49 | 显示全部楼层
查明细查出4000多w行 ?? ,查出来搞啥

使用道具 举报

回复
认证徽章
论坛徽章:
89
生肖徽章2007版:鼠
日期:2009-03-10 21:19:05生肖徽章2007版:牛
日期:2008-01-02 17:35:53生肖徽章2007版:虎
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2009-10-14 09:31:39生肖徽章2007版:龙
日期:2009-03-10 21:14:14生肖徽章2007版:蛇
日期:2009-03-16 10:41:01生肖徽章2007版:马
日期:2009-10-21 16:45:13生肖徽章2007版:羊
日期:2009-03-24 07:32:42生肖徽章2007版:猴
日期:2008-01-02 17:35:53生肖徽章2007版:鸡
日期:2008-01-02 17:35:53
发表于 2009-8-11 15:51 | 显示全部楼层
原帖由 棉花糖ONE 于 2009-8-11 15:49 发表
查明细查出4000多w行 ?? ,查出来搞啥


这是一个物化视图的select语句。要把这些结果保存到物化视图中

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2009-8-11 15:57 | 显示全部楼层
只有关联,没有过滤,又是外关联,没法优化。

使用道具 举报

回复
论坛徽章:
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
发表于 2009-8-11 16:01 | 显示全部楼层

回复 #3 nw5452 的帖子

parallel + 加大pga

使用道具 举报

回复
认证徽章
论坛徽章:
89
生肖徽章2007版:鼠
日期:2009-03-10 21:19:05生肖徽章2007版:牛
日期:2008-01-02 17:35:53生肖徽章2007版:虎
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2009-10-14 09:31:39生肖徽章2007版:龙
日期:2009-03-10 21:14:14生肖徽章2007版:蛇
日期:2009-03-16 10:41:01生肖徽章2007版:马
日期:2009-10-21 16:45:13生肖徽章2007版:羊
日期:2009-03-24 07:32:42生肖徽章2007版:猴
日期:2008-01-02 17:35:53生肖徽章2007版:鸡
日期:2008-01-02 17:35:53
发表于 2009-8-11 16:33 | 显示全部楼层
DROP MATERIALIZED VIEW TOPBOX.TOPBOX_ORG_SC_M_VIEW;
CREATE MATERIALIZED VIEW TOPBOX.TOPBOX_ORG_SC_M_VIEW

DROP MATERIALIZED VIEW TOPBOX.TOPBOX_CSDETAIL_M_VIEW;
CREATE MATERIALIZED VIEW TOPBOX.TOPBOX_CSDETAIL_M_VIEW

DROP MATERIALIZED VIEW TOPBOX.TOPBOX_COURSESTUDY_M_VIEW;
CREATE MATERIALIZED VIEW TOPBOX.TOPBOX_COURSESTUDY_M_VIEW

现在主要的问题是,这3个物化视图要通过drop在create,我们现在在做数据库的迁移,所以没做完一个省的基本就要把这3个MVrefresh一次。
但就是刷一次的时间太长了,所以考虑看能不能把里面的SQL进行调优。
前两个MV刷一次几十分钟还可以忍受,但最后一个刷5个小时都没刷完.....这个真忍不了了

pga_aggregate_target                1592M

SQL> show sga                                                                                                               

Total System Global Area 7784628224 bytes
Fixed Size                  2032944 bytes
Variable Size            1325402832 bytes
Database Buffers         6442450944 bytes
Redo Buffers               14741504 bytes

OS:linux
oracle:10.2.0.1

SQL> Select  PGA_TARGET_FOR_ESTIMATE "pga size",                                                                             
  PGA_TARGET_FACTOR  "pga factor",BYTES_PROCESSED "bytes proc",
  3    ESTD_EXTRA_BYTES_RW "estd byte RW",                                                                                   
  4    ESTD_PGA_CACHE_HIT_PERCENTAGE "setd pga hit",                                                                        
  5    ESTD_OVERALLOC_COUNT "estd overalloc cnt"                                                                             
  6  from  v$pga_target_advice;                                                                                             

  pga size pga factor bytes proc estd byte RW setd pga hit estd overalloc cnt
---------- ---------- ---------- ------------ ------------ ------------------
208666624       .125 1.1052E+12   1.9055E+12           37                 52
417333248        .25 1.1052E+12   6.0755E+11           65                  0
834666496         .5 1.1052E+12   6.0356E+11           65                  0
1251999744        .75 1.1052E+12   6.0356E+11           65                  0
1669332992          1 1.1052E+12   6.0204E+11           65                  0
2003198976        1.2 1.1052E+12   5.9858E+11           65                  0
2337065984        1.4 1.1052E+12   5.9858E+11           65                  0
2670931968        1.6 1.1052E+12   5.9858E+11           65                  0
3004798976        1.8 1.1052E+12   5.9858E+11           65                  0
3338665984          2 1.1052E+12   5.9858E+11           65                  0
5007998976          3 1.1052E+12   5.9858E+11           65                  0

pga有必要调整吗?命中很低,晕

使用道具 举报

回复
认证徽章
论坛徽章:
89
生肖徽章2007版:鼠
日期:2009-03-10 21:19:05生肖徽章2007版:牛
日期:2008-01-02 17:35:53生肖徽章2007版:虎
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2009-10-14 09:31:39生肖徽章2007版:龙
日期:2009-03-10 21:14:14生肖徽章2007版:蛇
日期:2009-03-16 10:41:01生肖徽章2007版:马
日期:2009-10-21 16:45:13生肖徽章2007版:羊
日期:2009-03-24 07:32:42生肖徽章2007版:猴
日期:2008-01-02 17:35:53生肖徽章2007版:鸡
日期:2008-01-02 17:35:53
发表于 2009-8-11 16:35 | 显示全部楼层
SELECT io.org_id, io.TYPE, io.sector, io.class_id, io.semester_id,
       io.speciality_id, io.department_id, io.course_id,
       NVL (usv.usercount, 0) AS usercount, io.totalcount
  FROM (SELECT   usi.org_id, ui.TYPE, uidetail.sector AS sector, usi.class_id,
                 usi.semester_id, usi.speciality_id,
                 depart.ID AS department_id, cw.ID AS course_id,
                 COUNT (DISTINCT usi.icnum) AS totalcount
            FROM topbox_userstudyinfo usi LEFT OUTER JOIN topbox_userinfo ui
                 ON ui.icnum = usi.icnum
                 LEFT OUTER JOIN topbox_userinfodetail uidetail
                 ON uidetail.icnum = usi.icnum
                 LEFT OUTER JOIN topbox_department depart
                 ON depart.speciality_id = usi.speciality_id
               AND depart.semester_id = usi.semester_id
                 LEFT OUTER JOIN topbox_unit unit
                 ON unit.department_id = depart.ID
               AND unit.semester_id = usi.semester_id
                 LEFT OUTER JOIN topbox_courseware cw ON cw.unit_id = unit.ID
           WHERE usi.org_id IS NOT NULL AND usi.icnum IS NOT NULL
        GROUP BY usi.org_id,
                 ui.TYPE,
                 uidetail.sector,
                 usi.class_id,
                 usi.semester_id,
                 usi.speciality_id,
                 depart.ID,
                 cw.ID) io
       LEFT OUTER JOIN
       (SELECT   usv.org_id AS org_id, ui.TYPE AS TYPE,
                 uidetail.sector AS sector, usv.class_id AS class_id,
                 usv.semester_id AS semester_id,
                 usv.speciality_id AS speciality_id,
                 csv.department_id AS department_id,
                 csv.course_id AS course_id,
                 COUNT (DISTINCT usv.icnum) AS usercount
            FROM topbox_userstudyinfo usv LEFT OUTER JOIN topbox_userinfo ui
                 ON ui.icnum = usv.icnum AND ui.TYPE IS NOT NULL
                 LEFT OUTER JOIN topbox_userinfodetail uidetail
                 ON uidetail.icnum = usv.icnum
                 LEFT OUTER JOIN topbox_csdetail_m_view csv
                 ON csv.icnum = usv.icnum
               AND csv.class_id = usv.class_id
               AND csv.semester_id = usv.semester_id
               AND csv.speciality_id = usv.speciality_id
           WHERE usv.org_id IS NOT NULL
        GROUP BY usv.org_id,
                 ui.TYPE,
                 uidetail.sector,
                 usv.class_id,
                 usv.semester_id,
                 usv.speciality_id,
                 csv.department_id,
                 csv.course_id) usv
       ON usv.org_id = io.org_id
     AND usv.class_id = io.class_id
     AND usv.semester_id = io.semester_id
     AND usv.speciality_id = io.speciality_id
     AND usv.department_id = io.department_id
     AND usv.course_id = io.course_id
     AND usv.TYPE = io.TYPE
     AND NVL (usv.sector, 'null') = NVL (io.sector, 'null')
WHERE io.course_id IS NOT NULL;

这个是刷新5个多小时都没完事的mV的select语句

使用道具 举报

回复
论坛徽章:
3
授权会员
日期:2005-12-27 14:11:11生肖徽章2007版:牛
日期:2009-03-31 09:55:262009日食纪念
日期:2009-07-22 09:30:00
发表于 2009-8-11 17:19 | 显示全部楼层
能详细说说具体的需求么?

使用道具 举报

回复
论坛徽章:
25
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442010世博会纪念徽章
日期:2010-07-30 12:07:232011新春纪念徽章
日期:2011-02-18 11:43:332010广州亚运会纪念徽章:高尔夫球
日期:2011-04-11 18:22:37蜘蛛蛋
日期:2011-08-17 08:44:40ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15复活蛋
日期:2011-12-15 09:06:552012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2009-8-11 17:21 | 显示全部楼层
只能提高配置了

使用道具 举报

回复
招聘 : 数据库管理员
认证徽章
论坛徽章:
20
祖国60周年纪念徽章
日期:2009-10-09 08:28:00数据库板块每日发贴之星
日期:2011-02-20 01:01:01ITPUB季度 技术新星
日期:2011-04-02 10:31:09ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26玉石琵琶
日期:2012-02-21 15:04:38最佳人气徽章
日期:2012-03-13 17:39:18ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:192013年新春福章
日期:2013-02-25 14:51:242011新春纪念徽章
日期:2011-02-18 11:43:33
发表于 2009-8-11 17:36 | 显示全部楼层
扩容好了。。。。

使用道具 举报

回复

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

本版积分规则 发表回复

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