[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_HCC
Source
1 procedure DBMS_FEATURE_HCC
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_level1 number;
9 num_level2 number;
10 num_level3 number;
11 num_hcc number;
12 num_dmls number;
13 num_rll number;
14 blk_level1 number;
15 blk_level2 number;
16 blk_level3 number;
17 blk_nonhcc number;
18 blk_nonhcctry number;
19 blk_rll number;
20
21 begin
22 -- initialize
23 feature_boolean := 0;
24 aux_count := 0;
25 num_cmp_dollar := 0;
26 num_hcc := 0;
27 num_level1 := 0;
28 num_level2 := 0;
29 num_level3 := 0;
30 blk_level1 := 0;
31 blk_level2 := 0;
32 blk_level3 := 0;
33
34 execute immediate 'select count(*) from compression$ '
35 into num_cmp_dollar;
36
37 -- check if there is something compressed
38 execute immediate 'select count(*) from seg$ s ' ||
39 ' where bitand(s.spare1, 100663296) = 33554432 OR ' ||
40 ' bitand(s.spare1, 100663296) = 67108864 OR ' ||
41 ' bitand(s.spare1, 100663296) = 100663296 '
42 into num_hcc;
43
44 if ((num_cmp_dollar > 0) OR (num_hcc > 0)) then
45
46 feature_boolean := 1;
47
48 -- check for HCC for Query LOW (level 1)
49 execute immediate 'select count(*) from seg$ s ' ||
50 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
51 ' bitand(s.spare1, 100663296) = 33554432 '
52 into num_level1;
53
54 execute immediate 'select sum(blocks) from seg$ s ' ||
55 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
56 ' bitand(s.spare1, 100663296) = 33554432 '
57 into blk_level1;
58
59 -- check for HCC for Query HIGH (level 2)
60 execute immediate 'select count(*) from seg$ s ' ||
61 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
62 ' bitand(s.spare1, 100663296) = 67108864 '
63 into num_level2;
64
65 execute immediate 'select sum(blocks) from seg$ s ' ||
66 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
67 ' bitand(s.spare1, 100663296) = 67108864 '
68 into blk_level2;
69
70 -- check for HCC for Archive (level 3)
71 execute immediate 'select count(*) from seg$ s ' ||
72 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
73 ' bitand(s.spare1, 100663296) = 100663296 '
74 into num_level3;
75
76 execute immediate 'select sum(blocks) from seg$ s ' ||
77 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
78 ' bitand(s.spare1, 100663296) = 100663296 '
79 into blk_level3;
80
81 -- track OLTP compression (non-HCC compression) w/in HCC
82 execute immediate 'select value from v$sysstat' ||
83 ' where name like ''EHCC Block Compressions'''
84 into blk_nonhcc;
85
86 execute immediate 'select value from v$sysstat' ||
87 ' where name like ''EHCC Attempted ' ||
88 'Block Compressions'''
89 into blk_nonhcctry;
90
91 execute immediate 'select value from v$sysstat' ||
92 ' where name like ''EHCC Conventional DMLs'''
93 into num_dmls;
94
95 -- check for HCC with Row Level Locking
96 execute immediate 'select count(*) from seg$ s ' ||
97 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
98 ' bitand(s.spare1, 2147483648) = 2147483648 '
99 into num_rll;
100
101 execute immediate 'select sum(blocks) from seg$ s ' ||
102 ' where bitand(s.spare1, 2048) = 2048 AND ' ||
103 ' bitand(s.spare1, 2147483648) = 2147483648 '
104 into blk_rll;
105
106 feature_usage :=
107 'Number of Hybrid Columnar Compressed Segments: ' || to_char(num_hcc) ||
108 ', ' || ' Segments Analyzed: ' || to_char(num_cmp_dollar) ||
109 ', ' || ' Segments Compressed Query Low: ' || to_char(num_level1) ||
110 ', ' || ' Blocks Compressed Query Low: ' || to_char(blk_level1) ||
111 ', ' || ' Segments Compressed Query High: ' || to_char(num_level2) ||
112 ', ' || ' Blocks Compressed Query High: ' || to_char(blk_level2) ||
113 ', ' || ' Segments Compressed Archive: ' || to_char(num_level3) ||
114 ', ' || ' Blocks Compressed Archive: ' || to_char(blk_level3) ||
115 ', ' || ' Blocks Compressed Non-HCC: ' || to_char(blk_nonhcc) ||
116 ', ' || ' Attempts to Block Compress: ' || to_char(blk_nonhcctry) ||
117 ', ' || ' Conventional DMLs: ' || to_char(num_dmls) ||
118 ', ' || ' Segments with HCC Row Level Locking: ' || to_char(num_rll) ||
119 ', ' || ' Blocks with HCC Row Level Locking: ' || to_char(blk_rll);
120
121 feature_info := to_clob(feature_usage);
122 else
123 feature_info := to_clob('Hybrid Columnar Compression not detected');
124 end if;
125
126 end;