Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Norway
    Posts
    3

    Question Unanswered: Strange SQL behaviour

    I'm using explain plan on Oracle 9.2.0.4.0. My query consists of 14 tables with rows between 100 to 2.000.000 records.
    My first explain plan gave a cost of 1.352. I, by misstake, added a condition wich I already had written, and the cost suddenly became 635!
    I thougt this was strange so I dublicated the line again, and the cost became 634!

    I illustrate with this :
    select .........
    from a , b , c
    where a.no=b.no
    and b.ro=c.ro -- original cost 1.352
    --------------------------------------
    and a.no=b.no -- my first mistake, cost now 635
    and a.no=b.no -- duplicated the line again, cost now 634
    --------------------------------------

    I tried then to add all my conditions twice in the same query, and the cost became 192!!!
    I illustrate again with this:
    select .........
    from a , b , c
    where a.no=b.no
    and b.ro=c.ro -- original cost 1.352
    -----------------------------
    and a.no=b.no
    and b.ro=c.ro -- new cost 192

    Executing these different queries gives me the same result, but the original uses 55 sec, the one with cost 635 uses 15 sec, and the last one uses 2 sec. No matter how many times I repeat them.

    I didn't think the execution plan should change by dublicating conditions, but it does! Have someone else experienced this?

    Please help!

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Unfortunately, I cannot explain why your execution plan does this or that, but I've got a really fun article for you to read. It will take you probable about half an hour to through it, but it is worth reading. The point is that you cannot compare COST values between queries (not even 2 identical ones)...

    Goto asktom.oracle.com and search for "consider cost or time - trying to compare the COST of two queries".

    I found it interesting, hope you do.

    ps. I did not post the URL directly, as it seems I'm too stupid to do so. I will work on it, promise...
    Last edited by cvandemaele; 11-14-03 at 08:22.

  3. #3
    Join Date
    Oct 2003
    Posts
    87

    Re: Strange SQL behaviour

    Be sure to send this to AskTom, this could, I repeat, could be a bug in the Optimizer as the redundant predicate shouldn't have influenced the cost.

    Originally posted by Sindre.g
    I'm using explain plan on Oracle 9.2.0.4.0. My query consists of 14 tables with rows between 100 to 2.000.000 records.
    My first explain plan gave a cost of 1.352. I, by misstake, added a condition wich I already had written, and the cost suddenly became 635!
    I thougt this was strange so I dublicated the line again, and the cost became 634!

    I illustrate with this :
    select .........
    from a , b , c
    where a.no=b.no
    and b.ro=c.ro -- original cost 1.352
    --------------------------------------
    and a.no=b.no -- my first mistake, cost now 635
    and a.no=b.no -- duplicated the line again, cost now 634
    --------------------------------------

    I tried then to add all my conditions twice in the same query, and the cost became 192!!!
    I illustrate again with this:
    select .........
    from a , b , c
    where a.no=b.no
    and b.ro=c.ro -- original cost 1.352
    -----------------------------
    and a.no=b.no
    and b.ro=c.ro -- new cost 192

    Executing these different queries gives me the same result, but the original uses 55 sec, the one with cost 635 uses 15 sec, and the last one uses 2 sec. No matter how many times I repeat them.

    I didn't think the execution plan should change by dublicating conditions, but it does! Have someone else experienced this?

    Please help!
    Oracle - DB2 - MS Access -

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140

    Re: Strange SQL behaviour

    Originally posted by N-ary
    Be sure to send this to AskTom, this could, I repeat, could be a bug in the Optimizer as the redundant predicate shouldn't have influenced the cost.
    That is exactly the point of Thomas Kyte (AskTom), you should not compare the COST of two queries. So saying that a redundant predicate shouldn't have influenced the cost is incorrect, as you cannot compare them.

    At least that is what he is saying...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Bah!

    - Run a trace on all of the queries.
    - goto your udump directory
    - tkprof the trace file using explain=username/password
    - look at the results in the .prf file

    I would then be interested in seeing your trace/tkprof output at that point.

    Using explain-plan on what SHOULD happen (aka not using trace) is almost meaningless. TKPROF a trace will actually show you the REAL path the query took in the db instead of a hypothetical you are getting now.

    I have found countless queries where Explain plan says it is using an index, then I TKPROF it and it shows it is actually NOT using an index. Very frustrating, but it tells you a great deal.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Oct 2003
    Posts
    87

    Re: Strange SQL behaviour

    Originally posted by cvandemaele
    That is exactly the point of Thomas Kyte (AskTom), you should not compare the COST of two queries. So saying that a redundant predicate shouldn't have influenced the cost is incorrect, as you cannot compare them.

    At least that is what he is saying...
    Maybe I didn't make my point very clear -- I'll try again, and I'll send this over to AskTom myself, its peeked my interest. Assuming statis, if you explained the query WITHOUT the redundent predicate several times each would probably yield a different cost number. Same for the query WITH the redundent predicate. But those numbers won't vary radically. My concern is that the redundent predicate does produce a radically different cost number. In my opinion, the redundent predicate shouldn't be considered at all by the Optimizer.
    Oracle - DB2 - MS Access -

  7. #7
    Join Date
    Nov 2003
    Location
    Norway
    Posts
    3

    Wink

    A friend of mine posted my problem to Metalink.oracle.com
    After sending explainplans to them, they sent this answer:

    "it looks well that you are meeting the Bug:2548714 OPTIMIZER DOES NOT ELIMINATE DUPLICATE PREDICATES. This problem was open in 10i version, but should be present on your version too. It is fixed in 10.1.0.1 version. This problem is present only when the predicates are in a AND chain. It fact, the duplicates reduce artificially the resulting cardinality which could lead to wrong results. So your behaviour isn't normal."

    Oracle will backport this to 9i r2.

  8. #8
    Join Date
    Oct 2003
    Posts
    87

    Wink

    Originally posted by Sindre.g
    A friend of mine posted my problem to Metalink.oracle.com
    After sending explainplans to them, they sent this answer:

    "it looks well that you are meeting the Bug:2548714 OPTIMIZER DOES NOT ELIMINATE DUPLICATE PREDICATES. This problem was open in 10i version, but should be present on your version too. It is fixed in 10.1.0.1 version. This problem is present only when the predicates are in a AND chain. It fact, the duplicates reduce artificially the resulting cardinality which could lead to wrong results. So your behaviour isn't normal."

    Oracle will backport this to 9i r2.
    Thanks!
    Oracle - DB2 - MS Access -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •