Navigate

Search

Oracle PGA (Program Global Area) in 12c

What is PGA

The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. An example of such information is the run-time area of a cursor. Each time a cursor is executed, a new run-time area is created for that cursor in the PGA memory region of the server process executing that cursor.

For complex queries (such as decision support queries), a big portion of the run-time area is dedicated to work areas allocated by memory intensive operators, including:

  • Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions
  • Hash-join
  • Bitmap merge
  • Bitmap create
  • Write buffers used by bulk load operations

A sort operator uses a work area (the sort area) to perform the in-memory sorting of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.

 

Work Area Sizes

Oracle Database enables you to control and tune the sizes of work areas. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. The available work area sizes include:

  • OptimalOptimal size is when the size of a work area is large enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is the ideal size for the work area.
  • One-passOne-pass size is when the size of the work area is below optimal size and an extra pass is performed over part of the input data. With one-pass size, the response time is increased.
  • Multi-passMulti-pass size is when the size of the work area is below the one-pass threshold and multiple passes over the input data are needed. With multi-pass size, the response time is dramatically increased because the size of the work area is too small compared to the input data size.

For example, a serial sort operation that must sort 10 GB of data requires a little more than 10 GB to run as optimal size and at least 40 MB to run as one-pass size. If the work area is less than 40 MB, then the sort operation must perform several passes over the input data.

When sizing the work area, the goal is to have most work areas running with optimal size (more than 90%, or even 100% for pure OLTP systems), and only a small number of them running with one-pass size (less than 10%). Multi-pass executions should be avoided for the following reasons:

  • Multi-pass executions can severely degrade performance.A high number of multi-pass work areas has an exponentially adverse effect on the response time of its associated SQL operator.
  • Running one-pass executions does not require a large amount of memory.Only 22 MB is required to sort 1 GB of data in one-pass size.

Even for DSS systems running large sorts and hash-joins, the memory requirement for one-pass executions is relatively small. A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data.

Setting the Initial Value for PGA_AGGREGATE_TARGET

Set the initial value of the PGA_AGGREGATE_TARGET initialization parameter based on the amount of available memory for the Oracle database instance. This value can then be tuned and dynamically modified at the instance level. By default, Oracle Database uses 20% of the SGA size for this value. However, this setting may be too low for a large DSS system.

To set the initial value for PGA_AGGREGATE_TARGET:

  1. Determine how much of the total physical memory to reserve for the operating system and other non-Oracle applications running on the same system.For example, you might decide to reserve 20% of the total physical memory for the operating system and other non-Oracle applications, dedicating 80% of the memory on the system to the Oracle database instance.
  2. Divide the remaining available memory between the SGA and the PGA:
    • For OLTP systems, the PGA memory typically makes up a small fraction of the available memory, leaving most of the remaining memory for the SGA.Oracle recommends initially dedicating 20% of the available memory to the PGA, and 80% to the SGA. Therefore, the initial value of the PGA_AGGREGATE_TARGET parameter for an OLTP system can be calculated as:PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.2 where total_mem is the total amount of physical memory available on the system.
    • For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of the available memory.Oracle recommends initially dedicating 50% of the available memory to the PGA, and 50% to the SGA. Therefore, the initial value of the PGA_AGGREGATE_TARGET parameter for a DSS system can be calculated as:PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.5 where total_mem is the total amount of physical memory available on the system.

For example, if an Oracle database instance is configured to run on a system with 4 GB of physical memory, and if 80% (or 3.2 GB) of the memory is dedicated to the Oracle database instance, then initially set PGA_AGGREGATE_TARGET to 640 MB for an OLTP system, or 1,600 MB for a DSS system.

About PGA_AGGREGATE_LIMIT

The PGA_AGGREGATE_LIMIT initialization parameter enables you to specify a hard limit on PGA memory usage. If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:

  • Calls for sessions that are consuming the most untunable PGA memory are aborted.
  • If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.

In determining the sessions and processes to abort or terminate, Oracle Database treats parallel queries as a single unit.

By default, the PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the PROCESSES parameter. However, it will not exceed 120% of the physical memory size minus the total SGA size. The default value is printed into the alert log. A warning message is printed in the alert log if the amount of physical memory on the system cannot be determined.

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

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

PGA Overallocation vs. tunable and non-tunable areas

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.

We can control the number of cursors by the init.ora OPEN_CURSORS parameter, but if this is exceeded we get an ORA-1000 error, which is clearly undesirable. See unpublished Note:1012266.6 – “Overview of ORA-1000 Maximum Number of Cursors Exceeded” for more info. More importantly, however, we have no control over the size of a cursor, and users may open very large cursors dependent on their SQL or PLSQL.
 
 Also note that if we set PGA_AGGREGATE_TARGET too small to accommodate the non-tunable part of the PGA plus the minimum memory required to execute the tunable part, then Oracle cannot honour the PGA_AGGREGATE_TARGET value, and will attempt to allocate extra memory. This is known as overallocation, and an estimation of this can be seen in the view V$PGA_TARGET_ADVICE under the column ESTD_OVERALLOC_COUNT.

As PGA memory is divided as tunable and non-tunable areas, while tunable is constrained under PGA_AGGREGATE_TARGET, non-tunable can be over allocated in any size (till ORA-04030), and Oracle records these activities in column ESTD_OVERALLOC_COUNT of V$PGA_TARGET_ADVICE. Therefore ESTD_OVERALLOC_COUNT is caused by over request of non-tunable areas.

Before each overallocation, probably Oracle tries to deallocate certain less used memory (for example, LRU Algorithm) at first. If not satisfied, new memory is allocated.

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. Under overallocation, session cursor caches could be subject to memory deallocation.

Test Case

Test case 1:-

First we will create a table with 3 GB size.My pga_aggregate_limit  =2G and pga_aggregate_target =1G.We will sort the table to check the behavior of PGA.

Initially before running the query let me capture the statistics.

col NAME for a40;
SELECT NAME, VALUE, UNIT FROM V$PGASTAT
where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PGA inuse ‘,
‘total PGA allocated’, ‘maximum PGA used for auto workareas’, ‘cache hit percentage’, ‘over allocation count’);
  4
NAME                                          VALUE UNIT
—————————————- ———- ————
aggregate PGA target parameter           1073741824 bytes
aggregate PGA auto target                 821412864 bytes
total PGA allocated                       216955904 bytes
maximum PGA used for auto workareas               0 bytes
over allocation count                             0
cache hit percentage                            100 percent
6 rows selected.
SQL> show parameter pga;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 1G
SQL> select bytes/1024/1024/1024 from dba_segments where segment_name=’T1′;
BYTES/1024/1024/1024
——————–
         3.31640625
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%’);  2    3    4    5
PROFILE                                                                 CNT
—————————————————————- ———-
PERCENTAGE
———-
workarea executions – optimal                                          6002
      100
workarea executions – onepass                                             0
        0
workarea executions – multipass                                           0
        0
SQL>exec dbms_workload_repository.create_snapshot;
SQL> 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’);SQL> SQL>   2    3    4    5    6
NAME                                  VALUE_MB
———————————– ———-
aggregate PGA target parameter            1024
total PGA inuse                     144.376953
total PGA allocated                 196.063477
over allocation count                        0

Now I will run the query in another session

SQL> set autotrace traceonly;
SQL> select * from t1 order by c1;
2999999 rows selected.

In the same time I will check what happens in WORK AREA using the following query:

col sid for 99999
col operation for a20
col start_time for a20
col expected_size_MB for 99,999.9
col Act_Used_MB for 99,999.9
col Max_Used_MB for 99,999.9
col TEMP_Seg_MB for 999,999
select to_char(sql_exec_start, ‘yyyy-mm-dd hh24:mi:ss’) start_time, sql_id,
      to_number(decode(sid, 65535, NULL, sid)) sid,
      operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,
      trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,
      number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB
FROM v$sql_workarea_active
ORDER BY 1,2;
START_TIME           SQL_ID           SID OPERATION            EXPECTED_SIZE_MB ACT_USED_MB MAX_USED_MB       PASS TEMP_SEG_MB
——————– ————- —— ——————– —————- ———– ———– ———- ———–
2018-02-08 07:37:42  0k6yvhdy619tc    357 SORT (v2)                        14.0        14.0        87.0          1       2,955

