1 PROCEDURE dbms_feature_wcr_replay
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 prev_sample_count NUMBER;
7 prev_sample_date DATE;
8 prev_sample_date_dbtz DATE;
9 date_format CONSTANT VARCHAR2(64) := 'YYYY:MM:DD HH24:MI:SS';
10
11 replays_since NUMBER; -- # of replays since last sample
12 BEGIN
13
14 /*
15 * We compute the total number of replays done on the
16 * current database by finding the number of replays done
17 * since the last sample and adding it to the current aux_count.
18 */
19
20 -- Find prev_sample_count and prev_sample_date first
21 select nvl(max(aux_count), 0), nvl(max(last_sample_date), sysdate-7)
22 into prev_sample_count, prev_sample_date
23 from dba_feature_usage_statistics
24 where name = 'Database Replay: Workload Replay';
25
26 -- convert date to db timezone
27 select to_date(to_char(from_tz(cast(prev_sample_date as timestamp),
28 sessiontimezone) at time zone dbtimezone, date_format),
29 date_format) into prev_sample_date_dbtz from dual;
30
31 -- Find # of workload replays since last sample in current DB
32 select count(*)
33 into replays_since
34 from dba_workload_replays
35 where (prev_sample_date_dbtz is null OR start_time > prev_sample_date_dbtz)
36 and dbid = (select dbid from v$database);
37
38 -- Mark boolean to be replays_since
39 feature_boolean := replays_since;
40 -- Add current aux_count with replays_since for new value
41 aux_count := prev_sample_count + replays_since;
42 -- Feature_info not used
43 feature_info := NULL;
44
45 END dbms_feature_wcr_replay;
[Home] [Help]