8 Replies Latest reply on Jan 11, 2014 12:29 PM by Dom Brooks

    SQL Baseline multiple child cursors

    user13454469

      Hello all,

       

       

      I had a question with regards to this behaviour.  We are on 11.2.0.3 with ACS turned off and bind_peeking off(_optim_peek_user_binds=false),

      i am trying to tune a SQL statement and i found a plan that seems to be good as the buffer read is almost 99% less than the original plan.

      Original plan buffer read is about 2000, while the new plan that i want is about 15 buffer read(uses a different index)...please keep

      in mind that this SQL is executed 100's of time per sec(cannot change this, thanks to SAP)...

       

       

       

      but my problem is when i create a SQL Baseline for it, it seems to pickup the plan as i have the baseline....

      but the problem is with the # of child cursors that are created...they are 10's of them and maybe more if i had that run for more time...

      for now i have deleted/droped the baseline....i do not get that problem if baseline is in place....

       

       

       

      Is this somet kind of bug i am hitting or this is how its suppose to behave?...looking at v$sql_shared_cursors the one thing that is

      common is USER_BIND_PEEK_MISMATCH=Y...

       

       

       

      SQL Looks like this...

      SELECT /*+ FIRST_ROWS (1)  */ * FROM TABLE_NAME

      WHERE COL1 =:A0 AND COL2 = :A1

      AND COL3 = :A2 AND COL4 =:A3 AND COL5 = :A4 AND ROWNUM <= :A5

       

      without the baseline or profile in place, everything seems to be okay...i only have one child

       

       

      15:13:56 system@db> @perf_child_mismatch

      Enter value for sql_id:89kaweb8s0k2

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 0

      USER_BIND_PEEK_MISMATCH        = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 0

      --------------------------------------------------

       

       

      PL/SQL procedure successfully completed.

       

       

      Elapsed: 00:00:00.37

       

       

      with SQL BASELINE IN i get multiple cursors...as below...same goes when i create a sql profile...

      it seems like for almost every new bind that is passed it a new cursor is created when baseline or profile is in place...

       

      13:44:15 system@db> @perf_child_mismatch

      Enter value for sql_id: 89kaweb8s0k2

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 3

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 4

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 5

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 6

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 7

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 8

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 9

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 10

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 11

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 12

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 13

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 15

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 16

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 17

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 19

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 20

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 21

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 22

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 23

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 24

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 28

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 30

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 31

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 32

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 33

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 34

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 36

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 38

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 39

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 40

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 41

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 42

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 43

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 45

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 46

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 47

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 48

      USER_BIND_PEEK_MISMATCH        = Y

      STB_OBJECT_MISMATCH            = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 49

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 50

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 51

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

      SQL_ID                         = 89kaweb8s0k2

      CHILD_NUMBER                   = 52

      USER_BIND_PEEK_MISMATCH        = Y

      PURGED_CURSOR                  = Y

      --------------------------------------------------

       

       

      PL/SQL procedure successfully completed.

       

       

      13:44:25 system@db>

        • 1. Re: SQL Baseline multiple child cursors
          Dom Brooks

          How have you turned off ACS?

           

          There are three related parameters:

          _optimizer_adaptive_cursor_sharing

          _optimizer_extended_cursor_sharing

          _optimizer_extended_cursor_sharing_rel

           

          If you've not touched the last two parameters then might bug 13456573 fit?

           

          If it was this issue, it would be an interesting exercise to see if it was also fixable by using a SQL Patch to inject a /*+ NO_BIND_AWARE */ hint in supplement the baseline.

          • 2. Re: SQL Baseline multiple child cursors
            user13454469

            I have 2 out of 3 values you suggested, turned off...

             

            _optimizer_extended_cursor_sharing_rel       |NONE            

            _optimizer_adaptive_cursor_sharing           |FALSE         

             

            for me to hit  bug 13456573 dose all 3 of them needs to be turend off?

            • 3. Re: SQL Baseline multiple child cursors
              Dom Brooks

              Actually the bug suggests that the reason in V$SQL_SHARED_CURSOR would be BIND_EQUIV_FAILURE so perhaps not related.

               

              Can you confirm for the cursors generated that _optim_peek_user_binds is actually false.

              If you generate the outline for one of the cursors via DBMS_XPLAN.DISPLAY_CURSOR it should be included in the full outline hints.

               

              Otherwise it does it make sense that bind peeking is off but you're still getting a bind peek mismatch?

               

              PURGED_CURSOR can indicate a couple of things including parse errors.

              I would also check the alert log for any ORA-00600s and in addition think about running an SPM trace or a 10053 trace.

               

              Raise it with Oracle Support.

              • 4. Re: SQL Baseline multiple child cursors
                user13454469

                yep looks like optim_peek_user_binds is set to false looking at below....this is when everything is good(no baseline is in place)....

                 

                Outline Data

                -------------

                 

                 

                  /*+

                      BEGIN_OUTLINE_DATA

                      IGNORE_OPTIM_EMBEDDED_HINTS

                      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

                      DB_VERSION('11.2.0.3')

                      OPT_PARAM('query_rewrite_enabled' 'false')

                      OPT_PARAM('_optim_peek_user_binds' 'false')

                      OPT_PARAM('optimizer_dynamic_sampling' 6)

                      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

                      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')

                      OPT_PARAM('_optimizer_use_feedback' 'false')

                      OPT_PARAM('star_transformation_enabled' 'true')

                      OPT_PARAM('_fix_control' '5705630:1 6626018:1 6440977:1 6972291:1 6399597:1 6430500:1

                              5099019:1 9495669:1 9196440:1 8937971:1 6055658:0 13077335:1')

                      FIRST_ROWS(1)

                      OUTLINE_LEAF(@"SEL$1")

                 

                But when baseline in place, i do not see that info anymore...i see below...sounds like that maybe a problem? 

                 

                IGNORE_OPTIM_EMBEDDED_HINTS

                OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

                DB_VERSION('11.2.0.3')

                OPT_PARAM('star_transformation_enabled' 'true')

                OPT_PARAM('_fix_control' '5705630:1 6626018:1 6440977:1 6972291:1 6399597:1 6430500:1 5099019:1 9495669:1 9196440:1 8937971:1 13077335:1')

                ALL_ROWS

                OUTLINE_LEAF(@"SEL$1")

                • 5. Re: SQL Baseline multiple child cursors
                  Dom Brooks

                  Interesting. Very interesting. Further investigation required.

                  So, the ACS and bind peeking parameters are still set to off at a system parameter level or a session level?

                   

                  Trying to rationalise it, I suppose that a baseline is there to enforce a particular plan.

                  And baselines are independent of ACS, etc.

                  If a particular parameter setting is not relevant to the plan, then perhaps the rationale is that the opt_param hint is not required in the baseline so you can change such parameter settings independently of your baseline?

                   

                  On the other hand, fix controls and star_transformation_enabled settings would be important inputs to getting a particular plan.

                  I'm not 100% convinced.

                  I've seen this with the parallel hint before as well in baselines (or rather not being in the baselined plan).

                   

                  As mentioned, you could use a SQL_PATCH to inject this additional behaviour?

                   

                  That seems to be the best immediate way to get the best of both worlds - your baselined plan + the SQL_PATCH to inject additional settings to minimise the child cursor behaviour.

                   

                  Have you raised an SR with Oracle Support?

                  • 6. Re: SQL Baseline multiple child cursors
                    user13454469

                    yep ACS and bind peeking is turned off at system level....

                     

                    My issue was resolved and here is what i did....as i found out quite a few of those hints( OPT_PARAM) were missing from the original baseline, so i created another baseline which had all those OPT_PARAM hints in it and after that multiple child cursors were not created....

                     

                    my understanding is that as OPT_PARAM('_optim_peek_user_binds' 'false') was NOT set in the original baseline and that MIGHT have caused it to go crazy with creating all those child cursor but once i had OPT_PARAM('_optim_peek_user_binds' 'false') in the baseline NO more child cursors were created...hope it make sense....

                     

                    on a side note you mentioned try SQL Patch to inject a /*+ NO_BIND_AWARE */ hint ...i know i could have created the SQL Patch but would it works has i have bind peeking already turned off at system level?

                    • 7. Re: SQL Baseline multiple child cursors
                      Mohamed Houri

                      Hi Dom, Hi user13454469,

                       

                      I am wondering if this issue is not related to the following question

                       

                      ‘’When, at hard parse time, we end up with a plan that is not into the SPM baseline and there are multiple accepted baselines for this particular sql_id (signature), all these ACCEPTED plans will be re costed and the best plan will be then used. However, the question is, during this re cost calculation, what optimizer environment or parameters the CBO will be using? Is it the optimizer parameters stored during the baseline capture or is it the optimizer parameter of the current environment (current situation)?’’

                       

                      I was inclined to say that the re cost will use the current optimizer environment. But according to this thread, it seems that the SPM plan will be reproduced using the optimizer parameter stored at the SPM capture time.

                       

                      It becomes urgent to test

                       

                      Mohamed Houri

                      • 8. Re: SQL Baseline multiple child cursors
                        Dom Brooks

                        Ok. I think then that makes sense and ties in with what Mohamed is saying and probably just needs a bit of a test to confirm.

                         

                        You have a baseline that was created with a particular optimizer environment which included various fix controls and start transformation enabled but presumably with ACS , etc still set to default at that time?

                         

                        I was assuming that all these parameters had been set at the time the baseline was created but were missing.