ITPUB论坛-中国最专业的IT技术社区

 手机号登录  找回密码
 注册
查看: 3131|回复: 3

[原创] ORACLE SQL 优化案例分享--如何选择驱动表

[复制链接]
论坛徽章:
11
目光如炬
日期:2018-01-14 22:00:00火眼金睛
日期:2018-02-28 22:00:00目光如炬
日期:2017-12-17 22:00:00目光如炬
日期:2017-11-26 22:00:00火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07目光如炬
日期:2017-08-27 22:00:01目光如炬
日期:2017-08-20 22:00:00娜美
日期:2017-06-26 15:18:15
发表于 2018-2-2 12:56 | 显示全部楼层 |阅读模式
本帖最后由 sunyunyi 于 2018-2-2 17:02 编辑

作者简介:
-------------------------------------------------------------------------------
@ 孙显鹏,海天起点oracle技术专家,十年从业经验,
@ 拥有OCP 11G认证,精通oracle内部原理,擅长调优
@ 和解决疑难问题,致力于帮助客户解决生产过程中出
@ 现的问题,提高生产效率。
@ 爱好书法,周易,中医!
@ 微信号:sunyunyi_sun
@ 电  话--18629679269
--------------------------------------------------------------------------------
现象:

现象:
SQL:如下,执行不出来,SQL通过DBLINK远程抽取源端数据库数据insert到本地表。
INSERT INTO TMP_AR_SPECIAL_CUST
  (DEPT_ID, CUST_TYPE, OBJ_ID)
SELECT /*+parallel(B,40)*/ DISTINCT '1','1',B.ACCT_NO
FROM A_RCVED_FSUN@DB_EPM C,A_RCVBL_FSUN@DB_EPM B
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201801'

在远程数据库执行SQL,A_RCVBL_FSUN,A_RCVED_FSUN都属于USERA用户下的表,都为分区表,统计信息为2015年,但是能反应数据趋势。
SELECT DISTINCT '1','1',B.ACCT_NO
FROM  USERA.A_RCVBL_FSUN B,USERA.A_RCVED_FSUN C
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201710'

001.png

分区策略:
PARTITION BY RANGE ("ORG_NO")
SUBPARTITION BY LIST ("RCVED_YM")

select count(*)
from dba_tab_partitions s
where s.table_name='A_RCVED_FSUN' and s.table_owner='USERA'
/
--19
也就是该表存在19个分区

那么C.RCVED_YM = '201801'条件就需要扫描19的分区下面的子分区201801的数据,并且RCVBL_AMT_ID列基本上相当于表的主键,

而B表和C表数据行数相当,且USERA.A_RCVBL_FSUN 表也需要访问19个分区,但是执行计划通过INDEX UNIQUE SCAN 扫描,
也就是需要扫描5145263次,看看5百万次嵌套需要多久在这个配置上?

SELECT count(B.ACCT_NO)
FROM  USERA.A_RCVBL_FSUN B,USERA.A_RCVED_FSUN C
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      C.RCVED_YM = '201710'

COUNT(B.ACCT_NO)
----------------
         5394281
Elapsed: 00:01:44.90

两个5百万的集合嵌套需要2分钟

SELECT DISTINCT '1','1',B.ACCT_NO
FROM  USERA.A_RCVBL_FSUN B,USERA.A_RCVED_FSUN C
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      C.RCVED_YM = '201710'
Elapsed: 00:01:39.33


SELECT DISTINCT '1','1',B.ACCT_NO
FROM  USERA.A_RCVBL_FSUN B,USERA.A_RCVED_FSUN C
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201801'

Elapsed: 00:02:21.19

--这个时间是可以接受的呀,怎么DBLINK就查不出来呢?

问题出在dblink上?

先dump出一直等到的进程:

