Active Session History (ASH) – najpotężniejsze narzędzie diagnostyczne Oracle

Jak działa próbkowanie ASH

Oracle co sekundę wykonuje migawkę wszystkich aktywnych sesji w bazie danych. Kluczowe jest tutaj słowo "aktywnych", czyli sesji, które faktycznie coś robią: wykonują kod na CPU lub czekają na jakiś zasób (I/O, blokadę, latch). Sesje bezczynne, czekające na polecenia od użytkownika, są pomijane.

Mechanizm próbkowania jest niezwykle lekki. Oracle nie musi wykonywać kosztownych operacji, aby zebrać dane. Proces MMNL (Manageability Monitor Light) odczytuje informacje bezpośrednio ze struktur pamięci SGA, gdzie i tak są przechowywane dane o sesjach. Narzut na system jest minimalny, zazwyczaj poniżej 1% wykorzystania CPU.

Każda próbka zawiera bogaty zestaw informacji: identyfikator sesji (SID, SERIAL#), wykonywany SQL_ID, aktualny Wait Event, numer bloku danych (jeśli sesja czeka na I/O), identyfikator blokującej sesji (jeśli występuje blokada), plan wykonania zapytania oraz wiele innych atrybutów.

Po 20 latach pracy z Oracle mogę powiedzieć jedno: ASH zmienił sposób, w jaki diagnozujemy problemy. Wcześniej musieliśmy czekać na raporty AWR generowane co godzinę. Teraz widzę, co działo się 30 sekund temu, z dokładnością do pojedynczej sesji.

V$ACTIVE_SESSION_HISTORY

Widok V$ACTIVE_SESSION_HISTORY to okno do bieżących danych ASH przechowywanych w pamięci SGA. Rozmiar bufora jest kontrolowany przez parametr ukryty _ash_size, ale zazwyczaj Oracle automatycznie przydziela około 2MB na każdy procesor w systemie.

W praktyce oznacza to, że w pamięci przechowywanych jest od kilku minut do kilku godzin historii, w zależności od aktywności systemu. Im więcej aktywnych sesji, tym szybciej bufor się zapełnia i starsze próbki są nadpisywane.

Najważniejsze kolumny widoku

• SAMPLE_TIME: dokładny czas pobrania próbki
• SESSION_ID, SESSION_SERIAL#: identyfikacja sesji
• SQL_ID, SQL_CHILD_NUMBER: wykonywane zapytanie i numer kursora potomnego
• SESSION_STATE: stan sesji (ON CPU lub WAITING)
• EVENT: nazwa Wait Event, jeśli sesja oczekuje
• WAIT_CLASS: kategoria oczekiwania (User I/O, Concurrency, Application)
• BLOCKING_SESSION: SID sesji blokującej
• CURRENT_OBJ#: identyfikator obiektu, na którym operuje sesja
• SQL_PLAN_HASH_VALUE: identyfikator planu wykonania

Zapytanie do tego widoku nie wymaga żadnych specjalnych uprawnień poza SELECT_CATALOG_ROLE. Dane są dostępne natychmiast, bez opóźnień związanych z generowaniem raportów.

DBA_HIST_ACTIVE_SESS_HISTORY

Co 10 sekund proces MMON przenosi część próbek z pamięci do repozytorium AWR na dysku. Nie wszystkie próbki są zachowywane; Oracle stosuje algorytm próbkowania, który wybiera co dziesiątą próbkę, ale z preferencją dla próbek zawierających istotne zdarzenia.

Dane historyczne są przechowywane zgodnie z polityką retencji AWR, domyślnie przez 8 dni. Można to zmienić procedurą DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS.

Główna różnica względem V$ACTIVE_SESSION_HISTORY to zasięg czasowy. Widok dynamiczny pokazuje ostatnie minuty lub godziny. Widok historyczny pozwala analizować problemy sprzed kilku dni. Ceną jest mniejsza granularność; zamiast próbki co sekundę mamy próbkę co 10 sekund.

Analiza blokad

ASH jest nieoceniony przy diagnozowaniu problemów z blokadami. Kolumna BLOCKING_SESSION wskazuje bezpośrednio sesję, która trzyma blokadę. W połączeniu z SQL_ID możemy zobaczyć, jakie zapytanie powoduje problem.

Typowe zapytanie diagnostyczne wygląda następująco:

SELECT sample_time, session_id, blocking_session, event, sql_id FROM v$active_session_history WHERE blocking_session IS NOT NULL AND sample_time > SYSDATE - INTERVAL '10' MINUTE ORDER BY sample_time DESC;

Wyniki pokazują chronologię blokad: kto kogo blokował i kiedy. Możemy prześledzić łańcuch blokad, gdy sesja A blokuje B, a B blokuje C. ASH pokaże wszystkie ogniwa tego łańcucha.

Identyfikacja źródła problemu

Sama informacja o blokadzie to za mało. Musimy zrozumieć, dlaczego sesja blokująca trzyma zasób tak długo. ASH pozwala sprawdzić, co robiła ta sesja: czy wykonywała długie zapytanie, czy czekała na zatwierdzenie transakcji przez użytkownika, czy może sama była zablokowana przez inną sesję.

Analiza pojedynczej sesji

Użytkownicy często zgłaszają: "moja aplikacja wolno działa". Z ASH możemy odtworzyć dokładną historię ich sesji.

Znając SID i SERIAL# sesji (lub przedział czasowy i nazwę użytkownika), możemy zobaczyć sekwencję wykonywanych operacji. Każda próbka to jeden kadr z filmu pokazującego pracę sesji.

Agregując próbki według SQL_ID, otrzymujemy ranking zapytań pochłaniających najwięcej czasu. Agregując według EVENT, widzimy, na co sesja czekała. Te dwie informacje zazwyczaj wystarczają, aby wskazać przyczynę spowolnienia.

Analiza pojedynczego SQL_ID

Gdy wiemy, że konkretne zapytanie sprawia problemy, ASH pozwala zbadać je szczegółowo. Możemy zobaczyć:

• Ile razy zapytanie było próbkowane (przybliżony czas wykonania w sekundach)
• Rozkład czasu między CPU a oczekiwaniami
• Konkretne Wait Events (db file sequential read, buffer busy waits)
• Czy zapytanie używało różnych planów wykonania (SQL_PLAN_HASH_VALUE)
• Które sesje wykonywały to zapytanie

SELECT sql_plan_hash_value, session_state, event, COUNT(*) as samples FROM v$active_session_history WHERE sql_id = 'abc123xyz' AND sample_time > SYSDATE - INTERVAL '1' HOUR GROUP BY sql_plan_hash_value, session_state, event ORDER BY samples DESC;

Wyniki mogą pokazać, że 80% czasu zapytanie spędza na db file sequential read, co sugeruje problem z indeksami. Albo że pojawiły się dwa różne plany wykonania i jeden z nich jest znacznie wolniejszy.

Przykłady praktycznych zapytań

Top SQL z ostatniej godziny

SELECT sql_id, COUNT(*) as samples, ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) as pct FROM v$active_session_history WHERE sample_time > SYSDATE - INTERVAL '1' HOUR AND sql_id IS NOT NULL GROUP BY sql_id ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;

