Топ-100
 
CerebroSQL

ORACLE - Memory using

The tool allows administrators to estimate the memory allocation allocated to a DBMS instance  by regions, pools  

Oracle memory usage.jpg

Pools

Allocating Memory Between SGA Pools

The data is updated every 15 seconds. Information is collected by the main core of monitoring the operation of the database

-> Request to retrieve data <-

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 on memory usage, memory allocation, and suggestions for increasing the size of the SGA and PGA areas

Parameter

The values of the parameters of the Oracle database responsible for working with memory.

-> Request to retrieve data <-

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
Oracle - SGA Info.jpg

Statistics on the memory allocation of the SGA area between its parts (pools) - detailed

-> Request to retrieve data <-

select name, round (bytes / 1024 / 1024.2) "MB", resizeable from V$SGAINFO

-----

SGAStat
Oracle - SGA Stat.jpg

System Global Area (SGA) Details

-> Request to retrieve data <-

select pool, name, bytes from V$SGASTAT

-----

PGAStat
Oracle - PGA Stat.jpg

Displays statistics about memory usage in the PGA area, as well as statistics about the automatic PGA memory manager when enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values are accumulated since the start of the instance.

-> Request to retrieve data <-

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
Oracle - PGA advice.jpg

Predicts how the value of the PGA_AGGREGATE_TARGET parameter will change by the percentage of cache hits and the number of reallocations statistics displayed by the V$PGASTAT performance view. Prediction is performed for different values of the PGA_AGGREGATE_TARGET parameter selected around its current value. The recommendation statistics are generated by simulating the past workload of the instance. If PGA_AGGREGATE_TARGET is not set, the content will be empty. Also, content is not updated if STATISTICS_LEVEL is set to BASIC. Base statistics are reset when the instance is started and when the PGA_AGGREGATE_TARGET initialization parameter is dynamically changed.

-> Request to retrieve data <-

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
Oracle - Cache advice.jpg

Predict the number of physical reads for different cache sizes. It also computes a "physical read rate", which is the ratio of the number of anticipated reads to the number of reads actually performed by the actual buffer cache during the measurement interval.

-> Request to retrieve data <-

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
Oracle - Memory advice.jpg

Information on how to resize the MEMORY_TARGET parameter based on the current size and quality of the instance.

-> Request to retrieve data <-

SELECT memory_size, memory_size_factor, estd_db_time, estd_db_time_factor
FROM  v$memory_target_advice
ORDER BY memory_size

-----