15.3.1 Condition analysis by RDAREA (logical analysis)

This utility analyzes the storage condition of all segments and pages for the tables and indexes that are stored in an RDAREA. Note, however, that for tables and indexes that have been partitioned and stored among several RDAREAs, this utility analyzes only those segments stored in the specified RDAREAs.

Organization of this subsection
(1) Purpose
(2) Analysis results
(3) Analyzing the analysis results

(1) Purpose

You can detect a disordered table or index and obtain the capacity status of a single RDAREA.

(2) Analysis results

The following shows the results of condition analysis by RDAREA (logical analysis):

(a) For an RDAREA other than a LOB RDAREA

pddbst VV-RR(Object Option) ** RD Area Logical Analysis **  2003/03/31 18:33:38 [1]
RD Area Name   : user_rdarea_1 [2]
 Server        : bes1 [3]
 Total Segment :        150[4] Segment Size :          5 Pages [6]
 Unused Segment:         24[5] Page Size    :       4096 Bytes [7]
 Original RD Area Name : user_rdarea_1 [21]
 Generation Number :  0[22]  Replica RD Area Count :  0 [23]
 [24]     [25]               [26]               [27]
 History1 Hold Status :      Hold Code :     0  Hold Time :
 History2 Hold Status :      Hold Code :     0  Hold Time :
Table Name : TBL01 [8]
Auth Id    : user1 [9]
Status     :   [20]
Reference Pending Status : P [33]
Check     Pending Status : P [34]
 Search Mode : INS [28] Segment Reuse :          - segments [29]
 Reuse Search Failure :          0/         0 [30]
         Used(Full)       Used(      Full)        Sum
         [10]  [11]        [12]       [13]        [14]
 Segment 100%(  0%)        104(         0)        104
         [15]  [16]        [17]       [18]        [19]
 Page    100%(  0%)        518(         0)        520
--------------------------------------------------------------------------------
 Collect Prearranged Page :         55 [31]
 Used Page Ratio       Page(Ratio) [32]
            0% :         57(  11%)
        1- 10% :         19(   4%)
       11- 20% :         28(   6%)
       21- 30% :         70(  14%)
       31- 40% :         95(  19%)
       41- 50% :         71(  14%)
       51- 60% :         69(  14%)
       61- 70% :         58(  12%)
       71- 80% :         37(   8%)
       81- 90% :         14(   3%)
       91-100% :          2(   1%)
 Total                  520

Table Name : TBL02
Auth Id    : user1
Status     :
 Search Mode : INS   Segment Reuse :          - segments
 Reuse Search Failure :          0/         0
         Used(Full)       Used(      Full)        Sum
 Segment 100%(  0%)         21(         0)         21
 Page     98%(  0%)        103(         0)        105
 Collect Prearranged Page :          9
 Used Page Ratio       Page(Ratio)
            0% :         11(  11%)
        1- 10% :          1(   1%)
       11- 20% :          6(   6%)
       21- 30% :          6(   6%)
       31- 40% :         12(  12%)
       41- 50% :         13(  13%)
       51- 60% :          5(   5%)
       61- 70% :          7(   7%)
       71- 80% :         27(  26%)
       81- 90% :         12(  12%)
       91-100% :          5(   5%)
 Total                  105

Index Name : IDX_TBL01_C1
Auth Id    : user1
Status     :
         Used(Full)       Used(      Full)        Sum
 Segment 100%(  0%)          1(         0)          1
 Page    100%(  0%)          5(         0)          5
 Collect Prearranged Page :          0
 Collect On Page :          0
 Used Page Ratio       Page(Ratio)
            0% :          0(   0%)
        1- 10% :          1(  20%)
       11- 20% :          0(   0%)
       21- 30% :          3(  60%)
       31- 40% :          0(   0%)
       41- 50% :          1(  20%)
       51- 60% :          0(   0%)
       61- 70% :          0(   0%)
       71- 80% :          0(   0%)
       81- 90% :          0(   0%)
       91-100% :          0(   0%)
 Total                    5

