![DB2 Sql性能查看与优化 DB2 Sql性能查看与优化](https://image.shishitao.com:8440/aHR0cHM6Ly9ia3FzaW1nLmlrYWZhbi5jb20vdXBsb2FkL2NoYXRncHQtcy5wbmc%2FIQ%3D%3D.png?!?w=700&webp=1)
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
来获取优化建议。