In my opinion you probably have more buffers than you effecively use:
The buffer pool hit ratio is unimportant in itself. What counts is how
well your application runs. If it is running well a hit ratio of 70% is
great, and if it is running slow then a hit ratio of 90% is not good
enough. How many free buffers do you have on average and what are your
buffer pool latch hit ratios? These will give you a better indication
of how effecively application is using the buffer pool. I believe that
the OS and your Oracle shadow processes could make better use of most
most of the memory your are allocating to the buffer pool. Here is an
Oracle note that states that for ver 7.3.4 and 8 that the statistics
used to calculate the buffer pool hit ratio are invalid to begin with.
Doc ID: Note:33883.1
Folder: Tuning, Optimization and Other Performance Issues
Topic:
Subject: STATISTIC: V7+8 DERIVED "cache hit ratio"
Creation date: : 14-FEB-1996
Last Revision Date: 02-APR-1999
Product: Oracle Server - Enterprise Edition V7
NB: This is a DERIVED statistic.
Based On: <Statistic:Db_Block_Gets>
<Statistic:Consistent_Gets>
<Statistic:Physical_Reads>
Definition:
The hit ratio gives an indication of how often the
various processes accessing the data buffers find the blocks
in cache.
Calculation:
Cache Hit Ratio = 1 - Physical Reads
---------------------------------
(db block gets + consistent gets)
This is generally expressed as a percentage.
Comments:
- This should generally be >80%
There is probably still scope for tuning if it is <90% *BUT*
note that the hit ratio is not the best measure of performance.
- The ratio can be artificially high in applications making
poor use of an UNSELECTIVE index. Eg: constantly scanning
a 'yes/no' index accesses the same blocks over and over
for the wrong reason.
- In Oracle 7.3.4 & Oracle8.0 a change was made to the
"physical reads" statistic such that it also includes
"direct reads" and hence the above formula is incorrect
in these releases.
- In Oracle8.1 "Direct reads" are recorded so the above
formula can be ammended to deduct "Direct reads"
from "physical reads".
- In Oracle8 the query below can be used to determine the
hit ratio for each buffer pool. In 7.3.4 there is no
reliable method of computing the ratio.
- Oracle8 allows multiple buffer pools which can be
looked at individually using <View:V$BUFFER_POOL_STATISTICS>
which is created by the CATPERF.SQL script.
Note:
Some documentation incorrectly reports hit ratio to be:
Hit Ratio = Logical Reads / ( Logical Reads + Physical Reads )
Related: <Statistic:Logical_Reads>
<Parameter:DB_BLOCK_BUFFERS>
<Parameter:BUFFER_POOL_KEEP>
<View:V$BUFFER_POOL>
> "opus" <opus_at_nospam.ca> wrote:
> I'm not a huge expert so I should clarify that right away but from
what you
> describe here's the thing.
>
> 1.You have 4 instances correct, with 1 Gig of memory on the server.
> 2. The SGA for the largest instance you have takes up half that gig of
> memory at least.
> 3. You say this is double the other 3 combined, okay, let's just say
all the
> other instances
> take up 400M of memory (that's 80% of the largest when combined) okay?
> 4. Forget about the cache hits (db_block_buffer) for a sec here,
Oracle
> SGA's are taking
> up your entire memory space of 1 Gig already, there's no memory left
for
> anything else and that
> includes the processes required by the O/S to even run. This means
that as
> a new process is required, let's say by one of your users or
applications,
> it must swap out the entire SGA for one of your instances to disk,
run that
> process, then probably swap more processes out to bring back the SGA
to
> service your request. This is a HUGE, and I mean a HUGE performance
hit.
> 5. My recommendation here if you don't buy more memory and I'd try
this
> sooner rather than later, is to dramatically decrease your SGA sizes
period.
> If someone disagrees with that here then that's what were all here
for I
> figure and we can talk about it more.
> 6. I think yo may have hit upon your other cache hit problem there
too,
> comples queries using most or all of the data so you may never
improve that
> hit ratio anyway. I think someone here already mentioned fidning out
what
> queries access what but I think this is secondary for your more
immediate
> problem for now of no memory.
> 7. If you're not the Unix admin someone there should be and they
should be
> able to tell what's going on with O/S swapping etc .... then you can
> confirm that this is happening as I describe to be sure.
>
> Hope that helps some more, good luck,
> Opus
>
> <junderhi_at_my-deja.com> wrote in message news:8eq2o8
$ris$1_at_nnrp1.deja.com...
> > To clarify...
> >
> > Our R50 supports 4 instances, true. But, the large instance I
> > describe is almost double the size of the other 3 combined. I'll
take
> > in mind your comments, but would also like to know if you have
> > additional input provided the extra info I have provided. We see
> > similar problems in the hit ratio even for our smaller clients. I
> > imagine this is due to the rather high complexity of the queries and
> > that they do indeed hit nearly all of the tables available (not at
once,
> > heh).
> >
> > In article <sh0v174fa4n68_at_corp.supernews.com>,
> > "opus" <opus_at_nospam.ca> wrote:
> > > I'm with Sybrand on this one .... there isn't near enough memory
for
what
> > > you describe and you must be experiencing some serious swapping
by the
O/S.
> > > You have one instance where the block buffer size is half a gig
and
you
> > > still run 3 other instances? I run 3-4 instances on a similar
...snip.....
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu May 04 2000 - 00:00:00 CDT
