Sonntag, 24. November 2013

A developer read about adaptive cursor sharing and therefore guessed, the optimizer would know about the number of rows when he passes a collection for a table(:bind) function. I can totally understand it, as there where no limitations in the statement (except the 14 binds):

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.
Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.

There is no limitation about where the bind peeking is done (and where not). Based on my findings I'd limit it to "only in filtration". But I accept any better wording.

Now let's go down to the testcase (all tested in 11.2.0.3):
To prepare a nice environment I did:

The statements with cardinality hint show a proper flip in the execution plan from 1000 to 10000 rows.
Unfortunately the statement with dynamic_sampling does not follow this pattern. The first execution plan sticks:

I hope you can see, even in 11.2.0.3 it's like rain on monday morning in Washington DC. Dynamic Sampling jumps in, but it's not adaptive.
What's the conclusion here?
If you use a collection in a table function, and the expected numbers in that collection varies by dimensions, take care! My solution is a cardinality hint, but that might be complicated if you are using a framework.
I don't know any 'cheap' solution.
Maybe anyone wants to try this in 12c. I did not focus there yet. It's still 2013 :-)