Re: Performance degradation

In article <m190hqxvbc.fsf_at_klondike.tiani.com>,   Juergen Gmeiner <jg_at_tiani.com> wrote:
> 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?

We are not using MTS.

> 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.

I think there is more than 512MB of RAM and the SGA is sized appropriately.

> 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 ...

Right now it does not appear that this is caused by excessive IO or CPU usage.

> 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.

Usually when this happens the database is so slow that we can't look at these views.

--
Alan Beal

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 05 1998 - 08:03:08 CST