Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: Wed, 25 Apr 2012 21:45:31 +0530
Message-ID: <CAEq4C0eocyMMPu32uOzjmb8_d09XQFtyGVNwAOMpT9KvPxkOjg_at_mail.gmail.com>

Hi,
Is the data and statistics same on both the environments?. Have a look at v$sys/ses_optimizer_env on both the environments to see if the optimizer settings are same. if all of them are same then generate a 10053 trace and look at the difference

best regards

sriram kumar

On Wed, Apr 25, 2012 at 8:03 PM, Li Li <litanli_at_gmail.com> wrote:

> Hi, List,
>
> I have a query that uses function based index in DEV1 db but does NOT
> use it in DEV2 db. I've compared parameters
> db_file_multiblock_read_count and optimizer_index_cost_adj, they are
> identical between DEV1 and DEV2. I've gathered system stats and stats
> on the tables that the query is based on in DEV2. Still the query
> refuses to use the index in DEV2. I can use the index() hint to force
> it to use it but that's not an accepted way to resolve this issue.
> Another strange thing is: even a commented out hint would make the
> query to use the index. such as:
>
> select --/*+ index(tranlogtra0_ NU_TTL_CANX) */
>
> I am confused and wondering what else I can do? Version 11.1.0.7.0 on RHEL
> 5.3
>
> Any suggestions and comments would be greatly appreciated!
> -Li
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Wed Apr 25 2012 - 11:15:31 CDT