BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB2 Top SQL查询

2016-10-08
DB2

常用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

Comments