在運(yùn)行下面的腳本之前需要先用生成AWR報(bào)告的SQL（程序腳本一般保存在$ORACLE_HOME下的rdbms/admin中，名稱為awrrpt.sql，需要輸入生成AWR報(bào)告的天數(shù)范圍）找到開始和結(jié)束的snapshot編號(hào)：begin_snap和end_snap。
代碼如下:
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 執(zhí)行次數(shù),
ROUND(a.cpu_times / a.execs, 2) as 單次執(zhí)行時(shí)間,
a.cpu_times as cpu消耗時(shí)間,
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消耗時(shí)間 desc) res
where rownum < 41;
exit
將腳本輸出內(nèi)容保存到記事本txt中，并將記事本的后綴名更改為.html，這樣就可以輸出以下的網(wǎng)頁內(nèi)容：
聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
