Jeśli jesteś administratorem baz Oracle to czasem dostajesz pytanie 'dlaczego mój job/query/program wolno działa’.
Jeśli tworzysz oprogramowanie dla baz danych Oracle to istnieje szansa, że takie pytanie nasuwa Ci się samo. A przynajmniej ja, jako admin Oracle, mam taką nadzieję.
W dalszej części artykułu powiem Ci skąd wziąć te informacje.
Wstęp
Oracle jest motorem baz danych o bogatej instrumentacji. Dostęp do informacji o tym jak działa instancja bazy danych zapewniany jest poprzez dynamiczne widoki wydajnościowe V$_.
Instalacje Real Application Clusters udostępniają informacje o wszystkich instancjach poprzez widoki globalne GV$_.
Bardzo przydatnym, w strojeniu wydajności, jest V$ACTIVE_SESSION_HISTORY. Zawsze i łatwo dostępne na przykład przy pomocy komend SQL. Od Oracle 10g. Do zakupu z Diagnostic Pack.
Czym jest V$ACTIVE_SESSION_HISTORY?
Widok ten jest zbiorem faktów o historii działania ostatnich aktywnych sesji. Aktywnych oznacza mających cokolwiek innego niż 'Idle’ w kolumnie WAIT_CLASS widoku V$SESSION.
Ostatnich oznacza, że nie wiadomo ile historii zmieści się w pamięci, której reprezentacją jest widok. Trzeba pamiętać, że obszar pamięci przeznaczony dla danych ma określoną pojemność i zapisywany jest na zasadzie round robin. Po polsku karuzelowo. Czyli na ruchliwej bazie danych informacje będą z krótszego okresu czasu niż na bazie z małym ruchem. Najstarsze dane szybciej zostaną nadpisane najnowszymi.
Próbki wydajnościowe zbierane są co sekundę. Agregując dane, wg odpowiednich kolumn, dostajesz informacje na co poświęciła zasoby jak CPU, IO czy pamięć Twoja instancja bazy danych. Innymi słowy, która sesja, kiedy i wykonując jaką komendę konsumowała jakie zasoby instancji.
Każdy rekord ma określony numer próbki: SAMPLE_ID oraz jej czas: SAMPLE_TIME i dotyczy jednej konkretnej sesji: SESSION_ID i SESSION_SERIAL#.
Jakie informacje można znaleźć w V$ACTIVE_SESSION_HISTORY?
Widok jest zbiorem faktów opisywanych przez wymiary. Dane można podzielić na kilka grup jak sesja, komenda SQL, wait, blok, obiekt czy aplikacja. Odpowiednie agregowanie surowych danych dostarczy informacji na wybrany temat.
Poniżej wycinek z wyniku komendy
desc V$ACTIVE_SESSION_HISTORYz opisem znaczenia grup kolumn.
Część kolumn usunąłem aby poprawić czytelność.
Jak znaleźć sesję, która Cię interesuje?
Gdy kontrolujesz proces – na przykład strojąc konkretne zapytanie czy procedurę składowaną – i wydajesz odpowiednie komendy ze swojego użytkownika i aplikacji typu SQLcl czy SQL Developer to znalezienie sesji nie nastręcza problemów. Wystarczy odpytać widok V$ACTIVE_SESSION_HISTORY wg kolumny PROGRAM i poszukać wartości 'SQLcl’ lub 'SQL Developer’.
Jeśli łączysz się sqlplus to w kolumnie PROGRAM znajdziesz 'sqlplus@<nazwa hosta na którym uruchamiasz sqlplus>’.
Jeśli to sesja aplikacji, na przykład pochodząca z serwera aplikacji łączącego się przez JDBC, to sprawa jest trudniejsza. Zazwyczaj jest wiele takich sesji należących do tego samego użytkownika, mających w kolumnie PROGRAM 'JDBC Thin Client’, a w MACHINE nazwę hosta na którym pracuje serwer aplikacji.
Możesz ograniczać wynik do określonej komendy. Przyrównujesz SQL_OPNAME do DELETE/UPDATE/INSERT/SELECT/…
Nawet takie podejście nie gwarantuje znalezienia poszukiwanej sesji. Może być wiele wykonujących tę samą komendę tylko innym SQL.
Jak znaleźć zapytanie, które Cię interesuje?
Jeśli nie możesz znaleźć sesji możesz odnaleźć interesujący SQL_ID komendy SQL.
Kolejnym wartym uwagi dynamicznym widokiem wydajnościowym jest V$SQL. Dzięki niemu uzyskujesz dostęp do statystyk wykonania komend SQL przechowywanych przez instancję w pamięci podręcznej. Czyli do komend, które były już wykonane przez instancję.
Zamiast V$SQL masterzy Oracle zalecają używanie V$SQLSTATS. Dane mają większą retencję, a widok lepiej się skaluje. Jest szybszy.
W kolumnie SQL_TEXT znajdziesz pierwsze 1000 znaków komendy SQL. Jeśli poszukiwana przez Ciebie komenda zawiera jakiś charakterystyczny ciąg znaków możesz wyszukać go tutaj. Na przykład tak:
SELECT sql_id FROM v$sql WHERE sql_text like '%charakterystyczny ciąg znaków%';
Aby łatwiej było znaleźć takie komendy możesz je odpowiednio modyfikować umieszczając własne znaczniki w komentarzach. Na przykład tak:
SELECT /* charakterystyczny napis Marcina */ kolumna1 ,kolumna2 FROM bardzo_duża_tabela WHERE kolumna_która_nie_ma_indeksu is null;
Widok V$SQL, w innych kolumnach, udostępnia wiele innych wartościowych informacji, o komendach SQL. O tym w następnych artykułach.
Mając SQL_ID zapytania, które Cię interesuje łatwo, w ASH, znajdziesz sesje, które je wykonywały. Przeszukujesz ASH wg kolumny SQL_ID. Pozostaje wybranie tej sesji, która interesuje Ciebie. Na przykład wg czasu rozpoczęcia wykonywania komendy SQL przez instancję udostępnionego w kolumnie SQL_EXEC_START.
Jak prześledzić co robiła sesja?
W poprzednich paragrafach pokazałem jak odnaleźć SESSION_ID i SESSION_SERIAL#, które jednoznacznie identyfikują sesję. Gdy masz te informacje to możesz agregować dane z ASH wg nich i dowiedzieć się na które SQL_ID, wykonywane w czasie trwania sesji, instancja poświęciła najwięcej czasu. Czyli, które SQL_ID trwało najdłużej.
Możesz to zrobić w taki sposób:
SELECT sql_exec_start ,sql_id ,count(*) as "czas(s)" FROM v$active_session_history WHERE SESSION_ID=<SESSION_ID interesującej Cię sesji> and SESSION_SERIAL#=<SERIAL# interesującej Cię sesji> GROUP BY sql_exec_start, sql_id ORDER BY sql_exec_start;
W ten sposób wychwycisz wszystkie zapytania uruchamiane na przykład przez procedurę składowaną albo job. Będą posortowane wg czasu ich wykonania. Zawęzisz strojenie z całej procedury do konkretnego zapytania.
Mając wartości SQL_ID możesz odnaleźć tekst komendy SQL razem z planem wykonania wywołując funkcję DISPLAY_CURSOR pakietu DBMS_XPLAN:
SELECT * FROM table(dbms_xplan.display_cursor(sql_id=>'<SQL_ID interesującej Cię komendy SQL>', format=>'ALLSTATS LAST'));
Jak znaleźć zapytania, którym warto się przyjrzeć?
Do strojenia zapytań można podejść bardziej proaktywnie. Czyli nie czekać na zgłoszenie od użytkowników tylko zerknąć na co instancja zużywa najwięcej zasobów.
Zapytania, którym warto się przyjrzeć to takie, które konsumują najwięcej zasobów instancji. Mogą to robić sporadyczne lecz bardzo kosztowne zapytania lub nie tak znowu kosztowne, ale bardzo częste. Jedne i drugie będą pojawiać się często w widoku ASH.
Kiepska wydajność znaczy, że użytkownik długo czeka na wykonanie komendy SQL. Czyli w wielu próbkach – wielu rekordach V$ACTIVE_SESSION_HISTORY – będzie występować ten sam SQL_ID. Wystarczy zagregować dane z ASH wg numeru SQL_ID.
SELECT sql_id ,count(*) as "czas(s)" FROM v$active_session_history WHERE sql_id is not null GROUP BY sql_id ORDER BY count(*) desc;
Jeśli interesuje Cię jedynie ostatnie kilka minut możesz ograniczyć ilość przeszukiwanych próbek wg kolumny SAMPLE_TIME. Na przykład do ostatnich 10 minut.
SELECT sql_id ,count(*) as "czas(s)" FROM v$active_session_history WHERE sql_id is not null and sample_time>sysdate - interval '10' minute GROUP BY sql_id ORDER BY count(*) desc;
Zakres dat, dostępnych próbek, sprawdzisz zapytaniem:
SELECT min(sample_time) ,max(sample_time) FROM v$active_session_history;
Inne narzędzia do przeglądania danych ASH
Dostęp do danych ASH zapewniają narzędzia dostarczane przez Oracle. Najprostszym tekstowym jest ASH Report. Dostępny jako skrypt sql: $ORACLE_HOME/rdbms/admin/ashrpt.sql zainstalowany razem z binariami motoru bazy danych.
Wygeneruje dla Ciebie raport z działania instancji za zadany czas. Zapisze go w pliku tekstowym lub html w katalogu z którego uruchamiasz sqlplus. Może to być maszyna zdalna. Skrypt, w zależności od wybranych przez Ciebie opcji, wywołuje odpowiednią funkcję ASH_% z pakietu DBMS_WORKLOAD_REPOSITORY.
Skrypt możesz uruchomić z poziomu sqlplus komendą: @?/rdbms/admin/ashrpt. W sqlplus znak zapytania zastępuje ORACLE_HOME.
Jeśli robisz to na maszynie zdalnej, gdzie jest zainstalowany tylko klient, to skrypt trzeba skopiować z binariów motoru bazy danych do ORACLE_HOME klienta. Inaczej musisz podać pełną ścieżkę do skryptu.
Więcej na temat generowania raportów ASH w dokumentacji Generating an ASH Report Using the Command-Line Interface.
SQL Developer zapewni dostęp poprzez menu w okienku połączeń dla DBA. Okienko dostępne w menu 'View’.
Jest także niezależny od Oracle produkt ASH Viewer. Potrafi 'udawać’, że ASH jest dostępne. Trzeba pamiętać, że opisana przeze mnie funkcjonalność dostępna jest po opłaceniu licencji Diagnostic and Tuning Pack. Gdy nie masz tej licencji używasz połączenia standard. Gdy masz to enterprise.
Last but not least jest Enterprise Manager. Daje graficzną reprezentację danych z ASH w czasie rzeczywistym.
Skomentuj, który sposób dostępu do danych z ASH jest Tobie najbliższy.
Podsumowanie
Przedstawiłem jeden z podstawowych sposobów wykorzystania danych z widoku V$ACTIVE_SESSION_HISTORY. Więcej informacji na ten temat znajdziesz w dokumentacji Database Performance Tuning Guide.
Oraz w Reference V$ACTIVE_SESSION_HISTORY.
Odpowiednio zagregowane dane ASH pokażą Ci największych konsumentów CPU, IO czy pamięci. O czym w kolejnych artykułach.
Prowadzę szkolenia i kursy z podstaw SQL. Sprawdź ofertę moich kursów SQL.
