In performance testing, it is very common that the slow page is due to slow queries in database. We always need to review database snapshot got during the test.
Since DB2 V9.7 and later, DB2 published new MON_TABLE_ APIs and admin vies to monitor database performance.
While the output data is very dynamic and accurate in time, it is difficult to read and analyze. Bryan Tsai from Tivoli performance team developed a tool to view the output of some of the table functions’ output, as well as snapshot output.
Steps to get db2 package cache:
1. db2 "connect to <db_name>
2. db2 "SELECT * FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T WHERE T.NUM_EXEC_WITH_METRICS <> 0" > output_file
3. db2 "disconnect all"
Package cache is where DB2 stores static and dynamic queries, access plans, and information about the execution of those queries.
The output is in table format and really not easy to read directly, especially the output is got under high workload with thousands of different sql executions in database.
We can open the output in Performance Analysis Suite and it extracts and displays the information in a user-friendly format:
Performance Analysis Suite can be used to view DB2 snapshot and package cache. Besides that, it also can be used to analyze Java verbose GC log, JVM thread dump etc. To learn more about “Performance Analysis Suite”:
For example, in one of our tests, we found one high I/O query.
Step 1, setup DB2 EXPLAIN tables:
db2 -tvf ~/sqllib/misc/EXPLAIN.DDL
Next, we use db2advis tool to generate index recommandations.
Step2. put slow query into a text file.
Step 3, Connect to the database and issue:
db2 connect to tridb
Step 4, run advisor on the sqls
db2advis -d tridb -i SQLs.txt -o SQLs.advis.db2
The output in file SQLs.advis.db2 looks:
Step 5, review and apply recommendations：
db2 -tvf SQLs.advis.db2
This article mainly introduce the base knowledge of the performance tuning. We will share more tuning methods for DB2 database in our process of optimization.