Oracle Dead Connection Detection – czyli lekarstwo na osierocone sesje

Problem

Niezrozumiały powolny przyrost sesji bazodanowych, skutkujący ostatecznie błędami ORA.

Rozwiązanie

Włączenie mechanizmu Oracle DCD (Oracle Dead Connection Detection).

Wyjaśnienie

Czym właściwie jest sesja

Sesja najprościej mówiąc to uwierzytelniony użytkownik. Zatem możemy przyjąć, że z punktu widzenia programisty Java, sesja bazodanowa jest jednoznaczna z połączeniem czyli obiektem java.sql.Connection. W bazach danych Oracle można ustawić maksymalną liczbę jednoczesnych sesji bazodanowych przy użyciu parametru sessions, o czym za chwilę niżej.

Objawy

Najczęściej w internecie można spotkać się z informacją, że w wyniku nadmiernego wzrostu sesji bazodanowych otrzymamy komunikat:

ORA-00018 maximum number of sessions exceeded

Prawdą jest też, że często można spotkać się z błędami:

  • Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found
  • Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack

Błędy ORA-12519 oraz ORA-12516 związane są bowiem z drugim bardzo ważnym pojęciem powiązanym z pojęciem sesji, a mianowicie: procesem oraz parametrem processes. Za pomocą tego parametru wyraża się maksymalną liczbę procesów systemu operacyjnego, które mogą jednocześnie działać na bazie danych. Przykładowo aby wykonać zapytanie w ramach jakiejś sesji potrzebny jest proces. Jeżeli baza danych będzie musiała obsługiwać żądania pochodzące od zbyt wielu sesji może najpierw wyrzucić właśnie błędy ORA-12519 oraz ORA-12516  zamiast ORA-00018. Wszystko zależy od konfiguracji bazy danych.

Przyczyny błędów ORA-00018, ORA-12519 oraz ORA-12516

Jest kilka powodów w wyniku których możemy spotkać się z wyżej opisanymi błędami.

Najbardziej trywialny to po prostu źle dobrany parametr określający maksymalną ilość współbieżnych sesji i procesów w bazie danych. Przykładowo maksymalną ilość sesji określiliśmy na 100, a tym czasem z naszej bazy danych jednocześnie chciało skorzystać 101 użytkowników. Zarządzanie liczbą sesji i procesów opisałem poniżej.

Do najczęstszych przyczyn, nie wynikających ze złej konfiguracji bazy danych, należą zwyczajnie błędy programistyczne powodujące wycieki połączeń. Wyciek połączeń to systematyczny przyrost połączeń bazodanowych (sesji). Tym tematem zajmę się jednak w kolejnym artykule.

Takim powodem wreszcie może być bardzo powolny, jednak systematyczny przyrost sesji bazodanowych nie wynikający z błędów w naszym kodzie. Nazwijmy ten problem osieroconymi sesjami. I to właśnie na tym problemie skupię się w tym artykule.

Zarządzanie sesjami i procesami

Aby sprawdzić aktualną liczbę sesji posługujemy się, opisywanymi w poprzednim artykule, widokami dynamicznymi, a konkretnie widokiem GV$SESSION:

  • select inst_id, count(*) from gv$session group by inst_id – w przypadku gdy interesuje nas środowisko RAC
  • select count(*) from gv$session – jeżeli wiemy, że nie działamy w środowisku RAC

Aby sprawdzić maksymalną możliwą liczbę sesji, możemy zrobić to na dwa sposoby, albo z wykorzystaniem widoku dynamicznego GV$PARAMETER:

  • select name, value from gv$parameter where name = ‚sessions’

albo z wykorzystaniem polecenia:

  • show parameter sessions

W pierwszym przypadku możemy otrzymać wyniki w nieco czytelniejszej formie. Ponadto widok GV$PARAMETER zawiera kilka dodatkowych ciekawych informacji.

Poniżej podaję sposób zmiany parametru sessions (jedynie jako wskazówkę, moim celem nie jest opisywanie tego polecenia):

alter system set sessions=300 scope=spfile

Następnie trzeba zrestartować bazę danych. Należy mieć świadomość, że poprawne skonfigurowanie parametru sessions w zależności od naszych potrzeb nie jest wystarczające. W parze z parametrem sessions zawsze stoi parametr processes (o czym wspomniałem wyżej). Wszędzie w instrukcjach bazodanowych, w powyższym paragrafie, gdzie użyłem nazwy parametru sessions, można użyć nazwy processes aby dowiedzieć się o konfigurację procesów. Prawidłowy sposób dobrania tych dwóch parametrów został opisany tutaj.

Kiedy najczęściej występują problemy osieroconych sesji 

Z błędami osieroconych sesji prawdopodobnie nie będzie dane wam się zetknąć, ale jeżeli już się na nie natkniecie mogą stanowić one całkiem ciekawą zagadkę. Jeżeli wasze środowisko pracy z bazą danych Oracle to serwer aplikacyjny poprzez który nawiązywane są wszystkie połączenia do bazy danych, a do tego ten serwer znajduje się w sieci lokalnej z bazą danych, możecie być prawie pewni, że problem Was nie spotka. Częściej z opisywanymi problemami możemy mieć do czynienia w przypadkach gdy do serwera bazy danych podłączają się różni klienci z maszyn o różnej architekturze sprzętowej czy programowej.

Sprawa wygląda tak. W przypadku zwykłych wycieków sesji (wynikających z błędów programistycznych) restart aplikacji (gdzie przez aplikację rozumiem program, który nawiązuje połączenia do bazy danych) sprawia, że liczba sesji w bazie danych wraca do stanu wyjściowego. W przypadku osieroconych sesji tak się nie dzieje.

Czym jest mechanizm DCD i jak go używać

Moim zdaniem najpełniejszy opis mechanizmu DCD można znaleźć w materiale: Dead Connection Detection (DCD) Explained. Aby przeczytać artykuł trzeba się zalogować, jednak założenie konta jest zupełnie darmowe. Poniżej przytoczę najważniejsze fakty o mechanizmie DCD (moim celem nie jest przepisywanie wiedzy, a jedynie jej zebranie i uporządkowanie).

DCD to cecha Oracle, która pojawiła się w SQL*Net > 2.1 (SQL*Net w skrócie można określić jako warstwę łączności pomiędzy klientami a bazą danych). Mechanizm ten pozwala na wykrywanie połączeń, które zostały zakończone w niepoprawny sposób. DCD oznacza takie połączenia jako martwe tak aby proces PMON mógł je wyczyścić. Wydaje się to zaskakujące, ale Oracle nie radzi sobie z takimi połączeniami. Generalnie takie połączenia (przynajmniej mi) udało się uzyskać jedynie poprzez wyciągnięcie kabla sieciowego z komputera. Połączenia były prawidłowo zamykane w takich sytuacjach jak: hibernacja, stan wstrzymania, kill procesu java. Zresztą w opisie mechanizmu DCD można znaleźć uwagę, że na większości OS’ów jeżeli proces zakończy się w nieprzewidziany sposób np. poprzez kill, OS i tak wyczyści poprawnie zasoby związane z tym procesem, włączając w to połączenia sieciowe.

DCD przewidziany jest dla środowisk klient-serwer, gdzie klienci mogą nie odłączać się prawidłowo (z różnych przyczyn) od sesji bazodanowych, tylko w jakiś sposób przerywają połączenie np. nagłe zamknięcie systemu. DCD jest pewnym obciążeniem dla bazy danych (monitorowanie stanu połączeń), dlatego jest domyślnie wyłączone. Jeżeli nie spotykacie się z problemem osieroconych sesji, nie ma sensu włączanie mechanizmu DCD.

DCD uruchamia się i konfiguruje po stronie serwera bazy danych. Konfiguracja odbywa się poprzez określenie parametru SQLNET.EXPIRE_TIME w pliku sqlnet.ora. Parametr wyraża się w minutach, zalecana wartość to 10 minut, jednak: UWAGA! w bazach danych ORACLE do wersji 11.2 jedyna możliwa wartość tego parametru to 1. Wszystkie inne wartości są ignorowane (opis tego błędu można znaleźć tutaj). Aby zmiana w pliku sqlnet.ora została uwzględniona należy zrestartować listenera i bazę danych.

Po włączeniu DCD, jeżeli na danym połączeniu nie były w określonym okresie czasu wykonywane żadne operacja I/O, DCD wysyła do klienta, który nawiązał to połączenie, małą paczką danych tzw. „próbkę” (pusty pakiet SQL*Net). Jeżeli klient odpowie, DCD resetuje zegar i za określony czas ponownie spróbuje sprawdzić połączenie, jeżeli natomiast klient nie odpowie DCD oznacza połączenie jako martwe.

Ważne jednak jest, że  ustawienie DCD np. SQLNET.EXPIRE_TIME=1 nie oznacza, że nieaktywne nieużywane sesje zostaną zamknięte po minucie, mechanizm DCD zależny jest od timeoutów w protokole TCP/IP.

Opis tego jak monitorować czy mechanizm DCD działa prawidłowo można znaleźć tutaj.

Oracle Dynamic Performance Views – czyli widoki V$ oraz GV$

Problem

Niezrozumiałe wyniki podczas korzystania z widoków dynamicznych V$.

Rozwiązanie

Zawsze staraj się używać widoków GV$, nawet gdy nie działasz w środowisku RAC, wówczas gdy będziesz działał w środowisku RAC nie będziesz musiał zmieniać swoich przyzwyczajeń i nie będziesz zastanawiał się dlaczego coś się nie zgadza.

Wyjaśnienie

Niejednokrotnie w swojej pracy muszę badać pewne problemy z którymi mam do czynienia w ramach baz danych Oracle (mówiąc dalej Oracle będę miał na myśli serwer bazy danych Oracle). Przeważnie zawsze wówczas sięgam do tzw. widoków V$, w tym chyba najpopularniejszego: V$SESSION.

Czym są widoki V$

Bardzo skrótowo mówiąc, Oracle posiada szereg wirtualnych tabel, w których zapisuje swój stan. Nazwy tych tabel rozpoczynają się od prefiksu V_$. Na bazie tych tabel tworzone są widoki, których nazwy prefiksowane są V$. Widoki te nazywają się dynamicznymi ponieważ dane, które reprezentują są nieustannie aktualizowane.

Kiedyś badając pewne problemy u Klienta przez chwilę nie mogłem zorientować się w sytuacji, gdyż uzyskiwane przeze mnie wyniki wydawały się być bez sensu. Szybko jednak połączyłem te dziwne wyniki z faktem, że Klient był w posiadaniu klastra RACowego (Oracle RAC = Real Application Clusters). Otóż (nie wchodząc w szczegóły konfiguracji klastra i jego właściwości) nawiązywane przeze mnie połączenia, Oracle losowo (równoważąc obciążenie) przekierowywał na różne węzły klastra, a jedną z podstawowych cech widoków V$ jest fakt dostarczania informacji z tej instancji bazy danych (węzła) do której jesteśmy podłączeni.

Jak zatem poradzić sobie z taką sytuacją? Dla prawie każdego widoku V$, istnieje jego odpowiednik, widok prefiksowany GV$. Zapytanie zadane do widoku GV$ pobiera informacje ze wszystkich instancji bazy danych składających się na klaster. Widoki GV$ różnią się w swej budowie od widoków V$ jedynie dodatkową kolumną INST_ID. Jest to identyfikator węzła klastra.

Materiały

Materiały Oracle dotyczące tego tematu można znaleźć w artykule Overview of the Dynamic Preformance Views. Opis wszystkich dynamicznych widoków Oracle można znaleźć tutaj.