| library cache ? [message #128176] |
Fri, 15 July 2005 10:08  |
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  |
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  library cash and dictionary cash
From:-- Sunil
|
|
|
|
| Re: library cache ? [message #128219 is a reply to message #128203] |
Fri, 15 July 2005 14:59 |
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 |
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
