Re: Library Cache namespaces

RogBaker_at_gmail.com wrote in
news:1117819806.078392.224650_at_g44g2000cwa.googlegroups.com:

> Oracle 8.1.7.4
> I am confused by the different namespaces in the Library Cache. I did
> a statspack report, and this is what I see:
>
> Library Cache Activity for DB: FSQT Instance: fsqt
> Snaps: 3 -4
> ->"Pct Misses" should be very low
>
> Get Pin Inva-
> Requ Pct Requ Pct Re lidat
> Namespace sts Miss ests Miss loads ions
> --------------- ----- ---- ----- ----- ------ -----
> BODY 4 0.0 4 0.0 0 0
> CLUSTER 0 0 0 0 0
> INDEX 0 0 0 0 0
> OBJECT 0 0 0 0 0
> PIPE 0 0 0 0 0
> SQL AREA 110 0.0 69,672 0.0 0 0
> TABLE/PROCEDURE 182 17.0 237 53.6 0 0
> TRIGGER 0 0 0 0
> --------------------------------------------
>
> As I understand it, the Library Cache is used to hold parsed SQL
> statements so that they can be reused without hard parsing to reduce
> CPU. I am concerned about the 17% and the 53.6% for the
> TABLE/PROCEDURE. I am not sure what this means. If you do a SELECT
> ENPNAME, DEPT FROM EMPLOYEEDATA WHERE EMPID=567, does this go into
> the SQL AREA or the TABLE/PROCEDURE AREA? The docs I have been
> reading only seem to list the namespaces and do not provide any
> further details or information. Does it look like we need a bigger
> library cache/shared pool? I believe we are using bind variables for
> the most part.
>
>
> Thanks,
>

There's more in the shared pool than just SQL. It also caches pl/sql, triggers, meta data about tables and indexes, etc. You may be able to decrease the misses by pinning frequently used objects in the cache. Have a look at the DBMS_SHARED_POOL.KEEP procedure. Don't worry too much about tables as in 8i I don't think they can be pinned.

Oracle has a few bulletins on Metalink about using DBMS_SHARED_POOL.KEEP that you should read. Just do a Metalink search. Received on Fri Jun 03 2005 - 14:31:47 CDT