Shareware software
Pools

Allocation of memory between pools of the SGA
The data is updated every 15 seconds. Information is collected by the main core of monitoring the operation of the database
select s.name "name",
sum(s.summ) "sum"
from
(select case when pool is null then name
when pool is not null then pool
end as name,
round(sum(bytes)/1024/1024,1) as summ
from v$sgastat
group by pool, name) s
group by s.name
union all
select name,
round(bytes/1024/1024) as sum
from v$sgainfo
where name in ('Free SGA Memory Available','Streams Pool Size','Maximum SGA Size')
Parameter and statistics
A set of tabs with detailed information about memory usage, memory allocation, and suggestions for increasing the size of the SGA and PGA areas
Parameter
The values of the Oracle database parameters responsible for working with memory.
select name,
display_value
from v$parameter
where Upper(name) like Upper('%inmemo%')
or Upper(name) like Upper('%sga%')
or Upper(name) like Upper('%pga%')
or Upper(name) like Upper('%memor%')
or Upper(name) like Upper('%pool%')
order by 1
SGAInfo

Statistics on the distribution of memory of the SGA area between its parts (pools) - detailed
select name, round(bytes/1024/1024,2) "MB", resizeable from V$SGAINFO
SGAStat

Details of the System Global Area (SGA)
select pool, name, bytes from V$SGASTAT
PGAStat

Displays PGA memory usage statistics, as well as statistics about the PGA's automatic memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values accumulate from the moment the instance is started.
select name,
case
when Upper(substr(unit, 0,4))= Upper('byte') then round(value/1024/1024,2)||' MB' else
to_char(value)
end "value",
substr(unit, 0,4) "Type"
from V$PGASTAT
PGAAdvice

Predicts how the value of the PGA_AGGREGATE_TARGET parameter will change in cache hit percentage and reallocation count statistics displayed by the V$PGASTAT performance view . Prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter chosen around its current value. Recommendation statistics are generated by simulating the past workload performed by the instance. If PGA_AGGREGATE_TARGET is not set, the content will be empty. Also, content is not updated when STATISTICS_LEVEL is set to BASIC . The base statistics are reset when the instance is started and when the value of the initialization parameter PGA_AGGREGATE_TARGET is dynamically changed.
SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb, estd_pga_cache_hit_percentage cache_hit_perc
FROM v$pga_target_advice
Cache advice

Predicts the number of physical reads for different cache sizes. A "physical read ratio" is also calculated, which is the ratio of the number of expected reads to the number of reads actually performed by the actual buffer cache during the measurement interval.
SELECT size_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name= 'DEFAULT'
AND block_size = (SELECT value
FROM v$parameter WHERE name = 'db_block_size'
)
AND advice_status = 'ON'
Memory advice

Information on how to resize the MEMORY_TARGET setting based on current instance size and performance.
SELECT memory_size,
memory_size_factor,
estd_db_time,
estd_db_time_factor