Alternate Buffer Pool

[Cringer]

Silly question, but how does one mark a table to be in the alternate buffer pool?

 

[gareth.roberts]

Data Administration > Admin > Alternative Buffer Pool > Alternative Buffer Pool Maintenance

 

[Cringer]

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?

 

[gareth.roberts]

from memory yes

 

[Cringer]

Fab thanks.

 

[Rob Fitzpatrick]

You can also assign all the objects in an area to B2 with proutil dbname -C enableB2 areaname.

 

[Cringer]

Thanks Rob. Definitely don't want to do that with our structure, but it's good to know.

 

[Rob Fitzpatrick]

But if you're starting to use B2, it may make sense to alter your structure.

 

[TomBascom]

Consider putting the schema area into B2.

It shouldn't have any user data in it and is a perfect candidate.

 

[Cringer]

Depends which school of thought you're in in terms of structure design I suppose.

 

[Cringer]

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?

 

[TomBascom]

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]

Depends which school of thought you're in in terms of structure design I suppose.

Only the school of thoughtlessness allows user data objects in the schema area.

 

[Cringer]

Only the school of thoughtlessness allows user data objects in the schematic area.

Oh absolutely! My post was aimed at Rob.

 

[Cringer]

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.

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.

 

[Rob Fitzpatrick]

Oh absolutely! My post was aimed at Rob.

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.

 

[Cringer]

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!)

 

[TomBascom]

So is the schema area

 

[Rob Fitzpatrick]

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!)

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?

 

[Cringer]

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.