top of page

ORACLE - Memory using

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

Oracle memory general


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 "name",

       sum(s.summ) "sum"


   (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

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


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

select name,


  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


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


Oracle - SGA Stat

Details of the System Global Area (SGA)

select pool, name, bytes from V$SGASTAT


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,       


        when Upper(substr(unit, 0,4))= Upper('byte') then                     round(value/1024/1024,2)||' MB' else


        end "value",

        substr(unit, 0,4) "Type" 

   from V$PGASTAT


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,       



  FROM v$db_cache_advice


   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,




  FROM v$memory_target_advice

ORDER BY memory_size

bottom of page