Re: Avoid full table scans

Also, check OPTIMIZER_INDEX_COST_ADJ (sp?) parameter.

In article <3B0D9BF5.B3BB23A3_at_bellsouth.net>, "Michael Austin" <miaustin_at_bellsouth.net> wrote:

> Charles John wrote:
>
>> I have 2 identical schema on 2 different Oracle database servers. The
>> same application is being run from clients accessing these db servers.
>> The performance of the one that is running on a relatively more
>> powerful db server is slower than the other.
>>
>> All indexes are the same.
>> The entire schema has been analyzed.
>> The optimizer is set to CHOOSE on both
>>
>>
> Is there a difference in the number of records for these tables
> (cardinality)? How long since the statistics were computed on the
> slower database. My guess would be that the slower database has grown
> significantly faster than the fast database. But then again, this is
> just a guess. Look at your statistics and see what is going on there.
>
> Michael Austin
> DBA Consultant
>
>
>
>> From the trace files, it has been noted that full table scans occur on
>> 3 tables on the slower db, while the other uses index scans.
>>
>> I took one of the SQL statements from the trace and run the same SQL
>> with explain plan on both the servers, and noticed this to be true.
>>
>> What are the things to check other than the indexes and the analyze,
>> that might cause the optimizer to do full table scans instead of using
>> the indexes ?
>>
>> Any thoughts would be appreciated.
>>
>> Charles
>
Received on Thu May 24 2001 - 19:37:14 CDT