1 PROCEDURE dbms_feature_concurrent_stats
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_all_ops NUMBER; -- number of all stats operations
9 num_gath_ops NUMBER; -- number of gather operations
10 num_conc_ops NUMBER; -- number of concurrent stats operations
11 num_auto_ops NUMBER; -- number of auto stats gathering jobs
12 interval_start VARCHAR(20); -- minimum start time in operations view
13 interval_end VARCHAR(20); -- maximum start time in operations view
14 conc_pref VARCHAR2(10); -- value of "concurrent" preference
15 tmp_buf VARCHAR2(32767);
16
17 -- cursor to get the types of stats gathering operations which were
18 -- were performed concurrently.
19 CURSOR op_type_cursor IS
20 select operation op, count(*) cnt from dba_optstat_operations
21 where extractvalue(xmltype(notes),
22 '/params/param[@name="concurrent"]/@val') = 'TRUE'
23 group by operation
24 order by 2 desc;
25
26 BEGIN
27 dbms_lob.createtemporary(feature_info, TRUE);
28
29 -- get the time interval reported in dba_optstat_operations
30 select to_char(min(start_time), 'MM/DD/YYYY'),
31 to_char(max(start_time), 'MM/DD/YYYY')
32 into interval_start, interval_end
33 from dba_optstat_operations;
34
35 tmp_buf := 'Time interval covered by dba_optstat_operations: '||
36 interval_start || ' - ' || interval_end || NEW_LINE;
37 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
38
39 -- get the total number of rows in dba_optstat_operations
40 SELECT count(*)
41 INTO num_all_ops
42 FROM dba_optstat_operations;
43
44 tmp_buf := 'Total Number of All Stats Operations: '|| num_all_ops
45 || NEW_LINE;
46 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
47
48 -- get the number of gather stats operations which is eligible to be run
49 -- concurrently
50 SELECT count(*)
51 INTO num_gath_ops
52 FROM dba_optstat_operations
53 WHERE operation like 'gather%';
54
55 tmp_buf := 'Total Number of Gather Stats Operations: '|| num_gath_ops
56 || NEW_LINE;
57 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
58
59 -- get the number of concurrent stats gathering operations
60 SELECT count(*)
61 INTO num_conc_ops
62 FROM dba_optstat_operations
63 WHERE extractvalue(xmltype(notes),
64 '/params/param[@name="concurrent"]/@val') = 'TRUE';
65
66 tmp_buf := 'Total Number of Concurrent Operations: ' || num_conc_ops || '.'
67 || NEW_LINE ||
68 'Types of concurrent operations with their frequencies:'
69 || NEW_LINE;
70 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
71
72 -- # of concurrent operations group by the kind of operation
73 for op_type_iter in op_type_cursor
74 loop
75 tmp_buf := ' ' || op_type_iter.op ||': '||
76 op_type_iter.cnt || NEW_LINE;
77 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
78 end loop;
79
80 -- value of "concurrent" preference
81 select dbms_stats.get_prefs('CONCURRENT')
82 into conc_pref
83 from dual;
84
85 tmp_buf := 'Current value of CONCURRENT preference: '|| conc_pref || NEW_LINE;
86 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
87
88 -- populate the outputs if concurrent stats gathering is enabled
89 if (num_conc_ops > 0 OR
90 upper(conc_pref) not in ('OFF', 'FALSE')) then
91 feature_boolean := 1;
92 else
93 feature_boolean := 0;
94 end if;
95
96 if (num_gath_ops > 0) then
97 aux_count := num_conc_ops/num_gath_ops;
98 else
99 aux_count := -1;
100 end if;
101
102 END dbms_feature_concurrent_stats;
[Home] [Help]