Re: Index clustering factor
- From: "Rakesh Tikku" <rakesh.tikku@xxxxxxxxx>
- To: greg@xxxxxxxxxxxxxxxxxx
- Date: Fri, 11 Jan 2008 01:59:22 -0800
In addition to bind peeking, another thing that can cause the explain plan
and the runtime execution plan to be different is implicit datatype
conversion that is performed by Oracle if the the datatypes of the column
and the bind variable are different. Explain plan assumes the bind variables
to be of character datatype, therefore the runtime plan can be different
that is acutally not the case.
For eg, if fielda column is of varchar2 datatype and the bind variable (:A)
is of number datatype, Oracle may internally convert the predicate "where
fielda=:A" to "where to_number(fielda)=:A". That will make your current
index unusable for this query.
How to find out if this is what is preventing the index to be used - Look
at the column filter_predicates of v$sql_plan, and you will be able to find
out if Oracle is doing any implicit type conversion.
hth.
Rakesh Tikku
dbPerf Inc.
On Jan 9, 2008 4:08 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
> I think you are overly focused on clustering factor. Assuming makes
> trouble. Let's start with what we do know, and not guess about what
> we do not.
>
> If explain plan does *not* match execution plan (red flag) - bind
> peeking may be the factor. Let's try a simple test.
>
> SQL> alter session set "_optim_peek_user_binds" = false;
> SQL> <set your binds and execute the statement>
> SQL> select * from table(dbms_xplan.display_cursor);
>
> Does this yield a plan with index access or FTS?
> If it uses the index, then there may be a histogram on one or both
> columns so lets find which ones:
> SQL> select column_name,histogram from user_tab_col_statistics where
> table_name ='<table>'
>
> Another good test is to substitute the literal values in place of the
> binds and see if that yields the desired plan.
>
> When you post your reply with the information from above, we'll
> proceed with more of what we know.
>
>
> On 1/9/08, Orysia Husak <Orysia.Husak@xxxxxxxxxxxxx> wrote:
> > When I generate an explain plan manually, the appropriate index is used.
> > When I view the sql running through OEM, I see the execution plan is a
> > full tablescan. Since the clustering factor for this index is very high,
> > my assumption is that the index isn't being selected due to the high
> > clustering factor.
>
> --
> Regards,
>
> Greg Rahn
> http://structureddata.org
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
