1 PROCEDURE dbms_feature_segadv_user
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 execs_since_sample NUMBER; -- # of execs since last sample
7 total_execs NUMBER; -- total # of execs
8 total_recs NUMBER; -- total # of recommendations
9 total_space_saving NUMBER; -- total potential space saving
10 tmp_buf VARCHAR2(32767); -- temp buffer
11 BEGIN
12 -- executions since last sample
13 SELECT count(*)
14 INTO execs_since_sample
15 FROM dba_advisor_executions
16 WHERE advisor_name = 'Segment Advisor' AND
17 task_name not like 'SYS_AUTO_SPCADV%' AND
18 execution_last_modified >= (SELECT nvl(max(last_sample_date),
19 sysdate-7)
20 FROM dba_feature_usage_statistics);
21
22 -- total # of executions
23 SELECT count(*)
24 INTO total_execs
25 FROM dba_advisor_executions
26 WHERE advisor_name = 'Segment Advisor' AND
27 task_name not like 'SYS_AUTO_SPCADV%';
28
29 -- total # of recommendations and total potential space saving
30 SELECT count(task.task_id), NVL(sum(msg.p3),0)
31 INTO total_recs, total_space_saving
32 FROM dba_advisor_tasks task,
33 sys.wri$_adv_findings fin,
34 sys.wri$_adv_recommendations rec,
35 sys.wri$_adv_message_groups msg
36 WHERE task.advisor_name = 'Segment Advisor' AND
37 task.task_name not like 'SYS_AUTO_SPCADV%' AND
38 task.task_id = rec.task_id AND
39 nvl(rec.annotation,0) <> 3 AND
40 fin.task_id = rec.task_id AND
41 fin.id = rec.finding_id AND
42 msg.task_id = fin.task_id AND
43 msg.id = fin.more_info_id;
44
45 -- set feature_used and aux_count
46 feature_boolean := execs_since_sample;
47 aux_count := execs_since_sample;
48
49 -- prepare feature_info
50 tmp_buf := 'Executions since last sample: ' || execs_since_sample || ', ' ||
51 'Total Executions: ' || total_execs || ', ' ||
52 'Total Recommendations: ' || total_recs || ', ' ||
53 'Projected Space saving (byte): ' || total_space_saving;
54
55 dbms_lob.createtemporary(feature_info, TRUE);
56 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
57
58 END dbms_feature_segadv_user;
[Home] [Help]