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. This memory is specific to a single server process and is not accessible by any other server process. Because the PGA is process-specific, it is never allocated in the SGA.

Various  components of PGA are as follows:

• Private SQL Area:  Holds information about a parsed SQL statement and other session-specific information for processing. A private SQL area is further  divided into the following areas:
  • Run time Area:  Contains query execution state information for example,  the number of rows retrieved so far in a full table scan.
  • Persistent area: Contains bind variable values.
• SQL Work Areas:  : Used for sorting, hash operations etc.
• User Global Area (UGA) : It essentially stores the session state.  It contains session information such as logon information, buffers read as a result of direct path reads and other information required by a database Session. The UGA must be available to a database session for the life of the session. In a dedicated server connection, since each session is associated with a dedicated server process having its own PGA, the UGA is stored in the PGA only. In a shared server connection, since a session can use any one of the shared servers, the UGA should be accessible to each one of the shared server processes and hence it cannot be stored in the PGA . Therefore,  when using shared server connections, the UGA is stored in the SGA .

  For complex queries, work areas in PGA 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.

References:

Expert Oracle Database Architecture by Thomas Kyte
Oracle database operating system memory allocation management for PGA
https://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT803
http://ksun-oracle.blogspot.in/2015/09/limit-pga-memory-usage.html

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

•  – Replacing literals with bind variables
•  – setting CURSOR_SHARING=SIMILAR

Overview

1. create a procedure to query sh.sales without using bind variables
2. set cursor_sharing to exact
3. Take begin snapshot for awr/statspack
4. run the procedure
5. Take end snapshot for awr/statspack
6. Generate awr/statspack report
7. Both reports indicate low soft parsing
8. Resolve the problem using

– set cursor_sharing to similar

OR

– set cursor_sharing to exact and use bind variables in the procedure

9. Run the procedure and generate awr/statspack report
10. Verify that soft parsing has increased considerably

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

Implementation

SQL>conn sh/sh

— create package test in which we are querying table sh.sales repeatedly (50000 times)  without using bind variables

sql>
CREATE OR REPLACE PACKAGE test AS
     PROCEDURE Workload;
     PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY test AS
PROCEDURE Workload IS
BEGIN
FOR i in 1 .. 50000
LOOP
Foo(i);
END LOOP;
END Workload;
PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
BEGIN
DECLARE
l_stmt VARCHAR2(2000);
BEGIN
l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = ' ||
TO_CHAR(CUSTID);
EXECUTE IMMEDIATE l_stmt;
END;
END Foo;
END;
/

— Create begin snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create begin snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();
    alter system set cursor_sharing = exact scope=memory;

— execute the workload —

sql>conn sh/sh
       exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat
      exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
       exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
    @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % is very low as cursor sharing is exact   and bind variables have not been used.

— To solve this issue, we can take two actions :

1.  Change cursor_sharing to similar
2.  Use bind variables in the procedure

-—————– Solution 1 —————————

       Change cursor_sharing to similar (if application code cannot be changed)

sql>conn / as sysdba
    alter system set cursor_sharing=similar scope=memory;

— create begin snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();

— execute the workload —

sql>conn sh/sh
    exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat

       exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
    @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % has increased substantially as cursor  sharing has been set to similar

—————— Solution 2 —————————

      . Use bind variables in the procedure

— Reset cursor_sharing to exact

sql>conn / as sysdba
    alter system set cursor_sharing=exact scope=memory;

— alter the Foo procedure to use bind variables :

CREATE OR REPLACE PACKAGE BODY test AS
      PROCEDURE Workload IS
       BEGIN
             FOR i in 1 .. 50000     LOOP
                 Foo(i);
             END LOOP;
         END Workload;

       PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
       BEGIN
           DECLARE
              l_stmt VARCHAR2(2000);
            BEGIN
                   l_stmt := ‘SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id';
                  EXECUTE IMMEDIATE l_stmt USING CUSTID;
            END;
         END Foo;
END;
/

— Create begin snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create begin snapshot for awr report —

sql>conn / as sysdba
 exec dbms_workload_repository.create_snapshot();
        alter system set cursor_sharing = exact scope=memory;

— execute the workload —

sql>conn sh/sh
      exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
       exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
      @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % has increased substantially as bind variables  have been used.
————————————————————————————————————————-