Library cache below 70% always!!

[jlakhani]

in my environment..library cache is always below 70%. i have increase the share pool size higher b ut it has not made any difference. can anybody give me suggestions?

thanks
Jigar
8:05 NT 4

[ggnanaraj]

What is the result of the following query during peak usage ?

select * from v$sgastat where name like '%free%';

select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;
 
select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache"  from v$rowcache;

If free memory is close to 0 and either the library
cache hit ratio or the row cache hit ratio is less than 0.95, then increase the shared pool until the ratios stop improving.

HTH

[Sureshy]

also consider pinning freqeuntly accesed packages into shared pool.

[jlakhani]

hello. currently this is my stats..
----------------------------------------------------------------
Get Hit Ratio for SQL AREA = 69.68
Get Hit Ratio for TABLE/PROCEDURE = 99.73
Get Hit Ratio for BODY = 99.52
Get Hit Ratio for TRIGGER = 99.91
Get Hit Ratio for INDEX = 0
Get Hit Ratio for CLUSTER = 99.82
Get Hit Ratio for OBJECT = 100
Get Hit Ratio for PIPE = 100
SQL> select * from v$sgastat where name like '%free%';

POOL NAME BYTES
----------- -------------------------- ----------
shared pool free memory 70180284

SQL> select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

Lib Cache
----------
.994586396

SQL> select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;

Row Cache
----------
.999683867



i dont get it?

[jlakhani]

any ideas?

[pando]

spam
close

[jlakhani]

Pando,
what do you mean by Spam close?

[pando]

I am telling doont spam, what's you point posting a post every hour?

[ggnanaraj]

Originally posted by jlakhani
SQL> select * from v$sgastat where name like '%free%';

POOL NAME BYTES
----------- -------------------------- ----------
shared pool free memory 70180284

SQL> select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

Lib Cache
----------
.994586396

SQL> select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;

Row Cache
----------
.999683867

Hi

Your db is ok. No probs.

Your library cache & row cache hit ratio is above 95%. So, also you have sufficient amount of free memory.

Cheers.