Para w gwizdek, albo niepotrzebne indeksy

Bohdan Szymczak

Nieodłącznym elementem projektu tabel w bazie danych, zgodnej z modelem relacyjnym, są indeksy. Spełniają one szereg ważnych funkcji, takich jak na przykład wymuszenie więzów unikalności na kolumnach tworzących klucz główny; jednak pierwszoplanowym zastosowaniem pozostaje skrócenie czasu dostępu do danych.

Odszukanie potrzebnego wiersza w tabeli, spełniającego zadany warunek jest wielokrotnie szybsze dzięki użyciu odpowiedniego indeksu. Ta zasada znana jest nawet początkującym programistom, dlatego też wszechobecne w bazach danych indeksy zakładane się zwykle równocześnie z tworzeniem tabel. Prawidłową praktyką jest przewidywanie sposobu dostępu do danych i projektowanie oraz zakładanie indeksów jeszcze przed uruchomieniem aplikacji.

W fazie eksploatacji, szczególnie w okresie pierwszego dużego przyrostu danych, na ogół pojawiają się problemy wydajnościowe, zauważalne dla użytkownika jako spowolnienie pracy aplikacji. Analiza planów wykonań problematycznych zapytań SQL prowadzi często do wniosków, że wobec dużego przyrostu danych lub powstania określonego rozkładu wartości, możliwe byłoby skonstruowanie lepszego planu wykonania, pod warunkiem możliwości skorzystania z indeksu, którego w bazie nie ma. W takim przypadku klasyczną zasadą jest dodanie brakującego indeksu do bazy.

To rozwiązanie jest szczególnie wygodne, gdyż nie wymaga modyfikacji kodu aplikacji, zaś optymalizator kosztu Oracle użyje nowo utworzony indeks w sposób automatyczny. Jedynym problemem, który należy przewidzieć jest czas wymagany na utworzenie nowego indeksu. W przypadku tabel o bardzo dużej liczbie wierszy, taka operacja może trwać nawet kilka godzin. Używając maszyn wieloprocesorowych, można ten czas skrócić używając opcji parallel, co umożliwi utworzenie indeksu przy pomocy równoległych procesów.

Praktyka pokazuje, że aplikacje eksploatowane w dłuższym okresie czasu, rzędu kilku lat, mają tendencję do rozbudowy liczby używanych indeksów. Nie jest niczym nadzwyczajnym, że rozmiar miejsca w bazie alokowanego dla indeksów, często przekracza rozmiar używany dla tabel. O ile dziś wielkość bazy danych nie stanowi już fundamentalnego problemu, co jest wynikiem dynamicznego rozwoju macierzy dyskowych, to problem kosztu utrzymania indeksów w bazie jest ciągle istotny.

Dwa główne problemy związane z utrzymaniem indeksów w bazie to:

  • Koszt wydajnościowy związany z rozbudową i wyważaniem drzew indeksowych podczas modyfikacji kolumn tabel wchodzących w skład klucza indeksu;
  • Wymagany czas i moc serwera bazy danych na aktualizację statystyk indeksów, których aktualność jest warunkiem poprawnej pracy optymalizatora kosztu.

W przypadku mocno obciążonych systemów bazodanowych wymienione problemy mogą stanowić zauważalny koszt pracy systemu i co za tym idzie, powinny być brane pod uwagę przez administratorów bazy.

Zjawiskiem, które dość często występuje w takich systemach, jest pominięcie przez optymalizator kosztu Oracle, istniejących indeksów przy budowie planów wykonań. Wzrost liczby wierszy w tabelach, zmiana rozkładu wartości danych, modyfikacje funkcjonalne aplikacji to podstawowe przyczyny, dla których indeksy, które wcześniej były używane, po pewnym okresie eksploatacji są nieużywane, a co tym idzie stają się niepotrzebne.

Racjonalnym działaniem w takim przypadku, powinno być usunięcie indeksów, które nie pełnią żadnej roli funkcjonalnej i nie są brane pod uwagę przez optymalizator kosztu podczas budowy planów wykonania. Podjęcie decyzji o usunięciu indeksu musi jednak być rozważne i przemyślane. Niewłaściwe wytypowanie kandydata do usunięcia z bazy spowoduje natychmiastowe załamanie wydajnościowe dla zapytania, które go potrzebuje, zaś czas potrzebny na ponowną odbudowę indeksu, jak wspomniano wcześniej, może stanowić problem eksploatacyjny.

Rozwiązaniem, które wspiera producent bazy Oracle od wersji 9i jest monitoring użycia indeksów. Jest to prosty w zastosowaniu mechanizm, który rejestruje w bazie danych fakt użycia określonego przez administratora indeksu.

Włączenie monitorowania indeksu odbywa się przy pomocy polecenia:

ALTER INDEX <index> MONITORING USAGE;

zaś wyłączenie przy pomocy polecenia:

ALTER INDEX <index> NOMONITORING USAGE;

Sprawdzenie, czy indeks był użyty przez optymalizator kosztu do budowy planu wykonania, odbywa się przez odpytywanie perspektywy dynamicznej V$OBJECT_USAGE, o następujące strukturze:

SQL> desc V$OBJECT_USAGE
Nazwa Wartość NULL?    Typ
------------------   -------------- ----------------
INDEX_NAME           NOT NULL VARCHAR2(30)
TABLE_NAME           NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

Kolumna MONITORING wskazuje, czy dany indeks jest monitorowany, zaś kolumna USED pokazuje, czy od momentu włączenia obserwacji indeks był użyty przynajmniej jeden raz. Obie kolumny przyjmują jedną z dwóch wartości: „YES” lub „NO”. Dokładny czas włączenia i wyłączenia monitoringu jest widoczny odpowiednio w kolumnach: START_MONITORING i END_MONITORING.

