Home » RDBMS Server » Server Administration » library cache ?
library cache ? [message #128176] Fri, 15 July 2005 10:08 Go to next message
Hina
Messages: 51
Registered: April 2004
Member
Following query is giving 97.18

Do I need to increase library cache size ?


Also, is it not shrink automatically, once its reach on optimal size.

select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;

Thanks
Re: library cache ? [message #128203 is a reply to message #128176] Fri, 15 July 2005 12:07 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi

Please Go through below written thing

TUNNING SHARED POOL
*********************

Shared pool is divided in to two parts
--> Library cache
--> Dictionary cache

Show parameter Shared_Pool_Size

SQL>col value format 999,999,999,999 heading "Shared Pool Size"
col bytes format 999,999,999,999 heading "Free Bytes"
SELECT to_number(v$parameter.value) value,
v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
FROM v$sgastat, v$parameter
WHERE v$sgastat.name = 'free memory' and
v$parameter.name = 'shared_pool_size' and
v$sgastat.pool='shared pool';

·--> Shared pool should not be free.
·--> In ideal case it should be nearly 4-5 % free, free below it will apply LRU.
·--> It is good if it is filled.

----------------------------------------------
TUNNING LIBRARY CACHE (USE IN REUSABILITY)
----------------------------------------------
There is no parameter to define Library cache Size.
Goal
·--> Reusability of SQL.
·--> Increase hit Ratio.

There are three tables which can be used for performance of Library cache.
· V$SQLAREA
· V$SQLTEXT
· V$LIBRARY CACHE
---------------------------------------------
To find library cache hit ratio
SQL>SELECT sum(pins) / (sum(pins)+sum(reloads)) * 100 "Cache Hit Ratio" FROM v$librarycache;
---------------------------------------------

Oracle standard is more than 95%.<-----
If hit ratio is less Solution is increase size of shared pool.

SQL> des V$LIBRARYCACHE
GETHITRATIO & PINHITRATIO should be greater than 95% and if it is not then oracle is not going to do caching.

SQL>NAMESPACE, GETHITRATIO, PINHITRATIO, FROM V$librarycache;
GETS: - The # of lookups for object of the namespace.
PINS: - The # of reads or execution of the object.
RELOADS: - cache miss.

Reasons For library cache miss,
1. Oracle is applying LRU
2. INVALIDATIONS

What is invalidation?
If already stored SQL in library cache is marked as invalid.

Why this happen?
Whenever objects involved in a SQL are identified after SQL is loaded into library cache.

Why pinning important PL/SQL and simple SQL into library cache?
So that oracle does not apply LRU to important SQL.
PL is stored in Database and SQL is stored in Application.

----------------------------------------
Keeping important PL/SQL code in memory.
----------------------------------------
/rdbms/admin/dbmspool.sql

SQL> sys/manager as sysdba
Sys is owner of data dictionary.

SQL> @E:\oracle\ora90\rdbms\admin\dbmspool.
By this DBMS_SHATED_POOL Package created

To find out procedure stored in library cache there is a table called V$DB_OBJECT_CACHE
SQL> select OWNER, NAME, NAPESPACE, TYPE, SHARABLE_MEM, KEPT FROM V$DB_OBJECT_CACHE WHERE OWNER = ‘SAP’;

To PIN procedure in library cache.
SQL>Execute dbms_shared_pool.KEEP (‘SAP.TEST’);
TO unkeep procedure from a shared pool

SQL>Execute dbms_shared_pool.UNKEEP (‘SAP.TEST’);
To mark SQL statements as keep in library cache there is a hint called KEEP.

SQL>Select /*+ KEEP +*/ * from dept;
For not keeping SQL in memory

SQL>Select /*+ UNKEEP +*/ * from dept;

BIND variable: - We should bind variable in a select statement whenever selection criteria is changing.

TUNNING DICTIONARY CACHE
Dictionary cache stores details of Data dictionary.
Dictionary cache hit ratio is there for performance tunning
Hit ratio should be greater than 95%.

SQL>SELECT (1-(sum(getmisses)/sum(gets))) * 100 "Dict Cache Hit Ratio" FROM v$rowcache;

We have to use v$rowcache table, it has many columns but 2 are
1. Getmisses
2. gets

Solution if dictionary cache hit ratio is less than 95% increase size of shared pool.

I hope you got every thing regarding Smile library cash and dictionary cash

From:-- Sunil

Re: library cache ? [message #128219 is a reply to message #128203] Fri, 15 July 2005 14:59 Go to previous messageGo to next message
Hina
Messages: 51
Registered: April 2004
Member
My database showing following result at ideal time.

Is it correct ?

  1* select NAMESPACE, GETHITRATIO, PINHITRATIO FROM V$librarycache
SQL> /


NAMESPACE       GETHITRATIO PINHITRATIO
--------------- ----------- -----------
SQL AREA         .881074361  .974583383
TABLE/PROCEDURE   .81842133  .870695453
BODY             .949506664  .990289128
TRIGGER          .793558401  .985128893
INDEX            .133333333  .571019473
CLUSTER          .968535959  .978953633
OBJECT                    1           1
PIPE                      1           1
JAVA SOURCE               1           1
JAVA RESOURCE             0           0
JAVA DATA        .986111111          .6
Re: library cache ? [message #128302 is a reply to message #128219] Sun, 17 July 2005 08:48 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

Please see the result of my query which is as below... value of Cache hit ratio

SQL> SELECT sum(pins) / (sum(pins)+sum(reloads)) * 100 "Cache Hit Ratio" FROM v$librarycache;

Cache Hit Ratio
---------------
99.9786313


And as per your query you can see the result

SQL> select 100*(sum(pins)-sum(reloads))/sum(pins) from v$librarycache;

100*(SUM(PINS)-SUM(RELOADS))/SUM(PINS)
--------------------------------------
99.9786285

that means you where also finding the Cash hit ratio it should be more than 95 % as per oracle ... u r getting 99% that means every thing is fine..

From :- Sunilkumar Mishra

[Updated on: Sun, 17 July 2005 08:48]

Report message to a moderator

Re: library cache ? [message #333564 is a reply to message #128302] Sat, 12 July 2008 15:51 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

hi all,

let me ensure one thing. should library cache hit ratio be more than 95% for oracle standard or more than 80%? because as you are saying that library cache must be more than 95% hit ratio for oracle standard. but i find in some forum in orafaq somebody saying that library cache must be more than 80% if it is less ,then increase shared pool.


Please correct me.bcoz currently in my database, library cache hit ratio is 93%(which is below 95%).should i have to tune my library cache?

Re: library cache ? [message #333579 is a reply to message #333564] Sun, 13 July 2008 01:13 Go to previous message
Michel Cadot
Messages: 68726
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Check Performances and Tuning guide.

Regards
Michel
Previous Topic: Alerts or Errors Stored in which table or vies?
Next Topic: Audit files
Goto Forum:
  


Current Time: Sat Dec 14 00:10:31 CST 2024