This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jul 13, 2012 6:40 AM by Jonathan Lewis Go to original post RSS
  • 15. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Dom,

    you are right about the query working with traditional Oracle syntax instead of ANSI, but you can also make ANSI syntax work if you declare department_id and site_id in REQUEST not nullable.

    I think the reason ANSI syntax doesn't work here is that it's preventing some query transform. I have recently had case where inability to do "normal" join predicate pushdown was causing a suboptimal plan in a query with a bunch of outer joins, and I have a feeling that something similar is going on here.

    Best regards,
    Nikolay
  • 16. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    rp0428 Guru
    Currently Being Moderated
    I'm not trying to argue - only trying to be precise. I agree with the others that you provide good explanation and same data.
    >
    rp048 - I said that the IFSS is cheaper than the Full Table Scan because its cost is lower, as reported by Oracle itself.
    >
    And as I said for the first query you don't show Oracle reporting a cost for IFSS.
    There is nothing in your post for the first query that shows any information for an Index Fast Full Scan so how can it be 'obvious'? For the second query the trace shows an IFFS is cheaper and Oracle used it.
    >
    It might seem like nit-picking but you can't use the cost of IFFS reported for query two to compare to the cost of the Full Table Scan reported by query 1. Those are two different queries and the only thing that is obvious is that Oracle either didn't consider IFFS at all for the firsts query or didn't think it was cheaper.
  • 17. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi

    >

    ...
    It might seem like nit-picking but you can't use the cost of IFFS reported for query two to compare to the cost of the Full Table Scan reported by query 1. Those are two different queries and the only thing that is obvious is that Oracle either didn't consider IFFS at all for the firsts query or didn't think it was cheaper.
    That's an interesting statement. Can you support it with an example? I.e. can you give an example of either full table scan or an index fast full scan having different costs in two different plans, generated on the same database using the same stats?

    Best regards,
    Nikolay
  • 18. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    rp0428 Guru
    Currently Being Moderated
    >
    Hinting is not really an option
    >
    Curious as to why hinting is not an option so thought I would pass on these two blogs by Maria Colgan - an Oracle optimizer team member.

    The first one talks about using SPM (sql plan management) to effectively disable a hint used by a third party app
    https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex>
    How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?
    >
    The second one talks about difference in outer joins between ansi and Oracle and how Oracle converts them
    https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle

    And just for good measure here is one on Using SQL Patch to add hints to a packaged application
    https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
  • 19. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    John Brady - UK wrote:
    Dom,

    You are right, which is where I was now heading - the OUTER joins are the issue. You have identified that it is the rewrite by Oracle to its native syntax that is not perfect for ANSI syntax joins.
    John,

    Sorry I'm coming to this late. As Dom has pointed out, the ANSI transformation that Oracle does to deal with outer joins has some annoying side effects which can be difficult to work around. Your demonstration code was terrific, though, so I took a little look at it, and have run up the model on 10.2.0.3 to see what I could find. I got a slightly different execution plan from yours - but only to the level that the last join was a right outer hash rather than a nested loop:
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |                     | 33333 |  1920K|       | 35491   (1)| 00:07:06 |
    |   1 |  SORT ORDER BY         |                     | 33333 |  1920K|  4456K| 35491   (1)| 00:07:06 |
    |*  2 |   HASH JOIN RIGHT OUTER|                     | 33333 |  1920K|       | 35012   (1)| 00:07:01 |
    |   3 |    INDEX FULL SCAN     | PK_PRODUCT_SUB_TYPE |    15 |    45 |       |     1   (0)| 00:00:01 |
    |*  4 |    HASH JOIN OUTER     |                     | 33333 |  1822K|  1728K| 35010   (1)| 00:07:01 |
    |*  5 |     HASH JOIN          |                     | 33333 |  1334K|       |   889   (3)| 00:00:11 |
    |*  6 |      TABLE ACCESS FULL | PRODUCT             | 33333 |   423K|       |   107   (4)| 00:00:02 |
    |*  7 |      TABLE ACCESS FULL | EPISODE             |   300K|  8216K|       |   778   (2)| 00:00:10 |
    |   8 |     TABLE ACCESS FULL  | REQUEST             |  4001K|    57M|       | 28832   (1)| 00:05:46 |
    ------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("PRODUCT"."PROD_SUB_TYPE_ID"="PRODUCT_SUB_TYPE"."PROD_SUB_TYPE_ID"(+))
       4 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID"(+))
       5 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")
       6 - filter("PRODUCT"."STATUS"='I')
       7 - filter("EPISODE"."DEPARTMENT_ID"=2)
    I tried putting in the correct index_ffs() hint - with correct query blocks and alias - and it was ignored; this probably means that the transformation used by Oracle to rewrite your query made the index illegal.

    I then took the approach of re-arranging the order of your predicates. I always have to think very carefully about the effects of outer joins in ANSI, especially when mixing ON clauses and WHERE clauses, and having "JOIN" appear after "OUTER JOIN". If I had written your query, I would have tried to minimise confusion (of myself) by writing it as follows:
    SELECT 
        episode.episode_id , episode.cross_ref_id , episode.date_required , 
        product.number_required , 
        request.site_id 
    FROM episode 
         JOIN product ON episode.episode_id = product.episode_id 
    LEFT JOIN product_sub_type ON product.prod_sub_type_id = product_sub_type.prod_sub_type_id 
    LEFT JOIN REQUEST on episode.cross_ref_id = request.cross_ref_id 
    WHERE (
            episode.department_id = 2
        and product.status = 'I'
          ) 
    ORDER BY episode.date_required
    ;
    I believe this is logically the same as the original query - all I have done in the statement FROM clause is expose a very clear line: episode -> product -> product_sub_type, with request tagged on the end. Here's the resulting execution plan.
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                     | 33333 |  1920K|       | 10445   (2)| 00:02:06 |
    |   1 |  SORT ORDER BY          |                     | 33333 |  1920K|  4456K| 10445   (2)| 00:02:06 |
    |*  2 |   HASH JOIN RIGHT OUTER |                     | 33333 |  1920K|       |  9966   (2)| 00:02:00 |
    |   3 |    INDEX FULL SCAN      | PK_PRODUCT_SUB_TYPE |    15 |    45 |       |     1   (0)| 00:00:01 |
    |*  4 |    HASH JOIN OUTER      |                     | 33333 |  1822K|  1728K|  9964   (2)| 00:02:00 |
    |*  5 |     HASH JOIN           |                     | 33333 |  1334K|       |   889   (3)| 00:00:11 |
    |*  6 |      TABLE ACCESS FULL  | PRODUCT             | 33333 |   423K|       |   107   (4)| 00:00:02 |
    |*  7 |      TABLE ACCESS FULL  | EPISODE             |   300K|  8216K|       |   778   (2)| 00:00:10 |
    |   8 |     INDEX FAST FULL SCAN| IX4_REQUEST         |  4001K|    57M|       |  3785   (2)| 00:00:46 |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("PRODUCT"."PROD_SUB_TYPE_ID"="PRODUCT_SUB_TYPE"."PROD_SUB_TYPE_ID"(+))
       4 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID"(+))
       5 - access("PRODUCT"."EPISODE_ID"="EPISODE"."EPISODE_ID")
       6 - filter("PRODUCT"."STATUS"='I')
       7 - filter("EPISODE"."DEPARTMENT_ID"=2)
    Clearly there is a defect in the optimizer that allows this re-arrangement to behave differently; but given the way that Oracle converts outer joins to lateral views, it isn't entirely surprising that with the ordering you had it lost track of the legality of what it could join and where with the index/table option when trying to cope with the combination of (a) outer lateral joint and (b) the potential for table entries not being in indexes. I didn't try looking at the 10053 to see if there were any specific clues about where the transformation failed on your first version, by the way.

    As a general guideline with ANSI, I would aim to avoid putting "INNER" joins after OUTER joins where possible, and then I would try to avoid breaking up "chains" of joins between tables, so (for example) if I has a 5 table join A, B, C, X, Y where A was the main driver, I would be thinking (A, B, C) (A, X, Y) rather than (A, B), (A, X) (B, C) (X,Y) - so my from clause would read: A, B, C, X, Y rather than A, B, X, C, Y (even though the latter ought to be equally valid).


    Regards
    Jonathan Lewis
  • 20. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Jonathan,

    thanks for looking at this. I found this thread very interesting, and I was frustrated that there were some unanswered questions left -- perhaps you could help with that.
    Jonathan Lewis wrote:
    >
    ...
    >
    Clearly there is a defect in the optimizer that allows this re-arrangement to behave differently; but given the way that Oracle converts outer joins to lateral views, it isn't entirely surprising that with the ordering you had it lost track of the legality of what it could join and where with the index/table option when trying to cope with the combination of (a) outer lateral joint and (b) the potential for table entries not being in indexes. I didn't try looking at the 10053 to see if there were any specific clues about where the transformation failed on your first version, by the way.
    I took John's code and simplified it a little bit further (the original code was taking to long to complete on my small local database). My Oracle version is 11.2.0.1
    create table tab1
    as select level id, rpad(0,100,0) padding
    from dual connect by level <= 1e5;
    
    drop table tab2;
    
    create table tab2
    as select level id, rpad(0,100,0) padding
    from dual connect by level <= 1e5;
    
    drop table tab3;
    
    create table tab3
    as select level id, rpad(0,100,0) padding
    from dual connect by level <= 1e5;
    
    create table tab4
    as select level id, rpad(0,100,0) padding
    from dual connect by level <= 1e5;
    
    create index i$tab1 on tab1(id);
    
    create index i$tab2 on tab2(id);
    
    create index i$tab3 on tab3(id);
    
    create index i$tab4 on tab4(id);
    
    alter session set events='10053 trace name context forever';
    
    
    select --+ bad_plan
    tab1.id, tab2.id
    from tab1 left outer join tab2 on (TAB1.ID = tab2.id) 
                   inner join tab3 on (tab1.id = tab3.id)
                   left outer join tab4 on (tab1.id = tab4.id);
    
    alter session set events='10053 trace name context off';
    In the resulting 10053 trace files (see below), I see a bunch of query rewrites
    using lateral views. Then at some points the lateral views disappear and don't
    come back (see "Final query after transformations").

    Does this mean that the optimizer first transformed the outer join into a lateral view,
    and they did a reverse transformation?

    And yet another question -- when you talk about legal and illegal transformation, am I
    correct in assuming that legality of transformation in this case is depending on nullability
    (i.e. it may be illegal to replace FTS with IFFS if there are NULLs involved)? I'm asking because
    I managed to get a good plan by declaring a column non-nullable (it's somewhere earlier in this
    thread).
    ***************************
    OBYE:     OBYE bypassed: no order by to eliminate.
    JE:   Considering Join Elimination on query block SEL$2 (#0)
    *************************
    Join Elimination (JE)    
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT "TAB1"."ID" "QCSJ_C000000000300000","from$_subquery$_008"."ID_0" "QCSJ_C000000000300001" FROM "SCOTT"."TAB1" "TAB1", LATERAL( (SELECT "TAB2"."ID" "ID_0" FROM "SCOTT"."TAB2" "TAB2" WHERE "TAB1"."ID"="TAB2"."ID"))(+) "from$_subquery$_008"
    SQL:******* UNPARSED QUERY IS *******
    SELECT "TAB1"."ID" "QCSJ_C000000000300000","from$_subquery$_008"."ID_0" "QCSJ_C000000000300001" FROM "SCOTT"."TAB1" "TAB1", LATERAL( (SELECT "TAB2"."ID" "ID_0" FROM "SCOTT"."TAB2" "TAB2" WHERE "TAB1"."ID"="TAB2"."ID"))(+) "from$_subquery$_008"
    Query block SEL$2 (#0) unchanged
    JE:   Considering Join Elimination on query block SEL$3 (#0)
    *************************
    Join Elimination (JE)    
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT "from$_subquery$_003"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000","from$_subquery$_003"."QCSJ_C000000000300001_1" "QCSJ_C000000000300001","TAB3"."ID" "ID" FROM  (SELECT "TAB1"."ID" "QCSJ_C000000000300000_0","from$_subquery$_008"."ID_0" "QCSJ_C000000000300001_1" FROM "SCOTT"."TAB1" "TAB1", LATERAL( (SELECT "TAB2"."ID" "ID_0" FROM "SCOTT"."TAB2" "TAB2" WHERE "TAB1"."ID"="TAB2"."ID"))(+) "from$_subquery$_008") "from$_subquery$_003","SCOTT"."TAB3" "TAB3" WHERE "from$_subquery$_003"."QCSJ_C000000000300000_0"="TAB3"."ID"
    SQL:******* UNPARSED QUERY IS *******
    SELECT "from$_subquery$_003"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000","from$_subquery$_003"."QCSJ_C000000000300001_1" "QCSJ_C000000000300001","TAB3"."ID" "ID" FROM  (SELECT "TAB1"."ID" "QCSJ_C000000000300000_0","from$_subquery$_008"."ID_0" "QCSJ_C000000000300001_1" FROM "SCOTT"."TAB1" "TAB1", LATERAL( (SELECT "TAB2"."ID" "ID_0" FROM "SCOTT"."TAB2" "TAB2" WHERE "TAB1"."ID"="TAB2"."ID"))(+) "from$_subquery$_008") "from$_subquery$_003","SCOTT"."TAB3" "TAB3" WHERE "from$_subquery$_003"."QCSJ_C000000000300000_0"="TAB3"."ID"
    Query block SEL$3 (#0) unchanged
    JE:   Considering Join Elimination on query block SEL$5 (#0)
    *************************
    Join Elimination (JE)    
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT "from$_subquery$_005"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000","from$_subquery$_005"."QCSJ_C000000000300001_1" "QCSJ_C000000000300001","from$_subquery$_005"."ID_2" "QCSJ_C000000000700000","from$_subquery$_009"."ID_0" "QCSJ_C000000000700001" FROM  (SELECT "from$_subquery$_003"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000_0","from$_subquery$_003"."QCSJ_C000000000300001_1" "QCSJ_C000000000300001_1","TAB3"."ID" "ID_2" FROM  (SELECT "TAB1"."ID" "QCSJ_C000000000300000_0","from$_subquery$_008"."ID_0" "QCSJ_C000000000300001_1" FROM "SCOTT"."TAB1" "TAB1", LATERAL( (SELECT "TAB2"."ID" "ID_0" FROM "SCOTT"."TAB2" "TAB2" WHERE "TAB1"."ID"="TAB2"."ID"))(+) "from$_subquery$_008") "from$_subquery$_003","SCOTT"."TAB3" "TAB3" WHERE "from$_subquery$_003"."QCSJ_C000000000300000_0"="TAB3"."ID") "from$_subquery$_005", LATERAL( (SELECT "TAB4"."ID" "ID_0" FROM "SCOTT"."TAB4" "TAB4" WHERE "from$_subquery$_005"."QCSJ_C000000000300000_0"="TAB4"."ID"))(+) "from$_subquery$_009"
    SQL:******* UNPARSED QUERY IS *******
    SELECT "from$_subquery$_005"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000","from$_subquery$_005"."QCSJ_C000000000300001_1" "QCSJ_C000000000300001","from$_subquery$_005"."ID_2" "QCSJ_C000000000700000","from$_subquery$_009"."ID_0" "QCSJ_C000000000700001" FROM  (SELECT "from$_subquery$_003"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000_0","from$_subquery$_003"."QCSJ_C000000000300001_1" "QCSJ_C000000000300001_1","TAB3"."ID" "ID_2" FROM  (SELECT "TAB1"."ID" "QCSJ_C000000000300000_0","from$_subquery$_008"."ID_0" "QCSJ_C000000000300001_1" FROM "SCOTT"."TAB1" "TAB1", LATERAL( (SELECT "TAB2"."ID" "ID_0" FROM "SCOTT"."TAB2" "TAB2" WHERE "TAB1"."ID"="TAB2"."ID"))(+) "from$_subquery$_008") "from$_subquery$_003","SCOTT"."TAB3" "TAB3" WHERE "from$_subquery$_003"."QCSJ_C000000000300000_0"="TAB3"."ID") "from$_subquery$_005", LATERAL( (SELECT "TAB4"."ID" "ID_0" FROM "SCOTT"."TAB4" "TAB4" WHERE "from$_subquery$_005"."QCSJ_C000000000300000_0"="TAB4"."ID"))(+) "from$_subquery$_009"
    ...
    Final query after transformations:******* UNPARSED QUERY IS *******
    SELECT "TAB1"."ID" "ID","TAB2"."ID" "ID" FROM "SCOTT"."TAB1" "TAB1","SCOTT"."TAB2" "TAB2","SCOTT"."TAB3" "TAB3","SCOTT"."TAB4" "TAB4" WHERE "TAB1"."ID"="TAB4"."ID"(+) AND "TAB1"."ID"="TAB3"."ID" AND "TAB1"."ID"="TAB2"."ID"(+)
    Best regards,
    Nikolay
  • 21. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Nikolay Savvinov wrote:
    Hi Jonathan,

    thanks for looking at this. I found this thread very interesting, and I was frustrated that there were some unanswered questions left -- perhaps you could help with that.
    Possibly not - it can take a lot of time to work out from the 10053 what the optimizer is probably doing; I only do it if (a) the client really wants it done and wants to pay for it, or (b) I find something interesting and potentially wide-ranging and have some free time.

    >
    I took John's code and simplified it a little bit further (the original code was taking to long to complete on my small local database). My Oracle version is 11.2.0.1
    I did run John's test on 11.2.0.3 - and found that the original 4-table query was subject to join elimination, which nominally brought it down to the three table join (excluding product sub type) except the resulting three table plan STILL did a full tablescan on REQUESTS.

    >
    In the resulting 10053 trace files (see below), I see a bunch of query rewrites
    using lateral views. Then at some points the lateral views disappear and don't
    come back (see "Final query after transformations").

    Does this mean that the optimizer first transformed the outer join into a lateral view,
    and they did a reverse transformation?
    I suspect that the lateral rewrite is a purely heuristic rewrite designed to take care of ANSI OUTER joins (perhaps limited to certain classes of ANSI OUTER), after which further "normal" transformations take place anyway. (Technically Oracle can accept lateral joins in ordinary code, although you have to set and event to make it legal http://jonathanlewis.wordpress.com/2011/01/31/ansi-outer/ , so it's not too surprising to see the mechanism used as an intermediate step in handling ANSI.) If you want another analogy, think about subquery unnesting - the optimizer can unnest an IN subquery to turn it into an in-line aggregate/distinct view, and may produce a plan that shows that aggregation; but it may then use complex view merging as a second transformation and join before aggregating.

    Regards
    Jonathan Lewis
  • 22. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Nikolay Savvinov wrote:

    And yet another question -- when you talk about legal and illegal transformation, am I
    correct in assuming that legality of transformation in this case is depending on nullability
    (i.e. it may be illegal to replace FTS with IFFS if there are NULLs involved)? I'm asking because
    I managed to get a good plan by declaring a column non-nullable (it's somewhere earlier in this
    thread).
    It's important to be careful with words: I didn't say legal and illegal transformations, I talked about transformations that would make the index illegal, and the nullability thing is exactly the type of thing I had in mind.

    When you write SQL with outer joins in ANSI you find that there are a lot of "basic" query blocks - roughly one per "outered" table. It may be coincidence by every plan which involved a tablescan of REQUESTS had that table in query blocks sel$1 or sel$2 (i.e. the first two after the heuristic lateral rewrite that I assume has to take place). The plans where the index fast full scan was involved had REQUESTS in sel$4 (and possibly, for one plan, sel$3). This may be an indication of some anomaly with the optimizer's code (perhaps a side effect of some other coded assumption) that assumes the driving tables can't be allowed to use index-only paths if that may miss table rows. This could, of course, be an illusion due to some other requirement that we don't know about.

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points