Simplify performance management and tuning with DB2 Performance Expert: Usage scenarios to analyze and improve database performance

Part 2 in a series explaining how to quickly troubleshoot and tune your DB2 UDB servers

Part 1 of this article introduced the DB2® Performance Expert (DB2 PE), a tool that simplifies the tasks of monitoring and administering your DB2 UDB servers. Now, Part 2 shows you a number of practical scenarios, demonstrating how to use the tool to analyze factors which directly affect database performance and how to troubleshoot problems.

Werner Schuetz (werner_schuetz@de.ibm.com), Technical Consultant DB2, IBM Innovation Center Stuttgart, IBM, Software Group

Werner Schuetz photoWerner Schuetz is an IBM Certified IT Specialist, IBM Certified Advanced Database Administrator and Certified Application Developer for DB2 UDB V8. He works as a DB2 Technical Consultant in the IBM Innovation Center in Stuttgart, Germany and supports Independant Software Vendors in testing DB2 solutions, executing performance and tuning sessions and running competitve database migrations.



16 September 2004

Introduction

Do you have a need for detailed analysis of key performance factors that let you control and tune the performance of DB2 and DB2 application? Are you expected to proactively diagnose performance and availability problems? Or have you experienced a problem operating your DB2 server, but could not determine the cause of the problem using current snapshots, and you wished you had historical monitoring data? IBM DB2 Performance Expert is a tool which can assist you in all these tasks.


Usage scenarios

The following usage scenarios demonstrate how to analyze and resolve various performance issues and troubleshooting tasks with the help of DB2 Performance Expert V2.1:


Determine need for an index

DB2 PE steps

  1. In the System Overview Panel select Application Summary.
    Figure 1. System Overview
    Sample display screen
  2. Select the appropriate application in the Application Summary view (in this example, db2bp.exe).
    Figure 2. Application Summary
    Sample display screen
  3. Select SQL Activity in the Application Details view.
    Figure 3. Application Details
    Sample display screen

Approach

The SQL Activity screen that you see in Figure 3 shows detailed information about the statements issued by the application, including units of work (UOW), cursors, rows read, rows selected, and so on. To determine whether or not we need an index, we need to look at the ratio of rows read to rows selected.

Rows read vs. rows selected
The ratio of rows read to rows selected indicates how many rows had to be read in order to find the target rows. If the number of rows read compared to rows selected is much higher than the recommended value, the query should be analyzed and possible indexes should be checked.

Calculate: (Rows read) / (Rows selected)
Good Value: 2 to 3 for OLTP

Conclusion

DB2 is reading 99,145 rows to select only 2,000. That is, it reads the whole table to select only 2,000 rows. The creation of an index will probably improve performance.


Review sort performance

DB2 PE Steps

  1. In the System Overview panel select Application Summary.
  2. Select the appropriate application in the Application Summary view (db2bp.exe in this example).
  3. Select Sort in the Application Details view, as shown in Figure 4.
    Figure 4. Application Details
    Sample display screen

Approach

The Sort screen shows detailed information about sort activities, including total sorts, total sort time, sort overflows, hash joins, and so on.

Sort overflow
This number indicates the total number of sorts that ran out of sort heap and may have required disk space for temporary storage.

At a database or application level, use this element to calculate the percentage of sorts that had to overflow to disk. If this percentage is high, you may want adjust the database configuration by increasing the value of sortheap. At a statement level, use this element to identify statements that require large sorts. These statements may benefit from additional tuning to reduce the amount of sorting required. When a sort overflows, additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk. This element provides information for one statement, one application, or all applications accessing one database. Basically, the data to be sorted spills over (overflows) into the TEMPSPACE tablespace from the bufferpool.

Calculate: (Sort overflows) / (Total sorts)
Good Value: zero or near zero at steady state for non-DSS workloads

Conclusion

When a sort overflows happens, additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk. To avoid sort overflow, increase the sort heap size and analyze the query to see if the query is using indexes.


Check for the need to reorganize tables

DB2 PE Steps

  1. In the System Overview Panel select Statistic Details.
    Figure 5. System Overview
    System Overview
  2. Select Tables in the Statistic Details view and check Receive table information.
    Figure 6. Statistic Details
    Statistic Details

