PROCEDURE: SYS.DBMS_FEATURE_DYN_SGA

Source

1 procedure DBMS_FEATURE_DYN_SGA
2     (feature_boolean  OUT  NUMBER,
3      aux_count        OUT  NUMBER,
4      feature_info     OUT  CLOB)
5 AS
6   num_resize_ops         number;                 -- number of resize operations
7   feature_usage          varchar2(1000);
8 begin
9   -- initialize
10   num_resize_ops := 0;
11   feature_boolean := 0;
12   aux_count := 0;
13   feature_info := to_clob('Dynamic SGA usage not detected');
14   feature_usage := '';
15 
16   execute immediate 'select count(*) from v$sga_resize_ops ' ||
17                     'where oper_type in (''GROW'', ''SHRINK'') and ' ||
18                     'oper_mode=''MANUAL''and ' ||
19                     'start_time >= ' ||
20                     'to_date((select nvl(max(last_sample_date), sysdate-7) ' ||
21                     'from dba_feature_usage_statistics))'
22   into num_resize_ops;
23 
24   if num_resize_ops > 0
25   then
26 
27     feature_boolean := 1;
28 
29     feature_usage := feature_usage||':rsz ops:'||num_resize_ops;
30 
31     -- get v$memory_dynamic_components info
32     for item in (select component, current_size, min_size, max_size,
33                  user_specified_size from
34                  v$memory_dynamic_components where current_size != 0)
35     loop
36       feature_usage := feature_usage||':comp:'||item.component||
37                        ':cur:'||item.current_size||':min:'||
38                        item.min_size||':max:'||item.max_size||
39                        ':usr:'||item.user_specified_size;
40     end loop;
41 
42     -- get v$system_event info for SGA events
43     for item in (select substr(event, 0, 15) evt, total_waits, time_waited
44                  from v$system_event where event like '%SGA%')
45     loop
46       feature_usage := feature_usage||':event:'||item.evt||':waits:'||
47                        item.total_waits||':time:'||item.time_waited;
48     end loop;
49 
50     feature_info := to_clob(feature_usage);
51 
52   end if;
53 
54 end;