Very slow INTERSECT under 9i - optimizer mistake?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Very slow INTERSECT under 9i - optimizer mistake?

  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    4

    Very slow INTERSECT under 9i - optimizer mistake?

    Hello,

    I have a weird performance issue with a relatively
    simple query with INTERSECT and need advice.
    The query in question looks like this

    (subquery A)
    intersect
    (subquery B)

    independently subquery A runs to completion in 2 seconds and returns 487 rows
    independently subquery B runs to completion in 6 seconds and returns 2440 rows

    the combined query (with INTERSECT) returns 15 matches
    (correct), but runs for a whooping 1 minute 6 seconds

    Considering the relatively small size of INTERSECT,
    I cannot understand why the performance degrades so much.

    Either subquery returns a result set of strings
    20-30 characters long. Both subqueries contain nothing
    exceptional - very conventional SQL. The size of
    intersected results is relatively small, so no memory
    issues should be responsible (I could intersect
    this results with Perl in less then a second).
    The only possible catch is that the strings
    returned generally differ only in the last few
    characters - could this affect sorting/hashing?

    For all
    means and purposes Oracle cannot run INTERSECT for
    almost 1 minute (we are talking 2 MHz/512MB machine
    running Oracle 9i)

    So I presume it must be a query optimizer fault.
    Nevertheless, playing with QUERY_REWRITE_ENABLED does not solve
    anything, likewise COST hint.

    Could anyone advice to me how to tackle this problem
    or at least explain what is happening?
    Anatoli Krassavine
    toly@intellidos.com
    Intellidos Limited

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you looked at the explain plan of ...
    * subquery A on it's own
    * subquery B on it's own
    * the two subquerys when intersected
    ... to see if there is a difference in how they are being executed when intersected?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287

    Re: Very slow INTERSECT under 9i - optimizer mistake?

    Originally posted by anatoli
    Hello,

    I have a weird performance issue with a relatively
    simple query with INTERSECT and need advice.
    The query in question looks like this

    (subquery A)
    intersect
    (subquery B)

    independently subquery A runs to completion in 2 seconds and returns 487 rows
    independently subquery B runs to completion in 6 seconds and returns 2440 rows

    the combined query (with INTERSECT) returns 15 matches
    (correct), but runs for a whooping 1 minute 6 seconds
    Both subquery independently don't use any addition sorting.
    Bot if u try to get intersection of the queries, Oracle has to execute addition step for each of them:
    SORT UNIQUE
    This is reason, why intersection more slowly, then two subqueries
    separately.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: Very slow INTERSECT under 9i - optimizer mistake?

    Originally posted by Shestakov
    Both subquery independently don't use any addition sorting.
    Bot if u try to get intersection of the queries, Oracle has to execute addition step for each of them:
    SORT UNIQUE
    This is reason, why intersection more slowly, then two subqueries
    separately.
    Given the number of rows involved, i don't believe that this explains the differencein execution time.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    2 slimdave

    This is real query (or near real, because view based on this query).
    This query use MINUS (it's like INTERSECT).
    U can see execution plan if u don't belive that this explains the difference.

    Code:
    select a.period, center, ent, 1 as source_id
    from dtax_model.TM_CC_ENT_LKUP  a
       , dtax_mlt.MLT_ENT           b 
    WHERE a.period = b.period 
      AND a.ent_id = b.ENT_ID 
      AND a.active ='Y' 
    union all
    ( 
            select a.period, center, ent, 2
            from dtax_mlt.MLT_CC_ENT_LKUP   a
               , dtax_mlt.MLT_ENT           b 
            WHERE a.period = b.period 
              AND a.ent_id = b.ENT_ID 
       minus
            select a.period, center, ent, 2
            from dtax_model.TM_CC_ENT_LKUP  a
               , dtax_mlt.MLT_ENT           b 
            WHERE a.period = b.period 
              AND a.ent_id = b.ENT_ID 
              AND a.active = 'Y' 
    )
    ;
    Plan :
    Code:
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=52 Bytes=884)
       1    0   VIEW OF 'V_TM_CC_ENT_LKUP_NEW2' (Cost=9 Card=52 Bytes=884)
       2    1     UNION-ALL
       3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TM_CC_ENT_LKUP' (Cost=2 Card=10334 Bytes=175678)
       4    3         NESTED LOOPS (Cost=3 Card=13 Bytes=390)
       5    4           TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
       6    5             INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
       7    4           INDEX (RANGE SCAN) OF 'IDX_1_ENT_CC' (NON-UNIQUE) (Cost=1 Card=10334)
       8    2       MINUS
       9    8         SORT (UNIQUE)
      10    9           TABLE ACCESS (BY INDEX ROWID) OF 'MLT_CC_ENT_LKUP' (Cost=1 Card=40900 Bytes=613500)
      11   10             NESTED LOOPS (Cost=3 Card=26 Bytes=728)
      12   11               TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
      13   12                 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
      14   11               INDEX (RANGE SCAN) OF 'IDX_ENT' (NON-UNIQUE)
      15    8         SORT (UNIQUE)
      16   15           TABLE ACCESS (BY INDEX ROWID) OF 'TM_CC_ENT_LKUP'(Cost=2 Card=10334 Bytes=175678)
      17   16             NESTED LOOPS (Cost=3 Card=13 Bytes=390)
      18   17               TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
      19   18                 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
      20   17               INDEX (RANGE SCAN) OF 'IDX_1_ENT_CC' (NON-UNIQUE) (Cost=1 Card=10334)
    Steps :
    9 SORT (UNIQUE)
    15 SORT (UNIQUE)
    show extra sort operations.

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    He was not disputing the fact that an extra sort operation is required.

    He is simply stating that it is unlikely that sorting a grand total of 2927 rows costs an additional 58 seconds, and I quite agree.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I could sort 'em by hand faster than that! Well, not really. But nearly as fast.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2003
    Location
    UK
    Posts
    4

    EXPLAIN PLAN results

    Thank you for your responses. I looked at the execution plan. I would have expected the combined query to largely inherit original subquery plans and combine them using UNIQUE SORT, etc. Instead, the first subquery subplan is completely rewritten (the second subquery subplan is identical).

    From looking at the new plan I could understand why it runs slow - but why Oracle decides to behave so differently with effectively identical SQL code? What causes it and how could I prevent it?

    The plans for independent subqueries and a combined query are attached. I realize that without seeing the original SQL they are not great use, nevertheless they illustrate the point. In a big combined query the optimizer suddenly decides to go for a very simple recursive condition check on several large tables.

    Cheers,
    Toly

    Anatoli Krassavine
    Intellidos Limited
    Attached Files Attached Files
    Anatoli Krassavine
    toly@intellidos.com
    Intellidos Limited

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The reasons belong to the optimizer alone. Based on the 5 dozen different variables it looked at, it decided that out of the 5000 ways it could solve a query of that size, it *thought* the best way was actually *not* to wait until the very last step to do an intgersect. It re-wrote the intersect out of the query (which it always does) and simply came up with a different plan than you expected. All I can tell you is that the optimizer makes mistakes.

    How do you stop it? Well, if you mean how do I force the intersect to be the last step when I do exactly this type of statement, the answer is easy, add AND ROWNUM > 0 to both sub-queries. This will force the optimizer to solve the queries independently, thus forcing the intersect to be done last. You could also use a lot of hints to do the same thing, and it would probably be 'more correct', but it would also be more difficult. The rownum thing is easier.

    If you mean 'How do I stop the optimizer from making mistakes and/or doing things that I don't expect? Well, all I can say is can you please let the rest of us know when you figure that one out, eh?

    Lesson of the day? Never assume you know what the optimizer is or *should* be doing. *Always* check the plan.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  10. #10
    Join Date
    Sep 2003
    Location
    UK
    Posts
    4
    Hello Chris,

    Please accept my deepest appreciation for your advice - it works and solves the problem at hand. The query now runs in under 4 seconds.

    I have some related questions to clarify the situation for me, but they are not urgent any more.

    I was not naive enough to ask how to make Oracle always optimize correctly, :-). My biggest frustration was to see Oracle optimize/run independent subqueries without a glitch and then suddenly go complete bonkers when I start applying trivial set operations to their results.

    1) What are the practical drawbacks of using ROWNUM > 0 in terms of performance, etc compared with a properly optimized query?

    2) Is there a global way to force Oracle to solve the queries involved in an INTERSECT independently? I.e. is there a way to treat INTERSECT as kind of a "hard break" for query optimization?

    Thank you again,
    Toly
    Anatoli Krassavine
    toly@intellidos.com
    Intellidos Limited

Posting Permissions

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



Click Here to Expand Forum to Full Width