Dumping Session Wait History
     for 'SQL*Net message to dblink' count=1 wait_time=0.000001 sec
                driver id=0, #bytes=1, =0
     for 'enq: HW - contention' count=1 wait_time=0.000273 sec
                name|mode=48570006, table space #=a, block=a94191
     for 'SQL*Net message from dblink' count=1 wait_time=0.010512 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message to dblink' count=1 wait_time=0.000003 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=1.896460 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message to dblink' count=1 wait_time=0.000005 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=0.000478 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message to dblink' count=1 wait_time=0.000001 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=0.000501 sec
                driver id=0, #bytes=1, =0
     for 'SQL*Net message to dblink' count=1 wait_time=0.000001 sec
                driver id=0, #bytes=1, =0
    Sampled Session History of session 1042 serial 1168
    ---------------------------------------------------
    The sampled session history is constructed by sampling
    the target session every 1 second. The sampling process
    captures at each sample if the session is in a non-idle wait,
    an idle wait, or not in a wait. If the session is in a
    non-idle wait then one interval is shown for all the samples
    the session was in the same non-idle wait. If the
    session is in an idle wait or not in a wait for
    consecutive samples then one interval is shown for all
    the consecutive samples. Though we display these consecutive
    samples  in a single interval the session may NOT be continuously
    idle or not in a wait (the sampling process does not know).

    The history is displayed in reverse chronological order.

    sample interval: 1 sec, max history 120 sec
    ---------------------------------------------------
      [121 samples,                                        08:39:04 - 08:41:04]
        waited for 'SQL*Net message from dblink', seq_num: 62864
          p1: 'driver id'=0x0
          p2: '#bytes'=0x1
          p3: ''=0x0
          time_waited: >= 120 sec (still in wait)

都在等待从源端传数据。

手动执行SQL,看看源端数据库的执行计划:
INSERT INTO TMP_AR_SPECIAL_CUST
  (DEPT_ID, CUST_TYPE, OBJ_ID)
SELECT /*+full(C) full(B)*/ DISTINCT '1','1',B.ACCT_NO
FROM A_RCVED_FSUN@DB_EPM C,A_RCVBL_FSUN@DB_EPM B
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201801'

远程库SQL:
SELECT  DISTINCT '1','1',"A1"."ACCT_NO" FROM "A_RCVED_FSUN"
"A2","A_RCVBL_FSUN" "A1" WHERE "A1"."RCVBL_AMT_ID"="A2"."RCVBL_AMT_ID" AND
("A1"."SETTLE_FLAG"='03' OR "A1"."SETTLE_FLAG"='04') AND "A1"."AMT_TYPE"='0204' AND
"A2"."RCVED_YM"='201801'
Plan hash value: 3464592102
002.png

远程数据库执行的SQL不一样?,用户为USERB_VIEW,奇怪查询A_RCVBL_FSUN两次,别名也变了A1,A2,什么鬼?
谓词添加一大堆?查询的不是USERA用户对象,是视图?检查视图:

select owner,object_type from dba_objects where object_name='A_RCVED_FSUN'

好吧 USERB_VIEW  VIEW --这个用户是视图,看看视图定义!

第一个视图:

DBMS_METADATA.GET_DDL('VIEW','A_RCVED_FSUN','USERB_VIEW')
----------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "USERB_VIEW"."A_RCVED_FSUN"
......略,一堆case条件

FROM USERA.A_RCVBL_FSUN T

另外一个视图:

  CREATE OR REPLACE FORCE VIEW "USERB_VIEW"."A_RCVBL_FSUN"
  ......略,一堆case条件
  FROM USERA.A_RCVBL_FSUN m where rcvbl_ym <> 'AAAAAA'

从上面定义看出这两个视图都是查询A_RCVBL_FSUN表,难怪远程执行计划查询A_RCVBL_FSUN两次,太粗心,
用户都搞错了,首先应该先弄清楚dblink查询的是哪个用户的对象才对。

分析为什么该SQL就跑不出来呢, 看看谓词信息:

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

   4 - filter(("AMT_TYPE"='0204' AND (CASE  WHEN "M"."RCVBL_YM"<'201001' THEN
              DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") WHEN ("M"."RCVBL_YM">='201003' AND
              "M"."RCVBL_AMT"-"M"."RCVED_AMT">5000) THEN DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") ELSE
              "SETTLE_FLAG" END ='03' OR CASE  WHEN "M"."RCVBL_YM"<'201001' THEN
              DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") WHEN ("M"."RCVBL_YM">='201003' AND
              "M"."RCVBL_AMT"-"M"."RCVED_AMT">5000) THEN DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") ELSE
              "SETTLE_FLAG" END ='04') AND "RCVBL_YM"<>'AAAAAA'))
   5 - filter(("T"."RCVBL_YM"='201801' AND "T"."RCVBL_YM"<>'AAAAAA'))
   6 - access("RCVBL_AMT_ID"="T"."RCVBL_AMT_ID")

