Microsoft PowerPoint - 19OptymalizacjaSQL_czesc

Optymalizacja polece

SQL

ęść

Metody poł

cze

metody sortowania, wskazówki

Poł

czenie (1)

•

Operacja binarna – zawsze udział bior

dwie tabele, jedna zostaje

nazwana tabel

zewn

trzn

, druga tabel

wewn

trzn

•

W przypadku polecenia ł

cego wi

cej ni

dwie tabele (np. A , B i

C), poł

czenie realizowane jest zawsze dla pary tabel (np. A z B,

wynik z C, albo A z C i wynik z B, itd.).

•

Podstawowe zasady:

• główna zasada: kolejno

ść

czenia tabel powinna jak najbardziej

ogranicza

zbiór rekordów

• optymalizator szuka w zbiorze ł

czonych tabel takich, których

poł

czenie wyprodukuje 1 rekord – je

li znajdzie, te tabele s

czone na pocz

tku

• w przypadku poł

czenia zewn

trznego tabela zewn

trzna jest

umieszczana w kolejno

ci za tabel

wewn

trzn

Poł

czenie (2)

•

Realizowane przy u

yciu jednego z algorytmów:

• nested loops,

• sort merge,

• hash join.

•

Wybór algorytmu zale

y od:

• rozmiaru tabeli,

• postaci warunku poł

czeniowego,

• spodziewanego rozmiaru wyniku poł

czenia,

• dost

pno

ci i rozmiaru obszaru sortowania,

• warto

ci parametru odczytu wieloblokowego

(DB_FILE_MULTIBLOCK_READ_COUNT).

Algorytm nested loops

•

Stosowany, gdy:

• w poł

czeniu bierze udział mała cz

ęść

rekordów relacji,

• istnieje efektywna metoda dost

pu do danych relacji wewn

trznej

(indeks zało

ony na kolumnie w warunku poł

czeniowym).

•

Główny koszt – koszt odczytu rekordów relacji zewn

trznej i

znalezienia odpowiadaj

cych rekordów relacji wewn

trznej.

•

Algorytm:

•

W planie wykonania
relacja zewn

trzna

ponad relacj

wewn

trzn

NESTED LOOPS

relacja_zewn

trzna

relacja_wewn

trzna

A
B
C
D

3
2
1
3

2
1

1
2

2
3

a
b

b
c

c
d

A
B
B
C

3
2
2
1

d
a
c
b

Relacja zewn

trzna

Relacja wewn

trzna

Wynik poł

czenia

Algorytm sort merge (1)

•

Stosowany, gdy:

•

czone relacje s

niezale

ne (brak poł

czenia kluczem obcym),

•

warunek poł

czeniowy z operatorami: <, <=, >, >= (ale nie !=) i du

rozmiary ł

czonych relacji (zachowuje si

lepiej ni

nested loop),

•

relacja ju

posortowane lub nie ma potrzeby realizacji sortowania

(bo np. istnieje odpowiedni indeks).

•

Główny koszt – koszt wczytania obu relacji do pami

ci i ich

posortowania.

•

Brak podziału na relacj

zewn

trzn

i wewn

trzn

•

Algorytm:

1. Posortowanie obu relacji ze wzgl

du na warto

ci kolumn w warunku

poł

czeniowym.

2. Poł

czenie rekordów o tych samych warto

ciach kolumn w warunku

poł

czeniowym.

A
B

B
C

C
D

3
2

2
1

1
3

2
1

1
2

2
3

a
b

b
c

c
d

C
B

B
A

A
D

1
2

2
3

3
3

1
1

1
2

2
2

b
e

e
a

a
c

sortowanie

zł

czenie

C
C

C
B

B
B

1
1

1
2

2
2

1
1

1
2

2
2

b
e

e
a

a
c

Algorytm sort merge (2)

Algorytm hash join (1)

•

Stosowany, gdy:

• warunek poł

czeniowy jest warunkiem równo

ciowym, i

• ł

czone relacje o du

ym rozmiarze lub wi

ksza cz

ęść

rekordów

mniejszej relacji bierze udział w poł

czeniu.

•

Główny koszt – zbudowanie tabeli haszowej dla relacji zewn

trznej i

odczyt rekordów z relacji wewn

trznej.

•

Relacja zewn

trzna – mniejsza z relacji, najlepiej, je

li mie

ci si

pami

ci.

•

W planie wykonania pierwsza relacja, z której zbudowano tablic

haszow

HASH JOIN

relacja_zewn

trzna

relacja_wewn

trzna

Algorytm hash join (2)

•

Algorytm:

A
B

B
C

C
D

3
2

2
1

1
3

2
1

1
2

2
3

a
b

b
c

c
d

B
C

C
B

B
A

2
1

1
2

2
3

2
1

1
2

2
3

a
b

b
c

c
d

Funkcja_haszuj

ca=

kolumna_poł

czeniowa mod 3

Relacja zewn

trzna

Relacja wewn

trzna

Wynik

Tablica haszowa

•

SORT ORDER BY – gdy w poleceniu wyra

enie ORDER BY.

•

SORT AGGREGATE – gdy w poleceniu wyliczana funkcji grupowa
na całym zbiorze rekordów.

•

SORT (HASH) GROUP BY – gdy w poleceniu wyliczana funkcji
grupowa dla kilku grup rekordów.

SELECT * FROM zespoly
ORDER BY adres DESC;

SELECT MAX(zatrudniony)

FROM pracownicy;

SELECT etat, AVG(placa_pod)

FROM pracownicy GROUP BY etat;

Operacje sortowania (1)

•

SORT (HASH) UNIQUE – gdy w poleceniu u

yto klauzuli DISTINCT.

Uwaga!

Nie mo

na zakłada

uzyskania posortowanego zbioru rekordów

przy operacjach GROUP BY i DISTINCT.

•

SORT JOIN – przy wykonywaniu operacji poł

czenia wg algorytmu

sort merge.

SELECT *

FROM pracownicy JOIN etaty ON placa_pod between
placa_min and placa_max;

SELECT DISTINCT etat

FROM pracownicy;

Operacje sortowania (2)

Zmienne wi

zania w poleceniu SQL (1)

•

Pozwalaj

na „sparametryzowanie” polecenia

SQL> variable zespol number;

SQL> exec :zespol := 10

Procedura PL/SQL została zako

czona pomy

lnie.

SQL> print :zespol

ZESPOL

------

SQL> SELECT count(*) FROM PRACOWNICY WHERE id_zesp = :zespol;

COUNT(*)

--------

SQL> exec :zespol := 20

Procedura PL/SQL została zako

czona pomy

lnie.

SQL> SELECT count(*) FROM PRACOWNICY WHERE id_zesp = :zespol;

COUNT(*)

--------

Zmienne wi

zania w poleceniu SQL (2)

•

Umo

liwiaj

wielokrotne u

ycie tego samego planu wykonania przy

kolejnych wywołania polecenia z ró

nymi warto

ciami zmiennej

zania – tzw. „współdzielenie kursora” (domy

lne działanie)

•

Przy pierwszym wywołaniu polecenia ze zmienn

zania

optymalizator „spogl

da” na warto

ść

zmiennej celem

wygenerowania optymalnego planu

•

Problem – kolejne wywołania tego samego polecenia z innymi
warto

ciami dla zmiennej wi

zania mog

przetwarza

dane o

innej

charakterystyce

te z pierwszego wywołania

•

Rozwi

zanie – optymalizator obserwuje kolejne wywołania i

podejmuje decyzje, czy dla kolejnego wywołania polecenia z inn

warto

zmiennej wi

zania wygenerowa

nowy plan

•

Efekt – by

e wiele planów wykonania dla tego samego

polecenia

Wskazówki (1)

•

Wskazówki (ang. hints) umo

liwiaj

okre

lenie bezpo

rednio w

poleceniu nast

puj

cych elementów pracy optymalizatora:

• celu optymalizacji,

•

cie

ki dost

pu do danych,

• kolejno

ci ł

czonych relacji przy operacji poł

czenia,

• sposobu realizacji poł

czenia

•

Wskazówki umieszcza si

w komentarzu bezpo

rednio po

klauzulach SELECT, INSERT, UPDATE, DELETE, przy czym
pierwszym znakiem wskazówki musi by

+ (plus).

•

Uwaga! Bł

dnie sformułowana wskazówka nie powoduje bł

wykonania polecenia – jest ignorowana!

SELECT

/*+ wskazówka */

… FROM …;

SELECT

--+ wskazówka

… FROM …;

Wskazówki (2)

•

Wybór celu optymalizacji:

• ALL_ROWS

– przepustowo

ść

• FIRST_ROWS

– czas odpowiedzi (wycofywana od Oracle10g),

• FIRST_ROWS(n)

– czas odpowiedzi (pierwszych n krotek).

•

Sposób dost

pu do danych:

•

FULL

(tabela) – pełne przegl

dni

cie tabeli,

• INDEX

(tabela [indeks]) – dost

p za pomoc

indeksu,

• NO_INDEX

(tabela [indeks]) – zakazanie u

ycia indeksu,

• INDEX_COMBINE

(tabela [indeks]) – dost

p za pomoc

indeksu

bitmapowego,

• INDEX_DESC

(tabela [indeks]) – dost

p za pomoc

odwróconego

przeszukiwania indeksu,

Wskazówki (3)

•

Sposób dost

pu do danych (cd):

• INDEX_FFS

(tabela [indeks]) – dost

p za pomoc

szybkiego

przeszukania indeksu,

• NO_INDEX_FFS

(tabela [indeks]) – zakazanie u

ycia szybkiego

przeszukania indeksu,

• INDEX_SS

(tabela [indeks]) – dost

p za pomoc

przegl

dni

cia indeksu

z pomini

ciem kolumn,

• NO_INDEX_SS

(tabela [indeks]) – zakazanie u

ycia przegl

dni

cia

indeksu z pomini

ciem kolumn,

• INDEX_JOIN

(tabela [indeks] …) – wykonanie poł

czenia indeksów,

•

Kolejno

ść

czenia relacji:

• LEADING

(tabela1 tabela2 ...) – okre

la zbiór tabel, które maj

czone jako pierwsze,

• ORDERED

– okre

la,

e tabele maj

czone w takiej kolejno

ci, jak

zostały wymienione w klauzuli FROM.

Wskazówki (4)

•

Algorytm ł

czenia relacji:

• USE_NL

(tabela_wewn

trzna ...) - poł

czenie NESTED LOOPS

• USE_HASH

(tabela_wewn

trzna ...) - poł

czenie HASH JOIN

• USE_MERGE

(tabela1 tabela2 ...) - poł

czenie SORT MERGE

• NO_USE_NL

(...),

NO_USE_HASH

(...),

NO_USE_MERGE

(...) – zakaz

ycia odpowiedniego algorytmu.

•

Inne:

• USE_CONCAT

– wymuszenie zast

pienia zapytania z warunkiem

zło

onym z operatorem OR przez kilka zapyta

, poł

czonych

operatorem UNION_ALL,

• NO_EXPAND

– zabronienie wykonania powy

szej transformacji.

• NO_QUERY_TRANSFORMATION

– zakazanie wszystkich transformacji

polecenia (przed budow

planu wykonania)

• DYNAMIC_SAMPLING

(tabela poziom_próbkowania) – okre

lenie

poziomu dynamicznego próbkowania (wy

szy poziom – wi

kszy zakres

próbkowania, zakres: 0-10)