Oracle 12c new features: pga_aggregate_limit

В версии 12с появилась возможность задавать жесткое ограничение на размер PGA. 
Раньше такой возможности, порой, не хватало.
Отвечает за это параметр pga_aggregate_limit.
Стоит обратить внимание, что этот параметр не играет роли для пользователя SYS и для фоновых процессов.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>show parameter pga NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit                  big integer 4G pga_aggregate_target                 big integer 1608M

Ограничение установлено в четыре гигабайта.
Есть небольшая тестовая табличка на сотню тысяч записей:

SQL> desc t1;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  C0                                        NOT NULL NUMBER  C1                                        NOT NULL VARCHAR2(32)  C3                                        NOT NULL DATE  C4                                        NOT NULL VARCHAR2(2000)

Заходим под непривилегированным пользователем и начинаем совершенно бональным образом есть память:

DECLARE    TYPE mytabtype IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;    mytab mytabtype;    j BINARY_INTEGER := 0;    CURSOR cur IS SELECT * FROM t1; BEGIN FOR i IN 1..20 LOOP OPEN cur;    LOOP       j := j + 1;       FETCH cur INTO mytab(j);       EXIT WHEN cur%NOTFOUND;    END LOOP;    CLOSE cur; END loop; END;

В зависимости от быстроты сервера, когда процесс съест все что можно, увидим у него такую картину ожиданий:

SQL> select seq#,event,wait_time from v$session_wait_history where sid=50 order by 1;             SEQ# EVENT                                 WAIT_TIME ---------------- ------------------------------ ----------------                1 acknowledge over PGA limit                  100                2 acknowledge over PGA limit                  100                3 acknowledge over PGA limit                  100                4 acknowledge over PGA limit                  100                5 acknowledge over PGA limit                  100                6 acknowledge over PGA limit                  100                7 acknowledge over PGA limit                  100                8 acknowledge over PGA limit                  100                9 acknowledge over PGA limit                  100               10 acknowledge over PGA limit                  100 10 rows selected.

В сессии получим ошибку:

DECLARE * ERROR at line 1: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT ORA-06512: at line 11

Еще получим запись в alert.log:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

А также трейс файл и инцидент.
Можно, для интереса, посмотреть:

SQL> select sid,spid,pga_used_mem,pga_alloc_mem,pga_max_mem from v$process, v$session where addr = paddr and sid= (select sid from v$mystat where rownum=1);              SID SPID           PGA_USED_MEM    PGA_ALLOC_MEM      PGA_MAX_MEM ---------------- ---------- ---------------- ---------------- ----------------               50 12939               1707117          2049589       4033824309

Было честно выделено все четыре гигабайта. Больше oracle не дал.
Можно и через top посмотреть:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 12939 oracle 20 0 8878m 4.0g 392m S 22.7 25.5 4:19.93 oracle_12939_or

или так:

[root@oracle ~]# grep VmHWM /proc/12939/status   VmHWM: 4307196 kB

Ограничение действует на все не SYS'овые сессии.

Для наглядности:

SID USERNAME                       SPID                     PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM ---------- ------------------------------ ------------------------ ------------ ------------- -----------         48 TEST1                          25665                      2053421037    2054685589  2342257557          50 TEST1                          25669                      1742126869    1743324053  2212103061         727 SYS                            25675                      3984055285    3986245173  4287776309

SYS успешно съел четыре гигабайта в одну сессию, а тестовые пользователи ждут пока кто-то освободит немного памяти ввиду ограничения pga_aggregate_limit.