Re: Advice on a weird query plan

On 17 Apr, 12:54, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Pat" <pat.ca..._at_service-now.com> wrote in message
>
> news:54a55e5f-0dc2-4628-8f06-bf1982f95e31_at_b64g2000hsa.googlegroups.com...
>
>
>
>
>
> >I have a bit of a followup I was hoping folks could help me with. I
> > created histograms on the hidden tables in question and my test case
> > started using an index range scan. I think my test case was incomplete
> > though because a couple of other queries against the store are showing
> > different behavior.
>
> > Specifically:
>
> > select * from sysevent where state='a' <- index range scan
> > select * from sysevent where state='ab' <- index range scan
> > select * from sysevent where state='abc' <- index range scan
> > select * from sysevent where state='abcd' <- index range scan
> > select * from sysevent where state='abcde' <- index range scan
> > select * from sysevent where state='abcdef' <- table access full
>
> > There are no records in the database meeting either of those criteria
> > e.g. all queries return nothing.
>
> > Seems like any query term with length >=6 is doing a table access
> > full.
>
> This looks like a piece of "special case" code that Oracle has put in
> for the case of character-based histograms.  As Mathew Butler pointed
> out, a character-based histogram effectively records information about
> just the first six BYTES of the character string, and because of the
> hextoraw() conversion that's going on, your 'abcdef' turns into a seven
> byte string. Even without using NLS settings, you can see this type of
> thing happening - although you'd have to be using a seven character
> string if you're using the typical single-byte character set.
>
> The full scan is because the arithmetic changes at that break point and
> predicts a much larger number of rows.  It is possible that the change of
> strategy is deliberate, but the resulting arithmetic is NOT a good
> strategy.
>
> I'll take a guess that (assuming your stats are perfectly up to date) the
> predicted cardinality in the execution plan is roughly:
>     number of rows in table / (2 * number of rows with value 'ready')
>
> Bottom Line:  you haven't done anything wrong, it's just the way that
> Oracle works (at least in 9.2.0.8 and 10.2.0.3 where I've just checked it).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -

I think I came across this before in a similar situation to the OP. We had a table that recorded running "jobs" these had text statuses recording "completed", "running", "killed" etc. From memory, we had problems with part of the system that queries on the status of "running" jobs, since even with string literals in the query we received an inefficient execution plan from the optimizer. Since then I've always held onto a mental note to keep flag values less than this BYTE limit to avoid the same problem in future.

Presumably the ideal solution to this would be to reduce the length of the flag value to be less than the BYTE limit and enable full use of histograms?

An sneaky alternative, might be to add another column to the table, populate the column with values that are less than this byte limit, but that map to the allowable values in the table. Create a view onto of this table that references this new column with the same name as in the original table and create the necessary histogram. Then in theory, the application when querying would be seamlessly re-directed to query against this new column, and the optimizer would be able to use the histograms to generate an appropriate ( and efficient ) execution plan.

Of course, the sneaky alternative would only be a stop gap solution whilst the application code was changed permanently to use the correct fix.

Does anyone you see any technical problems with this approach? I accept that if the application is provided by a third party then this sneaky approach throws up some issues around product support. Received on Thu Apr 17 2008 - 09:23:25 CDT