1 2 Previous Next 28 Replies Latest reply: Feb 14, 2013 5:07 PM by Dropbear67 Go to original post RSS
      • 15. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
        Jonathan Lewis
        Dropbear67 wrote:
        I'm at a bit of a loss now as to where to proceed without posting the entire SPM Trace file, so excuse me for taking up so much bandwidth.
        NLJ_BATCHING(@"SEL$EE94F965" "CFCO"@"SEL$3")
        I think you've found a defect in the way Oracle is handling optimizer_features_enable.
        The hints in the original SQL Baseline don't tell Oracle whether to use nlj_prefetch or nlj_batching. The latter is only available in 11.2, so there's no hint in 10.2 to block it or make it happen. When the query is optimized with the given baseline the optimizer chooses nlj_batching because it can - causing a plan mismatch. I would argue that setting optimzer_features_enable to 10.2.0.5 should disable nlj_batching unavailable. You might check all the other plans that misbehave to see show the nlj_batching() hint.

        Workaround - to be confirmed by Oracle Support since is uses a hidden parameter:
        alter session set "_nlj_batching_enabled" = 0;
        You could put this setting into a logon trigger, or you could add it to the parameter file.

        Update: there are more precise alternative strategies:
        e.g. set the parameter for a single session then generate a new SQL Baseline that includes the new outline; or use dbms_sqldiag_internal package to create a patch for the SQL adding the hint NLJ_PREFETCH() to the SQL with the same parameter that the current plan has for its NLJ_BATCHING() hint. (There is a blog article from the optimizer support group about the patch feature, but I can't find it at present, here's a link to a blog by Dom Brooks that gives an example: http://orastory.wordpress.com/2012/03/09/sql-patch-iii-plus-parallel-baselines/ )


        Regards
        Jonathan Lewis
        • 16. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
          Dom Brooks
          There is a blog article from the optimizer support group about the patch feature, but I can't find it at present
          Here it is:
          https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
          use dbms_sqldiag_internal package to create a patch for the SQL adding the hint NLJ_PREFETCH() to the SQL with the same parameter that the current plan has for its NLJ_BATCHING() hint
          here's a link to a blog by Dom Brooks that gives an example: http://orastory.wordpress.com/2012/03/09/sql-patch-iii-plus-parallel-baselines/
          Jonathan,

          I'm not sure if you are suggesting using a sql patch to apply the NLJ_PREFETCH hint on top of the SQL Plan baseline.

          If so, then in that article I did look at trying to combine a sql patch with a baseline and it was not a successful combination, reason being that the optimizer used the baseline to try to generate the plan, failed and rejected the baseline, generated the best cost plan and then applied the sql patch (which may or may not be relevant to the best cost plan).
          set the parameter for a single session then generate a new SQL Baseline that includes the new outline;
          I think this would be the best approach.
          Another alternative, as mentioned, being the COE_XFR_SQL_PROFILE.SQL script via Oracle Support doc id 215187.1, using the SQL Profile mechanism to apply the outline hints thus bypassing the plan hash requirement.

          Another alternative might be to set the NLJ_PREFETCH hint via OPT_PARAM in the sql statement but that would probably change the sql signature. But then you could remap the baseline plan to a different signature using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE, see http://orastory.wordpress.com/2011/10/13/no-need-to-change-source-code-hint-it-using-a-baseline/

          There are quite a few ways to skin this cat.
          I would argue that setting optimzer_features_enable to 10.2.0.5 should disable nlj_batching unavailable
          Undoubtedly.
          This seems to be an obvious weakness in the case for using baselines for such an upgrade.

          I've always had this doubt about OFE when I've wanted to look at the effects of a bug from a previous version, e.g. a wrong results bug, that's fixed in the version I'm on. I've never been able to use OFE to go back to this bug like behaviour. Of course, that's probably understandable but illustrates that there are some features that OFE affects and some it doesn't. And to what extent is V$SYSTEM_FIX_CONTROL a list of things it does turn off and on? I suppose I should look a deeper look at an optimizer trace sometime and what is documented as off/on with different OFE settings.

          I'm at a bit of a loss now as to where to proceed without posting the entire SPM Trace file
          apologies once again for the huge post.
          Dropbear67 - This is exactly what was required.
          • 17. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
            Randolf Geist
            Jonathan Lewis wrote:
            I think you've found a defect in the way Oracle is handling optimizer_features_enable.
            The hints in the original SQL Baseline don't tell Oracle whether to use nlj_prefetch or nlj_batching. The latter is only available in 11.2, so there's no hint in 10.2 to block it or make it happen. When the query is optimized with the given baseline the optimizer chooses nlj_batching because it can - causing a plan mismatch. I would argue that setting optimzer_features_enable to 10.2.0.5 should disable nlj_batching unavailable. You might check all the other plans that misbehave to see show the nlj_batching() hint.
            Jonathan,

            I'm not sure I would agree to this entirely - the NLJ_BATCHING hint comes from the section of the trace where SPM tries to reproduce using the session OFE (which happens to be 11.2.0.3) and the plan outline hints. I'm not sure what the rationale behind this is, by the way, but there is probably a good reason why SPM tries to do so (first).

            The next sections show attempts with OFE set to 10.2.0.3 as suggested by the outline, but the optimizer is still unable to reproduce.

            Therefore it probably would be important to see the plan thar is supposed to be re-produced as outlined by Dominic, to understand the difference why the optimizer can't even reproduce with OFE 10.2.0.3.

            Randolf
            • 18. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
              Dom Brooks
              Randolf,

              What you're saying is that we still don't know exactly what "planId in plan baseline = 386463207" looks like, right?

              And neither does the 11.2.0.3 optimizer yet as it's not been able to generate it from:
              1. The baseline hints
              2. Just the OFE hint
              3. The best cost plan generation.

              So OP needs to provide this from 10.2.0.3 environment so that we can try to confirm what the missing piece of the jigsaw is.
              • 19. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                Randolf Geist
                Dom Brooks wrote:
                Randolf,

                What you're saying is that we still don't know exactly what "planId in plan baseline = 386463207" looks like, right?

                And neither does the 11.2.0.3 optimizer yet as it's not been able to generate it from:
                1. The baseline hints
                2. Just the OFE hint
                3. The best cost plan generation.

                So OP needs to provide this from 10.2.0.3 environment so that we can try to confirm what the missing piece of the jigsaw is.
                Hi Dominic,

                yes, but I think Jonathan is on the right track, see the latest post on his blog: http://jonathanlewis.wordpress.com/2013/02/13/sts-ofe-and-spm/

                So probably the original plan includes a NLJ prefetching but the reproduced plans don't, as Jonathan outlines in his post.

                But in order to confirm the original plan would be helpful, yes.

                Randolf
                • 20. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                  Jonathan Lewis
                  Randolf Geist wrote:
                  >

                  Jonathan,

                  I'm not sure I would agree to this entirely - the NLJ_BATCHING hint comes from the section of the trace where SPM tries to reproduce using the session OFE (which happens to be 11.2.0.3) and the plan outline hints. I'm not sure what the rationale behind this is, by the way, but there is probably a good reason why SPM tries to do so (first).

                  The next sections show attempts with OFE set to 10.2.0.3 as suggested by the outline, but the optimizer is still unable to reproduce.
                  Randolf,

                  I think you're right - damn nuisance since I've already blogged about the principle.
                  Therefore it probably would be important to see the plan thar is supposed to be re-produced as outlined by Dominic, to understand the difference why the optimizer can't even reproduce with OFE 10.2.0.3.
                  True, and it would be interesting to see the plan finally produced - but I have a plan B.
                  The trace shows the 11.2.0.3 plan with nlj_prefetching at a cost of 22 for 42 rows; then it shows plans with neither nlj_prefetch nor nlj_batching at a cost of 179 for 153 rows. Such simple changes in plan shouldn't result in significant changes in cardinality, so something odd has happened there. (Given the FIRST_ROWS setting, almost anything can happen with the arithmetic, of course.)

                  My blog example shows an NLJ_PREFETCH path on a real 10.2.0.3 which disappears on 11.2.0.3 with OFE set to 10.2.0.3. Perhaps the same disappearing trick has occurred here. That would explain the rejection of the original plan.


                  UPDATE: I'm also a little curious about the query that produced this plan - how come we get up to sel$4 without having a table referenced from sel$4; and how come there's a "not null" predicate at line 10 on a join. The latter suggests that there was some type of subquery that got transformed out of existence - but without playing with an 11.2 instance I'm a little stuck on the former.

                  Regards
                  Jonathan Lewis

                  Edited by: Jonathan Lewis on Feb 13, 2013 4:39 PM
                  • 21. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                    Dropbear67
                    I think Jonathon might be on the right track.

                    Before reading this replies, I did the following and managed to get my plan to stick.

                    On our 11.2.0.3 test database;

                    1. For my current session - set optimizer_features_enable to 10.2.0.3

                    2. Do an explain plan for the query in question - verify that it is producing the same plan as we are seeing in our current 10.2.0.3 production environment

                    3. Run the query using bind variables captured from the prod database.

                    4. Locate the query in v$SQL and verify the plan_hash_value I found while testing two separate queries that the SQL ID produced was different than the 10.2.0.3 environment. Why? I have no idea

                    5. Use dbms_spm.load_plans_from_cursor_cache('&SQL_ID') with the discovered SQL ID to produce a baseline plan from the 10.2.0.3 plan


                    I then set the optimizer_features_enable back to 11.2.0.3, flushed the shared pool (quick and dirty way for me to force a reparse) and yes, the explain plan indicated that it was using the stored baseline, and was indeed returning the 10.2.03 execution plan as expected.

                    So I'm not sure exactly why this approach worked, and not the approach from loading the plan via the SQL Tuning Sets . Perhaps it has something to do with this NLJ_PREFETCH which I must admit I have no idea or experience with.

                    I'm also not comfortable with the query produing a different SQL ID in the 11.2.0.3 environment (which made it difficult for me to locate the query in V$SQL - I loathe using SQL TEXT matching, especially in this environment where statements are long, complicated and similar)

                    Can I just give a huge shout out to Jonathon, Dom and later Randolf for sticking with this and working through what seems to be to be a difficult issue with difficult to follow examples. What a fabulous community effort.
                    • 22. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                      Dropbear67
                      The plans are as follows;

                      10.2.0.3
                      Plan hash value: 1984058303
                      
                      ------------------------------------------------------------------------------------------------------
                      | Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
                      ------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                |                    |   153 | 11628 |   179   (2)| 00:00:01 |
                      |   1 |  SORT UNIQUE                    |                    |   153 | 11628 |   178   (2)| 00:00:01 |
                      |*  2 |   TABLE ACCESS BY INDEX ROWID   | FN_ACCOUNT         |     1 |    14 |     1   (0)| 00:00:01 |
                      |   3 |    NESTED LOOPS                 |                    |   153 | 11628 |   177   (1)| 00:00:01 |
                      |   4 |     NESTED LOOPS                |                    |   826 | 51212 |    11   (0)| 00:00:01 |
                      |   5 |      NESTED LOOPS               |                    |   826 | 26432 |     3   (0)| 00:00:01 |
                      |   6 |       TABLE ACCESS FULL         | CL_PROVIDER_GROUP  |   116 |  2668 |     2   (0)| 00:00:01 |
                      |*  7 |       INDEX RANGE SCAN          | CPGP_UK            |     7 |    63 |     1   (0)| 00:00:01 |
                      |   8 |      TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_DETAIL |     1 |    30 |     1   (0)| 00:00:01 |
                      |*  9 |       INDEX UNIQUE SCAN         | CPDE_PK            |     1 |       |     1   (0)| 00:00:01 |
                      |* 10 |     INDEX RANGE SCAN            | FACC_CPDE_FK_I     |    27 |       |     1   (0)| 00:00:01 |
                      ------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - filter(UPPER("CPGR"."NAME") LIKE UPPER('%'||:1||'%') OR UPPER("CPDE"."NAME") LIKE
                                    UPPER('%'||:2||'%') OR UPPER("CPDE"."ABN") LIKE UPPER(:3||'%') OR "CFCO"."ACCT_NO" LIKE
                                    UPPER(:4||'%'))
                         7 - access("CPGP"."CPGR_ID"="CPGR"."CPGR_ID")
                         9 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                        10 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                             filter("CFCO"."CPDE_ID" IS NOT NULL)
                      and the one being produced in 11.2.0.3
                      Plan hash value: 3177569161
                      
                      --------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
                      --------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                  |                          |    41 |  3116 |    19  (11)| 00:00:01 |
                      |   1 |  SORT UNIQUE                      |                          |    41 |  3116 |    18   (6)| 00:00:01 |
                      |   2 |   CONCATENATION                   |                          |       |       |            |          |
                      |   3 |    NESTED LOOPS                   |                          |       |       |            |          |
                      |   4 |     NESTED LOOPS                  |                          |     1 |    76 |     4   (0)| 00:00:01 |
                      |   5 |      NESTED LOOPS                 |                          |     1 |    53 |     3   (0)| 00:00:01 |
                      |   6 |       NESTED LOOPS                |                          |     1 |    44 |     2   (0)| 00:00:01 |
                      |*  7 |        TABLE ACCESS BY INDEX ROWID| FN_ACCOUNT               |     1 |    14 |     1   (0)| 00:00:01 |
                      |*  8 |         INDEX RANGE SCAN          | FACC_UK                  |     2 |       |     1   (0)| 00:00:01 |
                      |   9 |        TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_DETAIL       |     1 |    30 |     1   (0)| 00:00:01 |
                      |* 10 |         INDEX UNIQUE SCAN         | CPDE_PK                  |     1 |       |     1   (0)| 00:00:01 |
                      |  11 |       TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_GROUP_PERIOD |     1 |     9 |     1   (0)| 00:00:01 |
                      |* 12 |        INDEX RANGE SCAN           | CPGP_CPDE_FK_I           |     1 |       |     1   (0)| 00:00:01 |
                      |* 13 |      INDEX UNIQUE SCAN            | CPGR_PK                  |     1 |       |     1   (0)| 00:00:01 |
                      |  14 |     TABLE ACCESS BY INDEX ROWID   | CL_PROVIDER_GROUP        |     1 |    23 |     1   (0)| 00:00:01 |
                      |  15 |    NESTED LOOPS                   |                          |       |       |            |          |
                      |  16 |     NESTED LOOPS                  |                          |     4 |   304 |     4   (0)| 00:00:01 |
                      |  17 |      NESTED LOOPS                 |                          |     4 |   248 |     3   (0)| 00:00:01 |
                      |  18 |       NESTED LOOPS                |                          |     4 |   156 |     2   (0)| 00:00:01 |
                      |  19 |        TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_DETAIL       |     4 |   120 |     1   (0)| 00:00:01 |
                      |* 20 |         INDEX RANGE SCAN          | CPDE_I5                  |     2 |       |     1   (0)| 00:00:01 |
                      |  21 |        TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_GROUP_PERIOD |     1 |     9 |     1   (0)| 00:00:01 |
                      |* 22 |         INDEX RANGE SCAN          | CPGP_CPDE_FK_I           |     1 |       |     1   (0)| 00:00:01 |
                      |  23 |       TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_GROUP        |     1 |    23 |     1   (0)| 00:00:01 |
                      |* 24 |        INDEX UNIQUE SCAN          | CPGR_PK                  |     1 |       |     1   (0)| 00:00:01 |
                      |* 25 |      INDEX RANGE SCAN             | FACC_CPDE_FK_I           |     1 |       |     1   (0)| 00:00:01 |
                      |* 26 |     TABLE ACCESS BY INDEX ROWID   | FN_ACCOUNT               |     1 |    14 |     1   (0)| 00:00:01 |
                      |  27 |    NESTED LOOPS                   |                          |       |       |            |          |
                      |  28 |     NESTED LOOPS                  |                          |     2 |   152 |     4   (0)| 00:00:01 |
                      |  29 |      NESTED LOOPS                 |                          |     2 |   124 |     3   (0)| 00:00:01 |
                      |  30 |       NESTED LOOPS                |                          |     2 |    78 |     2   (0)| 00:00:01 |
                      |* 31 |        TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_DETAIL       |     2 |    60 |     1   (0)| 00:00:01 |
                      |* 32 |         INDEX RANGE SCAN          | CPDE_I1                  |     2 |       |     1   (0)| 00:00:01 |
                      |  33 |        TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_GROUP_PERIOD |     1 |     9 |     1   (0)| 00:00:01 |
                      |* 34 |         INDEX RANGE SCAN          | CPGP_CPDE_FK_I           |     1 |       |     1   (0)| 00:00:01 |
                      |  35 |       TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_GROUP        |     1 |    23 |     1   (0)| 00:00:01 |
                      |* 36 |        INDEX UNIQUE SCAN          | CPGR_PK                  |     1 |       |     1   (0)| 00:00:01 |
                      |* 37 |      INDEX RANGE SCAN             | FACC_CPDE_FK_I           |     1 |       |     1   (0)| 00:00:01 |
                      |* 38 |     TABLE ACCESS BY INDEX ROWID   | FN_ACCOUNT               |     1 |    14 |     1   (0)| 00:00:01 |
                      |  39 |    NESTED LOOPS                   |                          |       |       |            |          |
                      |  40 |     NESTED LOOPS                  |                          |    44 |  3344 |     5   (0)| 00:00:01 |
                      |  41 |      NESTED LOOPS                 |                          |    44 |  2728 |     4   (0)| 00:00:01 |
                      |  42 |       NESTED LOOPS                |                          |    44 |  1408 |     3   (0)| 00:00:01 |
                      |* 43 |        TABLE ACCESS FULL          | CL_PROVIDER_GROUP        |     6 |   138 |     2   (0)| 00:00:01 |
                      |* 44 |        INDEX RANGE SCAN           | CPGP_UK                  |     8 |    72 |     1   (0)| 00:00:01 |
                      |* 45 |       TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_DETAIL       |     1 |    30 |     1   (0)| 00:00:01 |
                      |* 46 |        INDEX UNIQUE SCAN          | CPDE_PK                  |     1 |       |     1   (0)| 00:00:01 |
                      |* 47 |      INDEX RANGE SCAN             | FACC_CPDE_FK_I           |     1 |       |     1   (0)| 00:00:01 |
                      |* 48 |     TABLE ACCESS BY INDEX ROWID   | FN_ACCOUNT               |     1 |    14 |     1   (0)| 00:00:01 |
                      --------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         7 - filter("CFCO"."CPDE_ID" IS NOT NULL)
                         8 - access("CFCO"."ACCT_NO" LIKE UPPER(:4||'%'))
                             filter("CFCO"."ACCT_NO" LIKE UPPER(:4||'%'))
                        10 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                        12 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                        13 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                        20 - access(UPPER("ABN") LIKE UPPER(:3||'%'))
                             filter(UPPER("ABN") LIKE UPPER(:3||'%'))
                        22 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                        24 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                        25 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                             filter("CFCO"."CPDE_ID" IS NOT NULL)
                        26 - filter(LNNVL("CFCO"."ACCT_NO" LIKE UPPER(:4||'%')))
                        31 - filter(LNNVL(UPPER("ABN") LIKE UPPER(:3||'%')))
                        32 - access(UPPER("NAME") LIKE UPPER('%'||:2||'%'))
                             filter(UPPER("NAME") LIKE UPPER('%'||:2||'%'))
                        34 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                        36 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                        37 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                             filter("CFCO"."CPDE_ID" IS NOT NULL)
                        38 - filter(LNNVL("CFCO"."ACCT_NO" LIKE UPPER(:4||'%')))
                        43 - filter(UPPER("CPGR"."NAME") LIKE UPPER('%'||:1||'%'))
                        44 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                        45 - filter(LNNVL(UPPER("NAME") LIKE UPPER('%'||:2||'%')) AND LNNVL(UPPER("ABN") LIKE
                                    UPPER(:3||'%')))
                        46 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                        47 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                             filter("CFCO"."CPDE_ID" IS NOT NULL)
                        48 - filter(LNNVL("CFCO"."ACCT_NO" LIKE UPPER(:4||'%')))
                      • 23. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                        Jonathan Lewis
                        Dropbear67 wrote:
                        The plans are as follows;

                        10.2.0.3
                        Plan hash value: 1984058303
                        
                        ------------------------------------------------------------------------------------------------------
                        | Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
                        ------------------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT                |                    |   153 | 11628 |   179   (2)| 00:00:01 |
                        |   1 |  SORT UNIQUE                    |                    |   153 | 11628 |   178   (2)| 00:00:01 |
                        |*  2 |   TABLE ACCESS BY INDEX ROWID   | FN_ACCOUNT         |     1 |    14 |     1   (0)| 00:00:01 |
                        |   3 |    NESTED LOOPS                 |                    |   153 | 11628 |   177   (1)| 00:00:01 |
                        |   4 |     NESTED LOOPS                |                    |   826 | 51212 |    11   (0)| 00:00:01 |
                        |   5 |      NESTED LOOPS               |                    |   826 | 26432 |     3   (0)| 00:00:01 |
                        |   6 |       TABLE ACCESS FULL         | CL_PROVIDER_GROUP  |   116 |  2668 |     2   (0)| 00:00:01 |
                        |*  7 |       INDEX RANGE SCAN          | CPGP_UK            |     7 |    63 |     1   (0)| 00:00:01 |
                        |   8 |      TABLE ACCESS BY INDEX ROWID| CL_PROVIDER_DETAIL |     1 |    30 |     1   (0)| 00:00:01 |
                        |*  9 |       INDEX UNIQUE SCAN         | CPDE_PK            |     1 |       |     1   (0)| 00:00:01 |
                        |* 10 |     INDEX RANGE SCAN            | FACC_CPDE_FK_I     |    27 |       |     1   (0)| 00:00:01 |
                        ------------------------------------------------------------------------------------------------------
                        I wrote a blog demonstrating what I thought the issue was, and got a comment - it's a known bug: 14009271, NLJ prefetching used in 10g but not hinted, fixed in 12.1. (Your plan above show prefetching).

                        11g used your oriiginal hints to get the same plan but with NLJ batching, discarded the plan as not matching, and came up with a concatenation option.

                        Regards
                        Jonathan Lewis
                        • 24. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                          Dropbear67
                          Thanks Jonathan

                          I tried your underscore parameter in the text above in our 11.2.0.3 test database, and whilst it produced a different plan, it didn't seem to push our saved baseline plan into being used.

                          I guess we're looking at sticking with the rather 'manual' setting the optimizer_features_enabled to 10.2.0.3, capturing that plan and producing a baseline plan from that. Not ideal but at least I can get it to work..
                          • 25. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                            Jonathan Lewis
                            Dropbear67 wrote:
                            Thanks Jonathan

                            I tried your underscore parameter in the text above in our 11.2.0.3 test database, and whilst it produced a different plan, it didn't seem to push our saved baseline plan into being used.

                            I guess we're looking at sticking with the rather 'manual' setting the optimizer_features_enabled to 10.2.0.3, capturing that plan and producing a baseline plan from that. Not ideal but at least I can get it to work..
                            Just following up the bug number - there is a patch for 11.2.0.3 for Solaris, AIX and Linux

                            Regards
                            Jonathan Lewis
                            • 26. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                              Dom Brooks
                              I'm also not comfortable with the query produing a different SQL ID in the 11.2.0.3 environment (which made it difficult for me to locate the query in V$SQL - I loathe using SQL TEXT matching, especially in this environment where statements are long, complicated and similar)
                              Use the same mechanism that baselines use - V$SQL.EXACT_MATCHING_SIGNATURE
                              • 27. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                                Randolf Geist
                                Dropbear67 wrote:
                                On our 11.2.0.3 test database;

                                1. For my current session - set optimizer_features_enable to 10.2.0.3

                                2. Do an explain plan for the query in question - verify that it is producing the same plan as we are seeing in our current 10.2.0.3 production environment

                                3. Run the query using bind variables captured from the prod database.

                                4. Locate the query in v$SQL and verify the plan_hash_value I found while testing two separate queries that the SQL ID produced was different than the 10.2.0.3 environment. Why? I have no idea

                                5. Use dbms_spm.load_plans_from_cursor_cache('&SQL_ID') with the discovered SQL ID to produce a baseline plan from the 10.2.0.3 plan


                                I then set the optimizer_features_enable back to 11.2.0.3, flushed the shared pool (quick and dirty way for me to force a reparse) and yes, the explain plan indicated that it was using the stored baseline, and was indeed returning the 10.2.03 execution plan as expected.

                                So I'm not sure exactly why this approach worked, and not the approach from loading the plan via the SQL Tuning Sets . Perhaps it has something to do with this NLJ_PREFETCH which I must admit I have no idea or experience with.
                                I think the critical difference between these steps and using the SQL Tuning Set is that the hint list generated by 11.2.0.3, even when switching to OPTIMIZER_FEATURES_ENABLE = 10.2.0.3, includes the NLJ_PREFETCH hint that allows to reproduce the stored plan.

                                The original hint list included in the SQL Tuning Set doesn't include this hint, and 11.2.0.3 doesn't use the NLJ prefetching with that hint set, even with OPTIMIZER_FEATURES_ENABLE = 10.2.0.3, and therefore rejects to use the baseline (which is already known as a bug as mentioned above).

                                Note that I simply summarize here what Jonathan has already described in his blog post, and the bug description seems to fit very well, so you could request a one-off patch backport for your platform, if it is not yet available.

                                Randolf
                                • 28. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                                  Dropbear67
                                  Issue is a known bug: 14009271
                                  1 2 Previous Next