Approach

The Table view in Statistic Details screen shows detailed information about tables, including table name, database name, rows written, rows read, overflowed rows, table file id, table type, page reorg, and so on.

Accesses to overflowed rows
This number indicates the total number of accesses (reads and writes) to overflowed rows of this table.

Overflowed rows indicate that data fragmentation has occurred. If this number is high, you may be able to improve table performance by reorganizing the table using the REORG utility, which cleans up this fragmentation. A row overflows when it is updated and no longer fits in the data page where it was originally written. This usually happens as a result of an update of a VARCHAR column, or as a result of an ALTER TABLE statement.

Page Reorganizations:
This number indicates The number of page reorganizations executed for a table.

Too many page reorganizations can result in less than optimal insert performance. You can use the REORG TABLE utility to reorganize a table and eliminate fragmentation. You can also use the APPEND parameter for the ALTER TABLE statement to designate that all inserts be appended to a table and so avoid page reorgs. In situations where updates to rows cause the row length to increase, the page may have enough space to accommodate the new row, but a page reorg may be required to defragment that space. Or, if the page does not have enough space for the new larger row, an overflow record is created. You can avoid both situations by using fixed length columns instead of varying length columns.

Conclusion

Too many page reorganizations can result in less than optimal insert performance. If you have a high number of page reorganizations, use the REORG TABLE utility to reorganize the table and eliminate fragmentation.


Tune number of DB2 agents

Your objective here is to ensure that there are enough DB2 agents to handle the workload.

DB2 PE steps

  1. In the System Overview Panel select Statistic Details.
  2. Select Instance Information in the Statistic Details view, as shown in Figure 7.
    Figure 7. Statistic Details
    Sample display screen

Approach

The Instance Information view in the Statistic Details screen shows detailed information about the current instance, including instance name, current connections, agents registered, maximum agents registered, agents waiting for a token, agents assigned from pool, stolen agents, and so on.

Agents registered
This indicates the number of agents registered in the database manager instance that is being monitored (coordinator agents and subagents). You can use this element to help evaluate your setting for the maxagents configuration parameter.

Maximum agents registered
This is the maximum number of agents that the database manager has ever registered, at the same time, since it was started (coordinator agents and subagents). You may use this element to help you evaluate your setting of the maxagents configuration parameter.

Agents waiting for token
This is the number of agents waiting for a token so they can execute a transaction in the database manager. You may use this element to help evaluate your setting for the maxcagents configuration parameter.

Stolen agents:
This is the number of times that agents are stolen from an application. Agents are stolen when an idle agent associated with an application is reassigned to work on a different application. This element can be used to evaluate the load that this application places on the system.

Conclusion

If you find either "Agents waiting for a token" or "Stolen agents", increase the number of agents available to the database manager (MAXAGENTS and/or MAX_COORDAGENTS as appropriate).


Resolve lock conflicts

DB2 PE steps

  1. In the System Overview Panel select Locking Conflicts
    Figure 8. System Overview
    System Overview
  2. Select the appropriate locking conflict in the Locking Conflicts view.
    Figure 9. Locking Conflicts
    Locking Conflicts
  3. In order to analyze an application waiting on a lock, select a Waiter application.
    Figure 10. Applications in Lock Conflicts - waiter application
    waiter application
  4. Select SQL Statement and Package from the Application Details view.
    Figure 11. SQL Statement and Package for waiter application
    SQL Statement and Package for waiter application
  5. Also select Locks from the Application Details view.
    Figure 12. Locks for waiter application
    Locks for waiter application
  6. In order to analyze an application holding locks, select a Holder application.
    Figure 13. Applications in lock conflicts - holder application
    Applications in Lock Conflicts - holder application
  7. Select SQL Statement and Package from the Application Details view.
    Figure 14. SQL Statement and Package for holder application
    SQL Statement and Package for holder application
  8. Also select Locks from the Application Details view.
    Figure 15. Locks for holder application
    Locks for holder application
  9. To find out the user who is running that holder application, select Identification from the Application Details view.
    Figure 16. User Identification for holder application
    User Identification for holder application

Approach

Selecting Applications in Lock Conflicts in the System Overview pane displays all applications involved in a locking situation, while the applications associated with a specific resource are shown when you select Locking Conflicts.

The Application in Lock Conflicts pane shows the holder and the waiter applications, including their application status, lock mode, lock wait time, and so on.

The SQL Statement and Package selection in the Application Details view reveals the SQL statement of the locking application.

The Locks selection in the Application Details view displays detailed locking information such as locks held by the application, deadlocks detected, lock escalations, agents waiting on locks, and so on.

The Identifcation selection in the Application Details view shows detailed information about the user who is running this application.

Locks held by application:
This number indicates how many locks are currently held.

If the monitor information is at the database level, this is the total number of locks currently held by all applications in the database. If the monitor information is at the application level, this is the total number of locks currently held by all agents for the application.

Lock waits since connect:
This is the total number of times that applications or connections have waited for locks.

At the database level, this is the total number of times that applications have had to wait for locks within this database. At the application-connection level, this is the total number of times that this connection requested a lock but had to wait because another connection was already holding a lock on the data. This element may be used to calculate, at the database level, the average wait time for a lock.This calculation can be done at either the database or the application-connection level. If the average lock wait time is high, you should look for applications that hold many locks, or have lock escalation, with a focus on tuning your applications to improve concurrency, if appropriate. If escalation is the reason for a high average lock wait time, then the values of one or both of the locklist and maxlocks configuration parameters may be too low.

Lock escalations
This number indicates the number of times a lock request was made as part of a lock escalation. Escalation is from many row locks (in a single table) to a single table lock.

Use this element to better understand the cause of deadlocks. If you experience a deadlock that involves applications doing lock escalation, you may want to increase the amount of lock memory (locklist) or change the percentage of locks that any one application can request (maxlocks).

Deadlocks detected
This is the total number of deadlocks that have occurred.

This element can indicate that applications are experiencing contention problems. These problems could be caused by the following situations:

  • Lock escalation is occurring in the database.
  • An application may be locking tables explicitly when system-generated row locks may be sufficient.
  • An application may be using an inappropriate isolation level when binding.
  • Catalog tables are locked for repeatable read.
  • Applications are getting the same locks in different orders, resulting in deadlocks.

You may be able to resolve the problem by determining in which applications (or application processes) the deadlocks are occurring. You may then be able to modify the application to better enable it to execute concurrently. Some applications, however, may not be capable of running concurrently. You can use the connection timestamp monitor elements to determine the severity of the deadlocks. For example, 10 deadlocks in 5 minutes is much more severe than 10 deadlocks in 5 hours. The descriptions for the related elements listed above may also provide additional tuning suggestions.

Agents waiting on locks:
This Indicates the number of agents waiting on a lock.

This element is an indicator of the percentage of applications waiting on locks. If this number is high, your applications may have concurrency problems, and you should identify applications that are holding locks or exclusive locks for long periods of time.

Conclusion:

Examine the SQL Statements for the waiter and the holder applications. Decide on actions like comitting frequently in the application to release locks or checking on the isolation level used for the application. When performing many updates, lock the entire table for the duration of the transaction before updating. This uses only one lock and keeps others from interfering with the updates, but it does reduce concurrency of the data to other users.


Examine frequently used SQL statements from the package cache

DB2 PE Steps

  1. In the System Overview Panel select Statistic Details.
  2. Select Dynamic SQL Statements in the Statistic Details view.
    Figure 17. Dynamic SQL statements
    Dynamic SQL Statements
  3. Drill down further to see the statement details.
    Figure 18. Statement details
    Statement Details

Approach

The Dynamic SQL Statements view in Statistic Details screen provides detailed information on SQL statements, including databases accessed, executions, elapsed execution time, worst and best preperation time, sorts, CPU time per statement, and so on when Receive statement cache information is checked.

By pressing the Executions column in the title, shown in Figure 17, SQL statements can be sorted descending according to the number of executions, and the most frequent SQL stamements can be examined.

