Re: Optimizer path

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 07 Mar 2012 21:31:46 +0100

Amir,

I see that your question hasn't been answered yet.

Ignoring other details, the answer to your question is that you've hit a corner 
case - and it even could be called a bug I think.

There is a clue in the information that you've provided:

- The table / index have 280K rows

- The index has only one distinct key

- The estimated number of rows for your original query is 139K (very close to 
the 280K rows * 0.5)

I believe you've hit a special case: Oracle 11g added the concept of "Column 
Groups" for the optimizer in order to address correlated column values. You can 
generate column groups explicitly via so called "Extended Statistics" in 11g 
but in the absence of such explicit column groups and histograms the optimizer 
will use the DISTINCT_KEYS of a matching composite index as measure for the 
selectivity and hence you end up with basically all rows returned by your three 
predicates corresponding to your index. The fourth predicate (REPROCESS_FLAG) 
is not covered by your composite index and very likely a Y/N field, so this 
results in a final selectivity of 0.5, hence 280K total rows times 0.5 gives us 
140K rows, which is pretty close to the estimate.

The problem here is however, that the values you've provided seem to be 
"out-of-range" predicates (not really, but according to the statistics I 
believe), so the optimizer should have discovered that and was supposed to give 
you basically a "no match" selectivity / cardinality estimate. I think this is 
a buggy behaviour in the special case of only one distinct value in the column 
statistics for all three columns, which is not unlikely given the shown value 
distribution (depending on how statistics are gathered on that table).

When you added the UPPER() functions to your WHERE clause the optimizer lost 
track of the matching column group and therefore reverted to the individual 
column statistics and recognized that the values are "out-of-range", hence the 
dramatic difference in the estimate of only a single row.

Basically you arrived at a better execution plan by accident: The optimizer 
estimated a cardinality of a single row, hence the index access path was 
favoured.

The original estimate based on the composite index DISTINCT_KEYS (without the 
functions) was 139K rows, hence the full table scan was selected.

If I understand your query correctly, then you're basically looking for a few 
rows at most that are in status "Running".
So you need to ask yourself why the optimizer estimated 139K rows based on the 
statistics when you expected only a couple of rows.

By generating a histogram you again break the index column group match and get 
an estimate based on the individual column statistics rather than based on the 
DISTINCT_KEYS of the index avoiding the buggy behaviour again.

There is a lot more to say for example about histograms, expressions like 
UPPER(), statistics gathering etc. but in your case I would think about (at 
least) three things:
- Use Virtual Columns / Function-Based Indexes that capture only the rare 
values that you're interested in and set the remaining values to NULL. This 
gives you a very small index and very precise cardinality estimates.
=> But you would need to change the application to use the expression of the 
virtual column / FBI

- If that is not feasible make sure that your column and index statistics 
reflect the state where more than one distinct value is available in the 
columns and use a real column group on the three columns with a histogram on 
the column group
=> There is a problem with low sample sizes for the histograms when using 
AUTO_SAMPLE_SIZE in 11g which might again render the histogram useless

If there is more than one distinct value in the statistics the "out-of-range" 
problem/bug doesn't show up

- Evaluate index compression on the existing indexes (which doesn't address 
your optimizer problem)

Hope this helps,
Randolf

Blogging at: http://oracle-randolf.blogspot.com

> When the statement is run with functions in the WHERE clause,
> the optimizer decided to use the leading column of the index to fetch
> rows, even though the selectivity of that column was bad. What made the
> optimizer go that route and why it did not choose it with the first run?



--
//www.freelists.org/webpage/oracle-l


Other related posts: