Re: 10g Performance: its crawling

  • From: Nuno Souto <dbvision@xxxxxxxxxxxx>
  • Date: Fri, 29 Dec 2006 21:59:58 +1100

MVR wrote,on my timestamp of 29/12/2006 9:22 AM:
Nope, unfortunately I dont have old plan (of 9206). Well, I know the
SQL. I have rebuilt one
of index(reclaimed more than 100M, and now size of the index is 279M)
... I will see if that makes any difference.. I guess it is INDEX
RANGE SCAN... so dont think it makes much difference... if its INDEX
FULL SCAN, and resetting highlevel watermark by rebuild makes sense...

Tuning Advisor recommends SQL profile which <10% benifit. If this
index thing does not help, next option is to create a SQL profile..
even it is <10%, but it matters when no# of executions are more.


the other thing to try - but only if this starts to happen
with a lot of your SQL - is to reset the optimizer_features_enable
parameter to your prior release.  That usually cures these
"runaway" problems on install of a new version.

Of course then you need to check the plans with the old release
level, compare them with the 10gr2 plans and see if you can then
figure out why things are going "clunk".  Usually it's some minor
difference in the CBO behavior that just happens to clash with
the particular conditions of your database and data/index distributions.

This parameter can be set with ALTER SESSION and ALTER SYSTEM,
so it's relatively simple to modify - meaning: doesn't involve
elaborate re-starts.

Had to do this a number of times already on some of our clients
who upgraded to 10gr2 and experienced similar problems.
Have a good look at it in the doco: it's quite a useful
way of temporarily resolving these unexpected/undocumented
problems.

Let me stress the "temporary": try to use the appropriate
level of CBO for your release whenever possible.  This parameter
is not a "cure-all", it's there to help minimize problems.

--
Cheers
Nuno Souto
dbvision@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l


Other related posts: