Shulou(Shulou.com)06/01 Report--

In the process of DB2 tuning, we often want to obtain the slow sql in the current production environment, which can be precisely tuned for a specific slow sql. The following scripts are available, but the operation is still tedious and needs to be run manually.

Db2 connect to tablename Db2-x "select current timestamp | |'| | stmt_text | |'| | member | |'| | NUM_EXEC_WITH_METRICS | |'| | TOTAL_ACT_TIME | |'| | TOTAL_ACT_WAIT_TIME | |'| | TOTAL_CPU_TIME | |'| POOL_READ_TIME |'| LOCK_WAIT_TIME | | LOCK_WAIT_TIME | | TOTAL_SECTION_SORT_TIME | |'| | TOTAL_SECTION_SORTS | ROWS_READ | |'| ROWS | _ RETURNED | |'|'| POOL_DATA_L_READS | |'| | POOL_TEMP_DATA_L_READS | |'| | POOL_INDEX_L_READS | |'| | POOL_TEMP_INDEX_L_READS | |'| | POOL_DATA_P_READS | |'| | POOL_TEMP_DATA_P_READS | POOL_INDEX_P_READS | |'| | POOL_TEMP_INDEX_P_READS | SORT_OVERFLOWS | |'| TOTAL_ | SECTION_TIME | |'|'| | TOTAL_SECTION_PROC_TIME | |'| |'| | FCM_RECV_WAIT_TIME | |'| | FCM_SEND_WAIT_TIME FROM (SELECT * FROM TABLE (MON_GET_PKG_CACHE_STMT (NULL) NULL, '5percent,-2)) WHERE NUM_EXEC_WITH_METRICS > 0 AND TOTAL_ACT_WAIT_TIME > 5000) WHERE TOTAL_ACT_WAIT_TIME/ (NUM_EXEC_WITH_METRICS*1.0) > 5000 "> > sqllist.txtdb2 connect reset Script description

This script is a script for crawling slow SQL and needs to be executed every 5 minutes on the database server.

Each execution of the script will grab the SQL captured by the SQL statements with an average execution time of more than 5 seconds in the past 5 minutes, and output to the file sqllist.txt to monitor the size of the file to avoid disk space-related problems caused by the excessive size of the file. Each time the script runs and grabs only the SQL statements executed in the past 5 minutes, it can be executed during daytime working hours, and the execution of the script will be turned off during unattended time. Parameter description

Specific monitoring content can be queried through the official website of ibm.

For example, TOTAL_ACT_TIME

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.