Poor Performance of backup cycle (phase 2) due to fragmentation - SARDBASE REORG functions
Article ID: 28213
Updated On:
Products
Issue/Introduction
What occurs during the delete phase (Phase 2) of a View backup cycle that would cause there to be performance degradation?
Environment
View
Cause
Database/Index fragmentation
Resolution
The deletion of reports causes the database master index blocks to become empty. Some processes in CA View read the master index sequentially across the lowest level, and empty index blocks can slow processing activity.
Index blocks at all levels are chained together. For example, the first block in a level contains the block number of the next sequential block in the index. When an index block fills, it is split into two blocks. Half of the index keys are retained in the original block, and the second half of the keys are written to a new block. These blocks are chained together. The deletion of reports through normal retention processing may delete all the key values in a block and empty the block. Empty blocks are not freed, but remain in the level's chain.
The backup cycle reads the View Master index sequentially at the lowest level while it is writing the index to the backup tape during Phase 2. Each block in the level's chain is read, and the key values are extracted and written to tape. Since all blocks at a level are chained together, empty blocks are also read.
As the number of empty blocks increases, the number of hardware input functions increase with little or no functional processing other than to obtain the next block number in the chain. Since normal report access is direct, where the index's tree structure is used to find a specific key, empty blocks generally do not affect performance. Performance will be impacted for processes that read the index sequentially.
There are numerous ways to remove empty blocks from an index level's chain. The most common and recommended way is to reorganize the database. The database utility SARDBASE has two reorganization functions - one function must be run in a stand-alone environment with the database off-line and the second can be run with normal database activity.
The reorganization function, which can be run with concurrent database activity, is called an "asynchronous reorganization" and is selected by adding the "ASYNCH" keyword parameter to the REORG statement (REORG ASYNCH). The asynchronous reorganization function will consolidate key values within active index blocks and returns empty blocks to the index's free block pool.
The stand-alone reorganization function does not consolidate the index key values, but rather copies the index to a new section of the database. At the completion of the copy, the old index is completely freed and returned to the database's free block pool. The stand-alone reorganization requires sufficient free database space to hold a second copy of the index.
A stand-alone reorganization job may run faster than the asynchronous reorganization, since there is no other database activity to contend with, but it does require the database to be off-line. Since the database can be active, an asynchronous reorganization is normally easier to schedule. Both will improve database performance.
A reorganization process should be scheduled as the number of empty blocks increases and the sequential processing performance of the database degrades. Generally, when the number of empty blocks exceeds 20% of the used blocks, but before reaching 50%, the database reorganization should be scheduled. These numbers can be obtained by using the "STATUS FULL" function of the SARDBASE utility. This function produces a report describing the database usage. The section of the report concerning the index has the following format:
SARDBA09 INDEX STATISTICS: SARDBA09 INDEX LEVELS: nnn SARDBA09 INDEX RECORDS: nnn SARDBA09 INDEX ALLOCATIONS: nnn SARDBA09 INDEX BLOCKS: nnn SARDBA09 INDEX USED BLOCKS: nnn SARDBA09 INDEX POINTER BLOCKS: nnn SARDBA09 INDEX DATA BLOCKS: nnn SARDBA09 INDEX EMPTY BLOCKS: nnn
The two fields that should be used when calculating database fragmentation are: INDEX EMPTY BLOCKS and INDEX USED BLOCKS. When empty, if fragmentation is in the range of 20%-50% of use, reorganization should be scheduled and run. It is strongly recommended that this percentage not exceed 50%.
Feedback
