Document

„Relacyjne Bazy Danych (Oracle)”
Prezentacja jest współfinansowana przez
Unię Europejską w ramach
Europejskiego Funduszu Społecznego w projekcie pt.
„Innowacyjna dydaktyka bez ograniczeń - zintegrowany rozwój Politechniki Łódzkiej zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolności do
zatrudniania osób niepełnosprawnych”
Prezentacja dystrybuowana jest bezpłatnie
Projekt współfinansowany przez Unię Europejską
w ramach Europejskiego Funduszu Społecznego
Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83
www.kapitalludzki.p.lodz.pl
1
Optymalizacja

Zasoby wykorzystywane przez bazę danych
można podzielić na cztery grupy:

Jednostka obliczeniowa CPU (Procesor/y)

Pamięć masowa (Dysk twardy, macierz dyskowa)

Zasoby sieciowe (połączenie sieciowe)

Zasoby pamięci operacyjnej (pamięć RAM)
CPU

Zasów wykorzystywany we wszystkich
operacjach związanych z bazą danych


Szybkości działania większości z nich nie można
poprawić
W przypadku zapytań SQL najczęściej
wykorzystywany jest czas procesora do
wykonania operacji

Przetwarzanie zapytania (Statement Parse)

Obliczenia matematyczne w zapytaniach
Pamięć masowa


W pamięci masowej przetrzymywane są dane
bazy, szybkość pamięci masowej ma kluczowy
wpływ na działanie baz danych w zależności od
ich rozmiaru
W większości zastosowań wykorzystywane są
dyski twarde, często połączone w macierze
Pamięć masowa

Dysk twardy

Budowa

Cechy kluczowe wydajności dysku twardego

Technologie wykorzystywane w pamięci masowej

Dyski mechaniczne





ATA/SATA/SATA II (TCQ, NCQ)
SCSI/SAS
FibreChannel (FC)
Dyski SSD (Solid State Drive)
Dyski hybrydowe
Pamięć masowa

Macierze dyskowe

Macierze programowe (software)

Macierze sprzętowe

Zasada działania

Wpływ na wydajność bazy danych
Zasoby sieciowe

Baza danych zwykle rezyduje na innej
maszynie niż korzystające z niej aplikacje



Dane powstałe w wyniku wykonania zapytania SQL
muszą być przesłane do klienta
W zależności od rozmiaru danych wyjściowych sieć
może mieć duży wpływ na wydajność bazy danych
z punktu widzenia użytkownika
Głównymi cechami posiadającymi wpływ na
wydajności są: przepustowość, opóźnienie
(oraz jego wariancja), jakość łącza
Pamięć operacyjna



Często pomijana w przypadku baz danych
Szybkość pamięci operacyjnej ma wpływ na
działanie każdej aplikacji
W bazie danych Oracle ilość pamięci
operacyjnej pozwala na zwiększenie rozmiarów
segmentów (SGA, PGA,..), a w szczególności

Buffer Cache

Library Cache
SQL Tuning

Czasami wydajność wykonania operacji SQL
zależy od jego formy

Zadanie zapytania SQL w innej formie może mieć
wpływ na czas jego wykonania
Optymalizator CBO

Baza danych Oracle posiada wbudowany
komponent, którego celem jest wykonanie
zapytania w jak najkrótszym czasie

CBO (Cost Based Optimizer)



Optymalizator ustala koszt wykonania
poszczególnych etapów zapytania
W starszych wersjach bazy dostępny był
optymalizator działający na regułach
Optymalizator CBO analizuje zapytanie, ustala
plan dostępu do zasobów w taki sposób, aby
koszt wykonania zapytania był jak najmniejszy
Cost Based Optimizer

Etapy działania

Ewaluacja wyrażeń występujących w zapytaniu

Transformacja zapytania (zmiana jego treści)

Wybranie celu optymalizacji (domyślnie:
przepustowość)

Wybór ścieżek dostępu do danych

Wybór kolejności łączenia tabel (JOIN)
Cost Based Optimizer

Cele działania



Najszybsza odpowiedź – optymalizacja mająca na celu
zwrócić pierwszy wiersz wyniku zapytania w możliwie
najkrótszym czasie i przy wykorzystaniu możliwie
najmniejszej ilości zasobów
Parametr OPTIMIZER_MODE


Największa przepustowość – optymalizacja pod kątem
wykorzystania jak najmniejszej liczby zasobów w celu
wykonania danego zapytania
ALL_ROWS,FIRST_ROWS_n
(1,10,100,1000),FIRST_ROWS, RULE (nie używany)
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
Explain Plan

Plan wykonania zapytania SQL składa się z
sekwencji akcji uruchamianych po stronie bazy
danych mających na celu pobranie danych z
pamięci masowej

Plan ma formę drzewa, każde dziecko w drzewie
jest kolejną operacją. Aby rodzic otrzymał dane,
muszą się uruchomić akcje w jego dzieciach (nie
muszą się jednak zakończyć – dane mogą być
oddawane w czasie rzeczywistym)
Podpowiedzi

Na przebieg procesu działania optymalizatora można
wpływać stosując tzw: podpowiedzi (Hints)

SELECT /*+ HINT */ FROM tabela WHERE...

SELECT --+ HINT – FROM tabela WHERE...

Np: skanowanie całej tabeli: /*+ FULL(tabela) */

Np: zmiana celu CBO /*+ ALL_ROWS */
Praktyki

Ograniczanie liczby wierszy w fazie
początkowej zapytania


Zmiana konstrukcji warunku może poprawić
wydajność
W celu dokładnej analizy planu wykonania
zapytania można skorzystać z funkcji autotrace


set autotrace on
select PLAN_TABLE_OUTPUT from TABLE
(DBMS_XPLAN.DISPLAY());
Perspektywa V$SQL_PLAN



Perspektywa pozwala na podgląd planu
wykonania zapytania w postaci tabeli
Zagregowanie informacje na temat planu
wykonania zapytania można uzyskać z
perspektywy V$SQL_PLAN_STATISTICS_ALL
select * from V$SQL_PLAN_STATISTICS_ALL
order by sql_id
Najważniejsze operacje







TABLE ACCESS FULL – pełne skanowanie tabeli
TABLE ACCESS ROWID RANGE – przeglądanie tabeli po
zestawie wartości ROWID
TABLE ACCESS BY INDEX ROWID – przeglądanie tabeli po
ROWID uzyskanych ze skanowania indeksu
SORT AGGREGATE – pobranie wiersza będącego wynikiem
funkcji agregującej
SORT GROUP BY – sortowanie wierszy dla operacji
grupowania (wyników grupowania)
SORT ORDER BY – sortowanie przy zapytaniach z
wykorzystaniem ORDER BY
SORT UNIQUE – przy wykorzystaniu DISTINCT
Najważniejsze operacje





BITMAP (AND,OR,MERGE,MINUS) – wykonanie operacji
na indeksach bitmapowych
COUNT – operacja zliczania wierszy
FILTER – operacja otrzymująca zestaw wierszy a
następnie eliminująca te, które nie spełniają
odpowiedniego warunku
HASH GROUP BY – operacja przeprowadzająca
hashowanie tabeli w celu wykonania grupowania
HASH UNIQUE – wykorzystane przy DISTINCT
Najważniejsze operacje





INDEX RANGE SCAN – pozyskanie zestawu ROWID
spełniającego kryteria na podstawie skanowania indeksu
INDEX FULL SCAN (DESCENDING) – skanowanie całego
indeksu w celu uzyskania pełnego zestawu ROWID
spełniającego kryteria
VIEW – operacja wykonywana na perspektywie – wyniki są
zwracane do pozostałych operacji
REMOTE – operacja na zdalnej bazie danych
NESTED LOOPS, MERGE JOIN, HASH JOIN – metody
wykorzystywane przy łączeniu tabel
JOIN

Nested Loops – pętle zagnieżdżone. Efektywne
przy łączeniu mniejszych tabel. Dla każdego
wiersza z pierwszej tabeli sprawdzany jest
warunek z każdym wierszem drugiej tabeli.


Dla dużych tabel metoda jest nieefektywna
Metoda może wykorzystywać indeksy w celu
uproszczenia pętli wewnętrznej, jednak może to nie
być optymalne
JOIN
Hash JOIN – łączenie dużych tabel. Tworzona
jest tablica haszująca dla mniejszej tabeli, po
czym wiersze większej tabeli porównywane są
z wartością w tablicy haszującej.

Operacja blokuje się na czas utworzenia tablicy
haszującej, dopiero po jej zbudowaniu wyniki mogą
być zwracane użytkownikowi
JOIN

Merge JOIN (sort-merge JOIN) – Łączenie
danych z użyciem sortowania. Dane muszą
zostać posortowane aby ten typ łączenia był
efektywny



Dane mogą być posortowane od początku, lub
kolumny wykorzystane w klauzuli JOIN mogą
posiadać indeks typu B-Tree
Zaletą tej metody jest fakt, że każda tabela
skanowana jest jeden raz
Wada – konieczność posortowania obu tabel po
kluczu użytym w klauzuli JOIN
Przykłady

Przykłady

„Relacyjne Bazy Danych (Oracle)”
Prezentacja jest współfinansowana przez
Unię Europejską w ramach
Europejskiego Funduszu Społecznego w projekcie pt.
„Innowacyjna dydaktyka bez ograniczeń - zintegrowany rozwój Politechniki Łódzkiej zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolności do
zatrudniania osób niepełnosprawnych”
Prezentacja dystrybuowana jest bezpłatnie
Projekt współfinansowany przez Unię Europejską
w ramach Europejskiego Funduszu Społecznego
Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83
www.kapitalludzki.p.lodz.pl
25