加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle insert 导致hint DRIVING_SITE失效解决方案

发布时间：2020-12-12 16:05:03 所属栏目：百科 来源：网络整理
导读：业务是将远程的表的数据同步到本地。 数据库是Oracle 11.2.0.4.0 INSERT INTO GG_ISSUE (ISSUE_ID, CONTRACT_ID, ISSUE_DATE, PROJECT_ID) SELECT /*+DRIVING_SITE(ISSUE)*/ /*+DRIVING_SITE(PRO)*/ '09SYN'||ISSUE.ISSUE_ID, ISSUE.CONTRACT_ID, ISSUE.ISSU
业务是将远程的表的数据同步到本地。
数据库是Oracle 11.2.0.4.0
INSERT INTO GG_ISSUE
(ISSUE_ID,
CONTRACT_ID,
ISSUE_DATE,
PROJECT_ID)
SELECT /*+DRIVING_SITE(ISSUE)*/ /*+DRIVING_SITE(PRO)*/ '09SYN'||ISSUE.ISSUE_ID,
ISSUE.CONTRACT_ID,
ISSUE.ISSUE_DATE,
ISSUE.PROJECT_ID
FROM GG_ISSUE@dblinkname
ISSUE,GG_PROJECT@dblinkname
PRO WHERE ISSUE.PROJECT_ID = PRO.PROJECT_ID
AND ISSUE.ISSUE_TYPE <> 3
AND ISSUE.ISSUE_TYPE > 0
AND ISSUE.ISSUE_ID NOT IN (SELECT SUBSTR(MI.ISSUE_ID,6,LENGTH(ISSUE_ID)) FROM GG_ISSUE MI WHERE SUBSTR(MI.DATA_AREA,2) = '09')
AND ISSUE.ISSUE_STATUS = 'audited';
原SQL执行非常缓慢,20分钟都没有反映，发现执行计划相当糟糕：
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 67925 | 31381350 | 457737 | 01:31:33 |
| 1 | LOAD TABLE CONVENTIONAL | GG_ISSUE | | | | |
| * 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 67926 | 31381812 | 2973 | 00:00:36 |
| 4 | REMOTE | GG_ISSUE | 67926 | 29547810 | 2855 | 00:00:35 |
| 5 | REMOTE | GG_PROJECT | 1 | 27 | 0 | 00:00:01 |
| * 6 | TABLE ACCESS FULL | GG_ISSUE | 2 | 58 | 7 | 00:00:01 |
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter( NOT EXISTS (SELECT 0 FROM "GG_ISSUE" "MI" WHERE SUBSTR("MI"."DATA_AREA",2)='09' AND LNNVL(SUBSTR("MI"."ISSUE_ID",LENGTH("ISSUE_ID"))<>:B1)))
* 6 - filter(SUBSTR("MI"."DATA_AREA",LENGTH("ISSUE_ID"))<>:B1))

去掉insert后直接执行select比较快,查询所有数据2千多条需要15s，执行计划如下：
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 247088 | 120578944 | 29555 | 00:05:55 |
| * 1 | HASH JOIN RIGHT ANTI NA | | 247088 | 120578944 | 29555 | 00:05:55 |
| 2 | REMOTE | GG_ISSUE | 14672 | 572208 | 25088 | 00:05:02 |
| 3 | NESTED LOOPS | | 247089 | 110942961 | 4466 | 00:00:54 |
| 4 | PARTITION RANGE ALL | | 247938 | 107853030 | 4452 | 00:00:54 |
| * 5 | TABLE ACCESS FULL | GG_ISSUE | 247938 | 107853030 | 4452 | 00:00:54 |
| * 6 | INDEX UNIQUE SCAN | PK_PROJECT_ID_T | 1 | 14 | 0 | 00:00:01 |
---------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A2"."ISSUE_ID"=SUBSTR("A3"."ISSUE_ID",LENGTH("A3"."ISSUE_ID")))
* 5 - filter("A2"."ISSUE_TYPE"<>3 AND "A2"."ISSUE_STATUS"='audited' AND "A2"."ISSUE_TYPE">0)
* 6 - access("A2"."PROJECT_ID"="A1"."PROJECT_ID")


说明insert 导致/*+DRIVING_SITE(ISSUE)*/失效。如果让SQL走上正确的执行计划呢？有几种方案尝试一下：
1.加hint告诉CBO表上有多少数据，观察执行计划是否有变。
2.用merge into，观察执行计划是否有变。
3.将远程的关联表作为视图，然后通过dblink访问视图。

