Re: 10g Performance: its crawling

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_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l