Explanation:
  1. Date and time of completion of acquisition of the information required for this condition analysis, in the format YYYY/MM/DD hh:mm:ss:
    YYYY: Year. MM: Month. DD: Date. hh: Hour. mm: Minute. ss: Second.
  2. Name of RDAREA subject to analysis.
  3. Name of server managing the RDAREA.
  4. Total number of segments in the RDAREA (number of used segments + number of unused segments).
  5. Total number of unused segments in the RDAREA.
  6. Segment size (number of pages per segment).
  7. Page size (in bytes).
  8. Name of a table or index contained in the RDAREA.
  9. Authorization identifier of the table or index. For a data dictionary RDAREA, (Data dictionary )is displayed.
  10. Percentage ratio of used segments:
    [Figure]number of used segments/value of 14 [Figure][Figure] 100 (%)
  11. Percentage ratio of full segments:
    [Figure]number of full segments/value of 14 [Figure][Figure] 100 (%)
  12. Number of used segments.
  13. Number of full segments.
  14. Total number of segments allocated to the table or index (number of used segments).
  15. Percentage ratio of used pages:
    [Figure]number of used pages/value of 19 [Figure][Figure] 100 (%)
  16. Percentage ratio of full pages:
    [Figure]number of full pages/value of 19 [Figure][Figure] 100 (%)
  17. Number of used pages.
  18. Number of full pages.
  19. Total number of pages in the segments allocated to the table or index (number of used pages + number of unused pages).
  20. For a table, reload-not-completed data status or being reorganized. If the table is not in either of the following statuses, this field is blank.
    DATA_UNFINISH:
    The table is in reload-not-completed data status, which means that table reorganization was executed, but reload processing has not been completed for some reason such as an error. To release the table from reload-not-completed data status, you must re-execute table reorganization or reload data into the table.
    ON_RORG:
    The table is currently being reorganized.
    For an index, this is the status of an unfinished index. If the index has been completed, this field is blank.
    UNFINISH_0
    See Figures 15-9 and 15-10.
    UNFINISH_1
    See Figures 15-11 and 15-12.
    UNFINISH_2
    When a UAP using the plug-in index delayed batch creation function is being executed or after the UAP finished executing, a batch index creation process using the database reorganization utility is not being executed. For details about the plug-in index delayed batch creation function, see the HiRDB Version 8 System Operation Guide.
  21. Name of the original RDAREA.
  22. Generation number of the corresponding RDAREA.
  23. Number of replica RDAREAs.
  24. Shutdown history of the corresponding RDAREA.
    History1: Shutdown information immediately before the shutdown was released (if the RDAREA is currently shut down, this field displays the current shutdown information)
    History2: Shutdown information immediately preceding History1
  25. Shutdown type of the corresponding RDAREA. If there is no history, this field is blank.
    CMD: Command shutdown by HiRDB error detection
    FLT: Error shutdown
  26. Code indicating the cause of the shutdown of the corresponding RDAREA. If there is no history, this field displays 0.
    0: Shutdown release
    10: Input/output error
    20: Page corruption
    40: Open or allocation error
    70: Rollback error
    80: Shutdown due to an error during DROP TABLE or DROP INDEX
    90: No-log shutdown (pdload, pdrorg, and pdrbal commands)
    92: No-log shutdown (UAP)
    93: Shutdown of LOB RDAREA
    96: Invalid time stamp
    97: Invalid object ID
    98: List RDAREA error
    100: Log application-disabled status due to execution of a utility (pdload, pdrorg, or pdrbal) in a mode other than the log acquisition mode
    102: Log application-disabled status due to execution of a UAP in a mode other than the log acquisition mode
    104: Log application-disabled status due to updating of a table for which PARTIAL or NO is specified for the BLOB column recovery restriction
    110: Log application-disabled status due to pdmod
    120: Log application-disabled status due to error shutdown
  27. Shutdown time of the corresponding RDAREA. If there is no history, this field is blank.
  28. Page search mode:
    INS: New page allocate mode
    REU: Free page reuse mode
  29. Number of segments specified for SEGMENT REUSE during execution of CREATE TABLE or ALTER TABLE. If a number of segments for SEGMENT REUSE is not specified, this field displays 0. If NO is specified for SEGMENT REUSE or SEGMENT REUSE is omitted, the field displays -.
  30. Number of times the mode changed from new page allocate to free page reuse and then changed back to new page allocate because there was no reusable free space (n/m).
    n: Idle count
    m: Number of times mode changed from new page allocate to free page reuse
    n and m are reset to 0 at the following times:
    • When the RDAREA is closed
    • When HiRDB is restarted
    • When the PURGE TABLE statement is executed
  31. Number of pages that can be released by pdreclaim (number of used free pages whose usage ratio is 0%).
  32. Number of used pages for each 10% range. The parentheses enclose the percentage ratio to all used pages. Because the fraction part of each ratio is rounded up, the total may exceed 100. Note the following:
    • For a page that stores 201 or more duplicate keys, the percentage ratio of PCTFREE specified during table definition is ignored. Therefore, data is not rearranged according to the PCTFREE value even during reorganization.
    • For an index to which the index key value with no lock is applied, the deletion key value is treated as free space. If all used pages are deletion key values, the usage ratio is 0%.
    • A full page may not always fall in the usage ratio range of 91-100%.
    • A free space created by update processing that shortens an existing row is not treated as free space.
    • If the ratio of used pages is less than (100 - free space ratio specified with PCTFREE), the storage efficiency may have decreased. You should consider executing pdrorg. For an area storing branch rows (variable-length character string data with a length of 256 bytes or greater, data for repetition columns, and data for columns of an abstract data type), executing pdrorg has no effect on the storage efficiency.
  33. Check pending status for referential constraint in the table information in the RDAREA. If no referential constraint has been defined for the table, this item is blank.
    P: Check pending status
    blank: Not check pending status
  34. Check pending status for check constraint in the table information in the RDAREA. If no check constraint has been defined for the table, this item is blank.
    P: Check pending status
    blank: Not check pending status
