Forum Stats

  • 3,855,378 Users
  • 2,264,500 Discussions
  • 7,905,980 Comments

Discussions

Bind Peeking only for "known" columns?

fjfranken
fjfranken Member Posts: 1,268 Silver Badge
edited Jan 15, 2015 7:11AM in General Database Discussions

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

fjfrankenCherif bhGS613

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    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

«1

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown

    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.

    fjfranken
  • fjfranken
    fjfranken Member Posts: 1,268 Silver Badge

    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

  • 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

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown

    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.

    fjfranken
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond

    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 --

    fjfranken
  • fjfranken
    fjfranken Member Posts: 1,268 Silver Badge

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    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

  • fjfranken
    fjfranken Member Posts: 1,268 Silver Badge

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    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

  • fjfranken
    fjfranken Member Posts: 1,268 Silver Badge

    And that is how you recognize a super DBA !!  

    Thanks Jonathan

This discussion has been closed.