ORACLE - Memory using

The tool allows administrators to estimate the distribution of memory allocated to a DBMS instance by area, pool

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

FROM v$memory_target_advice

ORDER BY memory_size