Re: Avoid full table scans

verify that ALL of the parameters are the same on the two database instances.

select * from v$parameter;

be sure to compare all of the parameters... the values do influence the optimizer...

e.g. a larger value for the "multi block read count" (i don't recall the exact name) parameter causes the optimizer to assume lower costs for full table scans.

also, compare the computed statistics between the two instances to compare the number of key values, etc. is it possible histograms have been collected on one of the schemas and not the other ?

"Charles John" <cjohn_at_slb.com> wrote in message news:681fc8ea.0105241434.2636211e_at_posting.google.com...
> 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
>
> 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 - 21:14:13 CDT