Avoid full table scans

From: Charles John <cjohn_at_slb.com>
Date: 24 May 2001 15:34:30 -0700
Message-ID: <681fc8ea.0105241434.2636211e@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 - 17:34:30 CDT