Let me capture the statistics again.

SQL> col NAME for a40;

SQL> SELECT NAME, VALUE, UNIT FROM V$PGASTAT
where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PG  2  A inuse ‘,
 ‘total PGA allocated’, ‘maximum PGA used for auto workareas’, ‘cache hit percentage’, ‘over allocation count’);
NAME                                          VALUE UNIT
—————————————- ———- ————
aggregate PGA target parameter           1073741824 bytes
aggregate PGA auto target                 830739456 bytes
total PGA allocated                       203883520 bytes
maximum PGA used for auto workareas       107125760 bytes
over allocation count                             0
cache hit percentage                          51.45 percent
6 rows selected.
SQL> 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’);SQL> SQL>   2    3    4    5    6
NAME                                  VALUE_MB
———————————– ———-
aggregate PGA target parameter            1024
total PGA inuse                     143.730469
total PGA allocated                 194.438477
over allocation count                        0
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%’);  2    3    4    5
PROFILE                                                                 CNT
—————————————————————- ———-
PERCENTAGE
———-
workarea executions – optimal                                          6484
      100
workarea executions – onepass                                             2
        0
workarea executions – multipass                                           0
        0

 Test case 2:-Let me run the sort again.

SQL>  set autotrace traceonly;
SQL> select * from t1 order by c1;
2999999 rows selected.

Let me capture the statistics again

 SQL> 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’);SQL> SQL>   2    3    4    5    6
NAME                                  VALUE_MB
———————————– ———-
aggregate PGA target parameter            1024
total PGA inuse                     153.366211
total PGA allocated                 209.775391
over allocation count                        0
SQL> SELECT NAME, VALUE, UNIT FROM V$PGASTAT
where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PG  2  A inuse ‘,
 ‘total PGA allocated’, ‘maximum PGA used for  3   auto workareas’, ‘cache hit percentage’, ‘over allocation count’);
NAME                                     VALUE UNIT
———————————– ———- ————
aggregate PGA target parameter      1073741824 bytes
aggregate PGA auto target            823560192 bytes
total PGA allocated                  216297472 bytes
maximum PGA used for auto workareas  107125760 bytes
over allocation count                        0
cache hit percentage                     50.93 percent
6 rows selected.
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%’);  2    3    4    5
PROFILE                                                                 CNT
—————————————————————- ———-
PERCENTAGE
———-
workarea executions – optimal                                          7095
      100
workarea executions – onepass                                             4
        0
workarea executions – multipass                                           0
        0

AWR statistics related to PGA

 

 

Test Case 3:-

Let me increase the PGA_AGGREGATE_TARGET to 10GB.

SQL> set autotrace traceonly;

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from t1 order by c1;

2999999 rows selected.

In the same time I will check what happens in WORK AREA using the following query:

SQL> col sid for 99999
SQL> col operation for a20
col start_time for a20
SQL> SQL> col expected_size_MB for 99,999.9
col Act_Used_MB for 99,999.9
SQL> SQL> col Max_Used_MB for 99,999.9
col TEMP_Seg_MB for 999,999
SQL> SQL> select to_char(sql_exec_start, ‘yyyy-mm-dd hh24:mi:ss’) start_time, sql_id,
      to_number(decode(sid, 65535, NULL, sid)) sid,
 2    3         operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,
      trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,
 4    5         number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB
FROM v$sql_workarea_active
 6    7  ORDER BY 1,2;
START_TIME           SQL_ID           SID OPERATION            EXPECTED_SIZE_MB ACT_USED_MB MAX_USED_MB       PASS TEMP_SEG_MB
——————– ————- —— ——————– —————- ———– ———– ———- ———–
2018-02-08 07:58:23  0k6yvhdy619tc    357 SORT (v2)                         1.0          .0       870.0          1       2,955
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           10240
total PGA inuse                     1081.51172
total PGA allocated                 1245.37207
over allocation count                        0

