Oracle9i PGA Memory Allocation for Dedicated Connections

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle PGA Memory Allocation for
Dedicated Connections

Oracle Tips by Burleson

When a dedicated connection is made to Oracle, an isolated memory region called the Program Global Area (PGA) 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 9-21.

Figure 9-75: Deferred UNIX RAM memory allocation for dedicated Oracle connections

Here you 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 Oracle

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. Oracle now has the option of running automatic PGA memory management. Oracle has introduced a new Oracle parameter called pga_aggregate_target. When the pga_aggregate_target parameter is set and you are using dedicated Oracle connections, Oracle will ignore all of the PGA parameters in the Oracle 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 percent overhead for other UNIX tasks) on the UNIX server after the instance has been started (see Figure 9-22).

Figure 9-76: 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. Oracle 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 Oracle.

Oracle 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 Oracle Views for Automatic PGA RAM Memory Management

Oracle has introduced several new views and new columns in existing views to aid in viewing the internal allocation of RAM memory in Oracle. The following new v$ views can be used to monitor RAM memory usage of dedicated Oracle 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 tool for the performance tuning processional who must locate suboptimal SQL statements.
v$workarea This new view provides detailed cumulative statistics on RAM memory usage for Oracle connections.
v$workarea_active This new view shows internal RAM memory usage information for all currently executing SQL statements.

Let’s take a closer look at these new Oracle features and scripts that allow you to see detailed RAM memory usage.

Using the Oracle v$sysstat View

The following query gives the total number and the percentage of time 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 percent.

Using the Oracle 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 Oracle 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 preceding display from v$pgastat, you see the following statistics:

Aggregate PGA auto target This column gives the total amount of available memory for Oracle connections. As we have already noted, this value is derived from the value on the Oracle parameter pga_aggregate_target.
Global memory bound This statistic measures the maximum size of a work area, and Oracle recommends that whenever this statistic drops below 1 megabyte, you should increase the value of the pga_aggregate_target parameter.
Total PGA allocated This statistic displays 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 are allowed by Oracle to use the automatic memory feature. For example, Java and PL/SQL will allocate RAM memory, and this will not be counted in this total PGA statistic. Hence, you 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 Oracle experienced a memory shortage, the DBA will invoke the multi-pass operation to attempt to locate recently freed RAM memory. This statistic is critical for monitoring RAM consumption in Oracle, and most Oracle DBAs will increase pga_aggregate_target to this value.

Enhancements to the v$process View in Oracle

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 you see allocated, used, and maximum memory for all connections to Oracle. You can see the RAM demands of each of the background processes, and you 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 Oracle

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 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 in the past has used up to 6.5 megabytes.

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

Viewing RAM Memory Usage for Specific SQL Statements

Oracle now has the ability to display RAM memory usage along with execution plan information. To get this information, you need to gather the address of the desired SQL statement from the v$sql view. For example, if you have a query that operates against the NEW_CUSTOMER table, you can run the following query to get the address:

select
   address
from
   v$sql
where
   sql_text like ‘%NEW_CUSTOMER’;

88BB460C

1 row selected.

Now that you have the address, you can plug it into the following script to get the execution plan details and the PGA memory usage for the SQL statement:

plan_mem.sql

select
   operation,
   options,
   object_name                        name,
   trunc(bytes/1024/1024)             "input(MB)",
   trunc(last_memory_used/1024)       last_mem,
   trunc(estimated_optimal_size/1024) opt_mem,
   trunc(estimated_onepass_size/1024) onepass_mem,
   decode(optimal_executions, null, null,
          optimal_executions||'/'||onepass_executions||'/'||
          multipasses_exections)      "O/1/M"
from
   v$sql_plan     p,
   v$sql_workarea w
where
   p.address=w.address(+)
and
   p.hash_value=w.hash_value(+)
and
   p.id=w.operation_id(+)
and
   p.address='88BB460C';

Here is the listing from this script. In addition to the execution plan, you also see details about RAM memory consumption for the hash join:

OPERATION    OPTIONS NAME input(MB) LAST_MEM OPT_MEM ONEPASS_MEM O/1/M
------------ -------- ---- --------- -------- ---------- ---------- ----
SELECT STATE
SORT         GROUP BY           4582       8         16         16 26/0/0
HASH JOIN    SEMI               4582     5976       5194       2187 16/0/0
TABLE ACCESS FULL     ORDERS      51
TABLE ACCESS FUL      LINEITEM 1000

Here you see the details about the execution plan along with specific memory usage details. This is an exciting new advance in Oracle and gives the Oracle DBA the ability to have a very high level of detail about the internal execution of any SQL statement.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.