Re: Eroding Oracle Performance

> I am experiencing a strange problem in one of my 8.04 instances. Gradually,
> over the course of a day, performance goes in the tank. I mean we have a
> benchmark query (select count(*) from foo) that runs in less than a second at
> 8:00AM and runs in 10 seconds by noon, and by 4:00PM is up to 20 seconds.
> Same number of rows, only slightly more user sessions. It doesn't seem to be
> database buffers, as I get anywhere from 93% to 97% hit rates on blocks in
> buffers. But when we go in and look at the performance monitor in NT, the CPU
> is very nearly 100% utilization, this at times when we have restricted user
> sessions and look to be doing no disk I/O. All of this goes away if we
> shutdown the instance and bring it back up. One other clue. The last time I
> shutdown the instance, I did a shutdown immediate. After 30 minues with no
> response I had to go in and kill the service in NT via service manager.

I would look at SGA size and other cache related sizings. They may be too small.

Daniel A. Morgan Received on Sat Sep 12 1998 - 23:23:25 CDT