Wolfgang,
I have a similar problem that I am still trying to work on.
Except for
* nls_sort ('binary' => performance is better,
null it is bad)
and
* sort_area_size (8388608 => bad performance,
8688000 => good performance)
all other parameter are same (from 10053 trace). With good performance, query returns in 3-4 seconds, for bad performance it is more than 1 minute. We must have the output in less than 5 seconds (worst case).
I am still playing with it though ... this is all 9202 ... one thing i have noticed is that explain plan is different.
Let's see if I find anything ...
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-----Original Message-----
From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 03, 2003 4:55 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Oracle's use of Indexes
The cardinalities are the same, but the costs are different. It looks as if
production has somehow optimizer_index_cost_adj set to 50 or lower or has
db_file_multiblock_read_count set to 8. It's the same instance so that is
not possible unless they are changed at a session level.
To check that, or any other init.ora differences that may be of importance
select the name-value pairs from v$parameter in both schemas and do a diff.
Of course the easiest is to diff 10053 event traces and see where they
deviate, which will hopefully provide a clue as to why.
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
