Oracle Memory Tuning: praktyczny przewodnik po SGA, PGA i diagnostyce problemów pamięci

Architektura pamięci Oracle: fundamenty

Zanim przejdziemy do tuningu, musimy zrozumieć podstawową architekturę pamięci Oracle. Baza danych Oracle dzieli pamięć na dwa główne obszary: SGA (System Global Area) oraz PGA (Program Global Area). To rozróżnienie nie jest przypadkowe; wynika z fundamentalnych różnic w sposobie wykorzystania tych zasobów.

SGA to pamięć współdzielona między wszystkie procesy serwera. Każda instancja Oracle ma dokładnie jedną SGA, która jest alokowana podczas startu bazy danych. PGA natomiast to pamięć prywatna dla każdego procesu serwera; ile masz sesji, tyle potencjalnie masz obszarów PGA.

SGA: serce wydajności Oracle

SGA składa się z kilku kluczowych komponentów. Najważniejsze to Buffer Cache, Shared Pool, Redo Log Buffer oraz Large Pool. Od wersji Oracle 10g mamy do dyspozycji automatyczne zarządzanie SGA (ASMM), a od 11g automatyczne zarządzanie całą pamięcią (AMM). Jednak automatyka nie zawsze działa optymalnie.

Buffer Cache: gdzie żyją Twoje dane

Buffer Cache przechowuje kopie bloków danych odczytanych z dysku. Im więcej bloków zmieści się w pamięci, tym mniej operacji I/O musi wykonać baza. Metryka Buffer Cache Hit Ratio przez lata była świętym Graalem DBA, ale dziś wiemy, że sama w sobie niewiele mówi.

Aby sprawdzić aktualny rozmiar i wykorzystanie Buffer Cache, wykonaj zapytanie:

SELECT component, current_size/1024/1024 MB FROM v$sga_dynamic_components WHERE component LIKE '%buffer%';

Kluczową metryką jest liczba physical reads w stosunku do logical reads. Możesz to sprawdzić w v$sysstat lub poprzez AWR. Jeśli widzisz wysoką liczbę physical reads przy jednocześnie niskim Buffer Cache Hit Ratio, to sygnał do zwiększenia pamięci.

Z mojego doświadczenia wynika, że Buffer Cache Hit Ratio powyżej 95% to dobry wynik dla systemów OLTP. Jednak w systemach analitycznych (DSS/DWH) wartość 70-80% może być całkowicie akceptowalna ze względu na sekwencyjny charakter odczytów i mechanizm direct path read.

Shared Pool: kod i metadane

Shared Pool przechowuje sparsowane zapytania SQL, plany wykonania, definicje obiektów oraz informacje o użytkownikach. Nieprawidłowo skonfigurowany Shared Pool objawia się błędami ORA-04031 (unable to allocate shared memory) lub wysokim czasem parsowania.

Typowe problemy ze Shared Pool to fragmentacja oraz brak używania bind variables. Aplikacje generujące tysiące unikalnych zapytań (literal SQL) potrafią zabić nawet bardzo duży Shared Pool w ciągu kilku godzin.

Do diagnostyki wykorzystaj widok v$sgastat:

SELECT pool, name, bytes/1024/1024 MB FROM v$sgastat WHERE pool = 'shared pool' ORDER BY bytes DESC;

Szczególną uwagę zwróć na komponent free memory. Jeśli jest blisko zera, a jednocześnie widzisz dużo małych fragmentów wolnej pamięci (v$shared_pool_reserved), masz problem z fragmentacją.

PGA: pamięć procesów roboczych

PGA to pamięć używana przez procesy serwera do operacji sortowania, hash joinów, bitmap merge oraz innych operacji wymagających pamięci roboczej. W trybie dedicated server każda sesja ma własną PGA; w trybie shared server część pamięci przenosi się do SGA (UGA).

Parametr PGA_AGGREGATE_TARGET określa docelową sumę wszystkich PGA. Oracle stara się utrzymać łączne zużycie PGA poniżej tej wartości, ale nie gwarantuje tego; w ekstremalnych przypadkach może przekroczyć limit nawet o 200%.

Kluczowe metryki PGA znajdziesz w widoku v$pgastat:

SELECT name, value FROM v$pgastat WHERE name IN ('aggregate PGA target parameter', 'total PGA allocated', 'maximum PGA allocated', 'cache hit percentage');

Wartość cache hit percentage powinna przekraczać 90%. Jeśli jest niższa, operacje wymagające pamięci roboczej wykonują się na dysku (temp tablespace), co dramatycznie spowalnia zapytania.

