Pojedyncze źle napisane zapytanie potrafi położyć całe środowisko produkcyjne w kilka sekund. SQL Quarantine to mechanizm Oracle, który automatycznie wykrywa i izoluje takie zapytania zanim zdążą wyrządzić szkody.
- SQL Quarantine współpracuje z Resource Managerem i wymaga aktywnych dyrektyw ograniczających zużycie zasobów
- Mechanizm działa na poziomie SQL_ID i PLAN_HASH_VALUE, co oznacza że ta sama kwerenda z innym planem może być traktowana odmiennie
- Kwarantanna jest trwała i przeżywa restarty instancji, ponieważ konfiguracje są przechowywane w SMB
- Funkcjonalność dostępna jest wyłącznie w Oracle Database 19c i nowszych w edycji Enterprise z opcją Diagnostics Pack
Problem runaway queries w środowiskach produkcyjnych
Przez dwadzieścia lat pracy z Oracle widziałem dziesiątki sytuacji, w których pojedyncze zapytanie SQL doprowadziło do całkowitego paraliżu systemu produkcyjnego. Najczęściej scenariusz wygląda podobnie: ktoś uruchamia raport bez odpowiednich filtrów, optymalizator wybiera katastrofalny plan wykonania po zbieraniu statystyk, albo programista testuje nową funkcjonalność na produkcji.
Runaway query to zapytanie, które zużywa zasoby w sposób niewspółmierny do oczekiwanych rezultatów. Może to być kwerenda wykonująca full table scan na tabeli z miliardami wierszy, zapytanie generujące gigabajty danych tymczasowych w tablespace TEMP, lub operacja blokująca CPU na 100% przez kilkadziesiąt minut.
Skutki są zawsze podobne: pozostali użytkownicy doświadczają timeoutów, aplikacje przestają odpowiadać, a telefon DBA rozgrzewa się do czerwoności. W najgorszych przypadkach trzeba wykonać kill session lub nawet restart instancji.
Czym jest SQL Quarantine
SQL Quarantine to mechanizm wprowadzony w Oracle Database 19c, który automatycznie identyfikuje zapytania przekraczające zdefiniowane limity zasobów i blokuje ich ponowne wykonanie. Kluczowe jest słowo automatycznie, ponieważ dotychczas DBA musiał ręcznie reagować na takie sytuacje.
Architektura SQL Quarantine opiera się na ścisłej współpracy z Resource Managerem. Gdy zapytanie zostanie zabite przez dyrektywę Resource Managera z powodu przekroczenia limitów, Oracle automatycznie tworzy konfigurację kwarantanny w SQL Management Base (SMB). Od tego momentu każda próba uruchomienia tego samego zapytania kończy się natychmiastowym błędem, zanim Oracle zdąży zużyć jakiekolwiek zasoby.
Mechanizm działa na poziomie kombinacji SQL_ID oraz PLAN_HASH_VALUE. To istotne rozróżnienie, ponieważ to samo zapytanie może być bezpieczne z jednym planem wykonania, ale katastrofalne z innym.
Jak Oracle identyfikuje niebezpieczne zapytania
Proces identyfikacji zapytań kwalifikujących się do kwarantanny rozpoczyna się od Resource Managera. Musisz mieć aktywny resource plan z dyrektywami definiującymi limity dla grup konsumentów. Najczęściej wykorzystywane parametry to:
- ELAPSED_TIME: maksymalny czas wykonania zapytania w sekundach
- CPU_TIME: limit czasu CPU zużywanego przez sesję
- IO_MEGABYTES: maksymalna ilość danych I/O
- LOGICAL_IO: limit operacji logicznego I/O
Gdy zapytanie przekracza którykolwiek z tych limitów, Resource Manager podejmuje akcję zdefiniowaną w dyrektywie. Może to być przełączenie do innej grupy konsumentów, zabicie sesji, lub anulowanie bieżącego zapytania. Dopiero ta ostatnia akcja (CANCEL_SQL lub KILL_SESSION) uruchamia mechanizm SQL Quarantine.
Z mojego doświadczenia wynika, że najskuteczniejsze jest ustawienie limitów na ELAPSED_TIME w połączeniu z akcją CANCEL_SQL. Daje to użytkownikowi szansę na poprawienie zapytania bez utraty całej sesji, jednocześnie chroniąc system przed przeciążeniem.
SQL Quarantine i Resource Manager
Zależność między SQL Quarantine a Resource Managerem jest fundamentalna i często źle rozumiana. SQL Quarantine nie jest samodzielnym mechanizmem ochronnym. Nie można go włączyć bez aktywnego Resource Managera z odpowiednio skonfigurowanymi dyrektywami.
Typowa konfiguracja wygląda następująco:
Najpierw tworzysz pending area dla Resource Managera, definiujesz grupy konsumentów (np. OLTP_USERS, REPORT_USERS, BATCH_JOBS), następnie tworzysz resource plan z dyrektywami określającymi limity dla każdej grupy. Na końcu aktywujesz plan poprzez parametr RESOURCE_MANAGER_PLAN.
Dyrektywa dla grupy raportowej mogłaby wyglądać tak: maksymalnie 300 sekund elapsed time, maksymalnie 10000 MB I/O, z akcją CANCEL_SQL po przekroczeniu limitu. Zapytania z tej grupy, które przekroczą limity, zostaną anulowane i automatycznie trafią do kwarantanny.
Automatyczna izolacja SQL
Po umieszczeniu zapytania w kwarantannie każda kolejna próba jego wykonania kończy się błędem ORA-56955: quarantined plan used. Błąd pojawia się natychmiast, w fazie parsowania, zanim Oracle zacznie wykonywać jakiekolwiek operacje I/O czy zużywać CPU.
Konfiguracje kwarantanny są przechowywane w SQL Management Base, tym samym repozytorium co SQL Plan Baselines i SQL Profiles. Oznacza to, że przeżywają restarty instancji i są replikowane w środowiskach Data Guard.
Oracle dostarcza pakiet DBMS_SQLQ do ręcznego zarządzania kwarantannami. Możesz tworzyć kwarantanny prewencyjnie (zanim zapytanie wyrządzi szkody), usuwać istniejące kwarantanny po naprawieniu problemu, oraz eksportować i importować konfiguracje między środowiskami.
Co dzieje się z zapytaniem po umieszczeniu w kwarantannie
Użytkownik próbujący uruchomić zapytanie objęte kwarantanną otrzymuje błąd ORA-56955. Komunikat zawiera informację o SQL_ID oraz przyczynę umieszczenia w kwarantannie (np. exceeded CPU time limit).
Z perspektywy aplikacji wygląda to jak zwykły błąd SQL, który można obsłużyć standardowymi mechanizmami exception handling. Aplikacje dobrze napisane powinny przechwycić ten błąd i wyświetlić użytkownikowi zrozumiały komunikat.
DBA widzi zdarzenie w widoku V$SQL_QUARANTINE oraz w alertlog. Ma pełną informację o tym, kiedy zapytanie trafiło do kwarantanny, ile razy próbowano je uruchomić po zablokowaniu, oraz jakie limity zostały przekroczone.
Monitorowanie SQL Quarantine
Podstawowym źródłem informacji jest widok DBA_SQL_QUARANTINE, który pokazuje wszystkie aktywne konfiguracje kwarantanny. Znajdziesz tam SQL_ID, PLAN_HASH_VALUE, datę utworzenia, przyczynę kwarantanny oraz status (ENABLED lub DISABLED).
Widok V$SQL zawiera kolumnę SQL_QUARANTINE, która wskazuje nazwę konfiguracji kwarantanny dla danego zapytania. Przydatne jest również V$RSRC_SESSION_INFO, pokazujące bieżące zużycie zasobów przez sesje w kontekście Resource Managera.
Do regularnego monitorowania polecam utworzenie prostego raportu pokazującego nowe kwarantanny z ostatnich 24 godzin. Każda nowa kwarantanna powinna być sygnałem do analizy; może wskazywać na problem z aplikacją, zmianę w danych lub błędną konfigurację limitów.
SQL Quarantine a plany wykonania
To jeden z najbardziej interesujących aspektów mechanizmu. SQL Quarantine blokuje konkretną kombinację SQL_ID i PLAN_HASH_VALUE. Jeśli optymalizator wybierze inny plan dla tego samego zapytania, kwarantanna nie zadziała.
W praktyce oznacza to, że problem może powrócić po:
- Zebraniu nowych statystyk obiektów
- Flush shared pool
- Zmianie parametrów optymalizatora
- Utworzeniu lub usunięciu indeksów
Dlatego SQL Quarantine traktuję jako mechanizm doraźnej ochrony, a nie trwałe rozwiązanie problemu. Po umieszczeniu zapytania w kwarantannie należy przeprowadzić analizę i naprawić rzeczywistą przyczynę; czy to przez poprawienie kodu SQL, dodanie indeksu, czy utworzenie SQL Plan Baseline wymuszającego optymalny plan.
Ochrona środowisk produkcyjnych
SQL Quarantine sprawdza się szczególnie dobrze w środowiskach z dużą liczbą użytkowników ad hoc, takich jak systemy raportowe czy hurtownie danych. Użytkownicy biznesowi często nie zdają sobie sprawy z konsekwencji swoich zapytań, a SQL Quarantine stanowi ostatnią linię obrony.
W systemach ERP takich jak SAP czy Oracle E-Business Suite mechanizm pomaga chronić przed źle zoptymalizowanymi customowymi raportami. W środowiskach deweloperskich z dostępem do danych produkcyjnych chroni przed przypadkowym uruchomieniem zapytań na pełnym wolumenie danych.
Kluczowe jest odpowiednie dobranie limitów. Zbyt restrykcyjne limity spowodują, że użytkownicy będą masowo trafiać na błędy kwarantanny. Zbyt luźne nie ochronią systemu przed rzeczywiście niebezpiecznymi zapytaniami. Zalecam rozpoczęcie od konserwatywnych limitów (np. 600 sekund elapsed time) i stopniowe dostrajanie na podstawie obserwacji.
Praktyczne przypadki z produkcji
Jeden z najbardziej pamiętnych przypadków dotyczył systemu finansowego, gdzie użytkownik uruchomił raport uzgodnieniowy bez podania zakresu dat. Zapytanie zaczęło przetwarzać dane z dziesięciu lat, generując kilkaset gigabajtów operacji I/O. Resource Manager anulował zapytanie po przekroczeniu limitu 500 sekund, a SQL Quarantine automatycznie zablokowało kolejne próby.
Dzięki temu ten sam użytkownik, próbując uruchomić raport ponownie (co jest naturalnym odruchem), otrzymał natychmiastowy błąd zamiast kolejnego przeciążenia systemu. DBA miał czas na kontakt z użytkownikiem i wyjaśnienie problemu, a pozostali użytkownicy pracowali bez zakłóceń.
Ograniczenia technologii
SQL Quarantine nie jest panaceum na wszystkie problemy wydajnościowe. Mechanizm nie pomoże, gdy:
- Problem wynika z blokad (locking), a nie zużycia zasobów
- Zapytanie mieści się w limitach, ale i tak jest nieoptymalne
- Resource Manager nie jest aktywny lub jest źle skonfigurowany
- Używasz edycji Standard Edition (funkcjonalność wymaga Enterprise Edition)
Dodatkowo SQL Quarantine wymaga licencji Diagnostics Pack, co zwiększa koszt całkowity rozwiązania. W środowiskach z wieloma różnymi zapytaniami ad hoc możesz szybko zgromadzić setki konfiguracji kwarantanny, co wymaga regularnego przeglądu i czyszczenia.
Przyszłość automatycznej ochrony baz danych
SQL Quarantine to element szerszego trendu automatyzacji w Oracle Database. Autonomous Database rozszerza tę koncepcję, automatycznie dostrajając nie tylko kwarantanny, ale również indeksy, statystyki i parametry systemu.
Dla tradycyjnych środowisk on premise SQL Quarantine pozostaje jednym z najbardziej praktycznych mechanizmów ochronnych wprowadzonych w ostatnich wersjach. Wymaga minimalnej konfiguracji (jeśli już masz Resource Managera), działa automatycznie i skutecznie chroni przed powtarzającymi się problemami.