Alternate Buffer Pool

jdpjamesp

jdpjamesp

ProgressTalk.com Moderator
Staff member
Silly question, but how does one mark a table to be in the alternate buffer pool?
 
Ah yes that's the one. Thanks. If I move a table and its indexes to the alternate pool, will I need to restart the db for it to take effect?
 
Thanks Rob. Definitely don't want to do that with our structure, but it's good to know.
 
That's a fair point, Tom, and it's not particularly big either! :)
Does -B2 have a default value, or does 0 really mean 0?
 
Zero means 0

The ideal candidates for B2 are small, active and do not grow. B2 should be sized to completely hold them so that the LRU mechanism is never engaged.
 
TomBascom said:
Zero means 0

The ideal candidates for B2 are small, active and do not grow. B2 should be sized to completely hold them so that the LRU mechanism is never engaged.
Click to expand...

Thanks Tom. We have a table with 208 records in it which has ~600m reads since 10pm last night. It's a static table. The reads are (almost) justified. It's a perfect candidate for B2.
 
Cringer said:
Oh absolutely! My post was aimed at Rob.
Click to expand...
To be clear, I'm not saying that you should assign objects to B2 by area, nor am I saying you should change your structure. My point is just that one shouldn't be married to a given structure and tell oneself that it can't change. Your structure should serve your needs and goals, and they can change over time. And starting to use B2 can be a valid reason for making structure changes.

For example, if you have 50 small reference tables that are very frequently read, you may want to create two areas for them, e.g. B2data and B2index. You can easily get the data into them with tablemoves. Then instead of fiddling with the dictionary UI for B2 object assignments you can just run a couple of proutil commands to do the assignments. This approach also has the advantage that the structure is more self-documenting; you know at a glance of a list of objects by area (e.g. dbanalys) which ones have been assigned to B2.
 
That's definitely a fair point Rob. And I am in the process of redesigning our structure. But I'm also just starting to play around with B2, and therefore haven't yet decided what I want to put into B2. I have an urgent requirement to dump and load though as we have around 80GB of empty blocks that I need to free up (less than 30GB space left in production!) :(
 
Cringer said:
That's definitely a fair point Rob. And I am in the process of redesigning our structure. But I'm also just starting to play around with B2, and therefore haven't yet decided what I want to put into B2. I have an urgent requirement to dump and load though as we have around 80GB of empty blocks that I need to free up (less than 30GB space left in production!) :(
Click to expand...

If you need to D&L, even partially, then this is a good time to think about your structure.

For now, focus on the areas with the largest gaps between HWM and total size in blocks. ProTop will show you that. D&L those objects into new areas and then you can truncate the old ones to free up the space (being careful to ensure you moved all the data). :)

Are you on 11.3 now?
 
11.2.1 at the moment. We're going to skip 11.3 and go to 11.4 at some stage soon. We want to look at table partitioning.
The area with the empty blocks is a load of LOBs I've deleted from the db. dbanalys doesn't show me those :( But the plan is to D&L the whole db as there's around 50 tables that are in the wrong RPB and cluster size, and we also have some indexes in table areas etc.
 
You must log in or register to reply here.
Back
Top