Re: Poor DB performance (Dictionary Cache?)

In article: <3hofkv$nrg_at_toon.ctp.com> pmuzu_at_ctp.com (Pavan Muzumdar) writes:
> 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.

It looks to me like your shared pool just isn't large enough. This could account for what you're seeing.

Whenever a new SQL or PL/SQL statement (eg stored procedure, view definition, etc.) is parsed, Oracle sticks it in the library cache in the shared pool. If there's no room for it, existing cache entries are aged out to make room. This can result in a lot of entries being kicked out in order to load the new statement, as a contiguous chunk of memory is required for it in the library cache.

If you're loading a big stored procedure for the first time and there's not enough space for it, the subsequent ageing out of existing entries can take a *long* time, as a lot of existing entries will need to be removed. During this period the library cache latch will be held, so all other parse calls will be blocked.

If your shared pool is too small to hold the majority of your SQL statements at any given time, then new parse calls by applications will continually cause existing entries to be aged out. Then when the aged out statements are needed again, the new statements are themselves aged out. This can quickly lead to the situation where Oracle is doing nothing but parsing SQL and ageing out library cache entries. Your applications will grind to a halt.

I've seen this happen on a major Oracle system. The answer is to make sure your shared pool is big enough to hold most of your statements at a given point in time. As well as increasing the size of the shared pool, you should look at the library cache entries to make sure that your applications are making efficient use of the shared pool. Make sure that SQL statements use bind variables and *not* literal values. This can make an enormous difference (order of magnitude) in the size of shared pool needed to hold all your statements at a point in time.

SQL*Forms 3 and many 3rd party products are poor in this area. Oracle Forms 4.x is very good.

If you have some very large stored procedures, you should look at pinning them in the shared pool, so that they don't ever get aged out. This avoids a disproportionately large number of other library cache entries being aged out (to get a contiguous chuck of memory of sufficient size) whenever the procedure is loaded.

Regards
Tony. Received on Wed Feb 15 1995 - 23:42:14 CET