Microsoft PowerPoint - 19OptymalizacjaSQL_czesc

Optymalizacja polece

SQL

ęść

Statystyki i histogramy,

metody dost

pu do danych

Statystyki (1)

•

Informacje, opisuj

ce dane i struktury obiektów bazy danych.

•

Przechowywane w słowniku danych.

•

ywane przez optymalizator do oszacowania:

• selektywno

ci predykatów polecenia,

• kosztu u

ycia

cie

ek dost

pu,

• kosztu operacji I/O i czasu procesora,

• kosztu planu wykonania polecenia

•

Tylko

aktualne

statystyki u

yteczne!

• Statystyki s

statyczne – nie s

automatycznie uaktualniane przy

zmianie danych.

Statystyki (2)

•

Przykłady statystyk:

• dla relacji:

• liczba rekordów,

• liczba bloków,

•

rednia długo

ść

rekordu,

• dla atrybutu relacji:

• liczba ró

nych warto

ci,

• 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.

Statystyki (3)

•

Statystyki mog

gromadzone automatycznie (przez

dedykowany proces SZBD) lub r

cznie (na

żą

danie u

ytkownika)

przy u

yciu pakietu DBMS_STATS.

•

W przypadku braku statystyk dla obiektów u

ywanych w zapytaniu

przed wykonaniem zapytania optymalizator realizuje dynamiczne
próbkowanie statystyk.

Histogramy (1)

•

Histogram – szczegółowa statystyka opisuj

ca rozkład warto

okre

lonej kolumny relacji.

•

Rodzaje:

• histogram o zrównowa

onej wysoko

ci (ang. height balanced) – zbiór

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):

• równomierny rozkład warto

ci atrybutu:

• nierównomierny rozkład warto

ci atrybutu:

1 10 20 30 40 50 60 70 80 90 100

1 10 10 10 30 40 40 40 65 80 100

Histogramy (2)

•

Rodzaje (cd):

• histogram cz

stotliwo

ci (ang. frequency) – ka

da warto

ść

kolumny

odpowiada jednemu przedziałowi, ka

dy przedział zawiera liczb

wyst

pie

tej warto

ci; tworzony wtedy, gdy liczba warto

ci kolumny

jest mniejsza b

równa

żą

danej liczbie przedziałów histogramu.

•

Histogramy nale

y tworzy

tylko dla kolumn z nierównomiernym

rozkładem warto

ci (ang. skewed data), cz

sto u

ywanych w

warunkach zapytania.

•

Gdy zmieni si

rozkład danych kolumny, konieczne jest ponowne

wygenerowanie histogramu,

czne zbieranie statystyk

•

Metody:

• na podstawie pełnych danych,

• szacowanie na podstawie próbki, próbka okre

lana w

procentach liczby rekordów.

•

Procedury zbieraj

ce statystyki:

• DBMS_STATS.GATHER_INDEX_STATS – dla indeksu,

• DBMS_STATS.GATHER_TABLE_STATS – dla relacji.

•

Procedury usuwaj

ce statystyki:

• DBMS_STATS.DELETE_INDEX_STATS – dla indeksu,

• DBMS_STATS.DELETE_TABLE_STATS – dla relacji,

• DBMS_STATS.DELETE_COLUMN_STATS – dla kolumny.

Zbieranie statystyk dla indeksu

•

li warto

ść

<procentowa_wielko

ść

_próbki> okre

lono jako:

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

• liczb

z przedziału <0,00001; 100>, wówczas szacowanie na podstawie

próbki o zadanym rozmiarze,

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

•

Uwaga! Od Oracle10g statystyki dot. indeksów s

gromadzone automatycz-

nie podczas tworzenia lub przebudowy indeksu.

exec DBMS_STATS.GATHER_INDEX_STATS(

ownname => <nazwa_schematu>, indname => <nazwa_indeksu>,
estimate_percent => <procentowa_wielko

ść

_próbki>);

exec DBMS_STATS.GATHER_INDEX_STATS(

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

Zbieranie statystyk dla relacji (1)

•

METHOD_OPT – okre

la zakres zbieranych statystyk:

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

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

dla wszystkich kolumn,

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

statystyki dla poindeksowanych kolumn,

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

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

exec DBMS_STATS.GATHER_TABLE_STATS(

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

ść

_próbki>,

method_opt => <rodzaj_statystyk>,
cascade =>

<DBMS_STATS.AUTO_CASCADE

TRUE | FALSE> );

Zbieranie statystyk dla relacji (2)

•

<klauzula SIZE> – SIZE { liczba | REPEAT | AUTO | SKEWONLY }:

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

• REPEAT – powtórzenie zbierania histogramów dla kolumn, które maj

histogramy,

• AUTO – SZBD okre

li, dla których kolumn zbiera

histogramy na podstawie

obci

ąż

enia i rozkładu danych kolumny,

• SKEWONLY – SZBD okre

li, dla których kolumn zbiera

histogramy tylko na

podstawie rozkładu danych kolumny (bez analizy obci

ąż

enia).

•

FOR ALL COLUMNS SIZE AUTO – warto

ść

domy

lna dla par. METHOD_OPT:

• statystyki tabeli,

• podstawowe statystyki wszystkich kolumn tabeli,

• histogramy dla kolumn wyznaczonych na podstawie wcze

niejszych

obserwacji dotycz

cych obci

ąż

enia i rozkładu warto

ci.

Zbieranie statystyk dla relacji (3)

exec DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'SCOTT', tabname => 'PRACOWNICY',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR COLUMNS placa_pod SIZE AUTO, nazwisko SIZE AUTO');

exec DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'SCOTT', tabname => 'PRACOWNICY',
method_opt => 'FOR ALL INDEXED COLUMNS',

cascade => TRUE);

•

Uwaga! Od Oracle12c statystyki dotycz

ce tabel zostaj

zebrane automatycznie

w sytuacji, gdy tabela, do której ładowane s

dane

cie

bezpo

redni

(polecenie INSERT /*+ APPEND */, dane umieszczane od razu w plikach bazy
danych z pomini

ciem bufora bazy danych), była poprzednio pusta:

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

• usuni

to z tabeli wszystkie rekordy.

Statystyki w słowniku bazy danych

•

Dla relacji:

• USER_TABLES, USER_TAB_STATISTICS

•

Dla kolumn:

• USER_TAB_COLUMNS, USER_TAB_COL_STATISTICS,

USER_TAB_HISTOGRAMS

•

Dla indeksów:

• USER_INDEXES, USER_IND_STATISTICS

SELECT num_rows, blocks, last_analyzed, sample_size
FROM USER_TAB_STATISTICS
WHERE table_name = 'PRACOWNICY';

SELECT num_distinct, low_value, high_value, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'PRACOWNICY'
AND column_name = 'NAZWISKO';

Usuwanie statystyk

exec DBMS_STATS.DELETE_INDEX_STATS(

ownname => <nazwa_schematu>, indname => <nazwa_indeksu>);

exec DBMS_STATS.DELETE_TABLE_STATS(

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

exec DBMS_STATS.DELETE_COLUMN_STATS(

ownname => <nazwa_schematu>, tabname => <nazwa_relacji>,
colname => <nazwa_kolumny>, col_stat_type => <rodzaj_usuwanych_statystyk>);

•

COL_STAT_TYPE:

• HISTOGRAM – usuwany jest histogram dla kolumny, podstawowe

statystyki kolumny pozostaj

• ALL – usuwane s

wszystkie statystyki dla kolumny (warto

ść

domy

lna).

Metody dost

pu do danych

•

Okre

laj

, w jaki sposób dane polecenia SQL s

odczytywane z

miejsca ich fizycznej lokalizacji.

•

Dost

p do tabeli:

• pełne przegl

dni

cie,

• dost

p przy pomocy adresu rekordu.

•

Dost

p do indeksu:

• unikalne przegl

dni

cie indeksu,

• (odwrócone) zakresowe przegl

dni

cie indeksu,

• przegl

dni

cie indeksu z pomini

ciem kolumn,

• pełne przegl

dni

cie indeksu,

• szybkie pełne przegl

dni

cie indeksu,

• dost

p do indeksu bitmapowego,

• poł

czenie indeksów.

Przy dost

pie do indeksu dane zwykle zwracane w kolejno

ci rosn

cej.

•

Ogólne zasady dost

pu do danych:

• odczyt du

ej cz

ęś

ci rekordów relacji – pełne przegl

dni

cie relacji,

• odczyt pojedynczych rekordów relacji – dost

p za pomoc

indeksu.

Pełne przegl

dniecie tabeli

•

Ang. full table scan

•

Sekwencyjny odczyt wszystkich bloków danych, w których tabela
przechowuje swoje rekordy, odfiltrowanie rekordów nie
spełniaj

cych zdefiniowanych w poleceniu SQL kryteriów selekcji

(np. w klauzuli WHERE).

•

Stosowane gdy:

• brak indeksu dla relacji lub

nie mo

na u

istniej

cych

indeksów,

• zostanie odczytana du

a cz

ęść

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

• rozmiar tabeli jest niewielki.

•

liwy odczyt wieloblokowy – pobranie w jednej operacji I/O wielu

przyległych bloków danych, bardziej efektywne ni

wiele odczytów

pojedynczych bloków.

DB_FILE_MULTIBLOCK_READ_COUNT

Dost

p do tabeli

Dost

p przy pomocy adresu rekordu

•

Ang. rowid scan

•

Odszukanie rekordu relacji na podstawie dostarczonego adresu
rekordu (rowid).

•

Najszybszy sposób dost

do rekordów tabeli.

•

ródło adresu rekordu:

• warunek selekcji polecenia SQL,

• pobranie z indeksu tabeli.

SELECT * FROM pracownicy
WHERE rowid = ‘AAAMMUAAEAAAAAtAAG’;

Dost

p do tabeli

Unikalne przegl

dni

cie indeksu

•

Ang. index unique scan

•

Dost

p do indeksu unikalnego, operacja zwraca co najwy

ej jeden

adres rekordu.

•

Stosowane, gdy w poleceniu SQL zastosowano warunek
równo

ciowy z atrybutem, na którym zdefiniowano indeks unikalny

(równie

ograniczenia klucz podstawowy i klucz unikalny).

Dost

p do indeksu

Zakresowe przegl

dni

cie indeksu

•

Ang. index range scan

•

Dost

p do indeksu unikalnego (warunek inny ni

równo

ciowy) lub

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

•

Stosowane, gdy w poleceniu SQL:

• warunek selekcji z kolumnami z cz

ęś

ci wiod

cej indeksu, takie jak:

• 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

•

Ang. index range scan descending

•

Odmiana zakresowego przegl

dni

cia indeksu.

•

Dane zwracane w kolejno

ci malej

cej .

•

Stosowane, gdy:

• w poleceniu konieczne posortowanie danych w porz

dku malej

cym,

• przy poszukiwaniu warto

ci mniejszych ni

warto

ść

wyspecyfikowana.

Dost

p do indeksu

Przegl

dni

cie indeksu z pomini

ciem kolumn

•

Ang. index skip scan

•

Operacja korzystaj

ca z indeksu zło

onego dla polecenia, w którym

nie wyst

puje kolumna z pocz

tku cz

ęś

ci wiod

cej klucza

indeksowego:

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

równa liczbie warto

ci pierwszej kolumny w kluczu indeksowym,

• 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

•

Ang. full index scan

•

Stosowane, gdy:

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

kolumny nie musz

ęś

wiod

klucza,

• brak odwoła

do poindeksowanych kolumn w warunku polecenia, ale:

• wszystkie kolumny, do których wyst

puje odwołanie w poleceniu (np. w

klauzuli SELECT), znajduj

w kluczu indeksowym,

• przynajmniej jedna z tych kolumn nie jest pusta.

•

Odczytywane s

wszystkie li

cie indeksu w porz

dku, bloki indeksu

odczytywane pojedynczo.

•

ywane głównie do eliminacji operacji sortowania relacji – dane s

posortowane wg klucza indeksowego.

Dost

p do indeksu

Szybkie pełne przegl

dni

cie indeksu

•

Ang. fast full index scan

•

Stosowane, gdy:

• wszystkie kolumny, które s

ywane w poleceniu SQL, wyst

puj

kluczu indeksowym,

• co najmniej jedna z tych kolumn ma zdefiniowane ograniczenie NOT

NULL.

•

Zast

puje pełne przegl

dni

cie relacji – wynik polecenia SQL

uzyskuje si

bezpo

rednio z indeksu, bez konieczno

ci dost

pu do

relacji.

•

Odczytywane s

wszystkie li

cie indeksu przy zastosowaniu

odczytu wieloblokowego – wi

ksza wydajno

ść

pełne

przegl

dni

cie indeksu, ale nie zostaje zachowane uporz

dkowanie.

•

Nie mo

e by

ywany do eliminacji operacji sortowania relacji –

dane nie s

posortowane wg klucza indeksowego.

Dost

p do indeksu

Dost

p do indeksu bitmapowego

•

Składa si

z dwóch kroków:

1. dost

p do bitmapy,

2. konwersja bitmapy do adresów rekordów (krok opuszczany w

przypadku mo

liwo

ci realizacji polecenia bez dost

pu do

relacji).

•

W przypadku polece

z warunkiem zło

onym (spójniki AND i OR,

negacja), operacje koniunkcji, alternatywy i negacji wykonywane
bezpo

rednio na bitmapach (widoczne w planie wykonania

polecenia).

Dost

p do indeksu

Poł

czenie indeksów

•

Ang. index join

•

Stosowane w przypadku, gdy wszystkie kolumny, u

ywane w

poleceniu SQL, znajduj

w kluczach kilku ró

nych indeksów.

•

Wynik polecenia uzyskuje si

tylko z indeksów, bez konieczno

dost

pu do relacji.

•

Nie mo

e by

stosowane do eliminacji operacji sortowania relacji.

•

Przykład:

SELECT id_prac FROM pracownicy WHERE placa_pod >1000;

Range scan(indeks na placa_pod)

Fast Full Scan(indeks na id_prac)

1600

00000001.001.001

placa_pod

ROWID

120

...

140

join (hash)

120

...

2000

00000001.0A1.01E

...

140

ROWID

id_prac

00000001.0A1.01E

...

00000001.001.001

Dost

p do indeksu

Wska

nik zgrupowania indeksu (1)

•

Minimaln

jednostk

operacji I/O jest blok dyskowy a nie rekord

•

Statystyka, pozwalaj

ca na porównanie kosztu operacji

przegl

dni

cia indeksu z kosztem pełnego przegl

dni

cia tabeli

•

Okre

la, jak mocno indeks jest "zsynchronizowany" z tabel

• mała warto

ść

– rekordy tabeli z tymi samymi (lub zbli

onymi)

warto

ciami poindeksowanej kolumny s

skupione w niewielkiej liczbie

bloków

• du

a warto

ść

– rekordy tabeli z tymi samymi (lub zbli

onymi)

warto

ciami poindeksowanej kolumny s

rozproszone w du

ej liczbie

bloków

Wska

nik zgrupowania indeksu (2)

•

Interpretacja:

• mała warto

ść

(równa lub bliska liczbie bloków tabeli) – dobrze, u

ycie

indeksu jest korzystne w stosunku do pełnego przegl

dni

cia tabeli z

powodu konieczno

ci wykonania mniejszej liczby operacji odczytu

bloków tabeli (odczytu danych) po dost

pie do indeksu (po odczycie

adresów rekordów)

• du

a warto

ść

(równa lub bliska liczbie rekordów tabeli) –

le, u

ycie

indeksu jest niekorzystne w stosunku do pełnego przegl

dni

cia tabeli

z powodu konieczno

ci wykonania wi

kszej liczby operacji odczytu

bloków tabeli po dost

pie do indeksu

•

Słownik

danych

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

bloki.

•

Przypadek 1. Mała warto

ść

wska

nika. Niski koszt skanu indeksu –

odczyt A wymaga dost

pu do jednego bloku tabeli

•

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

BLOK 1

BLOK 2

BLOK 3