TUNING PGA : PART – I

 In this article, I will discuss about tuning of PGA. First, we will discuss the scenario when PGA was tuned manually , find out what were the problems and how the problems were resolved using automatic tuning of PGA.

Program Global Area or PGA as it is popularly called contains data and control information about a server process. There are two components of PGA – Stack space and User Global Area (UGA)

- STACK SPACE : It holds bind variables, arrays (PL/SQL) etc .A bind variable value is supplied to a SQL statement at run time when the statement is executed

- UGA         : It contains

  • Session information such as logon information, and other information required by a database Session
  • SQL Work areas : used for sorting, hash operations etc.
  • Private SQL Area : contains Open/Closed cursors and cursor state information for open cursors  for example,  the number of rows retrieved so far in a full table scan.

  For complex queries, workareas in UGA are required to perform memory intensive operations.  For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

Memory intensive operations which make use of PGA are

- Sort based operators (Order by, group by, rollup, Distinct etc.)

- Hash join

- Bitmap related operations

- Write buffers used by bulk load operations (Direct Path Load)

                              HOW DOES SIZE OF PGA AFFECT APPLICATION’S PERFORMANCE

If amount of data to be processed fits the work area i.e. size of work area is optimal, all the operations are performed in memory.

If memory required by the amount of data to be processed is more than the available workarea, the input is divided into smaller pieces. Then, some pieces pf data are processed in memory while the rest are spilled to temporary tablespace.

When one extra pass is performed on all or some of the input data, the corresponding size of the available work area is called one-pass size i.e. if the data having size equal to one-pass size is sorted with currently available PGA workarea, an extra pass on the data will have to be made.

When the available work area size is even less than one pass threshold, multiple passes over the imput data are needed causing dramatic increase in response time. This is referred to as multipass size of the workarea.

In an OLTP system, size of input data is small and hence they mostly run in optimal mode.

In DSS  systems, where input data is very large , it is important to size the workarea for good performance as in-memory operations are m uch faster than temporary disk operations. Generally, bigger workareas can significantly improve the performance of an operation at the cost of higher memory consumption.

Optimally, the size of workarea should be enough to accommodate the input data.

                        MANUAL PGA MEMORY MANAGEMENT

Earlier releases required DBA to manually specify the maximum workarea size for each type of SQL operator.

PGA has two components : Tunable and untunable

- Untunable PGA : consists of

  • Context information of each session
  • Each open cursor
  • PL/SQL, OLAP or Java memory

This component of PGA can’t be tuned i.e. whatever is memory is needed it will be consumed else the operation fails.

- Tunable PGA : consists of memory available to SQL work areas (used by various sort operations)

  •  approx. 90% of PGA in DSS systems
  •  approx. 10% of PGA in OLTP systems

This component is tunable in the sense that memory available and hence consumed may be less than what is needed  and the operation will still complete but may spill to disk. Hence, increasing available memory may improve performance of such operations.

                   MANUAL PGA MEMORY MANAGEMENT

In manual management, DBA manually specifies the maximum work area size for each type of SQL operator (Sort, hash ,etc.) i.e. when WORKAREA_SIZE_POLICY = Manual

SORT_AREA_SIZE, HASH_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, BITMAP_MERGE_AREA_SIZE parameters decided the size of memory available for each of these operations per server process.

e.g. SORT_AREA_SIZE = Memory available for sorting to each server process (each user in dedicated server connection)

Let SORT_AREA_SIZE = 1M

As no. of users performing sort operations increase, the total PGA memory for the instance increases linearly as n * 1 M where n = no. of sessions

Implication :

- Each user session will use 1M of PGA for sorting irrespective of the size of the data.

If size of the data is > 1M, his sorts will spill to the disk even though we might be having PGA memory available to the instance i.e. a session won’t be able to use the available memory because the memory allocated to his session has been fixed by SORT_AREA_SIZE.

IF SIZE OF THE DATA IS << 1M, the sorting will take place completely in memory but will still consume 1M of memory which is more than what is needed. The extra memory can’t be transferred to another session needing it.

- If SORT_AREA_SIZE is small, sorts spill to disk and

If SORT_AREA_SIZE is made large and no. of sessions performing sorts is so large that the total memory required by them (n * 1M) is more than the available memory ( Total memory available to oracle instance – SGA ), paging and swapping will take place to satisfy that memory requirement resulting in heavily degraded performance.

