Forum Stats

  • 3,728,034 Users
  • 2,245,522 Discussions
  • 7,853,255 Comments

Discussions

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

minusnine
minusnine Member Posts: 2

I have this query:

 SELECTd.devicename,d.template,d.deleteflag,SUM(isUnexpected(gv.value,sv.param1,sv.param2,sv.comparator,sv.operator) ) totalDeviationFROMvm_wells_situ sINNER JOIN devices d         ON upper(s.well) = upper(d.devicename)AND d.deleteflag IS NULLAND d.template = 'APT'INNER JOIN zafiro_srv_wells z         ON s.well = z.nameINNER 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.deviceWHEREs.type = 'specialWell'GROUP BYd.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?

Cherif bh

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2018

    The cardinality estimates are after some filters have been applied, they’re in bottom Of your plan. What cardinalities do you get for those queries when you run them directly on that DB?

    I think we need to simplify the problem before we immediately blame public synonyms.

  • minusnine
    minusnine Member Posts: 2
    edited May 2018

    When I run the explain plan for the exact same query in the development enviroment (contains the same data, but all tables are inside only one schema and no synonyms are used) the cardinality changes like I mentioned before:

    SELECT: FROM 1 TO 74118
    ZAFIRO_SRV_WELLS: FROM 1 TO 2789
    DEVICES: FROM 1 TO 294
    GENERAL_VALUES: FROM 1 TO 450K

    Plus all "MERGE JOIN CARTESIAN" are replaced with "HASH JOIN"

    I can post the whole execute plan if you find it necessary (I didn't because the table names are originally in spanish, I'll need to translate it again)

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2018
    851d90ee-01c0-4d76-9da3-5d46e0ed2c90 wrote:When I run the explain plan for the exact same query in the development enviroment (contains the same data, but all tables are inside only one schema and no synonyms are used) the cardinality changes like I mentioned before:SELECT: FROM 1 TO 74118
    ZAFIRO_SRV_WELLS: FROM 1 TO 2789
    DEVICES: FROM 1 TO 294
    GENERAL_VALUES: FROM 1 TO 450KPlus all "MERGE JOIN CARTESIAN" are replaced with "HASH JOIN"I can post the whole execute plan if you find it necessary (I didn't because the table names are originally in spanish, I'll need to translate it again)

    Not the development environment. The DB that your synonyms are pointing to. No need to do all the remote queries, you should be able to reproduce the cardinality of any individual remote queries by running the remote statement directly on that DB.

    Your statistics are very much likely to be different between your dev and whatever instance this is pointing to.

    I believe there is a restriction with histograms not being able to be applied against remote tables when calculating cardinality, so you might be suffering from that. Ive not replicated this, just saw it mentioned as a side comment here, so it might not be true. A single explain plan of one of the remote queries should tell us one way or the other though.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited May 2018

    The problem is probably not about public synonyms, and all about limitations the optimizer has with distributed queries.

    Two areas to examine when the query goes distributed:

    a) histogram information on remote tables is not available to the optimizer

    b) function-based indexes at the remote site are not considered by the optimizer for distributed queries

    Looking at your query, either of this restrictions could be relevant.

    Regards

    Jonathan Lewis

    P.S.  As a quick and dirty test, you could add the hint /*+ driving_site (s) */ to your query ("s" being the alias of a remote table in the first query block of your query).

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown
    edited May 2018

    Welcome to the forms.

    When you can, please change your nick name.

    Video tutorial how to change nickname available

    You'll probably get more answers over here for this question.

    https://dba.stackexchange.com/questions/205723/oracle-optimizer-wrongly-estimates-cardinality-as-1-for-public-synonyms

    MK

This discussion has been closed.