7 Replies Latest reply: Nov 24, 2012 2:26 AM by Nikolay Savvinov RSS

    10.2.0.5 - execution plan changed due to bind peeking

    Chewy
      Hi Guys,

      Our DB server has very high cpu utilization on certain days.
      Based on investigation, it is due a particular SQL change in execution plan.

      For eg, this SQL has SQL_ID: 123, with 2 execution plan (hash value).
      PLAN 1 - ABC [GOOD] - index scan
      PLAN 2 - DEF [BAD] - full scan

      Problem SQL <not actually sql, simplify it for example>
      SELECT *
      FROM XX
      WHERE CRITERIA=:B1;


      I wish to identify the value it has peeked that caused the change of plan from plan 1 to plan 2.
      Anyway I can find out?
      I also have the SQLT report. But can't really understand the peek bind portion of the report.

      Kindly advise.
      thanks
        • 1. Re: 10.2.0.5 - execution plan changed due to bind peeking
          Fran
          gathering fresh statistics the execution plan could change because database think that the new execution plan is more efficient.
          Please check:
          Execution plan change for same query.
          • 2. Re: 10.2.0.5 - execution plan changed due to bind peeking
            Dom Brooks
            Use DBMS_XPLAN.DISPLAY_CURSOR, you can use the format mask of '+PEEKED_BINDS', e.g.
            select * from table(dbms_xplan.display_cursor('<sql_id>','<child no>','+PEEKED_BINDS'));
            Or you can look at V$SQL.BIND_DATA or possibly V$SQL_PLAN.OTHER_XML although I'm unsure at what version the peeked binds appear in the latter.
            You can use DBMS_SQLTUNE.EXTRACT_BIND/S against the RAW column of V$SQL.BIND_DATA if a) you licensed for tuning pack and b) it exists in that version.

            Edited by: Dom Brooks on Nov 23, 2012 9:54 AM
            • 3. Re: 10.2.0.5 - execution plan changed due to bind peeking
              Chewy
              Hi Dom,

              What if the information is no longer in my shared pool?
              Is there any place i can find it in the hist awr tables, etc?

              thanks
              • 4. Re: 10.2.0.5 - execution plan changed due to bind peeking
                Dom Brooks
                Yep, try the same format mask on DBMS_XPLAN.DISPLAY_AWR.

                Also see DBA_HIST_SQL_PLAN.OTHER_XML, DBA_HIST_SQLSTAT.BIND_DATA.

                Plus you've got DBA_HIST_SQLBIND but that is periodic captures not necessarily those peeked at parse time.
                • 5. Re: 10.2.0.5 - execution plan changed due to bind peeking
                  Chewy
                  Wierd.

                  I realise both the good and bad is using the same peeked variable.
                  Plan hash value: 2476761236

                  Peeked Binds (identified by position):
                  --------------------------------------

                  1 - :1 (VARCHAR2(30), CSID=1): 'A0000000000XX'


                  Plan hash value: 4132388962

                  Peeked Binds (identified by position):
                  --------------------------------------

                  1 - :1 (VARCHAR2(30), CSID=1): 'A0000000000XX'




                  In this case, any idea why did the optimiser decided to choose the bad plan instead?
                  thanks
                  • 6. Re: 10.2.0.5 - execution plan changed due to bind peeking
                    Dom Brooks
                    Hard to say without more information.
                    Might it be that there's not much to choose between the two plans from a cost perspective and it only take a small variation in the inputs to cause one to be picked over the other.

                    Can you post the two plans plus predicates?

                    See [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request.

                    It may be that you want to lock in the better plan using a sql profile - see coe_xfr_sql_profile.sql on http://support.oracle.com doc id 215187.1
                    Or it may be with a better quality of information, you get a better plan. it depends.
                    Do you have histograms on the column related to the peeked bind?
                    That can often be a problem. The features aren't really designed for completely opposite problems and so many features like cardinality feedback and adaptive cursor sharing are designed to workaround that fact. It may be the stats that are changing (or the histograms/the histogram endpoints) rather than the peeked bind.
                    It depends. So many factors.
                    • 7. Re: 10.2.0.5 - execution plan changed due to bind peeking
                      Nikolay Savvinov
                      Hi

                      >
                      ...
                      In this case, any idea why did the optimiser decided to choose the bad plan instead?
                      maybe the index was unusable. maybe the stats changed. maybe data changed. how are we supposed to know without having any evidence?
                      at very least, we'd need to have both plans with cardinality and cost estimates. it would also help to know what's in V$SQL_SHARED_CURSOR
                      for this query.

                      Best regards,
                      Nikolay