Figure A

Oracle9i background processes (from Oracle9i manual)

Enhanced v$process view

Oracle has implemented RAM memory monitoring by enhancing the v$process view. The new columns in the v$process view allow you to show details about the program global area (PGA) 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. From these metrics, you can 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. 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

select
   program       c1,
   pga_used_mem  c2,
   pga_alloc_mem c3,
   pga_max_mem   c4
from
   v$process
order by
   c4 desc;

Listing A below shows the script output.

Listing A

PROGRAM                   PGA_USED_MEM   PGA_ALLOC_MEM   PGA_MAX_MEM ------------------------- ------------   -------------   ----------- oracle@janet (PMON)            120,463         234,291       234,291 oracle@janet (DBW0)          1,307,179      1,817,295     1,817,295 oracle@janet (LGWR)          4,343,655       4,849,203     4,849,203 oracle@janet (CKPT)            194,999         332,583       332,583 oracle@janet (SMON)            179,923         775,311       775,323 oracle@janet (RECO)           129,719         242,803       242,803 oracle@janet (TNS V1-V3)     1,400,543       1,540,627     1,540,915 oracle@janet (P000)            299,599         373,791       635,959 oracle@janet (P001)            299,599         373,791       636,007 oracle@janet (TNS V1-V3)     1,400,543       1,540,627     1,540,915 oracle@janet (TNS V1-V3)        22,341       1,716,253     3,625,241

This example provides insight into the behavior of the Oracle database engine. For example, you can see that Oracle's log writer (LGWR) process is the highest consumer of PGA RAM memory, which makes sense because the Oracle Log Writer process must transfer redo log images from Oracle's Log Buffer (in RAM memory) to the online redo log filesystem. You can also see high RAM memory utilization for Oracle's Database Writer (DBW0) process. This also makes sense, because Oracle's asynchronous I/O process must make extensive use of RAM memory resources to ensure that all database changes are successfully written to the database.

RAM for individual processes

But the real value in viewing RAM usage in Oracle9i is to see RAM utilization for individual processes. Oracle9i now has a shared RAM region called pga_aggregate_target. When using the Oracle multithreaded server, the pga_aggregate_target parameter works similar to Oracle's large pool but with one important difference. By having a shared RAM memory area, individual Oracle processes are free to use up to 5 percent of the total amount of memory within the pool when performing sorting and hash join activities. This is a huge improvement over the Oracle8i requirement that each PGA region be restricted according to the value of the sort_area_size initialization parameter.

Oracle9i also provides a new dictionary view called v$pgastat. The v$pgastat view shows the total amount of RAM memory utilization for every RAM memory region within the database. This information can tell you the high water mark of RAM utilization, and allow you to size RAM memory demands according to the relative stress on the system. Here is a simple query against v$pgastat:

column name format a40
column value format 999,999,999

select
   name,
   value
from
   v$pgastat
order by
   value desc;

Listing B below contains the output from this script.

Listing B

NAME                                                   VALUE     ------------------------------------------------------ ---------- aggregate PGA auto target                             736,052,224 global memory bound                                        21,200 total expected memory                                     141,144 total PGA inuse                                        22,234,736 total PGA allocated                                    55,327,872 maximum PGA allocated                                  23,970,624 total PGA used for auto workareas                        262,144 maximum PGA used for auto workareas                     7,333,032 total PGA used for manual workareas                             0 maximum PGA used for manual workareas                           0 estimated PGA memory for optimal                         141,395 maximum PGA memory for optimal                        500,123,520 estimated PGA memory for one-pass                         534,144 maximum PGA memory for one-pass                        52,123,520

From this listing, you can see the value of pga_aggregate_target and the high water marks for all RAM memory areas used by this instance. But let's take a look at optimal, one pass, and multipass RAM memory executions.

When an Oracle process requires an operation, such as a sort or a hash join, it goes to the shared RAM memory area within pga_aggregate_target region and attempts to obtain enough contiguous RAM frames to perform the operation. If the process is able to acquire these RAM frames immediately, it is marked as an optimal RAM access. If the RAM acquisition requires a single pass through pga_aggregate_target, the RAM memory allocation is marked as one pass. If all RAM is in use, Oracle may have to make multiple passes through pga_aggregate_target to acquire the RAM memory. This is called multipass.

Remember, RAM memory is extremely fast, and most sorts or hash joins are completed in microseconds. Oracle allows a single process to use up to 5 percent of the pga_aggregate_target, and parallel operations are allowed to consume up to 30 percent of the PGA RAM pool.

Multipass executions indicate a RAM shortage, and you should always allocate enough RAM to ensure that at least 95 percent of connected tasks can acquire their RAM memory optimally.

You can also obtain information about workarea executions by querying the v$sysstat view shown here:

col c1 heading 'Workarea|Profile' format a35
col c2 heading 'Count'            format 999,999,999
col c3 heading 'Percentage'       format 99
 

select
   name                                      c1,
   cnt                                       c2,
   decode(total, 0, 0, round(cnt*100/total)) c3
from
(
   select name,value cnt,(sum(value) over ()) total
   from
      v$sysstat
   where
   name like 'workarea exec%'
);

Listing C below shows the output.

Listing C

PROFILE                        CNT        PERCENTAGE -----------------------------  ---------- ---------- workarea executions - optimal        5395         98 workarea executions - onepass         284          2 workarea executions - multipass         0          0

At least 95 percent of the tasks should have optimal workarea executions. In the output above, you can see all workarea executions that were able to execute optimal, onepass, and multipass modes.

This listing provides valuable information regarding the appropriate size for the pga_aggregate_target region. It can also indicate an overallocation of the RAM memory region. If the percentage of optimal workarea executions consistently stays at 98 to 100 percent, you can safely steal RAM frames from pga_aggregate_target and reallocate them to other areas of the Oracle SGA (such as db_cache_size) that may have a greater need for the RAM memory resources.

Viewing individual workareas

Oracle also provides data dictionary views that show the amount of RAM memory used by individual steps within the execution plan of SQL statements. This can be invaluable for the appropriate sizing of hash_area_size and other RAM-intensive parameters.

The v$sql_workarea_active view shows the amount of RAM usage by each individual workarea within the Oracle9i database.

Also, Oracle provides several methods for joining tables together, each with widely varying RAM memory usage. The Oracle9i SQL optimizer can choose sort merge joins, nested loop joins, hash joins, and star joins methods. In some cases, the hash join can run faster than a nested loop join, but hash joins require RAM memory resources and a high setting for the hash_area_size parameter.

This query shows the query for v$sql_workarea_active view:

Select
   to_number(decode(SID, 65535, NULL, SID)) sid,
   operation_type                           OPERATION,
   trunc(WORK_AREA_SIZE/1024)               WSIZE,
   trunc(EXPECTED_SIZE/1024)                ESIZE,
   trunc(ACTUAL_MEM_USED/1024)              MEM,
   trunc(MAX_MEM_USED/1024)                 "MAX MEM",
   number_passes                            PASS
from
   v$sql_workarea_active
order by 1,2;

Listing D below shows the output.

Listing D

SID OPERATION             WSIZE     ESIZE       MEM   MAX MEM PASS --- --------------------- ----- --------- --------- --------- ----  27 GROUP BY (SORT)          73        73        64        64    0  44 HASH-JOIN              3148      3147      2437      6342    1  71 HASH-JOIN             13241     19200     12884    34684    1

In Listing D, you can see the amount of RAM used for each step of SQL execution. One SQL statement is performing a Group By sort using 73 KB of RAM memory. You can also see the system ID (SID) for two SQL statements that are performing hash joins. These hash joins are using the 3 and 13 MB respectively to build their in-memory hash tables.

An invaluable tool

Oracle is becoming one of the most flexible and sophisticated database management systems. Its ability to provide views of the RAM usage of the database components is an invaluable tool for managing your Oracle databases. With it, Oracle professionals can more easily ensure that all the settings for RAM memory utilization are optimal for their applications.

 

If you like Oracle tuning, check-out my latest book "Oracle Tuning: The Definitive Reference".  Packed with almost 1,000 pages of Oracle performance tuning techniques, it's the foolproof way to find and correct Oracle bottlenecks.