4 Replies Latest reply: Jul 27, 2010 12:29 AM by 698658 RSS

    AND_EQUAL hint ignored

    698658
      Hi,
      in my 9.2.0.8 I've got query like:
      SELECT /*+ AND_EQUAL(l I_LOK_OPJ_FK I_LOK_DGR_FK) USE_NL(L) USE_NL(OPJ)*/ MAX(lok_audyt_dt)
                          FROM  LIMITY_OKRESY l,  OSOBA_PROGRAM_LOJAL opj,  OSOBY oss
                          WHERE lok_opj_id = opj_id
                          AND opj_osb_id = osb_id
                          AND osb_audyt_st = '1'
                          AND opj_audyt_st = '1'
                          AND lok_audyt_st = '1'
                          and osb_audyt_st = opj_audyt_st
                          and osb_audyt_st = lok_audyt_st
                          and lok_dgr_id = 26
                          AND oss.osb_pesel =  '33010511133'
      
      -------------------------------------------------------------------------------------------
      | Id  | Operation                      |  Name                    | Rows  | Bytes | Cost  |
      -------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |                          |     1 |    57 | 75898 |
      |   1 |  SORT AGGREGATE                |                          |     1 |    57 |       |
      |   2 |   NESTED LOOPS                 |                          |   929 | 52953 | 75898 |
      |   3 |    NESTED LOOPS                |                          |   900 | 34200 |  1198 |
      |   4 |     TABLE ACCESS BY INDEX ROWID|  OSOBY                |    92 |  2024 |    94 |
      |   5 |      INDEX RANGE SCAN          | I_OSB_PESEL            |    92 |       |     3 |
      |   6 |     TABLE ACCESS BY INDEX ROWID|  OSOBA_PROGRAM_LOJAL  |    10 |   160 |    12 |
      |   7 |      INDEX RANGE SCAN          | I_OPJ_SYNCHRO2         |    10 |       |     3 |
      |   8 |    TABLE ACCESS BY INDEX ROWID |  LIMITY_OKRESY        |     1 |    19 | 75898 |
      |   9 |     AND-EQUAL                  |                          |       |       |       |
      |  10 |      INDEX RANGE SCAN          | I_LOK_OPJ_FK           |  1363K|       |    80 |
      |  11 |      INDEX RANGE SCAN          | I_LOK_DGR_FK           |  1363K|       |    80 |
      -------------------------------------------------------------------------------------------
      
      Note: cpu costing is off, PLAN_TABLE' is old version
      
      
      
          explain plan for
          SELECT /*+ AND_EQUAL(l I_LOK_OPJ_FK I_LOK_DGR_FK) */ MAX(lok_audyt_dt)
                              FROM  LIMITY_OKRESY l,  OSOBA_PROGRAM_LOJAL opj,  OSOBY oss
                              WHERE lok_opj_id = opj_id
                              AND opj_osb_id = osb_id
                              AND osb_audyt_st = '1'
                              AND opj_audyt_st = '1'
                              AND lok_audyt_st = '1'
                              and osb_audyt_st = opj_audyt_st
                             and osb_audyt_st = lok_audyt_st
                             and lok_dgr_id = 26
                             AND oss.osb_pesel =  '33010511133'
      
      Explained.
      
      SQL> select * from table(dbms_xplan.display());
      
      --------------------------------------------------------------------------------------------
      | Id  | Operation                       |  Name                    | Rows  | Bytes | Cost  |
      --------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                |                          |     1 |    57 |  1844 |
      |   1 |  SORT AGGREGATE                 |                          |     1 |    57 |       |
      |   2 |   HASH JOIN                     |                          |   929 | 52953 |  1844 |
      |   3 |    TABLE ACCESS BY INDEX ROWID  |  OSOBA_PROGRAM_LOJAL  |    10 |   160 |    12 |
      |   4 |     NESTED LOOPS                |                          |   900 | 34200 |  1198 |
      |   5 |      TABLE ACCESS BY INDEX ROWID|  OSOBY                |    92 |  2024 |    94 |
      |   6 |       INDEX RANGE SCAN          | I_OSB_PESEL            |    92 |       |     3 |
      |   7 |      INDEX RANGE SCAN           | I_OPJ_SYNCHRO2         |    10 |       |     3 |
      |   8 |    TABLE ACCESS BY INDEX ROWID  |  LIMITY_OKRESY        |  1363K|    24M|   630 |
      |   9 |     INDEX RANGE SCAN            | I_LOK_DGR_FK           |  1363K|       |    80 |
      --------------------------------------------------------------------------------------------
      
      Note: cpu costing is off, PLAN_TABLE' is old version
      The first query which respects AND_EQUAL hint runns with 243 cr , the second one which somehow ignores the hint need about 40k cr to complete .
      Why CBO is ignoring my and_equal hint I need that for performance .
      Please advice.
      Regards.
      Greg
        • 1. Re: AND_EQUAL hint ignored
          riedelme
          Figuring out why the CBO makes its decisions can be very hard.

          My first thought is that you may not have the conditions required for AND_EQUAL to occur. This is supported by the fact that the first index you specified isn't listed in the execution plan. See if you can change the query to promote use of the missing index and see what happens

          My second thought is that the cost of the plan your query is using is lower than the cost of using the AND_EQUAL if it should take place.

          My third thought is that the CBO JUST DOES NOT WANT TO USE AND_EQUAL IN THE SECOND QUERY AND YOU CAN'T MAKE IT! NYAH! NYAH! NYAH!

          Some posters on OTN insist that hints are really directives and MUST be followed when specified. My experience is that the CBO will use a hint if it wants to and not if it does not want to - I have seen hints (usually INDEX) more or less ignored for no apparent reason several times (lacking the time to do the 10053 trace indicated below).

          You can do a 10053 trace to see what the CBO is considering if that would be helpful
          • 2. Re: AND_EQUAL hint ignored
            Charles Hooper
            user10388717 wrote:
            Hi,
            in my 9.2.0.8 I've got query like:
            (snip)

            Note: cpu costing is off, PLAN_TABLE' is old version
            The first query which respects AND_EQUAL hint runns with 243 cr , the second one which somehow ignores the hint need about 40k cr to complete .
            Why CBO is ignoring my and_equal hint I need that for performance .
            Please advice.
            Regards.
            Greg
            Greg,

            I do not see anything that would explicitly prevent the AND_EQUAL hint from working, other than an automatic transformation of the SQL statement that only happened when the two nested loop hints were provided. Hints are directives - the optimizer must obey hints unless:
            * The hint is invalid due to the wrong alias used in the hint
            * The hint is malformed
            * The hint is incompatible with another hint
            * The query was transformed by the optimizer into a form that is incompatible with the hint before the optimizer applied the hint
            * The hint, if followed, would cause the wrong results to be returned

            See this article for more information:
            http://hoopercharles.wordpress.com/2010/07/19/demonstration-of-oracle-ignoring-an-index-hint/

            I would assume that bullet point #4 applies in this case. Examining a 10053 trace, as suggested by riedelme, might help. I believe that the 9i version of a 10053 trace will show how the query is being transformed as it is optimized, just like 10g and above.

            Note that the AND_EQUAL hint is deprecated as of Oracle 10g. Jonathan Lewis' "Cost-Based Oracle Fundamentals" book contains a little bit of information about that hint and the AND EQUAL execution path:
            http://books.google.com/books?id=TGSd3pkMx5IC&pg=PA457

            Charles Hooper
            Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
            http://hoopercharles.wordpress.com/
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: AND_EQUAL hint ignored
              Boneist
              riedelme wrote:
              Some posters on OTN insist that hints are really directives and MUST be followed when specified.
              From what Jonathan Lewis says on the matter, I believe it's akin to saying "When you drive from London to Scotland, take the the M6 toll road and avoid Birmingham altogether (surely that's worth the ~£5?! *{;-) )". But that "directive" is completely irrelevant if you end up going via the A1, which happens to have less roadworks at that time!
              • 4. Re: AND_EQUAL hint ignored
                698658
                Thank You,
                I disappointed that AND_EQUAL hint is deprecated in 10 and above. All that b-tree conversion to bitmaps transformations are so inefficient .
                When I'm observing plans with that step there is always performance problem (9i for sure) .
                Regards.
                Greg