Oracle Database Memory
-- In Oracle database SMON (System Monitor) performs 
-- background activities such as coalescing tablespace, 
-- recovery of failed transactions, shrinking rollback 
---segments etc., and uses a lot of CPU.  The query 
-- below shows the spid of the system monitor (SMON).

SELECT spid, program, background
FROM  v$process
WHERE  INSTR(program,'SMON') >0;

-- The query below shows the memory details of SMON.

SELECT  spid, program, 
 pga_max_mem     memory_max,
 pga_alloc_mem   memory_alloc,
 pga_used_mem    memory_used,
 pga_freeable_mem   memory_free,
 100*pga_used_mem/pga_alloc_mem  usage_Pct
FROM v$process
WHERE  INSTR(program,'SMON') >0;

-- The query below shows the memory used by various 
-- processes.

SELECT
  p.spid, p.program, 
  pm.category,
  pm.allocated,
  pm.max_allocated
FROM v$process p,
     v$process_memory pm
WHERE pm.pid = p.pid
AND   p.spid = (SELECT spid
                FROM   v$process
                WHERE  INSTR(program,'SMON') >0);

Oracle Database CPU Utilization Analysis

Oracle Database CPU Analysis Queries

Oracle PGA and SGA Analysis

Oracle registered trademark of Oracle Corporation.

Last Revised On: February 20th, 2015

  449