Re: Advice on a weird query plan

On Apr 17, 3:23 pm, mathewbutler <mathewbut..._at_yahoo.com> wrote:
> 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.

You'd also need an instea ed of trigger and a table rename. It would have to be a pretty important report to warrant such a work around.

Might be better off just tuning the FTS. Received on Fri Apr 18 2008 - 04:35:01 CDT