1 PROCEDURE dbms_feature_spd
2 (feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 NEW_LINE CONSTANT VARCHAR2(8) := '
7 ';
8 num_dirs NUMBER; -- number of directives
9 plan_dir_mgmt_control NUMBER;
10 dsdir_usage_control NUMBER;
11 spd_retention_weeks NUMBER;
12 num_dir_obj NUMBER;
13 num_dir_subobj NUMBER;
14 tmp_buf VARCHAR2(32767);
15 CURSOR spd_reason_cursor IS
16 select reason c1, count(*) c2 from dba_sql_plan_directives group by reason;
17 CURSOR spd_state_cursor IS
18 select state c1, count(*) c2 from dba_sql_plan_directives group by state;
19 CURSOR spd_type_cursor IS
20 select type c1, count(*) c2 from dba_sql_plan_directives group by type;
21 BEGIN
22 -- get total number of rows in dba_sql_plan_directives
23 SELECT count(*)
24 INTO num_dirs
25 FROM dba_sql_plan_directives;
26
27 dbms_lob.createtemporary(feature_info, TRUE);
28
29 -- # of directives with each type
30 for spd_type_iter in spd_type_cursor
31 loop
32 tmp_buf := 'Number of directives with type, '||spd_type_iter.c1||': '||
33 spd_type_iter.c2 || NEW_LINE;
34 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
35 end loop;
36
37 -- # of directives with each reason
38 for spd_reason_iter in spd_reason_cursor
39 loop
40 tmp_buf := 'Number of Directives with reason, '||spd_reason_iter.c1||': '||
41 spd_reason_iter.c2||NEW_LINE;
42 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
43 end loop;
44
45 -- # of directives with each state
46 for spd_state_iter in spd_state_cursor
47 loop
48 tmp_buf := 'Number of Directives with state, '||spd_state_iter.c1||': '||
49 spd_state_iter.c2 || NEW_LINE;
50 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
51 end loop;
52
53 -- # of directive objects and subobjects
54 select count(object_name), count(subobject_name)
55 into num_dir_obj, num_dir_subobj
56 from dba_sql_plan_dir_objects;
57
58 tmp_buf := 'Number of Directive objects: '|| num_dir_obj ||
59 ', subobjects: ' || num_dir_subobj || NEW_LINE;
60 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
61
62 -- # of retention weeks
63 select dbms_spd.get_prefs('SPD_RETENTION_WEEKS')
64 into spd_retention_weeks
65 from dual;
66
67 tmp_buf := 'spd_retention_weeks: '||spd_retention_weeks|| NEW_LINE;
68 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
69
70 -- get value of _sql_plan_directive_mgmt_control
71 select ksppstvl value
72 into plan_dir_mgmt_control
73 from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
74 ksppinm = '_sql_plan_directive_mgmt_control';
75
76 tmp_buf := '_sql_plan_directive_mgmt_control: ' || plan_dir_mgmt_control
77 || NEW_LINE;
78 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
79
80 -- get value of _optimizer_dsdir_usage_control
81 select ksppstvl value
82 into dsdir_usage_control
83 from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
84 ksppinm = '_optimizer_dsdir_usage_control';
85
86 tmp_buf := '_optimizer_dsdir_usage_control: ' || dsdir_usage_control ||
87 NEW_LINE;
88 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
89
90 -- populate the outputs if some directive mgmt operation is enabled or
91 -- if there is atleast one directive.
92 if (plan_dir_mgmt_control > 0 or num_dirs > 0) then
93 feature_boolean := plan_dir_mgmt_control;
94 else
95 feature_boolean := 0;
96 end if;
97
98 aux_count := num_dirs;
99
100 END dbms_feature_spd;
[Home] [Help]