This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Feb 14, 2013 3:07 PM by Dropbear67 RSS

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

Dropbear67 Newbie
Currently Being Moderated
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.?
    SalmanQureshi Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.?
    SalmanQureshi Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.?
    SalmanQureshi Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.?
    SalmanQureshi Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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