1 PROCEDURE dbms_feature_stats_incremental
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 im_preference VARCHAR2(30) := 'INCREMENTAL';
7 global_on VARCHAR2(20);
8 table_im_on NUMBER;
9 table_im_off NUMBER;
10 stats_gathered_im NUMBER;
11 tmp_buf VARCHAR2(32767);
12 BEGIN
13
14 /*
15 * We compute the following stats for db feature usage:
16 * whether global preference of incremental maintenance turned on
17 * # of tables with table level incremental maintenance preference
18 * turned on
19 * # of tables with table level incremental maintenance preference
20 * turned off
21 * # of tables that have had stats gathered in incremental mode
22 */
23
24 --whether global preference of incremental maintenance turned on
25 SELECT decode(count(*), 0, 'FALSE', 'TRUE')
26 INTO global_on
27 FROM dual
28 WHERE dbms_stats.get_prefs(im_preference) = 'TRUE';
29
30 --# of tables with table level incremental maintenance preference
31 -- turned on
32 SELECT count(*)
33 INTO table_im_on
34 FROM all_tab_stat_prefs
35 WHERE PREFERENCE_NAME = im_preference and PREFERENCE_VALUE = 'TRUE';
36
37 -- # of tables with table level incremental maintenance preference
38 -- turned off
39 SELECT count(*)
40 INTO table_im_off
41 FROM all_tab_stat_prefs
42 WHERE PREFERENCE_NAME = im_preference and PREFERENCE_VALUE = 'FALSE';
43
44 -- # of tables that have had stats gathered in incremental mode
45 SELECT distinct count(bo#)
46 INTO stats_gathered_im
47 FROM sys.wri$_optstat_synopsis_head$
48 WHERE analyzetime is not null;
49
50 -- the used boolean and aux count we set to the number of execs since last
51 -- sample
52 feature_boolean := stats_gathered_im;
53 aux_count := stats_gathered_im;
54
55 -- compose the CLOB
56 tmp_buf := 'Incremental global preference on : ' || global_on || ', ' ||
57 'Number of tables with table level incremental maintenance preference ' ||
58 'turned on: ' || table_im_on || ', ' ||
59 'Number of tables with table level incremental maintenance preference ' ||
60 'turned off: ' || table_im_off || ', ' ||
61 'Number of tables that have had statistics gathered in incremental mode: ' ||
62 stats_gathered_im;
63
64 dbms_lob.createtemporary(feature_info, TRUE);
65 dbms_lob.writeappend(feature_info, length(tmp_buf), tmp_buf);
66
67 END dbms_feature_stats_incremental;
[Home] [Help]