Re: Avoid full table scans

A copy of this was sent to cjohn_at_slb.com (Charles John) (if that email address didn't require changing) On 24 May 2001 15:34:30 -0700, you 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
>
>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 ?

compare the init.ora's

sort_area_sizes, hash_area_size, db_block_buffers, db_file_multiblock_read_count, and so on will affect the CBO.

Also look at tweaking

optimizer_index_caching
optimizer_index_cost_adj

to make indexes "more appealing" to the CBO (Oracle8i and up only)

>
>Any thoughts would be appreciated.
>
>Charles

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp