Strojenie sql optymalizacja poleceń sql



Pobieranie 143,24 Kb.
Data15.02.2018
Rozmiar143,24 Kb.


Oracle

Strojenie SQL

Optymalizacja poleceń SQL

Optymalizacja poleceń SQL jest procesem szukania optymalnego planu wykonania polecenia. Optymalny plan wykonania jest to plan o najmniejszej funkcji kosztu. Przez koszt rozumie się czas zajętości procesora, urządzeń we/wy, urządzeń sieciowych, wykorzystanie innych zasobów systemu, np. wielkości zajętej pamięci operacyjnej.

Optymalizacja poleceń jest procesem kosztownym, z tego względu wykorzystuje się różnego rodzaju metody przybliżone. Oracle wykorzystuje dwie metody optymalizacji: regułową i kosztową.

Optymalizacja kosztowa została wprowadzona w wersji 7 bazy danych i powinna być wykorzystywana we wszystkich nowych aplikacjach.

Optymalizacja regułowa jest wspierana ze względu na kompatybilność ze starszymi wersjami systemu.




Optymalizator regułowy

  • Oparty na rankingu

  • Zawsze dostępny




    1. Optymalizacja regułowa


Optymalizacja regułowa korzysta z rankingu operacji. Operacje te są uporządkowane wg kosztu ich wykonania. Jako pierwsza w tym rankingu znajduje się operacja najmniej kosztowna, a jako ostatnia najbardziej kosztowna.

Optymalizator regułowy wybiera w pierwszej kolejności te plany, które wykorzystują operacje ze szczytu rankingu. Poniższa tabela zawiera ranking operacji wykorzystywany przez optymalizator regułowy:



Pozycja

Operacja

1

Odczyt pojedynczego rekordu za pomocą adresu logicznego ROWID

2

Odczyt pojedynczego rekordu za pomocą połączenia tabel w klastrze

3

Odczyt pojedynczego rekordu za pomocą klucza haszowego

4

Odczyt pojedynczego rekordu za pomocą indeksu unikalnego

5

Połączenie tabel znajdujących się w klastrze

6

Odczyt rekordów za pomocą klucza haszowego

7

Odczyt rekordów za pomocą indeksu klastra

8

Odczyt rekordu za pomocą indeksu złożonego

9

Odczyt rekordów za pomocą indeksu jednokolumnowego

10

Odczyt rekordów za pomocą indeksu w zakresie wartości klucza indeksu obustronnie określonym. Przykładowo taki odczyt może być wykonany, dla indeksu id_prac tabeli pracownicy, w przypadku wydania następującego zapytania:

SELECT COUNT(*) FROM PRACOWNICY

WHERE ID_PRAC BETWEEN 100 AND 200;

11

Odczyt rekordów za pomocą indeksu w zakresie wartości klucza indeksu jednostronnie określonym. Taki odczyt może być wykonany przykładowo dla zapytania:

SELECT COUNT(*) FROM PRACOWNICY

WHERE ID_PRAC < 100;

12

Połączenie tabel metodą sort-merge

13

Wyznaczenie wartości funkcji max i min na kolumnach indeksowanych

14

Posortowanie tabeli (order by) na podstawie poindeksowanych kolumn

15

Sekwencyjny odczyt wszystkich rekordów tabeli


Optymalizator kosztowy

  • Oparty na funkcji kosztu

  • Wybiera najmniej kosztowny plan wykonania

  • Musi posiadać wygenerowane statystyki dla obiektów




    1. Optymalizacja kosztowa


Optymalizacja kosztowa opiera się o funkcję kosztu, która umożliwia oszacowanie kosztu wykonania danego planu. Koszt uwzględnia czas zajętości procesora, urządzeń we/wy, zajętość pamięci operacyjnej itp. Koszt jest proporcjonalny do czasu realizacji polecenia SQL. Generowanie planu wykonania odbywa się w następujących krokach:

  • generowanie wszystkich możliwych planów wykonania

  • oszacowanie kosztu wykonania każdego planu

  • wybranie planu o najmniejszej wartości funkcji kosztu

Optymalizator kosztowy systemu Oracle zakłada duże obciążenie systemu : wielu równocześnie pracujących użytkowników i stosunkowo mały współczynnik trafień w bufor bazy danych. Takie plany nie muszą być optymalne dla środowiska jednodostępnego z bardzo dużym buforem danych; i odwrotnie, polecenie SQL dostrojone w środowisku jednodostępnym może być wykonane nieefektywnie w środowisku wielodostępnym.

Do wyznaczenia, przez optymalizator kosztowy, kosztu wykonania poleceń SQL są niezbędne dodatkowe informacje zwane statystykami, a w szczególności kiedy chcemy określić rozkład danych - wtedy mamy do czynienia z histogramami.

W celu zagwarantowania poprawności planów generowanych przez optymalizator kosztowy, należy regularnie zbierać statystyki. Częstotliwość zbierania tych statystyk zależy od intensywności zmian w bazie danych.


Zbieranie statyStyk


  • Polecenie ANALYZE

  • Pakiet DBMS_STATS

  • Automatycznie dobierana wielkość próbki SAMPLE_AUTO_SIZE

  • Wartości statystyk w widokach DBA_TABLES, USER_TABLES




      1. Zbieranie statystyk dla optymalizatora kosztowego


Przy pomocy polecenia ANALYZE można zbierać statystyki dla tabeli, indeksu, klastra. Dokładna składnia tego polecenia zostanie omówiona w jednym z późniejszych rozdziałów. Nie jest to zalecana przez Oracle metoda zbierania statystyk, zamiast tego polecenia proponowane są procedury z pakietu DBMS_STATS.

W przypadku pakietu DBMS_STATS można skorzystać z następujących procedur:



  • Gather_Table_Stats – zbieranie statystyk dla konkretnej tabeli

  • Gather_Index_Stats – zbieranie statystyk dla wskazanego indeksu

  • Gather_Schema_Stats – obliczenie statystyk dla wszystkich obiektów w danym schemacie

  • Gather_Database_Stats - wyliczenie statystyk dla wszystkich obiektów w bazie

  • Gather_System_Stats – zebranie statystyk obciążenia procesora oraz operacji we/wy

Uproszczona składnia powyższych procedur jest podobna i tak dla zbierających statystyki dla indeksu, tabeli jest następująca:

DBMS_STATS.GATHER_TABLE_STATS(‘właściciel tabeli’,’nazwa tabeli’,’nazwa partycji domyślnie Null, procent przybliżenia oszacowania)

Dla schematu:

DBMS_STATS.GATHER_SCHEMA_STATS(‘właściciel schematu’, procent przybliżenia)

Często problemem jest dobranie wystarczającego procentu oszacowania, który zapewni wiarygodne wyniki, a jednocześnie generowanie statystyk nie obciąży znacząco bazy. Dla przybliżenia wielkości 100% można uzyskać najbardziej dokładne wyniki, ale kosztem dużego obciążenia bazy. Gdy procent będzie zbyt mały, dane mogą być niedokładne. Rozwiązaniem tego problemu jest parametr DBMS_STATS.AUTO_SAMPLE_SIZE, dzięki któremu Oracle sam dobierze odpowiednią wielkość próbki danych np.:

DBMS_STATS.GATHER_SCHEMA_STATS(‘właśc.’,DBMS_STATS.AUTO_SAMPLE_SIZE)

Zebranie statystyk jedną z powyższych procedur spowoduje wypełnienie kolumn: NUM_ROWS – liczba wierszy w tabeli, BLOCKS – liczba bloków, które zajmuje obiekt, EMPTY_BLOCKS – liczba bloków powyżej HWM, AVG_ROW_LEN – średnia długość wiersza, SAMPLE_SIZE – wielkość próbki poddanej analizie, LAST_ANALYZED – czas ostatniego zbierania statystyk.


AUTOMATYCZNE Zbieranie statYstyk


  • Dla tabel posiadających nieaktualne statystyki

  • Dla tabel nie posiadających statystyk

  • Procedura ALTER_SCHEMA_TAB_MONITORING

  • Procedura ALTER_DATABASE_TAB_MONITORING

  • Klauzula MONITORING

  • Widok DBA_TAB_MODIFICATIONS




      1. Automatyczne zbieranie statystyk


Nową funkcją która pojawiła się od Oracle 9i jest możliwość automatycznego zbierania statystyk dla tabel, które nie mają ich zebranych, lub mają je nieaktualne. Automatyczne zbieranie statystyk można uruchomić posługując się rozbudowaną wersją poleceń GATHER_SCHEMA_STATS lub GATHER_DATABASE_STATS o opcje OPTIONS i objlist.

Parametr OPTIONS może przyjmować wartości:

GATHER STALE – zebranie statystyk dla obiektów, które mają je nieaktualne

GATHER – zebranie informacji dla wszystkich obiektów /wartość domyślana/

GATHER EMPTY – dla obiektów nie posiadających statystyk

LIST STALE – utworzenie listy tabel, indeksów z nieaktualnymi statystykami

LIST EMPTY - utworzenie listy tabel, indeksów nie posiadających statystyk

Parametr objlist jest przewidziany jako parametr wyjściowy dla listy tabel uzyskanej przy pomocy LIST STALE lub LIST EMPTY. Typ parametru DBMS_STATS.OBJECTTAB

Przykładowe polecenie:


  • DBMS_STATS.GATHER_SCHEMA_STATS(‘STUDENT’,10, OPTIONS=>’GATHER STALE’);

Aby serwer Oracle mógł automatycznie decydować czy należy zebrać statystyki, należy włączyć obserwację tabel pod kątem dokonywanych w nich zmian. Można tego dokonać przy pomocy poleceń:

  • DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING(‘właściciel’,TRUE włączenie monitorowania)

  • DBMS_STATS.ALTER_DATABASE_TAB_MONITORING(TRUE włączenie monitorowania, FALSE – monitorowanie obiektów systemowych);

  • Przy poleceniu CREATE TABLE lub ALTER TABLE klauzula MONITORING

Każde z powyższych poleceń spowoduje obserwacje tabel pod kątem ilości operacji INSERT, UPDATE, DELETE od ostatniego zebrania statystyk. Statystyki będą uznane za nieaktualne, jeżeli 10% wierszy w tabeli będzie zmienione. Modyfikacje na monitorowanych tabelach można obserwować korzystając z widoku DBA_TAB_MODIFICATIONS. Przy włączonej opcji monitorowania, można korzystając z pakietu DBMS_JOB zautomatyzować zbieranie statystyk, gdzie serwer będzie decydował, czy należy je zbierać i dla których obiektów.


Zbieranie statystyk systemowych

  • Zalecane przez Oracle

  • Odzwierciedlają aktualne obciążenie serwera bazodanowego

  • Procedura CREATE_STAT_TABLE

  • Procedura GATHER_SYSTEM_STATS

  • Procedura IMPORT_SYSTEM_STATS

  • Procedura DELETE_SYSTEM_STATS




      1. Zbieranie statystyk systemowych


Oracle zaleca, aby dodatkowo oprócz statystyk dotyczących obiektów w bazie zbierać statystyki dotyczące obciążenia procesora oraz ilości operacji we/wy. Służy do tego procedura GATHER_SYSTEM_STATS. Ze względu na zróżnicowanie obciążenia serwera bazodanowego o różnych porach dnia zalecane jest zebranie statystyk dla poszczególnych okresów i importowanie ich w zależności od obciążenia. Takie statystyki mogą pomóc optymalizatorowi kosztowemu podjąć optymalną decyzję w zależności od obciążenia systemu.

Aby mieć możliwość przechowywania zebranych statystyk w pomocniczych tabelach, aby później w odpowiednich okresach importować je do bazy należy posłużyć się procedurą CREATE_STAT_TABLE.



  • DBMS_STATS.CREATE_STAT_TABLE(nazwa_schematu ,nazwa_tabeli, przestrzeń);

Kolejnym krokiem jest zebranie statystyk systemowych przy różnym obciążeniu i zapisanie ich do uprzednio utworzonej tabeli. Zebranie statystyk systemowych bezpośrednio do słownika danych można wykonać uruchamiając procedurę GATHER_SYSTEM_STATS tylko z parametrem interval.

  • DBMS_STATS.GATHER_SYSTEM_STATS(interval=>’czas w minutach zbierania statystyk’, stattab=>’nazwa tabeli gdzie statystyki zostaną umieszczone’, statid=>’ Identyfikator np. statystyki z godzin 8:00-16:00’);

Po zebraniu statystyk dla różnych obciążeń bazy związanych z godzinami pracy można importować statystyki systemowe do, aby jak najdokładniej oddawały aktualne obciążenie serwera. Można też do tego wykorzystać pakiet dbms_job aby całkowicie zautomatyzować tą czynność.

  • DBMS_STATS.IMPORT_SYSTEM_STATS(‘nazwa tabeli ze statystykami’, ’identyfikator’);

  • DBMS_STATS.DELETE_SYSTEM_STATS – kasuje zaimportowane statystyki

Sprawdzenie statystyk można wykonać przy pomocy procedury GET_SYSTEM_STATS

  • DBMS_STATS.GET_SYSTEM_STATS(status=>zmienna1,pstart=>zmienna2,pstop=>zmienna3, pname=>'mreadtim',pvalue=>zmienna4);

Gdzie pname może przybierać wartości:

Mreadmin – średni czas w ms odczytu pojedynczego bloku

Sreadmin – średni czas odczytów wieloblokowych

Cpuspeed – ilość cykli procesora w milionach na sekundę

Typy zmiennych: zmienna1 VARCHAR2, zmienna2 i 3 DATE,zmienna4 NUMBER


Tryby pracy optymalizatora


  • RULE

  • CHOOSE

  • FIRST_ROWS

  • FIRST_ROWS_n

  • ALL_ROWS




    1. Wybór optymalizatora i celu optymalizacji


Oracle daje możliwość wyboru optymalizatora stosowanego do optymalizacji poleceń SQL: regułowy lub kosztowy. Dodatkowo dla optymalizatora kosztowego można określić cel optymalizacji: czas odpowiedzi lub przepustowość systemu (optymalizacja czasu wykonania). Optymalizacja czasu odpowiedzi jest przydatna np. w aplikacjach interakcyjnych, gdzie najważniejsze jest zminimalizowanie czasu oczekiwania użytkownika na pojawienie się pierwszych wyników. Optymalizacja przepustowości jest zazwyczaj stosowana dla aplikacji wsadowych, np. raporty, gdzie dla użytkownika najważniejszy jest czas wygenerowania całego raportu.

Oracle umożliwia wybór optymalizatora i celu optymalizacji na trzech poziomach:



  • instancji

  • sesji użytkownika

  • pojedynczego polecenia SQL

Wybór optymalizatora na poziomie instancji dokonuje się za pomocą parametru OPTIMIZER_MODE zapisanego w pliku INIT.ora Parametr ten może przyjąć następujące wartości:

  • RULE - wybór optymalizacji regułowej

  • CHOOSE - jeśli istnieją odpowiednie statystyki dla obiektu, to zastosowanie wartości CHOOSE spowoduje stosowanie optymalizatora kosztowego, w przeciwnym przypadku polecenia SQL będą optymalizowane przez optymalizator regułowy

  • FIRST_ROWS - wybór optymalizacji kosztowej i określenie minimalizacji czasu odpowiedzi

  • FIRST_ROWS_n –wybór optymalizacji kosztowej i określenie minimalizacji czasu odpowiedzi odpowiednio dla wartości n reprezentującej ilośc wierszy 1,10,100,1000

  • ALL_ROWS - wybór optymalizacji kosztowej i określenie minimalizacji czasu wykonania

Domyślnie wartością parametru OPTIMIZER_MODE jest CHOOSE. Wybór optymalizatora i celu optymalizacji może być zmieniony przez ustawienie parametrów na poziomie sesji użytkownika lub polecenia SQL.


Wybór trybu pracy optymalizatora na poziome sesji

ALTER SESSION SET OPTIMIZER_MODE = wartość;

Dopuszczalne wartości:


  • RULE

  • CHOOSE

  • FIRST_ROWS

  • FIRST_ROWS_n

  • ALL_ROWS

Do wyboru optymalizatora i celu optymalizacji na poziomie sesji użytkownika służy polecenie:

ALTER SESSION SET OPTIMIZER_MODE = wartość;

Wyboru optymalizatora i celu optymalizacji na poziomie polecenia SQL dokonuje się za pomocą wskazówek (wskazówki dla optymalizatora zostaną omówione dokładnie w dalszej części rozdziału).




Generowanie planu zapytań

  • Polecenie EXPLAIN PLAN

  • Dyrektywa AUTOTRACE

  • SQL TRACE




    1. Plan zapytań


Projektant aplikacji i administrator systemu Oracle mają do dyspozycji trzy narzędzia umożliwiające poznanie planu polecenia SQL wygenerowanego przez optymalizator. Podstawowym narzędziem do wyjaśnienia planów jest polecenie SQL explain plan. Pozostałe narzędzia to: dyrektywa AUTOTRACE programu SQL Plus oraz program TKPROF. Polecenie explain plan oraz TKPROF nie wykonują instrukcji SQL tylko generują sam plan zapytania, natomiast ustawienie dyrektywy AUTOTRACE powoduje wykonanie analizowanych poleceń.


Składnia polecenia EXPLAIN PLAN

EXPLAIN PLAN [SET STATEMENT_ID = 'oznaczenie']

[INTO TABLE tabela]

FOR


wyjaśniane_polecenie;




      1. Polecenie EXPLAIN PLAN


Domyślnie wynik polecenia jest zapisywany do tabeli PLAN_TABLE. Jeśli tabela nie została jeszcze utworzona to można ją wygenerować skryptem

UTLXPLAN.sql,

który powinien znajdować się na ścieżce

...\RDBMS\ADMIN\Utlxplan.sql

Polecenie ma następującą składnię:

EXPLAIN PLAN [SET STATEMENT_ID = 'oznaczenie']

[INTO TABLE tabela]

FOR


wyjaśniane_polecenie

;

Klauzula SET STATEMENT_ID służy do jednoznacznego identyfikowania wygenerowanego planu. Klauzula INTO TABLE służy do wskazania tabeli, do której zostanie zapisany wynik wyjaśnienia. Domyślnie wynik zapisywany jest do tabeli PLAN_TABLE. Jeśli wyniki są zapisywane do innej tabeli niż PLAN_TABLE to tabela ta musi posiadać taką samą strukturę jak PLAN_TABLE.



Przykładowe polecenie:

EXPLAIN PLAN SET STATEMENT_ID = 'PRAC100'

FOR SELECT NAZWISKO FROM PRACOWNICY

WHERE ID_PRAC = 100;

wygeneruje wynik do tabeli PLAN_TABLE. Każdy rekord w tabeli opisuje pojedynczą operację wchodzącą w skład planu wykonania wyjaśnianego zapytania.



Odczytywanie wyników polecenia EXPLAIN PLAN

SELECT


OPERATION, OPTIONS, OBJECT_NAME, ID, COST, PARENT_ID

FROM PLAN_TABLE

WHERE STATEMENT_ID = oznaczenie

ORDER BY ID;


lub

SELECT


LPAD(' ', 2* (LEVEL-1)) || OPERATION || ' ' || OPTIONS|| ' ' || OBJECT_NAME

|| ' ' || DECODE (ID,0, 'COST = '||POSITION ) "QUERY_PLAN"

FROM PLAN_TABLE

START WITH ID = 0 AND STATEMENT_ID = oznaczenie

CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = oznaczenie;
lub
Skrypty UTLXPLS dla zapytań szeregowych oraz UTLXPLP dla zapytań wykonanych w trybie równoległym

Opis najważniejszych kolumn w tabeli PLAN_TABLE:



Kolumna

Opis

STATEMENT_ID

Identyfikator wyjaśnianego polecenia

TIMESTAMP

Data i czas wykonania polecenia explain plan

REMARKS

Komentarz użytkownika, który może zostać zapisany w tabeli poleceniem update

OPERATION

Nazwa operacji wykonanej w bieżącym kroku planu

OPTIONS

Opcja wykonania operacji podanej w kolumnie operation

OBJECT_OWNER

Nazwa właściciela obiektu, na którym wykonano bieżącą operację wyjaśnianego polecenia

OBJECT_NAME

Nazwa obiektu, na którym wykonano bieżącą operację wyjaśnianego polecenia

OBJECT_TYPE

Typ obiektu na którym wykonano bieżącą operację wyjaśnianego polecenia

OPTIMIZER

Tryb pracy optymalizatora

ID

Identyfikator bieżącego kroku planu zapytania

PARENT_ID

Identyfikator kroku planu wykonania, który korzysta z wyników realizacji bieżącego kroku

POSITION

Numer porządkowy bieżącego kroku, dla kroków, które mają tę samą wartość kolumny parent_id

COST

Przybliżony koszt realizacji bieżącego kroku wyliczony przez optymalizator kosztowy, dla optymalizatora regułowego wartość kolumny jest nieokreślona NULL

CARDINALITY

Przybliżona liczba rekordów przetwarzana w ramach bieżącego kroku

BYTES

Przybliżona wielkość danych ( w bajtach ) przetwarzana w ramach bieżącego kroku.

Oracle dostarcza dwa gotowe skrypty formatujące wynik polecenia explain plan. UTLXPLS oraz UTLXPLP znajdują się w katalogu ...\RDBMS\ADMIN\. Skrypt UTLXPLP jest dedykowany do zapytań wykonywanych w trybie równoległym a UTLXPLS dla pozostałych.



Dyrektywa AUTOTRACE

set autotrace {on | traceonly }





      1. Dyrektywa AutoTrace


Dyrektywa autotrace programu SQL Plus służy do wyświetlenia informacji dotyczących wykonywanego polecenia SQL (np. planu wykonania, ilości odczytanych bloków).

W celu skorzystania z dyrektywy autotrace użytkownik powinien mieć dostęp do tabeli PLAN_TABLE oraz posiadać rolę PLUSTRACE którą można wygenerować ze skryptu PLUSTRCE.sql, który znajduje się w katalohu %ORACLE_HOME%\SQLPLUS\ADMIN. Rolę należy wygenerować jako użytkownik SYS.

Składnia dyrektywy jest następująca:

set autotrace {on | traceonly }

Użycie słowa kluczowego ON powoduje, że w pierwszej kolejności zostaje wyświetlony wynik polecenia SQL, a następnie plan tego polecenia. Natomiast słowo kluczowe TRACEONLY powoduje, że zostaje pominięte wyświetlanie wyników.

Warunkiem wyświetlenia kosztu jest wygenerowanie statystyk dla danych.




Rodzaje Złączeń TABEL

  • Nested Loops

  • Merge Join

  • Hash Join




    1. Złączenia



      1. Łączenie tabel


Oracle używa trzech podstawowych metod łączenia tabel:

  • Nested loops – w tej metodzie przeszukiwana jest cała tabela wewnętrzna dla każdego wiersza z tabeli zewnętrznej

  • Merge join – metoda ta wymaga posortowania obu tabel (lub części tabel) wg kolumny występującej w złączeniu. Następnie dwa posortowane zbiory są sekwencyjnie łączone.

  • Hash join – w tej metodzie łączenia Oracle dla mniejszej z tablic oblicza i buduje tablice kluczy wyliczonych funkcją haszującą na podstawie wartości kolumny występującej w złączeniu. Następnie zachowuje się jak w metodzie nested loops traktując tablicę z kluczami jako wewnętrzną i wyszukując odpowiednie rekordy poprzez wartość klucza przeliczoną przez funkcję haszującą.

Metoda nested loops jest używana przez Oracle w przypadku celu optymalizacji FIRST_ROWS, wymaga jednak wsparcia ze strony indeksu na kolumnie złączenia po stronie tabeli wewnętrznej (tylko w tabeli wewnętrznej). Merge join jest wydajniejsza w przypadku celu ustawionego na ALL_ROWS.

