Wait Events: język, którym Oracle mówi o problemach wydajnościowych

Architektura Oracle Wait Interface

Oracle Wait Interface to mechanizm wbudowany w jądro bazy danych, który śledzi każdą chwilę, gdy proces serwera nie wykonuje produktywnej pracy. Kiedy sesja czeka na zasób (blok danych z dysku, zwolnienie blokady, potwierdzenie zapisu do redo logów), Oracle rejestruje ten fakt z dokładnością do mikrosekund.

Fundamentalnym pojęciem jest DB Time, czyli suma czasu CPU wykorzystanego przez sesje użytkowników oraz czasu spędzonego na oczekiwaniu na zasoby. DB Time mierzy rzeczywistą pracę wykonywaną przez bazę danych z perspektywy użytkownika. Jeśli masz 10 sesji, każda aktywna przez 60 sekund w ciągu minuty, Twój DB Time wynosi 600 sekund.

Average Active Sessions (AAS) to DB Time podzielony przez czas zegarowy. Wartość AAS równa 5 oznacza, że średnio 5 sesji było aktywnych w każdej chwili analizowanego okresu. Porównanie AAS z liczbą dostępnych rdzeni CPU daje natychmiastowy obraz obciążenia systemu.

Wait Events są grupowane w Wait Classes, co ułatwia kategoryzację problemów. Oracle definiuje klasy takie jak User I/O, System I/O, Concurrency, Commit, Network, Configuration, Administrative i inne. Ta hierarchia pozwala szybko zorientować się w naturze problemu bez analizowania setek indywidualnych zdarzeń.

Wait Events w AWR i ASH

AWR (Automatic Workload Repository) wykonuje snapshoty co godzinę, zbierając skumulowane statystyki Wait Events. Sekcja Top Foreground Wait Events w raporcie AWR pokazuje zdarzenia, które pochłonęły największą część DB Time w analizowanym okresie.

Typowy błąd początkujących to skupianie się na liczbie wystąpień zamiast na całkowitym czasie oczekiwania. Milion zdarzeń trwających mikrosekund każde jest mniej istotny niż tysiąc zdarzeń trwających sekundę. Zawsze patrz na kolumnę Total Wait Time i jej procentowy udział w DB Time.

ASH (Active Session History) działa inaczej; próbkuje aktywne sesje co sekundę, zapisując szczegółowe informacje o tym, co każda sesja robiła w danej chwili. To pozwala na analizę z rozdzielczością sekundową i identyfikację krótkotrwałych problemów, które znikają w zagregowanych danych AWR.

Wartko zapamiętać zasadę: AWR mówi ci co się stało, ASH mówi ci kiedy i komu. Używaj AWR do identyfikacji wzorców, ASH do drążenia konkretnych incydentów.

Klasa User I/O: dostęp do danych użytkownika

Zdarzenia z klasy User I/O dominują w większości systemów OLTP i stanowią najczęstszą przyczynę problemów wydajnościowych. Dwa kluczowe zdarzenia to db file sequential read oraz db file scattered read.

Zdarzenie db file sequential read oznacza odczyt pojedynczego bloku, typowo podczas dostępu przez indeks. Wysoki czas tego zdarzenia wskazuje na problemy z podsystemem storage (wolne dyski, przeciążona macierz) lub nieefektywne wykorzystanie indeksów (odczyt wielu bloków dla pojedynczego zapytania).

Zdarzenie db file scattered read to odczyt wielu sąsiadujących bloków podczas pełnego skanu tabeli lub indeksu. Nazwa pochodzi od faktu, że bloki są rozpraszane po buffer cache. Dominacja tego zdarzenia może oznaczać brakujące indeksy, nieaktualne statystyki optymalizatora lub zapytania, które z założenia muszą skanować duże wolumeny danych.

Analiza User I/O wymaga korelacji z planem wykonania zapytania. Samo wysokie db file sequential read nie jest problemem, jeśli zapytanie musi odczytać milion wierszy przez indeks. Problem pojawia się, gdy odczytujemy wielokrotnie te same bloki lub gdy czas pojedynczego odczytu przekracza rozsądne wartości (powyżej 10ms dla nowoczesnego storage).

Klasa System I/O: procesy wewnętrzne Oracle

System I/O obejmuje operacje wykonywane przez procesy tła Oracle, przede wszystkim DBWR (Database Writer) i LGWR (Log Writer). Te zdarzenia rzadko dominują w Top Wait Events, ale ich obecność sygnalizuje poważne problemy infrastrukturalne.

Zdarzenie db file parallel write to zapis brudnych bloków przez DBWR. Wysokie wartości oznaczają przeciążenie storage dla datafiles lub zbyt agresywne checkpointy wymuszające częste zapisy. Sprawdź parametry fast_start_mttr_target oraz obciążenie dysków przechowujących pliki danych.

Problemy z LGWR manifestują się przez log file parallel write (fizyczny zapis do redo logów) oraz wpływają na zdarzenia z klasy Commit. LGWR jest krytyczny dla wydajności transakcyjnej; każdy commit wymaga potwierdzenia zapisu do redo logów.

Zdarzenia control file parallel write i control file sequential read wskazują na problemy z plikami kontrolnymi. Częste aktualizacje plików kontrolnych (np. przy dużej liczbie przełączeń redo logów) mogą stać się wąskim gardłem.

Klasa Concurrency: walka o zasoby współdzielone

Zdarzenia Concurrency sygnalizują rywalizację pomiędzy sesjami o dostęp do tych samych zasobów. To najtrudniejsza klasa do diagnozy, ponieważ przyczyna często leży w logice aplikacji, nie w konfiguracji bazy danych.

Zdarzenie enq: TX row lock contention to klasyczny konflikt blokad na poziomie wierszy. Jedna sesja zmodyfikowała wiersz i nie zatwierdziła transakcji, podczas gdy inna sesja próbuje zmodyfikować ten sam wiersz. Rozwiązanie wymaga analizy logiki aplikacji: dlaczego transakcje trwają długo, dlaczego wiele sesji modyfikuje te same wiersze.

Latch waits (np. cache buffers chains, shared pool) wskazują na rywalizację o wewnętrzne struktury Oracle. Cache buffers chains to latch chroniący łańcuchy hash w buffer cache; wysoka rywalizacja sugeruje hot blocks, czyli bloki odczytywane przez wiele sesji jednocześnie. Rozwiązanie to często zmiana struktury aplikacji lub użycie reverse key indexes.

Mutex waits (np. cursor: pin S wait on X) dotyczą współdzielonego dostępu do kursorów w shared pool. Dominacja tego zdarzenia może oznaczać problemy z parsingiem (zbyt wiele hard parse) lub nieefektywne wykorzystanie bind variables.

Klasa Commit: koszt trwałości transakcji

Klasa Commit zawiera przede wszystkim zdarzenie log file sync, które mierzy czas oczekiwania sesji na potwierdzenie przez LGWR, że dane redo zostały zapisane na dysk. To zdarzenie występuje przy każdym COMMIT i ROLLBACK.

Wysoki średni czas log file sync (powyżej 5ms) wskazuje na problemy z wydajnością storage dla redo logów. Redo logi powinny znajdować się na najszybszym dostępnym storage, najlepiej na dyskach SSD z niskim latency zapisu.

Wysoka liczba wystąpień log file sync przy akceptowalnym średnim czasie wskazuje na nadmierną liczbę commitów. Aplikacje commitujące po każdej operacji DML generują ogromne obciążenie LGWR. Rozwiązanie to grupowanie operacji w większe transakcje (batch commits).

Zdarzenie log file parallel write (widoczne jako wait LGWR) to rzeczywisty zapis do redo logów. Różnica między log file sync a log file parallel write pokazuje, ile czasu sesja czeka w kolejce do LGWR versus ile trwa sam zapis.

Klasa Network: komunikacja z aplikacją

Zdarzenia Network wymagają szczególnej uwagi interpretacyjnej, ponieważ często wskazują na problemy poza bazą danych. Zdarzenie SQL*Net message from client mierzy czas, gdy baza czeka na kolejne polecenie od aplikacji; to czas bezczynności sesji z perspektywy bazy.

Wysokie wartości SQL*Net message from client są normalne i oznaczają, że aplikacja przetwarza dane lub użytkownik myśli. To zdarzenie nie powinno być liczone jako problem wydajnościowy bazy danych.

