The facility for predicting reorganization time makes its predictions on the basis of predefined standard values for items. You can change these standard values as appropriate to your environment. You can specify in the standard value definition file the new standard values you wish to use for any items.
<threshold> |
Table 15-7 describes the items whose standard value can be changed. Table 15-8 provides guidelines for changing standard values.
Table 15-7 Items whose standard value can be changed
No. | Item name | Specifiable values | Standard value | Description | Whether or not changeable | ||
---|---|---|---|---|---|---|---|
R | T | I | |||||
1 | EMPTY_PAGE_RATIO | *, 10-100 | 30 | Among the total number of pages allocated, specifies the percentage that can be released by pdreclaim (%). | Y | Y | Y |
2 | UNDER_PAGE_RATIO | 10-100 | 10, or 80- PCTFREE | Specifies the page usage percentage to be used to check No. 3 (%). | Y | Y | Y |
3 | UNUSED_PAGE_RATIO | *, 10-100 | 50 | Among the total number of pages allocated, specifies the percentage whose page usage is less than the value of No. 2 (%). | Y | Y | Y |
4 | BRANCH_ROW | *, 10-100 | 50 | Among the total number of rows, specifies the percentage stored on multiple pages (%). | Y | Y | N |
5 | USED_SEGMENT_LOB | *, 10-100 | 80 | Among the total number of segments, specifies the percentage derived from the last segment number in the LOB RDAREA (%). | Y | N | N |
6 | USED_SEGMENT_RATIO_CLUS | *, 10-100 | 50 | Among the total number of segments allocated to a clustering data page, specifies the percentage whose usage is 100% (%). | Y | Y | N |
7 | DIFF_PCTFREE_M | *, 10-100 | 10 | Specifies the value used to determine the minimum page usage (%). This value is obtained by subtracting the minimum page usage rate from the page usage rate that is obtained based on the percentage of unused area specified in PCTFREE. If the specified value is greater than (100 - percentage of unused area specified in PCTFREE), the value of (100 - percentage of unused area specified in PCTFREE) is assumed as this value. If * is specified, only the value specified in No. 8 takes effect. | Y | Y | Y |
8 | DIFF_PCTFREE_P | *, 10-100 | 10 | Specifies the value used to determine the maximum page usage (%). This value is obtained by subtracting the page usage that is obtained based on the percentage of unused area specified in PCTFREE from the maximum page usage rate. If the specified value is greater than the percentage of unused area specified in PCTFREE, the percentage of unused area specified in PCTFREE is assumed as this value. If * is specified, only the value specified in No. 7 takes effect. | Y | Y | Y |
9 | DIFF_PCTFREE_RATIO | *, 10-100 | * | Among the total number of pages allocated, specifies the percentage whose usage rate is less than the minimum value of No. 7 and greater than the maximum value of No. 8 (%). When this item is omitted, analysis is not performed for this item. | Y | Y | Y |
10 | USED_SEGMENT | *, 10-100 | 80 | Among the total number of segments, specifies the percentage used throughout the entire RDAREA (%). | Y | N | N |
11 | EXTEND_COUNT_MIN | 0-24 | 0 | Specifies the minimum number of times the RDAREA can be extended automatically. This value must satisfy the condition EXTEND_COUNT_MIN![]() | Y | N | N |
12 | EXTEND_COUNT | 0-24 | 0 | Specifies the number of times the RDAREA can be extended automatically. This value must satisfy the condition EXTEND_COUNT_MIN![]() | Y | N | N |
Table 15-8 Guidelines for changing the standard value
No. | Item name | Standard value | How to determine whether or not maintenance is needed | Guideline for changing standard value |
---|---|---|---|---|
1 | EMPTY_PAGE_RATIO | (p)=30% | If the percentage of used pages with a page usage rate of 0% is (p)% or greater, the utility determines that free pages must be released. | If there is not much space available in the RDAREA and you want to release invalid pages as soon as possible, reduce the value of (p). |
2 | UNDER_PAGE_RATIO | (a)=X-20% | If the percentage of used pages whose page usage rate of (a)% or greater is equal to or greater than (p)%, the facility determines that reorganization is necessary. |
|
3 | UNUSED_PAGE_RATIO | (p)=50% | ||
4 | BRANCH_ROW | (p)=50% | If the percentage of branched rows among the total number of rows is (p)% or greater, the facility determines that reorganization is necessary. | If there are many UPDATE processes that result in longer data and search performance needs to be maintained, reduce the value of (p). |
5 | USED_SEGMENT_LOB | (p)=80% | If the last segment number in the LOB RDAREA exceeds (p)% among the total number of segments and reorganization does not improve the condition, the utility determines that extension of the RDAREA is necessary. If reorganization results in a value less than (p), the facility determines that reorganization is necessary. | If the amount of data to be stored may increase suddenly and you want to take action at an early stage, reduce the value of (p). |
6 | USED_SEGMENT_RATIO_CLUS | (p)=50% | If the number of segments with a page usage per segment of 100% (segments without free pages) is equal to or greater than (p)% of the total number of segments in use, use of free space based on the percentage of unused pages, not the use of free space based on the percentage of unused area, has already begun; therefore, the facility determines that reorganization is necessary. | While data can be stored in the same page, clustering effects are still maintained. If you can determine from the storage status in the same segment that clustering is no longer effective, reduce the value of (p). |
7 | DIFF_PCTFREE_M | (a)=X-10% | If the percentage of used pages with a page usage outside the range from (a)% to (b)% is equal to or greater than (p)%, the facility determines that reorganization is necessary. | If you want to perform reorganization even when the page usage rate is high (for a table in which UPDATEs resulting in a longer row occur frequently, or for a table with a cluster key defined in which there are frequent occurrences of INSERTs that involves data with an intermediate key), specify (p). In the case of an index with many duplicate key values, PCTFREE is ignored for the pages that store duplicate key values; therefore, if you specify (p), specify a sufficient value. |
8 | DIFF_PCTFREE_P | (b)=X+10% | ||
9 | DIFF_PCTFREE_RATIO | (p)=* | ||
10 | USED_SEGMENT | (p)=80% | If the number of used segments in the RDAREA is equal to or greater than (p)%, the facility determines that reorganization is necessary. If the value remains equal to or greater than (p)% after reorganization, the utility determines that extension is necessary. However, in the case of an RDAREA for which automatic extension has been specified, the utility does not recommend extension because the RDAREA can be extended automatically only up to (a) times. |
|
11 | EXTEND_COUNT_MIN | (a)=23 | ||
12 | EXTEND_COUNT |
The following shows a standard value definition file specification example:
<threshold> .........................Common specification |