Note 1
Nos. 21-23 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and the specified replica RDAREA generation does not exist, the utility issues a warning message, in which case no results are displayed.
Note 2
Nos. 31-32 are displayed when the -d option is specified.

Figure 15-9 Types of unfinished index by table (1/2)

[Figure]

Figure 15-10 Types of unfinished index by table (2/2)

[Figure]

Figure 15-11 Types of unfinished index by RDAREA (1/2)

[Figure]

Figure 15-12 Types of unfinished index by RDAREA (2/2)

[Figure]
(b) For LOB RDAREA

pddbst VV-RR(Object Option) ** RD Area Logical Analysis **  2003/04/03 12:36:43 [1]
RD Area Name   : user_rdlob_2 [2]
 Server        : bes1 [3]
 Total Segment :      11000 [4] Segment Size :          1 Pages [6]
 Unused Segment:       9178(      9178) [5]
 Page Size    :       8192 Bytes [7]
 Original RD Area Name : user_rdlob_2 [16]
 Generation Number :  0[17]  Replica RD Area Count :  0 [18]
 [19]     [20]               [21]               [22]
 History1 Hold Status :      Hold Code :     0  Hold Time :                    
 History2 Hold Status :      Hold Code :     0  Hold Time :                    
--------------------------------------------------------------------------------
Table Name : TBL03 [8]
Auth Id    : user1 [9]
Status     :   [23]
         Used       Used        Sum
 Segment 100% [10]  1822 [11]  1822 [12]
 Segment Over  : N [13] Last Segment :       1822/     11000 [14]
 Lobmap  Over  : N [15]

