1 2 Previous Next 28 Replies Latest reply: Feb 14, 2013 5:07 PM by Dropbear67 RSS

    SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?

    Dropbear67
      Environment: 11.2.0.3 on HP-UX Itanium.

      We're preparing for an upgrade of our main production database from 10.2.0.3 to 11.2.0.3. We're using SQL Tuning Sets to capture execution plans from the 10G environment and load them into the upgraded (test) environment as SQL Plan Baselines. We've been using SQL Performance Analyzer to identify plans which have regressed from 10G to 11G. For these regressed plans we wish to use SPM to lock in the better 10G plan.

      We've been successful in locking in the 10G plans for those regressed queries where the 11g CBO "Cost" of the query is higher than it is in 10g, however we have some examples where SPA has shown a performance regression for queries (measured in terms of elapsed time, or buffer gets or CPU time), but the 11G explain plan has a lower "cost" as identified by the CBO. Ie: the 11G "cost" is lower, but the plan is worse.

      For these small number of queries, I have not been able to get the 11G optimiser to choose the 10G plans even though they are "fixed, enabled and accepted" within SPM. The optimiser consistently chooses what it considers its "lower cost" plan, even though it is a worst plan. Am I missing something here or should it be possible, with the use of SPM to have the optimiser chose the 10G plan, regardless of what its internal cost says?
      SQL> show parameter baseline
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE
        • 1. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
          Salman Qureshi
          Hi,
          According to this white paper, you would need to DISABLE the "bad" plan which is currently in use. Are you sure that "bad" plan is not present in baseline? If it is, then it will be used.

          http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

          Read page 7, point 4.

          Salman
          • 2. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
            Dropbear67
            Hi Salman

            Thank you for the reply. I'm pretty sure there was only one plan for that SQL ID as part of the SQL Tuning Set that came over.
            The only baselines we have in our 11g test databases are the ones we have loaded via SQL Tuning Sets. We have automatic baseline collection turned off.

            Do you know, if SPM does support fixing, and using a plan with a "higher" cost than the native 11g optimizer would by default create and use?
            ie: can we lock in a plan with a higher cost, and get the optimizer to chose that?
            • 3. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
              Salman Qureshi
              Hi,
              Thank you for the reply. I'm pretty sure there was only one plan for that SQL ID as part of the SQL Tuning Set that came over.
              But no one has stopped current optimizer to generate new (bad) plan for this SQL. So there is another plan which you can disable as mentioned in the whitepaper? I know optimizer_capture_sql_plan_baselines is FALSE, still can you just check if dba_sql_plan_baselines lists only one plan for this SQL>
              Do you know, if SPM does support fixing, and using a plan with a "higher" cost than the native 11g optimizer would by default create and use?
              ie: can we lock in a plan with a higher cost, and get the optimizer to chose that?
              Yes obviously SPM supports this, otherwise there is no benifit left for SPM because optimizer will always use least cost plan.

              Can you just flush the shared pool and see if it starts using your plan instead of generating a new one and use it?

              Salman
              • 4. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                Dropbear67
                Hi Again

                I've pretty much tested every combination I can find here.. I've run the query in the test 11g database to produce a row in V$SQL and V$SQL_PLAN in order to generate an execution plan.

                I've used the following command to then produce a second baseline plan
                SQL> variable cnt number;
                EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'cdz08vd97mf5m');
                I can verify that the plan has now been created with the following query
                  SELECT *
                 FROM dba_sql_plan_baselines
                 WHERE signature IN (
                   SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID');
                which returns two records...
                The first baseline plan which was the one imported from the 10G STS is set to Enabled-Yes, Accepted-Yes and Fixed-Yes.
                The second baseline plan which was the one just created (which i verified using dates) is set to Enabled-No, Accepted Yes, Fixed No

                I then do an
                explain plan for
                <my query>

                and I am getting the execution plan used in the second baseline plan (the one with enabled set to no - which is also the default the execution plan 11G was giving me anyway).

                Also the execution plan is NOT containing the text which says

                Used SQL Baseline Plan ID <blah> which I get have successfully enabled plans before..

                So in summary it appears as though the execution plans being produced from this query is not using the plans recorded in the SPM baseline plans, even though the query
                  SELECT *
                 FROM dba_sql_plan_baselines
                 WHERE signature IN (
                   SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID');
                tells me that it's a matching signature.


                As for verifying 100% whether there are only those plans in the database for that given SQL, the above query is really the only sure-fire way I can think of, of doing that..

                Edited by: Dropbear67 on Feb 6, 2013 10:15 PM
                • 5. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                  Salman Qureshi
                  Hi,
                  and I am getting the execution plan used in the second baseline plan (the one with enabled set to no - which is also the default the execution plan 11G was giving me >anyway).
                  I think i got your problem. Explain plan DOES NOT use plan from baseline and will always generate the new plan for this SQL. It does not mean that it will also use same generated plan which you are seeing in EXPLAIN PLAN. So you should not worry and should monitor your SQL at run time to see what plan it is actually using, and this should be the plan which you have loaded from 10g and made it enabled and accepted.

                  Salman
                  • 6. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                    Dropbear67
                    Hi Salman.

                    On the contrary I have seen EXPLAIN PLAN report that it is using baseline plans before when I've been testing using plans that have genuinely regressed in 11g.
                    SELECT sql_handle, plan_name, enabled, accepted, fixed 
                    FROM   dba_sql_plan_baselines
                    WHERE  sql_handle = 'SQL_190f3b76e66121ba';
                    
                    SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
                    ------------------------------ ------------------------------ --- --- ---
                    SQL_190f3b76e66121ba           SQL_PLAN_1k3tvfvm628dua24a6980 YES YES YES
                    
                    CARADM_PUBLIC>explain plan for
                      2  SELECT DISTINCT AADI.ASSU_ID ,
                            AADI.CODE
                    FROM    AC_AUTOMATIC_DIARY AADI
                    WHERE   EXISTS
                            (
                                    SELECT 1 FROM CL_VALID_INJURY_CODE CVIC WHERE CVIC.AADI_ID = AADI.AADI_ID
                            )  3    4    5    6    7    8  ;
                    
                    Explained.
                    
                    CARADM_PUBLIC>select * from table (dbms_xplan.display);
                    Plan hash value: 1102557324
                    
                    ----------------------------------------------------------------------------------------------------
                    | Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
                    ----------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT              |                    |   832 | 16640 |     5  (40)| 00:00:01 |
                    |   1 |  HASH UNIQUE                  |                    |   832 | 16640 |     5  (40)| 00:00:01 |
                    |   2 |   MERGE JOIN                  |                    |   832 | 16640 |     4  (25)| 00:00:01 |
                    |   3 |    TABLE ACCESS BY INDEX ROWID| AC_AUTOMATIC_DIARY |   328 |  5904 |     2   (0)| 00:00:01 |
                    |   4 |     INDEX FULL SCAN           | AADI_PK            |   328 |       |     1   (0)| 00:00:01 |
                    |*  5 |    SORT JOIN                  |                    |   832 |  1664 |     2  (50)| 00:00:01 |
                    |*  6 |     INDEX FULL SCAN           | CVIC_AADI_FK_I     |   832 |  1664 |     1   (0)| 00:00:01 |
                    ----------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       5 - access("CVIC"."AADI_ID"="AADI"."AADI_ID")
                           filter("CVIC"."AADI_ID"="AADI"."AADI_ID")
                       6 - filter("CVIC"."AADI_ID" IS NOT NULL)
                    
                    Note
                    -----
                       - SQL plan baseline "SQL_PLAN_1k3tvfvm628dua24a6980" used for this statement
                    Edited by: Dropbear67 on Feb 7, 2013 7:29 PM
                    • 7. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                      Salman Qureshi
                      Hi,
                      On the contrary I have seen EXPLAIN PLAN report that it is using baseline plans before when I've been testing using plans that have genuinely regressed in 11g.
                      As far as I remeber, this statement i got from an Oracle support personnel during an SR discussion. My apology as it is proven wrong.

                      During real time execution of the SQL statement, can you check which plan is in use?
                      Do all objects exist which are currently in your 10g execution plan (i.e. all indexes which are involved in this execution plan?)

                      Salman
                      • 8. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                        Dom Brooks
                        Also the execution plan is NOT containing the text which says
                        Used SQL Baseline Plan ID <blah> which I get have successfully enabled plans before..
                        This is the key point.
                        This may be due to the limitations of EXPLAIN PLAN.
                        For example, all binds are VARCHAR2 which can lead to issues with implicit datatype conversion.
                        Implicit datatype conversion could prevent a baselined plan from being reproducible.


                        The best way to check this would be by looking for implicit data type conversions in the predicates section.
                        This is just one possibility.

                        Regardless, do an SPM trace, you should see that the plan is not reproducible and so the optimizer rejects the baselines falls back on the lowest cost plan it can generate.
                        To do an SPM trace:
                        alter session set events 'trace[RDBMS.SQL_Plan_Management.*]'; 
                        ...
                        alter session set events 'trace[RDBMS.SQL_Plan_Management.*] off'; 
                        then check trace file.
                        • 9. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                          Dropbear67
                          Thanks all for your help so far..

                          It seems to be related to being unable to reproduce the saved plan? ... im having trouble following the SPM Trace files.. But have included the following.

                          Here is an example that 'works'. We have used the plan
                          SPM: statement found in SMB
                          SPM: fixed planId's of plan baseline are: 1576897796
                          SPM: using qksan to reproduce, cost and select accepted plan, sig = 15351896724095330333
                          SPM: plan reproducibility round 1 (plan outline + session OFE)
                          SPM: using qksan to reproduce accepted plan, planId = 1576897796
                            Bind List follows:
                          bind value pos=4 name=
                              bind(0x9fffffff7f1b77b0): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          bind value pos=3 name=
                              bind(0x9fffffff7f1b7818): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          bind value pos=2 name=
                              bind(0x9fffffff7f1b7880): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          bind value pos=1 name=
                              bind(0x9fffffff7f1b78e8): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          
                          SPM: plan reproducibility - session OFE = 11020003, hinted OFE = 10020003
                          SPM: planId in plan baseline = 1576897796, planId of reproduced plan = 1576897796
                          SPM: best cost so far = 327696.09, current accepted plan cost = 327696.09
                          SPM: re-parse to use selected accepted plan, planId = 1576897796
                          SPM: statement found in SMB
                          SPM: re-parsing to generate selected accepted plan,  planId = 1576897796
                          and here is the trace where we cannot get the plan to be used
                          SPM: statement found in SMB
                          SPM: fixed planId's of plan baseline are: 2347386768
                          SPM: using qksan to reproduce, cost and select accepted plan, sig = 3826239504587099358
                          SPM: plan reproducibility round 1 (plan outline + session OFE)
                          SPM: using qksan to reproduce accepted plan, planId = 2347386768
                            Bind List follows:
                          bind value pos=3 name=
                              bind(0x9fffffff7f0ff638): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          bind value pos=2 name=
                              bind(0x9fffffff7f0ff6a0): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          bind value pos=1 name=
                              bind(0x9fffffff7f0ff708): flags=3 typ=0 data=0x0000000000000000 len=0
                          
                          
                          SPM: plan reproducibility - session OFE = 11020003, hinted OFE = 10020003
                          SPM: planId in plan baseline = 2347386768, planId of reproduced plan = 3875564909
                          ------- START SPM Plan Dump -------
                          SPM: failed to reproduce the plan using the following info:
                          I think the key is the following line
                          SPM: planId in plan baseline = 2347386768, planId of reproduced plan = 3875564909
                          Can anyone explain exactly what that is saying? the stored plan id in the SPM is 2347386768 but when it goes to use the hints etc stored in the SPM it comes up with a
                          different plan id 3875564909 and is therefore saying the plan is non-reproducible?
                          • 10. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                            Jonathan Lewis
                            Dropbear67 wrote:
                            SPM: plan reproducibility - session OFE = 11020003, hinted OFE = 10020003
                            SPM: planId in plan baseline = 2347386768, planId of reproduced plan = 3875564909
                            ------- START SPM Plan Dump -------
                            SPM: failed to reproduce the plan using the following info:
                            Can anyone explain exactly what that is saying? the stored plan id in the SPM is 2347386768 but when it goes to use the hints etc stored in the SPM it comes up with a
                            different plan id 3875564909 and is therefore saying the plan is non-reproducible?
                            This is telling you that the set of hints stored as the baseline do not reproduce the plan that they originally produced.
                            The text following the "failed to reproduce" should report:
                            a) The stored set of hints
                            b) The resulting execution plan including
                            c) The set of hints that would become the SQL baseline for the resulting execution plan.

                            If you compare (a) and (c) this may give you some clue about what's missing.
                            If you compare (b) and the plan you actually get when running with OFE = 10.2.0.3 this may give you further clues.

                            I have come across cases where the set of hints generated by one version of Oracle is not sufficient to reproduce the plan - but I don't think I've seen it happen in your circumstances yet.


                            Regards
                            Jonathan Lewis
                            • 11. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                              Dom Brooks
                              Following on from what Jonathan has said...

                              You should be able get the hints from the baselined plan using DBMS_XPLAN.DISPLAY_AWR with a format of '+OUTLINE' (which is easier than extracting directly from sqlobj$data.comp_data).

                              SPM plan ids should map to plan_hash_2 in v$sql_plan.other_xml not to the v$sql(_plan).plan_hash_value.

                              With that in mind, it would be good to see what plan you get if you paste these directly into your sql statement and execute - is this the plan id 3875564909?

                              Also, do you have the execution plan that you wanted to lock in?

                              If you can includes these hints and the plans including predicates, then it may indicate what the issue is.


                              In addition, one of the strengths and weaknesses of SPM is that the plan has to be reproducible otherwise the baselined plan is rejected.
                              If you needed to urgently influence an execution plan but were running into the issue that you describe, then using the SQL Profile mechanism to apply specifc outline style hints might be a good alternative. There is an Oracle supported script to do this - see COE_XFR_SQL_PROFILE.SQL script via Oracle Support doc id 215187.1
                              • 12. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                                Dropbear67
                                Thanks again guys.. It worries me that we need to go to these lengths to do what we thought was going to be a generic procedure to ensure against plan changes during our upgrade.

                                Basically we're about to upgrade our main production OLTP DB from 10.2 to 11.2. We wanted to use SPM to provide 'insurance', allowing us to quickly switch back to a saved baselined plan, if required. We've done a fair a bit of work in building up our SQL Tuning Sets in the current 10.2 production system as we go. We've used SPA to run tests against upgraded copies of our production database etc, so we can see where the plans are expected to change (thankfully, not many of them).

                                I was hoping it was going to be as simple as identify the SQL_ID, find the baseline plan name from that, enable and fix it ... I guess it won't necessarily be.
                                • 13. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                                  Dom Brooks
                                  In general, it IS a simple, generic approach that does work.

                                  There are undoubtedly circumstances where, for some reason, it doesn't work.

                                  That is why finding out why it didn't work is crucial - so you and we can see exactly why it didn't in this particular case rather than just writing off a feature as not working when it does.

                                  I've known DBAs who were much more comfortable with the COE_XFR_SQL_PROFILE approach of fixing plans, precisely because a) either they didn't fully understand the baseline mechanism and/or b) because the COE method doesn't have the added complexity of enforcing a specific plan so more reliable / immune to complications.
                                  And in the latter, they had a point.

                                  As long as you have the information in AWR or in SQL Tuning Sets, you can use both approaches to quickly try to fix a previous execution plan.

                                  But I would urge you to help us help you understand why baselines did not work for this statement.
                                  • 14. Re: SQL PLAN MANAGEMENT - Locking in a more 'expensive', better plan.?
                                    Dropbear67
                                    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.

                                    But first some background. Our test databases should be identical in terms of schemas. That is we have a 10G prod database. From that we clone it to two test databases. One we leave at 10G, the other we upgrade to 11G. So there should be no schema differences between the 11G and 10G databases.

                                    h5.
                                    *** 2013-02-12 15:41:27.671
                                    SPM: statement found in SMB
                                    
                                    *** 2013-02-12 15:41:27.881
                                    SPM: fixed planId's of plan baseline are: 386463207
                                    SPM: using qksan to reproduce, cost and select accepted plan, sig = 17844158604836855983
                                    SPM: plan reproducibility round 1 (plan outline + session OFE)
                                    SPM: using qksan to reproduce accepted plan, planId = 386463207
                                    SPM: plan reproducibility - session OFE = 11020003, hinted OFE = 10020003
                                    SPM: planId in plan baseline = 386463207, planId of reproduced plan = 3080367404
                                    ------- START SPM Plan Dump -------
                                    SPM: failed to reproduce the plan using the following info:
                                      parse_schema name        : CARADM
                                      plan_baseline signature  : 17844158604836855983
                                      plan_baseline plan_id    : 386463207
                                      plan_baseline hintset    :
                                        hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
                                        hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                                        hint num  3 len 36 text: OPT_PARAM('_always_anti_join' 'off')
                                        hint num  4 len 36 text: OPT_PARAM('_always_semi_join' 'off')
                                        hint num  5 len 40 text: OPT_PARAM('_index_join_enabled' 'false')
                                        hint num  6 len 43 text: OPT_PARAM('_optim_peek_user_binds' 'false')
                                        hint num  7 len 49 text: OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
                                        hint num  8 len 39 text: OPT_PARAM('optimizer_index_cost_adj' 1)
                                        hint num  9 len 40 text: OPT_PARAM('optimizer_index_caching' 100)
                                        hint num 10 len 10 text: FIRST_ROWS
                                        hint num 11 len 29 text: OUTLINE_LEAF(@"SEL$EE94F965")
                                        hint num 12 len 22 text: MERGE(@"SEL$9E43CB6E")
                                        hint num 13 len 17 text: OUTLINE(@"SEL$4")
                                        hint num 14 len 24 text: OUTLINE(@"SEL$9E43CB6E")
                                        hint num 15 len 22 text: MERGE(@"SEL$58A6D7F6")
                                        hint num 16 len 17 text: OUTLINE(@"SEL$3")
                                        hint num 17 len 24 text: OUTLINE(@"SEL$58A6D7F6")
                                        hint num 18 len 15 text: MERGE(@"SEL$1")
                                        hint num 19 len 17 text: OUTLINE(@"SEL$2")
                                        hint num 20 len 17 text: OUTLINE(@"SEL$1")
                                        hint num 21 len 36 text: FULL(@"SEL$EE94F965" "CPGR"@"SEL$1")
                                        hint num 22 len 153 text: INDEX(@"SEL$EE94F965" "CPGP"@"SEL$1" ("CL_PROVIDER_GROUP_PERIOD"."CPGR_ID" "CL_PROVIDER_GROUP_PERIOD"."CPDE_ID" "CL_PRO
                                    VIDER_GROUP_PERIOD"."START_DATE"))
                                        hint num 23 len 77 text: INDEX_RS_ASC(@"SEL$EE94F965" "CPDE"@"SEL$2" ("CL_PROVIDER_DETAIL"."CPDE_ID"))
                                        hint num 24 len 62 text: INDEX(@"SEL$EE94F965" "CFCO"@"SEL$3" ("FN_ACCOUNT"."CPDE_ID"))
                                        hint num 25 len 84 text: LEADING(@"SEL$EE94F965" "CPGR"@"SEL$1" "CPGP"@"SEL$1" "CPDE"@"SEL$2" "CFCO"@"SEL$3")
                                        hint num 26 len 38 text: USE_NL(@"SEL$EE94F965" "CPGP"@"SEL$1")
                                        hint num 27 len 38 text: USE_NL(@"SEL$EE94F965" "CPDE"@"SEL$2")
                                        hint num 28 len 38 text: USE_NL(@"SEL$EE94F965" "CFCO"@"SEL$3")
                                    SPM: generated non-matching plan:
                                    ----- Explain Plan Dump -----
                                    ----- Plan Table -----
                                    
                                    ============
                                    Plan Table
                                    ============
                                    -------------------------------------------------------------+-----------------------------------+
                                    | Id  | Operation                        | Name              | Rows  | Bytes | Cost  | Time      |
                                    -------------------------------------------------------------+-----------------------------------+
                                    | 0   | SELECT STATEMENT                 |                   |       |       |    22 |           |
                                    | 1   |  SORT UNIQUE                     |                   |    41 |  3116 |    21 |  00:00:01 |
                                    | 2   |   NESTED LOOPS                   |                   |       |       |       |           |
                                    | 3   |    NESTED LOOPS                  |                   |    41 |  3116 |    20 |  00:00:01 |
                                    | 4   |     NESTED LOOPS                 |                   |   826 |   50K |    11 |  00:00:01 |
                                    | 5   |      NESTED LOOPS                |                   |   826 |   26K |     3 |  00:00:01 |
                                    | 6   |       TABLE ACCESS FULL          | CL_PROVIDER_GROUP |   116 |  2668 |     2 |  00:00:01 |
                                    | 7   |       INDEX RANGE SCAN           | CPGP_UK           |     7 |    63 |     1 |  00:00:01 |
                                    | 8   |      TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_DETAIL|     1 |    30 |     1 |  00:00:01 |
                                    | 9   |       INDEX UNIQUE SCAN          | CPDE_PK           |     1 |       |     1 |  00:00:01 |
                                    | 10  |     INDEX RANGE SCAN             | FACC_CPDE_FK_I    |     1 |       |     1 |  00:00:01 |
                                    | 11  |    TABLE ACCESS BY INDEX ROWID   | FN_ACCOUNT        |     1 |    14 |     1 |  00:00:01 |
                                    -------------------------------------------------------------+-----------------------------------+
                                    Predicate Information:
                                    ----------------------
                                    7 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                                    9 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                                    10 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                                    10 - filter("CFCO"."CPDE_ID" IS NOT NULL)
                                    11 - filter((UPPER("CPGR"."NAME") LIKE UPPER('%'||:1||'%') OR UPPER("NAME") LIKE UPPER('%'||:2||'%') OR UPPER("ABN") LIKE UPPER(:3||'%') OR "CFCO"."A
                                    CCT_NO" LIKE UPPER(:4||'%')))
                                    
                                    Content of other_xml column
                                    ===========================
                                      db_version     : 11.2.0.3
                                      parse_schema   : CARADM
                                      plan_hash      : 524147661
                                      plan_hash_2    : 3080367404
                                      Outline Data:
                                      /*+
                                        BEGIN_OUTLINE_DATA
                                          IGNORE_OPTIM_EMBEDDED_HINTS
                                          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
                                          DB_VERSION('11.2.0.3')
                                          OPT_PARAM('_always_anti_join' 'off')
                                          OPT_PARAM('_always_semi_join' 'off')
                                          OPT_PARAM('_index_join_enabled' 'false')
                                          OPT_PARAM('_optim_peek_user_binds' 'false')
                                          OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
                                          OPT_PARAM('optimizer_index_cost_adj' 1)
                                          OPT_PARAM('optimizer_index_caching' 100)
                                          FIRST_ROWS
                                          OUTLINE_LEAF(@"SEL$EE94F965")
                                          MERGE(@"SEL$9E43CB6E")
                                          OUTLINE(@"SEL$4")
                                          OUTLINE(@"SEL$9E43CB6E")
                                          MERGE(@"SEL$58A6D7F6")
                                          OUTLINE(@"SEL$3")
                                          OUTLINE(@"SEL$58A6D7F6")
                                          MERGE(@"SEL$1")
                                          OUTLINE(@"SEL$2")
                                          OUTLINE(@"SEL$1")
                                          FULL(@"SEL$EE94F965" "CPGR"@"SEL$1")
                                          INDEX(@"SEL$EE94F965" "CPGP"@"SEL$1" ("CL_PROVIDER_GROUP_PERIOD"."CPGR_ID" "CL_PROVIDER_GROUP_PERIOD"."CPDE_ID" "CL_PROVIDER_GROUP_PERIOD"."STA
                                    RT_DATE"))
                                          INDEX_RS_ASC(@"SEL$EE94F965" "CPDE"@"SEL$2" ("CL_PROVIDER_DETAIL"."CPDE_ID"))
                                          INDEX(@"SEL$EE94F965" "CFCO"@"SEL$3" ("FN_ACCOUNT"."CPDE_ID"))
                                          LEADING(@"SEL$EE94F965" "CPGR"@"SEL$1" "CPGP"@"SEL$1" "CPDE"@"SEL$2" "CFCO"@"SEL$3")
                                          USE_NL(@"SEL$EE94F965" "CPGP"@"SEL$1")
                                          USE_NL(@"SEL$EE94F965" "CPDE"@"SEL$2")
                                          USE_NL(@"SEL$EE94F965" "CFCO"@"SEL$3")
                                          NLJ_BATCHING(@"SEL$EE94F965" "CFCO"@"SEL$3")
                                        END_OUTLINE_DATA
                                    
                                      */
                                    
                                    ------- END SPM Plan Dump -------
                                    SPM: plan reproducibility round 1 (plan outline only)
                                    SPM: using qksan to reproduce accepted plan, planId = 386463207
                                    SPM: planId in plan baseline = 386463207, planId of reproduced plan = 4009937738
                                    ------- START SPM Plan Dump -------
                                    SPM: failed to reproduce the plan using the following info:
                                      parse_schema name        : CARADM
                                      plan_baseline signature  : 17844158604836855983
                                      plan_baseline plan_id    : 386463207
                                      plan_baseline hintset    :
                                        hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
                                        hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                                        hint num  3 len 36 text: OPT_PARAM('_always_anti_join' 'off')
                                        hint num  4 len 36 text: OPT_PARAM('_always_semi_join' 'off')
                                        hint num  5 len 40 text: OPT_PARAM('_index_join_enabled' 'false')
                                        hint num  6 len 43 text: OPT_PARAM('_optim_peek_user_binds' 'false')
                                        hint num  7 len 49 text: OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
                                        hint num  8 len 39 text: OPT_PARAM('optimizer_index_cost_adj' 1)
                                        hint num  9 len 40 text: OPT_PARAM('optimizer_index_caching' 100)
                                        hint num 10 len 10 text: FIRST_ROWS
                                        hint num 11 len 29 text: OUTLINE_LEAF(@"SEL$EE94F965")
                                        hint num 12 len 22 text: MERGE(@"SEL$9E43CB6E")
                                        hint num 13 len 17 text: OUTLINE(@"SEL$4")
                                        hint num 14 len 24 text: OUTLINE(@"SEL$9E43CB6E")
                                        hint num 15 len 22 text: MERGE(@"SEL$58A6D7F6")
                                        hint num 16 len 17 text: OUTLINE(@"SEL$3")
                                        hint num 17 len 24 text: OUTLINE(@"SEL$58A6D7F6")
                                        hint num 18 len 15 text: MERGE(@"SEL$1")
                                        hint num 19 len 17 text: OUTLINE(@"SEL$2")
                                        hint num 20 len 17 text: OUTLINE(@"SEL$1")
                                        hint num 21 len 36 text: FULL(@"SEL$EE94F965" "CPGR"@"SEL$1")
                                        hint num 22 len 153 text: INDEX(@"SEL$EE94F965" "CPGP"@"SEL$1" ("CL_PROVIDER_GROUP_PERIOD"."CPGR_ID" "CL_PROVIDER_GROUP_PERIOD"."CPDE_ID" "CL_PRO
                                    VIDER_GROUP_PERIOD"."START_DATE"))
                                        hint num 23 len 77 text: INDEX_RS_ASC(@"SEL$EE94F965" "CPDE"@"SEL$2" ("CL_PROVIDER_DETAIL"."CPDE_ID"))
                                        hint num 24 len 62 text: INDEX(@"SEL$EE94F965" "CFCO"@"SEL$3" ("FN_ACCOUNT"."CPDE_ID"))
                                        hint num 25 len 84 text: LEADING(@"SEL$EE94F965" "CPGR"@"SEL$1" "CPGP"@"SEL$1" "CPDE"@"SEL$2" "CFCO"@"SEL$3")
                                        hint num 26 len 38 text: USE_NL(@"SEL$EE94F965" "CPGP"@"SEL$1")
                                        hint num 27 len 38 text: USE_NL(@"SEL$EE94F965" "CPDE"@"SEL$2")
                                        hint num 28 len 38 text: USE_NL(@"SEL$EE94F965" "CFCO"@"SEL$3")
                                    SPM: generated non-matching plan:
                                    ----- Explain Plan Dump -----
                                    ----- Plan Table -----
                                    
                                    ============
                                    Plan Table
                                    ============
                                    ------------------------------------------------------------+-----------------------------------+
                                    | Id  | Operation                       | Name              | Rows  | Bytes | Cost  | Time      |
                                    ------------------------------------------------------------+-----------------------------------+
                                    | 0   | SELECT STATEMENT                |                   |       |       |   179 |           |
                                    | 1   |  SORT UNIQUE                    |                   |   153 |   11K |   178 |  00:00:01 |
                                    | 2   |   NESTED LOOPS                  |                   |   153 |   11K |   177 |  00:00:01 |
                                    | 3   |    NESTED LOOPS                 |                   |   826 |   50K |    11 |  00:00:01 |
                                    | 4   |     NESTED LOOPS                |                   |   826 |   26K |     3 |  00:00:01 |
                                    | 5   |      TABLE ACCESS FULL          | CL_PROVIDER_GROUP |   116 |  2668 |     2 |  00:00:01 |
                                    | 6   |      INDEX RANGE SCAN           | CPGP_UK           |     7 |    63 |     1 |  00:00:01 |
                                    | 7   |     TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_DETAIL|     1 |    30 |     1 |  00:00:01 |
                                    | 8   |      INDEX UNIQUE SCAN          | CPDE_PK           |     1 |       |     1 |  00:00:01 |
                                    | 9   |    TABLE ACCESS BY INDEX ROWID  | FN_ACCOUNT        |     1 |    14 |     1 |  00:00:01 |
                                    | 10  |     INDEX RANGE SCAN            | FACC_CPDE_FK_I    |    27 |       |     1 |  00:00:01 |
                                    ------------------------------------------------------------+-----------------------------------+
                                    Predicate Information:
                                    ----------------------
                                    6 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                                    8 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                                    9 - 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||'%')))
                                    10 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                                    10 - filter("CFCO"."CPDE_ID" IS NOT NULL)
                                    
                                    Content of other_xml column
                                    ===========================
                                      db_version     : 11.2.0.3
                                      parse_schema   : CARADM
                                      plan_hash      : 3311216257
                                      plan_hash_2    : 4009937738
                                      Outline Data:
                                      /*+
                                        BEGIN_OUTLINE_DATA
                                          IGNORE_OPTIM_EMBEDDED_HINTS
                                          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                                          DB_VERSION('11.2.0.3')
                                          OPT_PARAM('_always_anti_join' 'off')
                                          OPT_PARAM('_always_semi_join' 'off')
                                          OPT_PARAM('_index_join_enabled' 'false')
                                          OPT_PARAM('_optim_peek_user_binds' 'false')
                                          OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
                                          OPT_PARAM('optimizer_index_cost_adj' 1)
                                          OPT_PARAM('optimizer_index_caching' 100)
                                          FIRST_ROWS
                                          OUTLINE_LEAF(@"SEL$EE94F965")
                                          MERGE(@"SEL$9E43CB6E")
                                          OUTLINE(@"SEL$4")
                                          OUTLINE(@"SEL$9E43CB6E")
                                          MERGE(@"SEL$58A6D7F6")
                                          OUTLINE(@"SEL$3")
                                          OUTLINE(@"SEL$58A6D7F6")
                                          MERGE(@"SEL$1")
                                          OUTLINE(@"SEL$2")
                                          OUTLINE(@"SEL$1")
                                          FULL(@"SEL$EE94F965" "CPGR"@"SEL$1")
                                          INDEX(@"SEL$EE94F965" "CPGP"@"SEL$1" ("CL_PROVIDER_GROUP_PERIOD"."CPGR_ID" "CL_PROVIDER_GROUP_PERIOD"."CPDE_ID" "CL_PROVIDER_GROUP_PERIOD"."STA
                                    RT_DATE"))
                                          INDEX_RS_ASC(@"SEL$EE94F965" "CPDE"@"SEL$2" ("CL_PROVIDER_DETAIL"."CPDE_ID"))
                                          INDEX_RS_ASC(@"SEL$EE94F965" "CFCO"@"SEL$3" ("FN_ACCOUNT"."CPDE_ID"))
                                          LEADING(@"SEL$EE94F965" "CPGR"@"SEL$1" "CPGP"@"SEL$1" "CPDE"@"SEL$2" "CFCO"@"SEL$3")
                                          USE_NL(@"SEL$EE94F965" "CPGP"@"SEL$1")
                                          USE_NL(@"SEL$EE94F965" "CPDE"@"SEL$2")
                                          USE_NL(@"SEL$EE94F965" "CFCO"@"SEL$3")
                                        END_OUTLINE_DATA
                                      */
                                    
                                    ------- END SPM Plan Dump -------
                                    SPM: plan reproducibility round 2 (hinted OFE only)
                                    SPM: using qksan to reproduce accepted plan, planId = 386463207
                                    SPM: planId in plan baseline = 386463207, planId of reproduced plan = 4009937738
                                    ------- START SPM Plan Dump -------
                                    SPM: failed to reproduce the plan using the following info:
                                      parse_schema name        : CARADM
                                      plan_baseline signature  : 17844158604836855983
                                      plan_baseline plan_id    : 386463207
                                      plan_baseline hintset    :
                                        hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                                    SPM: generated non-matching plan:
                                    ----- Explain Plan Dump -----
                                    ----- Plan Table -----
                                    
                                    ============
                                    Plan Table
                                    ============
                                    ------------------------------------------------------------+-----------------------------------+
                                    | Id  | Operation                       | Name              | Rows  | Bytes | Cost  | Time      |
                                    ------------------------------------------------------------+-----------------------------------+
                                    | 0   | SELECT STATEMENT                |                   |       |       |   179 |           |
                                    | 1   |  SORT UNIQUE                    |                   |   153 |   11K |   178 |  00:00:01 |
                                    | 2   |   NESTED LOOPS                  |                   |   153 |   11K |   177 |  00:00:01 |
                                    | 3   |    NESTED LOOPS                 |                   |   826 |   50K |    11 |  00:00:01 |
                                    | 4   |     NESTED LOOPS                |                   |   826 |   26K |     3 |  00:00:01 |
                                    | 5   |      TABLE ACCESS FULL          | CL_PROVIDER_GROUP |   116 |  2668 |     2 |  00:00:01 |
                                    | 6   |      INDEX RANGE SCAN           | CPGP_UK           |     7 |    63 |     1 |  00:00:01 |
                                    | 7   |     TABLE ACCESS BY INDEX ROWID | CL_PROVIDER_DETAIL|     1 |    30 |     1 |  00:00:01 |
                                    | 8   |      INDEX UNIQUE SCAN          | CPDE_PK           |     1 |       |     1 |  00:00:01 |
                                    | 9   |    TABLE ACCESS BY INDEX ROWID  | FN_ACCOUNT        |     1 |    14 |     1 |  00:00:01 |
                                    | 10  |     INDEX RANGE SCAN            | FACC_CPDE_FK_I    |    27 |       |     1 |  00:00:01 |
                                    ------------------------------------------------------------+-----------------------------------+
                                    Predicate Information:
                                    ----------------------
                                    6 - access("CPGR"."CPGR_ID"="CPGP"."CPGR_ID")
                                    8 - access("CPDE"."CPDE_ID"="CPGP"."CPDE_ID")
                                    9 - 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||'%')))
                                    10 - access("CPDE"."CPDE_ID"="CFCO"."CPDE_ID")
                                    10 - filter("CFCO"."CPDE_ID" IS NOT NULL)
                                    
                                    Content of other_xml column
                                    ===========================
                                      db_version     : 11.2.0.3
                                      parse_schema   : CARADM
                                      plan_hash      : 3311216257
                                      plan_hash_2    : 4009937738
                                      Outline Data:
                                      /*+
                                        BEGIN_OUTLINE_DATA
                                          IGNORE_OPTIM_EMBEDDED_HINTS
                                          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
                                          DB_VERSION('11.2.0.3')
                                          OPT_PARAM('optimizer_index_cost_adj' 1)
                                          OPT_PARAM('optimizer_index_caching' 100)
                                          FIRST_ROWS
                                          OUTLINE_LEAF(@"SEL$EE94F965")
                                          MERGE(@"SEL$9E43CB6E")
                                          OUTLINE(@"SEL$4")
                                          OUTLINE(@"SEL$9E43CB6E")
                                          MERGE(@"SEL$58A6D7F6")
                                          OUTLINE(@"SEL$3")
                                          OUTLINE(@"SEL$58A6D7F6")
                                          MERGE(@"SEL$1")
                                          OUTLINE(@"SEL$2")
                                          OUTLINE(@"SEL$1")
                                          FULL(@"SEL$EE94F965" "CPGR"@"SEL$1")
                                          INDEX(@"SEL$EE94F965" "CPGP"@"SEL$1" ("CL_PROVIDER_GROUP_PERIOD"."CPGR_ID" "CL_PROVIDER_GROUP_PERIOD"."CPDE_ID" "CL_PROVIDER_GROUP_PERIOD"."STA
                                    RT_DATE"))
                                          INDEX_RS_ASC(@"SEL$EE94F965" "CPDE"@"SEL$2" ("CL_PROVIDER_DETAIL"."CPDE_ID"))
                                          INDEX_RS_ASC(@"SEL$EE94F965" "CFCO"@"SEL$3" ("FN_ACCOUNT"."CPDE_ID"))
                                          LEADING(@"SEL$EE94F965" "CPGR"@"SEL$1" "CPGP"@"SEL$1" "CPDE"@"SEL$2" "CFCO"@"SEL$3")
                                          USE_NL(@"SEL$EE94F965" "CPGP"@"SEL$1")
                                          USE_NL(@"SEL$EE94F965" "CPDE"@"SEL$2")
                                          USE_NL(@"SEL$EE94F965" "CFCO"@"SEL$3")
                                        END_OUTLINE_DATA
                                      */
                                    
                                    ------- END SPM Plan Dump -------
                                    SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3838063866
                                    SPM: kkopmCheckSmbUpdate (enter) xscP=0x9fffffff7f3c8cc8, pmExCtx=0xc000000c8bbade48, ciP=0xc000000c872f95c0, dtCtx=0x6000000000054c30
                                    apologies once again for the huge post.
                                    1 2 Previous Next