I have this query:
SELECT
d.devicename,
d.template,
d.deleteflag,
SUM(isUnexpected(gv.value,sv.param1,sv.param2,sv.comparator,sv.operator) ) totalDeviation
FROM
vm_wells_situ s
INNER JOIN devices d
ON upper(s.well) = upper(d.devicename)
AND d.deleteflag IS NULL
AND d.template = 'APT'
INNER JOIN zafiro_srv_wells z
ON s.well = z.name
INNER JOIN district t
ON z.batteryDistrict = t.cod_zafiro
AND t.ug = 'GSJ'
AND t.management = 'GOP-2'
INNER JOIN equipment eq
ON z.batteryEquipment = eq.cod_zafiro
AND eq.deleted = 0
INNER JOIN station st
ON z.battery = st.cod_zafiro
AND st.deleted = 0
LEFT OUTER JOIN suggested_values sv
ON sv.tecnologia = d.template
AND sv.type = 1
LEFT OUTER JOIN general_values gv
ON sv.variable = gv.name
AND d.devicename = gv.device
WHERE
s.type = 'specialWell'
GROUP BY
d.devicename,
d.template,
d.deleteflag
Which generates the following execution plan:
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 246 | 230 (2)| 00:00:03 | | |
| 1 | WINDOW SORT | | 1 | 246 | 230 (2)| 00:00:03 | | |
| 2 | HASH GROUP BY | | 1 | 246 | 230 (2)| 00:00:03 | | |
| 3 | NESTED LOOPS OUTER | | 1 | 246 | 228 (1)| 00:00:03 | | |
| 4 | VIEW | | 1 | 205 | 226 (1)| 00:00:03 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| ZAFIRO_SRV_WELLS | 1 | 37 | 2 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 331 | 226 (1)| 00:00:03 | | |
| 7 | MERGE JOIN CARTESIAN | | 1 | 294 | 224 (1)| 00:00:03 | | |
| 8 | MERGE JOIN CARTESIAN | | 1 | 266 | 221 (1)| 00:00:03 | | |
| 9 | MERGE JOIN CARTESIAN | | 1 | 242 | 218 (1)| 00:00:03 | | |
|* 10 | HASH JOIN | | 1 | 215 | 215 (1)| 00:00:03 | | |
| 11 | NESTED LOOPS OUTER | | 1 | 172 | 196 (1)| 00:00:03 | | |
| 12 | REMOTE | DEVICES | 1 | 52 | 180 (1)| 00:00:03 | SRVESP | R->S |
| 13 | REMOTE | SUGGESTED_VALUES | 17 | 2040 | 16 (0)| 00:00:01 | SRVESP | R->S |
| 14 | REMOTE | VM_WELLS_SITU | 595 | 25585 | 18 (0)| 00:00:01 | SRVHIS | R->S |
| 15 | BUFFER SORT | | 2 | 54 | 199 (1)| 00:00:03 | | |
|* 16 | TABLE ACCESS FULL | DISTRICT | 2 | 54 | 3 (0)| 00:00:01 | | |
| 17 | BUFFER SORT | | 27 | 648 | 218 (1)| 00:00:03 | | |
|* 18 | TABLE ACCESS FULL | EQUIPMENT | 27 | 648 | 3 (0)| 00:00:01 | | |
| 19 | BUFFER SORT | | 128 | 3584 | 221 (1)| 00:00:03 | | |
|* 20 | TABLE ACCESS FULL | STATION | 128 | 3584 | 3 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | ZAFIRO_SRV_WELLS_IDX1 | 1 | | 1 (0)| 00:00:01 | | |
| 22 | REMOTE | GENERAL_VALUES | 1 | 41 | 2 (0)| 00:00:01 | SRVESP | R->S |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("Z"."BATTERYDISTRICT" IS NOT NULL AND "Z"."BATTERY" IS NOT NULL AND
"Z"."BATTERYEQUIPMENT" IS NOT NULL AND "Z"."BATTERY"="ST"."COD_ZAFIRO" AND
"Z"."BATTERYEQUIPMENT"="EQ"."COD_ZAFIRO" AND "Z"."BATTERYDISTRICT"="T"."COD_ZAFIRO")
10 - access(UPPER("S"."WELL")=UPPER("D"."DEVICENAME"))
16 - filter("T"."GERENCIA"='GOP-2' AND "T"."UG"='GSJ')
18 - filter("EQ"."DELETED"=0)
20 - filter("ST"."DELETED"=0)
21 - access("S"."WELL"="Z"."NAME")
Remote SQL Information (identified by operation id):
----------------------------------------------------
12 - SELECT "DEVICENAME","TEMPLATE","DELETEFLAG" FROM "DEVICES" "D" WHERE "DELETEFLAG" IS NULL AND
"TEMPLATE"='APT' (accessing 'SRVESP.NAPENDP.COM' )
13 - SELECT "TECHNOLOGY","VARIABLE","TYPE","PARAM1","OPERATOR","PARAM2","COMPARATOR" FROM
"CONFIG"."SUGGESTED_VALUES" "SV" WHERE "TECHNOLOGY"='APT' AND TO_NUMBER("TYPE")=1 (accessing
'SRVESP.NAPENDP.COM' )
14 - SELECT "WELL","TYPE" FROM "VM_WELLS_SITU" "S" WHERE "TYPE"='SPECIALWELL' (accessing
'SRVHIS.NAPENDP.COM' )
22 - SELECT "DEVICE","NAME","VALUE" FROM "ESP"."GENERAL_VALUES" "GV" WHERE :1="DEVICE" AND :2="NAME" (accessing
'SRVESP.NAPENDP.COM' )
Problem is the result set actually brings ~75k rows, not one.
Cardinalities should be ~
ZAFIRO_SRV_WELLS: 3K
DEVICES: 300
GENERAL_VALUES: 450K
Those 3 are public synonyms, the last two are pointing at remote tables.
Since the optimizer expects them to have only one row, it uses cartesian joins.
As expected, running a cartesian join against a table which in reality has got half a million rows takes forever to complete, so my query becomes useless.
The problem does NOT replicate in a development environment where all tables belong to the same schema and no synonyms are used.
How can I fix this?