provides guidelines on how to determine an optimum before-image (BI) file cluster size.
The BI cluster size determines the frequency of the number of checkpoints that are made.
In essence, a larger bicluster size would mean less frequent checkpoints, longer recovery times and longer freeze when BI File grows. A smaller cluster size means that the checkpoints will happen more frequently.
Checkpoints should occur at least 60 seconds apart and a spacing of 5 minutes is better. Common cluster size ranges are 4 MB to 32 MB. The default is ½ MB (512 KB).
Determining the optimum bi cluster size is a reiterative process, that needs to be base-lined against periods of "usual load" on the production database.
To examine checkpoint frequency, go to the following directory and sample at regular intervals:
PROMON > R&D > 3 Other > 4 Checkpoints.
For example: "The suggested minimum for checkpoints is 120 seconds apart"
If the average length of time between any two checkpoints were found to be 30 seconds
apart in the LEN
column of the above PROMON output, and you would like to improve this by making the bi cluster take an average of 120 seconds
to fill up, then you would therefore need to increase the current bicluster size, incrementally up to 4 times to increase the time it takes to fill the cluster by 4 times (120/30 = 4
The bicluster size must be changed offline, optionally the biblocksize can be changed at the same time, through the following command syntax:
$ proutil <dbname> -C truncate bi -bi <clustersize in KB> -biblocksize <biblocksize in KB>
$ proutil <dbname> -C truncate bi -bi 16384 -biblocksize 16