Enhanced v$process view
RAM memory monitoring is implemented in Oracle by enhancing the v$process view.
The new columns in the
The three new columns in the v$process view include
v$process view allow you to show details about the (PGA) program global area regions for all current Oracle processes.
The PGA is a dedicated area of RAM memory used by individual processes to perform RAM intensive functions, such as sorting. The three new columns in the v$process view include
- pga_used_memory,
- pga_allocated_memory, and
- pga_max_memory.
- see the actual RAM utilization for individual background processes within the Oracle environment and
- also look at the RAM demands of individual connections to the database.
col c1 heading 'Program|Name' format a30 col c2 heading 'PGA|Used|Memory' format 999,999,999 col c3 heading 'PGA|Allocated|Memory' format 999,999,999 col c4 heading 'PGA|Maximum|Memory' format 999,999,999
select
program c1,
pga_used_mem c2,
pga_alloc_mem c3,
pga_max_mem c4
from
v$process
order by
c4 desc;
Figure 1
PROGRAM PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM ------------------------- ------------ ------------- ----------- oracle@john (PMON) 120,463 234,291 234,291 oracle@john (DBW0) 1,307,179 1,817,295 1,817,295 oracle@john (LGWR) 4,343,655 4,849,203 4,849,203 oracle@john (CKPT) 194,999 332,583 332,583 oracle@john (SMON) 179,923 775,311 775,323 oracle@john (RECO) 129,719 242,803 242,803 oracle@john (TNS V1-V3) 1,400,543 1,540,627 1,540,915 oracle@john (P000) 299,599 373,791 635,959 oracle@john (P001) 299,599 373,791 636,007 oracle@john (TNS V1-V3) 1,400,543 1,540,627 1,540,915 oracle@john (TNS V1-V3) 22,341 1,716,253 3,625,241
This example provides insight into the behavior of the Oracle database engine. One can see that the (LGWR) log writer process is the highest consumer of PGA RAM memory, which makes sense because the
Oracle Log Writer process must transfer redo log images from the Log Buffer (in RAM memory) to the online redo log filesystem. You can also see high RAM memory utilization for the (DBW0) Database Writer process.
The asynchronous I/O processes used by Oracle make extensive use of RAM memory resources to ensure that all database changes are successfully written to the database.
