[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_ADV_IDXCMP
Source
1 procedure DBMS_FEATURE_ADV_IDXCMP(
2 feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage varchar2(1000);
7 oltp_high_idx_cnt number;
8 oltp_low_idx_cnt number;
9 oltp_high_part_idx_cnt number;
10 oltp_low_part_idx_cnt number;
11 num_oltp_high number;
12 num_oltp_low number;
13 blk_oltp_high number;
14 blk_oltp_low number;
15 def_oltp_high number;
16 def_oltp_low number;
17
18 BEGIN
19 feature_boolean := 0;
20 aux_count := 0;
21
22 --Check for ADVANCED HIGH seg, block, deferred seg
23 execute immediate 'select count(*) from seg$ s ' ||
24 ' where s.type# = 6 AND ' ||
25 ' s.user# not in (select user# from user$ ' ||
26 ' where name in (''SYS'' , ''SYSTEM'' )) AND' ||
27 ' bitand(s.spare1, 2048) = 2048 AND ' ||
28 ' bitand(s.spare1, 16777216 + 1048576) = 16777216 '
29 into num_oltp_high;
30
31 if (num_oltp_high = 0) then
32 blk_oltp_high := 0;
33 else
34 execute immediate 'select sum(blocks) from seg$ s ' ||
35 ' where s.type# = 6 AND ' ||
36 ' s.user# not in (select user# from user$ ' ||
37 ' where name in (''SYS'', ''SYSTEM'' )) AND' ||
38 ' bitand(s.spare1, 2048) = 2048 AND ' ||
39 ' bitand(s.spare1, 16777216 + 1048576) = 16777216 '
40 into blk_oltp_high;
41 end if;
42
43 execute immediate 'select count(*) from deferred_stg$ ds ' ||
44 ' where ds.obj# in (select obj# from obj$ ob' ||
45 ' where ob.owner# not in (select user# from user$ ' ||
46 ' where name in (''SYS'', ''SYSTEM'' ))) AND' ||
47 ' bitand(ds.flags_stg, 4) = 4 AND ' ||
48 ' bitand(ds.cmpflag_stg, 6) = 2 '
49 into def_oltp_high;
50
51 --Check for ADVANCED LOW seg, block, deferred seg
52 execute immediate 'select count(*) from seg$ s ' ||
53 ' where s.type# = 6 AND ' ||
54 ' s.user# not in (select user# from user$ ' ||
55 ' where name in (''SYS'', ''SYSTEM'' )) AND' ||
56 ' bitand(s.spare1, 2048) = 2048 AND ' ||
57 ' bitand(s.spare1, 16777216 + 1048576) = 1048576 '
58 into num_oltp_low;
59
60 if (num_oltp_low = 0) then
61 blk_oltp_low := 0;
62 else
63 execute immediate 'select sum(blocks) from seg$ s ' ||
64 ' where s.type# = 6 AND ' ||
65 ' s.user# not in (select user# from user$ ' ||
66 ' where name in (''SYS'', ''SYSTEM'' )) AND' ||
67 ' bitand(s.spare1, 2048) = 2048 AND ' ||
68 ' bitand(s.spare1, 16777216 + 1048576) = 1048576 '
69 into blk_oltp_low;
70 end if;
71
72 execute immediate 'select count(*) from deferred_stg$ ds ' ||
73 ' where ds.obj# in (select obj# from obj$ ob' ||
74 ' where ob.owner# not in (select user# from user$ ' ||
75 ' where name in (''SYS'', ''SYSTEM'' ))) AND' ||
76 ' bitand(ds.flags_stg, 4) = 4 AND ' ||
77 ' bitand(ds.cmpflag_stg, 6) = 4 '
78 into def_oltp_low;
79
80 --Summary
81 feature_usage :=
82 ' Advanced Index Compression feature usage: ' ||
83 ' Segments Compressed for ADVANCED HIGH: ' ||
84 to_char(num_oltp_high) ||
85 ', ' || ' Blocks Compressed for ADVANCED HIGH: ' ||
86 to_char(blk_oltp_high) ||
87 ', ' || ' Deferred Segements Compressed for ADVANCED HIGH: ' ||
88 to_char(def_oltp_high) ||
89 ', ' || ' Segments Compressed for ADVANCED LOW: ' ||
90 to_char(num_oltp_low) ||
91 ', ' || ' Blocks Compressed for ADVANCED LOW: ' ||
92 to_char(blk_oltp_low) ||
93 ', ' || ' Deferred Segements Compressed for ADVANCED LOW: ' ||
94 to_char(def_oltp_low);
95
96 if (num_oltp_high + def_oltp_high + num_oltp_low + def_oltp_low > 0) then
97 feature_boolean := 1;
98 feature_info := to_clob(feature_usage);
99 else
100 feature_boolean := 0;
101 feature_info := to_clob('Advanced Index Compression not detected');
102 end if;
103 END;