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@! 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
