查看: 2305|回复: 7

帮忙看下SQL优化

[复制链接]
论坛徽章:
18
CTO参与奖
日期:2009-01-15 11:42:46奥运纪念徽章
日期:2012-11-28 09:33:31奥运纪念徽章
日期:2012-11-22 14:39:55灰彻蛋
日期:2012-05-15 14:37:582011新春纪念徽章
日期:2011-02-18 11:43:32生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-01-04 10:35:48数据库板块每日发贴之星
日期:2010-12-09 01:01:012010广州亚运会纪念徽章:帆船
日期:2010-12-06 11:01:472010广州亚运会纪念徽章:曲棍球
日期:2010-12-06 10:59:28
发表于 2010-11-15 17:08 | 显示全部楼层 |阅读模式
请帮忙分析一下下面的SQL应该从里优化,该建的索引基本上都建了,执行需要20多秒
但如果不分组的话(只执行红色的部分)执行速度会很快,只有0.5秒,,也就是说时间都花费在了group by上面
请问这种情况应该如何优化?

SELECT
         /*+ ordered index( eli EPO_LOCAL_ITEMS_U1) index(eairt EPO_AP_INVOICE_RT_TXN_N3) index(eait EPO_AP_INVOICE_TRANSFER_U1) */
         --index( eli EPO_LOCAL_ITEMS_U1) index(eairt EPO_AP_INVOICE_RT_TXN_N3) index(eait EPO_AP_INVOICE_TRANSFER_U1)
        --  eait.vendor_site_id --2009.03.24 CIC.XuZhe Closed
         nvl(to_number(eait.attribute12)
            ,eait.vendor_site_id) vendor_site_id --2009.03.24 CIC.XuZhe Add For Raken Multiple Tade Vendor
        ,eait.org_id
        ,eait.organization_id
        ,mtp.organization_code organization_code
        ,eait.invoice_yyyymm delivery_yyyymm
        ,msi.segment1 part_no
        ,msi.inventory_item_id inventory_item_id
        ,msi.description description
        ,msi.attribute1 spec
        ,msi.primary_uom_code uom
        ,pvsa.vendor_site_code vendor_site_code
        ,pv.vendor_id vendor_id
        ,pv.vendor_name vendor_name
        ,blrt.file_no
        ,eairt.check_code
        ,eairt.transaction_id
        ,esph.sum_sales_price
        ,eairt.split_quantity
        ,eait.order_type -- 20080129
        ,eli.start_date start_date ---20090407
        ,nvl(eli.end_date
            ,SYSDATE) end_date ---20090407
        FROM   epo.epo_ap_invoice_rt_txn   eairt--
              ,epo_ap_invoice_transfer     eait--
              ,epo_local_items             eli--
              ,mtl_system_items_b          msi
              ,mtl_parameters              mtp--
              ,po_vendor_sites_all         pvsa
              ,po_vendors                  pv
              ,epo.epo_sales_price_header  esph
              ,bics_local_rcv_transactions blrt
           
        WHERE  eait.transfer_id = eairt.transfer_id
        AND    eli.inventory_item_id = eairt.inventory_item_id -- 20071012 SongYM
        AND    eli.organization_id = eairt.organization_id
              --    AND  eli.vendor_site_id          = eait.vendor_site_id --2009.03.24 CIC.XuZhe Closed
        AND    eli.vendor_site_id =
               nvl(to_number(eait.attribute12) ,eait.vendor_site_id) --2009.03.24 CIC.XuZhe Add For Raken Multiple Tade Vendor
        AND    eli.start_date <= eairt.transaction_date
        AND    nvl(eli.end_date ,SYSDATE) >= trunc(eairt.transaction_date)
        AND    msi.organization_id = eairt.organization_id
        AND    msi.inventory_item_id = eairt.inventory_item_id
              --AND  msi.global_attribute6       = '1'                    -- Local Part No   20070607 deleted by wuhongdan
        AND    msi.item_type IN ('G'
                                ,'S'
                                ,'N'
                                ,'L') -- Zhao Yingqian added type'L' CSR: C2005091401000000225
        AND    mtp.organization_id = eait.organization_id
        AND    pvsa.org_id = eait.org_id
              --   AND  pvsa.vendor_site_id         = eait.vendor_site_id --2009.03.24 CIC.XuZhe Closed
        AND    pvsa.vendor_site_id =
               nvl(eait.attribute12
                   ,eait.vendor_site_id) --2009.03.24 CIC.XuZhe Add For Raken Multiple Tade Vendor
        AND    pvsa.attribute14 IN ('LL'
                                   ,'LN') -- Local LG, Local NonLG
        AND    nvl(pvsa.attribute12
                  ,'#') <> 'I' -- Intertransaction
        AND    pv.vendor_id = pvsa.vendor_id
        AND    esph.organization_id(+) = eairt.organization_id
        AND    esph.inventory_item_id(+) = eairt.inventory_item_id
        AND    esph.yyyymm(+) = to_char(eairt.transaction_date ,'yyyymm')
        AND    esph.latest_flag(+) = 'Y'
              --AND  decode(eait.order_type,'S',nvl(esph.yyyymm,eait.invoice_yyyymm),eait.invoice_yyyymm)  = eait.Invoice_Yyyymm
        AND    blrt.rcv_transaction_id(+) = eairt.transaction_id
        AND    blrt.check_code(+) = eairt.check_code
        

        GROUP  BY --eait.vendor_site_id --2009.03.24 CIC.XuZhe Closed
                  nvl(to_number(eait.attribute12)
                     ,eait.vendor_site_id) --2009.03.24 CIC.XuZhe Add For Raken Multiple Tade Vendor
                 ,eait.org_id
                 ,eait.organization_id
                 ,mtp.organization_code
                 ,eait.invoice_yyyymm
                 ,msi.segment1
                 ,msi.inventory_item_id
                 ,msi.description
                 ,msi.attribute1
                 ,msi.primary_uom_code
                 ,pvsa.vendor_site_code
                 ,pv.vendor_id
                 ,pv.vendor_name
                 ,blrt.file_no
                 ,eairt.check_code
                 ,eairt.transaction_id
                 ,esph.sum_sales_price
                 ,eairt.split_quantity
                 ,eait.order_type --20080129
                 ,eli.start_date ---20090407
                 ,eli.end_date; ---20090407


