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

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

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.

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

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

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

Check for the need to reorganize tables

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.

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

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.

Tune number of DB2 agents

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.

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

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

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.

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

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

Examine frequently used SQL statements from the package cache

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.

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

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.

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

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

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%

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

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

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.