Determine the tuning goals and warning values for the monitored items. Determine the overall system performance (for example, application processing time and transaction commit count), and then set tuning goals accordingly. Also set warning values for the following monitored items:

• Hardware resource usage rate
  The items to be monitored are the usage rates of hardware resources, such as a server machine's processor and disk volumes.
  
• Global buffer pool status
  The item to be monitored is the global buffer pool hit rate.
  
• Application throughput and SQL execution time
  The items to be monitored are the transaction commit count and SQL execution time.
  

When the usage rates of hardware resources, such as a server machine's processor and disks, become high, tuning might be necessary. High hardware resource usage rates might cause system throughput to decline. This is the reason why you should monitor the usage rates of hardware resources.

Using the OS's sar command (vmstat command for Linux), for example, you can identify hardware resources that have high usage rates, such as the server machine's processor and disks.

Reference note

• Use of JP1/Performance Management - Agent Option for Platform provides you with a facility for checking hardware resource usage rates. For details about JP1/Performance Management - Agent Option for Platform, see the manual For UNIX Systems: Job Management Partner 1/Performance Management - Agent for Platform.
• If you are using a Hitachi disk array system, you can use Hitachi Tuning Manager - Agent for RAID to collect performance data for the disk array system. For details about Hitachi Tuning Manager - Agent for RAID, see the manual Hitachi Tuning Manager - Agent for RAID.

When the global buffer pool hit rate declines, tuning might be necessary. You can execute the pdbufls command to check the global buffer pool hit rate.

Reference note

You can also use JP1/Performance Management - Agent Option for HiRDB to check the global buffer pool hit rate.

When application throughput decline, tuning might be necessary. You can collect statistics on system operations by executing the statistics analysis utility (pdstedit command) and then use this information to check application throughput.

You can also use the SQL runtime warning output facility to monitor SQL execution time. For details about the SQL runtime warning output facility, see 8.9 Output of warning information about the time required for SQL execution (SQL runtime warning output facility).

Reference note

You can also use JP1/Performance Management - Agent Option for HiRDB to check application throughput.

When the arrangement of table data becomes fragmented, tuning might be necessary. The KFPH00212-I or KFPH22017-I message is output when the retrieval rate or storage efficiency declines for a table, so you should watch for these messages.

Reference note

• HiRDB provides a facility for predicting reorganization time, which predicts the date when a table will need to be reorganized. For details about the facility for predicting reorganization time, see 13.4 Predicting table reorganization time (facility for predicting reorganization time).
• You can also use JP1/Performance Management - Agent Option for HiRDB to collect messages related to tables whose data retrieval rates or storage efficiency have declined.

You should perform tuning when any of the items monitored in steps (2) through (4) exceeds its warning value. If you use OS or HiRDB commands to collect data on the items being monitored, compare the currently collected data with previously collected data to determine whether warning values have been exceeded.

If a hardware resource whose warning value has been exceeded is a processor, use an OS command to determine whether HiRDB processes are using many processors.

If a warning is issued by the SQL runtime warning output facility, go to step (8) and perform tuning.

If the data arrangement becomes fragmented in a table being monitored in step (5) and a message is issued, go to step (18) and perform tuning.

Reference note

If you use JP1/Performance Management - Agent Option for Platform, JP1/Performance Management - Agent Option for HiRDB, or Hitachi Tuning Manager - Agent for RAID, you can monitor items by specifying warning values for data in the monitored items. When any of these warning values is exceeded, a warning is issued.

When a hardware resource or global buffer pool hit rate exceeds its warning value in step (6), you must identify the HiRDB file system area to which the hardware resource is related. To collect information about the relationship between the hardware resource or global buffer pool that exceeded its warning value and a HiRDB file system area, you need to summarize the information between individual layers.

You can check SQL_PHYSICAL_FILES in the dictionary table to identify the relationship between a disk volume and a HiRDB file system area, the relationship between a HiRDB file system area and HiRDB files, and the relationships between HiRDB files and RDAREAs. If you are using the inner replica facility, check the SQL_IOS_GENERATIONS table.

For a HiRDB file system area for work table files, you can determine the name of the HiRDB file system area by checking the value specified in the pdwork operand. You can also check the SQL_TABLES table or SQL_INDEXES table in the dictionary table to identify the relationships between RDAREAs and tables (or indexes).

Reference note

• HiRDB CM enables you to acquire information about the relationships among the disk volumes down to the level of tables (or indexes), as shown in the figure.
• If you are using a Hitachi disk array system, Hitachi Tuning Manager - Storage Mapping Agent enables you to acquire information about the relationships among ports in the disk array system and among logical devices. For details about Hitachi Tuning Manager - Storage Mapping Agent, see the manual Hitachi Tuning Manager - Storage Mapping Agent.

If the buffer sector count of a global buffer pool is small, system throughput might have declined or hardware resource usage rates might have increased.

Tune the buffer sector count of the global buffer pool that is related to the HiRDB file system area identified in step (7). If a reduced global buffer pool hit rate necessitated tuning, tune the buffer sector count of the applicable global buffer pool. If application throughput or SQL execution time necessitated tuning, tune the buffer sector counts of all global buffer pools.

For details about the buffer sector count of a global buffer pool, see the HiRDB Version 9 Installation and Design Guide. For details about how to tune global buffer pools, see 23.1 Tuning global buffer pools.

