Analyze the performance of DB2 transactions by transaction type

Transactions are one of the core concepts in the database system. Applications can control the execution and stop of a transaction by starting, committing, and rolling back. From the application perspective, a transaction usually corresponds to a series of closely related user operations. Therefore, the transaction performance is one of the important factors affecting the user experience. This article proposes a method to useDB2Event monitor and snapshot monitor to analyzeTransactionsOfPerformance. In the analysis report, the average transaction execution time, execution times, the execution time of each SQL statement in the transaction, and the application state when the SQL statement is executed are provided. Based on the transaction Performance Analysis Report, database administrators and developers can perform better performance tuning.

Overview

Transactions are one of the core concepts in the database system. As the logical Unit of Work of the database system, transactions must have four attributes: atomicity, consistency, isolation, and persistent ACID ). The database system often uses the lock mechanism to ensure transaction isolation and the log mechanism to ensure transaction persistence. Applications can control the execution and stop of a transaction by starting, committing, and rolling back. From an application perspective, a transaction usually corresponds to a series of closely related user operations, such as deposits and transfers in the banking system. For a user, committing a transaction is equivalent to completing a transaction. Therefore, the time that spans before and after a transaction is one of the factors that affect the user experience.

The performance of the database system is one of the important factors in judging the database system. As a successful database product, DB2 provides many performance tuning features and functions. On the one hand, DB2 provides a large number of configurable parameters at the database manager layer and database layer. You can view and modify these parameters through db2 get/update dbm cfg and db2 get/update db cfg, the Configuration Advisor in Control Center and db2cc can be used to obtain the optimized Configuration parameter values. On the other hand, DB2 provides optimization functions for queries. For example, SQL Explain Facility can be used to analyze an Access Plan after an SQL statement is optimized), and the Command line Editor (Command Editor) A graphical view of the access plan is also provided. However, if you want to monitor and analyze the performance of a transaction, such as the transaction execution time, the execution time of each SQL statement in the transaction, and the idle time in the transaction, it cannot be implemented simply by using existing tools. This article introduces a method to analyze the transaction performance of DB2 to help database designers and administrators optimize database performance.

Logical composition of transactions

A transaction can logically consist of a set of SQL statements and a commit/rollback operation. In DB2, transactions are implicitly started by the first SQL statement sent to the database, without the need to issue a command to start the transaction. All subsequent database read/write operations from the same application are classified into one transaction until the application sends a COMMIT) or ROLLBACK statement. The ROLLBACK statement will cancel all modifications to the database caused by this transaction. If the application does not issue a COMMIT or ROLLBACK, the transaction will be automatically committed. If the application does not exit normally while the transaction is being executed, it will be rolled back automatically. Once the COMMIT/ROLLBACK command is issued, the command cannot be stopped. Because the transaction is composed of a string of SQL statements, there is no physical representation of the transaction.

During the execution of a transaction, the database and application may be in different States. For example, in the transaction shown in figure 1, the application executes three SQL statements sequentially and the COMMIT statement. The application is in the UOW Executing State or Lock wait within the time from t0 to t1. The UOW Executing State indicates that the application is performing database operations, the Lock wait Status refers to the Lock that the application is Waiting for on the database object. The Lock is in UOW Waiting between t1 and t2, And the UOW Waiting indicates that the application has not performed database operations currently. The time consumed by the execution of a transaction may be used to execute SQL statements, execute application code, or wait for the lock. If the performance of a transaction is poor, you need to identify the time consumed in which the changes are made.

This article analyzes the performance of DB2 transactions from the transaction type and provides a deeper analysis of the DB2 database. I hope the content mentioned above will help you.