Dwóm z takich wynalazków chciałbym poświęcić poniższy artykuł.

Indeksy wirtualne czyli funkcjonalność czekająca 15 lat na dokończenie.

Każda osoba próbująca swych sił w tuningu poleceń SQL na pewno stanęła niejednokrotnie przed pytaniem, czy warto założyć indeks na określonej kolumnie/zestawie kolumn. Czy przyniesie to poprawę przy wyszukiwaniu rekordów, czy wręcz przeciwnie, spowolni tylko modyfikacje danych nie dając nic w zamian.

Niektóre sytuacje są ewidentne  – konfrontując statystyczny rozkład logiczny danych z warunkami selekcji zapytania jesteśmy pewni jaki efekt da założenie indeksu, jednak czasami, gdy selektywność zapytania jest w granicach kilku procent rekordów wszystko zależy od jakości indeksu, czyli przede wszystkim od współczynnika CLUSTERING_FACTOR, który odzwierciedla nam korelację pomiędzy wartościami indeksowanej kolumny a rozkładem fizycznym rekordów w segmencie. W zależności od dobrego lub złego clustering_factor może się okazać, że indeks nie jest opłacalny w stosunku do full scanu tabeli już przy 0.1%, podczas gdy na tej samej tabeli indeks na innej kolumnie opłaca się używać jeszcze przy 30% wybieranych warunkiem rekordów.

Ponieważ statystyki indeksu trudno przewidzieć, najlepiej byłoby założyć interesujący nas indeks i po prostu zweryfikować jego działanie. Niestety pojawia się tu problem, bo najczęściej wyżej opisane dylematy mamy w kontekście dużych tabel, a na dużych tabelach indeksy zakładają się dłuuuugo. A kto pracuje w Standard Edition, to jeszcze musi przewidzieć, że długie zakładanie indeksu blokuje na długo modyfikacje danych w indeksowanej tabeli. W związku z tym trochę szkoda spędzić kilkadziesiąt minut na zakładaniu indeksu, aby tylko stwierdzić, że się on do niczego nie nadaje i go usunąć.

Doskonałym rozwiązaniem powyższych problemów miały być wprowadzone w wersji 10g indeksy wirtualne. Pomysł zasadza się na tym, że przecież aby zweryfikować skuteczność indeksu i koszty hipotetyczne jego użycia optymalizator CBO nie potrzebuje samego indeksu, wystarczą tylko jego statystyki. Dopóki nie będziemy chcieli wykonać zapytania, a tylko oszacować jego koszty, to nie musimy żmudnie budować segmentu indeksu generując olbrzymie ilości operacji I/O.

A zatem poeksperymentujmy.
Na początek załóżmy tabelę i wypełnijmy ją dwoma milionami przypadkowych rekordów, przy czym pierwsza kolumna jest poukładana – będzie miała dobry indeks,  a pozostałe kolumny są zupełną niewiadomą:

create table test (kol1 number constraint test_pk primary key
,kol2 number
,kol3 number
,kol4 number
);
insert into test
select rownum, dbms_random.random, dbms_random.random, dbms_random.random
from dual
connect by level <= 2000000;
commit;
-- i jeszcze statystyki
begin
dbms_stats.gather_table_stats(ownname => 'ST', tabname => 'TEST');
end;
/

Powstała nam tablica o rozmiarze około 70MB oraz dobry indeks na uporządkowanym kluczu głównym. Sprawdźmy jaki CBO zaproponuje nam algorytm przy próbie sortowania wszystkich rekordów  po kolumnie z istniejącym indeksem.

select * from test order by kol1;
----------------------------------------------------------------------------------------
| Id | Operation                     | Name    | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |         | 2000000 | 52000000 | 13003 | 00:00:01 |
|  1 |   TABLE ACCESS BY INDEX ROWID | TEST    | 2000000 | 52000000 | 13003 | 00:00:01 |
|  2 |    INDEX FULL SCAN            | TEST_PK | 2000000 |          |  3909 | 00:00:01 |
---------------------------------------------------------------------------------------- 

Zgodnie z oczekiwaniem, nawet pomimo krótkiego wiersza preferującego sortowanie w pamięci, CBO zaproponował użycie indeksu. To dobry pomysł, bo oprócz lepszego kosztu mamy dodatkowo „przy okazji” szybki pierwszy wiersz.

A co w sytuacji, gdybyśmy chcieli posortować po kolumnie kol2? Krótki wiersz preferuje sortowanie bez indeksu, losowy układ danych w kol2 sugeruje, że indeks będzie byle jaki, ale najlepiej sprawdzić – skorzystajmy w takim razie z indeksów wirtualnych

create index test_kol2_virt_idx on test (kol2) nosegment;
-- i jeszcze statystyki
begin
dbms_stats.gather_index_stats(ownname => 'ST', indname => 'TEST_KOL2_VIRT_IDX');
end;
/

Powyższe polecenie spowodowało szybkie (ok 0.01s) wykreowanie indeksu wirtualnego.
Jakie koszty pokaże nam CBO?

select * from test order by kol2;
----------------------------------------------------------------------------
| Id | Operation            | Name | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      | 2000000 | 52000000 | 17380 | 00:00:01 |
|  1 |   SORT ORDER BY      |      | 2000000 | 52000000 | 17380 | 00:00:01 |
|  2 |    TABLE ACCESS FULL | TEST | 2000000 | 52000000 |  2496 | 00:00:01 |
---------------------------------------------------------------------------- 

Okazuje się, że CBO  preferuje jednak sortowanie w pamięci, ale co ciekawe, nawet użycie hintu nie pozwala wymusić używania założonego przed chwilą indeksu. Szkopuł tkwi w tym, że jeśli chcemy używać indeksów wirtualnych, musimy je jeszcze globalnie włączyć zmieniając nieudokumentowany parametr (a więc na własne ryzyko)

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Teraz plan i indeks wygląda bardzo obiecująco.

--------------------------------------------------------------------------------------------------
| Id | Operation                     | Name               | Rows    | Bytes    | Cost | Time     |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                    | 2000000 | 52000000 |  826 | 00:00:01 |
|  1 |   TABLE ACCESS BY INDEX ROWID | TEST               | 2000000 | 52000000 |  826 | 00:00:01 |
|  2 |    INDEX FULL SCAN            | TEST_KOL2_VIRT_IDX | 2000000 |          |   26 | 00:00:01 |
-------------------------------------------------------------------------------------------------- 

Ale, ale, … czy indeks może być aż tak dobry, aby koszty zmniejszyły się do niespełna 1000, podczas gdy analogiczna operacja sortowania prawie idealnym indeksem kosztowała 13 000, a sam full scan tabeli test kosztuje 2500? Raczej niemożliwe, ale sprawdźmy jak zachowa się prawdziwy indeks.

drop index test_kol2_virt_idx;
create index test_kol2_idx on test (kol2);
-- statystyki
begin
dbms_stats.gather_index_stats(ownname => 'X_EAM', indname => 'TEST_KOL2_IDX');
end;
/

Wprawdzie indeks zakładał się teraz 5s zamiast 0.01s (500x dłużej), ale przynajmniej CBO nie wariuje i możemy z czystym sumieniem zapomnieć o zakładaniu tego indeksu w celu obniżenia kosztów sortowania:

------------------------------------------------------------------------------------------------
| Id | Operation                     | Name          | Rows    | Bytes    | Cost    | Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |               | 2000000 | 52000000 | 2005665 | 00:01:19 |
|  1 |   TABLE ACCESS BY INDEX ROWID | TEST          | 2000000 | 52000000 | 2005665 | 00:01:19 |
|  2 |    INDEX FULL SCAN            | TEST_KOL2_IDX | 2000000 |          |    5163 | 00:00:01 |
------------------------------------------------------------------------------------------------ 

Index wirtualny okazał się być kompletnym niewypałem. Można by jeszcze mieć nadzieję, że skoro CBO nie potrafi korzystać ze statystyk indeksów wirtualnych, to moglibyśmy je „ręcznie” zweryfikować? Niestety rzeczywistość okazuje się w tym względnie okrutna – indeksów wirtualnych nie zobaczymy ani w user_indexes, ani w user_ind_statistics, nie zadziała nam polecenie analyze index validate structure. Można je znaleźć tylko w user_objects, a tam niestety niczego wartościowego się o indeksie nie dowiemy.

O ile w wersji 10g można było wybaczyć zaistniałą sytuacje tym, że development funkcjonalności nie został ukończony na czas, by wejść do wersji, to w kolejnych wersjach to tłumaczenie się nie broni. Przykłady w artykule zostały wykonane na 12cR2, a nawet 18c niczego tu nie zmienia. Indeksy wirtualne w tym wypadku są świetnym przykładem na to, że ze świetnego pomysłu może wyjść kompletna klapa.

Wygląda na to, że zacząłem od tej „mniej użytecznej” funkcjonalności. Za to druga część, czyli indeksy niewidzialne na pewno zasługują na uwagę i rekompensują zawód sprawiony przez pierwszą część artykułu.

Indeksy niewidzialne, czyli jak się bezpiecznie pozbyć indeksu.

Wiadomo powszechnie, że każdy indeks szkodzi, ale nie każdy pomaga. Sztuką jest wyważyć czy kolejny zakładany indeks przyniesie więcej korzyści, czy też szkód. Niestety tej sztuki nie opanowały wszystkie osoby zakładające indeksy, ponieważ najczęściej zakładając indeks skupiamy się na wąskim obszarze, np. jednym zapytaniu, w którym korzyści się kumulują i są ewidentne, a zapominamy o spowolnieniu modyfikacji danych, które rozkłada się na wielu innych procesach. Takie podejście jednak prowadzi szybko do sytuacji, w której tabela obrasta nadmiarem indeksów (dedykowanych często jednemu zapytaniu), które kumulatywnie powodują, że modyfikacje danych w tabeli zaczynają być poważnym problemem.

Zatem wypadałoby posprzątać, pozbyć się nadmiarowych indeksów, tylko, że tym razem sytuacja się odwraca – usuwając jeden indeks z wielu nie poprawimy znacznie procesów modyfikujących dane, ale za to ryzykujemy, że ten usunięty indeks był kluczowy dla pewnego krytycznego procesu i po jego usunięciu proces ten przestanie się wykonywać w akceptowalnym czasie. Testy będą z pewnością pomocne, ale nie zagwarantują stuprocentowego bezpieczeństwa, że czegoś nie przegapimy.

A pamiętajmy, że odtworzenie takiego indeksu będzie trwało długo i dodatkowo będzie musiało być wykonywane offline, jeśli dysponujemy tylko edycją SE Oracle.

Jak w takim razie sobie poradzić w takiej sytuacji?
Niestety istniejący monitoring użycia indeksów nam nie pomoże:

• przed wersją 12c po włączeniu monitoringu mogliśmy dowiedzieć się tylko w v$object_usage, że w okresie monitorowania indeks został chociaż raz użyty – ale ile razy, w jakich zapytaniach, jak intensywne i jak ważne były te użycia niestety nie.
• Od wersji 12c mamy do dyspozycji dba_object_usage, która zastąpiła v$object_usage, ale przede wszystkim mamy dba_index_usage, który niesie w sobie znacznie więcej informacji o tym jak często i w jaki sposób poszczególne indeksy były używane.

Niemniej nawet nowości 12c nie dają nam możliwości przewidzenia, czy jeśli usunę pewien indeks, to czy inny, np. wielokolumnowy, lub alternatywny nie przejmą jego roli w pełnym lub częściowym zakresie. Powinniśmy raczej preferować indeksy uniwersalne niż idealnie dedykowane konkretnemu zapytaniu.

Odpowiedź na to pytanie możemy otrzymać tylko usuwając indeks i obserwując procesy zachodzące w bazie. Tylko, że jak okaże się, że jednak usunęliśmy zbyt pochopnie, to mleko się już rozleje.

I tutaj właśnie przychodzi nam z pomocą funkcjonalność indeksów niewidzialnych wprowadzona w wersji 11g i dostępna również w SE. Polega ona na tym, że zamiast usuwać indeks, możemy go ukryć w dowolnym momencie przed optymalizatorem. Przy czym podczas modyfikacji danych w tabeli indeks nadal jest utrzymywany w statusie VALID – nie uzyskamy nic podczas modyfikacji danych, ale dzięki temu w każdej chwili natychmiastowo, bez zakładania żadnych istotnych blokad możemy przywrócić widoczność takiego indeksu.

Spróbujmy wykorzystać tabelę test i indeks na kolumnie kol2 z poprzedniej części artykułu.

select * from test where kol2 = 1;
------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |    1 |    26 |    5 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | TEST          |    1 |    26 |    5 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | TEST_KOL2_IDX |    1 |       |    3 | 00:00:01 |
------------------------------------------------------------------------------------------------

Widać, że CBO chce użyć indeksu, jeśli jednak wykonamy polecenie:

alter index test_kol2_idx1 invisible;
to indeks pozostanie w stanie VALID
select index_name, status, visibility
from user_indexes
where index_name = 'TEST_KOL2_IDX';

INDEX_NAME                     STATUS   VISIBILITY
—————————— ——– ———-
TEST_KOL2_IDX                  VALID    INVISIBLE

a widzialność indeksu dla CBO będzie zależeć od wartości parametru: optimizer_use_invisible_indexes, który może być modyfikowany per sesja.

Daje nam to do ręki dwa możliwe scenariusze działania:

• Typujemy indeks do usunięcia, ukrywamy go i obserwujemy system, czy nie pojawiają się problemy wydajnościowe spowodowane jego brakiem. W ten sposób czekamy przez pełen cykl pracy systemu – jeśli problemy nie wystąpiły, to możemy bezpiecznie usunąć indeks, jeśli problemy pomimo wcześniejszych testów się pojawiły, to jednym, szybkim poleceniem zmieniamy widzialność indeksu na noinvisible.
• Zmieniamy wartość parametru optimizer_use_invisible_indexes na true dla całego systemu i ukrywamy indeks typowany do usunięcia. Dla systemu nie zmieni to niczego, ale za to będziemy w określonej pojedynczej sesji mogli wyłączyć indeks manipulując wartością w/w parametru i przeprowadzić testy, co się stanie, jeśliby zabrakło indeksu.

Najbezpieczniej skorzystać z obu scenariuszy w odwrotnej kolejności niż podana powyżej.

Mam nadzieję, że powyższe rozważania będą pomocne zarówno dla początkujących osób zajmujących się tuningiem, jak również dla bardziej doświadczonych, ale nie mających czasu na co dzień do poszerzania własnego warsztatu.