If the tuning goal has been reached by the tuning performed in step (8), terminate tuning. If the tuning goal has not been reached, proceed to the next step and continue tuning.

Identify SQLs to be tuned. Also establish tuning goals for the SQLs.

Use the pdobils command to collect statistics on an SQL object buffer, and make the SQLs listed below the tuning targets. When you collect statistics on an SQL object buffer, you can check each SQL's execution time, number of executions, and number of inputs/outputs.

Set each SQL's target execution time, target number of executions, and target number of inputs/outputs as the tuning goals.

• SQL with the longest execution time
• SQL with the largest number of executions
• SQL with the largest number of inputs/outputs
  
  • SQL with the largest number of real READ counts for data pages, index pages, and directory pages; SQL with the largest number of real READ counts for LOB column data pages; or SQL with the largest number of real READ counts for list pages
  • SQL with a real WRITE count for data pages, index pages, and directory pages; SQL with a real WRITE count for LOB column data pages; or SQL with a real WRITE count for list pages
  • SQL with the largest number of updates for data pages, index pages, and directory pages; SQL with the largest number of updates for LOB column data pages; or SQL with the largest number of updates for list pages
  • SQL with the largest number of READ or WRITE counts for work table files

Execute the statistics analysis utility (pdstedit command) to collect statistics on system operations and check whether lock-release wait has occurred. If it has, collect the server's lock status regularly, and check whether lock-release wait is occurring. If an applicable SQL is using a resource for which there is a lock-release wait, it is safe to assume that lock-release wait is occurring for that tuning-target SQL.

If there is a lock-release wait, release the lock by performing application tuning (lock tuning) in step (12). If no lock-release wait has occurred, tune the optimizing information as described in step (14).

If lock-release wait is occurring for a tuning-target SQL, tune the application (tune the locking). For details about application tuning (tuning locking), see Lock in the HiRDB Version 9 UAP Development Guide.

Check if application tuning has eliminated the lock-release wait that has occurred for the tuning-target SQL. For details about how to perform this check, see step (11).

If lock-release wait has been eliminated, go to step (17) and check if the tuning goal has been achieved. If lock-release wait has not been eliminated, go to step (14) and tune the optimizing information.

Check whether optimizing information has been collected and registered. You can check whether the optimizing information collection utility was executed to save the optimizing information for the table being used by the target SQL in a parameter file that contains optimized information, and whether it was registered in a dictionary table. For details about saving optimizing information in a parameter file that contains optimized information, see 19.3.3 Saving optimizing information to a parameter file that contains optimized information.

If the check result indicates that optimizing information has not been collected or registered, proceed to SQL tuning in step (15).

If optimizing information has been collected and registered, take one of the following actions:

• To tune each SQL separately, delete the optimizing information. After deleting it, carry out SQL tuning as described in step (15). For details about deleting optimizing information, see 19.2.4 Deleting optimizing information.
• Determine whether the optimizing information needs to be updated, and update it if necessary. For details about updating optimizing information, see 19.2.2 Updating optimizing information.
• If a problem such as performance degradation occurs after the optimizing information is updated, restore the optimizing information. For details about restoring optimizing information, see 19.2.3 Restoring optimizing information.

The following table shows SQL tuning methods.

Table 23-1 SQL tuning methods

Check if the tuning goal has been reached for the tuning-target SQL. Collect statistics on the SQL object buffer, and check whether the SQL execution time, execution count, and input/output count are within the target ranges. If the goals have not been reached, return to step (15) and tune the SQL again.

Check if the tuning goal has been reached. If it has, terminate tuning. If the tuning goal has not been reached, return to step (10), identify the tuning-target SQL again, and tune it.

If SQL tuning or application tuning has not produced the desired effects, go to step (18) and tune the database.

If SQL tuning or application tuning does not produce the desired effects, tune the database. Tune the following items:

• Buffer sector count of a global buffer pool
  For details about the sector count of a global buffer pool, see the HiRDB Version 9 Installation and Design Guide. For details about tuning global buffer pools, see 23.1 Tuning global buffer pools.
  
• Work table buffer size
  For details about work table buffer size, see the preparations for executing hash join or subquery hash in the HiRDB Version 9 UAP Development Guide.
  
• Table row partitioning
  For details about table row partitioning, see the HiRDB Version 9 Installation and Design Guide.
  
• Data rearrangement
  For details about data rearrangement, see 23.8 Tuning the database.
  
• Deferred write processing
  For details about deferred write processing, see 23.2 Tuning deferred write processing.
  

Check if tuning the database has achieved the tuning goal. If so, terminate tuning. If the tuning goal has not been reached, go to step (10), identify the tuning-target SQL again, and tune it.

If database tuning has not produced the desired effects, go to the next step and tune hardware resources.

Add or augment hardware resources. Using the OS's sar command (vmstat command for Linux), for example, determine processor usage and disk usage. Add or augment the hardware resource that has the highest usage.

If the hardware resource with the highest usage is a processor, switching to a higher-performance processor might reduce processing time. If the hardware resource with the highest usage is a disk, switching to a higher-performance disk or adding disks (increasing the spindle count) in the case of a disk array device might reduce input/output time.

Reference note

• You can also use JP1/Performance Management - Agent Option for Platform to check processor and disk volume usage.
• If you are using a Hitachi disk array system, you can use Hitachi Tuning Manager - Agent for RAID to collect performance data for the disk array system.