Let me capture statistics again after query is executed.

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%’);   2    3    4    5
PROFILE                                                                 CNT PERCENTAGE
—————————————————————- ———- ———-
workarea executions – optimal                                          7711        100
workarea executions – onepass                                             8          0
workarea executions – multipass                                           0          0
SQL> SELECT NAME, VALUE, UNIT FROM V$PGASTAT
 where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PG  2  A inuse ‘,
 ‘total PGA allocated’, ‘maximum PGA used for  3   auto workareas’, ‘cache hit percentage’, ‘over allocation count’);  3
NAME                                     VALUE UNIT
———————————– ———- ————
aggregate PGA target parameter      1.0737E+10 bytes
aggregate PGA auto target           9517178880 bytes
total PGA allocated                  218789888 bytes
over allocation count                        0
cache hit percentage                     50.57 percent

AWR report related to PGA:-

Test case 4:-

Now we will test about Non-tunable component like PL/SQL area and we will see effect of  PGA_AGGREGATE_LIMIT.

Let me set the PGA parameters to less value

SQL> show parameter pga;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
pga_aggregate_limit                  big integer 20G
pga_aggregate_target                 big integer 10G
SQL> alter system set pga_aggregate_target=1G;
System altered.
SQL> alter system set pga_aggregate_limit=2G;
System altered.

Now create PL/SQL table and execute it

SQL> create or replace package demo_pkg    As
type array is table of char(2000) index by binary_integer;
g_data array;
end;
begin
for i in 1 .. 200000
loop
demo_pkg.g_data(i) := ‘x’;
end loop;
end;
/

Let me capture the statistics

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%’);   2    3    4    5
PROFILE                                                                 CNT
—————————————————————- ———-
PERCENTAGE
———-
workarea executions – optimal                                          7954
      100
workarea executions – onepass                                             8
        0
workarea executions – multipass                                           0
        0
SQL> 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’);  2    3    4    5    6
NAME                                                               VALUE_MB
—————————————————————- ———-
aggregate PGA target parameter                                         1024
total PGA inuse                                                   604.05957
total PGA allocated                                              656.980469
over allocation count                                                     0

Now I will increase 2000000 to simulate error .This will cause PL/SQL table to allocate more memory in PGA.

SQL> begin
for i in 1 .. 2000000
loop
demo_pkg.g_data(i) := ‘x’;
end loop;
end;
/  2    3    4    5    6    7
begin
*
ERROR at line 1:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Let me capture statistics after failure

SQL> 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’);  2    3    4    5    6
NAME                                                               VALUE_MB
—————————————————————- ———-
aggregate PGA target parameter                                         1024
total PGA inuse                                                  593.813477
total PGA allocated                                              627.732422
over allocation count                                            .000016212
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%’);  2    3    4    5
PROFILE                                                                 CNT
—————————————————————- ———-
PERCENTAGE
———-
workarea executions – optimal                                          8067
      100
workarea executions – onepass                                             8
        0
workarea executions – multipass                                           0
        0

Test case 5:

Now I will increase pga_aggregate_limit to 10G and check the behavior.The PL/SQL table will run fine now.

SQL> alter system set pga_aggregate_limit=10G;
System altered.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> begin
for i in 1 .. 2000000
loop
demo_pkg.g_data(i) := ‘x’;
end loop;
end;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.
SQL>  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’);  2    3    4    5    6
NAME                                                               VALUE_MB
—————————————————————- ———-
aggregate PGA target parameter                                         1024
total PGA inuse                                                  4607.88965
total PGA allocated                                              4641.04688
over allocation count                                            .000023842

AWR report status:-

Reference:-

//docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA95344

//oracleinaction.com/tune-pga-i/

PGA tuning
debasis maity: 12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA
Related Post
  1. Monitor real time active session statistics by ORAPUB tool

    The tool was devised by Craig from ORAPUB which is excellent to monitor what happens…

  2. Cache buffer chain CBC latch simple query to investigate

    For deep understanding on CBC,please refer to below links:- //www.proligence.com/pres/ioug14/2014_431_Nanda_ppr.pdf and Video tutorial from ORAPUB.…

  3. Performance monitoring or tuning tool for SQL query taking long time using various tools like explain plan,sql monitor,awrsqrpt,sqlhc

    Performance monitoring or tuning tool for SQL query taking long time using various tools  …

DEBASIS MAITY

Recent Comments

    Recent Comments

      Blog Stats

      • 267,526 hits