Before running the following script, you need to use SQL that generates the AWR report (the program script is generally saved in rdbms/admin under $ORACLE_HOME, the name is awrrpt.sql, and you need to enter the number of days to generate the AWR report) to find the start and end snapshot. Numbers: begin_snap and end_snap.
The code copy is as follows:
<span style="font-size:18px;">set line 1000
set linesize 200
set pagesize 2000
set long 9999999
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 number of executions,
ROUND(a.cpu_times / a.execs, 2) as single execution time,
a.cpu_times as cpu consumes time,
ROUND(a.cpu_times / b.sum_time * 100, 2) as percentage of cpu consumption,
a.buffer_gets as logical reading,
ROUND(a.buffer_gets / b.sum_buffer * 100, 2) as logical read percentage,
a.disk_read as physical reading,
ROUND(a.disk_read / b.sum_disk * 100, 2) as Physical read percentage,
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 consume time desc) res
where rownum < 41;
exit</span>
Save the script output content to notepad txt and change the suffix name of notepad to .html so that the following web page content can be output: