poniedziałek, 5 grudnia 2011

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 .

1 komentarz:

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

    coś mi się nie tu zgadza, estimate_percent nie oznacza że liczymy statystyki dla obiektów których zawartość zmieniła się w 14 procentach, ale estimate_percent oznacza wielkość próbki brana pod uwage przy obliczaniu statystyk.

    Proszę sprawdzić w dokumentacji Oralce

    OdpowiedzUsuń