Tuning the database

Purpose: Physical analysis information is collected for each RDAREA in order to determine how appropriate the utilization efficiency and size of the RDAREA are with respect to the defined RDAREA size and segment size.; The validity of the utilization efficiency and size of an RDAREA is evaluated by analyzing the storage status of all segments and pages in the RDAREA without taking into account the tables or indexes.

Evaluating the analysis results

When the ratio of used segments is low

Consider that there are many unused segments, and study the following:

If one RDAREA consists of multiple HiRDB files, check the sizes of the files to determine whether they are being used.
Check the amount of data to be added in the future, and the table and index definitions.

When the ratio of used segments is high and the ratio of used pages is low

Consider the following factors, and check that the ratio of free pages in the segments is not too large:

Consider whether the RDAREA is being used efficiently.
A common reason for a large ratio of used segments occurs when the segment size specified during RDAREA definition is larger than the volume of the RDAREA, which reduces the number of segments such that the defined tables and indexes use all of the segments.

When the ratio of used segments is high and the ratio of used pages is high

Possible causes are:

There is not enough free space in the RDAREA.
If the ratio of full pages is high, the size might be insufficient.

Actions to be taken

Check the validity of the segment size specified when the RDAREA was created. Determine the validity by checking the numbers of tables or index and data items stored in the RDAREA. If the ratio of full pages is too high or too low, take the following actions:

If the ratio of full pages is too high, expand the RDAREA with the database structure modification utility. Or, move some of the tables or indexes from this RDAREA to another RDAREA.
If the ratio of full pages is too low, the storage efficiency inside the RDAREA might have declined, resulting in fragmented data placement. In this case, use the database reorganization utility to reorganize the table by RDAREA.
In the case of the data dictionary RDAREA, reorganize it with the database reorganization utility with dir specified in the -C option. When the data dictionary RDAREA is reorganized, reorganization of specified data dictionary tables or reorganization of all data dictionary tables can be selected. When specific data dictionary tables are not specified, the entire data dictionary RDAREA is reorganized.

(2) Logical analysis information for an RDAREA

Purpose

Logical analysis information is collected for each RDAREA in order to determine how appropriate the following are:

Whether the size of each RDAREA matches the estimated size
Whether the database should be reorganized

The storage status of all segments and all pages of the tables or indexes in an RDAREA should be analyzed.

Evaluating the analysis results

Table condition analysis information

When the ratio of used segments is high
If there are no unused segments in the relevant RDAREAs, it can be considered that no more data can be inserted. In this case, consider the availability of free space (PCTFREE) for subsequent data additions and updates.
When the ratio of used segments is high, and the ratio of used pages is low
Consider that the ratio of segment free pages specified in the PCTFREE operand is not appropriate. In this case, consider the availability of free space (PCTFREE) for subsequent data additions and updates.
When there are more used pages than the estimated number of pages
If there are VARCHAR, NVARCHAR, or MVARCHAR columns, consider whether the data length of any of these columns has exceeded 255 bytes, and determine whether this was considered in the calculation.
When the ratio of full segments and the ratio of full pages are both high
The RDAREA size is inadequate, or disorder has occurred in the data arrangement because the following operations were performed many times:

Updating of null-value data to numeric or character data
Updating of a VARCHAR, NVARCHAR, or MVARCHAR column resulting in a longer column
Updating of column value to the null value, or updating of a VARCHAR, NVARCHAR, or MVARCHAR column resulting in a shorter column

Index condition analysis information: If the number of used segments and used pages is greater than what was calculated in advance, and if the addition of a large number of key values has caused index splitting to occur, consider that the page usage rate might have increased.

Actions to be taken

Either expand the RDAREA or store some of the tables in different RDAREAs (if multiple tables are stored in the RDAREA).
Reorganize the table with the database reorganization utility.
Reevaluate the ratio of free pages in the segment that was specified in the PCTFREE operand during table definition.
Reevaluate the ratio of unused area in a page that was specified in the PCTFREE operand during table definition.

(3) Logical analysis information for a table or index

Purpose: If a table was row-partitioned, check that the table and any index were partitioned properly. This can be done by comparing the amount of output data (numbers of segments and pages) and the estimated amount of data. Also, whether the database should be reorganized can be determined by checking the table or index storage condition.

Evaluating the analysis results

Table condition analysis information