Explanation
  1. Date and time of completion of acquisition of the information required for condition analysis, in the format YYYY/MM/DD hh:mm:ss:
    YYYY: Year. MM: Month. DD: Date. hh: Hour. mm: Minute. ss: Second.
  2. Name of RDAREA subject to analysis.
  3. Name of server managing the RDAREA.
  4. Total number of segments in the RDAREA (number of used segments + number of unused segments).
  5. Total number of unused segments in the RDAREA. The parentheses enclose the total number of unused segments in HiRDB files that are not in the frozen update status in the corresponding RDAREA.
  6. Segment size (number of pages per segment).
  7. Page size (bytes).
  8. Name of a table or index contained in the RDAREA.
  9. Authorization identifier of the table. For a data dictionary LOB RDAREA, (Data dictionary) is displayed.
  10. Percentage ratio of used segments:
    [Figure]value of 11/value of 12 [Figure][Figure] 100 (%)
  11. Number of used segments.
  12. Total number of segments allocated to the table (number of used segments).
  13. Whether or not the order is lost in a LOB RDAREA.
    Y: Order is lost.
    N: Order is not lost.
  14. Position information indicating that the segment being used is the last segment; this is indicated in a last position/total number of segments format; always indicates the last segment when {13} is Y.
  15. Whether or not the LOB management entries are all used:
    Y: All are used.
    N: There are unused entries.
  16. Name of the original RDAREA.
  17. Generation number of the corresponding RDAREA.
  18. Number of replica RDAREAs.
  19. Shutdown history of the corresponding RDAREA.
    History1: Shutdown information immediately before the shutdown was released (if the RDAREA is currently shut down, this field displays the current shutdown information)
    History2: Shutdown information immediately preceding History1
  20. Shutdown type of the corresponding RDAREA. If there is no history, this field is blank.
    CMD: Command shutdown by HiRDB error detection
    FLT: Error shutdown
  21. Code indicating the cause of the shutdown of the corresponding RDAREA. For details about the shutdown cause code, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  22. Shutdown time of the corresponding RDAREA. If there is no history, this field is blank.
  23. For a table, reload-not-completed data status or being reorganized. If the table is not in either of the following statuses, this field is blank.
    DATA_UNFINISH:
    The table is in reload-not-completed data status, which means that table reorganization was executed, but reload processing has not been completed for some reason such as an error. To release the table from reload-not-completed data status, you must re-execute the table reorganization or reload data into the table.
    ON_RORG:
    The table is currently being reorganized.
    For an index, this is the status of an unfinished index. If the index has been completed, this field is blank.
    UNFINISH_0:
    See Figures 15-9 and 15-10.
    UNFINISH_1:
    See Figures 15-11 and 15-12.
    UNFINISH_2:
    Delayed plug-in index updating is underway. This is the status when a UAP using the plug-in index delayed batch creation function is being executed, or after the UAP finished executing, a batch index creation process using the database reorganization utility is not being executed. For details about the plug-in index delayed batch creation function, see the HiRDB Version 8 System Operation Guide.
Note
Nos. 16-18 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and the specified replica RDAREA generation does not exist, the utility issues a warning message, in which case no results are displayed.

(3) Analyzing the analysis results

The results of condition analysis by RDAREA (logical analysis) can be analyzed as shown in Table 15-1.

Table 15-1 Analyzing the analysis results by RDAREA (logical analysis)

Status in the RDAREADescription
Percentage of total unused pages in the table is less than the percentage of free pages per segment that was specified in the CREATE TABLE definition SQL statement.Table data may be in one of the following statuses:
  • There are only a few unused pages because data was added repeatedly.
  • Used free pages are scattered because data addition, deletion, and updating occurred repeatedly.
If necessary, you can correct the table arrangement by expanding the RDAREA that stores the table and then reorganizing the table.1, 2
The total number of used index storage pages displayed in the analysis results is greater than the number of index storage pages obtained from the number of table storage rows.The index may be in the following status:
  • Because many rows in a specific range were deleted, a large amount of the index was also deleted, resulting in many used free pages.
In this case, you can correct the index arrangement by reorganizing the index. You can adjust the number of used pages to a reasonable value by releasing free index pages. For details about determining the number of index storage pages, see the HiRDB Version 8 Installation and Design Guide.
Table with clusteringUsed free pagesManyPercentage of free pages per segment specified in CREATE TABLE is too large. If necessary, reorganize the table because used free pages have been created due to data addition, deletion, and updating.1
FewIf you are adding data, the table should be reorganized.1
Table without clusteringUsed free pagesManyPercentage of free pages per segment specified in CREATE TABLE is too large. If necessary, reorganize the table because used free pages have been created due to data addition, deletion, and updating.1
FewIf there are only a few unused segments in the RDAREA, expand the RDAREA as required.2
End-of-segment is YLOB column search performance may become poor. Reorganize the LOB RDAREA.1
End-of-Lobmap is YLOB column search performance may become poor. Reorganize the LOB column structure base table.1
Note
If an RDAREA storing the LOB attribute of a plug-in-provided abstract data type or an RDAREA storing a plug-in index is analyzed, the percentage of used segments in the RDAREA depends on the method used by the plug-in to allocate segments. For example, if a plug-in completes allocation of segments during index definition, most of the segments may become used segments immediately upon definition (before data is stored).
1 For details about table and index reorganization, see Chapter 8. Database Reorganization Utility (pdrorg). For details about releasing free index pages, see Chapter 11. Free Page Release Utility (pdreclaim).
2 For details about RDAREA expansion, see Chapter 7. Database Structure Modification Utility (pdmod).