Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Thu, 5 Nov 2009 04:27:16 -0800 (PST)
Message-ID: <2a9267fc-7eb3-41b2-90a6-7f73951d02ed_at_v25g2000yqk.googlegroups.com>
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
> possibilities.
>
> 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.
>
> jg
> --
Joel,
Thanks for providing the links. It looks like Jonathan's blog post describes what I saw while trying to un-fix 11.2.0.1.
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:
http://richardfoote.wordpress.com/2009/07/22/the-cbo-and-indexes-optimizer_index_cost_adj-part-ii/
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 11.1.0.7 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 11.1.0.6 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 11.2.0.1 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
tested).
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.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Nov 05 2009 - 06:27:16 CST