验证方案1：
SELECT /*+use_hash(PRO,ISSUE) CARDINALITY(PRO 1000000) CARDINALITY(ISSUE 2000000)*/ '09SYN'||ISSUE.ISSUE_ID,
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 3596461335 | 1661565136770 | 24935447682 | 999:59:59 |
| 1 | LOAD TABLE CONVENTIONAL | GG_ISSUE | | | | |
| * 2 | FILTER | | | | | |
| * 3 | HASH JOIN | | 3596475454 | 1661571659748 | 462539 | 01:32:31 |
| 4 | REMOTE | GG_PROJECT | 10000000 | 270000000 | 64 | 00:00:01 |
| 5 | REMOTE | GG_ISSUE | 20000000 | 8700000000 | 3578 | 00:00:43 |
| * 6 | TABLE ACCESS FULL | GG_ISSUE | 2 | 58 | 7 | 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter( NOT EXISTS (SELECT 0 FROM "GG_ISSUE" "MI" WHERE SUBSTR("MI"."DATA_AREA",LENGTH("ISSUE_ID"))<>:B1)))
* 3 - access("ISSUE"."PROJECT_ID"="PRO"."PROJECT_ID")
* 6 - filter(SUBSTR("MI"."DATA_AREA",LENGTH("ISSUE_ID"))<>:B1))

SELECT /*+use_hash(PRO,
SELECT /*+use_hash(PRO,ISSUE) CARDINALITY(MI 2000000)*/ SUBSTR(MI.ISSUE_ID,2) = '09'
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 35964613 | 16615651206 | 71116178 | 237:03:15 |
| 1 | LOAD TABLE CONVENTIONAL | GG_ISSUE | | | | |
| * 2 | FILTER | | | | | |
| * 3 | HASH JOIN | | 35964755 | 16615716810 | 47294 | 00:09:28 |
| 4 | REMOTE | GG_PROJECT | 1000000 | 27000000 | 64 | 00:00:01 |
| 5 | REMOTE | GG_ISSUE | 2000000 | 870000000 | 2925 | 00:00:36 |
| * 6 | TABLE ACCESS FULL | GG_ISSUE | 2000000 | 58000000 | 2 | 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter( NOT EXISTS (SELECT /*+ OPT_ESTIMATE (TABLE "MI" ROWS=2000000.000000 ) */ 0 FROM "GG_ISSUE" "MI" WHERE SUBSTR("MI"."DATA_AREA",2)='09' AND
LNNVL(SUBSTR("MI"."ISSUE_ID",LENGTH("ISSUE_ID"))<>:B1))
FILTER的算法类似nestloop,这样的执行计划很难执行出结果来

验证方案2：
merge into GG_ISSUE MI
using( SELECT /*+DRIVING_SITE(ISSUE)*/ /*+DRIVING_SITE(PRO)*/
ISSUE.ISSUE_ID,
ISSUE.ISSUE_DATE
FROM GG_ISSUE@dblinkname ISSUE,
GG_PROJECT@dblinkname PRO
WHERE ISSUE.PROJECT_ID = PRO.PROJECT_ID
AND ISSUE.ISSUE_TYPE <> 3
AND ISSUE.ISSUE_TYPE > 0
AND ISSUE.ISSUE_STATUS = 'audited') bb
on(SUBSTR(MI.ISSUE_ID,LENGTH(MI.ISSUE_ID))=bb.ISSUE_ID)
when not matched then
insert(ISSUE_ID,CONTRACT_ID,ISSUE_DATE) values(bb.ISSUE_ID,bb.CONTRACT_ID,bb.ISSUE_DATE)
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT,GOAL=ALL_ROWS | | 188585335 | 73193 | 00:01:28 |
| 1 | MERGE | GG_ISSUE | | | | |
| * 2 | VIEW | | | | | |
| * 3 | HASH JOIN OUTER | | 347938 | 188585335 | 73193 | 00:01:28 |
| 4 | VIEW | | | 3328374 | 2971 | 00:00:32 |
| 5 | REMOTE | | 1 | | | 00:00:00 |
| * 6 | TABLE ACCESS FULL | GG_ISSUE | 247938 | 635299745 | 39254 | 00:00:54 |
-----------------------------------------------------------------------------------------------
执行计划变得正常，实测12s能够完成。由于方案2解决了问题，方案3就不用验证了。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同