Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db
- From: Sriram Kumar <k.sriramkumar@xxxxxxxxx>
- To: litanli@xxxxxxxxx
- Date: Wed, 25 Apr 2012 21:45:31 +0530
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@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
