Statystyki i histogramy

Czym są statystyki?

W skrócie statystyki są zbiorem informacji na temat tabel, kolumn i indeksów. Zawierają informacje o np. ilości wierszy w tabeli, ich zróżnicowaniu, największej i najmniejszej wartości występującej w kolumnie. W oparciu o te informacje, optymalizator kosztowy (CBO) podejmuje decyzje o algorytmie dostępu do danych (przypominamy sobie analogię do podróży i różnych środków transportu z rozdziału o wprowadzeniu do optymalizacji), szacuje koszty.

Należy je odświeżać, bo w przeciwnym przypadku CBO opierając się o nieprawdziwe informacje pochodzące z przedawnionych statystyk podejmować będzie decyzje błędne odnośnie doboru algorytmów i metod dostępu do danych.

Rodzaje statystyk

Statystyki tabel

Zawierają informacje o :

• ilości wierszy w tabeli
• ilości bloków zajmowanych przez tabelę
• średnia długość wierszy

Słownik: DBA_TAB_STATISTICS

Statystyki indeksów

Zawierają informacje o :

• ilości poziomów w drzewie indeksu
• ilości unikalnych wartości (lub ich kombinacji) w kolumnie (lub kolumnach) na którą został założony indeks.

Słownik: DBA_IND_STATISTICS

Statystyki systemowe

Zawierają informacje o :

• wydajności I/O
• wydajności procesora

Statystyki kolumn

Zawierają informacje o:

• ilości unikalnych wartości w kolumnie
• ilości nulli w kolumnie
• średniej długości wartości w kolumnie
• maksymalnej wartości
• minimalnej wartości
• zróżnicowanie danych w kolumnie.

Słownik: DBA_TAB_COL_STATISTICS

Histogramy

Same statystyki zawierają informacje podstawowe o zróżnicowaniu zawartości kolumn – takie jak np. maksymalna czy minimalna wartość oraz ilości unikalnych wartości w kolumnie. Histogramy zawierają znacznie bardziej szczegółowe informacje o zróżnicowaniu. Znajdziesz tutaj np. informacje o częstotliwości występowania konkretnych wartości. Zasadniczo informacje dotyczące histogramów znajdziesz w tej samej tabeli co statystyki tj. DBA_TAB_COL_STATISTICS i dodatkowo w DBA_TAB_HISTOGRAMS.

Generalnie tworzenie histogramów jest najbardziej czasochłonne podczas odświeżania statystyk.

Dzięki histogramom możesz wywnioskować jaki rodzaj indeksu można nałożyć na daną kolumnę. Pamiętaj że indeks bitmapowy nakładamy tam gdzie zróżnicowanie w kolumnie jest małe, indeks b-tree tam gdzie zróżnicowanie jest duże. Jeśli się pomylisz, może się okazać że optymalizator kosztowy nie będzie chciał wykorzystywać indeksu.

Odświeżanie statystyk i histogramów

Odświeżanie statystyk systemowych:

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS;

Odświeżanie statystyk indeksu:

EXECUTE DBMS_STATS.GATHER_INDEX_STATS('nazwaschematu','nazwaindeksu');

Odświeżanie statystyk tabeli

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('nazwaschematu','nazwatabeli');

Takie wywołanie spowoduje odświeżenie statystyk i histogramów dla tabeli którą wskażemy, ale również dla indeksów założonych na kolumny w tej tabeli.

Odświeżanie statystyk schematu

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('nazwaschematu');

Takie wywołanie odświeży statystyki i histogramy dla wszystkich tabel i wszystkich indeksów w schemacie.

Mamy możliwość włączenia specjalnego monitoringu dla kilku tabel i odtwarzać statystyki tylko dla tych obiektów które tego wymagają. To czy tego wymagają czy nie określamy my, poprzez wskazanie wartości procentowej danych w obiekcie które zostały zmienione. Określamy np. że jeżeli zmieniło się 14% danych w tabeli employees to dla tej tabeli mają zostać wygenerowane statystyki.
Pierwsze co czynimy to ustawienia dla tabel:


alter table employees monitoring;