Korzystając z podpowiedzi ORDERED można decydować, w jakiej kolejności powinny być łączone tabele. Generalna zasada jest taka, że najpierw powinny być przetwarzane tablice najmniejsze lub posiadające najbardziej restryktywną klauzulę WHERE.




Porównanie Metod łączenia tabel

  • Nested Loops jest lepsza dla planu FIRST_ROWS

  • Merge/Hash Join bardziej optymalne dla ALL_ROWS




      1. Wybór najlepszej metody łączenia


Porównanie metod merge/hash z nested loops:

  • Nested loops jest lepsza dla optymalizacji FIRST_ROWS, merge/hash dla ALL_ROWS

  • Merge/hash zwiększają swoją efektywność wraz ze wzrostem ilości wierszy w mniejszej z tablic

  • Nested loops wymaga indeksu na kolumnie złączenia w tablicy wewnętrznej

  • Merge/hash nadaja się dobrze do operacji zrównoleglenia

  • Hash join jest bardziej optymalny od merge join

Optymalizacja złączeń

Optymalizacja złączeń polega na odpowiednim wybraniu:



  • Kolejności łączonych tabel

  • Metody łączenia

  • Dla metody hash join należy odpowiednio dobrać wartości parametrów:

    • HASH_AREA_SIZE maksymalny rozmiar pamięci wykorzystywany przy złączeniu

    • HASH_MULTIBLOCK_IO_COUNT liczba bloków odczytywanych z dysku w jednej operacji I/O podczas wykonywania złączenia (dla tabeli na której są budowane wartości haszujące)



Złączenia tabel w zapytaNiach

  • Złączenia wewnętrzne (inner join)

  • Równościowe

  • Nierównościowe

  • Złączenia zewnętrzne (outer join)


    1. Złączenia tabel w zapytaniach



      1. Złączenia wewnętrzne (inner join)


Są to najczęściej spotykane złączenia. Polegają na połączeniu wierszy z jednej tablicy z wierszami z innej tablicy wg wartości pewnego wspólnego klucza. Wiersze z obu tabel, które nie mają swojego odpowiednika w łączonych tabelach nie występują w zbiorze wynikowym. Złączenia wewnętrzne dzielą się na złączenia równościowe i nie równościowe:

Równościowe (equijoin)

Kolumny klucza w obu tabelach połączone są operatorem „=”.

SELECT dname, ename

FROM emp, dept

WHERE emp.deptno = dept.deptno;

Nie równościowe (theta join)

Kolumny klucza w obu tabelach połączone są operatorem jednym z operatorów „>”, „<”, „>=”, ”<=”, „<>”, „BETWEEN”.

SELECT ename, grade

FROM emp, salgrade

WHERE sal BETWEEN losal AND hisal;

      1. Złączenia zewnętrzne (outer join)


Złączenia zewnętrzne pozwalają zawrzeć w zbiorze wynikowym także wiersze, które nie znajdują swojego odpowiednika w dołączanej tabeli. Brakujący wiersz z dołączanej tabeli jest reprezentowany przez szereg wartości NULL.
SELECT dname, ename

FROM emp, dept

WHERE emp.deptno(+) = dept.deptno;
W Oracle (+) jest tzw. operatorem złączenia zewnętrznego.


Złączenia tabel w zapytaNiach


  • Antyzłączenia (anti join)

  • Somozłączenia (self join)


      1. Antyzłączenia (anti join)


Antyzłączenia są wykorzystywane w przypadku, gdy interesują nas te wiersze z pewnej tabeli, które nie mają swojego odpowiednika w tabeli dołączanej. Zapytania te są zazwyczaj implementowane przy użyciu operatorów „NOT IN” i „NOT EXISTS”.

Informacje o departamentach nie zatrudniających analityków:

SELECT *

FROM dept

WHERE deptno NOT IN (SELECT deptno

FROM emp


WHERE job = ‘ANALYST’

);

      1. Samozłączenia (self join)


Samozłączenie polega na wielokrotnym odwołaniu się do tej samej tabeli w jednym zapytaniu.

Nazwisko pracownika i nazwisko jego przełożonego:

SELECT p.ename, d.ename

FROM emp p, emp d

WHERE p.mgr = d.empno;


Optymalizacja Złączeń


  • Półzłączenia

  • Specyficzna odmiana złączenia merge/hash join

  • Wymuszana przez wskazówki MERGE_SJ lub HASH_SJ

  • Parametr instancji ALWAYS_SEMI_JOIN





    1. Optymalizacja złączeń



      1. Półzłączenia


Półzłączenia pozwalają Oraclowi wykonywać efektywnie podzapytania skorelowane zawierające operator EXISTS pomimo, iż nie istnieje indeks, który pomagałby wykonywać podzapytanie.

Półzłączenie to specyficzna odmiana złączenia merge lub hash. Różnica polega na tym, że złączenie zwraca tylko jeden wiersz z tablicy nadrzędnej, niezależnie od tego, ile istnieje odpowiadających wierszy w tablicy podrzędnej.

Półzłączenie nigdy nie jest proponowane automatycznie przez optymalizator. Możemy wymóc konwersję podzapytania na półzłączenie poprzez podpowiedzi MERGE_SJ lub HASH_SJ umieszczone w podzapytaniu.

Można także ustawić parametr instancji ALWAYS_SEMI_JOIN na wartości MERGE lub HASH, ale w takim wypadku półzłączenie będzie wymuszane nawet, gdy będzie istniał odpowiedni indeks saportujący podzapytanie.




Optymalizacja Złączeń

  • Antyzłączenia

  • NOT IN

  • NOT EXISTS

  • MINUS




      1. Antyzłączenia


Antyzłączenia to takie zapytania, które wybierają tą część wierszy z jednego zbioru wynikowego, która nie ma odpowiedników w drugim zbiorze wynikowym.

NOT IN

Jest to najbardziej naturalna metoda przedstawienia antyzłączeniaTa forma zapytania jest mało efektywna. W przypadku optymalizacji regułowej jest zupełnie niedopuszczalna, gdyż nigdy nie jest stosowany żaden indeks, a zatem zapytanie jest wykonywane jak podzapytanie skorelowane z wykorzystaniem pełnych odczytów obu tablic.

W przypadku optymalizatora kosztowego, indeks na podzapytaniu jest już wykorzystywany.

NOT EXISTS

W przypadku użycia operatora EXISTS optymalizator regułowy używa odpowiedniego indeksu do wykonywania podzapytania.

W przypadku stosowania optymalizatora kosztowego plan wykonania jest identyczny jak przy operatorze NOT IN.

MINUS

Operator MINUS nie zawsze może zostać wykorzystany w zastępstwie NOT IN i NOT EXISTS, ponieważ oba porównywane ze sobą zbiory wierszy muszą mieć tą samą ilość kolumn tego samego typu.

W przypadku wykonywania antyzłączenia spowodowanego operatorem MINUS, nie są wykonywane podzapytania. Odczytywane są oba zbiory wynikowe, następnie są sortowane i posortowane odpowiednio łączone.

Operator MINUS jest zdecydowanie efektywniejszy od NOT IN i NOT EXISTS w przypadku, gdy nadrzędny zbiór wierszy jest stosunkowo duży, natomiast podrzędny zbiór wierszy jest stosunkowo mały.




Optymalizacja Złączeń

  • Antyzłączenia merge i hash

  • Tylko w przypadku optymalizatora kosztowego

  • Zapytania zewnętrzne nie mogą używać operatora OR

  • Wskazówki MERGE_AJ lub HASH_AJ

  • Parametr instancji ALWAYS_ANTI_JOIN




      1. Antyzłączenie merge i antyzłączenie hash


Ponieważ antyzłączeniawykonywane za pomocą podzapytań są bardzo mało efektywne, Oracle wprowadził możliwość konwertowania antyzłaczeń w ten sposób, aby były one wykonywane tak jak złączenia typu merge i hash.

Aby wymóc taką konwersję na optymalizatorze, należy użyć jednej z podpowiedzi: HASH_AJ, MERGE_AJ. W takim planie zapytania Oracle przeczyta tablice podrzędną, następnie przeczyta tablicę nadrzędna, posortuje je i dokona odpowiedniego złączenia.

Po takiej zmianie metody wykonania antyzłączeniawydajność znacznie wzrośnie.

Aby jednak można ją było zastosować, muszą być spełnione następujące warunki:



  • Optymalizator kosztowy musi być dostępny.




  • Kolumny z podzapytania nie mogą przyjmować wartości NULL, tzn. musza być zdefiniowane jako NOT NULL, lub musi być dodany warunek do klauzuli WHERE IS NOT NUL.




  • Podzapytanie nie może być skorelowane




  • Zapytanie zewnętrzne nie może zawierać operatora OR




  • Musi zostać umieszczona podpowiedź MERGE_AJ lub HASH_AJ w podzapytaniu lub musi zostać ustawiony parametr instancji ALWAYS_ANTI_JOIN na wartość MERGE lub HASH.

Podsumowanie

Jeżeli jest dostępny optymalizator kosztowy, należy korzystać z podpowiedzi MERGE_AJ lub HASH_AJ. Jeżeli trzeba korzystać z optymalizatora regułowego (np. wersja Oracle lub brak statystyk) należy skorzystać z operatora MINUS, jeżeli jednak jest to niemożliwe, należy używać NOT EXISTS, w żadnym wypadku nie NOT IN.



Typy wskazówek dla optymalizatora




    1. Wskazówki dla optymalizatora w poleceniach SQL


Projektant na podstawie swojej wiedzy dotyczącej charakterystyki aplikacji może wspomóc optymalizator w poszukiwaniu optymalnego planu. Można to zrealizować przy pomocy wskazówek - HINTS. Użycie wskazówek pozwala w znacznym stopniu obniżyć koszt samej optymalizacji.

Wskazówki umożliwiają określenie następujących elementów pracy optymalizatora:



  • rodzaj optymalizatora

  • cel optymalizacji

  • sposób dostępu do danych

  • kolejność łączonych tabel dla operacji połączenia

  • sposób realizacji połączenia

Wskazówki umieszcza się w komentarzu, bezpośrednio po słowach kluczowych SELECT, DELETE lub UPDATE. Pierwszym znakiem wskazówki jest + (plus). Między znakami komentarza i znakiem plus nie są dopuszczalne jakiekolwiek odstępy: spacje, tabulatory, itp. Wskazówki w poleceniach złożonych, podzapytaniach lub zapytaniach z operatorami zbiorowymi - UNION, MINUS, INTERSECT - dotyczą tylko tej części polecenia, w której zostały umieszczone.

Wskazówki są akceptowane przez optymalizator tylko wtedy, kiedy istnieje jakikolwiek plan, który wykorzystuje sposób dostępu do danych określony we wskazówce. Optymalizator nie zaakceptuje wskazówki wymuszającej użycie indeksu jeśli odpowiedni indeks nie będzie założony. Jeżeli zostanie popełniony błąd w składni wskazówki, to polecenie zostanie wykonane z pominięciem wskazania dla optymalizatora. Oracle nie zgłosi w takim przypadku żadnego komunikatu o błędzie.




Wybór rodzaju optymalizatora

  • /*+CHOOSE */ - optymalizator kosztowy

  • /*+RULE */ - optymalizator regułowy




      1. Wybór rodzaju optymalizatora


Do wyboru optymalizatora na poziomie polecenia służą wskazówki RULE i CHOOSE. Ich znaczenie jest takie samo, jak odpowiednich wartości parametru konfiguracyjnego OPTIMIZER_MODE w INIT.ORA

Wskazówka



/*+CHOOSE */

wymusza użycie optymalizatora kosztowego, np.

DELETE /*+CHOOSE*/ ETATY

WHERE NAZWA='ASYSTENT';


Przykład użycia optymaliztora regułowego:
DELETE /*+RULE */ ETATY

