Projekt współfinansowany przez Unię Europejską w ramach Europejskiego Funduszu Społecznego

„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

Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83www.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

Największa przepustowość – optymalizacja pod kątem wykorzystania jak najmniejszej liczby zasobów w celu wykonania danego zapytania

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

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

Projekt współfinansowany przez Unię Europejską w ramach Europejskiego Funduszu Społecznego

„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

Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83www.kapitalludzki.p.lodz.pl

25