250623 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3830 Card=1 Bytes=30
          3)
   1    0   SORT (GROUP BY) (Cost=3830 Card=1 Bytes=303)
   2    1     NESTED LOOPS (OUTER) (Cost=3792 Card=1 Bytes=303)
   3    2       NESTED LOOPS (Cost=3790 Card=1 Bytes=284)
   4    3         NESTED LOOPS (OUTER) (Cost=3789 Card=1 Bytes=262)
   5    4           NESTED LOOPS (Cost=3788 Card=1 Bytes=216)
   6    5             NESTED LOOPS (Cost=3782 Card=3 Bytes=420)
   7    6               HASH JOIN (Cost=3777 Card=5 Bytes=560)
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'EPO_LOCAL_
          ITEMS' (Cost=2671 Card=4935 Bytes=123375)
   9    8                   INDEX (FULL SCAN) OF 'EPO_LOCAL_ITEMS_U1'
          (UNIQUE) (Cost=24 Card=1)
  10    7                 HASH JOIN (Cost=1061 Card=321050 Bytes=27931
          350)
  11   10                   HASH JOIN (Cost=206 Card=3663 Bytes=150183
          )
  12   11                     TABLE ACCESS (FULL) OF 'MTL_PARAMETERS'
          (Cost=3 Card=44 Bytes=352)
  13   11                     TABLE ACCESS (BY INDEX ROWID) OF 'EPO_AP
          _INVOICE_TRANSFER' (Cost=202 Card=3663 Bytes=120879)
  14   13                       INDEX (FULL SCAN) OF 'EPO_AP_INVOICE_T
          RANSFER_U1' (UNIQUE) (Cost=15 Card=3663)
  15   10                   TABLE ACCESS (BY INDEX ROWID) OF 'EPO_AP_I
          NVOICE_RT_TXN' (Cost=826 Card=321050 Bytes=14768300)
  16   15                     INDEX (FULL SCAN) OF 'EPO_AP_INVOICE_RT_
          TXN_N3' (NON-UNIQUE) (Cost=26 Card=198)
  17    6               TABLE ACCESS (BY INDEX ROWID) OF 'PO_VENDOR_SI
          TES_ALL' (Cost=1 Card=1 Bytes=28)
  18   17                 INDEX (UNIQUE SCAN) OF 'PO_VENDOR_SITES_U1'
          (UNIQUE)
  19    5             TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MTL_SYST
          EM_ITEMS_B' (Cost=2 Card=1 Bytes=76)
  20   19               INDEX (UNIQUE SCAN) OF 'MTL_SYSTEM_ITEMS_B_U1'
           (UNIQUE) (Cost=1 Card=1)
  21    4           TABLE ACCESS (BY INDEX ROWID) OF 'EPO_SALES_PRICE_
          HEADER' (Cost=1 Card=1 Bytes=46)
  22   21             INDEX (RANGE SCAN) OF 'EPO_SALES_PRICE_HEADER_U2
          ' (UNIQUE) (Cost=1 Card=1)
  23    3         TABLE ACCESS (BY INDEX ROWID) OF 'PO_VENDORS' (Cost=
          1 Card=1 Bytes=22)
  24   23           INDEX (UNIQUE SCAN) OF 'PO_VENDORS_U1' (UNIQUE)
  25    2       TABLE ACCESS (BY INDEX ROWID) OF 'BICS_LOCAL_RCV_TRANS
          ACTIONS' (Cost=2 Card=1 Bytes=19)
  26   25         INDEX (UNIQUE SCAN) OF 'BICS_LOCAL_RCV_TRANSACTIONS_
          N1' (UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
    3164934  consistent gets
       6786  physical reads
          0  redo size
    7565759  bytes sent via SQL*Net to client
     187378  bytes received via SQL*Net from client
      16710  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     250623  rows processed
论坛徽章:
3
数据库板块每日发贴之星
日期:2010-11-19 01:01:012011新春纪念徽章
日期:2011-01-04 10:24:58数据库板块每日发贴之星
日期:2011-01-16 01:01:01
发表于 2010-11-15 17:20 | 显示全部楼层
弱弱问一句,如果增大PGA_AGGREGATE_TARGET(假设你的workarea_size_policy=auto),可以快些吗?
就是增大pga里的sort area

使用道具 举报

回复
论坛徽章:
18
CTO参与奖
日期:2009-01-15 11:42:46奥运纪念徽章
日期:2012-11-28 09:33:31奥运纪念徽章
日期:2012-11-22 14:39:55灰彻蛋
日期:2012-05-15 14:37:582011新春纪念徽章
日期:2011-02-18 11:43:32生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-01-04 10:35:48数据库板块每日发贴之星
日期:2010-12-09 01:01:012010广州亚运会纪念徽章:帆船
日期:2010-12-06 11:01:472010广州亚运会纪念徽章:曲棍球
日期:2010-12-06 10:59:28
 楼主| 发表于 2010-11-16 10:37 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
11
2010新春纪念徽章
日期:2010-03-01 11:08:27SQL大赛参与纪念
日期:2011-04-13 12:08:172010广州亚运会纪念徽章:空手道
日期:2011-03-08 15:29:592011新春纪念徽章
日期:2011-02-18 11:43:362010广州亚运会纪念徽章:台球
日期:2011-01-26 10:41:28数据库板块每日发贴之星
日期:2010-12-10 01:01:022010广州亚运会纪念徽章:网球
日期:2010-12-09 13:11:342010广州亚运会纪念徽章:篮球
日期:2010-12-06 14:28:04辩论纪念章
日期:2010-11-15 10:46:13ITPUB9周年纪念徽章
日期:2010-10-08 09:28:52
发表于 2010-11-16 10:41 | 显示全部楼层
返回25W....
你是怎么得出不要group by只需0.5秒的结论的?

使用道具 举报

回复
论坛徽章:
0
发表于 2010-11-16 12:33 | 显示全部楼层

回复 #1 lichangzai 的帖子

使用道具 举报

回复
论坛徽章:
18
CTO参与奖
日期:2009-01-15 11:42:46奥运纪念徽章
日期:2012-11-28 09:33:31奥运纪念徽章
日期:2012-11-22 14:39:55灰彻蛋
日期:2012-05-15 14:37:582011新春纪念徽章
日期:2011-02-18 11:43:32生肖徽章2007版:兔
日期:2011-01-20 12:58:492011新春纪念徽章
日期:2011-01-04 10:35:48数据库板块每日发贴之星
日期:2010-12-09 01:01:012010广州亚运会纪念徽章:帆船
日期:2010-12-06 11:01:472010广州亚运会纪念徽章:曲棍球
日期:2010-12-06 10:59:28
 楼主| 发表于 2010-11-16 13:00 | 显示全部楼层
原帖由 6666444 于 2010-11-16 10:41 发表
返回25W....
你是怎么得出不要group by只需0.5秒的结论的?

试的呀

使用道具 举报

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


LZ,  你目前的数据库是9i的吧, 你把该语句的“Predicate Information”, 贴一下!

使用道具 举报

回复
认证徽章
论坛徽章:
3
ITPUB9周年纪念徽章
日期:2010-10-08 09:31:22铁扇公主
日期:2012-02-21 15:02:40ITPUB年度最佳BLOG写作奖
日期:2012-03-13 17:09:53
发表于 2010-11-16 13:12 | 显示全部楼层
建议你用autotrace看一下sort(memory)和sort(disk)的比例情况,如果sort(disk)比较大,可以考虑增加PGA的空间看看。因为毕竟排序之类的在里面的sortarea实现。

使用道具 举报

回复

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

本版积分规则 发表回复

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