OpenEdge 10.2B introduced an additional database startup parameter (-B2) and enhanced Database utilities to improve performance for Enterprise databases under certain specific use cases.
Database Objects with high reuse read rates can benefit Application performance when they are kept in the buffer pool as long as possible. The -B2 <value> database startup parameter sets an additional group of buffers in shared memory into which objects (tables, indices, clobs/blobs) may use this memory space when they are assigned to the Alternate Buffer Pool. Database Objects in:
- Type I Areas cannot specify individual tables, indices or LOBS. All of the Area must be flagged for use within the Alternate Buffer Pool or none with the PROUTIL enableb2 command line option.
- Type II Areas may either assign individual tables, indices or LOBS with the Data Dictionary Tool, or may also flag the entire area be placed into the Alternate Buffer Pool with the PROUTIL enableb2 command line option.
When an Object is assigned to the Alternate Buffer Pool at both the Object and Area level:
- The Area level assignment will take precedence
- When the Object is unassigned, it will remain assigned at the Area level
- When an Area is unassigned, the Object level will remain assigned.
The Alternate Buffer Pool (-B2) is always
considered enabled regardless if it is running or not. It is not a database feature that needs enabling - you do not need to specify proutil enableB2 to use B2, you just need to startup the database with the -B2 parameter so that objects / areas assigned to it can be loaded into it once their buffers are read into shared-memory.
- When the database is started with -B2 and no objects or areas are assigned, that shared memory will be attached to the database but remain unused
- When objects or areas have been assigned to the Alternate Buffer pool, but the database is not started with -B2 all objects will load into the Primary Buffer Pool
To Assign Objects or Areas to -B21. To assign or unassign which Objects in a Type II Area are loaded into the Alternate Buffer Pool, an additional sub-menu of the Admin menu labelled:
Alternate Buffer Pool was added to the Data Dictionary (character version) and the Database Administration (Windows GUI version)
2. To designate all objects in a Storage Area to be loaded into the Alternate Buffer Pool, or unassign the Storage Area new command line options were added to PROUTIL These commands can only run in single user mode, in other words, the Enterprise database must be run offline:
$ proutil <dbname> -C enableB2 "area_name"
$ proutil <dbname> -C disableB2 "area_name"
The database needs to be started with -B2 <blocks> in order for the the secondary buffer-pool to be used. Otherwise they are loaded into the primary buffer pool:
To Report on Alternate Buffer Pool Assignment1.
Quick Alternate Buffer Pool Report : Data Dictionary (character version) and the Database Administration (Windows GUI version)
Database > Reports > Alternate Buffer Pool
If any Object Level assignments have already been committed, they will be reported under the following header:
List of object-level assignable objects that are assigned to the alternate buffer pool at the object levelIf any Object Level assignments have already been committed, they will be reported under the following header:
List of object-level assignable objects that are assigned to the alternate buffer pool at the area level Objects in Type I Areas do not qualify for Object Level assignments
2. PROUTIL viewB2 provides information for all Areas and Objects indicating what is in the (Primary) Buffer Pool (-B) and what is in the (Secondary) Alternate Buffer Pool (-B2).
$ proutil <dbname> -C viewB2
- Since OpenEdge 11.6.3 11.7.0 - PROUTIL -C viewB2 output includes the assigned storage pool, the size (in blocks) of each object and a total blocks for the Area.
- This sizing includes all blocks associated with each object - including free blocks.
- viewB2 with the -csoutput Option, includes this additional data
Virtual System Tables
VST's have been modified to add information relating to the Alternate Buffer Pool. It is advisable to run "proutil -C updatevst" to see these if the database has been opened in OpenEdge 10.2B or later without a dump and load.
_ActBuffer
_BuffStatus
_Startup
_indexStats
_tableStats
_indexStats and _tableStats have been modified to add new information when the areas the tables / indices reside in are Type II areas:
_IndexStat-OsReads
_TableStat-OsReads
PROMON
Menus in PROMON have been enhanced to include information related to the Alternate Buffer Pool.
In addition to the Buffer Hits % information, additional information will distinguish between Primary and Secondary Buffer Pool hit percentages.
Example: PROMON > 5. Activity :
Buffer Hits 95 % Primary Hits 95 % Alternate Hits 99 %
PROMON > R&D > 1. Status Displays > 7. Buffer Cache
PROMON > R&D > 2. Activity > Buffer Cache
Recommendations for use of the Alternate Buffer Pool:1. Consider Database Objects / Storage Areas which are configured for Transparent Data Encryption
:
- If using Encryption, put all tables / areas which have Transparent Data Encryption (aka block level encryption) enabled into the Alternate Buffer Pool.
- Since there is added overhead to perform encryption and decryption, keeping the block in memory at all times reduces the need to repeat these steps.
- While a block is in memory it is unencrypted. It is only encrypted when it is written down to disk. It is decrypted when it is read from disk.
- Start the Database without -B2 in order to run epolicy manage update sessions to encrypt existing data.
Once blocks have been encrypted, initialise -B2 online with: proutil -C increaseto -B2 <value>
2. Consider Database Objects / Storage Areas that need high availability for performance considerations.
- Highly active tables, indexes, LOBs, or Storage Areas. The database “Schema Area” containing only the database metaschema is a good example of this.
- Static tables, indexes, LOBs, or Storage Areas. An Item inventory (which is rarely updated) is one of many examples.
3. What should not be allocated to the Alternate Buffer Pool (-B2) ?:
- Database objects / areas that have a very broad range of record usage. For example, a table that users might run queries/tasks that cover many years of data or has high Create-Delete-Update activity.
- Database objects that are rarely used. For example, Historical or Audit data.
For further discussion refer to Article
Basic guide on when to use the Alternate Buffer Pool -B2 Changes to the LRU algorithm for the Alternate Buffer Pool.
The Alternate Buffer Pool will not use an LRU2 if all the data assigned for the Alternate Buffer Pool fits within the allocated space.
- If the Alternate Buffer Pool is defined as 10000 buffers and the table defined to fit in the Alternate Buffer Pool is 5000 then the entire table will fit in the Alternate Buffer Pool and will not be paged out.
- If at any point more data is assigned to the Alternate Buffer Pool than will fit in the pre-allocated space then at that point a LRU2 chain will be created to handle the Alternate Buffer Pool.
- If the buffer pools are increased using PROUTIL -C increaseto -B2 <new larger value> which allows all of the data assigned to the Alternate Buffer Pool to comfortably fit in the Alternate Buffer Pool, the LRU2 chain will no longer be used for the Alternate Buffer Pool.
- Like the primary buffer pool (LRUSKIPS), the secondary buffer pool uses -LRU2SKIPS
- For further information refer to Article How to size the Alternate Buffer Pool (-B2)
Additional Considerations
1. PROREST
when restored over an existing database:
- Does keep TARGET AREA level B2 assignment
- Does not keep TARGET Object level B2 assignment. Source Object level assignment persists
when not restored over an existing database:
2. PROCOPY/PRODB keeps both AREA and object level assignment of the SOURCE database.
3. Online utilities with Block Level Access, as opposed to block requests for specific objects are used. These need to be run with Private Buffers (-Bp)
- probkup online dbname <backupvolume> -Bp 32
- proutil dbname -C dbanalys -Bp 32
Consider increasing the database startup parameter -Bpmax 64 (default). The private buffer pool (-Bp) is an isolated portion of public buffer pool (-B), it is also limited to no more than 25% of the primary buffer pool (-B) value. Client sessions may also be making use of using private buffers (-Bp, NumSeqBuffers)
For further detail refer to Article
Alternate Buffer requires more blocks than the number of database objects assigned 
