Partition Pruning and Bind Variables

Prompted by a question at the Dizwell Forum, here is a script to demonstrate that using bind variables in partition key predicates causes Oracle to use global (table) statistics instead of partition (or subpartition) statistics.

Script

Result on 9.2.0.4

This is similar behaviour to Oracle’s use of global statistics in other circumstances, such as when joining on a partition key to a smaller table on which a predicate is placed. Anyway, see the forum post for other comments :D

About these ads

15 thoughts on “Partition Pruning and Bind Variables

  1. Hmm, now that is an interesting topic:
    …such as when joining on a partition key to a smaller table on which a predicate is placed and worthy of further study.

  2. Another test would be to create a few more partitions (5-10) and equally populate them – this would perhaps be a touch more realistic for a DW case and see how the plan changes with binds

  3. Pingback: Partitioned tables and statistics « Pete-s random notes

  4. David,

    I think you need to revisit your test case.

    You have used the define command to set the values for your variable, but you need to say something like:

    execute :l_col1 := 5

    There is also the problem that explain plan treats a bind variable as an unpeekable, therefore unknown, value.

    But with run-time peeking the optimizer sees the value and will generate an execution path accordingly – which can be a bit of a disaster when you run the query a second time with a different value for the bind. You probably have to check the 10053 trace to see this effect.

  5. Oh ho — yes of course. I shall give that a run. With regard to the peeking issue, it seems to me that the predicate on the partition key must surely be flagged in some way to prevent the same exact plan being used in regard of partition pruning, as using the same pruning would likely lead to incorrect results.

    Well, maybe not. If the plan stored just indicates “dynamic pruning based on the value of this bind variable” then I suppose it is not an issue. Or that is how the issue is implemented.

    Moving along, I’ll just rerun the test. :(

  6. There are two different effects -

    Optimization time peeking which dictates the plan.

    Run-time knowledge of values which dictates the partition against which the plan is to be run. The latter including the option for not running (the bulk of) the plan at all if the incoming value is above the known high-value. There will be an example on my blog very soon.

  7. Pingback: Rittman Mead Consulting » Blog Archive » Partitioned tables and statistics

  8. I have a similar problem.

    I have two database cloned from production env with same snapshot.
    I have HASH JOIN + PARTITION RANGE ALL + TABLE ACCESS FULL in one explain plan with total cost > 38689
    while other db got only nested loops for same query with cost = 1191.

    I have run ALTER INDEX, ANALYZE INDEX, ANALYZE TABLE but with no luck.

    Both databases have exactly same indexes and same number of records.

    Can you suggest what I am missing.

      • In fact its on 10g db. The only thing I can see which may impact performance is GSS or Table Stats. What else could exactly explain deviation in two databases, with same number of records, same indexes (as mentioned earlier, snapshot of production)

        Have already run Gather Schema Stats and Gather Table Stats with no luck. Anything else which I should try…

      • Are the table, partition, and index statistics the same? Same clustering factor? Is one of the queries using dynamic sampling? Are the optimiser-related parameters the same?

  9. Finally got it. 10g 10.2.0.5 has problem while 10.2.0.4 is working normally. Any Optimizer rule changes?

    • I should think so — an optimizer trace using event 10053 is probably the best way to find out as it would also show up any changes in the arithmetic. You could try changing optimizer_features_enable on 10.2.0.5 to see if it will behave the same as 10.2.0.4

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s