Poor DB performance (Dictionary Cache?)
Date: 13 Feb 1995 20:32:31 GMT
Message-ID: <3hofkv$nrg_at_toon.ctp.com>
Hi all,
HP 9000/887
Oracle 7.0.16.6
HP/UX 9.04
PL/SQL 2.0
One of the instances on our server machines has suddenly taken a nose-dive in
performance. This degradation was first noticed after a "big" stored
procedure was loaded in the database and executed. At that point it seemed that
the database was hanging. The solution at that time was to flush the shared pool.
After that, we noticed performance degradation whenever views were being accessed.
This even includes "describe" operations. The complexity of the view did not
seem to affect this behavior. Even simple views would hang the database.
The solution at that time was to keep the instance running was to have a shell script that keeps flushing the shared pool. Recently however, this script was not running and at some point the database hanged when a client process. At this point we took a dump of the system state and numerous other dumps using "oradbx" (this was suggested by Oracle WW support). After this the script was started again. However, since that time, the database has been operating at a snail's pace.
I ran SQL*DBA and noticed that in the "system statistics" readout, the value of the "recursive calls" variable ranges between '15000' and '20000'. However, the Corrigan and Gurry "Oracle Performance Tuning" book states that this should be '0'. They say that this is due to an incorrectly tuned dictionary cache. But I am not sure what exactly has to be done to rectify the situation.
Any help or direction would be greatly appreciated.
Thanks!
Pavan.
Pavan V. Muzumdar
Cambridge Technology Partners, Cambridge, MA 02139. USA. Tel: (617) 374-9800 Fax: (617) 374-8300 E-mail: pmuzu_at_ctp.com Received on Mon Feb 13 1995 - 21:32:31 CET