Hence, DBA had to continuously monitor the user workload and decide on the appropriate value of SORT_AREA_SIZE such that neither the sorts spilled to disk nor swapping/paging took place.

                   AUTOMATIC PGA MEMORY MANAGEMENT

Automatic PGA memory management resolved above mentioned issues by allowing DBA to allocate an aggregate PGA to all the server processes for all the SQL operations which could be distributed as per the requirement. In this case, Oracle dynamically adapts the SQL memory allocation based on

  • - PGA memory available
  • - SQL operator needs
  • - System workload

With automatic PGA memory management, sizing of SQL work areas for all dedicated server sessions is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.

  To implement it, two parameters need to be set.

WORKAREA_SIZE_POLICY = AUTO

PGA_AGGREGATE_TARGET = Target size of PGA for the total instance.

Given the specified target PGA, Oracle itself tunes the size of individual PGA’s depending upon the workload.

IMPLICATIONS:

- As the workload changes, memory available to each session changes dynamically while keeping the sum of all PGA allocations under the threshold PGA_AGGREGATE_TARGET.

e.g.

If PGA_AGGREGATE_TARGET = 10G,

let Tunable PGA = 5G

If one session is performing sort needing 5G workarea,

he will get workarea = 5G

If two sessions performing the same sort,

They will get 5/2 = 2.5 G each

and so on..

i.e. Sort area for each session is not fixed. Rather it changes dynamically.

- PGA_AGGREGATE_TARGET is a goal of an upper limit. It is not a value that is allocated when instance is started i.e. memory will be allocated only when there is a sorting operation which needs it. Else, it will be returned to the Operating System or transferred to SGA if AMM is enabled.

– HOW IS MEMORY ALLOCATED TO PGA – PRACTICAL IMPLEMENTATION

OVERVIEW

- Setup

- Disable AMM

- Create  3 test tables

. hr.small(9M),

. scott.medium(17M),

. sh.large (33M)

- Set workarea_size_policy = AUTO

- Check current value of  PGA_AGGREGATE_TARGET

- Set PGA_AGGREGATE_TARGET to current value

- To check that PGA memory allocated to the instance can exceed even the specified

PGA_AGGREGATE_TARGET if fixed PGA requirement is more

- Check  current allocation/PGA used/overallocation count

- Create a  PL/SQL array requiring large fixed PGA

- Check that  PGA allocated > PGA_AGGREGATE_TARGET

PGA allocated > PGA in use

over allocation count increased

- To check that overallocation count increases if \fixed memory requirement of SQL work area is not satisfied by the extra PGA memory allocated to the instance  (PGA allocated > PGA in use)

- Execute queries on 3 test tables

- Check that overallocation count increases as specific requirement of the SQL workareas is not met

by the extra memory allocated in PGA.

—————— IMPLEMENTATION ———————–

- SETUP

- Check if AMM enabled

SQL>CONN / AS SYSDBA

sho parameter memory_target

  – Disable AMM

ALTER SYSTEM SET MEMORY_TARGET = 0;

  – Create 3 test tables

. hr.small(9M),

. scott.medium(17M),

. sh.large (33M)

SQL>ALTER USER SCOTT IDENTIFIED BY tiger account unlock;

ALTER USER Sh IDENTIFIED BY sh account unlock;

GRANT SELECT_CATALOG_ROLE TO HR, SCOTT, SH;

GRANT EXECUTE ON DBMS_STATS TO HR, SCOTT, SH;

CONN HR/hr

drop table hr.small purge;

CREATE TABLE SMALL AS SELECT * FROM dba_OBJECTS;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘SMALL’);

col segment_name for a30

SELECT SEGMENT_NAME, BYTES/1024/1024 MB

FROM   USER_SEGMENTS

WHERE  SEGMENT_NAME = ‘SMALL’;

SEGMENT_NAME                           MB

—————————— ———-

SMALL                                   9

CONN SCOTT/tiger

drop table scott.medium purge;

CREATE TABLE MEDIUM AS SELECT * FROM dba_OBJECTS;

INSERT INTO MEDIUM SELECT * FROM MEDIUM;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘MEDIUM’);

col segment_name for a30

SELECT SEGMENT_NAME, BYTES/1024/1024 MB

FROM   USER_SEGMENTS

WHERE  SEGMENT_NAME = ‘MEDIUM’;

SEGMENT_NAME                           MB

—————————— ———-

MEDIUM                                 17

CONN Sh/sh

drop table sh.large purge;

CREATE TABLE LARGE AS SELECT * FROM dba_OBJECTS;

