1 2 Previous Next 15 Replies Latest reply on Jan 15, 2015 12:11 PM by Jonathan Lewis

    Bind Peeking only for "known" columns?

    fjfranken

      Hi all,

       

      We are working on our 11.2.0.3 RAC (on AIX 7.1) database on trying to figure out why a certain repeated query ( batch load) is not using the correct execution plan.

      The query itself looks like:

       

      select CATENTRY_ID from CATENTRY where ((PARTNUMBER=:1 ) OR ((0 = :2 ) AND (PARTNUMBER IS NULL))) and ((MEMBER_ID=:3 ) OR ((0 = :4 ) AND (MEMBER_ID IS NULL)));

       

      This query is an IBM Webshere internal query, which therefore is unchangeable.

      The table in question has an Index available on PARTNUMBER & MEMBER_ID

      The execution plan however looks like

       

      The execution plan of the above statement looks like:

       

      Execution Plan

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

      0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=2038 Card=1 Bytes=23)

      1 0 TABLE ACCESS FULL WCSADMIN.CATENTRY (Cost=2038 Card=1 Bytes=23)

       

      So a FTS scan is used where an Index-lookup would be expected.

      The values passed to this query are e.g.:

       

      :1 = XA-GED-1068849

      :2 = 1

      :3 = -6000

      :4 = 1

       

      With the part of the WHERE CLAUSE then having ((0=1) AND (PARTNUMBER IS NULL)) and the same for ((0=1) AND (MEMBER_ID IS NULL)) would result in an Index lookup.:

       

      select

      catentry_id

      from catentry

      where ( (partnumber = 'XA-GED-5702810')

        or ( (0 = 1)

        and (partnumber is null)))

      and ( (member_id = -6000)

        or ( (0 = 1)

        and (member_id is null))) ;

       

      Execution Plan

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

      0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=23)

      1 0 TABLE ACCESS BY INDEX ROWID WCSADMIN.CATENTRY (Cost=3 Card=1 Bytes=23)

      2 1 INDEX UNIQUE SCAN WCSADMIN.I0000064 (Cost=2 Card=1)

       

       

      Somewhere in the parsing of the query the optimizer does not have/use all the information needed to determine the correct plan, allthough the tracefile shows all values are captured correctly

      I would expect that the optimizer would "PEEK" all available variables to determine the best execution plan.

       

      It looks however that the two BINDs for the "0=:2" and "0=:4" are not "peeked" and therefore not used, which results in a Full Table Scan as the PARTNUMBER IS NULL and MEMBER_ID IS NULL are not skipped.

       

      Can anyone confirm that only BINDs for "existing/real" columns are peeked??

      And is this configurable ??

       

      Thanks

       

      FJ Franken

        • 1. Re: Bind Peeking only for "known" columns?
          Dom Brooks

          Your expectations are wrong.

          Yes binds are peeked to get some specific information but the execution plan has to assume that the bind values can/will change.

           

          When you are using a literal, the optimizer can eliminate parts of the plan as irrelevant - i.e. we can see that 0=1 is always false and can eliminate part of the sql.

           

          When you are using binds you need a shareable execution plan which is good for all possible supplied binds so 0 = :2 has to be a runtime evaluation in the plan.

          • 2. Re: Bind Peeking only for "known" columns?
            fjfranken

            Hi Dom,

             

             

            Thanks for the response.

            What you are describing is exactly what I am expecting.

            Even if, in the first run, the optimizer does "peek" only the values for partnumber & member_id, I understand that the execution plan would result in a Full Tablescan as the binds for the 0=:1 are unknown and that part of the query has to be taken into account.

             

            However with every next run ( and there are thousands of value-sets in the batch), I would expect that the optimizer with then use all values available and change the execution plan, or create a new one for this situation.

            I've been looking at a document of Arup Nanda where Adaptive Cursor Sharing results in execution plans changing, or the generation of multiple execution sub-plans, each one suitable for the related set of binds.

            The doc: http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html

             

            Or am I missing something here??

             

            Thanks again

            • 3. Re: Bind Peeking only for "known" columns?
              michaelrozar17

              I may be missing something but I'm quite unable to understand on why you think that Optimizer shouldn't chose different plan for same sql that uses bind variables. This is the reason Adaptive Cursor Sharing was introduced. Check the SQL in question is IS_BIND_AWARE and IS_BIND_SENSITIVE via V$SQL view. If they are Y then optimizer has chosen to use different plan for different bind values. Read https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1 and also ensure that statistics on the table are up-to-date

              • 4. Re: Bind Peeking only for "known" columns?
                Dom Brooks

                ACS might kick in over time...

                But you have to have suboptimal executions with different bindsets for the optimizer to recognise that there might be a better plan for a particular bindset.

                 

                What you can't have is execution plans which are completely invalid for a particular bindset.

                 

                So, in that respect, comparing the plan when you provide a set of literals and comparing a plan where you have binds does not make sense.

                 

                On second thoughts, ACS might not kick in.

                 

                Bind sensitivity should occur when the bind vairable has been peeked AND the column referring to bind has a histogram - in this case there is no column referring to the :2 and :4.

                1 person found this helpful
                • 5. Re: Bind Peeking only for "known" columns?
                  Mark D Powell

                  fjfranklen, >> I would expect that the optimizer would "PEEK" all available variables to determine the best execution plan.<<

                  - -

                  I just want to add to Dom's pretty good replies to your post that when just talking about bind variable peeking Oracle peeks once on the first execution of a query and then uses that plan for all subsequent executions.  There are events that will cause a re-peek.

                  - -

                  HTH -- Mark D Powell --

                  1 person found this helpful
                  • 6. Re: Bind Peeking only for "known" columns?
                    fjfranken

                    I suddenly found THE solution.


                    I created an extra Index that also contains the selected column. Now the optimizer decides that accessing the Index is sufficient, thus bypassing tableaccess and possible (non existing) null values

                    The batch that ran for 25,5 hrs yesterday now finished in less than 5 minutes !!!



                    Thanks for all the Input

                    • 7. Re: Bind Peeking only for "known" columns?
                      Jonathan Lewis

                      That's an interesting question - at first sight it looks as if adaptive cursor_sharing ought to be able to address your problem.

                       

                      However, I think the optimizer has to produce a plan that will ALWAYS produces the right result regardless of the actual values supplied. Since your query could require the optimizer to find rows where memberid and partnumber are both null, and you index (presumably) doesn't have a mandatory column in it then the only legal execution plan is the full tablescan.

                       

                      Depending on the relative frequency of values, and the number of nulls for each column, you might find that the generic solution (rather than the index-only solution that you got for this specific query) is to create an index on (partnumber, member_id, 1) or (member_id, partnumber, 1) then the optimizer could use CONCATENATION to pick one of two alternative paths.

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Bind Peeking only for "known" columns?
                        fjfranken

                        Thanks Jonathan,

                         

                         

                        Your solution of creating the Index on (partnumber, member_id, 1) forces the optimizer in the direction we need.

                        I executed :

                         

                        create  unique index cat_test on catentry(partnumber, member_id,1)

                        nologging  noparallel compute statistics;

                         

                         

                        and now the execution plan shows:

                         

                        Execution Plan

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

                           0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=6 Card=2 Bytes=46)

                           1    0    CONCATENATION

                           2    1      FILTER

                           3    2        TABLE ACCESS BY INDEX ROWID WCSADMIN.CATENTRY (Cost=2 Card=1 Bytes=23)

                           4    3          INDEX RANGE SCAN WCSADMIN.CAT_TEST (Cost=2 Card=1)

                           5    1      TABLE ACCESS BY INDEX ROWID WCSADMIN.CATENTRY (Cost=4 Card=1 Bytes=23)

                           6    5        INDEX RANGE SCAN WCSADMIN.I0000064 (Cost=3 Card=1)

                         

                         

                        The CAT_TEST index being "your" index,

                        The I0000064 Index being the Index on (partnumber, member_id) that already existed and of which we wanted it to be used ;-)

                         

                        Your solution will actually be better than the Index I created, as this one also supports multi-column selects

                         

                        Thanks

                        • 9. Re: Bind Peeking only for "known" columns?
                          Jonathan Lewis

                          I tried to modify my answer moments after I published it - but I couldn't retrieve it (even though the inbox said it was the latest item that I had posted).

                           

                          Just a tiny change - if you use 0 instead of 1 as the extra column on the index then the index will be slightly smaller since 0 is stored internally as 0x80, which 1 is stored as C1 02.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Bind Peeking only for "known" columns?
                            fjfranken

                            And that is how you recognize a super DBA !!  

                             

                             

                            Thanks Jonathan

                            • 11. Re: Bind Peeking only for "known" columns?
                              Jonathan Lewis

                              I've just noticed you created the test index as unique:

                                create  unique index cat_test on catentry(partnumber, member_id,1)


                              Presumably because the current index is unique; but a unique index allows multiple entries where all columns are null - which means this new index could raise a "uniqueness" data-entry error where the old index doesn't. To be safe you need this index to be non-unique.


                              If you want to drop the other index (if the columns are in the same order it's redundant) you could still create this index as a non-unique index then create a unique constraint based on just the first two columns.


                              Regards

                              Jonathan Lewis


                              • 12. Re: Bind Peeking only for "known" columns?
                                fjfranken

                                Jonathan,

                                 

                                 

                                Just one final question.

                                I still don't understand the mechanism of why the optimizer does now use the Index when that extra literal column ( the 1 or the 0 ) is added.

                                Can you elaborate on this?

                                 

                                 

                                Thanks

                                 

                                Frank-Jan

                                • 13. Re: Bind Peeking only for "known" columns?
                                  GS613

                                  Both columns in your index (partnumber and member_id) can be null.

                                   

                                  In order for your query to use an index the entry in the index needs to exist - if both columns are null then there will be no index entry.

                                   

                                  An index entry does not exist if ALL the fields in the index are null.

                                   

                                  In order to ensure that those records for nullable columns are included in the index a work-around is to add a literal to the index key - in this case a 0. Now one of the fields in the index will always have a value (0 is not null) and therefore will be indexed and can be accessed as such.

                                   

                                  Correct me if I am wrong, Jonathan!

                                  • 14. Re: Bind Peeking only for "known" columns?
                                    GS613

                                    To add to my answer:

                                    there is a explanation in the following article - read from code listing 3.

                                    Ask Tom: On Constraints, Metadata, and Truth

                                    1 2 Previous Next