ORACLE - Memory using
The tool allows administrators to evaluate the allocation of memory allocated to a DBMS instance by regions, 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",
(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
group by pool, name) s
group by s.name
round(bytes/1024/1024) as sum
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
The values of the Oracle database parameters responsible for working with memory.
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
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
Details of the System Global Area (SGA)
select pool, name, bytes from V$SGASTAT
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.
when Upper(substr(unit, 0,4))= Upper('byte') then round(value/1024/1024,2)||' MB' else
substr(unit, 0,4) "Type"
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
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.
WHERE name= 'DEFAULT'
AND block_size = (SELECT value
FROM v$parameter WHERE name = 'db_block_size'
AND advice_status = 'ON'
Information on how to resize the MEMORY_TARGET setting based on current instance size and performance.
ORDER BY memory_size