INSERT INTO LARGE SELECT * FROM LARGE;

INSERT INTO LARGE SELECT * FROM LARGE;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘LARGE’);

col segment_name for a30

SELECT SEGMENT_NAME, BYTES/1024/1024 MB

FROM   USER_SEGMENTS

WHERE  SEGMENT_NAME = ‘LARGE’;

SEGMENT_NAME                           MB

—————————— ———-

LARGE                                  33

 - Set WORKAREA_SIZE_POLICY = AUTO

SQL>CONN / AS SYSDBA

ALTER SYSTEM SET WORKAREA_SIZE_POLICY = AUTO;

- Check current value of PGA_AGGREGATE_TARGET

SHO PARAMETER PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE

———————————— ———–

pga_aggregate_target                 big integer 496M

– Note that current PGA allocated <  PGA_AGGREGATE_TARGET

as currently only the PGA required for fixed areas has been allocated

COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM   V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’, ‘total PGA allocated’);

NAME                                  VALUE_MB

———————————– ———-

aggregate PGA target parameter             496

total PGA allocated                 56.3554688

- Set PGA_AGGREGATE_TARGET to current value

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 496M;

———————

- To check that PGA memory allocated to the instance can exceed even the specified

PGA_AGGREGATE_TARGET if fixed PGA requirement is more

———————–

  – Check current allocation /  PGA used / overallocation count

- Note that

- PGA allocated (57M) > PGA in use (44M) i.e. some extra

memory is allocated

- Overallocation count  = 0 because currently

the fixed memory requirement  < PGA_AGGREGATE_TARGET (496M)

COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM   V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’);

NAME                                  VALUE_MB

———————————– ———-

aggregate PGA target parameter             496

total PGA inuse                     44.9199219

total PGA allocated                 57.2304688

over allocation count                        0

   – Create a  PL/SQL array requiring large fixed PGA

SQL>create or replace package demo_pkg    As

type array is table of char(2000) index by binary_integer;

g_data array;

end;

/

- Fill up the chararray (a CHAR datatype is blank-padded so each of     these array elements is exactly 2,000 characters in length):

SQL> begin

for i in 1 .. 200000

loop

demo_pkg.g_data(i) := ‘x’;

end loop;

end;

/

    – Check that  PGA allocated (509M)  > PGA_AGGREGATE_TARGET (496M)

                         PGA allocated (509M) > PGA in use (493M)

over allocation count increased (0 to 2)

COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM   V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’)

union

SELECT NAME, VALUE

FROM   V$PGASTAT

WHERE NAME IN (‘over allocation count’);

NAME                                  VALUE_MB

———————————– ———-

aggregate PGA target parameter             496

over allocation count                        2

total PGA allocated                 509.044922

total PGA inuse                     493.198242

- To check that overallocation count increases if fixed memory requirement of SQL work area is not satisfied by the extra PGA memory allocated to the instance  (PGA allocated > PGA in use)

- Execute queries on 3 test tables

- Check that overallocation count increases as specific requirement of the SQL workareas is not met

by the extra memory allocated in PGA.

HR>CONN HR/hr

set autotrace traceonly

select * from small order by 1,2,3,4,5,6;
SYS>COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM   V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’)

union

SELECT NAME, VALUE

FROM   V$PGASTAT

WHERE NAME IN (‘over allocation count’);

SCOTT>CONN scott/tiger

set autotrace traceonly

select * from medium order by 1,2,3,4,5,6;
SYS>COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM   V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’)

union

SELECT NAME, VALUE

FROM   V$PGASTAT

WHERE NAME IN (‘over allocation count’);

SH>CONN sh/sh

set autotrace traceonly

select * from large order by 1,2,3,4,5,6;

SYS>COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM   V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’)

union

SELECT NAME, VALUE

FROM   V$PGASTAT

WHERE NAME IN (‘over allocation count’);

————————————————————

— cleanup —

sql>conn / as sysdba

drop table hr.small purge;

drop table scott.medium purge;

drop table sh.large purge;

– enable AMM if it was initially enabled

SQL>ALTER SYSTEM SET MEMORY_TARGET= <inital value>;

In my next article (Tuning PGA Part-II)  in this series, I will discuss how to set an appropriate value for PGA_AGGREGATE_TARGET.

——————————————————————————————————

Related links:

Home

Tuning Index
Tuning PGA : Part – II
Tuning PGA : Part – III

                                                  ——————

Your comments and suggestions are welcome!