Paging PSC Documentation to Aisle 3....

Posted by Tim Kuehn on 10-Nov-2010 11:42

This was recently posted on the PEG, and illustrates a shortcoming of the PSC docs....

after reading the 10.2B documentation and clicking all links associated to the -B2 alternate buffer pool parameter, I don't find any explaination of when/why/recommendation on settings anywhere in the

Since this is new in OE10 - and was not in v9 - does anyone have any answers to my questions below:

1.  When should the -B2 parameter be set?
2.  Why should the -B2 parameter be set?
3.  What recommendation for values of -B2 should be used as rule of how should it be calculated?
4.  Normally should this be set?

I'd note that incomplete / hard to understand docs -> the product not being fully utilized, user frustration, and more tech support calls.

All Replies

Posted by gus on 09-Dec-2010 10:53

The alternate buffer pool is similar to the main buffer pool you already have. Its size is set with the -B2 startup configuration parameter and like -B, its value is the number of buffers to allocate. The buffers are allocated from the shared memory area just like the main buffer pool is.

Unlike the main buffer pool, nothing goes into the alternate buffer pool unless you say to put it there. You can assign individual tables and/or indexes that are housed in Type II data areas. You make the assignments using the data admin tool. You can also assign an entire data area (Type I or Type II) to the alternate buffer pool. You can use proutil for this. Details are in the documentation.

As for what to put into the alternate buffer pool and how it should be sized, that is highly application and system dependent. The reason for putting something into the alternate buffer pool is to increase performance. The stuff in the alternate buffer pool is isolated from the main buffer pool and won't be paged out by all the other activity in the main buffer pool. If you make good choices for what to put there, and it all fits, you can get a big performance boost. If you put too much stuff into the alternate buffer pool, you may get less benefit. There are several possible usage scenarios:

  • Put encrypted tables there
  • Put small to medium sized tables that are heavily used and fit in their entirety there. Tom Bascom has done this for one of his customers and achieved a large improvement in performance. That was an interim step before fixing all the bad code that was causing the problem in the first place.
  • Put indexes to very large tables there.

Hope that is at least somewhat helpful.

Posted by Tim Kuehn on 13-Dec-2010 10:56

Gus -

Thanks for your response - while your answer is technically accurate, the point I was making was that this information isn't in the PSC docs, and it should've been.

Posted by gus on 14-Dec-2010 09:17

In the documentation it says:

"The Alternate Buffer Pool is a collection of buffers in shared memory that

are logically separate from the primary buffer pool. Management of the

buffers in the Alternate Buffer Pool is independent of the primary buffer

pool. Assigning specific database areas or objects to occupy buffers from

the Alternate Buffer Pool might improve your buffer hit rate, thereby

reducing the need to read and write buffers to and from disk, and possibly

improving performance.

Specifying the best objects for the Alternate Buffer Pool is

application-specific. Tables considered ³hot² (very active) are good

candidates, as are their related indexes. Tables and indexes that are

governed by an encryption policy are also considered good candidates because

the cost of encrypting and decrypting blocks as they are written and read

from disk can be high."

While you might wish for more, it /is/ in the doc.

This thread is closed