| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How do I know when my Shared Pool is the right size ?
Well, your statspack should tell you that. If you have a huge number of
library cache misses or invalidations, v$rowcache misses are growing and
free space ("shared pool free memory") is always below
shared_pool_reserved_size
(area that was left a side for big operations), then you have a problem.
I believe that the cache hit rate for the shared pool is very well
described in
the literature. Here are some helpful queries:
select * from
v$sgastat
where name in
('free memory', 'db_block_buffers','log_buffer',
'dictionary cache','sql area', 'library cache');
select 'dc_hit_ratio' ratio, 1 step_order,(sum(getmisses)/sum(gets))*100
from v$rowcache
union
select 'lc_hit_ratio' ratio, 2 step_order, (sum(reloads)/sum(pins))*100
from v$librarycache
union
select 'bc_hit_ratio' ratio, 3 step_order,( sum(decode(name,
'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0)))/ ( sum(decode(name, 'consistent gets',value,0))
+ sum(decode(name,'db block gets', value,0)) ) * 100
from v$sysstat
union
select 'roll_ratio' ratio, 4 step_order, round((sum(waits) / (sum(gets)
+ .00000001)) * 100,2)
from v$rollstat
union
select 'w2wait_ratio' ratio, 5 step_order, (l.misses/l.gets)*100
from v$latch l,v$latchname n
where n.name in ('redo allocation')
and n.latch# = l.latch#
order by 2
These two queries were taken from the tool called ORAC_DBA, written by
the owner of this
list and Andy Duncan.
On 2003.05.15 04:21 Waleed Haggagy wrote:
> > Hi > > How do I know when my Shared Pool is the right size?? > And Will increasing the size of my Shared Pool always bring benefits? > > > Waleed Haggagy > Cairo and Alexandria Stock Exchanges > 4 A El Sherifein St. Cairo Egypt > Postal Code 11513 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Waleed Haggagy > INET: WHaggagy_at_EgyptSE.com > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
-- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: mgogala_at_adelphia.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu May 15 2003 - 08:01:50 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |