Editor’s note: This article was originally published on Builder.com.

Prior to Oracle9i, most relational databases couldn’t show the individual RAM memory usage for processes connected to a database. Because many performance problems are directly related to a shortage of RAM memory, it’s important for the database professional to see RAM memory utilization within the database, both for connected sessions and the database processes.

Oracle has addressed this issue by enhancing its v$ views to include information about RAM memory utilization. Oracle9i also provides a number of background processes to provide database services. These background processes (Figure A) perform system management functions. Understanding how these processes utilize system resources allows you to ensure that your settings for RAM memory utilization are optimal for your applications. Let’s take a look at some of the views and the information you can gather from them.

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 shows the script output.

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 similarly to Oracle’s large pool, 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 contains the output from this script.

In 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 shows the output.

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 shows the output.

In this listing, 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 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.