Optimize your Tablespaces and Bufferpools-2: Tablespace Sizing

Hey people...
So I hope the last post was informative... This post is going to be rather short... Not cause of the topic, but cause of my lack of knowledge on this matter... I could have very well knocked it off, but thought of giving some basics (to which my knowledge is restricted) from where people can carry on with google...

We will discuss sizing of tablespaces and their separation... We will wind up with little on temp tablespaces...

If as a DBA you tend to think, you can allocate any space, on your disk for your tablespaces and allow them to grow unlimited by any fraction as incremental size, and can sit back... You are damn wrong... Things that can cause problems...

1) If you allocate even initial size (which may be too huge) for all the tablespaces, the table spaces which has too frequent writes might face issues like allocation of new extents and which might be too scattered cause initial allocation was not thought properly...

2) Same applies if you think allocation for every tablespace should go at same pace... You might end up killing the space requirements for tablespaces which are too frequently accessed for writes... Moreover you will be facing issues with cost for storage (sooner or latter)...

3) Also you need to consider the fact that, some tables will have very sequential access and the tablespace having those tables, should have a more incremental size otherwise your data is going to scattered and prefetching suffers...

4) Static data and configuraiton data needs to have a medium allocation at starting and the increment window need not be too huge as very rarely you add new configurations to the system and very rarely you access configuration in sequence (well this might not be true always)...

Now wat is the way out??? This is something I have learnt from my seniors at work...

1) List the tables, and list the tablespaces they belog to. List the tables' row size and its cardinality as well

2) Sum up the above data per tablespace and see at what rate the data increases for every table

3) Combine all these inputs to decide, what should be the initial size (do not keep it same as current data's requirement at least keep 10%-50% extra space depending on rate at which data grows) and what should be the increment size...

This will really help you get rid of time lags for your write operations...

Next is to place all such tables together in a tablespace, where you think there will be sequential reads... Prefetching will benefit from this (as long as you remember to schedule a reorg at a proper frequency)... Tables which has static data which hardly gets updated/inserted should go into one tablespace (which should be reorganized at initial setup)... We will see more about bufferpool planing in upcoming posts...

Last... People tend to ignore the importance of system temporary tablespace and user temporary tablespace...

It is important to separate these two and explicitly create a new user temp tablespace, this will really help your sort operation which can have more freedom of "space" in system temp tablespaces... All other temp processing might as well benefit... Also, you should consider keeping a separate bufferpool for user temporary tablespace... Also, if you have not allocated enough space for you temp tablespace, it will try to reallocate/acquire space for sort operations, this will cause a delay in result for your sorting operations, which is quite frequent in application we see these days (patient enquiry sorted by admit date or if you remember the transaction order in your bank statement)...

So the mantra is to think wisely and group your tables in proper tablespaces, then size them and decide proper growth for them... Meanwhile do no forget the reorganization of tables and indexes (frequently) and of tablespaces at times... Meanwhile never ignore the importance of temp tablespaces...

I will be back with some tips on deciding proper strategy for your bufferpools and how to make them do the best according to your tablespace and tables contained by it...

Meanwhile I have got a some content on backup recovery... Planning to write it some time...