Zdarzenie SQL*Net more data to client oznacza wysyłanie dużych zbiorów wyników do aplikacji. Wysoki czas może wskazywać na problemy sieciowe (wąskie pasmo, duże latency) lub na zapytania zwracające zbyt wiele danych. Sprawdź, czy aplikacja naprawdę potrzebuje wszystkich zwracanych wierszy.

Zdarzenie SQL*Net message to client to wysyłanie pojedynczych komunikatów; wysokie wartości sugerują problemy z siecią między serwerem bazy a serwerem aplikacji.

Dynamiczne widoki monitorujące Wait Events

Widok V$SYSTEM_EVENT zawiera skumulowane statystyki Wait Events od startu instancji. Używaj go do identyfikacji dominujących zdarzeń w długim okresie, ale pamiętaj, że dane nie pokazują, kiedy zdarzenia występowały.

Widok V$SESSION_EVENT pokazuje statystyki Wait Events per sesja od momentu jej nawiązania. Przydatny do identyfikacji sesji z nietypowym profilem oczekiwań lub do porównania zachowania różnych aplikacji.

Widok V$SESSION_WAIT pokazuje bieżący stan sesji; na co czeka w tej chwili lub jakie było ostatnie zdarzenie. To podstawowe narzędzie do analizy w czasie rzeczywistym, gdy użytkownik zgłasza że jego sesja wisi.

Widok V$ACTIVE_SESSION_HISTORY zawiera próbki aktywnych sesji z ostatnich minut (dokładny czas zależy od aktywności i rozmiaru bufora). To główne źródło danych do analizy niedawnych problemów wydajnościowych.

Widok DBA_HIST_ACTIVE_SESS_HISTORY to historyczne dane ASH zapisywane przez AWR. Przechowuje tylko część próbek (domyślnie co 10. próbka), ale pozwala na analizę problemów sprzed dni czy tygodni.

Plan działania dla najważniejszych klas Wait Events

Dla dominacji User I/O zacznij od identyfikacji zapytań generujących najwięcej odczytów (V$SQL, kolumny DISK_READS, BUFFER_GETS). Sprawdź plany wykonania i rozważ dodanie indeksów, aktualizację statystyk lub przepisanie zapytań. Zweryfikuj wydajność storage (latency odczytów w V$FILESTAT).

Dla dominacji Concurrency zidentyfikuj obiekty będące źródłem konfliktów (V$SEGMENT_STATISTICS dla hot blocks, V$LOCK dla blokad wierszy). Przeanalizuj logikę aplikacji pod kątem długich transakcji i konfliktujących modyfikacji. Rozważ partycjonowanie lub zmianę struktury indeksów.

Dla dominacji Commit sprawdź średni czas log file sync. Jeśli jest wysoki, zoptymalizuj storage dla redo logów. Jeśli liczba commitów jest wysoka przy niskim średnim czasie, pracuj z zespołem deweloperskim nad grupowaniem transakcji.

Dla dominacji Network przeanalizuj, czy problem leży w sieci (traceroute, testy przepustowości), w aplikacji (zbyt duże zestawy wyników, nieefektywne przetwarzanie) czy w konfiguracji Oracle Net (SDU size, TCP buffers).

Najczęstsze błędy interpretacji Wait Events

Pierwszy błąd to ignorowanie kontekstu DB Time. Zdarzenie odpowiadające za 90% Wait Time, ale tylko 10% DB Time, nie jest głównym problemem; pozostałe 90% DB Time to praca CPU, która może być prawdziwym wąskim gardłem.

Drugi błąd to porównywanie bezwzględnych wartości między różnymi systemami. System przetwarzający 10x więcej transakcji będzie miał 10x więcej Wait Events; to normalne. Porównuj proporcje i średnie czasy, nie sumy.

Trzeci błąd to traktowanie idle events jako problemów. Zdarzenia takie jak SQL*Net message from client, PX Deq: Execution Msg czy rdbms ipc message to normalne oczekiwanie na pracę, nie problem wydajnościowy.

Czwarty błąd to analiza Wait Events w izolacji od charakterystyki aplikacji. System OLAP wykonujący pełne skany dużych tabel będzie miał wysokie db file scattered read i to jest oczekiwane zachowanie, nie problem do naprawy.