从上面信息不难看出,应该首先执行5 - filter(("T"."RCVBL_YM"='201801' AND "T"."RCVBL_YM"<>'AAAAAA')) 过滤数据,
然后过滤第四步一堆DECODE才对。 那么为什么首先过滤5会提供效率呢?因为5可以大量过滤数据,4这些条件是在select部分不在where条件,并且4的过滤条件数据量很大,比如"RCVBL_YM"<'201001'数据量就非常大,所以先过滤5会提供效率。

添加hint让第五步先过滤:

这个是在远程库执行:
SELECT /*+leading(C)*/DISTINCT '1','1',B.ACCT_NO
FROM  USERB_VIEW.A_RCVBL_FSUN B,USERB_VIEW.A_RCVED_FSUN C
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201801'

130 rows selected.

Elapsed: 00:00:57.37

我们在本地实际执行看看,因为远程库修改了表名需要/*+leading(A2)*/而不是/*+leading(C)*/这个一定要注意

INSERT INTO TMP_AR_SPECIAL_CUST
  (DEPT_ID, CUST_TYPE, OBJ_ID)
SELECT  /*+leading(A2)*/ DISTINCT '1','1',B.ACCT_NO
FROM A_RCVED_FSUN@DB_EPM C,A_RCVBL_FSUN@DB_EPM B
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201801'

Elapsed: 00:00:59.21 --效果很好

还是强制点好:

INSERT INTO TMP_AR_SPECIAL_CUST
  (DEPT_ID, CUST_TYPE, OBJ_ID)
SELECT  /*+ordered*/ DISTINCT '1','1',B.ACCT_NO
FROM A_RCVED_FSUN@DB_EPM C,A_RCVBL_FSUN@DB_EPM B
WHERE B.RCVBL_AMT_ID = C.RCVBL_AMT_ID AND
      B.SETTLE_FLAG IN('03','04') AND
      B.AMT_TYPE = '0204' AND C.RCVED_YM = '201801'

Elapsed: 00:00:50.13

添加提示后的谓词信息:
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(("T"."RCVBL_YM"='201801' AND "T"."RCVBL_YM"<>'AAAAAA'))
   6 - filter(("AMT_TYPE"='0204' AND (CASE  WHEN "M"."RCVBL_YM"<'201001' THEN
              DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") WHEN ("M"."RCVBL_YM">='201003' AND
              "M"."RCVBL_AMT"-"M"."RCVED_AMT">5000) THEN DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") ELSE
              "SETTLE_FLAG" END ='03' OR CASE  WHEN "M"."RCVBL_YM"<'201001' THEN
              DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") WHEN ("M"."RCVBL_YM">='201003' AND
              "M"."RCVBL_AMT"-"M"."RCVED_AMT">5000) THEN DECODE("SETTLE_FLAG",'01','03','02','03',"SETTLE_FLAG") ELSE
              "SETTLE_FLAG" END ='04') AND "RCVBL_YM"<>'AAAAAA'))
   7 - access("RCVBL_AMT_ID"="T"."RCVBL_AMT_ID")

总执行时间50S左右,问题到此解决,这个SQL的优化还是驱动表的选择不合理导致。

2018-02-01

孙显鹏






发表于 2018-2-7 09:24 | 显示全部楼层
我刚毕业 数据与传统文化都在学习阶段 哈哈 易与医不分家 !

使用道具 举报

回复
论坛徽章:
11
目光如炬
日期:2018-01-14 22:00:00火眼金睛
日期:2018-02-28 22:00:00目光如炬
日期:2017-12-17 22:00:00目光如炬
日期:2017-11-26 22:00:00火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07目光如炬
日期:2017-08-27 22:00:01目光如炬
日期:2017-08-20 22:00:00娜美
日期:2017-06-26 15:18:15
 楼主| 发表于 2018-2-7 09:31 | 显示全部楼层
支离破碎的美梦 发表于 2018-2-7 09:24
我刚毕业 数据与传统文化都在学习阶段 哈哈 易与医不分家 !

给数据库诊断就如同给人看病,世界上的道理是相同的!

使用道具 举报

回复
发表于 2018-2-7 09:33 | 显示全部楼层
sunyunyi 发表于 2018-2-7 09:31
给数据库诊断就如同给人看病,世界上的道理是相同的!

是的 愿你做一个数据库中的上工

使用道具 举报

回复

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

本版积分规则

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