- DB2 Top SQL查询 Tips
- 1)Get the TOP 10 SQLS by the Total Execution time
- 2)Get the Top 10 SQLS by Average Execution Time
- 3)Get the Top 10 SQLS by Average CPU Time
- 4)Top 10 Ranking SQL by Number of Execution
- 5)Top 10 Ranking SQL by Number of sorts
常用TOP SQL查询语句”
DB2 Top SQL查询 Tips
包括：
总执行时间
平均执行时间
平均 CPU 时间
执行次数
排序次数
1)Get the TOP 10 SQLS by the Total Execution time
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
2)Get the Top 10 SQLS by Average Execution Time
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
3)Get the Top 10 SQLS by Average CPU Time
Top 10 Ranking SQL by Average CPU Time
SELECT substr(stmt_text,1,50) as sql_statement, total_sys_cpu_time, total_usr_cpu_time, num_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 > 0 ORDER BY avg_cpu_time desc FETCH FIRST 10 ROWS ONLY
4)Top 10 Ranking SQL by Number of Execution
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
5)Top 10 Ranking SQL by Number of sorts
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