Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle9i provides enhanced views on RAM usage
October 18, 2002
Donald Burleson

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

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

Listing A below shows the script output.

Listing A
 ------------------------- ------------   -------------   -----------
 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

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


   name                                      c1,
   cnt                                       c2,
   decode(total, 0, 0, round(cnt*100/total)) c3
   select name,value cnt,(sum(value) over ()) total

   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:

   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
order by 1,2;

Listing D below shows the output.

Listing D
 --- --------------------- ----- --------- --------- --------- ----
 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.





Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational