Oracle Awr Trends
define hours=12
column f_hours new_value v_hours
select &hours f_hours from dual;
column f_secs new_value v_secs
column f_samples new_value samples
select 3600 f_secs from dual;
select &v_secs f_samples from dual;
--select &seconds f_secs from dual;
column f_bars new_value v_bars
select 5 f_bars from dual;
column aas format 999.99
column f_graph new_value v_graph
select 30 f_graph from dual;
column graph format a30
column total format 99999
column npts format 99999
col waits for 999999999
col cpu for 999999999
set pagesize 400 linesize 500
/*
dba_hist_active_sess_history
*/
select
to_char(to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS'),'DD-MON HH24:MI:SS') tm,
samples npts,
total/&samples aas,
substr(
substr(substr(rpad('+',round((cpu*&v_bars)/&samples),'+') ||
rpad('-',round((waits*&v_bars)/&samples),'-') ||
rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
p.value ||
substr(rpad('+',round((cpu*&v_bars)/&samples),'+') ||
rpad('-',round((waits*&v_bars)/&samples),'-') ||
rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,30)
,0,&v_graph)
graph,
-- total,
cpu,
waits
from (
select
to_char(sample_time,'YYMMDD') tday
, trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
, sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1))) total
, (max(sample_id) - min(sample_id) + 1 ) samples
, sum(decode(session_state,'ON CPU' ,1,0)) cpu
, sum(decode(session_type,'BACKGROUND',0,decode(session_state,'WAITING',1,0))) waits
/* for waits I want to subtract out the BACKGROUND
but for CPU I want to count everyon */
from
gv$active_session_history
where sample_time > sysdate - &v_hours/24
group by trunc(to_char(sample_time,'SSSSS')/&v_secs),
to_char(sample_time,'YYMMDD')
union all
select
to_char(sample_time,'YYMMDD') tday
, trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
, sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
, (max(sample_id) - min(sample_id) + 1 ) samples
, sum(decode(session_state,'ON CPU' ,10,0)) cpu
, sum(decode(session_type,'BACKGROUND',0,decode(session_state,'WAITING',10,0))) waits
/* for waits I want to subtract out the BACKGROUND
but for CPU I want to count everyon */
from
dba_hist_active_sess_history
where sample_time > sysdate - &v_hours/24
and sample_time < (select min(sample_time) from v$active_session_history)
group by trunc(to_char(sample_time,'SSSSS')/&v_secs),
to_char(sample_time,'YYMMDD')
) ash,
v$parameter p
where p.name='cpu_count'
order by to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS')
;
TM NPTS AAS GRAPH CPU WAITS
------------------------- ------ ------- ------------------------------ ---------- ----------
10-MAY 23:00:00 531 2.84 ++++---------- 2980 7240
11-MAY 00:00:00 3581 11.46 +++++++++++++++++++++++++----- 18320 22940
11-MAY 01:00:00 3581 3.77 ++++++++----------- 5540 8030
11-MAY 02:00:00 3581 2.87 +++++++++------ 6140 4180
11-MAY 03:00:00 3591 3.51 +++++++++--------- 6290 6360
11-MAY 04:00:00 3581 20.04 ++++++++++++++++++++++++++++++ 35660 36480
11-MAY 05:00:00 3581 303.96 ++++++++++++++++++++++++++++++ 21310 1072960
11-MAY 06:00:00 3571 479.73 ++++++++++++++++++++++++++++-- 20480 1706550
11-MAY 07:00:00 671 66.68 +++++------------------------- 3290 236750
11-MAY 07:00:00 2913 284.21 ++++++++++++++++-------------- 11813 1011355
11-MAY 08:00:00 3593 4.17 +++++++++++++-------- 9032 5981
11-MAY 09:00:00 3594 4.24 +++++++++++++-------- 9178 6077
11-MAY 10:00:00 3593 3.59 ++++++++++++------ 8531 4396
11-MAY 11:00:00 3056 3.44 ++++++++++------- 7152 5242
14 rows selected.
awr_event_trends.sql
from
https://github.com/abdulirfan3/Oracle_SQL_Scripts/blob/master/awr_event_trends.sql
-- ttitle off
clear breaks computes
break on wait_class on report
prompt
prompt Some useful database wait-events upon which to search:
col wait_class format a20 heading "Wait Class"
col name format a60 heading "Name"
select chr(9)||wait_class, name name
from v$event_name
order by wait_class, name;
define V_NBR_DAYS=1
define V_EVENTNAME='direct path write'
set echo off feedback off timing off pagesize 500 linesize 160
set trimout on trimspool on verify off
col sort0 noprint
col day format a6 heading "Day"
col hr format a6 heading "Hour"
col event_name format a30 heading "Event Name"
col total_waits format 999,990 heading "Total|Waits (m)"
col time_waited format 999,990.00 heading "Secs|Waited"
col tot_wts format 990.00 heading "% Total|Waits"
col tot_pct format 990.00 heading "% Secs|Waited"
col avg_wait format 990.00 heading "Avg|hSecs|Per|Wait"
col avg_pct format 990.00 heading "% Avg|hSecs|Per|Wait"
col wt_graph format a18 heading "Graphical view|of % total|waits overall"
col tot_graph format a18 heading "Graphical view|of % total|secs waited overall"
col avg_graph format a18 heading "Graphical view|of % avg hSecs|per wait overall"
set linesize 500 pagesize 400
col EVENT_NAME for a27
col WT_GRAPH for a20
col TOT_GRAPH for a20
col AVG_GRAPH for a20
col spoolname new_value V_SPOOLNAME noprint
col instance_name new_value V_INST_NAME noprint
col instance_number new_value V_INST_NBR noprint
col dbid new_value V_DBID noprint
select replace(replace(replace(lower('&&V_EVENTNAME'),' ','_'),'(',''),')','') spoolname,
i.instance_name,
i.instance_number,
d.dbid
from v$instance i,
v$database d;
--spool awr_evtrends_&&V_INST_NAME._&&V_SPOOLNAME
clear breaks computes
ttitle center 'Trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 999,990.00 heading "Waits (m)"
prompt
select event_name,
total_waits/1000000 total_waits,
(ratio_to_report(total_waits) over ()*100) tot_wts,
rpad('*', round((ratio_to_report(total_waits) over ()*100)/6, 0), '*') wt_graph,
time_waited,
(ratio_to_report(time_waited) over ()*100) tot_pct,
rpad('*', round((ratio_to_report(time_waited) over ()*100)/6, 0), '*') tot_graph,
avg_wait*100 avg_wait,
(ratio_to_report(avg_wait) over ()*100) avg_pct,
rpad('*', round((ratio_to_report(avg_wait) over ()*100)/6, 0), '*') avg_graph
from (select event_name,
sum(total_waits) total_waits,
sum(time_waited)/1000000 time_waited,
decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
from (select s.event_name,
s.snap_id,
nvl(decode(greatest(s.time_waited_micro,
lag(s.time_waited_micro,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.time_waited_micro,
s.time_waited_micro - lag(s.time_waited_micro)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.time_waited_micro), 0) time_waited,
nvl(decode(greatest(s.total_waits,
lag(s.total_waits,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.total_waits,
s.total_waits - lag(s.total_waits)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.total_waits), 0) total_waits
from dba_hist_system_event s,
dba_hist_snapshot ss
where s.event_name like '%'||'&&V_EVENTNAME'||'%'
and s.instance_number = &&V_INST_NBR
-- and s.dbid = &&V_DBID
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
group by event_name)
order by time_waited desc;
clear breaks computes
break on report
compute avg of total_waits on report
compute avg of time_waited on report
compute avg of avg_wait on report
ttitle center 'Daily trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 999,990.00 heading "Waits (m)"
prompt
select sort_day || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
day,
event_name,
total_waits/1000000 total_waits,
(ratio_to_report(total_waits) over ()*100) tot_wts,
rpad('*', round((ratio_to_report(total_waits) over ()*100)/6, 0), '*') wt_graph,
time_waited,
(ratio_to_report(time_waited) over ()*100) tot_pct,
rpad('*', round((ratio_to_report(time_waited) over ()*100)/6, 0), '*') tot_graph,
avg_wait*100 avg_wait,
(ratio_to_report(avg_wait) over ()*100) avg_pct,
rpad('*', round((ratio_to_report(avg_wait) over ()*100)/6, 0), '*') avg_graph
from (select sort_day,
day,
event_name,
sum(total_waits) total_waits,
sum(time_waited)/1000000 time_waited,
decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
from (select to_char(ss.begin_interval_time, 'YYYYMMDD') sort_day,
to_char(ss.begin_interval_time, 'DD-MON') day,
s.event_name,
s.snap_id,
nvl(decode(greatest(s.time_waited_micro,
lag(s.time_waited_micro,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.time_waited_micro,
s.time_waited_micro - lag(s.time_waited_micro)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.time_waited_micro), 0) time_waited,
nvl(decode(greatest(s.total_waits,
lag(s.total_waits,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.total_waits,
s.total_waits - lag(s.total_waits)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.total_waits), 0) total_waits
from dba_hist_system_event s,
dba_hist_snapshot ss
where s.event_name like '%'||'&&V_EVENTNAME'||'%'
and s.instance_number = &&V_INST_NBR
-- and s.dbid = &&V_DBID
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
group by sort_day,
day,
event_name)
order by sort0;
clear breaks computes
ttitle center 'Hourly trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 9,990.00 heading "Waits (m)"
break on day skip 1 on hr on report
compute avg of total_waits on report
compute avg of time_waited on report
compute avg of avg_wait on report
prompt
select sort_hr || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
day,
hr,
event_name,
total_waits/1000000 total_waits,
(ratio_to_report(total_waits) over (partition by day)*100) tot_wts,
rpad('*', round((ratio_to_report(total_waits) over (partition by day)*100)/4, 0), '*') wt_graph,
time_waited,
(ratio_to_report(time_waited) over (partition by day)*100) tot_pct,
rpad('*', round((ratio_to_report(time_waited) over (partition by day)*100)/4, 0), '*') tot_graph,
avg_wait*100 avg_wait,
(ratio_to_report(avg_wait) over (partition by day)*100) avg_pct,
rpad('*', round((ratio_to_report(avg_wait) over (partition by day)*100)/4, 0), '*') avg_graph
from (select sort_hr,
day,
hr,
event_name,
sum(total_waits) total_waits,
sum(time_waited)/1000000 time_waited,
decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
from (select to_char(ss.begin_interval_time, 'YYYYMMDDHH24') sort_hr,
to_char(ss.begin_interval_time, 'DD-MON') day,
to_char(ss.begin_interval_time, 'HH24')||':00' hr,
s.event_name,
s.snap_id,
nvl(decode(greatest(s.time_waited_micro,
lag(s.time_waited_micro,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.time_waited_micro,
s.time_waited_micro - lag(s.time_waited_micro)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.time_waited_micro), 0) time_waited,
nvl(decode(greatest(s.total_waits,
lag(s.total_waits,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.total_waits,
s.total_waits - lag(s.total_waits)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.total_waits), 0) total_waits
from dba_hist_system_event s,
dba_hist_snapshot ss
where s.event_name like '%'||'&&V_EVENTNAME'||'%'
and s.instance_number = &&V_INST_NBR
-- and s.dbid = &&V_DBID
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
group by sort_hr,
day,
hr,
event_name)
order by sort0;
ttitle off
col avg_snap_frequency new_value V_AVG_SNAP_FREQUENCY noprint
select decode(greatest(count(*), &&V_NBR_DAYS * 4), &&V_NBR_DAYS * 4, 'HOURLY', 'MULTIPLE TIMES/HOUR') avg_snap_frequency
from (select count(*) cnt
from dba_hist_snapshot
where begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS
and dbid = &&V_DBID
and instance_number = &&V_INST_NBR
group by trunc(begin_interval_time, 'HH24')
having count(*) > 1);
clear breaks computes
ttitle center 'Snapshot-by-snapshot trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 9,990.00 heading "Waits (m)"
REM break on day skip 1 on hr on report
REM compute avg of total_waits on report
REM compute avg of time_waited on report
REM compute avg of avg_wait on report
select sort_snap || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
day,
tm,
event_name,
total_waits/1000000 total_waits,
(ratio_to_report(total_waits) over (partition by day)*100) tot_wts,
rpad('*', round((ratio_to_report(total_waits) over (partition by day)*100)/4, 0), '*') wt_graph,
time_waited,
(ratio_to_report(time_waited) over (partition by day)*100) tot_pct,
rpad('*', round((ratio_to_report(time_waited) over (partition by day)*100)/4, 0), '*') tot_graph,
avg_wait*100 avg_wait,
(ratio_to_report(avg_wait) over (partition by day)*100) avg_pct,
rpad('*', round((ratio_to_report(avg_wait) over (partition by day)*100)/4, 0), '*') avg_graph
from (select sort_snap,
day,
tm,
event_name,
total_waits total_waits,
time_waited/1000000 time_waited,
decode(total_waits,0,0,((time_waited/total_waits)/1000000)) avg_wait
from (select to_char(ss.begin_interval_time, 'YYYYMMDDHH24MI') sort_snap,
to_char(ss.begin_interval_time, 'DD-MON') day,
to_char(ss.begin_interval_time, 'HH24:MI') tm,
s.event_name,
s.snap_id,
nvl(decode(greatest(s.time_waited_micro,
lag(s.time_waited_micro,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.time_waited_micro,
s.time_waited_micro - lag(s.time_waited_micro)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.time_waited_micro), 0) time_waited,
nvl(decode(greatest(s.total_waits,
lag(s.total_waits,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.total_waits,
s.total_waits - lag(s.total_waits)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.total_waits), 0) total_waits
from dba_hist_system_event s,
dba_hist_snapshot ss
where '&&V_AVG_SNAP_FREQUENCY' <> 'HOURLY'
and s.event_name like '%'||'&&V_EVENTNAME'||'%'
and s.instance_number = &&V_INST_NBR
and s.dbid = &&V_DBID
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS))
order by sort0;
clear breaks computes
set lines 120
set verify off
undefine SQL_ID
accept SQL_ID char prompt 'Enter SQL ID: '
column "Cost" format 999,999,999
column "Module" format a15
column "Schema" format a15
column "Buffer Gets" format 999,999,999
column "Elapsed Time" format 999,999,999
column "Snapshot Time" format a30
SELECT optimizer_cost "Cost",
module "Module",
parsing_schema_name "Schema",
buffer_gets_total "Buffer Gets",
elapsed_time_delta "Elapsed Time",
end_interval_time "Snapshot Time"
FROM dba_hist_sqlstat st, dba_hist_snapshot ss
WHERE st.SQL_ID = '&SQL_ID' AND ss.snap_id = st.snap_id
ORDER BY "Snapshot Time" DESC
/
