PROCEDURE: SYS.DBMS_FEATURE_AUTO_SGA

Source

1 procedure DBMS_FEATURE_AUTO_SGA
2     (feature_boolean  OUT  NUMBER,
3      aux_count        OUT  NUMBER,
4      feature_info     OUT  CLOB)
5 AS
6   feature_usage          varchar2(1000);
7   sga_target             number;
8   sga_max_size           number;
9 begin
10 
11   -- initialize
12   feature_boolean := 0;
13   aux_count := 0;
14   feature_info := to_clob('Automatic SGA Tuning usage not detected');
15   feature_usage := '';
16   sga_target := 0;
17   sga_max_size := 0;
18 
19   execute immediate 'select to_number(value) from v$system_parameter where ' ||
20                     'name like ''sga_target'''
21   into sga_target;
22 
23   if sga_target > 0
24   then
25 
26     feature_boolean := 1;
27 
28     feature_usage := feature_usage||':sga_target:'||sga_target;
29 
30     -- get sga_max_size value
31     execute immediate 'select to_number(value) from v$system_parameter where ' ||
32                       'name like ''sga_max_size'''
33     into sga_max_size;
34 
35     feature_usage := feature_usage||':sga_max_size:'||sga_max_size;
36 
37     -- get v$memory_dynamic_components info
38     for item in (select component, current_size, min_size, max_size,
39                  user_specified_size from
40                  v$memory_dynamic_components where current_size != 0)
41     loop
42       feature_usage := feature_usage||':comp:'||item.component||
43                        ':cur:'||item.current_size||':min:'||
44                        item.min_size||':max:'||item.max_size||
45                        ':usr:'||item.user_specified_size;
46     end loop;
47 
48     -- get v$system_event info for SGA events
49     for item in (select substr(event, 0, 15) evt, total_waits, time_waited
50                  from v$system_event where event like '%SGA%')
51     loop
52       feature_usage := feature_usage||':event:'||item.evt||':waits:'||
53                        item.total_waits||':time:'||item.time_waited;
54     end loop;
55     feature_info := to_clob(feature_usage);
56 
57   end if;
58 
59 end;