Re: Performance degradation

abeal_at_us.ibm.com writes:

> We have an Oracle database running on Solaris 2.6 using Oracle 7.3.3 with
> typically 1100 concurrent connections many of which are idle most of the
> time. However, recently we started experiencing severe database degradation
> where the database slows to a crawl. One of the DBA was able to observe a
> high number of enqueues, library cache latches, and high number of shared
> pool latches. When the degradation happens, it is usually so severe that
> they have to shutdown the database and restart it.

your problem description is a bit vague, but is it possible that the machine starts trashing? are you using mts or dedicated server processes? if you have 1100 server processes hanging around, i could imagine they're paged out most of the time.

did you adjust the sga to the ram size? i've had several (well, two :) customers who upgraded to 512MB and didn't adjust the buffer cache.

is there too much io on any given disk? did you separate the "hot spots"? usually online redo logs, rollback table space and you're most active tables/indexes ...

is someone issueing "killer sql"? try to get the pid of the server process(es) consuming all the resources (like using "top" or "monitor", don't know what's available on solaris) and look in v$session for it and its parent process id.
in v$sql you should find the statement that's causing all this.

hth,
juergen Received on Thu Nov 05 1998 - 04:55:51 CST