PROCEDURE: SYS.DBMS_FEATURE_IMA

Source

1 procedure DBMS_FEATURE_IMA(
2     feature_boolean OUT NUMBER,
3     aux_count       OUT NUMBER,
4     feature_info    OUT CLOB)
5 AS
6     feature_usage               varchar2(1000);
7     num_kv                      number;
8     avg_num_dim                 number;
9     max_num_dim                 number;
10     num_fact                    number;
11 
12 BEGIN
13     feature_boolean             := 0;
14     aux_count                   := 0;
15 
16     -- find the total number of recent key vectors
17     execute immediate 'select count(*) from gv$key_vector where probed > 0'
18       into num_kv;
19 
20     -- average number of recent key vectors per query, overall maximum and
21     -- total number of unique fact tables referenced (does not include
22     -- complex facts)
23     execute immediate 'select round(avg(key_vector_count),2),
24                               max(key_vector_count),
25                               count(distinct fact_name)
26                        from (select fact_name, count(*) key_vector_count
27                              from gv$key_vector
28                              where probed > 0
29                              group by sql_id, sql_exec_id, fact_name)'
30       into avg_num_dim, max_num_dim, num_fact;
31 
32     --Summary
33     feature_usage :=
34         ' In-Memory Aggregation Feature Usage: ' ||
35                 'Total Number of Key Vectors: ' ||
36                   to_char(num_kv) ||
37          ', ' || 'Maximum Number of Key Vectors for a Query: ' ||
38                   to_char(max_num_dim) ||
39         ', ' || 'Average Number of Key Vectors per Query: ' ||
40                   to_char(avg_num_dim) ||
41         ', ' || 'Total Number of Unique Fact Tables: ' ||
42                   to_char(num_fact);
43 
44      if (num_kv > 0) then
45       feature_boolean := 1;
46       feature_info := to_clob(feature_usage);
47     else
48       feature_boolean := 0;
49       feature_info := to_clob('In-Memory Aggregation Not Detected');
50     end if;
51 END DBMS_FEATURE_IMA;