Re: plan shows insert into remote table using all remote selects
Date: Sun, 2 Jun 2019 11:15:00 +0000
Message-ID: <LO2P265MB04158A36DE9536464E279409A51B0_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
You can't.
The query that supplies data to the insert has to be executed by the database where the DML is taking place - which means the remote (to you) database. The plan you see is the plan from the perspective of the remote (to you) database, which is why it reports the table which you think of as local as being remote (to the executing session).
If you want a plan which does ALL the work at the local (to you) database you need to create a local (to you) view for the query (possibly with a no_merge hint) and the do "insert into remote_table select * from local_view". You might, however, manage to get away with creating a set of hints on the query that force a nested loop join all the way through every table, then the plan would be (just like operation 22 in the current plan) just "REMOTE", with the join query being reported as the remote SQL.
Alternatively you could create a pipelined function that returns the data you want and "insert ... select from pipelined function" https://jonathanlewis.wordpress.com/2010/10/07/distributed-pipelines/
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> Sent: 30 May 2019 20:24
To: oracle-l-freelist
Subject: plan shows insert into remote table using all remote selects
SQL and ALL tables are on the local database. Insert is going to a remote database. All tables are listed as remote in the below plan even though on the local database. How can we force all the local tables to actually be local.
PLAN_TABLE_OUTPUT
Plan hash value: 3968295312
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
| 0 | INSERT STATEMENT REMOTE | | 9 | 8901 | | 73074 (2)| 00:17:16 | | |
| 1 | LOAD TABLE CONVENTIONAL | PAY_TYPE_HRS_EMP | | | | | | OR14 | |
|* 2 | COUNT STOPKEY | | | | | | | | | |* 3 | FILTER | | | | | | | | | |* 4 | HASH JOIN | | 571K| 538M| | 73074 (2)| 00:17:16 | | |
| 5 | REMOTE | COMBHOMEACCT | 32797 | 992K| | 45 (3)| 00:00:01 | ! | R->S |
|* 6 | HASH JOIN | | 116K| 105M| | 73026 (2)| 00:17:15 | | |
| 7 | REMOTE | BASEWAGERTHIST | 21288 | 644K| | 45 (3)| 00:00:01 | ! | R->S |
|* 8 | HASH JOIN | | 36257 | 32M| | 72981 (2)| 00:17:15 | | |
| 9 | REMOTE | PERSONSTATUSMM | 14743 | 633K| | 45 (3)| 00:00:01 | ! | R->S |
|* 10 | HASH JOIN | | 16393 | 13M| | 72936 (2)| 00:17:14 | | |
| 11 | REMOTE | PAYCODE | 135 | 10530 | | 3 (0)| 00:00:01 | ! | R->S |
|* 12 | HASH JOIN | | 16393 | 12M| 4592K| 72933 (2)| 00:17:14 | | |
| 13 | REMOTE | LABORACCT | 11925 | 4448K| | 89 (0)| 00:00:02 | ! | R->S |
|* 14 | HASH JOIN | | 16393 | 6771K| | 72617 (2)| 00:17:10 | | | |* 15 | HASH JOIN OUTER | | 1423 | 466K| | 120 (1)| 00:00:02 | | | |* 16 | HASH JOIN OUTER | | 763 | 227K| | 100 (1)| 00:00:02 | | | |* 17 | HASH JOIN | | 763 | 204K| | 90 (2)| 00:00:02 | | | |* 18 | HASH JOIN RIGHT OUTER| | 763 | 132K| | 46 (3)| 00:00:01 | | |
| 19 | REMOTE | PAYRULE | 1 | 31 | | 3 (0)| 00:00:01 | ! | R->S |
|* 20 | HASH JOIN | | 763 | 109K| | 42 (0)| 00:00:01 | | |
| 21 | REMOTE | PAYRULEIDS | 14 | 364 | | 3 (0)| 00:00:01 | ! | R->S |
| 22 | REMOTE | | 1 | 40 | | 5 (0)| 00:00:01 | ! | R->S |
| 23 | REMOTE | PERSON | 6666 | 624K| | 44 (0)| 00:00:01 | ! | R->S |
| 24 | REMOTE | ASSIGNFTE | 6664 | 201K| | 10 (0)| 00:00:01 | ! | R->S |
| 25 | REMOTE | BADGEASSIGN | 12424 | 376K| | 20 (0)| 00:00:01 | ! | R->S |
| 26 | REMOTE | WFCTOTAL | 30M| 2549M| | 72379 (2)| 00:17:06 | ! | R->S |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter(ROWNUM<10)
3 - filter(TO_DATE('4-MAY-19')>=TO_DATE('12-JAN-19'))
4 - access("A13"."EMPLOYEEID"="A8"."EMPLOYEEID")
6 - access("A13"."EMPLOYEEID"="A12"."EMPLOYEEID")
8 - access("A14"."PERSONID"="A11"."PERSONID")
10 - access("A16"."PAYCODEID"="A15"."PAYCODEID")
12 - access("A16"."LABORACCTID"="A4"."LABORACCTID")
14 - access("A16"."EMPLOYEEID"="A13"."EMPLOYEEID")
filter(TRUNC(INTERNAL_FUNCTION("A16"."STARTDTM"))>=TRUNC(INTERNAL_FUNCTION("A2"."START_DT")) AND
TRUNC(INTERNAL_FUNCTION("A16"."STARTDTM"))<=TRUNC(INTERNAL_FUNCTION("A2"."END_DT")))
15 - access("A14"."PERSONID"="A9"."PERSONID"(+))
16 - access("A14"."PERSONID"="A10"."PERSONID"(+))
17 - access("A14"."PERSONID"="A13"."EMPLOYEEID")
18 - access("A6"."PAYRULEID"="A5"."PAYRULEID"(+))
20 - access("A7"."PAYRULEID"="A6"."PAYRULEID")
Remote SQL Information (identified by operation id):
5 - SELECT "EMPLOYEEID","EFFECTIVEDTM","EXPIRATIONDTM" FROM "TKCSOWNER"."COMBHOMEACCT" "A8" WHERE "EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
7 - SELECT "EMPLOYEEID","EFFECTIVEDTM","EXPIRATIONDTM" FROM "TKCSOWNER"."BASEWAGERTHIST" "A12" WHERE "EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
9 - SELECT "PERSONID","EMPLOYMENTSTATID","EFFECTIVEDTM","EXPIRATIONDTM" FROM "TKCSOWNER"."PERSONSTATUSMM" "A11" PLAN_TABLE_OUTPUT
WHERE "EMPLOYMENTSTATID">0 AND "EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
11 - SELECT "PAYCODEID","NAME","IS_MONEYCAT" FROM "TKCSOWNER"."PAYCODE" "A15" WHERE "IS_MONEYCAT"=0 (accessing '!' )
13 - SELECT "LABORACCTID","LABORLEV1NM","LABORLEV2NM","LABORLEV1DSC","LABORLEV2DSC" FROM "TKCSOWNER"."LABORACCT" "A4" (accessing '!' )
19 - SELECT "PAYRULEID","EFFECTIVEDTM","EXPIRATIONDTM" FROM "TKCSOWNER"."PAYRULE" "A5" WHERE :1<=NVL("EXPIRATIONDTM",:2+1) AND :3>=NVL("EFFECTIVEDTM",:4-1) (accessing '!' )
21 - SELECT "PAYRULEID","NAME" FROM "TKCSOWNER"."PAYRULEIDS" "A6" (accessing '!' )
22 - SELECT "A1"."PAYRULEID","A1"."START_DT","A1"."END_DT","A1"."END_DATE","A2"."EMPLOYEEID","A2"."PAYRULEID","A3"."
EMPLOYEEID","A4"."EMPLOYEEID","A4"."PAYRULEID","A4"."EFFECTIVEDTM","A4"."EXPIRATIONDTM" FROM "TKCSOWNER"."PAYPERIOD"
"A1","TKCSOWNER"."ASSIGNPAYRULE" "A2","TKCSOWNER"."WTKEMPLOYEE" "A3","TKCSOWNER"."ASSIGNPAYRULE" "A4" WHERE
"A4"."EXPIRATIONDTM">=SYSDATE_at_! AND "A4"."EFFECTIVEDTM"<=SYSDATE@! AND "A3"."EMPLOYEEID"="A4"."EMPLOYEEID" AND
"A3"."EMPLOYEEID"="A2"."EMPLOYEEID" AND "A2"."PAYRULEID"="A1"."PAYRULEID" AND TRUNC("A1"."END_DATE")>='12-JAN-19' AND
TRUNC("A1"."END_DATE")<='4-MAY-19' (accessing '!' )
23 - SELECT "PERSONID","PERSONNUM","FULLNM" FROM "TKCSOWNER"."PERSON" "A14" (accessing '!' )
24 - SELECT "PERSONID","EFFECTIVEDTM","EXPIRATIONDTM" FROM "TKCSOWNER"."ASSIGNFTE" "A10" WHERE "EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
25 - SELECT "PERSONID","EFFECTIVEDTM","EXPIRATIONDTM" FROM "TKCSOWNER"."BADGEASSIGN" "A9" WHERE "EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
26 - SELECT "WFCTOTALID","TIMESHEETITEMID","EMPLOYEEID","DURATIONSECSQTY","WAGEAMT","MONEYAMT","APPLYDTM","LABORACCT
ID","PAYCODEID","STARTDTM","STIMEZONEID","ADJSTARTDTM","ADJAPPLYDTM","ENDDTM","UPDATEDTM","TOTALEDVERSION","ACCTAPPROV
ALNUM" FROM "TKCSOWNER"."WFCTOTAL" "A16" (accessing '!' )
Note
- fully remote statement
98 rows selected.
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jun 02 2019 - 13:15:00 CEST