PROCEDURE: SYS.DBMS_FEATURE_HCCRLL

Source

1 procedure DBMS_FEATURE_HCCRLL
2     (feature_boolean  OUT  NUMBER,
3      aux_count        OUT  NUMBER,
4      feature_info     OUT  CLOB)
5 AS
6     feature_usage         varchar2(1000);
7     num_cmp_dollar        number;
8     num_hcc               number;
9     num_rll               number;
10     blk_rll               number;
11 
12 begin
13     -- initialize
14     feature_boolean := 0;
15     aux_count := 0;
16     num_cmp_dollar := 0;
17     num_hcc := 0;
18 
19     execute immediate 'select count(*) from compression$ '
20         into num_cmp_dollar;
21 
22     -- check if there is something compressed
23     execute immediate 'select count(*) from seg$ s ' ||
24          ' where bitand(s.spare1, 100663296) = 33554432 OR ' ||
25                ' bitand(s.spare1, 100663296) = 67108864 OR ' ||
26                ' bitand(s.spare1, 100663296) = 100663296 '
27         into num_hcc;
28 
29         -- check for HCC with Row Level Locking
30         execute immediate 'select count(*) from seg$ s ' ||
31           ' where bitand(s.spare1, 2048) = 2048 AND ' ||
32                 ' bitand(s.spare1, 2147483648) = 2147483648 '
33            into num_rll;
34 
35         execute immediate 'select sum(blocks) from seg$ s ' ||
36           ' where bitand(s.spare1, 2048) = 2048 AND ' ||
37                 ' bitand(s.spare1, 2147483648) = 2147483648 '
38            into blk_rll;
39 
40     if ((num_rll > 0) OR (blk_rll > 0)) then
41 
42         feature_boolean := 1;
43 
44      feature_usage :=
45       'Number of Hybrid Columnar Compressed Segments: ' || to_char(num_hcc) ||
46         ', ' || ' Segments Analyzed: ' || to_char(num_cmp_dollar) ||
47         ', ' || ' Segments with HCC Row Level Locking: ' || to_char(num_rll) ||
48         ', ' || ' Blocks with HCC Row Level Locking: ' || to_char(blk_rll);
49 
50         feature_info := to_clob(feature_usage);
51     else
52         feature_info := to_clob('Hybrid Columnar Compression Row Level Locking not detected');
53     end if;
54 
55 end;