Ile indeksów potrzebujesz?

To ile jest tych indeksów?

Dużo to czy mało? Jeśli to za dużo to ile tak właściwie powinno ich być?

Odpowiedź na to pytanie jest dość prosta. Aczkolwiek mało satysfakcjonująca.

To zależy.

Od czego?

Od typu systemu, od wielkości tabeli, od danych, od ilości i typu przetwarzań.

Mocno generalizując powiedziałabym, że tak do 5 indeksów (plus PK, FK) wydaje się w miarę sensownie. Zaś powyżej 10 zaczęłabym się już zastanawiać....Nie mówiąc już o tabelach, gdzie znajdziemy 30-40+ indeksów. Tu już mocno podejrzanie pachnie... Jeśli dodatkowo mamy problem wydajnościowy w systemie to może warto przyjrzeć się również bazie od tej strony.

No ale z samego patrzenia się na indeksy wiele nie wywnioskujemy. Trzeba tu zaprząc do pracy jakieś sprytne narzędzie, by wykonało mozolną pracę za nas. 

Tutaj przyda nam się INDEX MONITORING.

Narzędzie to umożliwia weryfikację, które indeksy są używane a które całkiem bezużyteczne. Żeby weryfikacja miała sens, narzędzie musi działać przez pełny okres życia aplikacji, tak podczas normalnej dziennej pracy jak i podczas nocnych przetwarzań batchowych. Zwrócić należy również uwagę na specyficzne dni, w których praca na systemie może się różnić od innych dni czyli na przykład końce miesiąca, koniec dnia, koniec kwartału, koniec roku itp. Wyniki działania narzędzia będą poprawne, jeśli uruchomimy je w reprezentatywnych ramach czasowych. 

Jak to działa? Pokażę na przykładzie. Skorzystam z defaultowego schematu HR na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production, gdzie utworzę testową tabelę wraz kilkoma indeksami na potrzeby naszego testu. 

create table emp_idx_monitoring as select * from employees;

ALTER TABLE emp_idx_monitoring
ADD CONSTRAINT emp_idx_m_pk PRIMARY KEY (employee_id);

create index emp_idx_m_first_name_idx on emp_idx_monitoring  (first_name);
create index emp_idx_m_last_name_idx on emp_idx_monitoring  (last_name);
create index emp_idx_m_salary_idx on emp_idx_monitoring  (salary);
create index emp_idx_m_email_idx on emp_idx_monitoring  (email);

select * from all_indexes where table_name = upper('emp_idx_monitoring');

Teraz należało by włączyć monitorowanie indeksów. Wykonuje się to poleceniem 

ALTER INDEX index_name MONITORING USAGE;
--wyłączenie
ALTER INDEX index_name NOMONITORING USAGE;

Polecenie należałoby wykonać dla każdego indeksu w bazie danych. Przy kilku indeksach, jak w przykładzie, nie ma problemu. Jednak przy większej bazie lepiej sobie ułatwić korzystając z tabel słownikowych.

select 'ALTER INDEX '||table_owner||'.'||index_name || ' MONITORING USAGE;' 
from all_indexes where table_name =upper('emp_idx_monitoring');

-----
ALTER INDEX HR.EMP_IDX_M_SALARY_IDX MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_EMAIL_IDX MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_PK MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_FIRST_NAME_IDX MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_LAST_NAME_IDX MONITORING USAGE;

Uruchamiamy kilka zapytań:

select * from emp_idx_monitoring where employee_id =  107; 
select * from emp_idx_monitoring where first_name = 'Diana';
select * from emp_idx_monitoring where last_name = 'Lorentz';

I możemy sprawdzić efekty.

Jeśli mamy wersję starszą niż Oracle 12.1 możemy skorzystać z widoku V$OBJECT_USAGE. Uwaga - w tabeli nie ma kolumny OWNER, dane będziemy widzieli tylko zalogowani na dany schemat! Jeśli chcemy z konta administratora sprawdzić wyniki monitoringu musimy zastosować inne zapytanie:

select * from 
    (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#)
    where used = 'YES';

W bazie 12.1 i nowszej wyniki monitoringu możemy znaleźć w dba_object_usage. 

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   dba_object_usage
WHERE  table_name =  upper('emp_idx_monitoring')
AND    owner = 'HR'
ORDER BY index_name;

Pożyteczne ale - obciążające... Czy warto? Jeśli masz wersję 12.2 - nie warto! W tej wersji i nowszych mamy monitorowanie indeksów w pakiecie:)

Oracle monitoruje indeksy zapisując statystyki co 15 minut. Informacje o użyciu indeksów można przeglądnąć w V$INDEX_USAGE_INFO i  DBA_INDEX_USAGE.

Index Usage Tracking defaultowo jest włączony z opcją SAMPLE. W tej opcji rzadko używane indeksy mogą nie zostać uwzględnione w statystykach. Jeśli chcemy dokładnie zbadać użycie indeksów trzeba ustawić parametr na ALL.

alter session set "_iut_stat_collection_type"=ALL;
alter session set "_iut_stat_collection_type"=SAMPLE;

Zmiana parametru na ALL może jednak wiązać się z dodatkowym obciążeniem bazy, więc warto sobie to wcześniej przetestować przetestować.

W widoku V$INDEX_USAGE_INFO mamy między innymi informację, kiedy by ostatni zapis LAST_FLUSH_TIME.

No a potem już sobie patrzymy w dane konkretnych indeksów w DBA_INDEX_USAGE.

select i.owner, i.table_name, i.index_name, i.index_type, i.uniqueness, iu.last_used 
from DBA_INDEX_USAGE  iu, all_indexes i 
where iu.name(+) = i.index_name
  and table_name =upper('emp_idx_monitoring');

Oczywiście statystyki użycia indeksów będą tak dobre, jak dobre będzie określenie przez nas ram czasowych, nieważne którego narzędzia użyjemy. Niemniej jeśli mamy w systemie dużo indeksów a do tego system nie domaga - może usunięcie zbędnych indeksów da aplikacji drugi oddech.