DB2 LUW Buffer Pool Tuning (2)

Joel Goldstein

DB2 LUW Buffer Pool Tuning (2)
Hi Bill,

Just as another thought here, snapshots from online monitors are mostly
useless when looking at pool activity.
You need data for some period of time, at least 15 mins.
Regards,
Joel

Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard

----- Original Message -----
From: <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, January 13, 2006 3:22 PM
Subject: [DB2-L] DB2 LUW Buffer Pool Tuning


> Dear List:
>
> Is there an easy way (I know nothing is really easy) to look at DB2
> snapshots for buffer pools to determine what percentage of read requests
> from the buffer pool are random versus sequential? At first glance I'm
> speculating that synchronous reads would typically be random (i.e. one
> page
> at a time) where asynchronous reads would be sequential (i.e. employing
> prefetch).
>
> I'm looking at some snapshots, and nothing is really jumping out at me as
> being an obvious way to determine random vs. sequential access of
> tablespace pages from a bufferpool.
>
> To take it a level deeper, is it possible to get this information at a
> tablespace level?
>
> Thanks,
>
> -------------------------------------------------------
> Bill Gallagher
> Database Administrator
> IT / Common Services
> Phoenix Life Insurance Company
> 860-403-6327
> [login to unmask email]
>
>
> ***********************************************************************************
> CONFIDENTIAL: This communication, including attachments, is intended only
> for the exclusive use of addressee and may contain proprietary,
> confidential and/or privileged information. If you are not the intended
> recipient, you are hereby notified that you have received this document in
> error, and any use, review, copying, disclosure, dissemination or
> distribution is strictly prohibited. If you are not the intended
> recipient, please notify the sender immediately by return e-mail, delete
> this communication and destroy any and all copies of this communication.
>
> ***********************************************************************************
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Martin Hubel

Re: DB2 LUW Buffer Pool Tuning
(in response to Joel Goldstein)
Hi Bill,

I'll give you a short answer, because my understanding of DB2-L is to provide quick answers:

1. DB2 LUW shows total logical and physical I/O. It shows asynchronous I/O - this means prefetch (sequential)

2. To get the random (synchronous) figures, subtract asynchronous from the total figure. Synchronous is not reported separately.

3. It is extremely helpful in V8 to define a block-based area in a BP for the sequential I/O.

4. For more info, pleased feel free to attend my presentation at IDUG in Tampa.

Cheers--Martin

>> Dear List:

>> Is there an easy way (I know nothing is really easy) to look at DB2
>> snapshots for buffer pools to determine what percentage of read requests
>> from the buffer pool are random versus sequential? At first glance I'm
>> speculating that synchronous reads would typically be random (i.e. one
>> page
>> at a time) where asynchronous reads would be sequential (i.e. employing
>> prefetch).

>> I'm looking at some snapshots, and nothing is really jumping out at me as
>> being an obvious way to determine random vs. sequential access of
>> tablespace pages from a bufferpool.

>> To take it a level deeper, is it possible to get this information at a
>> tablespace level?

>> Thanks,

>> -------------------------------------------------------
>> Bill Gallagher
>> Database Administrator
>> IT / Common Services
>> Phoenix Life Insurance Company
>> 860-403-6327
>> [login to unmask email]


>> **************************************************************************
>> ********* CONFIDENTIAL: This communication, including attachments, is
>> intended only for the exclusive use of addressee and may contain
>> proprietary, confidential and/or privileged information. If you are not
>> the intended recipient, you are hereby notified that you have received
>> this document in error, and any use, review, copying, disclosure,
>> dissemination or distribution is strictly prohibited. If you are not the
>> intended recipient, please notify the sender immediately by return
>> e-mail, delete this communication and destroy any and all copies of this
>> communication.

>> **************************************************************************
>> *********

>> --------------------------------------------------------------------------
>> -------
>> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>> home page at http://www.idugdb2-l.org/archives/d b2-l.html. From that page
>> select "Join or Leave the list". The IDUG DB2-L FAQ is at
>> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>> [login to unmask email] Find out the latest on IDUG conferences
>> at http://conferences.idug.org/index.c fm







===========
Martin Hubel
MHC Inc.
www.mhubel.com
+1 905-764-7498
+1 416-670-7498 Mobile
+44 7986 041838 UK Mobile
+1 905-764-8411 Fax

Check out www.db-hq.net Your Headquarters of How-To for DB2
=========== --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm