0

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?

  • Are those remote tables Oracle or other DBMS? Have you analyzed them lately? – mustaccio May 3 '18 at 17:49
  • Yes, they are Oracle. They all were analyzed an hour ago – minusnine May 3 '18 at 18:13
0

In short, you cannot analyze a remote table. You must explicitly gather statistics using DBMS_STATS package.

The schema object to be analyzed must be local, and it must be in your own schema or you must have the ANALYZE ANY system privilege.

Here is a link to the relevant Oracle doc:

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4005.htm#SQLRF01105

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.