-
Notifications
You must be signed in to change notification settings - Fork 2
/
awr_r2_5_createxaxis-sysstat.sql
47 lines (43 loc) · 1.94 KB
/
awr_r2_5_createxaxis-sysstat.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- create the x axis - sysstat (independent value)
-- drop table r2_x_value purge;
set echo off verify off
COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;
COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;
create table r2_x_value as
select
s0.snap_id,
s0.end_interval_time tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
s0.dbid,
b.stat_name,
e.value - b.value diff
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sysstat b,
dba_hist_sysstat e
where s0.dbid = &_dbid --<---- DBID HERE!
and s1.dbid = s0.dbid
and b.dbid = s0.dbid
and e.dbid = s0.dbid
and s0.instance_number = &_instancenumber --<---- instance_number HERE!
and s1.instance_number = s0.instance_number
and b.instance_number = s0.instance_number
and e.instance_number = s0.instance_number
and s1.snap_id = s0.snap_id + 1
and b.snap_id = s0.snap_id
and e.snap_id = s0.snap_id + 1
and s1.startup_time = s0.startup_time
and to_char(s0.end_interval_time,'d') >= &&DayOfWeek1 --<---- indicate workload periods HERE!
and to_char(s0.end_interval_time,'d') <= &&DayOfWeek2
and to_char(s0.end_interval_time,'hh24mi') >= &&Hour1
and to_char(s0.end_interval_time,'hh24mi') <= &&Hour2
and s0.end_interval_time >= to_date('&&DataRange1','yyyy-mon-dd hh24:mi:ss')
and s0.end_interval_time <= to_date('&&DataRange2','yyyy-mon-dd hh24:mi:ss')
and e.stat_name = b.stat_name;