Re: Avoid full table scans

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 - 18:40:37 CDT