Oracle Automatic Indexing to jedna z najbardziej zaawansowanych technologii samodzielnej optymalizacji wydajności SQL. Po kilkudziesięciu latach ręcznego tworzenia indeksów przyznać muszę, że mechanizm ten robi wrażenie; pytanie brzmi, czy naprawdę możemy mu zaufać w środowisku produkcyjnym.
- Automatic Indexing samodzielnie identyfikuje, tworzy i testuje indeksy bez ingerencji administratora
- Mechanizm wykorzystuje indeksy niewidoczne (Invisible Indexes) do bezpiecznej walidacji zmian
- Oracle automatycznie usuwa indeksy, które przestają przynosić korzyści wydajnościowe
- Technologia nie zastępuje DBA, ale znacząco zmienia charakter jego pracy
Dlaczego indeksowanie jest jednym z najtrudniejszych problemów optymalizacji
Każdy doświadczony DBA wie, że projektowanie indeksów to sztuka balansowania między sprzecznymi wymaganiami. Z jednej strony mamy zapytania SELECT, które kochają indeksy. Z drugiej operacje DML, dla których każdy dodatkowy indeks oznacza narzut na utrzymanie struktur B-tree.
Problem komplikuje się, gdy w systemie działają setki różnych zapytań. Jedno zapytanie potrzebuje indeksu na kolumnach (A, B, C), inne na (A, C, B), jeszcze inne tylko na (A, B). Czy tworzymy trzy indeksy? A może jeden kompozytowy i liczymy na Index Skip Scan? Te decyzje wymagają głębokiej znajomości optymalizatora Oracle oraz specyfiki konkretnego środowiska.
Dodatkowym wyzwaniem jest zmienność wzorców dostępu. Aplikacje ewoluują, pojawiają się nowe funkcjonalności, zmieniają się proporcje między różnymi typami operacji. Indeks doskonały w styczniu może być bezużyteczny w czerwcu.
Architektura Oracle Automatic Indexing
Automatic Indexing pojawił się w Oracle Database 19c jako element szerszej inicjatywy Autonomous Database. Mechanizm działa jako zadanie w tle (background task), które uruchamia się domyślnie co 15 minut w trybie AUTO lub na żądanie administratora.
Podstawowy przepływ pracy wygląda następująco: system analizuje zapytania SQL przechowywane w Automatic Workload Repository (AWR) oraz SQL Tuning Sets, identyfikuje kandydatów na indeksy, tworzy je jako niewidoczne (INVISIBLE), testuje ich skuteczność w izolowanym kontekście, a następnie podejmuje decyzję o ich publikacji lub odrzuceniu.
Konfiguracja mechanizmu odbywa się przez pakiet DBMS_AUTO_INDEX. Podstawowe tryby pracy to OFF (wyłączony), REPORT ONLY (tylko rekomendacje bez tworzenia indeksów) oraz IMPLEMENT (pełna automatyzacja). W środowiskach produkcyjnych często zaczynam od REPORT ONLY, żeby najpierw zrozumieć, jakie decyzje podejmowałby algorytm.
Kluczowe komponenty techniczne
Automatic Indexing bazuje na kilku fundamentalnych elementach Oracle Database. Po pierwsze, wykorzystuje SQL Plan Management do porównywania planów wykonania. Po drugie, korzysta z mechanizmu Invisible Indexes wprowadzonego w wersji 11g. Po trzecie, integruje się z SQL Tuning Advisor, który dostarcza rekomendacje dotyczące potencjalnych indeksów.
Proces identyfikacji kandydatów na indeksy
Oracle analizuje obciążenie SQL pod kątem zapytań, które wykonują pełne skanowanie tabel (Full Table Scan) lub nieoptymalne skanowanie indeksów. System bierze pod uwagę częstotliwość wykonywania zapytania, jego koszt oraz potencjalny zysk z utworzenia indeksu.
Algorytm jest sprytniejszy niż mogłoby się wydawać. Nie proponuje indeksu dla zapytania wykonywanego raz dziennie, które skanuje tabelę 1000 wierszy. Koncentruje się na zapytaniach o wysokim łącznym koszcie; czyli takich, które albo są bardzo drogie jednostkowo, albo wykonywane masowo.
W mojej praktyce zauważyłem, że Automatic Indexing szczególnie dobrze radzi sobie z wykrywaniem brakujących indeksów na kolumnach używanych w klauzulach WHERE z operatorami równości. Gorzej wypada przy złożonych predykatach z funkcjami lub konwersjami typów, gdzie problem leży nie w braku indeksu, ale w konstrukcji zapytania.
Invisible Indexes jako fundament bezpieczeństwa
Mechanizm Invisible Indexes to klucz do bezpiecznego testowania zmian. Indeks niewidoczny istnieje fizycznie, jest utrzymywany przy każdej operacji DML, ale optymalizator domyślnie go ignoruje podczas generowania planów wykonania.
Automatic Indexing tworzy każdy nowy indeks jako INVISIBLE. Następnie uruchamia testowe wykonanie problematycznych zapytań z wymuszonym użyciem nowego indeksu (poprzez hint lub zmianę parametru sesji OPTIMIZER_USE_INVISIBLE_INDEXES). Porównuje czasy wykonania i plany, a dopiero po pozytywnej walidacji zmienia status indeksu na VISIBLE.
To podejście eliminuje ryzyko regresji wydajności. W tradycyjnym modelu utworzenie indeksu natychmiast wpływało na wszystkie sesje; czasem z katastrofalnymi skutkami, gdy optymalizator wybierał nowy, teoretycznie lepszy plan, który w praktyce okazywał się gorszy.
Kryteria akceptacji i odrzucania indeksów
Oracle stosuje konserwatywne podejście do akceptacji indeksów. Aby indeks został opublikowany (zmieniony na VISIBLE), musi spełniać kilka warunków. Przede wszystkim musi wykazać mierzalną poprawę wydajności; domyślnie wymagana jest redukcja kosztu wykonania o co najmniej 3x. Ponadto poprawa musi być stabilna w wielu wykonaniach testowych.
System śledzi również wykorzystanie opublikowanych indeksów. Jeśli indeks przestaje być używany przez określony czas (domyślnie 373 dni), zostaje oznaczony jako kandydat do usunięcia. To elegancko rozwiązuje problem zaśmiecania bazy nieużywanymi indeksami.
Typy tworzonych indeksów
Automatic Indexing tworzy wyłącznie indeksy B-tree, zarówno jednokolumnowe jak i kompozytowe. Nie obsługuje indeksów bitmapowych, funkcyjnych ani domenowych. To świadoma decyzja projektowa; indeksy B-tree są najbezpieczniejsze i najbardziej uniwersalne.
Monitorowanie i raporty
Oracle dostarcza bogaty zestaw narzędzi do monitorowania Automatic Indexing. Podstawowym źródłem informacji jest widok DBA_AUTO_INDEX_CONFIG pokazujący aktualną konfigurację oraz DBA_AUTO_INDEX_EXECUTIONS prezentujący historię wykonań zadania.
Najcenniejszy jest jednak raport generowany przez funkcję DBMS_AUTO_INDEX.REPORT_ACTIVITY. Pokazuje on szczegółowo, jakie indeksy zostały rozważone, utworzone, opublikowane lub odrzucone wraz z uzasadnieniem każdej decyzji.
Przykładowe wywołanie generujące raport z ostatnich 24 godzin:
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP - 1, SYSTIMESTAMP, 'TEXT', 'ALL', 'ALL') FROM DUAL;
Praktyczne doświadczenia z produkcji
W ciągu ostatnich trzech lat wdrażałem Automatic Indexing w kilkunastu środowiskach produkcyjnych. Wyniki są mieszane, ale generalnie pozytywne.
Najlepsze rezultaty obserwowałem w systemach OLTP z dużą liczbą prostych zapytań ad-hoc. W jednym przypadku mechanizm utworzył 47 indeksów w ciągu pierwszego miesiąca, redukując średni czas odpowiedzi kluczowych transakcji o 60%.
Gorzej sprawdza się w środowiskach z dominacją złożonych zapytań analitycznych lub w bazach z bardzo dynamicznym schematem. W hurtowniach danych, gdzie przeważają zapytania z wieloma złączeniami i agregacjami, tradycyjne projektowanie indeksów wciąż daje lepsze rezultaty.
Ograniczenia, o których warto wiedzieć
Automatic Indexing ma istotne ograniczenia techniczne. Nie działa na tabelach tymczasowych, zewnętrznych ani w schematach SYS i SYSTEM. Nie obsługuje tabel z wirtualnymi kolumnami prywatnymi ani klastrów indeksowych.
Poważniejszym ograniczeniem jest brak wsparcia dla indeksów funkcyjnych. Jeśli zapytania używają wyrażeń typu UPPER(kolumna) lub TRUNC(data), mechanizm nie zaproponuje odpowiedniego indeksu funkcyjnego. Tu nadal potrzebna jest interwencja DBA.
Mechanizm nie radzi sobie również z problemami wynikającymi ze skośności danych (data skew). Może utworzyć indeks, który świetnie działa dla większości wartości, ale powoduje katastrofalne plany dla wartości popularnych.
Wpływ na rolę administratora baz danych
Automatic Indexing nie zastępuje DBA; zmienia jednak charakter jego pracy. Mniej czasu spędzamy na ręcznym tworzeniu indeksów dla typowych przypadków. Więcej na analizie raportów, walidacji decyzji automatycznych oraz rozwiązywaniu problemów, z którymi mechanizm sobie nie radzi.
Rola DBA ewoluuje w kierunku nadzorcy i architekta. Zamiast ręcznie strojić każde zapytanie, definiujemy polityki, monitorujemy zachowanie algorytmu i interweniujemy w przypadkach brzegowych.
Porównuję to do ewolucji w innych dziedzinach IT. Automatyczne skalowanie w chmurze nie wyeliminowało administratorów systemów; zmieniło ich pracę z ręcznego zarządzania serwerami na projektowanie architektur i definiowanie polityk. Podobnie Automatic Indexing przesuwa DBA na wyższy poziom abstrakcji.
Rekomendacje wdrożeniowe
Na podstawie doświadczeń praktycznych rekomenduję stopniowe podejście do wdrażania Automatic Indexing. W pierwszym etapie włączamy tryb REPORT ONLY na 2 do 4 tygodni. Analizujemy rekomendacje i porównujemy je z własną wiedzą o systemie.
W drugim etapie przechodzimy na tryb IMPLEMENT, ale z restrykcyjnymi wykluczeniami. Wyłączamy automatyczne indeksowanie dla schematów, które znamy dobrze i które mają stabilną, zoptymalizowaną strukturę indeksów.
Regularnie przeglądamy raporty. Szczególną uwagę zwracamy na indeksy odrzucone; czasem wskazują one na głębsze problemy z konstrukcją zapytań lub modelem danych.