Podstawy Oracle Database: alokacja pamięci PGA

Pamięć PGA (Program Global Area) serwera bazy danych Oracle Database 12c jest bezpośrednio wykorzystywana przez procesy Server Process obsługujące sesje użytkowników. W przeciwieństwie do SGA, PGA nie jest współdzielona przez wiele sesji – każda sesja ma do dyspozycji swoją odrębną prywatną część PGA. Preferowany sumaryczny rozmiar PGA dla wszystkich sesji może być ustalony przez administratora (PGA_AGGREGATE_TARGET) lub automatycznie dobrany przez instancję (w ramach MEMORY_TARGET). W trybie serwera dedykowanego (Dedicated Server Mode) pamięć PGA zawiera trzy główne obszary:

• SQL Work Areas – wykorzystywany do sortowania rekordów, budowania tablic haszowych, tworzenia i scalania indeksów bitmapowych, buforowania zapisów dokonywanych przez operacje typu Bulk
• Session Memory – przechowujący informacje o bieżącej sesji, zmienne pakietów PL/SQL, zmienne kontekstów aplikacyjnych, stos wykonywania kodu PL/SQL
• Private SQL Areas – przechowujący informacje o kursorach aktualnie wykonywanych poleceń SQL – zmienne wiązane i stany kursorów, łączna liczba kursorów w obszarach Private SQL Areas jest limitowana przez parametr OPEN_CURSORS

Rozmiar alokacji obszarów Session Memory i Private SQL Areas jest zależny od ilości umieszczonych w nich informacji, natomiast rozmiar obszarów SQL Work Areas jest automatycznie dobierany odpowiednio do liczby aktualnych sesji oraz preferowanego sumarycznego rozmiaru PGA. Z tego powodu obszary SQL Work Areas są przez Oracle nazywane „tunable” (regulowane), a pozostałe obszary – „untunable” (sztywne). Może się oczywiście zdarzyć (i często się zdarza), że rozmiary obszarów „untunable” łącznie przekroczą wskazany za pomocą parametru PGA_AGGREGATE_TARGET preferowany sumaryczny rozmiar PGA. W takim przypadku serwer zachowa się dość łagodnie – zezwoli na przekroczenie rozmiaru preferowanego, nierzadko niestety doprowadzając w ten sposób do rozpoczęcia wymiatania na dysk pamięci wirtualnej przez system operacyjny. Problem ten dotyczy głównie środowisk, w których administrator skonfigurował MEMORY_TARGET lub wartość PGA_AGGREGATE_TARGET+SGA_TARGET na granicy rozmiaru dostępnej pamięci RAM. Warto nadmienić, że serwer Oracle Database 12c oferuje na tę okazję uproszczony mechanizm ratunkowy w postaci parametru o nazwie PGA_AGGREGATE_LIMIT, określającego absolutny limit sumarycznego rozmiaru PGA, którego próba przekroczenia powoduje automatyczne zabicie wywołania lub sesji. Domyślna wartość PGA_AGGREGATE_LIMIT to największa z trzech wartości: 2GB, 2xPGA_AGGREGATE_TARGET lub PROCESSESx3MB.

Najczęstsze problemy administratorskie związane z konfiguracją pamięci PGA dotyczą następujących sytuacji:

• niewłaściwie ustawiony parametr PGA_AGGREGATE_TARGET (nieodpowiadający potrzebom serwera)
• sesja/sesje nadmiernie konsumująca pamięć PGA typu „untunable” i doprowadzająca do wyczerpania pamięci RAM serwera, a w konsekwencji do wymiatania pamięci na dysk
• wyciek pamięci PGA z powodu błędów aplikacyjnych lub z powodu usterek serwera Oracle Database

Poniżej zaprezentujemy stosowne metody monitorowania i rozwiązywania powyższych problemów.

Obserwacja aktualnego preferowanego sumarycznego rozmiaru pamięci PGA

Jeżeli sami ustawiliśmy PGA_AGGREGATE_TARGET, to wystarczy odczytać jego wartość, np.:

SQL> show parameter PGA_AGGREGATE_TARGET

Jeżeli jednak rozmiar ten jest automatycznie ustalany przez serwer, to aktualną wartość docelową odnajdziemy w perspektywie v$pgastat:

select name, value from v$pgastat
where name like 'aggregate PGA target%';
NAME                           VALUE
------------------------------ -----------
aggregate PGA target parameter 536870912

Obserwacja aktualnego zużycia pamięci PGA przez wszystkie sesje

Perspektywa v$pgastat:

select value from v$pgastat
where name='total PGA inuse';
VALUE
----------
1120851968

Perspektywa v$process:

select sum(pga_used_mem) as value from v$process;
VALUE
----------
1120876424

Perspektywa v$sysstat:

select value from v$sysstat
where name='session pga memory';
VALUE
----------
1121925392

Perspektywa v$sesstat:

select sum(ss.value) as value
from v$sesstat ss join v$statname sn
on (ss.statistic# = sn.statistic#)
where sn.name = 'session pga memory';

VALUE
----------
1121925392

Zwykle zauważymy drobne niedokładności raportowanych wartości w zależności od użytego źródła informacji. Parę lat temu Jonathan Lewis sugerował, aby większym zaufaniem obdarzać v$sysstat aniżeli v$process (https://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/). Perspektywa v$process uwzględnia np. procesy, które mogą nie obsługiwać żadnej sesji (Dnnn, Snnn, Pnnn).

Obserwacja zużycia pamięci PGA z podziałem na sesje

Perspektywa v$sesstat:

select ss.sid, ss.value
from v$sesstat ss join v$statname sn
on (ss.statistic# = sn.statistic#)
where sn.name = 'session pga memory';

SID      VALUE
---------- ----------
1     790912
2     766056
3     766056
4     774456

Obserwacja zużycia pamięci PGA z podziałem na procesy

Perspektywa v$process:

select spid, pga_used_mem
from v$process;

SPID                     PGA_USED_MEM
------------------------ ------------
4931                           797556
4933                           775604
4935                           772356
4939                           780780
4941                           775604
5180                           776828
4945                           778700
4947                          1849804
...

Obserwacja poleceń SQL, których operacje nie mieszczą się w SQL Work Areas

Perspektywa v$sql_workarea:

select s.sql_text, wa.operation_type, wa.last_memory_used
from v$sql s join v$sql_workarea wa on (s.address = wa.address)
where onepass_executions+multipasses_executions>0;

SQL_TEXT                           OPERATION_TYPE LAST_MEMORY_USED
---------------------------------- -------------- ----------------
select * from ... order by a desc  SORT (v2)      5269504

Obserwacja podziału pamięci PGA wybranej sesji na poszczególne obszary

Perspektywa v$process_memory pokazuje podział pamięci PGA procesu na różne kategorie. Kategoria „PL/SQL” mieści w sobie część Session Memory, natomiast kategoria „SQL” – SQL Work Areas. „Freeable” to niepotrzebna już pamięć, która zostanie zwolniona przy okazji. Co kryje w sobie kategoria „Other” pozostaje tajemnicą Oracle…

select s.sid, p.spid, pm.category, pm.allocated
from v$process_memory pm join v$process p on (pm.pid=p.pid)
join v$session s on (p.addr=s.paddr)
where sid=&identyfikator_sesji;

SID SPID                     CATEGORY         ALLOCATED
---------- ------------------------ --------------- ----------
22 12438                    SQL                6492496
22 12438                    PL/SQL           350259056
22 12438                    Freeable           1048576
22 12438                    Other              8250324

Perspektywa v$sql_workarea_active prezentuje zawartość SQL Work Areas:

select actual_mem_used, operation_type
from v$sql_workarea_active
where sid=&identyfikator_sesji;

ACTUAL_MEM_USED OPERATION_TYPE
--------------- ----------------------------------------
4746240 SORT (v2)

Diagnostyka

Diagnostyka zwykle polega na realizacji następujących kroków:

1. Weryfikacja rzeczywistego zużycia PGA w stosunku do ustawionego PGA_AGGREGATE_TARGET
2. Wyszukanie sesji, które konsumują największą ilość pamięci PGA typu „untunable”, a następnie analiza kodu PL/SQL wykorzystywanego przez te sesje w celu jego optymalizacji.
3. Obserwacja, czy zużycie PGA przez sesję jest stabilne w czasie (wycieki pamięci z powodu usterki dewelopera lub usterki Oracle).
4. Ustalenie rozmiaru PGA_AGGREGATE_TARGET w sposób pozwalający uniknąć sortowań dyskowych, a jeśli to nie będzie możliwe – to w sposób umożliwiający wykonanie sortowań dyskowych maksymalnie jednoprzebiegowo (kolumny optimal_executions, onepass_executions i multipasses_executions w perspektywie v$sql_workarea_histogram)
5. W ostateczności, zabawa perspektywą V$PROCESS_MEMORY_DETAIL (wcześniej ORADEBUG SETMYPID i  ORADEBUG DUMP PGA_DETAIL_GET pid).