这篇文章主要介绍了查找 Oracle 高消耗语句的方法, 需要的朋友可以参考下
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
在运行下面的脚本之前需要先用生成 AWR 报告的 SQL(程序脚本一般保存在 $ORACLE_HOME 下的 rdbms/admin 中,名称为 awrrpt.sql,需要输入生成 AWR 报告的天数范围)找到开始和结束的 snapshot 编号:begin_snap 和 end_snap。
- <span style="font-size:18px;">set line 1000
- set linesize 200
- set pagesize 2000
- set long 999999
- set echo on
- set markup html on
- select 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</span>
将脚本输出内容保存到记事本 txt 中,并将记事本的后缀名更改为. html,这样就可以输出以下的网页内容:
来源: http://www.phperz.com/article/17/0321/276553.html