 | Level: Introductory Werner Schuetz (werner_schuetz@de.ibm.com), Technical Consultant DB2, IBM Innovation Center Stuttgart, IBM
16 Sep 2004 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.
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
- In the System Overview Panel select Application Summary.
Figure 1. System Overview
- Select the appropriate application in the Application Summary view (in this example, db2bp.exe).
Figure 2. Application Summary
- Select SQL Activity in the Application Details view.
Figure 3. Application Details
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
- In the System Overview panel select Application Summary.
- Select the appropriate application in the Application Summary view (db2bp.exe in this example).
- Select Sort in the Application Details view, as shown in Figure 4.
Figure 4. Application Details
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
- In the System Overview Panel select Statistic Details.
Figure 5. System Overview
- Select Tables in the Statistic Details view and check Receive table information.
Figure 6. 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
- In the System Overview Panel select Statistic Details.
- Select Instance Information in the Statistic Details view, as shown in Figure 7.
Figure 7. Statistic Details
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
- In the System Overview Panel select Locking Conflicts
Figure 8. System Overview
- Select the appropriate locking conflict in the Locking Conflicts view.
Figure 9. Locking Conflicts
- In order to analyze an application waiting on a lock, select a Waiter application.
Figure 10. Applications in Lock Conflicts - waiter application
- Select SQL Statement and Package from the Application Details view.
Figure 11. SQL Statement and Package for waiter application
- Also select Locks from the Application Details view.
Figure 12. Locks for waiter application
- In order to analyze an application holding locks, select a Holder application.
Figure 13. Applications in lock conflicts - holder application
- Select SQL Statement and Package from the Application Details view.
Figure 14. SQL Statement and Package for holder application
- Also select Locks from the Application Details view.
Figure 15. Locks for holder application
- 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
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
- In the System Overview Panel select Statistic Details.
- Select Dynamic SQL Statements in the Statistic Details view.
Figure 17. Dynamic SQL statements
- Drill down further to see the statement details.
Figure 18. 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
- In the System Overview Panel select Buffer Pool Analysis.
Figure 19. System Overview
- Select File -> Generate new report in the Buffer Analysis view.
Figure 20. Buffer pool analysis
Figure 21 shows the resulting buffer pool trace report.
Figure 21. Buffer pool trace report
- Drill down for the buffer pool analysis details.
Figure 22. 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
- In the System Overview Panel select System Health.
Figure 23. System overview
- Select Data View in the navigator.
Figure 24. Data view
- Right click Open Predefined Data View
Figure 25. Open Predefined Data View
- Select the database you want to monitor.
Figure 26. Open Predefined Data View - Select database
- Figure 27 illustrates the system health view.
Figure 27. 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
About the author | 
| | Werner 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. |
Rate this page
| |
