Oracle UNIX PGA Memory Allocation for Dedicated Connection Administration Oracle UNIX/Linux Tips by Burleson Consulting

Oracle9i PGA memory allocation for dedicated connections

When a dedicated connection is made to Oracle, an isolated memory region called the program Global Area is allocated in UNIX RAM memory.  The PGA consists of the following components:

* Sort area ? This is the largest and most important area of the PGA

* Session information ? This small area contains internal address for the connection to allow the connection to communicate with Oracle.

* Cursor state ? This component of the PGA contains all reentrant values for the executing connection.

* Stack space ? This area contains miscellaneous control structures.

The largest component of a PGA is the sort area size, and Oracle allows you to dynamically change the sort area size at the session level.

alter session set sort_area_size=10m deferred;

When you issue this alter session command, you instruct UNIX to expand the sort area within the PGA at the time that the sort is required.  To illustrate the deferred RAM memory allocation in UNIX, consider the diagram in Figure 7-4.

Figure 4: Deferred UNIX RAM memory allocation for dedicated Oracle connections

Here we see that Oracle interfaces with UNIX to issue the malloc() command to provide a RAM sort area.  This RAM region is only allocated after the retrieval from the database has been completed, and the memory only exists for the duration that the sort is required.  This technique reduces the RAM memory demands on the UNIX server and ensures that the RAM is only available when it is needed by Oracle.

Automatic RAM memory management in Oracle9i

As we have noted, a serious problem in Oracle8i was the requirement that all dedicated connections use a one-size-fits-all sort_area_size. Oracle9i now has the option of running automatic PGA memory management. Oracle has introduced a new init.ora parameter called pga_aggregate_target. When the pga_aggregate_target parameter is set and you are using dedicated Oracle connections, Oracle9i will ignore all of the PGA parameters in the init.ora file, including sort_area_size and sort_area_retained_size.  Oracle recommends that the value of pga_aggregate_target be set to the amount of remaining memory (less a 20% overhead for other UNIX tasks) on the UNIX server after the instance has been started (Figure 7-5).

Figure 5: Allocating the pga_aggregate_target for a UNIX server

Once the pga_aggregate_target has been set, Oracle will automatically manage PGA memory allocation, based upon the individual needs of each Oracle connection.  Oracle9i also allows the pga_aggregate_target parameter to be modified at the instance level with the alter system command, thereby allowing the DBA to dynamically adjust the total RAM region available to Oracle9i.

Oracle9i also introduces a new parameter called workarea_size_policy.  When this parameter is set to automatic, all Oracle connections will benefit from the shared PGA memory.  When workarea_size_policy is set to manual, connections will allocate memory according to the values for the sort_area_size parameter. Under this automatic mode, Oracle tries to maximize the number of work areas that are using optimal memory and uses one-pass memory for the others.

New Oracle9i views for automatic PGA RAM memory management

Oracle9i has introduced several new views and new columns in existing views to aid in viewing the internal allocation of RAM memory in Oracle9i. The following new v$ views can be used to monitor RAM memory usage of dedicated Oracle9i connections.

* v$process ? Three new columns are added in Oracle 9i for monitoring PGA memory usage.  The new columns are called pga_used_mem, pga_alloc_mem and pga_max_mem.

* v$sysstat ? There are many new statistics rows, including work area statistics for optimal, one-pass and multi-pass.

* v$pgastat ? This new view shows internals of PGA memory usage for all background processes and dedicated connections.

* v$sql_plan ? This exciting new view contains execution plan information for all currently executing SQL.  This is a tremendous tolls for the performance tuning processional who must locate sub-optimal SQL statements.

* v$workarea ? This new view provides detailed cumulative statistics on Ram memory usage for Oracle9i connections.

* v$workarea_active ? This new view show internal RAM memory usage information for all currently executing SQL statements.

Let?s take a closer look at these new Oracle9i features and scripts that allow us to see detailed RAM memory usage.

Using the Oracle9i v$sysstat view

The following query gives the total number and the percentage of times work areas were executed in these three modes since the database instance was started.

work_area.sql
select
   name                                      profile,
   cnt,
   decode(total, 0, 0, round(cnt*100/total)) percentage
