plan shows insert into remote table using all remote selects
Date: Thu, 30 May 2019 15:24:54 -0400
Message-Id: <5CF02E060200000B00059E0C_at_groupwise2014.gcrta.org>
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 | | |18 - access("A6"."PAYRULEID"="A5"."PAYRULEID"(+)) 20 - access("A7"."PAYRULEID"="A6"."PAYRULEID")
| 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")
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_at_! 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 Thu May 30 2019 - 21:24:54 CEST