19OptymalizacjaSQL czesc 2

Statystyki (1)

•

Informacje, opisujące dane i struktury obiektów bazy danych.

•

Przechowywane w słowniku danych.

Optymalizacja poleceń SQL

•

Używane przez optymalizator do oszacowania:

Część 2.

• selektywności predykatów polecenia,

• kosztu użycia ścieżek dostępu,

Statystyki i histogramy,

• kosztu operacji I/O i czasu procesora,

metody dostępu do danych

• kosztu planu wykonania polecenia.

•

Tylko aktualne statystyki użyteczne!

• Statystyki są statyczne – nie są automatycznie uaktualniane przy zmianie danych.

Statystyki (2)

Statystyki (3)

•

Przykłady statystyk:

•

Statystyki mogą być gromadzone automatycznie (przez

• dla relacji:

dedykowany proces SZBD) lub ręcznie (na żądanie użytkownika)

• liczba rekordów,

przy użyciu pakietu DBMS_STATS.

• liczba bloków,

• średnia długość rekordu,

•

W przypadku braku statystyk dla obiektów używanych w zapytaniu

• dla atrybutu relacji:

przed wykonaniem zapytania optymalizator realizuje dynamiczne

• liczba różnych wartości,

próbkowanie statystyk.

• liczba rekordów, w których atrybut ma wartość pustą,

• rozkład wartości (histogram),

• dla indeksu:

• liczba bloków-liści,

• wysokość drzewa,

• wskaźnik zgrupowania indeksu,

• statystyki systemowe:

• wykorzystanie procesora,

• liczba operacji we/wy.

Histogramy (1)

Histogramy (2)

•

Histogram – szczegółowa statystyka opisująca rozkład wartości

•

Rodzaje (cd):

określonej kolumny relacji.

• histogram częstotliwości (ang. frequency) – każda wartość kolumny

•

Rodzaje:

odpowiada jednemu przedziałowi, każdy przedział zawiera liczbę wystąpień tej wartości; tworzony wtedy, gdy liczba wartości kolumny

• histogram o zrównoważonej wysokości (ang. height balanced) – zbiór jest mniejsza bądź równa żądanej liczbie przedziałów histogramu.

wartości kolumny dzielony jest na przedziały o tej samej (w przybliżeniu) liczbie rekordów; przykład (zakres wartości: <1, 100>, liczba przedziałów: 10):

•

Histogramy należy tworzyć tylko dla kolumn z nierównomiernym

• równomierny rozkład wartości atrybutu:

rozkładem wartości (ang. skewed data), często używanych w warunkach zapytania.

1 10 20 30 40 50 60 70 80 90 100

•

Gdy zmieni się rozkład danych kolumny, konieczne jest ponowne

• nierównomierny rozkład wartości atrybutu:

wygenerowanie histogramu,

1 10 10 10 30 40 40 40 65 80 100

Ręczne zbieranie statystyk

Zbieranie statystyk dla indeksu

•

Metody:

exec DBMS_STATS.GATHER_INDEX_STATS(

• na podstawie pełnych danych,

ownname => <nazwa_schematu>, indname => <nazwa_indeksu>, estimate_percent => <procentowa_wielkość_próbki>);

• szacowanie na podstawie próbki, próbka określana w

procentach liczby rekordów.

•

jeśli wartość <procentowa_wielkość_próbki> określono jako:

•

Procedury zbierające statystyki:

• null, wówczas statystyki zbierane na podstawie pełnych danych,

• DBMS_STATS.GATHER_INDEX_STATS – dla indeksu,

• liczbę z przedziału <0,00001; 100>, wówczas szacowanie na podstawie próbki o zadanym rozmiarze,

• DBMS_STATS.GATHER_TABLE_STATS – dla relacji.

• DBMS_STATS.AUTO_SAMPLE_SIZE – rozmiar próbki dobiera system.

•

Procedury usuwające statystyki:

• DBMS_STATS.DELETE_INDEX_STATS – dla indeksu,

• DBMS_STATS.DELETE_TABLE_STATS – dla relacji,

exec DBMS_STATS.GATHER_INDEX_STATS(

• DBMS_STATS.DELETE_COLUMN_STATS – dla kolumny.

ownname => 'SCOTT', indname => 'PK_PRAC', estimate_percent => 20);

•

Uwaga! Od Oracle10g statystyki dot. indeksów są gromadzone automatycznie podczas tworzenia lub przebudowy indeksu.

Zbieranie statystyk dla relacji (1)

Zbieranie statystyk dla relacji (2)

exec DBMS_STATS.GATHER_TABLE_STATS(

•

<klauzula SIZE> – SIZE { liczba | REPEAT | AUTO | SKEWONLY }: ownname => <nazwa_schematu>, tabname => <nazwa_relacji>, estimate_percent => <procentowa_wielkość_próbki>,

• liczba – liczba przedziałów w histogramie, zakres: <1, 254>, method_opt => <rodzaj_statystyk>,

• REPEAT – powtórzenie zbierania histogramów dla kolumn, które mają już cascade =><DBMS_STATS.AUTO_CASCADE | TRUE | FALSE> ); histogramy,

• AUTO – SZBD określi, dla których kolumn zbierać histogramy na podstawie

•

METHOD_OPT – określa zakres zbieranych statystyk:

obciążenia i rozkładu danych kolumny,

• FOR TABLE – tylko statystyki dla tabeli bez statystyk dla kolumn,

• SKEWONLY – SZBD określi, dla których kolumn zbierać histogramy tylko na podstawie rozkładu danych kolumny (bez analizy obciążenia).

• FOR ALL COLUMNS [<klauzula SIZE>] – statystyki dla tabeli i statystyki dla wszystkich kolumn,

• FOR ALL INDEXED COLUMNS [<klauzula SIZE>] – statystyki dla tabeli i

•

FOR ALL COLUMNS SIZE AUTO – wartość domyślna dla par. METHOD_OPT: statystyki dla poindeksowanych kolumn,

• statystyki tabeli,

• FOR COLUMNS [<klauzula SIZE>] kolumna1 [<klauzula SIZE>], kolumna2

• podstawowe statystyki wszystkich kolumn tabeli,

[<klauzula SIZE>], ... – statystyki dla tabeli i statystyki dla wskazanych

• histogramy dla kolumn wyznaczonych na podstawie wcześniejszych kolumn.

obserwacji dotyczących obciążenia i rozkładu wartości.

Zbieranie statystyk dla relacji (3)

Statystyki w słowniku bazy danych

exec DBMS_STATS.GATHER_TABLE_STATS(

•

Dla relacji:

ownname => 'SCOTT', tabname => 'PRACOWNICY',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

• USER_TABLES, USER_TAB_STATISTICS

method_opt => 'FOR COLUMNS placa_pod SIZE AUTO, nazwisko SIZE AUTO');

•

Dla kolumn:

• USER_TAB_COLUMNS, USER_TAB_COL_STATISTICS,

exec DBMS_STATS.GATHER_TABLE_STATS(

USER_TAB_HISTOGRAMS

ownname => 'SCOTT', tabname => 'PRACOWNICY',

•

Dla indeksów:

method_opt => 'FOR ALL INDEXED COLUMNS',

• USER_INDEXES, USER_IND_STATISTICS

cascade => TRUE);

SELECT num_rows, blocks, last_analyzed, sample_size

FROM USER_TAB_STATISTICS

•

Uwaga! Od Oracle12c statystyki dotyczące tabel zostają zebrane automatycznie w sytuacji, gdy tabela, do której ładowane są dane ścieżką bezpośrednią WHERE table_name = 'PRACOWNICY';

(polecenie INSERT /*+ APPEND */, dane umieszczane od razu w plikach bazy danych z pominięciem bufora bazy danych), była poprzednio pusta: SELECT num_distinct, low_value, high_value, num_buckets, histogram

• tabela została dopiero co utworzona i nie posiada jeszcze rekordów, lub FROM USER_TAB_COL_STATISTICS

• usunięto z tabeli wszystkie rekordy.

WHERE table_name = 'PRACOWNICY'

AND column_name = 'NAZWISKO';

Usuwanie statystyk

Metody dostępu do danych

exec DBMS_STATS.DELETE_INDEX_STATS(

•

Określają, w jaki sposób dane polecenia SQL są odczytywane z ownname => <nazwa_schematu>, indname => <nazwa_indeksu>); miejsca ich fizycznej lokalizacji.

•

Dostęp do tabeli:

exec DBMS_STATS.DELETE_TABLE_STATS(

• pełne przeglądnięcie,

ownname => <nazwa_schematu>, tabname => <nazwa_relacji>);

• dostęp przy pomocy adresu rekordu.

•

Dostęp do indeksu:

exec DBMS_STATS.DELETE_COLUMN_STATS(

• unikalne przeglądnięcie indeksu,

ownname => <nazwa_schematu>, tabname => <nazwa_relacji>,

• (odwrócone) zakresowe przeglądnięcie indeksu,

colname => <nazwa_kolumny>, col_stat_type => <rodzaj_usuwanych_statystyk>);

• przeglądnięcie indeksu z pominięciem kolumn,

• pełne przeglądnięcie indeksu,

• szybkie pełne przeglądnięcie indeksu,

•

COL_STAT_TYPE:

• dostęp do indeksu bitmapowego,

• HISTOGRAM – usuwany jest histogram dla kolumny, podstawowe

• połączenie indeksów.

statystyki kolumny pozostają,