WHERE NAZWA='ASYSTENT';



Wybór celu optymalizacji

  • /*+FIRST_ROWS */ - minimalizacja czasu odpowiedzi

  • /*+FIRST_ROWS(n) */ - minimalizacja czasu odpowiedzi dla n wierszy

  • /*+ALL_ROWS */ - minimalizacja czasu wykonania




      1. Wybór celu optymalizacji


Wybór celu optymalizacji realizuje się za pomocą następujących wskazówek:

/*+FIRST_ROWS */ - minimalizacja czasu odpowiedzi

/*+FIRST_ROWS(n) */ - minimalizacja czasu odpowiedzi dla n wierszy, gdzie n może być dowolną liczbą całkowitą

/*+ALL_ROWS */ - minimalizacja czasu wykonania

Poniższy przykład wymusza optymalizację polecenia pod kątem minimalizacji czasu wykonania:



SELECT /*+ALL_ROWS */ NAZWISKO, ETAT

FROM PRACOWNICY

WHERE ID_ZESP=10;

Wskazówki FIRST_ROWS i ALL_ROWS są ignorowane w poleceniach INSERT, UPDATE, DELETE oraz w poleceniach SELECT zawierających:



  • operatory zbiorowe UNION, MINUS, INTERSECT

  • klauzulę GROUP BY

  • klauzulę FOR UPDATE

  • funkcje grupowe np. AVG, MIN, MAX

  • operator DISTINCT


Dostęp do danych

/*+ FULL */ - dostęp poprzez pełen przegląd

/*+ CLUSTER */ – dostęp po klastrze indeksowym

/*+ HASH */ – dostęp po klastrze haszowym

/*+ INDEX */ – odwołanie do danych po indeksie

/*+ INDEX_ASC */ – przeszukiwanie po indeksie w porządku rosnącym

/*+ INDEX_DESC */ – przeszukiwanie po indeksie w porządku malejącym

/*+ INDEX_FFS */ – przeszukiwanie tylko indeksu, bez zawartości tabeli






    1. Wybór sposobu dostępu do danych




  • /*+FULL */ - wskazówka wymusza sekwencyjny odczyt wszystkich rekordów tabeli np.

SELECT /*+FULL (PRACOWNICY) */ NAZWISKO

FROM PRACOWNICY

WHERE ID_PRAC=100;

  • /*+CLUSTER */ - dostęp do danych w klastrze indeksowym, np.

SELECT /*+CLUSTER (POZYCJE_ZAMOWIEN)*/ ZAMOWIENIA

FROM POZYCJE_ZAMOWIEN

WHERE ID_ZAMOWIENIA=123;

  • /*+HASH */ - dostęp do danych w klastrze haszowym - wymuszenie dostępu do danych za pomocą funkcji haszującej, np.

SELECT /*+HASH (PRACOWNICY) */ NAZWISKO

FROM PRACOWNICY

WHERE ID_ZESP=10;

  • /*+INDEX ( tabela [indeks, ... ] ) */ - dostęp do rekordów za pomocą indeksu. Jeżeli zostanie podany jeden indeks to zostanie on wykorzystany do wygenerowania planu, jeżeli zostanie podanych kilka indeksów to wykorzystane zostaną tylko te które zminimalizują koszt wykonania, jeżeli nie zostaną wskazane żadne indeksy, to ze wszystkich dostępnych zostaną wybrane te, które zminimalizują koszt realizacji, np.

SELECT /*+INDEX (PRACOWNICY PK_PRAC) */ NAZWISKO

FROM PRACOWNICY

WHERE ID_PRAC=100;

  • Wskazówki /*+INDEX_ASC*/ i /*+INDEX_DESC*/ określają kierunek przeszukiwania liści indeksu w poleceniach selekcjonujących określony zakres wartości. Wskazówka INDEX_ASC wymusza kierunek rosnący, natomiast INDEX_DESC kierunek malejący, np.

SELECT /*+ INDEX_ASC (PRACOWNICY PK_PRAC) */ NAZWISKO

FROM PRACOWNICY

WHERE ID_PRAC BETWEEN 100 AND 500;

  • Wskazówka /*+INDEX_FFS*/ umożliwia szybkie przeszukanie indeksu w sytuacji gdy wszystkie dane potrzebne do wykonania polecenia SQL zawarte są w indeksie - nie ma potrzeby dostępu do danych tabeli. Składnia wskazówki INDEX_FFS jest taka sama jak wskazówki INDEX, np.

SELECT /*+INDEX_FFS (PRACOWNICY PERSONALIA_IDX) */ NAZWISKO

FROM PRACOWNICY

WHERE ID_ZESP = 20;

Wybór kolejności łączenia tabel

/*+ ORDERED */ - określa, iż tabele będą łączone w porządku wymienionym w klauzuli FROM




      1. Kolejność łączonych tabel dla operacji złączenia




  • Kolejność łączonych tabel ma bardzo duży wpływ na efektywność wykonania całej operacji połączenia - wskazówka /*+ORDERED*/ umożliwia określenie kolejności łączenia tabel. Jeżeli zostanie użyta to tabele zostaną połączone wg kolejności, w jakiej zostały wyspecyfikowane w klauzuli FROM zapytania, np.

SELECT /*+ORDERED*/ P.NAZWISKO, E.PLACA_MIN, Z.NAZWA

FROM ETATY E, PRACOWNICY P, ZESPOLY P

WHERE P.ETAT=E.ETAT AND P.ID_ZESP = Z.ID_ZESP;
Jeżeli programista wie jaka powinna być kolejność łączenia tabel w poleceniu SQL to powinien tą wiedzę wykorzystać. Optymalizator wybiera kolejność łączenia wykonując kolejne permutacje. Zakładając , iż w zapytaniu będzie łączonych pięć tablic, to optymalizator będzie musiał wykonać 120 permutacji (5!=120). Używając klauzuli ORDERED, można uniknąć nadmiernego wykorzystania procesora.



