By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

bufferpool content

P: n/a
Hi,
is there any way to know the content of the bufferpool :
- space usage not present in snapshot
- tables actually cached in and number of pages, same for index ...
thx
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
While this is an interesting question more interesting ones are:
What's the value of this information?
Why are you interested in it?

Bufferpool contents change rapidly with changing access to data. If your
database is larger than the buffer pool, then eventually (almost) all of
the pool will contain data. Relational systems do this to make as much
data as possible available without resorting to disk access. Assuming
you could measure the items you requested; the values you obtain may
change dramatically within a few seconds.
Phil Sherman

Paul wrote:
Hi,
is there any way to know the content of the bufferpool :
- space usage not present in snapshot
- tables actually cached in and number of pages, same for index ...
thx


Nov 12 '05 #2

P: n/a
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
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
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 .
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

Paul
Nov 12 '05 #3

P: n/a
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


Nov 12 '05 #4

P: n/a
Thx for your time even i already know this
maybe you consider me begginer but i'm not (without modesty)
i like to have control over things and maybe you consider this futile for
bufferpool, i don't think so
i certainly agree that it's can be time consuming whereas optimised sql if
much better but at my work
we have server with more than 10 databases (price reduction you know and
without the possibily of sql rewritintg) and it's not easy to deal with
memory
for me it's just another way i can explore to provide better performances
for db but maybe it's too low level if ibm don't provide any api to do this
....

Nov 12 '05 #5

P: n/a
Ian
Paul wrote:
Thx for your time even i already know this
maybe you consider me begginer but i'm not (without modesty)
i like to have control over things and maybe you consider this futile for
bufferpool, i don't think so
i certainly agree that it's can be time consuming whereas optimised sql if
much better but at my work
we have server with more than 10 databases (price reduction you know and
without the possibily of sql rewritintg) and it's not easy to deal with
memory
for me it's just another way i can explore to provide better performances
for db but maybe it's too low level if ibm don't provide any api to do this
...


It is possible to tune the bufferpools without knowing what is in them,
although it's generally somewhat of an "informed" trial an error
approach, i.e. keep an eye on bufferpool performance (don't consider
hit ratio only!) as you change (either increase or decrease bufferpool
size), and you'll find the "optimal" bufferpool size.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.