| What can I do to increase my Library Cache Hit stats [message #293043] |
Thu, 10 January 2008 13:04  |
aidi-h
Messages: 45
Registered: November 2005
|
Member |
|
|
I was wondering if anyone can help me out regarding the values that I am getting for my Library Cache hits stats
Half of the samples that I have taken on a periodic interval today have ranged from 89% to 96%.
The SQL that I have used is,
SELECT
sysdate,
SUM(PINS-RELOADS)/SUM(PINS)*100
from v\$librarycache
;
Also, Running the AWR report for 4am to 4pm, see below
Shared Pool Statistics AWR report
Begin End
Memory Usage %: 50.83 42.43
% SQL with executions>1: 55.56 77.13
% Memory for SQL w/exec>1: 74.12
Regarding the current SGA settings,
SQL> show parameter sga_target;
NAME TYPE VALUE
------------------------------------ ----------- -------------
sga_target big integer 1184M
SQL>
SQL> select pool,name,bytes/1048576 "Size in MB" from v$sgastat where name = 'free memory';
POOL NAME Size in MB
------------ -------------------------- ----------
shared pool free memory 135.742641
large pool free memory 15.9389648
java pool free memory 16
The main questions are,
a) is the low Library cache hit ration particularly low?
b) if I want to improve this figure, it is advised that the 'SHARED_POOL_SIZE' parameter should be increased.
Obviously Oracle itself is in charge of this at present, so what can I do to improve?
c) Are there any really good links to help me to understand the figures that appear in the AWR report.
|
|
|
|
| Re: What can I do to increase my Library Cache Hit stats [message #293051 is a reply to message #293043] |
Thu, 10 January 2008 13:48  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
sometimes Don Burleson has some good stuff. Try this:
http://www.dba-oracle.com/m_library_cache_hit_ratio.htm
from one of the links at this url:
The Oracle Documentation notes on the library cache:
| Quote: |
"The library cache Oracle metric holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code.
When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit.
If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss."
|
Personally, I have never tuned using hit ratios like this. Not because I don't like them, just because they never really made sense to me (too hard to figure out what they mean (guess that might be why you are asking a question eh?)). Anyways, I always start with the basics.
In your case, I figure the real reason you are asking this question of because you are trying to figure out if you are making efficient use of your shared_pool. Efficient use of shared_pool almost always means, make use of bind variables. This then leads to the question, ?how can I tell if I have a bind variable issue?.
Try runing this sql and see if anything strange pops out at you.
select sql_text
from v$sqlarea
where sql_text not like '%$%'
and sql_text not like '%#%'
and sql_text not like '%:%'
order by 1
/
Not exactly an automated way of doing business, but it works for me. You are looking for what is basically the same statement with just a few simple differences.
Let us know what you find. Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