Czas, na jaki należy włączyć monitoring na indeksach, musi być wystarczająco długi, by wnioskowanie o zasadności usunięcia indeksu było pewne. Najkrótszy okres czasu wyznacza pełen cykl eksploatacyjny aplikacji. Dla wielu z nich może to być okres jednego miesiąca, ale należy zawsze pamiętać o pewnych charakterystycznych datach, na przykład końcu roku, gdy systemy księgowe zwykle wykonują przetwarzanie, które nie jest wykonywane w pozostałym czasie.

Jak to zwykle w Oracle bywa, każdy mechanizm kryje w sobie pewne niuanse i niespodzianki, o których warto wiedzieć, by z dostarczonego mechanizmu korzystać w sposób optymalny.

Pierwszą niespodzianką jest nieco dziwna definicja perspektywy V$OBJECT_USAGE, która we frazie where zawiera klauzulę sys.obj$.owner = userenv(‚SCHEMAID’), co skutkuje tym, że perspektywa pokazuje wyłącznie indeksy schematu użytkownika, który ją ogląda. W szczególności administrator bazy danych, używający konta SYS nie zobaczy żadnego indeksu aplikacji, która zgodnie z zasadami prawidłowej alokacji obiektów, składuje indeksy w schematach przewidzianych przez projektanta. Schematy te zawsze są różne od schematu SYS.

Aby uniknąć tego typu pomyłek administrator bazy danych powinien zrezygnować z używania perspektywy V$OBJECT_USAGE, na rzecz poniższego zapytania:

select u.name "OWNER", io.name "INDEX_NAME", t.name "TABLE_NAME",
        decode(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",
        decode(bitand(ou.flags, 1), 0, 'NO', 'YES') "USED",
        ou.start_monitoring,
        ou.end_monitoring
    from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
    where i.obj# = ou.obj#
        and io.obj# = ou.obj#
        and t.obj# = i.bo#
        and u.user# = io.owner#;

Przydatnym rozwiązaniem może być również zdefiniowanie własnej perspektywy opartej o podane zapytanie. Konsekwentne jej stosowanie pozwoli na uniknięcie potencjalnej pomyłki podczas interpretacji wyniku zapytania do predefiniowanej perspektywy systemowej V$OBJECT_USAGE.

Drugim problemem, o którym warto wiedzieć jest błąd sygnowany numerem Bug 6798910, istniejący w wersjach bazy Oracle Enterprise Edition od numeru 10.2.0.1 do 10.2.0.4, który objawia się przestawieniem flagi USED na wartość „YES”, pomimo braku wykorzystania indeksu w okresie monitorowania.

Takie zdarzenie wystąpi, jeśli zostanie na bazie uruchomione obliczenie statystyk przy użyciu pakietu DBMS_STATS, dla funkcji:

  • GATHER_SCHEMA_STATS,
  • GATHER_TABLE_STATS,
  • GATHER_DATABASE_STATS,

z opcją CASCADE=TRUE.

Dla wskazanych wersji bazy danych rozwiązaniem jest powstrzymanie się od używania opcji CASCADE=TRUE podczas naliczania statystyk pakietem DBMS_STATS, lub też wyłączenie monitoringu na czas naliczania statystyk, zaś ponowne włączenie go po tej operacji. Jeżeli jednak zbieranie statystyk jest ujęte w mechanizm automatyczny, uruchamiany codziennie o określonej porze dnia, takie rozwiązanie może okazać się dość uciążliwe. Skutecznym rozwiązaniem okazuje się jak zawsze podniesienie bazy do wersji 10.2.0.5 lub 11.2.

Monitoring indeksów jest bardzo użytecznym mechanizmem, standardowo wbudowanym do bazy Oracle. Prosty w użyciu i skuteczny w działaniu, powinien być zawsze używany w sytuacji, gdy istnieje podejrzenie, że w bazie znajdują się nadmiarowe indeksy, nie używane przez optymalizator kosztu Oracle do budowy planów wykonań.

Pozostawienie niepotrzebnych indeksów w bazie, zużywających miejsce na dyskach i moc procesora, stanowi niczym nieuzasadniony koszt i jest przysłowiową „parą w gwizdek”.

Należy jednak zawsze pamiętać, że współcześnie używane aplikacje są konstrukcją o niezwykle wysokim stopniu komplikacji. Może się zatem okazać, że indeks planowany do usunięcia jest absolutnie niezbędny do realizacji bardzo ważnej funkcjonalności, uruchamianej na przykład raz do roku. Dlatego też, dobrą praktyką jest stosowanie zasady podwójnego potwierdzenia. Przed usunięciem indeksu z bazy, po stwierdzeniu braku jego użycia przez optymalizator, warto dodatkowo sprawdzić w dokumentacji projektowej, w jakich warunkach oraz na bazie jakich przesłanek indeks został zaprojektowany i utworzony.

Zawsze należy pamiętać, że usunięcie indeksu to tylko krótka chwila, zaś jego odbudowa to długi i nerwowy czas oczekiwania, przerywany częstymi telefonami od zirytowanego użytkownika aplikacji.

Bibliografia:

  1. Oracle® Database SQL Reference 10g Release 2 (10.2) B14200-02
  2. Oracle® Database Reference 10g Release 2 (10.2) B14237-03
  3. Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command. Note:136642.1
  4. Viewing All Indexes Being Monitored Under Another User’s Schema. Note: 160712.1
  5. Index Monitoring Result is Affected by the DBMS_STATS with CASCADE=TRUE. Note: 430034.1