In article <5rdrgtsciksocbc86bgbgpufrd61dc9i8t_at_4ax.com>, tkyte_at_us.oracle.com wrote:
>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
>
Received on Fri May 25 2001 - 13:59:09 CDT
