Kapil Goyal在他的IOUG presentation中共享了几个很有用的AWR性能诊断脚本，在这里共享一下：
Select extract(day from snap_interval) * 24 * 60 +
extract(hour from snap_interval) * 60 +
extract(minute from snap_interval) "Snapshot Interval",
extract(day from retention) * 24 * 60 +
extract(hour from retention) * 60 + extract(minute from retention) "Retention Interval(Minutes) ",
extract(day from retention) "Retention(in Days) "
from dba_hist_wr_control;
System Event Trending
event_response.sql
alter session set nls_date_format='dd-mon-yy';
set lines 150 pages 100 echo off feedback off
col date_time heading 'Date time|mm/dd/yy_hh_mi_hh_mi' for a30
col event_name for a26
col waits for 99,999,999,999 heading 'Waits'
col time for 99,999 heading 'Total Wait|Time(sec)'
col avg_wait_ms for 99,999 heading 'Avg Wait|(ms)'
prompt "Enter the date in DD-Mon-YY Format:"
WITH system_event AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
se.event_name event_name,
se.total_waits e_total_waits,
lag(se.total_waits, 1) over(order by se.snap_id) b_total_waits,
se.total_timeouts e_total_timeouts,
lag(se.total_timeouts, 1) over(order by se.snap_id) b_total_timeouts,
se.time_waited_micro e_time_waited_micro,
lag(se.time_waited_micro, 1) over(order by se.snap_id) b_time_waited_micro
from dba_hist_system_event se, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and se.snap_id = sn.snap_id
and se.dbid = sn.dbid
and se.instance_number = sn.instance_number
and se.dbid = (select dbid from v$database)
and se.instance_number = (select instance_number from v$instance)
and se.event_name = '&event_name') select to_char
(se1.BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char
(se1.END_INTERVAL_TIME, '_hh24_mi') date_time,
se1.event_name,
se1.e_total_waits - nvl(se1.b_total_waits,
0) waits,
(se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,
0)) / 1000000 time,
((se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,
0)) / 1000) / (se1.e_total_waits - nvl(se1.b_total_waits,
0)) avg_wait_ms from system_event se1 where(se1.e_total_waits - nvl(se1.b_total_waits,
0)) > 0 and nvl(se1.b_total_waits,
0) > 0
/
Load Profile Trending
alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = '&stat_name')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/
Time Model Statistics Trend
alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000
col date_time heading 'Date time' for a40
col stat_name heading 'Statistics Name' for a25
col time heading 'Time (s)' for 99,999,999,999
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'DBtime', 'DB CPU', 'sql execute elapsed time', 'PL/SQL execution elapsed time','parse time elapsed', 'background elapsed time'"
WITH systimemodel AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
st.stat_name stat_name,
st.value e_value,
lag(st.value, 1) over(order by st.snap_id) b_value
from DBA_HIST_SYS_TIME_MODEL st, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and st.snap_id = sn.snap_id
and st.dbid = sn.dbid
and st.instance_number = sn.instance_number
and st.dbid = (select dbid from v$database)
and st.instance_number = (select instance_number from v$instance)
and st.stat_name = '&stat_name')
select to_char (BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char (END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name, round((e_value - nvl(b_value, 0)) / 1000000) time
from systimemodel
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
/
Want to Know if Execution Plan Changed Recently?
set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'
col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/
xp_awr.sql
select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));