Analyzing the performance of DB2 things by transaction type

Transaction is one of the core concepts in database system. An application can control the execution and stop of a transaction by starting, committing, rolling back, and so on. From the application point of view, a transaction often corresponds to a series of closely related user operations, so the performance of the transaction is one of the important factors that affect the user experience. This paper presents a method to analyze the performance of transactions running on DB2 systems using DB2 event monitors and snapshot monitors. In the analysis report, the average execution time of the transaction, the number of executions, the execution time of each SQL statement in the transaction, and the state of the application when executing the SQL statement are provided. Database administrators and developers can better perform performance tuning based on performance analysis reports for transactions.

Overview

Transaction is one of the core concepts in database system. As a logical unit of work for a database system (Work), a transaction must have four properties, namely atomicity, consistency, isolation, and persistence (ACID). The database system guarantees the isolation of the transaction through the locking mechanism and guarantees the persistence of the transaction through the log mechanism. An application can control the execution and stop of a transaction by starting, committing, rolling back, and so on. From an application point of view, a transaction often corresponds to a series of closely related user actions, such as deposits, transfers, etc. in the banking system. For a user, committing a transaction is equivalent to completing a transaction, so the time span before and after a transaction is one of the factors that affect the user experience.

The performance of database system is one of the important factors to judge the database system, DB2 as a successful database product provides many features and functions of performance tuning. On the one hand DB2 provides a large number of configurable parameters at the Database Manager layer and the database layer, which can be viewed and modified through the DB2 get/update dbm CFG and DB2 get/update DB CFG, and can be passed through control center, DB 2CC) to obtain the optimized configuration parameter values for the Configuration Advisor. On the other hand, DB2 provides optimization for queries, such as SQL Explain facility can analyze an access plan that is optimized for an SQL statement (Access plans), and also provides a graphical view of the access plan in the command-line editor (Command Editor). However, if you want to monitor and analyze the performance of a transaction, such as the execution time of a transaction, the execution time of each SQL statement in a transaction, the idle time in the transaction, etc., you cannot simply implement it through the existing tools. This article will introduce a way to analyze the transactional performance of DB2 to help database designers and administrators tune database performance.

The logical composition of a transaction

A transaction can logically consist of a set of SQL statements and a commit/rollback operation. In DB2, a transaction is initiated implicitly by the first SQL statement issued to the database without the need to issue a command to start the transaction. All subsequent database reads and writes from the same application are grouped into one transaction until the application issues a commit (commit) or ROLLBACK (rollback) statement. The ROLLBACK statement cancels all changes to the database caused by this transaction. This transaction is automatically committed if the application quits normally without committing a COMMIT or ROLLBACK. If the application does not exit gracefully on the way things are performing, it is automatically rolled back. Once the Commit/rollback command is issued, the command cannot be stopped. Because transactions are made up of just a bunch of SQL statements, there is no physical representation of the transaction.

The database and application may be in a different state during the execution of a transaction. For example, in the transaction shown in Figure 1, the application sequentially executes 3 SQL statements and executes a COMMIT statement. In T0 to T1 time the application is in the UOW executing state or lock wait, where the UOW executing state refers to the application executing the database operation, and the lock wait state refers to the application waiting for the lock on the database object; T1 to T 2 time in UOW waiting, UOW waiting means that the application does not currently have database operations. The time it takes to execute a transaction may be used to execute SQL statements, execute application code, or wait for a lock, and if the performance of a particular class of transactions is poor, it needs to be resolved in which respect the time is consumed and adjustments are made.

Figure 1. The logical composition of a transaction