Re: Index clustering factor

From: Rakesh Tikku <>
Date: Fri, 11 Jan 2008 01:59:22 -0800
Message-ID: <>

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 <> 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 <> 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
> --

Received on Fri Jan 11 2008 - 03:59:22 CST

Original text of this message