top of page
CerebroSQL

ORACLE - Memory using

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

Oracle memory general

Pools

Oracle memory usage

 

 

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

Oracle - SGA Info

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

Oracle - SGA Stat

Details of the System Global Area (SGA)

select pool, name, bytes from V$SGASTAT

PGAStat

Oracle - PGA Stat

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

Oracle - PGA advice

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

Oracle - 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

Oracle - 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

bottom of page