| |
And sga_target different, pga_aggregate_target does not limit the size of the PGA. Another difference is that, SGA is the database startup according sga_max_size pre-assigned, and PGA is the "needs" of.
Following a detailed explanation of why pga_aggregate_target does not limit the size of the PGA. PGA general document will be divided into two parts of stack space and UGA, but the wording was too general and does not help us understand the problem. We put it into part of the work area and the work area outside.
The so-called work area, is the session to execute SQL, the database needs internal distribution, in order to store the intermediate results of memory. For example sort area, in order to sort by memory, such as hash area, in order to use memory hash join, this part directly related to the implementation of SQL, affect the efficiency of SQL execution, such as a larger hash area will hash join faster. pga_aggregate_target actually only limit the size of the work area. When the limit of pga_aggregate_target work area is reached, it will generate an error 4030. Implicit parameter _pga_max_size just limit the size of work area using a single process, and it is less confined within the limits of the pga_aggregate_target. The memory work area outside, it is not limited pga_aggregate_target and _pga_max_size. So you will often see the PGA size exceeds pga_aggregate_target. This is usually because PL / SQL variables and arrays in data loaded into a huge result. A common example is the bulk collect, imp / exp, sql loader and other tools also have similar problems.
Here I give a specific example:
alter system set "_pga_max_size" = 10m scope = both;
alter system set pga_aggregate_target = 100m scope = both;
SQL> shutdown immediate
startup
SQL> select ksppinm as "hidden parameter", ksppstvl as "value" from x $ ksppi join x $ ksppcv using (indx) where ksppinm like '% _pga_max_size%' order by ksppinm;
hidden parameter
-------------------------------------------------- ------------------------------
value
-------------------------------------------------- ------------------------------
_pga_max_size
10485760
<========= Restrict the use of a single process PGA 100M (in fact, work area)
Execute a PL / SQL, use bulk collect memory usage so that it greatly exceeds pga_aggregate_target and _pga_max_size:
DECLARE
CURSOR c1 IS
SELECT *
FROM Oracle.employees;
TYPE emp_row IS TABLE OF c1% ROWTYPE;
table_set emp_row;
BEGIN
- Assign values to nested table of records:
SELECT *
BULK COLLECT INTO table_set
FROM oracle.employees;
- Print nested table of records:
FOR i IN table_set.FIRST .. table_set.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
table_set (i) .email || '' ||
table_set (i) .last_name || ',' ||
table_set (i) .first_name
);
END LOOP; END;
/
select pid, spid, program, pga_used_mem / 1024/1024, pga_alloc_mem / 1024/1024 from v $ process
where spid = 3735758
PID SPID PROGRAM PGA_USED_MEM / 1024/1024 PGA_ALLOC_MEM / 1024/1024
--- ------------ ----------------------------------- ------------- ---------------------- --------------- --------
19 3735758 oracle @ nascds5 (TNS V1-V3) 616.22734165191650390625 616.63962650299072265625
"========= PGA uses a 600M
select a.name, to_char (b.value, '999,999,999') value
from v $ statname a, v $ mystat b
where a.statistic # = b.statistic #
and a.name like '% ga memory%';
NAME VALUE
-------------------------------------------------- -------------- ------------
session uga memory 1,896,024
session uga memory max 1,896,024
session pga memory 2,330,120
session pga memory max 647,400,968 <========== Peak PGA size for the session. 647,400,968
12c PGA_AGGREGATE_LIMIT added a new parameter to limit the size of PGA:
Limiting process size with database parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1) |
|
|
|