Memory Advisors: Twoi pomocnicy

Oracle dostarcza zestaw advisorów, które analizują historyczne wykorzystanie pamięci i sugerują optymalne wartości parametrów. Znajdziesz je w widokach v$db_cache_advice, v$shared_pool_advice, v$pga_target_advice oraz v$memory_target_advice.

Buffer Cache Advisor

Ten advisor pokazuje przewidywaną liczbę physical reads dla różnych rozmiarów Buffer Cache:

SELECT size_for_estimate MB, size_factor, estd_physical_read_factor FROM v$db_cache_advice WHERE name = 'DEFAULT' ORDER BY size_for_estimate;

Szukaj punktu, w którym dalsze zwiększanie pamięci nie przynosi już znaczącej redukcji physical reads. Zwykle jest to miejsce, gdzie estd_physical_read_factor spada poniżej 1.0 i stabilizuje się.

PGA Target Advisor

Podobnie działa advisor dla PGA:

SELECT pga_target_for_estimate/1024/1024 MB, pga_target_factor, estd_extra_bytes_rw/1024/1024 EXTRA_IO_MB FROM v$pga_target_advice ORDER BY pga_target_for_estimate;

Kolumna estd_extra_bytes_rw pokazuje przewidywaną ilość dodatkowego I/O do temp tablespace. Gdy wartość spada do zera lub bliskiej zeru, dalsze zwiększanie PGA nie ma sensu.

Diagnostyka problemów pamięci

Problemy z pamięcią objawiają się na różne sposoby: wolne zapytania, błędy ORA-04031, wysokie wait events związane z pamięcią lub po prostu niestabilność systemu.

Kluczowe widoki diagnostyczne

Do codziennej diagnostyki wykorzystuję następujące widoki:

• v$sga_dynamic_components; pokazuje aktualny rozmiar i operacje resize komponentów SGA
• v$memory_resize_ops; historia operacji zmiany rozmiaru pamięci
• v$sql_workarea_active; aktywne operacje wymagające pamięci roboczej
• v$process; rzeczywiste zużycie PGA przez procesy

Typowe problemy i rozwiązania

ORA-04031 to klasyczny objaw przepełnionego lub sfragmentowanego Shared Pool. Pierwsze kroki to analiza v$sqlarea pod kątem literal SQL oraz sprawdzenie, czy aplikacja używa bind variables. Doraźnie można opróżnić shared pool komendą ALTER SYSTEM FLUSH SHARED_POOL, ale to tylko tymczasowe rozwiązanie.

Wysokie wait events typu 'latch: shared pool' lub 'latch: library cache' wskazują na rywalizację o zasoby Shared Pool. Rozwiązaniem może być zwiększenie parametru SHARED_POOL_SIZE lub optymalizacja aplikacji.

Jeśli widzisz częste operacje resize w v$memory_resize_ops przy włączonym AMM/ASMM, rozważ ustawienie minimalnych wartości dla krytycznych komponentów poprzez parametry DB_CACHE_SIZE i SHARED_POOL_SIZE (działają jako wartości minimalne przy włączonym automatycznym zarządzaniu).

Analiza AWR i ASH

Raporty AWR dostarczają sekcji Memory Statistics oraz Memory Advisory, które powinny być pierwszym punktem analizy. Szczególnie wartościowa jest sekcja Instance Efficiency Percentages zawierająca Buffer Hit%, Library Hit% oraz inne kluczowe wskaźniki.

ASH (Active Session History) pozwala na analizę problemów pamięci w kontekście czasowym. Zapytanie do v$active_session_history filtrowane po wait_class = 'Memory' pokaże sesje, które doświadczały problemów z pamięcią.

Praktyczne rekomendacje konfiguracyjne

Na serwerze dedykowanym dla Oracle zwykle przydzielam 70-80% RAM dla instancji bazy danych. Podział między SGA i PGA zależy od charakterystyki obciążenia: dla OLTP stosunek 80:20 jest rozsądny; dla systemów analitycznych może to być nawet 60:40.

Przy konfiguracji AMM (MEMORY_TARGET) ustawiam również minimalne wartości dla Buffer Cache (40% SGA) i Shared Pool (20% SGA). Zapobiega to sytuacji, w której automatyka zabierze pamięć z krytycznego komponentu w odpowiedzi na chwilowe zapotrzebowanie.

Warto pamiętać o HugePages na systemach Linux. Dla dużych instancji SGA (powyżej 8GB) HugePages znacząco redukują narzut związany z zarządzaniem pamięcią i eliminują ryzyko swapowania SGA przez system operacyjny.