Zalecenia pod kątem poprawy wydajności poleceń SQL

  • Korzystanie z PL/SQL

  • Dobór struktur fizycznych

  • Wybór optymalizatora kosztowego

  • Stosowanie podpowiedzi dla optymalizatora

  • Stosowanie sekwencji

  • LONG, LONG RAW – przechowywanie w oddzielnych segmentach



    1. Strojenie poleceń SQL


Strojenie poleceń SQL jest ważnym elementem implementacji aplikacji, umożliwiającym osiągnięcie przez system satysfakcjonującej wydajności.

Zalecenia mające na celu poprawienie wydajności pod kątem poleceń SQL:



  • Korzystanie z PL/SQL, szczególnie ze składowanych procedur, zamiast sekwencji SQL - obniża się w ten sposób koszt komunikacji między aplikacją a bazą danych oraz liczbę wywołań poleceń SQL.

  • Staranne dobranie odpowiednich struktur fizycznych - zastosowanie w odpowiedni sposób indeksów i klastrów może wielokrotnie przyspieszyć wykonanie polecenia SQL.

  • Zastosowanie optymalizatora kosztowego - w oparciu o rozkłady danych system dobierze optymalny sposób realizacji polecenia.

  • Wykorzystanie wskazówek dla optymalizatora kosztowego - dysponując dodatkową wiedzą na temat rozkładu danych programista może wskazać efektywne sposoby wykonywania poleceń.

  • Korzystanie z sekwencji do tworzenia kluczy głównych i unikalnych - wykorzystanie tych obiektów jest optymalnym sposobem generowania unikalnych identyfikatorów.

  • Przechowywanie danych typu LONG i LONG RAW w oddzielnych tabelach - unika się w ten sposób niepożądanego wczytywania do buforów danych dużych obiektów (np. zdjęć) przy odczytywaniu danych tekstowych.


Zalecenia co Do zakładania indeksów

  • Zakładanie indeksów na kolumnach często przeszukiwanych

  • Indeksowanie kolumn wykorzystywanych w złączeniach

  • Tworzenie indeksów na kolumnach o dużej selektywności

  • Rekordy w poindeksowanych kolumnach powinny być modyfikowane jak najrzadziej

  • Wykrywanie nieużywanych indeksów



    1. Zalecenia dotyczące tworzenia indeksów




  • Indeksy zakładane są na kolumnach często wykorzystywanych w warunkach selekcji - opłacalność utrzymania indeksu zależy od częstotliwości jego używania, im częściej są wykonywane operacje selekcji na atrybutach, na których dany indeks jest założony, tym większy jest zysk z wykorzystania indeksu w porównaniu do kosztu modyfikowania indeksu.

  • Na atrybutach połączeniowych (kolumnach często wykorzystywanych w złączeniach) - indeksy na atrybutach połączeniowych tabel przyśpieszają operacje połączenia tabel metodą nested-loops .

  • Na atrybutach o dużej selektywności - wydajność indeksu w dużym stopniu zależy od jego selektywności, czyli stosunku liczby różnych wartości atrybutu do liczby rekordów w tabeli. Przykładowo, atrybut płeć jest mało selektywny, ponieważ istnieją tylko dwie wartości tego atrybutu, natomiast pesel jest bardzo selektywny, ponieważ jednej wartości tego atrybutu odpowiada jeden rekord tabeli.

  • Na atrybutach rzadko modyfikowanych - utrzymywanie indeksu wiąże się z potrzebą jego modyfikowania przy każdej zmianie wartości indeksowanych atrybutów oraz przy wstawianiu i usuwaniu rekordów w tabeli. Jeżeli liczba operacji modyfikujących jest wielokrotnie większa od liczby odczytów, może się okazać, że koszt utrzymywania indeksu przekracza korzyści płynące z jego wykorzystania.

  • Administrator ma możliwość sprawdzenia czy założony indeks jest wykorzystywany. Poleceniem ALTER INDEX nazwa MONITORING USAGE włączone zostaje sprawdzanie czy indeks jest wykorzystywany w zapytaniach. Informacje o tym można znaleźć w perspektywie V$OBJECT_USAGE, kolumna USED wskazuje YES gdy indeks jest wykorzystywany i NO w przeciwnym wypadku. Monitorowanie można wyłączyć klauzulą ALTER INDEX nazwa NOMONITORING USAGE




Zasady tworzenia klastrów indeksowych

  • Klastry tworzone są dla tabel często łączonych

  • Kluczem klastra nie powinny być kolumny, których zawartość jest często modyfikowana

  • Nie należy umieszczać w klastrze tabel, na których wykonywane są pełne przeglądy

  • Umieszczanie w klastrze tabel typu nadrzędna-podrzędna przyspiesza wykonywanie operacji na tych tabelach




    1. Zalecenia dotyczące tworzenia klastrów indeksowych


Klastry tworzone sa dla tabel często łączonych - bloki klastra zawierają rekordy pochodzące z wielu tabel. Rekordy te mają wspólną wartość klucza klastra. W celu połączenia tabel znajdujących się w tym samym klastrze, w oparciu o jego klucz, wystarczy przeczytać sekwencyjnie wszystkie bloki klastra a następnie dla wszystkich rekordów każdego z bloków dokonać operacji połączenia zgodnie z warunkiem połączeniowym. Jest to najmniej kosztowny sposób łączenia tabel.

Dla bardzo rzadko modyfikowanych kluczy klastra - zmiana wartości klucza klastra wymaga fizycznego przeniesienia rekordu z jednego bloku bazy danych do drugiego. Jest to operacja kosztowna i należy jej unikać.



Dla tabel, które nie są przeglądane liniowo - przy przeglądaniu liniowym jednej tabeli do buforów bazy danych wczytywane są bloki klastra zawierające również nadmiarowe dane z pozostałych tabel. W rezultacie, do odczytania tej samej informacji wymagana jest większa liczba odczytów z dysku.

Dla tabel będących w związku nadrzędna - podrzędna - umieszczenie tabel w klastrze przyspieszy operacje wymagające dostępu zarówno do tabeli nadrzędnej jak i podrzędnej.



©operacji.org 2017
wyślij wiadomość

    Strona główna