Executions
This is the number of times that an SQL statement has been executed. You can use this element to identify the most frequently executed SQL statements in your system.

CPU per statement
This number indicates the total system CPU time for an SQL statement. Use this element with "Elapsed statement execution time" and "CPU per user statement" to evaluate which statements are the most expensive.

Best preparation time:
This is the shortest amount of time that was required to prepare a specific SQL statement. Use this value to identify SQL statements that are expensive to compile.

Worst Preparation Time:
The longest amount of time in microseconds that was required to prepare a specific SQL statement. Use this value to identify SQL statements that are expensive to compile.

Conclusion

The Executions monitor element helps to identify the most frequently executed SQL statements in your system. In this case one query run 500 times and had 500 sorts. This is a good candidate for query optimization and checking sort values and verifying if there is a need to create new indexes.


Analyze buffer pools

DB2 PE steps

  1. In the System Overview Panel select Buffer Pool Analysis.
    Figure 19. System Overview
    System Overview
  2. Select File -> Generate new report in the Buffer Analysis view.
    Figure 20. Buffer pool analysis
    Buffer pool analysisFigure 21 shows the resulting buffer pool trace report.
    Figure 21. Buffer pool trace report
    Sample display screen
  3. Drill down for the buffer pool analysis details.
    Figure 22. Buffer pool analysis details
    Buffer Pool Analysis Details

Approach

The Buffer Pool Analysis provides buffer pool trace reports which are displayed in HTML format or interactive reports with optional graphical charts.

Buffer pool hit ratio
This ratio indicates the percentage of time that the database manager did not need to load a page from disk in order to service a page request, because the page was already in the buffer pool.

To calculate:
BPHR = (1 - ((buffer pool data physical reads + buffer pool index physical reads) /
(buffer pool data logical reads + pool index logical reads) ) ) * 100%

Index hit ratio
This ratio indicates the percentage of all read requests for index pages that were satisfied by a page found in the buffer pool.

To calculate:
IHR = (1 - (buffer pool index physical reads / buffer pool index logical reads) ) ) * 100%

Data hit ratio
This ratio indicates the percentage of all read requests for data pages that were satisfied by a page found in the buffer pool.

To calculate:
DHR = (1 - (buffer pool data physical reads / buffer pool data logical reads) ) ) * 100%

Conclusion

A buffer pool hit ratio above 80% is considered good. Ideally for an OLTP system it should be as close as possible to 100% (especially the index hit ratio).

To increase the buffer pool hit ratio, either try increasing the buffer pool size, or consider allocating multiple buffer pools, possibly one for each frequently-accessed large table with its own table space, and one for a group of small tables.


Monitor system health

DB2 PE Steps

  1. In the System Overview Panel select System Health.
    Figure 23. System overview
    System overview
  2. Select Data View in the navigator.
    Figure 24. Data view
    data view
  3. Right click Open Predefined Data View
    Figure 25. Open Predefined Data View
    Open Predefined Data View
  4. Select the database you want to monitor.
    Figure 26. Open Predefined Data View - Select database
    Open Predefined Data View - Select database
  5. Figure 27 illustrates the system health view.
    Figure 27. System Health View
    System Health View

Approach

System Health displays important performance counters in graphical form in data views. You may either use predefined data views, or you can customize your own data views.

Conclusion

System Health is an ideal tool to graphically monitor important performance values. Once defined they will be displayed in the System Overview pane as well.


Summary

Part 1 of this article introduced the DB2 Performance Expert, and Part 2 has illustrated specific ways it can be used to make the job of tuning your databases and your systems easier. You can use DB2 PE to help you understand many factors which affect performance, such as indexes, buffer pool use, statement caching, locking, reorg requirements, and many others. In addition it can be used to warehouse performance data for later analysis, and to generate alerts based on many different factors which you can define.


Acknowledgement

Special thanks to Ute Baumbach, IBM Development Lab DB2 Performance Expert, for reviewing this article and to Cintia Y Ogura, Advanced Technical Support, IBM US, for providing education material.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=16290
ArticleTitle=Simplify performance management and tuning with DB2 Performance Expert: Usage scenarios to analyze and improve database performance
publish-date=09162004