1 procedure DBMS_FEATURE_AWR
2 ( feature_boolean_OUT OUT NUMBER,
3 aux_count_OUT OUT NUMBER,
4 feature_info_OUT OUT CLOB)
5 AS
6 DBFUS_LAST_SAMPLE_DATE DATE;
7
8 l_DBtime7day_secs number;
9 l_DBcpu7day_secs number;
10
11 -- cursor fetches last 7 days of AWR snapshot DB time and DB cpu
12 cursor TimeModel7day_cur
13 IS
14 WITH snap_ranges AS
15 (select /*+ FULL(ST) */
16 SN.dbid
17 ,SN.instance_number
18 ,SN.startup_time
19 ,ST.stat_id
20 ,ST.stat_name
21 ,MIN(SN.snap_id) as MIN_snap
22 ,MAX(SN.snap_id) as MAX_snap
23 ,MIN(CAST(begin_interval_time AS DATE)) as MIN_date
24 ,MAX(CAST(end_interval_time AS DATE)) as MAX_date
25 from
26 dba_hist_snapshot SN
27 ,wrh$_stat_name ST
28 where
29 SN.begin_interval_time > TRUNC(SYSDATE) - 7
30 and SN.end_interval_time < TRUNC(SYSDATE)
31 and SN.dbid = ST.dbid
32 and ST.stat_name IN ('DB time', 'DB CPU')
33 group by
34 SN.dbid,SN.instance_number,SN.startup_time,ST.stat_id,ST.stat_name
35 )
36 ,delta_data AS
37 (select
38 SR.dbid
39 ,SR.instance_number
40 ,SR.stat_name
41 ,CASE WHEN SR.startup_time BETWEEN SR.MIN_date AND SR.MAX_date
42 THEN TM1.value + (TM2.value - TM1.value)
43 ELSE (TM2.value - TM1.value)
44 END
45 as delta_time
46 from
47 WRH$_SYS_TIME_MODEL TM1
48 ,WRH$_SYS_TIME_MODEL TM2
49 ,snap_ranges SR
50 where
51 TM1.dbid = SR.dbid
52 and TM1.instance_number = SR.instance_number
53 and TM1.snap_id = SR.MIN_snap
54 and TM1.stat_id = SR.stat_id
55 and TM2.dbid = SR.dbid
56 and TM2.instance_number = SR.instance_number
57 and TM2.snap_id = SR.MAX_snap
58 and TM2.stat_id = SR.stat_id
59 )
60 select
61 stat_name
62 ,ROUND(SUM(delta_time/1000000),2) as secs
63 from
64 delta_data
65 group by
66 stat_name;
67
68 begin
69 --> initialize OUT parameters
70 feature_boolean_OUT := 0;
71 aux_count_OUT := null;
72 feature_info_OUT := null;
73
74 --> initialize last sample date
75 select nvl(max(last_sample_date), sysdate-7)
76 into DBFUS_LAST_SAMPLE_DATE
77 from wri$_dbu_usage_sample;
78
79 if DBFUS_LAST_SAMPLE_DATE IS NOT NULL
80 then
81 --> get snapshot count since last sample date
82 select count(*)
83 into feature_boolean_OUT
84 from wrm$_snapshot
85 where dbid = (select dbid from v$database)
86 and status = 0
87 and bitand(snap_flag, 1) = 1
88 and end_interval_time > DBFUS_LAST_SAMPLE_DATE;
89 end if;
90
91 --> fetch 7 day DB time and DB CPU from AWR
92 for TimeModel7day_rec in TimeModel7day_cur
93 loop
94 case TimeModel7day_rec.stat_name
95 when 'DB time' then l_DBtime7day_secs := TimeModel7day_rec.secs;
96 when 'DB CPU' then l_DBcpu7day_secs := TimeModel7day_rec.secs;
97 end case;
98 end loop;
99
100 --> assemble feature info CLOB
101 feature_info_OUT := 'DBtime:'||TO_CHAR(l_DBtime7day_secs)||
102 ',DBcpu:'||TO_CHAR(l_DBcpu7day_secs);
103
104
105 end;
[Home] [Help]