PROCEDURE: SYS.DBMS_FEATURE_AUTO_MEM

Source

1 procedure DBMS_FEATURE_AUTO_MEM
2     (feature_boolean  OUT  NUMBER,
3      aux_count        OUT  NUMBER,
4      feature_info     OUT  CLOB)
5 AS
6   feature_usage          varchar2(1000);
7   memory_target             number;
8   sga_max_size              number;
9   memory_max_target         number;
10 begin
11 
12   -- initialize
13   feature_boolean := 0;
14   aux_count := 0;
15   feature_info := to_clob('Automatic Memory Tuning usage not detected');
16   feature_usage := '';
17   memory_target := 0;
18   sga_max_size := 0;
19   memory_max_target := 0;
20 
21   execute immediate 'select to_number(value) from v$system_parameter where ' ||
22                     'name like ''memory_target'''
23   into memory_target;
24 
25   if memory_target > 0
26   then
27 
28     feature_boolean := 1;
29 
30     feature_usage := feature_usage||':memory_target:'||memory_target;
31 
32     -- get sga_max_size value
33     execute immediate 'select to_number(value) from v$system_parameter where ' ||
34                       'name like ''sga_max_size'''
35     into sga_max_size;
36 
37     feature_usage := feature_usage||':sga_max_size:'||sga_max_size;
38 
39     -- get memory_max_target value
40     execute immediate 'select to_number(value) from v$system_parameter where ' ||
41                       'name like ''memory_max_target'''
42     into memory_max_target;
43 
44     feature_usage := feature_usage||':memory_max_target:'||memory_max_target;
45 
46     -- get v$memory_dynamic_components info
47     for item in (select component, current_size, min_size, max_size,
48                  user_specified_size from
49                  v$memory_dynamic_components where current_size != 0)
50     loop
51       feature_usage := feature_usage||':comp:'||item.component||
52                        ':cur:'||item.current_size||':min:'||
53                        item.min_size||':max:'||item.max_size||
54                        ':usr:'||item.user_specified_size;
55     end loop;
56 
57     -- get v$pgastat info
58     for item in (select name, value from v$pgastat where
59                  name in ('tot PGA alc', 'over alc cnt',
60                           'tot PGA for auto wkar',
61                           'tot PGA for man wkar',
62                           'glob mem bnd', 'aggr PGA auto tgt',
63                           'aggr PGA tgt prm'))
64     loop
65       feature_usage := feature_usage||':'||item.name||':'||item.value;
66     end loop;
67 
68     -- get v$memory_target_advice info
69     feature_usage := feature_usage||':mem tgt adv:';
70     for item in (select memory_size, memory_size_factor, estd_db_time,
71                  estd_db_time_factor from v$memory_target_advice
72                  order by memory_size)
73     loop
74       feature_usage := feature_usage||':msz:'||item.memory_size||
75                        ':sf:'||item.memory_size_factor||
76                        ':time:'||item.estd_db_time||
77                        ':tf:'||item.estd_db_time_factor;
78     end loop;
79 
80     -- get v$system_event info for SGA events
81     for item in (select substr(event, 0, 15) evt, total_waits, time_waited
82                  from v$system_event where event like '%SGA%')
83     loop
84       feature_usage := feature_usage||':event:'||item.evt||':waits:'||
85                        item.total_waits||':time:'||item.time_waited;
86     end loop;
87 
88     feature_info := to_clob(feature_usage);
89 
90   end if;
91 
92 end;