5 Replies Latest reply on May 3, 2018 9:30 PM by Mike Kutz

    Oracle 10g optimizer wrongly estimates cardinality as 1 for public synonyms

    minusnine

      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?