Re: performance issue after upgrade to oracle 188.8.131.52 linux 32 bit.
Date: Thu, 5 Nov 2009 04:27:16 -0800 (PST)
On Nov 4, 7:56 pm, joel garry <joel-ga..._at_home.com> wrote:
> Yes, Jonathan blogged about an example of this in "FBI
> problem" (http://jonathanlewis.wordpress.com/2007/11/18/fbi-problem/
> ) But it's still usually worth a try, if just to narrow
> Also, just to clearly emphasize to the OP, it is recommended in a lot
> of places not to set OPTIMIZER_INDEX_COST_ADJ and
> OPTIMIZER_INDEX_CACHING, at least until testing shows they help - it's
> likely a mistake to keep the settings from earlier versions. Seehttp://richardfoote.wordpress.com/2009/09/01/optimizer_index_caching-...
> for some idea of what to look for.
Thanks for providing the links. It looks like Jonathan's blog post describes what I saw while trying to un-fix 184.108.40.206.
Richard Foote also has at least a 3 part series on the effects of
OPTIMIZER_INDEX_COST_ADJ. This example shows a query requiring
00:01:49.75 (just under 2 minutes) with the default value of
OPTIMIZER_INDEX_COST_ADJ, and 02:16:44.42 (just over 2.25 hours) with
a very small value for the parameter:
Of course there is also one of my test cases here: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6c436cee329326ec/65f7487bd653d0df
"What about the performance results?
* Oracle 220.127.116.11 on 64 bit Windows finished the full tablescan access
path for the query in 35.38 seconds and the index access path in 1 hour, 38 minutes and 7 seconds.
* Oracle 18.104.22.168 on 64 bit Oracle Enterprise Linux 5 required 41.98 seconds for the full tablescan access path and one hour, 14 minutes and 40 seconds for the index access path. * Oracle 22.214.171.124 required 13.13 seconds (a significant drop from the 41.98 seconds received on the same server with the same operating system and disk subsystem) and 22.13 seconds for the index access path
(an unbelievable drop from the one hour, 14 minutes and 40 seconds on the same server, operating system, and I/O subsystem)."
35.38 seconds with the default value compared to 1:38:07 (just over an
hour and a half) with a value of 5 (10 might have also worked, but not
41.98 seconds with the default value compared to 1:14:40 (just under 1.25 hours) with a value of 5.
13.13 seconds with the default value compared to 22.13 seconds with a value of 5.
I guess the message is to test to see why the performance is different.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Nov 05 2009 - 06:27:16 CST