PDF《Troubleshooting SQL problems in》

0 ORDER BY avg_cpu_time desc FETCH FIRST

10 ROWS ONLY ibm.com/developerWorks/ developerWorks? Troubleshooting SQL problems in your database Page

3 of

8 Here, the average CPU time (avg_cpu_time >

) is equal to the total CPU time a system uses for an SQL statement (total_sys_cpu_time) plus the total CPU time a user uses for an SQL statement (total_usr_cpu_time), divided by the total number of executions (num_executions). In other words: Avg_cpu_time = (total_sys_cpu_time + total_usr_cpu_time) / num_executions The result from your query should be similar to Table 3. Table 3. Table 3. SQLs ranked by their average CPU time SQL_STATEMENT AVG_CPU_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 D3 . TC001 AS CUSTOMER ,CHAR( RIGHT( DI SELECT X3 . S__0MATL_TYPE AS S____113 , D1 . S SELECT S0000 . SID , P0000 . DOC_TYPE FROM /BI SELECT DT . SID_0CALMONTH AS S____098 , D1 . S SELECT * FROM /BIC/AZDPBDSO140 WHERE SID = ? O SELECT * FROM /BIC/ASCGD201740 WHERE SID = ? O 1026.000000000 831.000000000 801.000000000 609.000000000 608.000000000 545.000000000 315.642857142 298.500000000 275.000000000 208.000000000 Ranking SQLs based on their number of executions Use the following query to display the SQLs ranked by the most number of executions. SELECT substr(stmt_text,1,50) as sql_statement, total_exec_time, num_executions,stmt_sorts,num_compilations,DECIMAL((real(total_exec_time) / real(num_executions)),18,9) as avg_exec_time FROM sysibmadm.snapdyn_sql WHERE num_executions >

0 ORDER BY

3 desc FETCH FIRST

10 ROWS ONLY Here, the num_executions shows you how many times the statement has been executed. You can use this element to identify the most frequently executed SQL statements in your system. The result from your query should be similar to Table 4. Table 4. SQLs ranked based on the number of executions SQL_STATEMENT NUM_EXECUTIONS INSERT INTO TESTDATRNRPART0 VALUES(OPTL INSERT INTO /BIC/B0002163000 VALUES( INSERT INTO /BIC/B0000362000 VALUES( DELETE FROM TESTDATRNRPART0 WHERE RNR IN ( ?, INSERT INTO /BIC/B0000209000 VALUES( INSERT INTO /BIC/B0002162000 VALUES( INSERT INTO /BIC/B0001314000 VALUES( INSERT INTO TESTDATRNRPART1 VALUES(OPTL SELECT * FROM /BI0/RREQUID WHERE SID = ? FETC SELECT * FROM RSREQDONE WHERE RNR = ? FETCH F

37165358 15376967

11695622 7423945

4940722 4934877

3983189 3904998

3427802 3149237 Ranking SQLs based on their number of sorts Use the following query to display the SQLs ranked by the ones with the most number of sorts. developerWorks? ibm.com/developerWorks/ Troubleshooting SQL problems in your database Page

4 of

8 SELECT substr(stmt_text,1,50) as sql_statement, total_exec_time, num_executions,stmt_sorts,num_compilations,DECIMAL((real(total_exec_time) / real(num_executions)),18,9) as avg_exec_time FROM sysibmadm.snapdyn_sql WHERE num_executions >

0 ORDER BY stmt_sorts desc FETCH FIRST

10 ROWS ONLY Here, the element stmt_sorts represents the total number of times that a set of data was sorted in order to process the stmt_operation. You can use this element to identify the need for an index, since indexes can reduce the need to sort data, which improves performance. Table

5 shows the result of a query for stmt_sorts. Table 5. SQLs ranked based on the number of sorts SQL_STATEMENT STMT_SORTS DELETE FROM TESTDATRNRPART0 WHERE RNR IN ( ?, DELETE FROM TESTDATRNRPART1 WHERE RNR IN ( ?, SELECT * FROM TBTCS WHERE ( SDLSTRTDT = ? AND SELECT JOBNAME , JOBCOUNT , JOBGROUP , INTREPO SELECT * FROM DD04L T_00, ( SELECT * FROM ( VALU SELECT * FROM DD01L T_00, ( SELECT * FROM ( VALU SELECT * FROM /BI0/SOPERATION T_00, ( SELECT * F SELECT * FROM RSDS WHERE DATASOURCE = ? AND L SELECT DISTINCT ENHNAME FROM ENHOBJ WHERE OB DELETE FROM TESTDATRNRPART2 WHERE RNR IN ( ?, SELECT AS4LOCAL FROM DD02L WHERE TABNAME = ?