RelationalDBDesign RelationalDBDesign

Instance Architecture   «Prev 

Enhanced v$process view

RAM memory monitoring is implemented in Oracle by enhancing the v$process view. The new columns in the 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
  1. pga_used_memory,
  2. pga_allocated_memory, and
  3. pga_max_memory.
After analyzing these metrics, you can
  1. see the actual RAM utilization for individual background processes within the Oracle environment and
  2. also look at the RAM demands of individual connections to the database.
To illustrate, consider the following query:
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

    program       c1,
    pga_used_mem  c2,
    pga_alloc_mem c3,
    pga_max_mem   c4
 order by
    c4 desc;

Figure 1
 ------------------------- ------------   -------------   -----------
 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.

1) When a database first starts, all the database buffers are empty.

2) As queries are executed, the cache begins to fill up. Blocks are read from disk and placed in the buffers.

3) UPDATE, INSERT, and DELETE statements cause the data in some blocks to change.

4) The database writer periodically checks for modified blocks and writes them back to disk.

5) Modified blocks are written to disk, but also kept in memory in case they are needed again.

6) If database activity slows, the database writer will catch up. Here the buffers are full but all changes have been written.