Re: SOS - the day after migrating to Oracle 9.2

Alex wrote:
>
> Hi,
>
> 2 days ago we migrated an Oracle 8.1.7.4 instance running on Linux to
> 9.2.0.5 running on Linux inside a VMWare virtual Machine.
>
> We had a lot of performance problems a year ago and so the database
> was tuned with a few parameters an since 9 Month running fine even
> with a gain of twice the sessions in the meantime (~ 800 at peak
> time).
>
> Mainly these parameters are:
>
> cursor_sharing = force
> optimizer_mode = first rows
> optimizer_index_caching = 20
> optimizer_index_caching = 90
>
> Now the migrated database was running fine in the morning until 300
> sessions and got unusable slow above 400 sessions.
>
> We had to lock out users to even get a statspack in a decent time.
>
> Here's the statspack.
> Has any experienced Oracle user some hints what to look for.
> Anything that might explain why it doesn't scale?
> Any bottleneck? I'm very thankful for any hint.
> This statspack was taken during warm up so I consider the cpu / latch
> free related to parsing at least that is what I saw at the old
> instance as well after starting up.
>

<snip>

Putting aside the vmware issue...(all i will say is: "ugh!")

Looks like you're getting hurt on library cache...Now sorting that one out under VMware sounds like a very tough on to me. All that CPU stuff is now virtualised within VMware. For example, when you spin on a latch, are you *really* spinning - VMware might sleep you out. cursor_sharing = force suggests that you're doing a lot of parsing (and hence used c_s = force to reduce the damage). Either way, you're still likely to be: "Welcome to Parse-town: Population: You" :-)

Bottom line - get it off VMWare, or at least run up a benchmark on a non-VmWare platform. You could crank up session_cursor_cache, but that may well be putting a band-aid on an amuptated leg.

hth

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------