Re: Library Cache Pin problems

Not sure what 93% pins means, actually.

I imagine we are talking about the pins-to-reloads ratio, and I guess your results imply 93% pins and 7% reloads. Since reloads are supposed to be no more than 1% of pins, you have a problem (potentially).

In simple terms, a pin is the execution of a SQL statement. If 7% of your SQL Statements are having to be "reloaded" (ie, re-parsed) before execution, it's because they've been aged out of the Library Cache because there's not enough room to store them all.

Therefore, a possible solution is to increase the size of your Library Cache -which you can't do on its own, but only by increasing the SHARED_POOL_SIZE parameter.

Other potential issues are: are you using the dbms_shared_pool.keep package/procedure to permanently fix particularly important execution plans in the Library Cache? Is your shared_pool_reserved_size parameter set adequately?

And finally, it's no good increasing the size of the shared_pool if doing so were to increase the amount of O/S paging that takes place, because now the SGA is too big to fit comfortably in memory. Without knowing the exact memory specifications, it's always dangerous to say 'increase parameter X'.

You might care to pay www.oraperf.com a visit. Once you've registered, you can upload your statspack report to the site, and have Anjo's scripts give it the once-over: you end up with quite a thorough analysis of the report, and plenty of suggestions as to what can be tuned, and how. And it's all free!!

Regards
HJR "ecki" <eckard.biskup_at_gmx.de> wrote in message news:60600e9b.0209190413.6c1daca0_at_posting.google.com...
> Hi all,
>
> I got some problems with Oracle and Library Cache pin's.
>
> Situation:
> ==========
> Oracle version: 8.1.7.4
> OS: Solaris 8
> Hardware: Sun Fire 15000
> Total size of database: 2,5 TB
> Involved Application: Specific ERP-Application with Oracle Backend
>
> Sometimes the database got in trouble with library cache pin's (93%
> within Stats Pack). All transactions which were running at this time
> ran very slow. The reason for that situation is likely unclear. But my
> question is, even if the ERP-application generates the problem, what
> can be done to prevent this situation in general?
>
> Thanks for your help
>
> Eckard
Received on Thu Sep 19 2002 - 19:03:01 CDT