oracle驱动表语句,SQL优化---高效选择驱动表

本帖最后由 sunyunyi 于 2018-11-10 10:48 编辑

作者简介：

-------------------------------------------------------------------------------

@ 孙显鹏，海天起点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 (10.05 KB, 下载次数: 170)

2018-2-2 13:01 上传

分区策略：

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 (8.71 KB, 下载次数: 168)

2018-2-2 13:02 上传

远程数据库执行的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"

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"

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"

添加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"

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"

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

孙显鹏