DB2 Sql性能查看与优化

1.执行次数最多的TOP10SQL"
db2 "select * from sysibmadm.snapdyn_sql order by NUM_EXECUTIONS desc fetch first 10 rows only"
2、平均执行时间最长的TOP10SQL"
db2  "select * from sysibmadm.top_dynamic_sql order by average_execution_time_s desc fetch  first 10 rows only"
3、排序最多的TOP10SQL"
db2 "select * from sysibmadm.snapdyn_sql order by stmt_sorts desc fetch first 10 rows only"

使用实例用户来执行。

查找运行时间比较长的SQL

 

db2 get monitor switches //获取监视器的配置

 db2 update monitor SWITCHES using SORT on STATEMENT on TIMESTAMP on //修改监视器的设置

db2 reset monitor all //重启监视器

db2 get snapshot for dynamic sql on dbname>sql // 获取快照

grep "Total execution time" sql|sort -rn +4>time //获取运行时间

 head -n 5 time //获取运行时间比较长的前五名

然后将耗时比较久的sql 放到放入 test.sql中

执行：

db2advis -d dbname -i test.sql -q dbuser

来获取优化建议。