Memory Usage for Oracle Processes

understanding the memory involved for an Oracle processes.


[root@hostname~]# ps -afe | grep ora_smon

oracle    2587     1  0 11:54 ?        00:00:01 ora_smon_db11


SQL> SELECT spid,program,background
          FROM v$process
          WHERE spid = 2587;


SPID  PROGRAM                                    B
-----    ----------------------------------------   -
2587  oracle@instance_name...com         (SMON)    


When an Oracle instance starts, or an application uses the Oracle database, the associated Oracle processes are allocated and consume memory. We can look at this memory usage in a number of ways. Firstly, the V$PROCESS view has some additional columns that report on total memory usage. The V$PROCESS view has a set of columns that report on the PGA memory (Program Global Area) which is a private memory region that contains data and control information for the process. Use the following SQL to look at the smon background process we identified in the previous SQL and see its PGA memory allocations.

SQL>  SELECT spid, program,
            pga_max_mem      max,
            pga_alloc_mem    alloc,
            pga_used_mem     used,
            pga_freeable_mem free
            FROM V$PROCESS
            WHERE spid = 2587;

To get an idea for the different categories a process uses memory for, you can query the V$PROCESS_MEMORY table. This table shows the dynamic PGA memory usage for each Oracle processes and contains information, if used, for Java, PL/SQL, OLAP, and SQL areas. Key to understanding and dynamically monitoring the PGA memory usage of processes are:

If you wanted to monitor the memory usage for our smon background processes in detail you could use the following SQL.


SQL> SELECT p.program,
            p.spid,
            pm.category,
            pm.allocated,
            pm.used,
            pm.max_allocated
            FROM V$PROCESS p, V$PROCESS_MEMORY pm
           WHERE p.pid = pm.pid
           AND p.spid = 2587;

How do you display the amount of RAM on an Oracle server?

Displaying the total RAM on the UNIX/Linux server is easy.

If you are licensed for the diagnostics and tuning packs, you can see RAM usage with below script:



prompt
prompt ******************************************************
prompt Get server RAM Size
prompt ******************************************************
col c1 heading 'Physical|Memory|MB' format 999,999,999
select
   max(value)/1024/1024 c1
from
   dba_hist_osstat
where
   stat_name = 'PHYSICAL_MEMORY_BYTES';


prompt
prompt ******************************************************
prompt Get server minimum Free RAM Size
prompt ******************************************************
col c1 heading 'Minimum|Physical|Free|Memory|MB' format 999,999,999
select
   min(value)/1024/1024 c1
from
   dba_hist_osstat
where
   stat_name ='FREE_MEMORY_BYTES';



prompt
prompt ******************************************************
prompt Get server maximum Free RAM Size
prompt ******************************************************
col c1 heading 'Maximum|Physical|Free|Memory|MB' format 999,999,999
select
   max(value)/1024/1024 c1
from
   dba_hist_osstat
where
   stat_name ='FREE_MEMORY_BYTES';