1 PROCEDURE dbms_feature_autosta
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 asqlt_task_name VARCHAR2(30) := 'SYS_AUTO_SQL_TUNING_TASK';
7
8 execs_since_sample NUMBER; -- # of execs since last sample
9 total_execs NUMBER; -- number of task executions
10 w_auto_impl NUMBER; -- execs with AUTO implement on
11 profs_rec NUMBER; -- total profiles in task
12 savedsecs NUMBER; -- db time saved (s)
13 tmp_buf VARCHAR2(32767); -- temp buffer
14 BEGIN
15
16 /*
17 * We compute the following stats for db feature usage:
18 * Number of executions since last sample (execs_since_sample)
19 * Total number of executions in the task (total_execs)
20 * Total number of executions with auto-implement ON (w_auto_impl)
21 * Total number of SQL profiles recommended in the task (profs_rec)
22 * Projected DB Time Saved through Auto Implementation (savedsecs)
23 *
24 * Note that these stats are only computed through looking at the task,
25 * which, by default, stores results from the last month of history only.
26 */
27
28 -- execs since last sample
29 SELECT count(*)
30 INTO execs_since_sample
31 FROM dba_advisor_executions
32 WHERE task_name = asqlt_task_name AND
33 execution_last_modified >= (SELECT nvl(max(last_sample_date),
34 sysdate-7)
35 FROM dba_feature_usage_statistics);
36
37 -- total # of executions
38 SELECT count(*)
39 INTO total_execs
40 FROM dba_advisor_executions
41 WHERE task_name = asqlt_task_name;
42
43 -- #execs with auto implement ON
44 SELECT count(*)
45 INTO w_auto_impl
46 FROM dba_advisor_exec_parameters
47 WHERE task_name = asqlt_task_name AND
48 parameter_name = 'ACCEPT_SQL_PROFILES' AND
49 parameter_value = 'TRUE';
50
51 -- total profiles recommended so far
52 SELECT count(*)
53 INTO profs_rec
54 FROM dba_advisor_recommendations r
55 WHERE r.task_name = asqlt_task_name AND
56 r.type = 'SQL PROFILE';
57
58 -- db time saved by AUTO impl profiles
59 SELECT round(nvl(sum(before_usec - after_usec)/1000000, 0))
60 INTO savedsecs
61 FROM (SELECT nvl(o.attr8, 0) before_usec,
62 nvl(o.attr8, 0) * (1 - r.benefit/10000) after_usec
63 FROM dba_sql_profiles sp,
64 dba_advisor_objects o,
65 dba_advisor_findings f,
66 dba_advisor_recommendations r
67 WHERE o.task_name = asqlt_task_name AND
68 o.type = 'SQL' AND
69 sp.task_id = o.task_id AND
70 sp.task_obj_id = o.object_id AND
71 sp.task_exec_name = o.execution_name AND
72 o.task_id = f.task_id AND
73 o.execution_name = f.execution_name AND
74 o.object_id = f.object_id AND
75 f.finding_id = sp.task_fnd_id AND
76 r.task_id = f.task_id AND
77 r.execution_name = f.execution_name AND
78 r.finding_id = f.finding_id AND
79 r.rec_id = sp.task_rec_id AND
80 sp.type = 'AUTO');
81
82 -- the used boolean and aux count we set to the number of execs since last
83 -- sample
84 feature_boolean := execs_since_sample;
85 aux_count := execs_since_sample;
86
87 -- compose the CLOB
88 tmp_buf := 'Execution count so far: ' || total_execs || ', ' ||
89 'Executions with auto-implement: ' || w_auto_impl || ', ' ||
90 'SQL profiles recommended so far: ' || profs_rec || ', ' ||
91 'Projected DB Time Saved Automatically (s): ' || savedsecs;
92
93 dbms_lob.createtemporary(feature_info, TRUE);
94 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
95
96 END dbms_feature_autosta;
[Home] [Help]