When the ratio of full segments or full pages is 80% or higher
If the ratio of used pages is much higher than expected from the estimated number of segments, consider that the table storage status might have become fragmented.
When a table is row-partitioned
If the number of used segments in a specific RDAREA is large, or if the number of rows stored in a specific RDAREA is large, consider that the tables might not be optimally partitioned. Reconsider the table allocation from the estimated data volume. If hash partitioning is already being used, change the hash function. At this time, check the number of rows stored in each RDAREA, and ensure that the rows are stored uniformly.
When the ratio of total unused pages for a table { (total-number-of-pages - total-number-of-used-pages) total-number-of-pages} is less than the ratio of free pages of segments specified in the table definition
Probably, there are too few unused pages due to repeated addition of data.
When there are not enough free pages to add data
Consider that the ratio of free pages for segments is too large, or that data deletion has caused an increase in the number of used free pages.

Index condition analysis information

When the number of pages that store indexes calculated from the number of rows stored in the tables is less than the total number of used pages in the analysis result
Consider that a large number of rows in a specific range were deleted, which caused the related index key values to also be deleted, resulting in used free pages being created in the index pages.
When the used pages are almost all full pages
If this occurs even after the database reorganization utility has been used to reorganize the indexes, consider that the ratio of segment free pages is not optimal or that data additions have used up the free area in the pages.

Actions to be taken

Table condition analysis information

When the ratio of full segments or full pages is high
If new data is to be added in the future, reorganize the corresponding table.
When there are many free pages (unused pages)
Re-examine the ratio of free pages in the segment.
When data deletions have increased the number of used free pages
Reorganize the tables, reorganize the indexes, or release the used free pages.
When there are few unused segments in an RDAREA
Expand the RDAREA.

Index condition analysis information

When there are many used free pages
Reorganize the tables, reorganize the indexes, or release the used free pages.
When more data is expected to be added in the future
If the column data might occur at random for the defined index, redefine the index and specify an appropriate value in the PCTFREE operand.
When the number of used pages is much greater in a specific RDAREA than in other RDAREAs
Reevaluate the table partitioning method. Partition the table so that the amount of partitioned data is the same in each RDAREA.
When there are many used free pages in specific RDAREAs only
Reorganize the tables and indexes in those RDAREAs, or release the used free pages.

(4) Storage condition analysis for cluster key and clustering data pages

Purpose

If a cluster key is defined, check the storage disorder rate, which indicates the disorder of tables and indexes, and the number of keys stored with a duplicate structure.

Cluster key storage condition analysis: The cluster key is searched in the order of key values to display the storage location changes count where the storage location spans multiple pages or segments and the storage disorders count (rate) where of the storage location changes count, the storage sequence is opposite to the ascending order of pages (segments) in units of pages and segments.; Page splitting increases the storage disorder rate.

Clustering data page storage condition analysis: A data page for a table with a cluster key defined is called a clustering data page.; The row data storage location changes count and the storage disorders count (rate) are displayed in units of pages and segments based on the storage location information for the row data in the cluster key when the row data is searched in the order of cluster key values.; If the storage condition becomes poor due to row addition and update processing, the storage disorder rate or the storage location changes count or both increase.

Evaluating the analysis results

Cluster key storage condition analysis information

When there are too many rows for the number of storage keys in an index
The performance of retrieval processing using the index is affected adversely because the key duplication rate is high.
When some keys are stored with duplicate key structure
The performance of retrieval processing using the index is affected adversely because there are (or there were) keys with a high duplication level.
When the storage disorder rate is high
Retrieval performance is affected adversely because there is some disorder in the data page storage sequence.

Clustering data page storage condition analysis information

When the storage disorder rate is high
The performance of accesses in the order of cluster key values is affected adversely because there is some disorder in the data page storage sequence.
When the storage location changes count is greater than the number of segments or pages used in the condition analysis result for each table minus 1
The performance of accesses in the order of cluster key values is affected adversely because there is some disorder in the data page storage sequence.

Actions to be taken

Cluster key storage condition analysis information

When there are too many rows for the number of storage keys in an index
Reevaluate the column structure in the index definition.
When some keys are stored with duplicate key structure
If there is any key with a high duplication level, redefine the table with the cluster key without using those index structure columns that have a high data duplication level. If in the past there was a key with a high duplication level, the duplicate key structure will be eliminated when the table is reorganized or the index is re-created by the database reorganization utility.
When the storage disorder rate is high
Reorganize the table or re-create the index with the database reorganization utility.

Clustering data page storage condition analysis information

When the storage disorder rate is high
Reorganize the table with the database reorganization utility.
The number of storage unit changes is greater than one less than the number of used segments or pages, based on condition analysis of table units.
Reorganize the table with the database reorganization utility.

23.8 Tuning the database

(1) Physical analysis information for each RDAREA

(2) Logical analysis information for an RDAREA

(3) Logical analysis information for a table or index

(4) Storage condition analysis for cluster key and clustering data pages