【Oracle脚本】查找Oracle高消耗语句
来源:互联网 发布:网络ip摄像头偷窥软件 编辑:程序博客网 时间:2024/05/22 22:38
在运行下面的脚本之前需要先用生成AWR报告的SQL(程序脚本一般保存在$ORACLE_HOME下的rdbms/admin中,名称为awrrpt.sql,需要输入生成AWR报告的天数范围)找到开始和结束的snapshot编号:begin_snap和end_snap。
set line 1000set linesize 200set pagesize 2000set long 999999set echo onset markup html onselect res.* from (select to_char(d.end_interval_time,'yyyy-mm-dd'), a.PARSING_SCHEMA_NAME, c.MODULE, a.sql_id, a.execs as 执行次数, ROUND(a.cpu_times / a.execs, 2) as 单次执行时间, a.cpu_times as cpu消耗时间, ROUND(a.cpu_times / b.sum_time * 100, 2) as 消耗cpu百分比, a.buffer_gets as 逻辑读, ROUND(a.buffer_gets / b.sum_buffer * 100, 2) as 逻辑读百分比, a.disk_read as 物理读, ROUND(a.disk_read / b.sum_disk * 100, 2) as 物理读百分比, c.sql_fulltext from (select PARSING_SCHEMA_NAME, sql_id, sum(EXECUTIONS_DELTA) AS execs, round(sum(CPU_TIME_DELTA) / 1000000, 2) AS cpu_times, round(sum(ELAPSED_TIME_DELTA) / 1000000, 2) AS elapsed_time, sum(BUFFER_GETS_DELTA) AS buffer_gets, sum(DISK_READS_DELTA) AS disk_read from sys.WRH$_SQLSTAT wr, gv$instance i where SNAP_ID <= &end_snap and snap_id >= &begin_snap and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER and i.instance_number = &instance_number group by PARSING_SCHEMA_NAME, wr.INSTANCE_NUMBER, sql_id) a, (SELECT round(SUM(CPU_TIME_DELTA) / 1000000, 2) sum_time, SUM(BUFFER_GETS_DELTA) sum_buffer, sum(DISK_READS_DELTA) sum_disk FROM sys.WRH$_SQLSTAT wr, gv$instance i where SNAP_ID <= &end_snap and snap_id >= &begin_snap and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER and i.instance_number = &instance_number) b, v$sqlarea c, dba_hist_snapshot d where a.execs > 0 and a.sql_id = c.sql_id and a.PARSING_SCHEMA_NAME <> 'SYS' and d.snap_id = &end_snap order by cpu消耗时间 desc) res where rownum < 41;exit
将脚本输出内容保存到记事本txt中,并将记事本的后缀名更改为.html,这样就可以输出以下的网页内容:
- 【Oracle脚本】查找Oracle高消耗语句
- Oracle高资源消耗SQL语句定位
- Oracle高资源消耗SQL语句定位
- Oracle查询当前高CPU消耗的SQL语句
- oracle数据库查找消耗cup进程信息和执行的语句
- Oracle 语句查找注释
- oracle监控脚本语句
- oracle查找当前比较消耗资源的session
- Oracle 性能优化之高消耗的SQL
- Oracle 中查询资源消耗最多的语句
- Oracle高性能SQL语句
- Oracle 语句递归查找父子关系语句
- 查找消耗CPU较大的sql语句
- 定位高消耗资源的sql语句
- Oracle中分页查找语句的实现
- Oracle中分页查找语句的实现
- ORACLE 通过spid查找sql语句
- 查找数据库中的topsql语句 (oracle)
- gerrit+git codereivew install
- 基于visual Studio2013解决C语言竞赛题之0202坐标转换
- insmod时内核做了什么
- linux shell 编程实例
- 你所不知道的main()函数
- 【Oracle脚本】查找Oracle高消耗语句
- 决定孩子命运的八大关键问题
- 物联网需要自己的专有操作系统
- 从源码的角度分析ViewGruop的事件分发
- 理解Dom的childNodes
- TCP/IP协议
- Win32 dll导出宏模板
- cocos2dx 精灵的碰撞检测和消灭(3)
- HLG 1022 Jiaozhu and SC -(二分查找)或(哈希函数处理字符串)