Przy dostępie do indeksu dane zwykle zwracane w kolejności rosnącej.

• ALL – usuwane są wszystkie statystyki dla kolumny (wartość

•

Ogólne zasady dostępu do danych:

domyślna).

• odczyt dużej części rekordów relacji – pełne przeglądnięcie relacji,

• odczyt pojedynczych rekordów relacji – dostęp za pomocą indeksu.

Dostęp do tabeli

Pełne przeglądniecie tabeli

Dostęp przy pomocy adresu rekordu

•

Ang. full table scan

•

Ang. rowid scan

•

Sekwencyjny odczyt wszystkich bloków danych, w których tabela

•

Odszukanie rekordu relacji na podstawie dostarczonego adresu przechowuje swoje rekordy, odfiltrowanie rekordów nie

rekordu (rowid).

spełniających zdefiniowanych w poleceniu SQL kryteriów selekcji

•

Najszybszy sposób dostępu

(np. w klauzuli WHERE).

do rekordów tabeli.

•

Stosowane gdy:

•

Źródło adresu rekordu:

• brak indeksu dla relacji lub

• warunek selekcji polecenia SQL,

nie można użyć istniejących

indeksów,

SELECT * FROM pracownicy

DB_FILE_MULTIBLOCK_READ_COUNT

• zostanie odczytana duża część

WHERE rowid = ‘AAAMMUAAEAAAAAtAAG’;

wszystkich bloków, w których tabela składuje swoje dane,

• rozmiar tabeli jest niewielki.

• pobranie z indeksu tabeli.

•

Możliwy odczyt wieloblokowy – pobranie w jednej operacji I/O wielu przyległych bloków danych, bardziej efektywne niż wiele odczytów pojedynczych bloków.

Dostęp do indeksu

Unikalne przeglądnięcie indeksu

Zakresowe przeglądnięcie indeksu

•

Ang. index unique scan

•

Ang. index range scan

•

Dostęp do indeksu unikalnego, operacja zwraca co najwyżej jeden

•

Dostęp do indeksu unikalnego (warunek inny niż równościowy) lub adres rekordu.

nieunikalnego, operacja zwraca zakres adresów rekordów.

•

Stosowane, gdy w poleceniu SQL zastosowano warunek

•

Stosowane, gdy w poleceniu SQL:

równościowy z atrybutem, na którym zdefiniowano indeks unikalny

• warunek selekcji z kolumnami z części wiodącej indeksu, takie jak: (również ograniczenia klucz podstawowy i klucz unikalny).

• kolumna = ‘wartość’, kolumna > ‘wartość’, kolumna < ‘wartość’

(również kombinacje powyższych)

• kolumna like ‘ABC%’ (% nie może być na początku wzorca),

• warunek złożony z ww. warunków ze spójnikiem AND,

• klauzula ORDER BY lub GROUP BY z atrybutami z części wiodącej indeksu.

Dostęp do indeksu

Odwrócone zakresowe przeglądnięcie indeksu

Przeglądnięcie indeksu z pominięciem kolumn

•

Ang. index range scan descending

•

Ang. index skip scan

•

Odmiana zakresowego przeglądnięcia indeksu.

•

Operacja korzystająca z indeksu złożonego dla polecenia, w którym

•

Dane zwracane w kolejności malejącej .

nie występuje kolumna z początku części wiodącej klucza

indeksowego:

•

Stosowane, gdy:

• indeks dzielony jest na mniejsze podindeksy, liczba podindeksów jest

• w poleceniu konieczne posortowanie danych w porządku malejącym, równa liczbie wartości pierwszej kolumny w kluczu indeksowym,

• przy poszukiwaniu wartości mniejszych niż wartość wyspecyfikowana.

• podindeksy skanowane są kolejno – operacja zastępuje pełne przeglądnięcie relacji.

•

Przykład:

• relacja Pracownicy(id_prac, adres, płeć), indeks o strukturze klucza: (płeć, id_prac), zapytanie: select * from Pracownicy where id_prac =

100

• indeks zostaje podzielony na dwa podindeksy: dla wartości płeć = ‘M’ i dla wartości płeć = ‘K’, podindeksy zostają przeskanowane kolejno.

Dostęp do indeksu

Pełne przeglądnięcie indeksu

Szybkie pełne przeglądnięcie indeksu

•

Ang. full index scan

•

Ang. fast full index scan

•

Stosowane, gdy:

•

Stosowane, gdy:

• w warunku polecenia SQL odwołania do kolumn z klucza indeksowego,

• wszystkie kolumny, które są używane w poleceniu SQL, występują w kolumny nie muszą być częścią wiodącą klucza,

kluczu indeksowym,

• brak odwołań do poindeksowanych kolumn w warunku polecenia, ale:

• co najmniej jedna z tych kolumn ma zdefiniowane ograniczenie NOT

• wszystkie kolumny, do których występuje odwołanie w poleceniu (np. w NULL.

klauzuli SELECT), znajdują się w kluczu indeksowym,

•

Zastępuje pełne przeglądnięcie relacji – wynik polecenia SQL

• przynajmniej jedna z tych kolumn nie jest pusta.

uzyskuje się bezpośrednio z indeksu, bez konieczności dostępu do

•

Odczytywane są wszystkie liście indeksu w porządku, bloki indeksu relacji.

odczytywane pojedynczo.

•

Odczytywane są wszystkie liście indeksu przy zastosowaniu

•

Używane głównie do eliminacji operacji sortowania relacji – dane są odczytu wieloblokowego – większa wydajność niż pełne

posortowane wg klucza indeksowego.

przeglądnięcie indeksu, ale nie zostaje zachowane uporządkowanie.

•

Nie może być używany do eliminacji operacji sortowania relacji –

dane nie są posortowane wg klucza indeksowego.

Dostęp do indeksu

Dostęp do indeksu bitmapowego

Połączenie indeksów

•

Składa się z dwóch kroków:

•

Ang. index join

1. dostęp do bitmapy,

•

Stosowane w przypadku, gdy wszystkie kolumny, używane w

2. konwersja bitmapy do adresów rekordów (krok opuszczany w poleceniu SQL, znajdują się w kluczach kilku różnych indeksów.

przypadku możliwości realizacji polecenia bez dostępu do

•

Wynik polecenia uzyskuje się tylko z indeksów, bez konieczności relacji).

dostępu do relacji.

•

W przypadku poleceń z warunkiem złożonym (spójniki AND i OR,

•

Nie może być stosowane do eliminacji operacji sortowania relacji.

negacja), operacje koniunkcji, alternatywy i negacji wykonywane

•

Przykład:

bezpośrednio na bitmapach (widoczne w planie wykonania

SELECT id_prac FROM pracownicy WHERE placa_pod >1000; polecenia).

Range scan(indeks na placa_pod)

Fast Full Scan(indeks na id_prac)

placa_pod

ROWID

id_prac

ROWID

1 0

6 0

0 0

0 1

0 .

1 00

0 1

0 .

1 00

0 1

1 0

0 0

0 1

0 .

1 0A

0 1

A .

1 01

0 E

...

2 0

0 0

0 1

0 .

1 0A

0 1

A .

1 01

0 E

1 0

0 0

0 1

0 .

1 00

0 1

0 .

1 00

0 1

1 0

join (hash)

...

1 0

Wskaźnik zgrupowania indeksu (1)

Wskaźnik zgrupowania indeksu (2)

•

Minimalną jednostką operacji I/O jest blok dyskowy a nie rekord

•

Interpretacja:

• mała wartość (równa lub bliska liczbie bloków tabeli) – dobrze, użycie

•

Statystyka, pozwalająca na porównanie kosztu operacji

indeksu jest korzystne w stosunku do pełnego przeglądnięcia tabeli z powodu konieczności wykonania mniejszej liczby operacji odczytu przeglądnięcia indeksu z kosztem pełnego przeglądnięcia tabeli bloków tabeli (odczytu danych) po dostępie do indeksu (po odczycie adresów rekordów)

•

Określa, jak mocno indeks jest "zsynchronizowany" z tabelą:

• mała wartość – rekordy tabeli z tymi samymi (lub zbliżonymi)

• duża wartość (równa lub bliska liczbie rekordów tabeli) – źle, użycie wartościami poindeksowanej kolumny są skupione w niewielkiej liczbie indeksu jest niekorzystne w stosunku do pełnego przeglądnięcia tabeli bloków

z powodu konieczności wykonania większej liczby operacji odczytu

• duża wartość – rekordy tabeli z tymi samymi (lub zbliżonymi) bloków tabeli po dostępie do indeksu

wartościami poindeksowanej kolumny są rozproszone w dużej liczbie

•

Słownik danych

bloków

SELECT clustering_factor FROM user_indexes

WHERE index_name = ‘PRAC_PK’;

Wskaźnik zgrupowania indeksu (3)

•

Przykład – tabela posiada 9 rekordów, poindeksowana kolumna K1

posiada trzy wartości A, B i C (po trzy rekordy), rekordy zajmują 3

bloki.

•

Przypadek 1. Mała wartość wskaźnika. Niski koszt skanu indeksu –

odczyt A wymaga dostępu do jednego bloku tabeli

BLOK 1

BLOK 2

BLOK 3

•

Przypadek 2. Duża wartość wskaźnika. Wyższy koszt skanu indeksu

– odczyt A wymaga dostępu do wszystkich trzech bloków tabeli BLOK 1

BLOK 2

BLOK 3