Performance

Question: I defined an index for a table in order to use the index to achieve high-speed retrieval, but the expected retrieval performance was not obtained. Is it possible that the index was not used?

Answer

An index might not be used for retrieval if the optimizing information collection utility (pdgetcst command) has not been used in the database's most recent status. For details about whether execution of the optimizing information collection utility is required, see the manual HiRDB Version 9 Command Reference.

If any of the events listed below occurs with respect to a table that has been optimized based on cost using the optimizing information collection utility (pdgetcst command), the pdgetcst command must be executed again to obtain the most recent optimization information:

A large amount of data has been added, updated, or deleted
The database load utility (pdload command) or the database reorganization utility (pdrorg command) has been executed
Table definition has been modified, or an index has been added or deleted

Remarks: The access path display utility (pdvwopt command) can be used to determine whether an index is being used for retrieval.

Question: How can I check a buffer's utilization status (such as the hit rate) and the number of read/write operations in order to tune the buffer?

Answer

A buffer's hit rate and the real READ/WRITE counts can be obtained by editing the global buffer statistical information with the statistics analysis utility (pdstedit command). Start collecting statistical information by executing the statistics information output start command (pdstbegin command) with buf specified as the statistical information type. Note that this is the differential information between synchronization point dumps; therefore, the information will not be edited or output until two synchronization point dumps have been validated. If necessary, use the following commands to forcibly swap the system log files and validate synchronization point dumps:

pdstbegin -k buf: Starts collection of statistical information
pdlogsync: Validates synchronization point dumps

Range of statistical information collection:

pdlogsync: Validates synchronization point dumps
pdstedit: Edits and outputs the statistical information

Remarks: The same information as obtained by the statistics analysis utility can be displayed easily by entering the pdbufls command. In this case, the differential information between two pdbufls command entries is displayed; therefore, execute the pdbufls command twice, once at the beginning and once at the end of the desired range of statistical information collection.

Appendix A.8 Performance

(1) The expected retrieval performance cannot be obtained

(2) Is it possible to check the buffer utilization status and the number of read/write operations?