Następnie co jakiś czas otwarzamy statystyki dla schematu, ale w sposób  nieco zmieniony:

execute dbms_stats.gather_schema_stats(ownname=>'hr',estimate_percent=>14,options=>'GATHER STALE');

Powyżej wywołałem odtwarzanie statystyk w schemacie hr dla tabel które zmieniły sie w 14 lub więcej procentach. Objęte tym będą tylko tabele dla których włączyliśmy monitoring.
Teraz wystarczy to wrzucić w jakiegoś joba, by statystyki odtwarzały nam się cyklicznie.

Odświeżanie statystyk całej bazy danych

EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;

Wszystkie statystyki w całej bazie zostaną odświeżone. Baardzo czasochłonna operacja.

Parametr OPTIMIZER_DYNAMIC_SAMPLING

Dynamiczne próbkowanie wykorzystywane jest gdy brak statystyk dla tabeli, lub gdy są one na tyle przestarzałe że nie można im ufać.

Poziom próbkowania określa parametr optimizer_dynamic_sampling który możemy zweryfikować w słowniku v$parameter. Domyślnie ten parametr ustawiony jest na wartość 2. Dopuszczalne wartości mieszczą się w przedziale 0 do 2.

0 oznacza zupełne wyłączenie dynamicznego próbkowania. 1 – próbkowanie stosowane jest tylko w przypadku gdy:

• jest przynajmniej jedna niezanalizowana tabela wykorzystywana w zapytaniu.
• Nieprzeanalizowana tabela nie posiada indeksów

Poziom 2 to włączenie dynamic sampling w taki sposób by próbkował wszystkie nieprzeanalizowane tabele, wyższe stopnie określają coraz bardziej agresywne próbkowanie.

Kasowanie statystyk

EXECUTE DBMS_STATS.DELETE_TABLE_STATS('hr', 'employees');

Blokowanie statystyk

EXECUTE DBMS_STATS.LOCK_TABLE_STATS('HR','EMPLOYEES');

Używane jest gdy chcemy zablokować stan kiedy nie ma statystyk dla obiektów, a chcemy by zawsze wykorzystywał DYNAMIC SAMPLING, albo kiedy chcemy zatrzymać statystyki w pewnym momencie tak by nie były automatycznie odświeżane.

Odblokowujemy w ten sposób:

EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS('HR','EMPLOYEES');

Jeśli zablokujesz statystyki dla tabeli, wszystkie statystyki zależne (np. statystyki indeksów) również zostaną zablokowane.

Odtwarzanie statystyk

Czasami odświeżanie statystyk idzie w złym kierunku, i chcielibyśmy przywrócić stan statystyk np. sprzed odświeżenia. Za każdym razem kiedy odświeżamy statystyki, ich poprzednia wersja jest zachowywana byśmy mogli przywrócić ich stan. Wykorzystujemy w tym celu procedurę restore_table_stats z pakietu dbms_stats:

Execute Dbms_Stats.Restore_Table_Stats(Ownname=>'hr', tabname=>'employees',as_of_timestamp=>to_timestamp('05-12-2011 12:00:00','dd-mm-yyyy hh24:mi:ss'));

Historia statystyk

Zawsze możemy sprawdzić kiedy były generowane statystyki dla tabeli, dzięki czemu będziemy wiedzieli np. do którego miejsca przywrócić statystyki. Aby obejrzeć historię statystyk przeglądamy słownik DBA_TAB_STATS_HISTORY.

SELECT * FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='EMPLOYEES';

Automatyczne generowanie statystyk przez Oracle

Statystyki generują się również automatycznie. W wersji 10g Oracle, automatyzacja generowania statystyk związana była z programem GATHER_STATS_PROG wywoływanym przez job o nazwie GATHER_STATS_JOB. Przy użyciu słowników :

• DBA_SCHEDULER_JOBS
• DBA_SCHEDULER_SCHEDULES

możemy odnaleźć szczegóły dotyczące częstotliwości odświeżania statystyk. W wersji 11g job ten nie jest już widoczny. Informacje o szczegółach automatycznego generowania statystyk znajdziemy w słowniku DBA_AUTOTASK_CLIENT_JOB .