Aktywność konkretnego użytkownika

SELECT sample_time, sql_id, event, session_state FROM v$active_session_history WHERE user_id = (SELECT user_id FROM dba_users WHERE username = 'APPUSER') AND sample_time > SYSDATE - INTERVAL '30' MINUTE ORDER BY sample_time;

Sesje czekające na I/O

SELECT session_id, sql_id, current_obj#, COUNT(*) as samples FROM v$active_session_history WHERE wait_class = 'User I/O' AND sample_time > SYSDATE - INTERVAL '15' MINUTE GROUP BY session_id, sql_id, current_obj# ORDER BY samples DESC;

Najczęstsze błędy interpretacji

Próbkowanie co sekundę ma fundamentalne ograniczenie: operacje trwające krócej niż sekunda mogą być pominięte lub niedoreprezentowane. Zapytanie wykonujące się 100ms może nie pojawić się w ASH wcale, mimo że jest wykonywane tysiące razy na minutę.

Drugi częsty błąd to wyciąganie wniosków z małej liczby próbek. Jeśli widzimy 3 próbki zapytania z Wait Event "log file sync", nie możemy twierdzić, że to zapytanie ma problem z logowaniem. Trzy próbki to za mało dla statystycznej istotności. Potrzebujemy dziesiątek lub setek próbek, aby mieć pewność.

Trzecia pułapka dotyczy kolumny BLOCKING_SESSION. Pokazuje ona sesję blokującą w momencie próbkowania. Jeśli blokada trwała pół sekundy, mogła nie zostać zarejestrowana. Z drugiej strony jedna próbka z blokadą nie oznacza poważnego problemu; krótkotrwałe blokady są normalne.

Wreszcie pamiętajmy, że ASH pokazuje tylko aktywne sesje. Jeśli użytkownik narzeka na wolne działanie, a jego sesja spędza 90% czasu bezczynnie (czekając na input), ASH tego nie pokaże. Zobaczymy tylko te momenty, gdy sesja faktycznie pracowała.