from
   (
      select
         name,
         value cnt,
         (sum(value) over ()) total
      from
         v$sysstat
      where
         name like 'workarea exec%'
   );

The output of this query might look like the following:

PROFILE                             CNT        PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal             5395         95
workarea executions - onepass              284          5
workarea executions - multipass              0          0

This output of this query is used to tell the DBA when to dynamically adjust pga_aggregate_target.  In general the value of pga_aggregate_target should be increased when multi-pass executions is greater than zero, and reduced whenever the optimal executions is 100%

Using the Oracle9i v$pgastat view

The v$pgastat view provides instance level summary statistics on the PGA usage and the automatic memory manager. The following script provides excellent overall usage statistics for all Oracle9i connections.

check_pga.sql
column name  format a30
column value format 999,999,999

select
   name,
   value
from
   v$pgastat
;

The output of this query might look like the following:

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

In the above display from v$pgastat we see the following statistics.

* Aggregate PGA auto target ? This column gives the total amount of available memory for Oracle9i connections.  As we have already noted, this value is derived from the value on the init.ora parameter pga_aggregate_target.

* Global memory bound ? This statistic measures the max size of a work area, and Oracle recommends that whenever this statistics drops below one megabyte, then you should increase the value of the pga_aggregate_target parameter.

* Total PGA allocated ? This statistic display the high-water mark of all PGA memory usage on the database.  You should see this value approach the value of pga_aggregate_target as usage increases.

* Total PGA used for auto workareas ? This statistic monitors RAM consumption or all connections that are running in automatic memory mode.  Remember, not all internal processes may use the automatic memory feature.  For example, Java and PL/SQL will allocate RAM memory, and this will not be counted in this statistic.  Hence, we can subtract value to the total PGA allocated to see the amount of memory used by connections and the RAM memory consumed by Java and PL/SQL.

* Estimated PGA memory for optimal/one-pass ? This statistic estimates how much memory is required to execute all task connections RAM demands in optimal mode.  Remember, when Oracle9i experienced a memory shortage, he will invoke the multi-pass operation.  This statistics is critical for monitoring RAM consumption in Oracle9i, and most Oracle DBA?s will increase pga_aggregate_target to this value.

Enhancements to the v$process view in Oracle9i

The v$process view has been enhanced with several new columns to show automatic PGA usage, including pga_used_mem, pga_alloc_mem and pga_max_mem.  Here is a query to display these values.

select
   program,
   pga_used_mem,
   pga_alloc_mem,
   pga_max_mem
from
   v$process;

The output of this query might look like the following:

PROGRAM                        PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------------ ------------ ------------- -----------
PSEUDO                                    0             0           0
oracle@janet (PMON)                  120463        234291      234291
oracle@janet (DBW0)                 1307179       1817295     1817295
oracle@janet (LGWR)                 4343655       4849203     4849203
oracle@janet (CKPT)                  194999        332583      332583
oracle@janet (SMON)                  179923        775311      775323
oracle@janet (RECO)                  129719        242803      242803
oracle@janet (TNS V1-V3)            1400543       1540627     1540915
oracle@janet (P000)                  299599        373791      635959
oracle@janet (P001)                  299599        373791      636007
oracle@janet (TNS V1-V3)            1400543       1540627     1540915
oracle@janet (TNS V1-V3)              22341       1716253     3625241

Here we see allocated, used and maximum memory for all connections to Oracle.  We can see the RAM demands of each of the background processes and we also have detailed information about individual connections.

Note that it is possible to join the v$process view with the v$sql_plan table to take a closer look at the RAM memory demands of specific connections.

Using the v$workarea views in Oracle9i

Oracle also has two new views to show active work area space, the v$sql_workarea and the v$sql_workarea_active views. The v$sql_workarea_active view will display all of the work areas that are currently executing in the instance. Note that small sorts (under 65,535 bytes) are excluded from the view, but you can use the v$sql_workarea_active view to quickly monitor the size of all large active work areas.

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;

Here is a sample listing from this script.

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

This output above shows that session 44 is running a hash-join whose work area is running in one-pass mode. This work area is currently using 2 megabytes of PGA memory and has used in the past up to 6.5 megabytes of PGA memory.

This view is very useful for viewing the current memory operations within Oracle, and you can use the SID column to join into the v$process and v$session views for additional information about each task.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.