DB2 Performance Expert simplifies Performance management and tuning 2 (1)

Introduction

Do you need to analyze in detail some key performance factors that enable you to control and tune DB2 and DB2 applications? Do you want to diagnose performance and availability issues in advance? Or have you ever encountered a problem when using the DB2 server, but you cannot use the current snapshot to determine the cause of the problem, so you want to use historical monitoring data? IBM DB2 Performance Expert is a tool that helps you complete these tasks.

Use Cases

The following scenarios show how to analyze and solve various Performance problems and complete troubleshooting tasks with the help of DB2 Performance Expert V2.1:

• Determine whether indexes can improve performance
• Review sorting performance
• Check the need for table Reconstruction
• Ensure that there is sufficient DB2 proxy to handle the workload
• Resolve lock conflicts
• Check the database frequently using the SQL statements provided in the cache package.
• Analysis Buffer Pool
• Monitor System Health Status

Are you sure you want to index

DB2 PE steps

1. On the System Overview panel, selectApplication Summary.
   

   Figure 1. System Overview
   

2. In the Application Summary view, select the appropriate Application in this example db2bp.exe ).
   

   Figure 2. Application Summary
   

3. Select SQL Activity in Application Details View.

   Figure 3. Application Details
   

Method
The SQL Activity interface shown in Figure 3 shows information about the statements executed by the application, including the task unit UOW, cursor, read row, and selected row. To determine whether an index is required, you need to check the ratio of the read row to the selected row.

Read rows and selected rows
The ratio of the read row to the selected row indicates the total number of rows of data to be read in order to find the target record row. If the ratio of the number of read rows to the number of selected rows is greater than the recommended value, we should analyze the query and check the possible indexes.

Calculation: (number of rows read)/(number of rows selected)
Ideal Value: 2 to 3 for OLTP

Conclusion
DB2 reads 99,145 rows, but only selects 2,000 rows. That is to say, it reads the content of the entire table, but selects only 2,000 rows. Therefore, creating an index may improve the performance.