转载

awr报表中用到的几个SQL

分享几个AWR脚本中查询资源占有的SQL，更改想要的snapid就可以查相应时间段的数据库性能信息了
按执行时间查sql
select s.sql_id
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, st.sql_text sqt
, elapsed_time/1000000 /decode(execs,0,null,execs) elpe
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(clwait_delta) clwait_time
, sum(iowait_delta) iowait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(elapsed_time_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by elapsed_time desc, sql_id;


按CPU
select s.sql_id
, cpu_time/1000000 cpu_time
, elapsed_time/1000000 elapsed_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(/s)+',' '),1,50) sqt
, ' ' nl
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
, st.sql_text sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(cpu_time_delta) desc)
where rownum <= 10 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by cpu_time desc, sql_id;


按I/O排序
select s.sql_id
, iowait_time/1000000 iowait_time
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(/s)+',' '),1,50) sqt
, ' ' nl
, iowait_time/1000000/decode(execs,0,null,execs) iope
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
, substr(regexp_replace(st.sql_text,'(/s)+',' '),51,50) sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(iowait_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by iowait_time desc, reads desc, sql_id;


按gets

select s.sql_id
, gets
, reads
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(/s)+',' '),1,50) sqt
, ' ' nl
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
, substr(regexp_replace(st.sql_text,'(/s)+',' '),51,50) sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(buffer_gets_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by gets desc, cpu_time desc, sql_id;

按执行次数
select s.sql_id
, execs
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, substr(regexp_replace(st.sql_text,'(/s)+',' '),1,50) sqt
, ' ' nl
, ' ' ep
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, substr(regexp_replace(st.sql_text,'(/s)+',' '),51,50) sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(executions_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by execs desc, sql_id;
正文到此结束
所属分类： 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：695 人
  • 运行天数：4,408天
  • 最后更新：2024年11月21日21点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG