Your response indicates that you are attempting to micro-manage the
buffer pools. This is a very time consuming, expensive, and frequently
futile tuning effort. Unless your buffer pool setup is a disaster; I'd
be surprised if you could make a 10% improvement in performance.
Application SQL statement design is usually a much larger factor in
overall performance. Bad design (both application SQL and supporting
database architecture) will also distort buffer pool usage.
See additional comments below.
Phil Sherman
Paul wrote:can improve performance by orders of mangitude
Hi
It's only for information and can be useful when tuning SQL statements
I can determine if a table is everytime in the bufferpool so i can create a
new bufferpool for this one
Today the table may be in the buffer pool but tomorrow's workload may
change this.
I know i can find this by other commands but it's more difficult
Also i can tell quickly if the bufferpool is too large if we can know this
information
Buffer pools have to be gigantic to be too large. Most reasonably sized
systems use a dedicated database server where all available memory is
available so support database needs.
i know i can use snapshot to view logical vs physical reads but it don't
tell me
how much space i can reduce from the bufferpool .
There is no tool that will tell you when you have "properly" minimized
the buffer pool. A DBA with a number of years of tuning experience and
some knowledge of how your applications work and your performance
objectives should know how to size a buffer pool. Performance tuning
classes and books make an excellent starting point.
A 10Go database may only need 100 MB bufferpool but at first we may size the
bufferpool at 1GB
Instead if we know the % used of the bufferpool by simple order, we can size
the bufferpool more quickly by
capturing the values every hour and during typical workload
This statement implies that you may be attempting to use separate buffer
pools for each table. There are valid performance reasons to do this -
where there is a real benefit - but I doubt it would apply to "a simple
order's" tables.
Paul
