Help needed for determing the writes per second

john king

Help needed for determing the writes per second
Hi All DBA Experts. Challenging work is nothing for you. I checked the archives, but I could not get the any ionformation regarding this topic. Could any one help me or suggest how to proceed.I m sending the details.
I m in a development project and I wrote all sqls. I had taken all the explian information for each type of sqls. Like the sqls explian oinformation show me that which are going to be sequential access and random accesses. I can separate out the sequential as well random access. I came to know the work load for the system by looking at the agreement.I mean like the kind of transcation daily basis. It seems they have more random accesses daily than sequential access. The sqls which gives sequential accesses are to be run weekly. In this situation how I would determine the writes per second so that I could size the buffer pool. The buffer pool is right now with default sizes with three buffer pool one for tablespaces , one for indexes and another one for sort. How to arrive at this writes per second so that i could calculate the sizes.Shall I have to check the number of get pages each query takes per transactions and to check what kind of transcation that is. Can any one suggest me I m going in right direction or not. I have only Craig's book for reference.

Waiting for your suggestions.

John




Sanjeev (CTS) S

Re: Help needed for determing the writes per second
(in response to john king)
John,

You are very correct in determining the activities and correlating it to the
bufferpool configuration. But at the end i am finding you have pools for
tablespaces,indexes and sort. At this stage( looks to me the begining), at
the max you shall try to configure your pool on the basis of activities on
indexes and tablespaces(i.e. random and sequential). First few days or
months of activities with good workload should give you the idea of how to
readjust the parameters. You should start with default thresholds. This is
required to create a benchmark where you will start the tuning from. If at
all system works best with the default parameters then it should be allowed
to run for some time. Most of the time we think we can make pool best at the
begining and it will not require further tuning but i believe we should move
up in the tuning criteria slowly and with going time and growing workload.

Also at the begining of the project i think workload as well as data will
not be much, so keep monitoring the pools is what i am going to do at this
stage.

Hope this helps....or someone may have different thoughts.

With Best Regards,
Sanjeev

> -----Original Message-----
> From: john king [SMTP:[login to unmask email]
> Sent: Saturday, January 05, 2002 4:18 PM
> To: [login to unmask email]
> Subject: Help needed for determing the writes per second
>
> Hi All DBA Experts. Challenging work is nothing for you. I checked the
> archives, but I could not get the any ionformation regarding this topic.
> Could any one help me or suggest how to proceed.I m sending the details.
> I m in a development project and I wrote all sqls. I had taken all the
> explian information for each type of sqls. Like the sqls explian
> oinformation show me that which are going to be sequential access and
> random accesses. I can separate out the sequential as well random access.
> I came to know the work load for the system by looking at the agreement.I
> mean like the kind of transcation daily basis. It seems they have more
> random accesses daily than sequential access. The sqls which gives
> sequential accesses are to be run weekly. In this situation how I would
> determine the writes per second so that I could size the buffer pool. The
> buffer pool is right now with default sizes with three buffer pool one for
> tablespaces , one for indexes and another one for sort. How to arrive at
> this writes per second so that i could calculate the sizes.Shall I have to
> check the number of get pages each query takes per transactions and to
> check what kind of transcation that is. Can any one suggest me I m going
> in right direction or not. I have only Craig's book for reference.
>
> Waiting for your suggestions.
>
> John
>
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]