PDF《Troubleshooting SQL problems in》

? Copyright IBM Corporation

2015 Trademarks Troubleshooting SQL problems in your database Page

1 of

8 Troubleshooting SQL problems in your database Query your SQLs to spot potential database problems Raghavendra Chiyodu December 03,

2015 An application'

s poor performance is sometimes caused by a performance problem in a database.

The way that the Structured Query Languages (SQLs) in the database are performing will give you a good indication of how the database is performing. In this article, learn how to query your SQLs to see how they are ranking and to identify possible performance issues in DB2 for Linux, UNIX, and Windows databases. An application'

s poor performance is sometimes caused by a performance problem in a database. The way that the Structured Query Languages (SQLs) in the database are performing is a good indication of how the database is performing. In this article, learn how to query your SQLs to identify possible performance issues in DB2 for Linux, UNIX, and Windows databases. To find how SQLs are performing in your database, you can use the Top

10 Ranking SQL script to rank your SQLs in relation to: ? Total execution time ? Average execution time ? Average CPU time ? Number of executions ? Number of sorts Ranking SQLs based on their total execution time Use the following script to find out the total time, in seconds and microseconds, that was spent executing a particular statement in the SQL cache. SELECT substr(stmt_text,1,50) as sql_statement, total_exec_time, total_sys_cpu_time, total_usr_cpu_time,num_executions, num_compilations FROM sysibmadm.snapdyn_sql ORDER BY total_exec_time desc FETCH FIRST

10 ROWS ONLY After running your query, you should get a result similar to Table

1 that ranks the SQLs based on their total execution time. The higher the number on the right, the more time it'

s taking to execute the SQL, which can lead to performance issues in the database. developerWorks? ibm.com/developerWorks/ Troubleshooting SQL problems in your database Page

2 of

8 Table 1. SQLs ranked by their total execution time SQL_STATEMENT TOTAL_EXEC_TIME SELECT S0000 . SID , P0000 . DOC_TYPE FROM /BI SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT * FROM /BIC/AZDPBDSO140 WHERE SID = ? O INSERT INTO IDOCREL VALUES( SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC INSERT INTO /BIC/B0002163000 VALUES( SELECT T_00. LANGU , T_00. DTA , T_00. DTA_TYPE , SELECT T_00. LANGU , T_00. DTA , T_00. DTA_TYPE ,

6687 3451

3170 3167

2812 2639

2471 2349

2264 2260 Ranking SQLs based on their average execution time Use the following query to display the average execution time, in seconds and microseconds, of your SQLs. SELECT substr(stmt_text,1,50) as sql_statement, total_exec_time, num_executions,DECIMAL((real(total_exec_time) / real(num_executions)),18,9) as avg_exec_time FROM sysibmadm.snapdyn_sql WHERE num_executions >

0 ORDER BY

4 desc FETCH FIRST

10 ROWS ONLY The average execution time (Avg_exec_time) is equal to the total time that was spent executing a particular statement in the SQL cache ((total_exec_time)) divided by the number of times an SQL was executed (num_executions). In other words: Avg_exec_time=(total_exec_time)/num_executions. Table

2 shows what is displayed when you use this query. Table 2. SQLs ranked by their average execution time SQL_STATEMENT AVG_EXEC_TIME SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT * FROM /BIC/AZDPBDSO140 WHERE SID = ? O SELECT * FROM /BIC/ASCGD201740 WHERE SID = ? O SELECT D3 . TC001 AS CUSTOMER ,CHAR( RIGHT( DI SELECT * FROM /BIC/ASDGC201140 WHERE SID = ? O SELECT T_00. REQUEST , T_00. DATAPAKID , T_00. REC SELECT X3 . S__0MATL_TYPE AS S____113 , D1 . S 3451.000000000 2639.000000000 2471.000000000 1688.000000000 1585.000000000 1557.000000000 1435.000000000 1048.000000000 961.500000000 713.000000000 Ranking SQLs based on their average CPU time Use the following query to display the SQLs ranked by their average CPU time. SELECT substr(stmt_text,1,50) as sql_statement, total_sys_cpu_time, total_usr_cpu_time, um_executions,DECIMAL(((real(total_sys_cpu_time) + real(total_usr_cpu_time)) / real(num_executions)),18,9) as avg_cpu_time FROM sysibmadm.snapdyn_sql WHERE num_executions >