Dla poprawnego działania, kosztowy optymalizator zapytań musi posiadać wiedzę o rozkładzie wartości kolumny, na której oparty jest predykat w zapytaniu. Wiedzę taką może pozyskać na trzy sposoby: (1) pobierając losową próbkę danych w chwili wykonywania zapytania (dynamic sampling), (2) posługując się wcześniej zebranymi statystykami podstawowymi i przyjmując rozkład jednorodny wartości, (3) posługując się zebranym wcześniej histogramem i odczytując z niego charakterystykę rozkładu wartości. W systemach OLTP będziemy zwykle unikali Dynamic Samplingu na rzecz właściwie zbieranych statystyk lub histogramów. Ważną decyzją projektanta lub administratora bazy danych jest określenie, dla których kolumn powinny być zbierane statystyki podstawowe, a dla których histogramy. Generalnie rzecz ujmując, histogramy przydatne są wtedy, gdy: (1) wartości kolumny cechują się nierównomiernym rozkładem i (2) kolumna jest wykorzystywana w predykatach selekcji. Zwykle w celu „ułatwienia sobie życia” administratorzy powierzają serwerowi bazy danych podjęcie decyzji, dla których kolumn powinny zostać wygenerowane histogramy, a którym kolumnom wystarczą statystyki podstawowe. Aby aktywować taki automatyzm, w wywołaniach procedur pakietu DBMS_STATS po prostu posługujemy się klauzulą „FOR ALL COLUMNS SIZE AUTO”. Warto jednak rozumieć specyfikę jej działania, aby uniknąć zgubnych niespodzianek.
Gdy gromadzimy statystyki z opcją METHOD_OPT=>’FOR ALL COLUMNS SIZE AUTO’, serwer bazy danych podejmuje decyzję o wygenerowaniu histogramu dla kolumny jeżeli spełnione są następujące warunki:
- kolumna uczestniczy w predykatach równościowych lub zakresowych zapytań SQL – w tym celu serwer zlicza każde wystąpienie kolumny w predykatach za pomocą tabeli systemowej SYS.COL_USAGE$ (skądinąd bardzo przydatna tabela)
- rozkład wartości kolumny jest nierównomierny (skewed), co oznacza, że różne wartości występują z różną częstością albo, że następują duże nieciągłości dziedziny wartości (nawet unikalnych – patrz zdziwienie Hermanta Chitale w http://hemantoracledba.blogspot.com/2008/11/histogram-skew-on-unique-values.html)
Nie należy się zatem spodziewać automatycznego wygenerowania histogramu dla kolumny, której nigdy jeszcze nie użyliśmy w predykacie zapytania SQL, bądź której wartości są ciągłe i równomiernie rozłożone. Zademonstrujmy to na poniższym przykładzie.
Tabela S_TEST będzie posiadać dwie kolumny, C_UNIFORM (rozkład równomierny, ciągły) i C_SKEWED (dwie wartości o zróżnicowanej częstości – 90% 'MALE’, 10% 'FEMALE’). W pierwszym kroku spróbujmy po prostu zebrać dla niej statystyki w sposób automatyczny, a następnie zweryfikować, jakiego rodzaju statystyki zostały wygenerowane.
create table s_test(c_uniform number(6), c_skew varchar2(6));
declare
sex varchar2(6);
begin
for i in 1..100 loop
if i<=90 then sex:='MALE'; else sex:='FEMALE'; end if;
insert into s_test values (i,sex);
end loop;
end;
/
exec dbms_stats.gather_table_stats('','S_TEST', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
select column_name, num_distinct, histogram from user_tab_columns where table_name='S_TEST';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
----------- ------------ ---------
C_UNIFORM 100 NONE
C_SKEW 2 NONE
W obu przypadkach serwer bazy danych uznał, że wystarczą mu statystyki podstawowe, a nie histogramy (HISTOGRAM=NONE). W przypadku kolumny C_UNIFORM jest to zrozumiałe, ale co z kolumną C_SKEWED? Otóż z powodu braku jakiejkolwiek aktywności SQL dotyczącej tej kolumny (predykaty równościowe lub zakresowe), serwer postanowił oszczędzić sobie pracy związanej z analizowaniem jej wartości i generowaniem histogramu. Zauważmy, jak jednak zmieni się to podejście w przypadku, gdy użytkownicy zaczną się interesować tabelą S_TEST. Wykonamy dwa predykaty równościowe oparte o kolumny C_UNIFORM i C_SKEW, zweryfikujemy w COL_USAGE$, że serwer odnotował tę aktywność (wcześniej FLUSH_DATABASE_MONITORING_INFO(), aby zapisać zmiany z pamięci operacyjnej), następnie ponownie zbierzemy i przeanalizujemy statystyki.
select count(*) from s_test where c_uniform=50;
select count(*) from s_test where c_skew='FEMALE';
exec dbms_stats.flush_database_monitoring_info();
select c.column_name, u.equality_preds, u.range_preds, u.like_preds, u.null_preds
from sys.col_usage$ u, dba_tab_columns c
where u.obj#=(select object_id from dba_objects where object_name='S_TEST' and owner='SYSTEM')
and u.intcol#=c.column_id and c.table_name='S_TEST' and c.owner='SYSTEM';
COLUMN_NAME EQUALITY_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
----------- -------------- ----------- ---------- ----------
C_SKEW 1 0 0 0
C_UNIFORM 1 0 0 0
exec dbms_stats.gather_table_stats('','S_TEST', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
select column_name, num_distinct, histogram from user_tab_columns where table_name='S_TEST';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
----------- ------------ ---------
C_UNIFORM 100 NONE
C_SKEW 2 FREQUENCY
Wreszcie jest tak, jak powinno być. Kolumna C_UNIFORM będzie wykorzystywać podstawowe statystyki, gdyż jej rozkład wartości jest wzorcowo równomierny, natomiast dla kolumny C_SKEW serwer postanowił wygenerować histogram częstotliwościowy, który pomoże właściwie szacować selektywność predykatów wybierających nierównomiernie rozłożone wartości „MALE” lub „FEMALE”.
Pamiętajmy: nie używamy (kolumny) – nie dostajemy (histogramu), nawet jeśli zasługujemy.