Bind Peeking only for "known" columns?

fjfranken · Jan 14, 2015 5:37AM

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

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

Jonathan Lewis · Jan 14, 2015 11:12AM

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

Dom Brooks · Jan 14, 2015 5:44AM

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 · Jan 14, 2015 5:55AM

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

michaelrozar17 · Jan 14, 2015 7:43AM

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 · Jan 14, 2015 8:10AM

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.

Mark D Powell · Jan 14, 2015 9:04AM

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 · Jan 14, 2015 10:26AM

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 · Jan 14, 2015 11:12AM

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 · Jan 15, 2015 4:05AM

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 · Jan 15, 2015 4:26AM

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 · Jan 15, 2015 4:29AM

And that is how you recognize a super DBA !!

Thanks Jonathan

Forum Stats

Quick Links

Categories

Bind Peeking only for "known" columns?

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Bind Peeking only for "known" columns?

Best Answer

Answers