Optimalizace SQL dotazů

Bankovní institut vysoká škola Praha Katedra informatiky a kvantitativních metod

Optimalizace SQL dotazů Diplomová práce

Autor:

Bc. David Kakrda Informační technologie a management

Vedoucí práce:

Praha

Ing. Bc. Jiří Rezler

Červen, 2014

Prohlášení Prohlašuji, ţe jsem diplomovou práci zpracoval samostatně a v seznamu uvedl veškerou pouţitou literaturu. Svým podpisem stvrzuji, ţe odevzdaná elektronická podoba práce je identická s její tištěnou verzí, a jsem seznámen se skutečností, ţe se práce bude archivovat v knihovně BIVŠ a dále bude zpřístupněna třetím osobám prostřednictvím interní databáze elektronických vysokoškolských prací.

V Praze dne 30.6. 2014

David Kakrda

Poděkování Na tomto místě bych rád poděkoval vedoucímu mé diplomové práce panu Ing. Jiřímu Rezlerovi za odbornou pomoc a panu Ing. Zdeňku Šilarovi Ph.D. za poskytnutí zahraniční literatury. Dále bych chtěl poděkovat mé manţelce za podporu a trpělivost při mých náladách během psaní této práce.

Anotace

Tato práce se zabývá optimalizací SQL dotazů nad zvoleným databázovým systémem. Část práce je věnována popisu nástrojů, technik a doporučení jako jsou optimalizátor databáze Oracle, indexy, statistiky, hinty, přístupy k datům apod. Druhá část práce je pak věnována praktickým ukázkám výše zmíněných technik a odladění aplikace běţící nad komerčním databázovým systémem.

Klíčová slova informační technologie, databázový systém, SQL, Oracle, optimalizace, výkon, ladění

Annotation This thesis is concerned with optimizing SQL queries over the selected database system. Part of the work focuses on the description of the tools, techniques, and recommendations, such as Oracle Database optimizer, indexes, statistics, hints, data access, etc. The second part is devoted to practical examples of the above-mentioned techniques and debugging of applications running over commercial database system.

Keywords Information technologies, database system, SQL, Oracle, optimization, performance, tunnig

Obsah Úvod ........................................................................................................................................... 8 Zvolené metody zpracování...................................................................................................... 10 1

2

3

Optimalizace obecně......................................................................................................... 11 1.1

Kritéria ....................................................................................................................... 11

1.2

Moţnosti optimalizace ............................................................................................... 11

Ladění výkonu pro databázové administrátory ................................................................ 13 2.1

Databázový server...................................................................................................... 13

2.2

Databáze..................................................................................................................... 13

2.3

Instance ...................................................................................................................... 16

2.3.1

Systémová globální oblast .................................................................................. 17

2.3.2

Globální programová oblast ............................................................................... 19

2.3.3

Optimalizátor ...................................................................................................... 21

Design databáze ................................................................................................................ 25 3.1

Návrh databázového schéma...................................................................................... 25

3.1.1

Vertikální dělení a spojování .............................................................................. 25

3.1.2

Denormalizace .................................................................................................... 27

3.1.3

Datové typy ........................................................................................................ 28

3.2

Indexy ........................................................................................................................ 28

3.2.1

B*strom index .................................................................................................... 29

3.2.2

Bitmapový index ................................................................................................ 31

3.2.3

Funkční index ..................................................................................................... 34

3.3

Tabulky ...................................................................................................................... 34

3.3.1

Indexově orientovaná tabulka ............................................................................. 34

3.3.2

Seskupené tabulky .............................................................................................. 36

3.4

Materializované pohledy............................................................................................ 37

3.5

Horizontální dělení .................................................................................................... 37

3.6

3.6.1

Komprese indexu ................................................................................................ 41

3.6.2

Komprese tabulky ............................................................................................... 42

3.7 4

Komprese ................................................................................................................... 41

Statistiky .................................................................................................................... 43

SQL dotazy ....................................................................................................................... 45 4.1

Zpracování příkazů .................................................................................................... 45

4.1.1

Analýza ............................................................................................................... 46

4.1.2

Optimalizace ....................................................................................................... 47

4.1.3

Generování řádkového zdroje............................................................................. 47

4.1.4

Provedení příkazu ............................................................................................... 48

4.2

Přístupové cesty ......................................................................................................... 49

4.3

Principy spojení ......................................................................................................... 52

4.4

Exekuční plán ............................................................................................................ 54

4.4.1

Nástroje pro generování a zobrazení plánu ........................................................ 55

4.4.2

Čtení exekučního plánu ...................................................................................... 58

4.5

Hinty .......................................................................................................................... 59

4.6

SQL Plan Management .............................................................................................. 61

4.6.1

Úloţné struktury SQL plan managementu ......................................................... 61

4.6.2

Činnosti SQL plan managementu ....................................................................... 62

4.7 5

Nástroje pro monitorování a ladění SQL dotazů ....................................................... 64

Optimalizace v praxi......................................................................................................... 66 5.1

Úloha č. 1 ................................................................................................................... 66

5.2

Úloha č. 2 ................................................................................................................... 69

5.3

Úloha č. 3 ................................................................................................................... 73

Závěr ......................................................................................................................................... 76 Seznam pramenů a literatury .................................................................................................... 77 I.

Monografie .................................................................................................................... 77

II.

Elektronické zdroje .................................................................................................... 77

Seznam obrázků........................................................................................................................ 78 Seznam tabulek ......................................................................................................................... 79 Seznam zkratek ......................................................................................................................... 80 Seznam příloh na CD................................................................................................................ 81

Úvod Relační databázové systémy jsou v dnešní době nejrozšířenějším způsobem pro efektivní správu velkého objemu dat. Setkáváme se s nimi, ať uţ v souvislosti s pouţitím v informačních systémech, kde se jedná povětšinou o tzv. OLTP1 systémy nebo v podobě datových skladů zaloţených na principu OLAP2. Z pohledu databázových vývojářů, jimiţ jsou různorodí programátoři a analytici, by měl být kladen důraz na výkon databázové aplikace. Ten bývá často kritickým místem těchto aplikací. Problémy s výkonem se nemusí objevit ihned při vývoji a testování dané aplikace, ale později, kdy mnoţství dat v databázi postupem času neustále narůstá a také se zvyšuje počet uţivatelů, kteří spouštějí své dotazy. Po zjištění takovéhoto výkonového problému je nutno následně přistoupit k optimalizaci, která si však můţe vyţádat nemalé finanční prostředky. Nedostatečně odladěné či dokonce špatně napsané aplikace se mohou negativně podepsat na běhu ostatních aplikací či celého systému. Důsledkem bývá znatelné zpomalení a v nejhorším případě i pád systému. Moţností jak docílit lepšího výkonu je mnoho. Neexistuje však ţádný univerzální postup, který by dokázal optimalizovat libovolný dotaz. Jedná se pouze o sadu nástrojů, technik a doporučení závisejících na konkrétním databázovém systému a jeho prostředí. Ne všichni uţivatelé mají stejné moţnosti, např. většina databázových vývojářů nemá přístup k nastavení vlastností instance databázového systému, které přímo i nepřímo ovlivňují zpracování dotazů. Na druhou stranu databázový administrátor bude málokdy zasahovat do samotného kódu aplikace, aby ji odladil. Z vlastní zkušenosti vím, ţe mnoho začínajících vývojářů nemá o moţnostech optimalizace hlubší povědomí a dopouštějí se tak často zbytečných chyb, čímţ se jejich práce stává sloţitější a časově náročnější. Ne kaţdý vývojář absolvoval vzdělání v oboru informačních technologií ať uţ středoškolské či vysokoškolské. Také mnohým školním předmětům, týkajících se základů databázových systémů, nezbývá pro problematiku optimalizace SQL dotazů prostor. V neposlední řadě je také důleţitá praxe. Studenti prezenčních studií anebo

1

On-line Transaction Processing. Systém určený pro zpracování velkého počtu transakcí. On-line Analytical Processing. Systém uzpůsobený pro provádění analýz nad daty pocházejících většinou z OLTP systémů. 2

8

studenti, kteří nepracují v oboru, mají většinou jen omezené moţnosti vyzkoušet si aplikaci doporučených technik a postupů. Cílem této diplomové práce je popsat moţnosti v přístupu k optimalizaci SQL dotazů na zvoleném databázovém systému. Tematicky je zaměřena primárně na vhodné postupy a dostupné nástroje pro databázové vývojáře neţ na ladění výkonu obecně. Práce je doplněna o praktické ukázky implementace konkrétních opatření a nalezení a následné odladění problematických dotazů či aplikací běţících nad reálnou komerční databází.

9

Zvolené metody zpracování Popis a praktické ukázky dané problematiky jsem se rozhodl aplikovat na relační databázový systém Oracle od stejnojmenné společnosti. Tento databázový systém je jedním z nejrozšířenějších3 v oblasti relačních databází a mám s ním také několikaletou zkušenost z pozice obchodního analytika. Při zpracování této práce jsem čerpal zejména z dostupné literatury v českém a anglickém jazyce, které je o tomto systému celá řada. Co se týče zdrojů v elektronické podobě, povaţuji za stěţejní dokumentaci k databázi Oracle, dostupnou z webu této společnosti. Tato práce pokrývá primárně verzi systému Oracle 12c Release 1, ale většinu přístupů a technik zde popsaných je moţno aplikovat i na starší verze anebo jiné databázové systémy. Na moţné rozdíly mezi jednotlivými verzemi Oracle jsem se snaţil v textu upozornit. Praktická část práce je provedena na firemním datovém skladu mezinárodní společnosti. Laskavý čtenář promine, ale jméno společnosti není v této práci uvedeno. Při implementaci konkrétních technik jsem vycházel z předešle nastudovaných poznatků a v neposlední řadě také z vlastních zkušeností.

3

DB-Engines Ranking. DB-Engines [online]. © 2012-2014 [cit. 2013-12-21]. Dostupné z: http://dbengines.com/en/ranking. 10

1

Optimalizace obecně

1.1 Kritéria Neţ začneme se samotnou optimalizací databázových aplikací a SQL dotazů, je třeba stanovit si alespoň rámcové metriky, díky kterým si můţeme ověřit, zda naše snaţení o zlepšení výkonu dosáhlo očekávaných výsledků. Co se týče výkonu v souvislosti s databázovým systémem (a nejen jím), sledujeme především dvě základní kritéria: 4 Časová odezva, za kterou nám daný dotaz vrátí poţadovaná data od odeslání poţadavku. Propustnost, coţ znamená objem práce vykonané za určitý čas.5 Od propustnosti se také odvíjí mnoţství systémových prostředků (CPU, operační paměť apod.), které jsou zapotřebí k provedení určité operace (v našem případě SQL příkazu nebo sady příkazů). U časové odezvy se snaţíme o její minimalizaci, tedy co největší zkrácení doby, po kterou musíme čekat, neţ nám dotaz vrátí výsledek. Na druhou stanu zvýšením propustnosti sniţujeme nároky na mnoţství prostředků, jeţ dotaz vyţaduje ke svému vykonání.

1.2 Možnosti optimalizace K optimalizaci většinou přistupujeme aţ po zjištění nějakého výkonnostního problému, např. dotaz vrací výsledky za neúnosně dlouhou dobu, nedobíhající uloţená procedura, aplikace je velmi náročná na systémové prostředky apod. Poté co identifikujeme problematický dotaz, hledáme nejvhodnější metody optimalizace, které by nám pomohly dosáhnout očekávaných výsledků. Naproti tomu proaktivní přístup se zaměřuje na testování a ladění výkonu jiţ při samotném návrhu aplikace či tvorbě dotazu.

4

Oracle Database SQL Tuning Guide: Introduction to SQL Tuning. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2013-12-21]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_intro.htm#TGSQL112. 5 Throughput. Oracle FAQ's [online]. 2007. 30.12. 2007 [cit. 2013-12-21]. Dostupné z: http://www.orafaq.com/wiki/Throughput. 11

Zlepšení výkonu můţeme docílit mnoha způsoby, přičemţ kaţdý pracuje na jiné vrstvě databáze: Databázový server – jedná se o konfiguraci hardwarových komponent, jako jsou: CPU, operační paměť, V/V subsystém (většinou diskové pole), počítačová síť. Databáze – správa logického a fyzického úloţiště dat. Instance – nastavení paměťových struktur a optimalizátoru Oracle. Logický a fyzický design databáze – optimální návrh schématu a práce s databázovými objekty: tabulky, indexy, materializované pohledy. Příkazy v jazyce SQL – pouţití Hintů, SQL plan management, přepsání dotazů atd. S kaţdou skupinou vrstev bude pravděpodobně pracovat jiná skupina uţivatelů. První tři jsou určeny zejména pro administrátory databázového systému respektive systémové architekty. S druhou skupinou pak pracují převáţně databázoví vývojáři a datoví modeláři.

12

2

Ladění výkonu pro databázové administrátory

V této kapitole si stručně přiblíţíme první tři vrstvy. I kdyţ je tato práce určena pro databázové vývojáře, povaţuji za vhodné alespoň popsat principy optimalizace na těchto vrstvách, aby mohl čtenář získat jakýsi ucelený obraz, jak se jednotlivé vrstvy navzájem ovlivňují.

2.1 Databázový server Konfigurace databázového serveru je často navrţena uţ při plánování samotné databáze. Je potřeba vzít v úvahu přibliţnou velikost databáze, architekturu (nejčastěji klient – server), počet uţivatelů apod. Vytíţení jednotlivých hardwarových komponent je moţné sledovat za pomoci nástrojů operačního systému. S postupem času můţe velikost databáze narůstat a také přibývat počet uţivatelů současně pracujících s databází. Pokud server jiţ parametrově přestává vyhovovat nárokům databáze, je třeba přistoupit k investicím do hardwaru (zvětšení RAM paměti a kapacity diskového pole, přidání CPU) anebo k migraci na výkonnější server. Obě tyto varianty jsou však často velmi nákladné. Na druhou stranu pokud je slabým místem celého systému právě server, nepřinese optimalizace dalších vrstev větší uţitek.

2.2 Databáze Pod pojmem databáze rozumíme kolekci dat umístěných v jednom či více souborech na pevném disku databázového serveru. K samotným fyzickým datům přistupujeme přes logickou strukturu, jejíţ základní jednotkou je tabulka. Databáze obsahuje databázové soubory a nedatabázové soubory. Databázové soubory si můţeme představit jako uţivatelská data, jejichţ správa je v podstatě účelem databázového systému. Nedatabázové soubory pak obsahují inicializační parametry, protokolovací informace atd.6 Fyzická správa dat V průběhu instalace databázového systému je moţno nastavit způsob fyzického ukládání dat. Na výběr máme buď tradiční souborový systém nebo Automatic Storage Management (ASM). Technologie ASM je podporována od verze Oracle 10g Release 1 a od verze 12c je

6

BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 26-27. ISBN 978-80-251-2189-4. 13

dokonce povinná pro všechny databáze vytvořené pod touto nebo pozdější verzí.7 Výkon zvyšuje tím, ţe automaticky rozmisťuje databázové objekty mezi různá úloţná zařízení. Zároveň eliminuje potřebu pouţití softwaru pro správu logických svazků třetích stran. Pokud potřebujeme přidat nové diskové zařízení, můţeme tak díky ASM učinit bez nutnosti odstávky databáze.8 Logická správa dat Kromě fyzického ukládání dat pouţívá Oracle i logické datové struktury, které se skládají z tabulkových prostorů, segmentů, rozsahů a bloků. Datový blok je nejmenší logickou úloţnou jednotkou. Jeden blok představuje určitý počet bytů fyzického prostoru na disku. Rozsah seskupuje datové bloky alokované pro uloţení určitého typu informace. Segment je sada rozsahů alokovaných pro určitý databázový objekt (tabulka, index). Segmenty rozlišujeme na uţivatelské, návratové a dočasné. V tabulkových prostorech jsou uloţeny datové soubory databáze, ta v základu obsahuje dva tabulkové prostory SYSTEM a SYSAUX. Mimo to je moţné vytvářet další uţivatelské prostory, Oracle doporučuje vytvoření alespoň jednoho tabulkového prostoru pro uţivatelská data. Logické uspořádání je patrné na obrázku 2-1:

Obrázek 2-1: Segmenty, rozsahy, bloky v rámci tabulkového prostoru9

7

Oracle Database Installation Guide for Linux: Overview of Oracle Database Installation. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2013 [cit. 2013-12-28]. Dostupné z:http://docs.oracle.com/cd/E16655_01/install.121/e17720/install_overview.htm#LADBI7430. 8 BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 121. ISBN 978-80-251-2189-4. 9 Oracle Database Concepts: Logical Storage Structures. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2013-12-28]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/logical.htm#CNCPT89001. 14

Logické uspořádání dat ovlivňuje výkon zejména nastavením parametrů segmentu, jako jsou seznamy volných bloků a parametry PCTUSED a PCTFREE. Seznam volných bloků udrţuje informaci o blocích objektu, které jsou pod nejvyšší značkou, coţ je hranice mezi pouţitým a volným místem v segmentu.10 Kaţdý objekt má alespoň jeden seznam volných bloků. Pokud u daného objektu předpokládáme rozsáhlé provádění souběţných DML operací, můţe vytvoření několika seznamů pozitivně ovlivnit výkon. Kaţdá relace11 pracující s tabulkou bude přiřazena k jinému seznamu volných bloků a sníţí se tak riziko kolize mezi relacemi. Relace pouţívá po celou dobu jejího trvání pouze jeden seznam. V prostředí RAC, kdy je k jedné databázi vytvořeno více instancí, se často vyuţívá také skupin seznamů volných bloků. Kaţdá instance tak můţe spravovat svou vlastní skupinu seznamů.12 Parametr PCTFREE udává, kolik prostoru by mělo být v bloku rezervováno pro případnou aktualizaci tabulky. Pokud se pro nedostatek volného místa aktualizované řádky nevejdou do bloku celé, musí být buď přesunuty do jiného bloku anebo zřetězeny, coţ je neţádoucí z hlediska výkonu. Čtení dat z takového řádku vyţaduje přístup k více fyzickým datovým oblastem. Pokud tedy víme, ţe bude docházet k častým aktualizacím tabulky, je vhodné nastavit vyšší hodnotu parametru PCTFREE neţ je výchozích 10%.13 V případě, ţe je blok zaplněn z 90% a hodnota PCTFREE je nastavena na 10%, odstraní se blok ze seznamu volných bloků. K jeho návratu do seznamu dojde pouze tehdy, přesáhne-li volné místo v bloku hodnotu, která je odvozena od parametru PCTUSED. Výchozí hodnota pro tento parametr je 40%. Dosáhne-li volné místo v bloku více jak 60% při výchozím nastavení, je blok vrácen zpět do seznamu volných bloků. Zřetězené řádky tabulky můţeme identifikovat pomocí příkazu ANALYZE:14 ANALYZE TABLE „název tabulky“ LIST CHAINED ROWS INTO chained rows; Příkaz uloţí výsledek do tabulky chained rows, kterou je moţno vytvořit skriptem v adresáři ORACLE_HOME/rdbsm/sdmin. Sloupec HEAD_ROWID této tabulky pak obsahuje ROWID (viz kapitola 4.2) první části zřetězeného řádku. V případě velkého počtu zřetězených řádků je 10

Oracle Database Concepts: Logical Storage Structures. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2013-12-28]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/logical.htm#CNCPT89001. 11 Anglicky session. Je to v podstatě doba, kdy jsme přihlášeni k databázi. 12 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 254-257. ISBN 80251-0569-5. 13 BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 287. ISBN 978-80-251-2189-4. 14 Tamtéţ 15

tabulku nutné znovu vytvořit a nastavit větší hodnotu parametru PCTFREE. Zřetězení rádků je také moţno zjistit z pohledu V$SYSSTAT, kde poloţka „table fetch continued row“ je zvýšena o 1 pokaţdé, kdyţ databáze čte zřetězený řádek. 15 Konfigurace seznamů volných bloků dat a parametru PCTUSED je moţná pouze v módu Manual Segment Space Management (MSSM). Tento přístup sebou přináší rizika v podobě plýtvání místem. Kaţdá relace pracuje totiţ pouze s jedním seznamem volných bloků a ostatní ignoruje. Kdyţ vyčerpá všechny bloky v seznamu, zvýší nejvyšší značku tabulky a další volné bloky se nepouţijí. Nastavení příliš nízké hodnoty PCTUSED, způsobí, ţe volné místo v blocích nebude pouţito ani při odmazání poloviny dat z tabulky při výchozím nastavení PCTFREE (10%) a PCTUSED (40%). Ve většině případů nedokáţeme odhadnout počet souběţných operací nad tabulkou pro správné nastavení počtu seznamů volných bloků a ani definovat optimální hodnotu parametru PCTUSED.16 Jako vhodným řešením se jeví pouţití funkcionality Automatic Segment Space Management (ASSM), u které není třeba, vyjma PCTFREE, ručního nastavení parametrů logického ukládání segmentů. Ve většině případů dosahuje ASSM lepších výsledků neţ manuální správa MSSM.17

2.3 Instance Instance se váţe k databázi a je to vyhrazené místo v operační paměti serveru pro oblast zvanou System Global Area (SGA) a procesy běţící na pozadí, které komunikují právě s SGA nebo databázovými soubory. Jednu databázi můţe pouţívat i více instancí za předpokladu pouţití technologie RAC.18 Další paměťovou oblastí instance je Program Global Area (PGA), která je určena pro uţivatelské a jiţ zmíněné procesy na pozadí, běţící na databázovém serveru. SGA, na rozdíl od PGA, je sdílena všemi uţivateli instance. Databázový systém Oracle nabízí kompletní automatickou správu paměťových struktur instance. Jediné co je potřeba, je nastavit celkovou kapacitu operační paměti vyhrazenou pro instanci. Oracle pak uţ sám přerozděluje paměť mezi SGA a PGA a jejich dílčí 15

BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 288. ISBN 978-80-251-2189-4. 16 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 255-263. ISBN 80251-0569-5. 17 Oracle Database Concepts: Logical Storage Structures. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-01-10]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/logical.htm#CNCPT7668. 18 LONEY, Kevin. Oracle Database: Kompletní průvodce. 1. vyd. Brno: Computer Press, a.s., 2010, s. 35. ISBN 978-80-251-2489-5. 16

komponenty.19 V následujících dvou kapitolách si popíšeme tyto dvě oblasti a moţnosti jejich manuální správy.

Obrázek 2-2: Paměťové struktury databázového systému Oracle20

2.3.1 Systémová globální oblast Systémová globální oblast neboli SGA je skupina paměťových struktur databázového systému Oracle, sdílená všemi uţivateli instance. Skládá se z několika dílčích pamětí: Vyrovnávací paměť (Buffer cache) Obsahuje bloky dat, načtené operacemi DML. Pokud není tato oblast dostatečně velká pro uchování nejčastěji vyuţívaných bloků, můţe dojít k tomu, ţe chybějící bloky bude nutné znovu načíst z pevného disku, coţ přispívá k zhoršení výkonu dotazu. Velikost vyrovnávací paměti se určuje hodnota parametru DB_CACHE_SIZE a lze ji měnit za běhu instance.21

19

Oracle Database Administrator's Guide: Managing Memory. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2001, 2014 [cit. 2014-05-17]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17636/memory.htm#ADMIN11197. 20 Oracle Database Administrator's Guide: Managing Memory. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2001, 2014 [cit. 2014-05-17]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17636/memory.htm#ADMIN11011. 21 LONEY, Kevin. Oracle Database: Kompletní průvodce. 1. vyd. Brno: Computer Press, a.s., 2010, s. 876-877. ISBN 978-80-251-2489-5. 17

Sdílená oblast (Shared pool) Nastavuje se inicializačním parametrem SHARED_POOL_SIZE. Sdílená oblast je sloţena z vyrovnávací paměti knihoven (library cache), vyrovnávací paměti datového slovníku (Data dictionary cache) a vyrovnávací paměti protokolu (Server result cache). V paměti knihoven najdeme informace o spuštěných příkazech SQL a PL/SQL, včetně prováděcích plánů a stromů rozkladu. Při spouštění stejných dotazů se tak zvyšuje rychlost provedení, protoţe databáze má jiţ k dispozici potřebné informace z předchozího zpracování dotazu nebo příkazu pro manipulaci s daty. Do paměti datového slovníku se načítají sloupce z tabulek datového slovníku. Tyto sloupce jsou vyuţívány při provádění SQL dotazů a příkazů. Příliš malá velikost této paměti můţe způsobit dodatečné V/V operace, pokud jsou vyţadovány další informace z datového slovníku. Vyrovnávací paměť protokolu zapisuje poslední změny v datových blocích. Toto je důleţité v případě pádu instance, neţ jsou změny zapsány do datových souborů.22 Oblast Large (Large pool) Jedná se o volitelnou část paměti SGA a její pouţití má význam pouze pro případ, kdy pouţíváme architekturu sdílených serverů, paralelní zpracování dotazů a nástroj Recovery Manager. Pro vytvoření této oblasti je potřeba nadefinovat parametr LARGE_POOL_SIZE.23 Oblast paměti Java (Java pool) V podstatě stejný princip jako sdílená oblast pro kód napsaný v jazyce Java. Tuto paměť pouţívá Java Virtual Machine (JVM).24 Oblast paměti Streams (Streams pool) Podporuje vlastnost Oracle streams, která spravuje sdílení dat a událostí v distribuovaném prostředí.25 Oracle nabízí pro správu SGA nástroj Automatic Shared Memory Management (ASMM). Ten je moţné spustit konfigurací parametru SGA_TARGET na nenulovou hodnotu. Tento 22

BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 58. ISBN 978-80-251-2189-4. 23 Oracle Database Administrator's Guide: Managing Memory. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2001, 2014 [cit. 2014-01-10]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17636/memory.htm#ADMIN11225. 24 BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 59. ISBN 978-80-251-2189-4. 25 BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009, s. 59. ISBN 978-80-251-2189-4. 18

parametr vyjadřuje velikost všech předešle zmíněných oblastí dohromady. Při pouţití ASMM je dynamicky distribuována kapacita paměti jednotlivým oblastem. Pokud mají tyto oblasti jiţ nastavené hodnoty svých parametrů vyšší neţ nula, jsou tyto hodnoty pouţity jako spodní hranice přidělené paměti. Automatická správa paměti nemá vliv na oblasti Redo log buffer, KEEP a RECYCLE pool, ty je třeba definovat skrze příslušné inicializační parametry. V případě,

ţe

zůstaneme

u

manuelní

správy

SGA

je

třeba

nastavit

parametr

SGA_MAX_SIZE, coţ je maximální velikost paměti vyhrazená pro databázovou instanci.26

2.3.2 Globální programová oblast Globální programová oblast (PGA) je část operační paměti alokovaná pro jednotlivé uţivatelské procesy zpracovávané databázovým serverem. Paměť je vyuţívána zejména při provádění analytických funkcí, řazení dat a hash spojení a její velikost má vliv na výkon těchto operací. Při vykonávání dotazu je PGA rozdělena do pracovních oblastí právě dle předešle zmíněných operací.27 Pokud je velikost oblasti nedostatečná pro potřeby vykonávané operace, proces musí data zapsat do dočasných segmentů na disk a pak je znovu načíst, čímţ se zvyšuje počet V/V operací. Nutnost vícenásobného zápisu a čtení se pak znatelně projeví na výkonu prodlouţením časové odezvy.28 Hlavní dvě části PGA jsou: Private SQL Area – udrţuje informace o analyzovaném SQL příkazu a jsou do ní ukládány tzv. vázané proměnné. Vázané proměnné jsou parametry dotazu, do kterých je při spuštění dotazu vţdy vloţena nějaká konkrétní hodnota. Pouţití proměnných umoţňuje, ţe je u dotazu při prvním spuštění provedena úplná analýza a opětovné spuštění jiţ databáze provádí pouze jemnou analýzu (viz kapitola 4.1.1). V kontextu Private SQL Area je také často zmiňován pojem kurzor, coţ je jakýsi ukazatel na straně klienta odkazující na konkrétní privátní oblast.29

26

Oracle Database Performance Tuning Guide: Tuning the System Global Area. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2013 [cit. 2014-01-10]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_sga.htm#TGDBA517. 27 Oracle Database Performance Tuning Guide: Tuning the Program Global Area. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2013 [cit. 2014-01-15]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_pga.htm#TGDBA346 28 HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 559-560. ISBN 978-0-13-701195-7. 29 Oracle Database Concepts: Overview of the Program Global Area. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-01-15]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/memory.htm#CNCPT12371. 19

Obrázek 2-3: Kurzor30

SQL Work Areas – vyuţívána pro jiţ zmiňované operace řazení dat, hash spojení apod. Rozdělení jednotlivých pracovních oblastí je patrné z obrázku 2-4:

Obrázek 2-4: Složení paměti PGA31

Oracle nabízí moţnost spravovat paměť PGA buď automaticky anebo manuálně. Při automatické správě nastavuje databázový systém velikost pracovních oblastí všem běţícím procesům na základě jejich poţadavků. Objem veškeré pouţitelné paměti je odvozen od nastavení inicializačního parametru PGA_AGGREGATE_TARGET, který nám udává poţadovanou velikost paměti dostupnou pro oblast PGA. Ve výchozím nastavení je velikost PGA stanovena dynamicky jako 20% z kapacity oblasti SGA, minimálně však 10MB. Systém přiděluje volnou paměť tak, ţe od hodnoty parametru PGA_AGGREGATE_TARGET odečte veškerou paměť jiţ alokovanou ostatními procesy a zbylá kapacita paměti je rozdělována jednotlivým aktivním pracovním oblastem.32 V módu manuální správy se hodnota PGA_AGGREGATE_TARGET nastaví na nulu a velikost pracovních oblastí je definována

30

Oracle Database Concepts: Overview of the Program Global Area. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-02-20]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/memory.htm#CNCPT12371. 31 Tamtéţ 32 Oracle Database Performance Tuning Guide: Tuning the Program Global Area. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2013 [cit. 2014-02-20]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_pga.htm#TGDBA470 20

parametry SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE a CREATE_BITMAP_AREA_SIZE.33

2.3.3 Optimalizátor Optimalizátor

databáze

Oracle

je

vestavěný

nástroj,

jehoţ

účelem

je

nalezení

nejefektivnějšího způsobu vykonání SQL příkazu a je proto pouţíván všemi SQL příkazy. Při vytváření exekučních plánů dotazu, vychází optimalizátor ze statistik nad dotazovanými daty. Po vygenerování plánů dotazu, optimalizátor zvaţuje, který plán bude nejefektivnější. Rozhoduje se přitom dle několika faktorů:34 Systémové zdroje – CPU, operační paměť, V/V subsystém Počet řádků vrácených dotazem Výchozí velikost datových setů Na základě dostupných informací spočítá optimalizátor kaţdému plánu jeho cenu. Cena je číselná interní metrika databáze Oracle, která vyjadřuje očekávané mnoţství zdrojů potřebných pro vykonání příkazu. Cena je sloţena z mnoha dílčích metrik a nelze ji tudíţ přirovnat k ţádné z reálných veličin. Čím je cena niţší, tím je plán dotazu povaţován za optimálnější. Pro tento přístup je optimalizátor často nazýván jako optimalizátor zaloţený na cenách (CBO). Optimalizátor obsahuje tři základní komponenty: Query Transformer Tato komponenta má za úkol přepsat dotaz do jiné sémantické podoby, pokud je to moţné a následně vyhodnotit cenu alternativního dotazu. Logika dotazu je přitom zcela zachována, všechny alternativní dotazy budou vracet naprosto stejné výsledky. Query Transformer pouţívá několik metod k přepisování dotazu35:

33

Oracle Database Administrator's Guide: Managing Memory. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2001, 2014 [cit. 2014-02-20]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17636/memory.htm#ADMIN11234. 34 Oracle Database SQL Tuning Guide: Query Optimizer Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-03-09]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optcncpt.htm#TGSQL195. 35 Oracle Database SQL Tuning Guide: Query Transformations. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-03-09]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optcncpt.htm#TGSQL201. 21

OR Expansion – pokud klauzule WHERE v dotazu obsahuje operátor OR, optimalizátor ho nahrazuje vytvořením dvou dotazů spojených pomocí UNION ALL. View Merging – je pouţito v případě dotazování se nad pohledy. Optimalizátor sloučí dotaz s dotazem pohledu. Predicate Pushing – přesouvá predikáty dotazu do bloku dotazu pohledu, na který nebyla aplikována metoda View Merging. Tyto predikáty mohou být vyuţity k pouţití indexu nebo jako filtry uţ v dotazu pohledu. Subquery Unnesting – transformuje poddotaz v podmínce WHERE do spojení JOIN, pokud je to moţné. Poddotaz nesmí obsahovat agregační funkce. Přepsání dotazu s použitím materializovaného pohledu – optimalizátor kontroluje, zda je daný dotaz kompatibilní s dotazem v definici materializovaného pohledu (viz kapitola 3.4) a v případě shody je dotaz přepsán s pouţitím pohledu. Transformace schématu hvězda – snaţí se předejít úplnému procházení tabulky faktů při spojení na tabulky dimenzí, které jsou omezeny podmínkou. Table Expansion – dovoluje optimalizátoru pouţít index nad nejčtenější částí tabulky rozdělené na oddíly. Join Factorization – pokud dotazy spojené klauzulí UNION ALL pracují alespoň z části se stejnými tabulkami, optimalizátor sloučí tyto tabulky do poddotazu. Estimator Estimator je další částí optimalizátoru, která počítá celkovou cenu exekučního plánu. K tomu pouţívá tři metriky:36 Selektivita – vyjadřuje procento řádků z celkového počtu řádků tabulky, které vyhovují predikátům v podmínce WHERE. Selektivita nabývá hodnot v rozsahu od 0 do 1. Čím blíţe 0, tím je dotaz selektivnější.

36

Oracle Database SQL Tuning Guide: Query Optimizer Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-03-17]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optcncpt.htm#TGSQL213. 22

Kardinalita – odhadovaný počet řádků, vrácený kaţdou z operací v exekučním plánu. Odvozuje se ze statistik DBMS_STATS nebo z predikátů dotazu a funkcí DISTINCT a agregačních funkcí. Kardinalitu povaţujeme za nízkou, pokud je nízký poměr distinktních hodnot ve sloupci vůči celkovému počtu řádků v tabulce. Cena – představuje mnoţství práce, která musí být vykonána systémovými zdroji, aby byl dotaz zpracován. Zahrnuje CPU, vnitřní paměť, fyzický disk. Plan Generator Plan Generator vytváří různé varianty plánů dotazu, tím, ţe na dotaz aplikuje rozdílné přístupové metody, metody spojení a pořadí spojení. Cena jednotlivých plánů je spočítána Estimatorem a je vybrán plán s nejniţší cenou. Díky variabilitě přístupových metod a spojení, můţe být generováno nepřeberné mnoţství plánů. Optimalizátor pouţívá vnitřní mechanismus, aby generování plánů včas ukončil. Tento mechanismus je zaloţen na ceně aktuálně nejlepšího plánu, pokud je cena vysoká, jsou zkoumány další alternativní dotazy, pokud je cena nízká je proces ukončen a je vybrán aktuální plán.

Obrázek 2-5: Komponenty optimalizátoru37

Cíl optimalizátoru Cíl neboli mód optimalizátoru se nastavuje prostřednictvím parametru OPTIMIZER_MODE při startu instance. Optimalizátoru je moţno nastavit jeden ze dvou cílů:38

37

Oracle Database SQL Tuning Guide: Query Optimizer Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-10]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_transform.htm#TGSQL94896. 23

ALL_ROWS – ukládá optimalizátoru co nejrychleji vrátit všechny řádky dotazu. Jedná se o optimalizaci propustnosti. Toto je výchozí nastavení optimalizátoru. FIRST_ROWS_N – ukládá optimalizátoru co nejrychleji vrátit N prvních řádků dotazu. N můţe nabývat hodnot 1,10,100,1000. Jedná se o optimalizaci počáteční odezvy. Tento cíl můţe být uţitečný u interaktivních aplikací, kdy koncoví uţivatel čeká na zobrazení dat a právě rychlost zobrazení první části můţe být u takových aplikací kritickým měřítkem. Chování optimalizátoru lze ovlivnit i nastavením mnoha dalších inicializačních parametrů. Pro příklad uveďme několik málo z nich:39 CURSOR_SHARING – převádí doslovné hodnoty v SQL příkazech na vázané proměnné. Ty mají vliv na sdílení kurzoru v sdílené oblasti SGA (viz kapitola 2.3.1) a ovlivňují exekuční plán dotazu. DB_FILE_MULTIBLOCK_READ_COUNT – určuje kolik bloků, bude načteno jednou V/V operací, během úplného procházení tabulky nebo indexu. Optimalizátor bere v úvahu tento parametr při výpočtu ceny operací úplného procházení. Velká hodnota můţe způsobit, ţe bude dána přednost projití celé tabulky před pouţitím indexu. OPTIMIZER_INDEX_CACHING – nastavuje se na hodnotu od 0 do 100. Tato hodnota vyjadřuje procento bloků indexu ve vyrovnávací paměti. Optimalizátor bere v potaz tento počet bloků k určení očekávaného mnoţství V/V operací, coţ ovlivňuje výpočet ceny plánu. OPTIMIZER_INDEX_COST_ADJ – nastavuje se na hodnotu od 0 do 1000. Tato hodnota ovlivňuje náklady vypočtené optimalizátorem na přístup pomocí indexu. Čím menší hodnota, tím levněji bude přístup pomocí indexu posouzen.

38

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 372. ISBN 80-2510569-5. 39 Oracle Database SQL Tuning Guide: Influencing the Optimizer. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-03-25]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_influence.htm#TGSQL250. 24

3

Design databáze

3.1 Návrh databázového schéma Při návrhu databáze je obvykle vytvořen logický model, znázorňující obchodní logiku dat spravovaných databázovým systémem. U relačních databází je to nejčastěji entitně – vztahový model, kde jsou data rozděleny do entit, mezi kterými jsou definovány vztahy. Logický model je pak převáděn na fyzický relační model dat. Jednotlivé relace jsou reprezentovány tabulkami, seznamem atributů (sloupců), datových typů atributů a integritních omezení. Obecně bývá jedním z cílů návrhu relačního modelu jeho normalizace. V normalizovaném schéma je eliminována redundance uloţených dat a řádky relací a vztahy mezi relacemi jsou specifikovány za pomoci primárních a cizích klíčů. Stupňů normalizace je několik a schéma by mělo být alespoň ve třetí normální formě 3NF. U třetí normální formy platí, ţe všechny atributy relace závisí přímo na klíči a neexistuje tranzitivní závislost ţádného z atributů. 40 Pro kaţdý sloupec tabulky, který není přímo závislý na klíči, by měla být vytvořena samostatná tabulka. Pro ilustraci si představme entitu objednávek, která udrţuje informace o id objednávky, čase objednávky, kdo objednávku vytvořil, produktu apod. Entita produkt není přímo závislá na objednávce, protoţe produkt můţe existovat samostatně, aniţ by na něj byla vytvořena objednávka. V normalizovaném schéma 3NF bude tedy pro entitu produkt vytvořena samostatná tabulka a objednávka se bude odkazovat přes cizí klíč do tabulky produktů. Kromě třetí normální formy existují i další formy jako Boyce – Coddova forma, čtvrtá normální a pátá normální forma.

3.1.1 Vertikální dělení a spojování Relační model má přímý vliv na sloţitost SQL dotazů nad příslušnými daty a tím pádem i na výkon. Volba vhodného schématu je odvislá právě od způsobu dotazování respektive pouţívání relací. Atributy určité entity můţeme buď uloţit do jedné relace41, nebo je rozdělit do více relací – tento způsob nazýváme vertikální dělení. Uvaţujme např. entitu zákazník

40

HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 79-80. ISBN 978-0-13-701195-7. 41 V kontextu databázového schéma je relací myšlena tabulka. 25

telefonního operátora a její atributy ADRESA a KREDIT. Atributy zákazníka jsou závislé na klíči ID. Entitu můţeme uloţit do jedné nebo do dvou relací: 42 Zákazník (ID, ADRESA, KREDIT) – vše v jedné relaci

ZákazníkAdresa (ID, ADRESA) – atribut ADRESA je uloţen v samostatné relaci ZákazníkKredit (ID, KREDIT) – atribut KREDIT je uloţen v samostatné relaci Druhé schéma se dvěma relacemi je vhodnější v těchto případech: Atributy jsou dotazovány častěji samostatně neţ zároveň Atributy obsahují dlouhé textové řetězce Atributy jsou často měněny (např. kredit po kaţdém hovoru) → relace ZákazníkKredit bude uloţena v méně blocích, můţe se snáze vejít do operační paměti → rychlejší table/index scan Oproti tomu uloţení všech atributů v jedné relaci má své opodstatnění pokud: Atributy jsou přistupovány současně → není třeba operace spojení43 Dalším, poněkud opačným přístupem je tzv. Vertikální spojování (antipartitioning). Vertikálním spojováním zvyšujeme počet atributů relace dle očekávaných dotazů. Mějme např. schéma akciového trhu a dvě relace: AkcieDetail(akcie_id, datum_vydání, firma) AkcieCena(akcie_id, datum, cena) Historie cen akcií je udrţována za posledních 3000 dní. Makléři se rozhodují hlavně podle dat za posledních 10 dnů. Dotazování na 10 denní historii vývoje ceny bude náročné i přes pouţití indexu na atributy akcie_id a datum. Můţeme doplnit relaci AkcieDetail o následující sloupce: cena_dnes, cena_včera, ..., cena před 10 dny. Dotazování na 10 denní historii bude

42

DOHNAL, Ladislav. Efektivní využívání DB: Ladění schéma. [online]. 2013, 19–20 [cit. 2014-04-05]. Dostupné z: https://is.muni.cz/el/1433/jaro2013/PA152/um/slides10-ladeni-schema.pdf 43 DOHNAL, Ladislav. Efektivní využívání DB: Ladění schéma. [online]. 2013, 19–20 [cit. 2014-04-05]. Dostupné z: https://is.muni.cz/el/1433/jaro2013/PA152/um/slides10-ladeni-schema.pdf 26

rychlejší → stačí jedno prohledání indexu a není třeba spojení pro dodatečné informace o akcii. Nevýhodami vertikálního spojování je především replikace dat.44

3.1.2 Denormalizace I přes nesporné klady normalizace databázového schéma, můţeme z důvodu zvýšení výkonu uvaţovat o denormalizaci. S tímto přístupem se můţeme setkat zejména u datových skladů a je to také jeden z hlavních rozdílů mezi nimi a OLTP systémy, které bývají téměř výhradně ve třetí nebo vyšší normální formě.45 Denormalizace většinou seskupuje atributy z různých relací do jedné. Díky tomu lze předcházet operacím spojení při současném výběru atributů z více relací. Proces denormalizace však sebou nese jistá rizika: 46 Dotazy na průměrné či jiné analytické hodnoty nad replikovanými daty se mohou komplikovat. Denormalizace vede k vyšší reţii při vkládání nebo aktualizaci řádků tabulky → nevhodné pro časté aktualizace dat. Pokud aplikace obsluhující denormalizovaná data není správně napsána, můţe způsobit nekonzistenci dat. Údrţba denormalizovaných dat můţe být spojena s nákladnějším vývojem aplikace, která by s těmito daty měla pracovat. Tento problém lze částečně redukovat pouţitím triggerů a materializovaných pohledů. Ty mohou provádět údrţbu redundantních dat, aniţ by tato funkce musela být součástí aplikace. Pouţití denormalizace bychom měli důkladně zváţit a porovnat, zda následné zvýšení výkonu vyváţí veškeré její zápory.

44

DOHNAL, Ladislav. Efektivní využívání DB: Ladění schéma. [online]. 2013, 21–20 [cit. 2014-04-05]. Dostupné z: https://is.muni.cz/el/1433/jaro2013/PA152/um/slides10-ladeni-schema.pdf 45 Oracle Database Data Warehousing Guide: Introduction to Data Warehousing Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2001, 2013 [cit. 2014-04-05]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17749/concept.htm#DWHSG9282 46 HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 89-90. ISBN 978-0-13-701195-7. 27

3.1.3 Datové typy Přestoţe se definice datových typů atributů můţe jevit jako banální záleţitost, nevhodně zvolené datové typy mohou mít za následek mj. tyto dva negativní jevy: 47 Niţší výkon Zvýšení poţadavků na prostor Pro sloupce, které obsahují číslo nebo datum je vţdy vhodnější pouţít datový typ NUMBER a DATE namísto typu CHAR nebo VARCHAR2. Mějme např. tabulku se dvěma indexovanými datumovými sloupci. V jednom ze sloupců bude datum uloţeno jako řetězec (YYYYMMDD) a v druhém typu DATE. Při provedení dotazu nad tabulkou prostřednictvím typu DATE optimalizátor lépe vyhodnotí kardinalitu dotazu neţ by tomu bylo u datového typu řetězec. Výsledná kardinalitu dotazu pak můţe mít vliv na to, zda optimalizátor zvolí úplné nebo indexové prohledávání. U datových typů CHAR a VARCHAR2 je velmi doporučeno nepouţívat maximální délky, jestliţe to není nezbytné. Pokud víme, ţe hodnoty ve sloupci mohou nabývat pouze konkrétní nebo určité maximální moţné délky, je vhodné tyto parametry specifikovat. Čím větší velikosti sloupců dotaz vrací, tím větší místo v operační paměti musí být rezervováno. Typ CHAR vţdy spotřebuje tolik místa na kolik je definována jeho velikost, i kdyţ do něj vloţíme jakkoliv niţší počet znaků, databázový systém doplňuje zbylý prostor mezerami. 48

3.2 Indexy Z databázových objektů mají na výkon SQL dotazů podstatný vliv zejména indexy. Index je struktura, nad databázovou tabulkou, poskytující rychlý přístup k poţadovaným řádkům tabulky. V indexu se vyskytují uspořádané hodnoty dat konkrétního sloupce nebo sloupců. Pouţití indexu databázovým systémem můţeme přirovnat k vyhledávání v rejstříku knihy.49 V případě neexistence indexu je k nalezení konkrétních řádků provedeno úplné procházení tabulky. 47

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 407-409. ISBN 80251-0569-5. 48 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 410. ISBN 80-2510569-5. 49 R. GROFF, James a Paul N. WEINBERG. SQL: Kompletní průvodce. 1. vyd. Brno: CP Books, a.s., 2005, s. 360-361. ISBN 80-251-0369-2. 28

Index lze vytvořit nad jedním nebo více sloupci tabulky. Indexované sloupce by měly být nejčastějšími predikáty dotazů nad tabulkou. Pokud indexované sloupce zároveň obsahují i omezení referenční integrity, předejdeme tak kompletnímu uzamčení tabulky v případě úprav primárního klíče rodičovské tabulky. U více sloupcového indexu je důleţité pořadí sloupců v definici indexu, kdy nejvíce dotazovaný sloupec by měl být na prvním místě. Indexy, kromě bitmapového neobsahují hodnoty NULL, nelze je tak vyuţít k dotazování se na prázdné řádky indexovaných sloupců.50 Při vkládání, mazání nebo změny řádků tabulky, databáze automaticky aktualizuje i index. Z tohoto důvodu můţe velký počet indexů nad tabulkou zpomalit provádění DML příkazů. Odmazáním nevyuţívaných indexů ušetříme databázovému systému práci s jejich údrţbou a fyzické místo potřebné pro uloţení indexů. Vyuţití indexů lze monitorovat za pomoci funkcionality ALTER INDEX MONITORING USAGE. Indexy můţeme rozdělit na několik základních typů: 51 B*strom index (B*Tree) Bitmapový index Funkční index Doménový index

3.2.1 B*strom index Jedná se o nejběţnější typ databázového indexu. Jeho název je odvozen od stromové struktury. Jednotlivé bloky větví obsahují seřazené rozsahy hodnot indexu. Bloky jsou hierarchicky uspořádány a jsou pouţity k vyhledání konkrétních hodnot sloupce. Tyto hodnoty jsou pak uloţeny v blocích listů spolu s adresou hledaného řádku tabulky ROWID. Všechny bloky listů jsou ve stejné úrovni, čili hledání jakékoliv hodnoty v indexu zabere zhruba stejný čas. S počtem bloků souvisí pojem výška indexu, ta nám udává kolik bloků, od

50

Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-11]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT811. 51 Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-12]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT811. 29

kořenového bloku aţ k bloku listu, je třeba projít pro nalezení poţadovaného řádku. S větší výškou roste i čas odezvy dotazu.52

Obrázek 3-1: Vnitřní struktura indexu B*strom53

Index s obráceným pořadím Index s obráceným pořadím (reverse key index), fyzicky prohazuje pořadí bytů klíče indexu. Zabraňuje tak shlukování klíčů v pravé větvi indexu. K tomuto problému dochází v prostředí RAC, kdy více instancí můţe najednou modifikovat stejný blok. Indexované primární klíče mají většinou sekvenčně vytvářené hodnoty. Jedna instance tak např. vloţí hodnotu klíče 20 a druhá 21, obě pouţijí listový blok na pravé straně indexu. Reverzní index zajistí, ţe vkládaná data budou rozloţena do více listových bloků. Nevýhodou tohoto indexu je to, ţe databáze nemůţe pouţít k procházení index range scan, protoţe hodnoty klíče nejsou při vládání řazeny. Ilustrujme příklad, kdy uţivatel bude dotazovat všechna data, která mají hodnotu klíče větší neţ 20. Procházení nemůţe začít v bloku s touto hodnotou a pokračovat horizontálně do dalších bloků, jelikoţ klíč 21 můţe být uloţen daleko od bloku s klíčem 20.54

52

Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-12]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT1170. 53 Tamtéţ 54 Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-12]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT1181. 30

Vzestupný a sestupný index Index řadí uloţená data vzestupně. Řetězce podle binární hodnoty bytů klíče, čísla od nejmenšího po největší, časová data od nejstarších po nejmladší. Během vytváření indexu je moţno nastavit i sestupné řazení. Tento přístup má své uplatnění, v případě kdy poţadujeme dotazovaná data seřadit sestupně. Databáze můţe vyuţít řazení indexu namísto toho, aby data seřadila v některé z dalších operací dotazu.55 B*strom indexy obecně vykazují velmi dobrý výkon u operací jako je vyhledávání dle rozsahu hodnot a vyhledání konkrétní hodnoty, díky vzájemnému propojení sousedících listových bloků. Údrţba indexu však můţe být někdy nákladnou záleţitostí. Nesekvenční vytvoření nové unikátní hodnoty indexovaného sloupce způsobí, ţe databáze bude chtít vloţit hodnotu do bloku s příslušným rozsahem. Problém nastává ve chvíli, kdy v bloku uţ není potřebné místo pro uloţení záznamu. Dojde tak k vytvoření nového bloku a polovina záznamů naplněného bloku je přesunuta do nového. Posléze jsou vytvořeny odkazy na sousedící bloky a v případě, ţe není místo ani v bloku větve je rozdělen i ten.56

3.2.2 Bitmapový index Zatímco v B*strom indexu kaţdý klíč odkazuje na konkrétní řádek indexovaného sloupce, v bitmapovém indexu můţe jeden klíč odkazovat na libovolné mnoţství řádků. Unikátním hodnotám klíče je přidělena bitmapa. Pro kaţdý řádek sloupce je nastaven příslušný bit na základě hodnoty klíče indexu. Mapovací funkce převádí pozice bitů na aktuální ROWID. Bitmapový index vyuţijeme zejména tam, kde je velmi nízká kardinalita, coţ je počet unikátních hodnot v daném sloupci vzhledem k celkovému mnoţství řádků. Na rozdíl od ostatních indexů, jsou ukládány i klíče pro NULL hodnoty, tato funkcionalita umoţňuje pouţít index u dotazů typu COUNT. Aktualizace řádku indexovaného sloupce většinou způsobí uzamčení všech řádků se stejnou hodnotou bitu jako daný řádek. Z tohoto důvodu není bitmapový index vhodný při časté modifikaci tabulky příkazy DML.57

55

Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-12]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT1181. 56 HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 114. ISBN 978-0-13-701195-7. 57 Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-14]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT1182. 31

Příklad pouţití jednotlivých indexů si ukáţeme na jednoduché tabulce zaměstnanců (3-1). V prvním sloupci tabulky jsou uloţena ID zaměstnanců, druhý sloupec obsahuje příjmení zaměstnance a poslední pohlaví. Logicky odvodíme, ţe ve sloupci pohlaví se budou vyskytovat pouze dvě moţné hodnoty M a Ţ. Tento sloupec je vzhledem k nízké kardinalitě velice vhodným kandidátem na bitmapový index. Výsledná bitmapa je znázorněna v tabulce 3–2. Naproti tomu atribut ID_ZAM je sekvenčně generovaný unikátní primární klíč tabulky, tedy se přímo nabízí pro vytvoření indexu B*strom. ID_ZAM

PRIJMENI

POHLAVI

1001

Nováková

Ţ

1002

Vyskočil

M

1003

Zlámal

M

1004

Zajícová

Ţ

1005

Lexa

M

Tabulka 3-1: Tabulka zaměstnanců

HODNOTA

ŘÁDEK 1

ŘÁDEK 2

ŘÁDEK 3

ŘÁDEK 4

ŘÁDEK 5

Ž

1

0

0

1

0

M

0

1

1

0

1

Tabulka 3-2: Příklad bitmapy nad sloupcem POHLAVI58

Typický dotaz s vyuţitím bitmapového indexu by mohl vypadat takto: SELECT COUNT(*) AS POCET_MUZU FROM zamestnanci WHERE pohlavi = 'M'; Typický dotaz s vyuţitím stromového indexu: SELECT * FROM zamestnanci WHERE id_zam = 1004;

58

Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-14]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT1182. 32

Bitmapový index pro operaci spojení Bitmapový index pro operaci spojení je index určený speciálně pro spojení dvou nebo více tabulek. Index uchovává ROWID pro kaţdou hodnotu sloupce v jedné tabulce, která odpovídá řádkům v druhé indexované tabulce. Pouţitím tohoto typu indexu můţeme významně zredukovat počet řádků, které by musely být spojeny na základě spojovací podmínky v dotazu. Pro příklad uvaţme tento dotaz:59 SELECT COUNT(*) AS POCET FROM zamestnanci JOIN profese ON zamestnanci.id_prof = profese.id_prof WHERE profese.nazev = 'Programátor'; Dotaz zjišťuje, kolik je zaměstnanců na pozici programátor. Databáze nejdříve vybere všechny řádky z tabulky PROFESE, které odpovídají podmínce a posléze všechny řádky z tabulky ZAMESTNANCI s příslušnou hodnotou ID_PROF. Nyní vytvoříme bitmapový index pro spojení: CREATE BITMAP INDEX zamestnanci _bm_idx ON zamestnanci (profese.nazev) FROM zamestnanci, profese WHERE zamestnanci.id_prof = profese.id_prof; Index je vytvořen nad tabulkou ZAMESTNANCI, indexovány jsou však hodnoty sloupce PROFESE.NAZEV, které odkazují na jednotlivé ROWID řádků v tabulce ZAMESTNANCI. Není tak třeba přístupu k oběma tabulkám, protoţe samotný index v sobě nese poţadované informace jak je patrné z 3-3. PROFESE.NAZEV

ZAMESTNANCI.ROWID

Programátor

AAAQNKAAFAAAABSAAJ

Programátor

AAAQNKAAFAAAABSAAK

Analytik

AAAQNKAAFAAAABTAAH

Tabulka 3-3: Obsah bitmapového indexu pro spojení60

59


3.2.3 Funkční index Funkční index umoţňuje nad indexovaným sloupcem vytvořit funkci, která pak můţe být součástí dotazu. Index uchovává výslednou hodnotu funkce. Tento přístup můţe být vyuţit jak pro B*strom tak pro Bitmapový index. Pro definici funkce lze vyuţít aritmetický výpočet, SQL a PL/SQL funkci, funkci balíku a funkci volání jazyka C. Příklady funkčního indexu nad tabulkou zaměstnanci (3-1): 61 CREATE INDEX zamestnanci _prijm_uppercase_idx ON zamestnanci (UPPER(prijmeni)); Index bude pouţit u dotazu typu: SELECT * FROM zamestnanci WHERE UPPER(prijmeni) = 'LEXA'; Funkční index je uţitečný i v situacích kdy chceme indexovat pouze řádky s určitou hodnotou: CREATE INDEX zamestnanci _act_flg _idx ON zamestnanci (CASE WHEN act_flg = 'Y' THEN 'Y' END); Funkce indexu vrací hodnotu NULL pro všechny řádky, které nesplňují podmínku ACT_FLG = 'Y'.62

3.3 Tabulky O tabulkách jsme se jiţ zmínili v souvislosti s logickou správou dat a databázovým schéma, kde jsme tabulky označovaly jako relace. Výkon SQL dotazů je moţné zlepšit vytvořením vhodného typu tabulky.

3.3.1 Indexově orientovaná tabulka Základním typem relační tabulky v databázovém systému Oracle je tabulka uspořádaná do haldy. Řádky v této tabulce nejsou nikterak seřazeny. Při vkládání nových záznamů je 61


pouţito první volné místo v segmentu, do kterého se data vejdou. Po odstranění dat z tabulky je toto místo opět k dispozici pro případné vloţení dalších a aktualizaci stávajících řádků.63 Databáze Oracle dále podporuje tzv. indexově orientované tabulky (IOT). Jedná se v podstatě o tabulku uloţenou v indexu B-strom. Namísto dvojí úloţné struktury (indexové a datové) máme pouze jednu. Data jsou setříděna dle primárního klíče tabulky. V listech indexu jsou uloţeny hodnoty klíčových i neklíčových sloupců. Náhodný přístup k řádkům tabulky je rychlý, protoţe jednotlivé řádky jsou získány přímo z indexu za pomoci hodnot primárního klíče, takţe není nutné přistupovat zvlášť do indexu a zvlášť do tabulky. Operace vkládání, mazání a aktualizace řádků jsou prováděny pouze na úrovni indexu.64

Obrázek 3-2: Indexově orientovaná tabulka65

Indexově orientované tabulky jsou vhodné zejména tam, kde přistupujeme do tabulky přes sloupec primárního klíče a tabulka obsahuje pouze malý počet sloupců s nevelkými řádky, jako např. asociativní tabulky. Velký počet sloupců nebo dlouhých řádků u IOT tabulky by mohl způsobit, ţe se nám do listového bloku vejde jen malý počet řádků a mnoţství bloků nám vzroste. Tento jev negativně působí na výkon operace vyhledávání dle rozsahu a zvětšuje

63

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 413. ISBN 80-2510569-5. 64 Oracle Database Administrator's Guide: Managing Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2001, 2014 [cit. 2014-05-15]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17636/tables.htm#ADMIN015. 65 Oracle Database Concepts: Indexes and Index-Organized Tables. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-18]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT88855. 35

výšku indexu.66 Nad IOT tabulkami je také moţno postavit sekundární index. Indexově orientovanou tabulku vytvoříme stejně jako klasickou tabulku, jen je nutné definovat primární klíč tabulky a přidat klauzuli ORGANIZATION INDEX.

3.3.2 Seskupené tabulky Speciální skupinou tabulek jsou tzv. seskupené tabulky neboli clustery. Pokud je nutné přistupovat k více tabulkám zároveň, můţeme je seskupit a zvýšit tak výkon dotazů nad těmito tabulkami. Pro cluster je nutné definovat klíč, tím je sloupec, který mají seskupené tabulky společný. Kaţdá hodnota klíče clusteru je uloţena v clusteru i v indexu pouze jednou, díky čemuţ ušetříme místo. Řádky sdílející stejnou hodnotu klíče jsou fyzicky uloţeny pohromadě v jednom bloku, coţ sniţuje počet V/V operací a čas odezvy. Seskupené tabulky jsou výborným nástrojem jak předejít častému spojování tabulek v jednom dotazu.67 Avšak jejich pouţití není příliš vhodné v situacích kdy:68 Řádky tabulky jsou často modifikovány. Modifikace zabere více času neţ u běţné tabulky. Modifikované řádky mohou být přesouvány do jiných bloků. Je často vykonáváno úplné procházení nad jednou z tabulek. Databáze bude pravděpodobně nucena přečíst více bloků, protoţe jsou tabulky uloţeny pohromadě. Data z tabulek se stejným klíčem se nevejdou do jednoho nebo dvou bloků. Počet řádků pro kaţdou hodnotu klíče clusteru se výrazně liší. Kromě tabulek seskupených pomocí indexu, existují ještě hash seskupené tabulky. Zde je namísto indexu pouţita hash funkce na klíč clusteru určitého řádku. Výsledná hodnota koresponduje s datovým blokem v clusteru, se kterým pracuje konkrétní dotaz nad tabulkou. Výhodou je provedení pouze jediné operace při hledání anebo ukládání řádku, protoţe data

66

HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 134. ISBN 978-0-13-701195-7. 67 Oracle Database Concepts: Tables and Table Clusters. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-18]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/tablecls.htm#CNCPT608. 68 Oracle Database SQL Tuning Guide: Using Indexes and Table Clusters. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-18]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_indcl.htm#TGSQL870. 36

jsou sama indexem. Tento typ clusteru je vhodný pro data, která jsou dotazována přes podmínku rovnosti klíče.69

3.4 Materializované pohledy V prostředí, kde jsou často poţadovány statistické a jiné analytické přehledy nad velmi rozsáhlými objemy dat, můţe být provedení takových dotazů velmi nákladnou záleţitostí. Takováto práce s daty je charakteristická především pro datové sklady. Jako řešení se jeví vytvoření dalších tabulek, ve kterých budou data sumarizována do poţadované podoby většinou dle různých časových období. Dotaz nad takovou tabulkou, tak bude podstatně méně náročnější, neţ pokud by sám měl provést agregaci nad detailními daty, zejména, jsou-li tyto dotazy vykonávány často. Databáze Oracle nabízí pro tyto situace sofistikované řešení v podobě materializovaných pohledů. Materializovaný pohled je ve své podstatě tabulkou, ve které jsou uloţeny řádky vrácené dotazem v definici pohledu. Pohledy jsou vyuţívány i optimalizátorem k přepisu přímých dotazů, v případě, ţe je přepsání dotazu shledáno efektivnějším a pohled obsahuje aktuální data. Databáze nabízí moţnost automatické údrţby pohledu, čímţ zajišťuje, ţe pohled bude vţdy anebo v pravidelných intervalech aktualizován. Aktualizace pohledu je zajišťována kompletním provedením dotazu pohledu, coţ můţe být časově velmi náročné. Druhou variantou je vytvoření logu materializovaného pohledu, který sleduje změny ve zdrojových tabulkách pohledu. Pohled je pak upraven pouze o tyto změny bez nutnosti kompletního přepočtu. Pohled lze aktualizovat jednorázovým příkazem, po kaţdé změně zdrojové tabulky nebo v pravidelných intervalech. S údrţbou pohledu je také často spojena nemalá reţie. Je na nás otestovat, zda vytvoření materializovaného pohledu přinese takové výsledky ve výkonu, aby vyrovnaly náklady spojené s jeho údrţbou.70

3.5 Horizontální dělení Horizontální dělení je technika, umoţňující rozdělit velké tabulky a indexy na oddíly. Kaţdý oddíl je nezávislým objektem s vlastním jménem a uloţením. Navenek se však rozdělené 69

Oracle Database Concepts: Tables and Table Clusters. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-22]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/tablecls.htm#CNCPT609. 70 HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 97, 99. ISBN 978-0-13-701195-7. 37

objekty stále tváří jako jeden a není třeba ţádné úpravy příkazů pracujících s tabulkou nebo indexem. Pouţívání oddílů sebou přináší tato pozitiva:71 Zlepšení dostupnosti – při výpadku části datových souborů dojde k odstávce pouze u jednoho nebo části oddílů a ne celého objektu, pokud jsou oddíly rozmístěny do oddělených tabulkových prostorů. Postiţený oddíl se přepne do reţimu offline a uţivatelé dál mohou pracovat s funkční mi oddíly objektu.72 Snadnější správa – provádění operací nad menšími objekty je obecně snadnější, rychlejší a méně náročné na prostředky. V případě potřeby znovuobnovení objektu rozděleného na oddíly, můţeme postupně obnovovat jednotlivé oddíly namísto celého objektu najednou.73 Sniţuje riziko kolize u sdílených zdrojů – významné především u OLTP systémů. DML operace jsou rozděleny do více segmentů. Zvýšení výkonu – souvisí se schopností optimalizátoru vyřadit určité oddíly při zpracování dotazu. Databáze Oracle rozděluje tabulky dle čtyř metod: Rozsahové dělení Data jsou rozdělena dle rozsahu hodnot klíče oddílu. Jedná se o nejběţnější typ dělení, často pouţívaný u časových dat. V definici tabulky je určena horní hranice hodnoty pro kaţdý z oddílů. Databáze pak automaticky ukládá do příslušných oddílů ty řádky s hodnotou klíče menší neţ určená hranice oddílu. Příklad vytvoření rozsahově dělené tabulky prodejních objednávek:74 CREATE TABLE objednávky (obj_id NUMBER(10) ,zak_id NUMBER ,datum DATE 71

Oracle Database Concepts: Partitions, Views, and Other Schema Objects. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-22]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/schemaob.htm#CNCPT88858. 72 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 211. ISBN 80-2510569-5. 73 Tamtéţ 74 Oracle Database Concepts: Partitions, Views, and Other Schema Objects. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-25]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/schemaob.htm#CNCPT88860. 38

,...) PARTITION BY RANGE (datum) (PARTITION obj_2012 VALUES LESS THAN (TO_DATE(20130101, 'YYYYMMDD ')), PARTITION obj_2013 VALUES LESS THAN (TO_DATE(20140101, 'YYYYMMDD')), PARTITION obj_2014 VALUES LESS THAN (TO_DATE(20150101, 'YYYYMMDD')), ...); Dělení dle seznamu Data jsou rozdělovány do oddílů dle seznamu hodnot nastaveného pro kaţdý rozdíl. Seznam obsahuje konkrétní hodnoty sloupce, který je určen jako klíč oddílu. Pro ukázku pouţijeme opět tabulku objednávek, tentokráte rozdělenou dle sloupce stav objednávky:75 CREATE TABLE objednávky (obj_id NUMBER(10) ,zak_id NUMBER ,datum DATE ,stav_obj NUMBER(10) ,...) PARTITION BY LIST (prod_kan_id) (PARTITION obj_dok VALUES ('dokončeno'), PARTITION obj_sto VALUES ('stornováno'), PARTITION obj_ned VALUES ('nedokončeno')); Hash dělení Na hodnoty klíčového sloupce je aplikována hashovací funkce. Hash algoritmus zajišťuje rovnoměrné rozloţení řádků mezi oddíly, kaţdý oddíl tak obsahuje přibliţně stejný počet záznamů. Pokud je počet oddílů tabulky změněn, databázový systém automaticky přerozdělí řádky. Tento přístup je výhodný u velkých tabulek pro jejich snadnější správu a u OLTP databází, kde jsou řádky často aktualizovány. Segment je rozdělen do několika částí a aktualizace je prováděna nad kaţdým z nich. Příklad hash dělení prostřednictvím sloupce id objednávky:76 CREATE TABLE objednavky (obj_id NUMBER(10) ,zak_id NUMBER ,datum DATE ,stav_obj NUMBER(10) ,...) 75

Oracle Database Concepts: Partitions, Views, and Other Schema Objects. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-25]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/schemaob.htm#CNCPT88860. 76 Oracle Database Concepts: Partitions, Views, and Other Schema Objects. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-25]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/schemaob.htm#CNCPT88864. 39

PARTITION BY HASH (obj_id) PARTITIONS 2); Kombinované dělení Posledním typem je kombinované dělení, kdy je tabulka rozdělena pomocí některé z výše uvedených metod a jednotlivé oddíly jsou dále děleny jinou metodou na pododdíly.77 Podobně jako tabulky lze rozdělit i indexy78: Lokálně rozdělený index Lokálně dělený index je index nad dělenou tabulkou. Index je rozdělen analogicky s tabulkou. Obsahuje stejný počet oddílů jako tabulka a kaţdý oddíl indexu je spjat s jedním konkrétním oddílem tabulky. Databáze automaticky synchronizuje tyto oddíly mezi sebou. Po vytvoření nového oddílu tabulky je vytvořen i nový oddíl indexu. Lokální dělení indexu sebou přináší obdobné výhody jako u tabulek. Vytvoření lokálně děleného indexu: CREATE INDEX hash_obj_idx ON objednavky(obj_id) LOCAL; Globálně dělený index Globálně rozdělit můţeme pouze index typu B*strom. Takto dělený index je nezávislý na oddílech tabulky a jednotlivé oddíly indexu mohou odkazovat do různých oddílů tabulky. Oddíly indexu lze definovat pouze podle rozsahu. Vytvoření globálně děleného indexu: CREATE INDEX objednavky_dt _idx ON objednavky (datum) GLOBAL PARTITION BY RANGE (datum) (PARTITION p1 VALUES LESS THAN (TO_DATE(20130101, 'YYYYMMDD ')), PARTITION p2 VALUES LESS THAN (TO_DATE(20140101, 'YYYYMMDD')), PARTITION p3 VALUES LESS THAN (TO_DATE(20150101, 'YYYYMMDD')), PARTITION p4 VALUES LESS THAN (MAXVALUE)); Eliminace oddílů Optimalizátor je schopen při vyhodnocování dotazu určité oddíly vypustit. K nalezení poţadovaných řádků je procházen jen konkrétní oddíl tabulky nebo indexu, coţ výrazně omezí počet V/V. Mějme, ale na paměti, ţe tato schopnost bude fungovat pouze u dotazů přes podmínku klíče oddílu, jen tak bude optimalizátor schopen vyhodnotit, ve kterém z oddílů se hledaná data nacházejí. V opačném případě, můţeme rozdělením na oddíly výkon spíše 77

Tamtéţ Oracle Database Concepts: Partitions, Views, and Other Schema Objects. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-25]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/schemaob.htm#CNCPT1520. 78

40

zhoršit. Dotaz na jakýkoliv jiný sloupec by musel projít všechny oddíly objektu, tedy několik oddělených struktur. Z hlediska výkonu je tato funkcionalita eliminace oddílů hlavním důvodem pro jejich pouţívání.79

3.6 Komprese Komprese pouţívá algoritmus, který vyhledává opakující se hodnoty a ty ukládá pouze jedenkrát v rámci bloku a ne při kaţdém jejich výskytu. Komprimovat je moţné indexy i tabulky, ale kaţdý z těchto přístupů uplatníme v jiné situaci.

3.6.1 Komprese indexu Pomocí komprese indexu jsou identifikovány části klíče, které se opakují. Tyto klíče jsou uloţeny pouze jednou v rámci listového bloku. Databáze je tak schopna do bloku uloţit více řádků neţ v nekomprimovaném indexu. Komprimovat lze pouze indexy typu B*strom a indexově orientované tabulky. Výhody komprese indexu jsou tyto:80 Úspora místa na disku Sniţuje mnoţství V/V operací Efektivnější vyuţití vyrovnávací mezipaměti. Do mezipaměti je ukládáno méně bloků. Moţné nevýhody komprese indexu:81 Komprimovaný index umístí do kaţdého bloku více řádkových poloţek, čímţ se zvyšuje riziko kolize mezi souběţnými relacemi. Struktura komprimovaného indexu je sloţitější, jeho zpracování vyţaduje nepatrně více času procesoru. Příklad vytvoření komprimovaného indexu:

79

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 205-206. ISBN 80251-0569-5. 80 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 458-469. ISBN 80251-0569-5. 81 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 458-469. ISBN 80251-0569-5. 41

CREATE INDEX compr_obj_idx ON objednavky(zak_id) COMPRESS 1;

3.6.2 Komprese tabulky Komprimace tabulky pracuje na úrovni slučování bloků, nikoliv řádků jak je tomu u indexu. Kdyţ je vytvářen blok, jsou vyhledány všechny opakující se hodnoty ve všech sloupcích a řádcích daného bloku.82 Komprimovat lze celé tabulkové prostory, tabulky, oddíly a pododdíly. Oracle nabízí dva typy komprese tabulek, základní a pokročilý. Základní komprese Základní komprese tabulky je vhodná pouze v prostředích, kde nedochází k častým změnám. Velmi časté aktualizace tabulky by vedly k její dekompresi. Toto je také hlavní rozdíl mezi komprimovaným indexem a tabulkou. Komprimovaný index pracuje poměrně efektivně i u systémů s častými změnami tabulky. Komprese tabulek je také vhodná pouze pro hromadné operace. Běţné příkazy INSERT a UPDATE data nekomprimují, je třeba pouţít příkazy jako: CREATE TABLE AS SELECT, INSERT /*+ APPEND */ (přímé vloţení), ALTER TABLE MOVE atd. Z předešle uvedených důvodů se základní komprimace pouţívá zejména u datových skladů.83 Příklad vytvoření tabulky se základní kompresí: CREATE TABLE objednavky ... ROW STORE COMPRESS BASIC; Pokročilá komprese řádků Tento typ komprese řeší nepraktičnost pouţití základní komprese v prostředí OLTP s častými změnami tabulky. Všechny sloupce jednoho řádku jsou uloţeny pohromadě a stejně tak sloupce následujícího řádku atd. Databáze nahradí duplicitní hodnoty krátkými odkazy na značku tabulky uloţenou na začátku bloku.84 Příklad vytvoření tabulky s pokročilou kompresí: CREATE TABLE objednavky ... ROW STORE COMPRESS ADVANCED;

82

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 465. ISBN 80-2510569-5. 83 Tamtéţ 84 Oracle Database Concepts: Tables and Table Clusters. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 1993, 2014 [cit. 2014-05-27]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e17633/tablecls.htm#CNCPT1132. 42

3.7 Statistiky Statistiky jsou klíčovým zdrojem informací pro optimalizátor Oracle. Na jejich základě určuje náklady na provedení konkrétního SQL příkazu. Existuje několik typů statistik:85 Tabulkové statistiky – obsahují informace o počtu řádků (důleţité pro výpočet kardinality), počet bloků, průměrnou délku řádků apod. Sloupcové statistiky – obsahují počet neduplicitních hodnot ve sloupci, počet NULL hodnot, histogramy, rozšířené statistiky. Indexové statistiky – obsahují počet listových bloků, počet úrovní, index cluster faktor86. Systémové statistiky – výkon a vyuţití V/V zařízení a CPU Automatický sběr statistik Databázový systém Oracle umoţňuje automatický sběr statistik voláním procedury DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC.

Tento

sběr

statistik

je

součástí automatizované údrţby infrastruktury zvané AutoTask. Ve výchozím nastavení je automatický sběr statistik povolen, případně jej lze povolit nebo zakázat nastavením balíku DBMS_AUTO_TASK_ADMIN:87 BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE (DISABLE) ( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL ) END; Sbírány jsou primárně statistiky nad objekty, které doposud ţádné nemají anebo jsou zastaralé z důvodu změny objektu. Za výraznou změnu je přitom povaţována úprava, přidání či

85

Oracle Database SQL Tuning Guide: Optimizer Statistics Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-27]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_statscon.htm#TGSQL336. 86 Procento přistupovaných bloků. Na základě tohoto údaje se optimalizátor rozhoduje, zda zvolí úplné procházení tabulky, i kdyţ nad dotazovaným sloupcem existuje index. 87 HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 198. ISBN 978-0-13-701195-7. 43

smazání více jak 10% řádků tabulky. Aby databáze sbírala informace o změnách objektů, je třeba nastavit parametr STATISTICS_LEVEL na hodnotu TYPICAL (výchozí) anebo ALL. Ruční sběr statistik V případě, ţe je automatický sběr zakázán nebo momentálně potřebujeme statistiky určitého objektu aktualizovat, lze pouţít ruční sběr statistik. Tento je prováděn voláním procedur balíku DBMS_STAT:88 GATHER_INDEX_STATS – indexové statistiky. GATHER_TABLE_STATS – tabulkové statistiky. GATHER_SCHEMA_STATS

–

statistiky

pro

všechny

objekty

daného

databázového schéma. GATHER_DICTIONARY_STATS – statistiky pro všechna databázová schéma GATHER_DATABASE_STATS – statistiky pro celou databázi Sběr statistik nad rozsáhlými objekty bývá často velmi nákladnou operací. Databáze provádí operace jako úplné procházení tabulky a třídění. Tyto náklady je moţné sníţit tzv. vzorkováním. Vzorkování říká databázi, kolik procent řádků má být pouţito pro získání dostatečných statistik. Toto se nastavuje pomocí parametru ESTIMATE_PERCENT. V případě, ţe nastavená hodnota parametru vrací příliš malý vzorek dat, databáze automaticky tuto hodnotu zvýší. Oracle doporučuje pouţít pro nastavení parametru hodnotu DBMS_STATS.AUTO_SAMPLE_SIZE, díky které si databáze sama určí velikost vzorku dat pro vytvoření dostatečných statistik:89 EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('název tabulky',DBMS_STATS.AUTO_SAMPLE_SIZE);

88

Oracle Database SQL Tuning Guide: Optimizer Statistics Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-27]. Dostupné z: http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i42181. 89 Oracle Database SQL Tuning Guide: Optimizer Statistics Concepts. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-27]. Dostupné z: http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i42181. 44

4

SQL dotazy

4.1 Zpracování příkazů SQL příkazy můţeme rozdělit do dvou základních skupin:90 DDL (Data Definition Language) – slouţí pro vytváření, mazání a změnu vlastností databázových objektů jako jsou tabulky, indexy, pohledy, procedury, uţivatelé apod. DML (Data Manipulation Language) – s pomocí příkazů DML přistupujeme k datům nebo provádíme jejich vkládání, úpravu a mazaní. Jedná se o příkazy typu: INSERT, SELECT, DELETE, UPDATE, MERGE apod. Algoritmus zpracování SQL příkazů je patrný z obrázku 4-1:

Obrázek 4-1: Jednotlivé fáze zpracování SQL příkazů91

90

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 270. ISBN 80-2510569-5. 91 Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-30]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL175. 45

Zpracování SQL příkazů zahrnuje tyto čtyři kroky:92 Analýza Optimalizace Generování řádkového zdroje Provedení příkazu

4.1.1 Analýza Analýza je první fází zpracování příkazů. Dotaz je rozloţen na části v datové struktuře, které mohou být zpracovány jinými rutinami. Po spuštění příkazu je volán kurzor. Kurzorem označujeme privátní SQL oblast, která obsahuje informace o příkazu a další související s jeho zpracováním. Privátní oblast je součástí PGA paměti. Zavřením kurzoru končí zpracování příkazu. Analýza příkazů DML a DDL neprobíhá zcela stejným způsobem. Během volání kurzoru jsou prováděny dílčí analýzy. Dále si popíšeme jednotlivé kroky analýzy. Syntaktická analýza Kontroluje, zda je dotaz platným příkazem SQL, coţ znamená, jestli je dotaz napsán dle pravidel jazyka SQL. Příkaz je posuzován na základě jeho charakteru a je zkoumána přítomnost poţadovaných klauzulí, správnost jejich pořadí apod. Sémantická analýza Posuzuje správnost dotazu v souladu s informacemi z datového slovníku. Existuje objekt, kterému přistupujeme? Nachází se poţadovaný sloupec v tabulce? Máme potřebná práva na objekt. Jsou nějaké sloupce nejednoznačně určeny? Kontrola sdíleného fondu O sdílené oblasti paměti SGA jsme jiţ mluvili v kapitole 2.3.1. Databázový systém prohledává tuto oblast, zda se v ní jiţ nevyskytuje právě vykonávaný SQL příkaz. Oracle pouţívá k identifikaci příkazů hash algoritmus, který přidělí kaţdému příkazu SQL_ID. Tento identifikátor je pevně daný v rámci konkrétní verze Oracle databáze, takţe stejný SQL 92

Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-30]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL175. 46

příkaz má totoţné i SQL_ID ve všech instancích. Při hledání ve sdílené oblasti jsou hledány právě takové příkazy, které mají tuto hodnotu stejnou. Kontrola sdíleného fondu není prováděna u DDL příkazů, ty jsou vţdy analyzovány kompletně a nikdy nejsou znovu pouţity. Pokud je ve sdílené oblasti nalezen příkaz se stejným SQL_ID, databáze provede pouze tzv. jemnou analýzu, v opačném případě je provedena úplná analýza.93 Úplná analýza – jsou provedeny všechny následující fáze zpracování jako optimalizace, generování řádkového zdroje a vykonání příkazu. V průběhu úplné analýzy je mnohokrát přistupována paměť knihoven a datového slovníku. Pro přístup k těmto oblastem pouţívá databáze serializační zařízení zvané latch, které chrání sdílené datové struktury proti současnému přístupu. Pouţití tohoto kontrolního mechanismu však zvyšuje dobu odezvy dotazu a sniţuje jeho konkurence schopnost.94 Jemná analýza – je vynechán krok optimalizace a generování řádkového zdroje.

4.1.2 Optimalizace Po úplné analýze následuje fáze optimalizace, kterou jsme si popisovali v kapitole 2.3.3. Tento krok je velmi nákladný a často můţe trvat déle neţ vlastní provedení dotazu. Optimalizace není nikdy prováděna u příkazů DDL, ledaţe by obsahovaly příkaz DML, např. CREATE TABLE AS SELECT.... Kaţdý příkaz je optimalizován, alespoň jednou.95

4.1.3 Generování řádkového zdroje Generátor je součástí databázového systému Oracle, který zpracovává exekuční plán vybraný optimalizátorem. Tento plán je přeměněn na datovou strukturu pouţitelnou pro ostatní části databáze. Výstupem generátoru je strom řádkových zdrojů (viz obrázek 4-2). Zdrojem můţe

93

Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-30]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL181. 94 Tamtéţ 95 Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-30]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL181. 47

být tabulka, pohled nebo výsledek operace spojení a seskupení. Strom řádkových zdrojů obsahuje následující informace: 96 Pořadí tabulek zpracovávaných dotazem Přístupovou metodu pro kaţdou tabulku Metodu spojení pro spojované tabulky Operace s daty jako filtraci, řazení a agregaci

Obrázek 4-2: Strom řádkových zdrojů97

4.1.4 Provedení příkazu Tento krok je jediným povinným v průběhu celého zpracování SQL příkazů. Databázový systém zpracovává jednotlivé uzly stromu řádkových zdrojů. Mezi uzly panuje vzájemná hierarchie. Pořadí kroků zpracování je obrácené, tudíţ ho čteme ho ze spodu. Jednotlivé kroky 96

Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-11]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL184. 97 Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-11]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL184. 48

zpracování vracejí sadu řádků, která můţe být pouţita jako vstup některým z dalších kroků. Například krok číslo 2 z obrázku 4–2 pouţívá data vrácená kroky 3 a 5 a provádí nad nimi operaci spojení, výsledek je pak předán kroku 1 jako další vstup. Během vykonávání příkazu načítá databázový systém data z fyzického úloţiště do vnitřní paměti, pokud v ní jiţ nejsou. Dále optimalizátor pouţívá latch a zámky k zachování integrity a zaznamenává všechny změny uskutečněné během provádění příkazu.98 Konzistentní čtení Konzistentní čtení je mechanismus, kterým databáze zajišťuje, ţe všechny datové bloky zpracovávané DML příkazy jsou v určitém časovém okamţiku navzájem konzistentní. Předchází tak situaci, kdy je příkazem načítáno velké mnoţství bloků, které jsou však v tentýţ čas modifikovány jinou relací. Takto je zajištěno, ţe příkaz vrací data platná k okamţiku jeho spuštění.

4.2 Přístupové cesty Přístupové cesty jsou metody, jejichţ prostřednictvím získává databáze Oracle poţadovaná data. Výběr přístupové cesty je ovlivněn strukturou přistupovaného objektu. Tabulka ukazuje pouţití různých přístupových metod v závislosti na objektu. Access Path

HeapOrganized Tables

Full Table Scans

x

Table Access by Rowid

x

Sample Table Scans

x

B-Tree Indexes and IOTs

Index Unique Scans

x

Index Range Scans

x

Index Full Scans

x

Index Fast Full Scans

x

Index Skip Scans

x

Index Join Scans

x

98

Bitmap Indexes

Table Clusters

Oracle Database SQL Tuning Guide: SQL Processing. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-31]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_sqlproc.htm#TGSQL188. 49

Bitmap Index Single Value

x

Bitmap Index Range Scans

x

Bitmap Merge

x

Cluster Scans

x

Hash Scans

x Tabulka 4-1: Datové struktury a přístupové cesty99

Úplné procházení tabulky (Full Table Scan) Úplné procházení tabulky postupně přečte všechny bloky v daném segmentu, dokud nenalezne všechny poţadované řádky. Obyčejně je na tuto metodu nahlíţeno negativně, co se výkonu týče, avšak v určitých situacích můţe být rychlejší neţ přístup pomocí indexu. Optimalizátor se většinou rozhodne pro úplné procházení za těchto podmínek:100 Nad tabulkou není vytvořen potřebný index. Index je součástí predikátu dotazu, ale je na něj pouţita funkce (v případě, ţe se nejedná o funkční index). U dotazu typu SELECT COUNT(*), kde indexovaný sloupec obsahuje NULL hodnoty (NULL hodnoty nejsou indexovány). V predikátu dotazu není pouţita vůdčí část indexu. Např. pokud vytvoříme index nad dvěma sloupci dohromady, ale je dotazován pouze druhý sloupec. Dotaz je neselektivní. Optimalizátor vyhodnotí, ţe musí být načtena převáţná část tabulky. Statistiky jsou zastaralé. Např. optimalizátor má nesprávné informace o velikosti tabulky. Tabulka je malá. Je zbytečné pouţití indexu. Je pouţit hint FULL

99

Oracle Database SQL Tuning Guide: Optimizer Access Paths. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-31]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optop.htm#TGSQL94943. 100 Oracle Database SQL Tuning Guide: Optimizer Access Paths. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-05-31]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optop.htm#TGSQL231. 50

Přístup přes ROWID ROWID je přesná adresace řádku tabulky v datovém bloku. Jedná se o nejrychlejší cestu k nalezení samostatného řádku. Tento přístup je většinou pouţit po projití indexu anebo pokud je ROWID získáno přímo z predikátu dotazu. Procházení vzorku tabulky Je vybrána náhodná část řádků tabulky. Pouţívá se, kdyţ je za klauzulí WHERE specifikováno SAMPLE (procenta), či SAMPLE BLOCK (procenta). Procházení jedinečného indexu Optimalizátor rozpozná, ţe indexované sloupce jsou jedinečné. Po prohledání indexu bude vrácen nanejvýše jeden řádek. Je pouţito v případě, ţe predikát dotazu obsahuje podmínku rovnosti na sloupce jedinečného indexu. Procházení rozsahů indexu Optimalizátor očekává vrácení ţádného, jednoho, dvou nebo více řádků. Rozsahy mohou být prohledány jedním či dvěma směry. Obvykle je index čten ve vzestupném pořadí, ale můţe být pouţito i sestupné. Procházení rozsahů indexu optimalizátor obvykle zvaţuje, pokud podmínka dotazu obsahuje následující operátory: =, <, >. Úplné procházení indexu Jsou procházeny všechny listové bloky indexu, ale pouze tolik bloků větví kolik je potřeba k nalezení prvního listového bloku.101 Pouţití úplného procházení indexu: Pokud je součástí dotazu klauzule ORDER BY nad indexovaným sloupcem s nenullovými hodnotami. Všechny sloupce tabulky jsou součástí indexu i dotazu a alespoň jeden indexovaný sloupec je bez NULL hodnot. Sloupec indexu je obsaţen v predikátu dotazu

101

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 494. ISBN 80-2510569-5. 51

Úplné rychlé procházení indexu Načítá všechny bloky indexu, včetně bloků větví. Data nejsou čtena v seřazeném pořadí. Nepřistupuje do tabulky, ale pouze do indexu. Tato metoda má uplatnění v situaci, kdy jsou přistupovány jen atributy obsaţené v indexu. Index Skip Scan K tomuto procházení můţe dojít u sloţených indexů, kde první sloupec indexu není zahrnut v podmínce dotazu anebo obsahuje velmi mnoho duplicitních hodnot, kdeţto druhý sloupec jich má méně. Spojení indexů Spojení indexů se pouţívá, pokud indexy nad tabulkou zahrnují všechny dotazované sloupce. Databáze nebude přistupovat k tabulce, ale pouţije hash spojení indexů, za podmínky, ţe tato celá operace vyjde levněji neţ procházení tabulky a jednoho indexu. Bitmap Index Single Value Přístupová metoda vyuţívající bitmapový index k nalezení konkrétní hodnoty klíče. Bitmap Index Range Scans Přístupová metoda vyuţívající bitmapový index k nalezení rozsahu hodnot klíče indexu. Bitmap Merge Spojuje několik bitmap do jedné. Většinou se jedná o bitmapy vygenerované procházením rozsahů. Clusterové procházení Vrací všechny řádky tabulky se stejnou hodnotou klíče jako index clusteru. Hash procházení Databáze pouţije hash hodnotu klíče tabulky k nalezení všech poţadovaných řádků.

4.3 Principy spojení Spojení kombinuje výstup ze dvou řádkových zdrojů a vrací jeden řádkový set jako výsledek. Spojovat můţeme tabulky a pohledy. Vztah mezi spojovanými objekty je definován spojovací podmínkou. Pokud není podmínka určena, dojde k tzv. kartézskému součinu, kdy se spojí 52

všechny řádky z jednoho zdroje se všemi řádky z druhého zdroje. Databázový systém pouţívá několik metod k provedení operace spojení. Hnízděné cykly (Nested loops join) Jedná se o nejběţnější metodu spojení. Jedna tabulka je určena jako vnější zdroj a druhá jako vnitřní. Pro kaţdý řádek vnější tabulky, splňující podmínku dotazu, jsou vyhledány všechny řádky z vnitřní tabulky, které se shodují na základě podmínky spojení. V případě existence indexu, můţe být pouţit pro přístup k řádkům vnitřního zdroje. Hnízděné cykly jsou uţitečné za následujících podmínek:102 Je spojováno malé mnoţství dat či je nastaven cíl optimalizátoru na FIRST_ROW u spojení velkého mnoţství řádků. Spojovací podmínka je efektivní metodou jak přistupovat k datům ve vnitřní tabulce. Hash spojení Pouţívá se hlavně u spojování velkého objemu dat. Databáze vybere menší z obou řádkových setů a z klíče spojení vytvoří hash tabulku, kterou uloţí do paměti. V této tabulce je lokace řádků specifikována skrze hash funkci. Následně jsou procházeny řádky větší tabulky a za pomoci hash tabulky jsou vybrány řádky odpovídající podmínce spojení. Protoţe je hash tabulka uloţena do paměťové oblasti PGA, není zapotřebí pouţití latch mechanismu (viz kapitola 4.1.1), coţ sniţuje mnoţství logických V/V operací. V případě, ţe se řádky menší z tabulek nevejdou do paměti všechny, je tabulka rozdělena na oddíly a ty jsou postupně spojovány. Tento jev má za následek značnou spotřebu oblasti PGA určenou pro řazení dat a také mnoho čtení z dočasného tabulkového prostoru. Hash spojení je optimalizátorem zvaţováno za těchto podmínek:103 Je spojováno relativně velké mnoţství dat. Spojení je definováno přes podmínku rovnosti.

102

Oracle Database SQL Tuning Guide: Joins. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_join.htm#TGSQL244. 103 Tamtéţ 53

Setřídění – slévání (Sort – merge join) Oba řádkové zdroje jsou nejdříve setříděny dle klíče z podmínky spojení a poté sloučeny. Setřídění – slévání nachází uplatnění tam kde:104 Spojení není definováno přes podmínku rovnosti, namísto toho jsou pouţity operátory jako <, <=, >, >=. V tomto případě totiţ nemůţe být pouţito hash spojení. Třídění je vyţadováno i jinou operací. Optimalizátor můţe shledat, ţe celková cena operací bude niţší s pouţitím setřídění – slévání.

Obrázek 4-3: Setřídění – slévání105

4.4 Exekuční plán O exekučním plánu jsme jiţ několikrát zmínili v předchozích kapitolách. Je to vlastně soupis všech kroků, které databáze musí projít, aby mohl být vykonán SQL příkaz. Základem plánu je strom řádkových zdrojů (viz kapitola 4.1.3). Kromě informací jiţ obsaţených v stromu řádkových zdrojů, najdeme v exekučním plánu i další informace:106 Cenu operace a kardinalitu kaţdé z operací plánu Přistupované oddíly 104

Oracle Database SQL Tuning Guide: Joins. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_join.htm#TGSQL94689. 105 Oracle Database SQL Tuning Guide: Joins. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_join.htm#TGSQL94690. 106 Oracle Database SQL Tuning Guide: Generating and Displaying Execution Plans. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL274. 54

Paralelní zpracování Čtením exekučního plánu zjistíme pro jaké přístupové metody a metody spojení se optimalizátor rozhodl a proč tak učinil. Plán dotazu je klíčovým zdrojem informací v procesu ladění výkonu SQL příkazů. Dále si popíšeme nástroje umoţňující zobrazení exekučního plánu.

4.4.1 Nástroje pro generování a zobrazení plánu Explain plan EXPLAIN PLAN je příkaz, který vygeneruje plán dotazu vybraný optimalizátorem pro příkazy SELECT, INSERT, UPDATE a DETELE. Je nutné si uvědomit, ţe tento příkaz nám zobrazí plán, tak jak by vypadal, kdyby ke spuštění příkazu došlo nyní. Ve skutečnosti mohl být anebo bude optimalizátorem vybrán plán jiný. Rozhodnutí optimalizátoru vţdy závisí na okolnostech jako nastavení relace, informace ze statistik apod. Ty se mohou v čase měnit, tudíţ i plán dotazu pro jeden a ten samý příkaz můţe nabývat odlišných podob.107 Spuštěním příkazu EXPLAIN PLAN nedojde k vlastnímu provedení SQL dotazu. Optimalizátor pouze vybere plán dotazu, který je vloţen do určené tabulky. Pro zobrazení plánu, pak tuto tabulku dotazujeme. Syntaxe příkazu je následující:108 EXPLAIN PLAN [SET STATEMENT_ID = 'statement_id'] [INTO table_name] FOR sql_statement Proměnná statement_id nám umoţňuje přiřadit plánu jedinečný identifikátor, díky tomu můţeme ukládat v jedné tabulce plány pro více dotazů. Table_name určuje název tabulky, do které chceme plán uloţit. V případě, ţe tabulku nespecifikujeme, vkládá příkaz EXPLAIN PLAN výsledky do tabulky PLAN_TABLE. Tato tabulka je globální dočasnou tabulkou dostupnou pro všechny uţivatele. Najdeme v ní však pouze plány dotazů aktuální relace. Mimo tabulku PLAN_TABLE můţeme pouţít pro uloţení plánu jinou tabulku, ta však musí mít stejnou strukturu. Takovou tabulku lze vytvořit

107

KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 103. ISBN 80-2510569-5. 108 HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 36. ISBN 978-0-13-701195-7. 55

spuštěním skriptu catplan.sql, který je součástí softwaru databázového systému Oracle. Umístění skriptu závisí na operačním systému.109 Poslední proměnnou je sql_statement, coţ je samotný SQL příkaz pro který chceme vygenerovat plán a je to také jediná povinná proměnná příkazu EXPLAIN PLAN. Analyzovaný příkaz musí být správný z hlediska syntaxe a sémantiky. Jako nejběţnější způsob zobrazení výsledků příkazu EXPLAIN PLAN se jeví pouţití funkce DBMS_XPLAN.DISPLAY. Funkci lze volat s různými parametry:110 Název tabulky, kde je uloţen plán dotazu, v případě, ţe se nejedná o tabulku PLAN_TABLE. ID příkazu, pokud bylo definováno při spouštění EXPLAIN PLAN. Detail zobrazovaného plánu. Na výběr máme ze tří moţností:111 o BASIC – zobrazuje pouze ID, název operace a název přistupovaného objektu. o TYPICAL – kromě předešle uvedených informací, můţeme vidět i cenu, kardinalitu, odhadovaný čas procesoru a dotazu atd., to vše pro kaţdou operaci samostatně. Dále zde najdeme i predikáty vyhodnocované jednotlivými operacemi. o ALL – informace z pohledu TYPICAL jsou doplněny o aliasy objektů a názvy bloků přistupovaných operací. Příklad volání DBMS_XPLAN.DISPLAY: SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('table_name', 'statement_id','TYPICAL')) V$SQL_PLAN pohledy Jako alternativa k příkazu EXPLAIN PLAN se nabízejí V$SQL_PLAN pohledy. 109

Oracle Database SQL Tuning Guide: Generating and Displaying Execution Plans. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL94701. 110 Oracle Database SQL Tuning Guide: Generating and Displaying Execution Plans. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL275. 111 COLGAN, Maria. The Oracle Optimizer Explain the Explain Plan. White Paper Oracle [online]. 2011, s. 5-6 [cit. 2014-06-01]. Dostupné z: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explainthe-explain-plan-052011-393674.pdf 56

V$SQL_PLAN pohled udrţuje exekuční plány všech příkazů uloţených ve sdílené paměti. Jeho struktura je podobná jako u tabulky PLAN_TABLE. V$SQL_PLAN_STATISTIC obsahuje informace o počtu řádků vrácených kaţdou z operací plánu a uplynulém čase. V$SQL_PLAN_STATISTIC obsahuje informace z předchozích dvou pohledů plus informace o vyuţití paměti z pohledu V$SQL_WORKAREA Funkce DISPLAY_CURSOR balíku DBMS_XPLAN umoţňuje zobrazit plán dotazovaný z V$SQL_PLAN pohledu ve stejném formátu jako výstup z tabulky PLAN_TABLE. Podmínkou je znalost parametrů SQL_ID a CHILD_NUMBER. Pokud existují v pohledu dva naprosto totoţné příkazy, ale s jiným exekučním plánem, budou mít stejné SQL_ID, ale jinou hodnotu CHILD_NUMBER.112 Autotrace Funkce Autotrace je vestavěnou funkcí nástroje SQL*Plus. Plán dotazu je generován po skutečném provedení dotazu, coţ je rozdíl oproti funkci EXPLAIN PLAN. Výstupem Autotrace můţe být exekuční plán i statistické informace jako počet čtení z disku a vnitřní paměti. Sestavy jsou vygenerovány po spuštění všech DML příkazů. Chování Atotrace sestav lze ovlivnit nastavením několika parametrů:113 SET AUTOTRACE OFF – výchozí nastavení, nejsou generovány ţádné sestavy. SET AUTOTRACE ON EXPLAIN – je zobrazován pouze průběh výpočtu optimalizátoru. SET AUTOTRACE ON STATISTICS – jsou zobrazovány pouze statistické informace týkající se provádění dotazů. SET AUTOTRACE ON – jsou zobrazeny všechny informace z předešlých dvou konfigurací.

112

HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 41. ISBN 978-0-13-701195-7. 113 KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007, s. 115. ISBN 80-2510569-5. 57

SET AUTOTRACE TRACEONLY – podobně jako příkaz SET AUTOTRACE ON, ale vynecháno zobrazení výstupu uţivatelského dotazu. Výhodné u dotazů, vracející velké mnoţství řádků. SET AUTOTRACE TRACEONLY STATISTICS – stejné jako předchozí, ale jsou zobrazeny pouze statistické informace. SET AUTOTRACE TRACEONLY EXPLAIN – je zobrazen pouze plán dotazu. Dotaz SELECT není navíc ani proveden, ale jen analyzován.

4.4.2 Čtení exekučního plánu Z předchozí kapitoly uţ víme jak exekuční plán zobrazit i jaké informace nám můţe nabídnout. Neméně důleţité je plán správně interpretovat, jen tak lze porozumět, jak se optimalizátor rozhodl daný dotaz provést. Vygenerujme a zobrazme si nyní exekuční plán ve výchozím módu TYPICAL pro následující příkaz:114 EXPLAIN PLAN FOR SELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103 AND e.job_id = j.job_id AND e.department_id = d.department_id; SELECT plan_table_output FROM table(dbms_xplan.display); Zobrazený plán bude vypadat takto: ----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)| | 1 | NESTED LOOPS | | 3 | 189 | 10 (10)| | 2 | NESTED LOOPS | | 3 | 141 | 7 (15)| |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)| | 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)| |* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)| |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | | ----------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("E"."EMPLOYEE_ID"<103) 5 - access("E"."JOB_ID"="J"."JOB_ID") 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

114

Oracle Database SQL Tuning Guide: Generating and Displaying Execution Plans. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL94708. 58

Tabulka 4-2: Ukázka exekučního plánu115

Z výše uvedeného plánu je vidět, ţe operace nejdou za sebou v pořadí, v jakém jsou vykonávány. Mezi operacemi panuje hierarchie, která je vyjádřena odsazením jednotlivých řádků. Nejvíce odsazené operace jsou prováděny jako první a po nich následují odsazené o úroveň méně atd. Jestliţe se více operací nachází na stejné úrovni, jsou provedeny od shora dolů. V našem případě jsou operace prováděny takto: Krok 5 – nejprve jsou vyhledány řádky z tabulky JOBS metodou INDEX UNIQUE SCAN se shodným klíčem JOB_ID jako v tabulce EMPLOYEES. Krok 3 – je projita celá tabulka EMPLOYEES a vybrány řádky vyhovující podmínce EMPLOYEE_ID < 103. Krok 4 – jsou zpřístupněny řádky z tabulky JOBS vyhledané indexem. Krok 7 – pomocí metody INDEX UNIQUE SCAN je prohledána tabulka DEPARTMENTS se shodným klíčem DEPARTMENT_ID jako v tabulce EMPLOYEES. Krok 2 – řádkové sety z kroků 3 a 4 jsou spojeny metodou hnízděných cyklů. Krok 6 – řádky vyhledané v kroku 7 jsou zpřístupněny. Krok 1 – řádkové sety z kroků 2 a 6 jsou spojeny metodou hnízděných cyklů. Krok 0 – výsledná mnoţina řádků z kroku 1 je vrácena jako výsledek dotazu.

4.5 Hinty Pouţitím hintů lze předat optimalizátoru instrukce jaké zvolit pořadí spojení, metodu přístupu k datům, jaký pouţít index apod. Optimalizátor dané doporučení provede pouze za předpokladu, ţe je to moţné. Nelze například pouţít index, který neexistuje nebo přístupovou metodu, která není dostupná. Pokud mu v tom nic nebrání, optimalizátor se řídí hintem i za předpokladu, ţe tento přístup bude mít za následek výrazné zhoršení výkonu. Z tohoto 115

Oracle Database SQL Tuning Guide: Generating and Displaying Execution Plans. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-01]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL94708. 59

důvodu bychom měli pouţití hintů pečlivě zváţit. Obecně je vhodnější nechat dělat optimalizátor svou práci a hinty nepouţívat. Pokud je optimalizátor správně nastaven a má k dispozici aktuální statistiky, rozhodne sám nejlépe jak dotaz provést. Hinty jsou součástí příkazu a zapisují se jako komentář ve tvaru: /* + HINT (název_tabulky) */ *. Většinou jsou umístěny hned za první klauzulí SQL příkazu, obvykle (SELECT, INSERT, MERGE, DELETE, UPDATE). Nejběţněji pouţívanými hinty jsou tyto:116 ALL_ROWS – optimalizátor pouţije cíl ALL_ROWS. FIRST_ROWS (N) – optimalizátor pouţije cíl FIRST_ROWS. APPEND – přímé vloţení řádků příkazem INSERT. CACHE (název_tabulky) – při provádění úplného procházení tabulky umístí databáze bloky tabulky do vyrovnávací paměti. Opakem je hint NOCACHE. FACT (název_tabulky) – pouţívá se u schématu hvězda. Optimalizátor povaţuje danou tabulku za tabulku faktů. FULL (název_tabulky) – pro přístup do tabulky je pouţito úplné procházení, i navzdory existujícímu indexu. HASH (název_tabulky) – je pouţito hash procházení pro přístup do tabulky. Funguje pouze u hash clusterů. INDEX (název_tabulky [název indexu]) – tabulka je procházena za pomoci indexu. Pokud index, specifikovaný v hintu neexistuje, je pouţit index s nejniţší cenou. Opakem je NO_INDEX. INDEX_COMBINE (název_tabulky název indexu název indexu...) – pro přístup do tabulky je pouţito více indexů. INDEX_SS – pouţije se přístupová metoda Index Skip Scan. LEADING (název_tabulky...) – určuje pořadí spojení specifikovaných tabulek. USE_HASH (název_tabulky) – u dané tabulky je pouţito hash spojení. 116

HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010, s. 212 - 213. ISBN 978-0-13-701195-7. 60

USE_MERGE (název_tabulky) – u dané tabulky je pouţito setříděné – slévané spojení. USE_NL (název_tabulky) – u dané tabulky jsou pro spojení pouţity hnízděné cykly.

4.6 SQL Plan Management SQL Plan Management je mechanismus, který optimalizátor pouţívá ke správě plánů dotazu. Obsahuje informace jako identifikátor dotazu, vázané proměnné, sadu hintů a prostředí. Tímto je zajištěno, ţe pro opakované provedení konkrétního dotazu optimalizátor pouţije pouze jiţ ověřený plán. Hlavním úkolem plan managementu je zajistit, aby nedošlo ke sníţení výkonu SQL dotazu z důvodu změny exekučního plánu. Dalším účelem je hladké přizpůsobení plánu změnám prostředí jako aktuální statistiky, nové indexy apod. V plánu jsou provedeny jen ty změny, které vedou ke zlepšení výkonu. Ke splnění těchto cílů pouţívá plan management nástroj zvaný SQL plan baseline. SQL plan baseline je sada prověřených plánů, které se váţou ke konkrétnímu dotazu a mohou být optimalizátorem vyuţity při jeho opakovaném provedení.

4.6.1 Úložné struktury SQL plan managementu V této kapitole si stručně popíšeme, kam jsou logicky ukládány exekuční plány a další informace, s nimiţ SQL plan management pracuje. SQL Management Base SQL Management Base je součástí datového slovníku a obsahuje informace jako:117 Záznam o SQL příkazu – v případě, ţe je povoleno automatické zachytávání plánů, najdeme zde SQL ID příkazů provedených optimalizátorem v průběhu času. Díky zaznamenanému SQL ID databáze pozná, zda byl příkaz jiţ někdy v minulosti spuštěn. Historie plánů – jedná se o sadu exekučních plánů pro opakované SQL dotazy. V historii plánů jsou obsaţeny jak akceptované plány, coţ znamená, ţe jsou uloţeny v SQL plan baseline, tak neakceptované plány. Všechny plány v historii jsou 117

Oracle Database SQL Tuning Guide: Managing SQL Plan Baselines. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-05]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL630. 61

automaticky nastaveny jako povolené, coţ znamená, ţe mohou být pouţity optimalizátorem. Ručně lze konkrétní plány zakázat. Vybrané akceptované plány uloţené v SQL plan baseline můţeme nastavit jako výchozí. Optimalizátor pak zvaţuje pouze tyto plány a ostatní ignoruje. SQL profiles (viz následující kapitola) SQL záplaty

4.6.2 Činnosti SQL plan managementu SQL plan management se skládá ze tří hlavních činností:118 Zachycení plánu Výběr plánu Rozvoj plánu Zachycení plánu Probíhá buď automaticky anebo manuálně. Výsledky jsou ukládány do SQL Management Base.

Pro

automatický

sběr

plánů

je

třeba

nastavit

parametr

optimalizátoru

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES na hodnotu TRUE. Kdyţ optimalizátor provádí opakovaný dotaz, zjišťuje, jestli pro daný dotaz existuje záznam v SQL plan baseline. Pokud ano, porovná aktuální plán dotazu s plánem v plan baseline, v případě, ţe je nalezen shodný plán je tento plán proveden. V opačném případě optimalizátor pouţije k provedení plán uloţený v SQL plan baseline a aktuální plán uloţí jako neakceptovaný do historie plánů. Jestliţe spouštěný dotaz nemá záznam v SQL plan baseline, optimalizátor uloţí plán dotazu jako akceptovaný a nastaví jej jako výchozí. Ruční zachycení plánu probíhá nahráním existujících plánů do SQL plan baseline. Plány můţeme nahrávat z různých zdrojů jako např. sdílená oblast (viz kapitola 2.3.1), SQL tuning

118

Oracle Database SQL Tuning Guide: Managing SQL Plan Baselines. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-05]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL784. 62

set119, stored outline120 atd. Ručně zachycené plány jsou vţdy označeny jako ověřené, protoţe optimalizátor předpokládá, ţe jejich výkon je uspokojivý.

Obrázek 4-4: Algoritmus automatického zachycení plánu121

Výběr plánu Výběr plánu je schopnost optimalizátoru pouţít k provedení dotazu plán uloţený v SQL plan baseline. Postup výběru jsme si popsali v předchozí podkapitole Zachycení plánu. Pro hlubší přehled je nutné zmínit, ţe kdyţ není v SQL plan baseline nalezen shodný plán dotazu, optimalizátor dále zkoumá, zda existují pro daný dotaz nějaké výchozí plány a případně vybere ten levnější z nich. Pokud ţádný plán není nastaven jako výchozí, vybírá se nejlevnější ze všech akceptovaných plánů k danému dotazu. Můţe nastat i situace, kdy jsou stávající plány neproveditelné např. z důvodu smazaného indexu, v takovém případě je proveden aktuální plán dotazu. Akceptované plány uloţené v SQL plan baseline lze zobrazit pomocí funkce balíku DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE. Rozvoj plánu Rozvoj plánu je proces, kdy optimalizátor ověřuje nové plány a přidává je do SQL plan baseline. Tento proces obvykle sestává ze dvou kroků:122

119

SQL tuning set je databázový objekt, ve kterém můţeme ukládat SQL příkazy, včetně jejich plánů, statistik ohledně jejich běhu a přidruţené informace jako schéma apod. 120 Stored outline je sada hintů, pro konkrétní SQL příkaz. Jejich účelem je, aby optimalizátor pouţil pro SQL příkaz vţdy stejný plán. 121 Oracle Database SQL Tuning Guide: Managing SQL Plan Baselines. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-06]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL784. 122 Tamtéţ 63

Ověření, zda je výkon nového plánu přinejmenším stejný, jako plánu uloţeném v SQL plan baseline. Přidání neakceptovaného ověřeného plánu do SQL plan baseline. Většinou jsou tyto kroky prováděny po sobě. Je však moţné nastavit SQL plan management, aby vykonával pouze jeden z nich. Hlavním účelem rozvoje plánu je zjistit, zda nové, ale doposud neověřené plány nevykazují lepší výkon neţ ověřený plán. Výkon plánu dotazu se můţe lišit v závislosti na změně prostředí jako např. vytvoření materializovaného pohledu, indexu nebo oddílů, coţ můţe výkonnost stávajícího ověřeného plánu zhoršit ve srovnání s novými plány. Rozvoj plánu lze řídit skrze funkce balíku DBMS_SPM. Databáze Oracle nabízí automatický i ruční rozvoj plánů.123

4.7 Nástroje pro monitorování a ladění SQL dotazů Kromě jiţ zmíněných hintů a SQL plan managementu, nabízí databázový systém Oracle i další nástroje, jeţ mohou být uţitečné v procesu optimalizace dotazů. Projděme si alespoň stručně některé z nich. Automatic Database Diagnostic Monitor ADDM ADDM je samo-diagnostický nástroj databáze Oracle, který automaticky identifikuje moţnou příčinu problémů s výkonem a navrhuje řešení včetně přínosu navrhovaného řešení. ADDM pouţívá jako zdroj informací Automatic Workload Repository, coţ je infrastruktura zajišťující sběr, údrţbu a pouţití statistik nad databází.124 SQL Tuning Advisor SQL Tuning Advisor je další z věstavěných nástrojů systému Oracle. Jako vstup pouţívá jeden nebo více SQL příkazů a provádí nad nimi tyto analýzy:125 Kontroluje chybějící nebo neaktuální statistiky.

123

Oracle Database SQL Tuning Guide: Managing SQL Plan Baselines. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-07]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL94648. 124 Oracle Database SQL Tuning Guide: Introduction to SQL Tuning. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-07]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_intro.htm#TGSQL120. 125 Tamtéţ 64

Staví SQL profiles, coţ je sada pomocných informací pro daný SQL dotaz. Můţeme je přirovnat např. ke statistikám nad tabulkou. Tyto informace pomáhají optimalizátoru při rozhodovaní o kardinalitě a selektivitě (viz kapitola 2.3.3). Zkoumá, kde by mohla změna přístupových metod zlepšit výkon. Vybírá dotazy s optimálním plánem Výstupem SQL Tuning Advisor je soubor doporučení a očekávaných přínosů, jako např. vytvoření indexu, sběr nových statistik, přepsání dotazu apod. SQL Access Advisor SQL Access Advisor je nástroj, který zvaţuje jaké indexy nebo materializované pohledy je moţné pouţít. Kromě stávajících objektů, radí i jaké nové objekty vytvořit anebo naopak smazat. Jako vstup pouţívá jiţ zmiňovaný Automatic Workload Repository, ale dokáţe vycházet i ze stávající struktury schéma databáze. SQL Access Advisor zvaţuje poměr mezi zvýšením výkonu dotazu a nároky objektu na pouţitý prostor. SQL Access Advisor dokáţe poradit i ohledně horizontálního dělení.126 Dynamické pohledy Dynamické pohledy nám umoţňují monitorovat SQL příkazy v reálném čase. Monitorovány jsou SQL příkazy, které spotřebují alespoň pět vteřin času CPU nebo V/V zařízení. O pouţití pohledů jsme se jiţ zmiňovali v kapitole 4.4.1 v souvislosti se zobrazením exekučního plánu dotazu. Nejdůleţitějšími pohledy pro monitorování SQL příkazů jsou:127 V$SQL_MONITOR – udrţuje informace o spuštěných SQL příkazech napříč relacemi. Kaţdý příkaz zde má svůj záznam. Obsahuje informace jako SQL ID, čas procesoru a V/V zařízení, čas odezvy příkazu, skript příkazu atd. V$SQL – je v podstatě podmnoţinou předešlého pohledu. Základním rozdílem je to, ţe pohled V$SQL udrţuje informaci o posledním spuštění daného SQL příkazu, zatímco V$SQL_MONITOR obsahuje pro kaţdý běh dotazu samostatný řádek. V$SESSION – obsahuje informace o aktuálních relacích, včetně spuštěných dotazů. 126

Oracle Database SQL Tuning Guide: Monitoring Database Operations. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 2014 [cit. 2014-06-07]. Dostupné z: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_monit.htm#TGSQL94597. 127 Tamtéţ 65

5

Optimalizace v praxi

V této kapitole si vyzkoušíme některé z technik a postupů popsaných v předešlých kapitolách. Naše databáze slouţí jako datový sklad mezinárodní telekomunikační společnosti. Datový sklad je vyuţíván zejména pro tvorbu velkého mnoţství reportů, ať uţ jednorázových pro různé analýzy anebo na pravidelné bázi např. pro výpočet provizí partnerským prodejnám apod. Verze databázového systému je Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi. Jako klientská aplikace pro přístup do databáze je pouţit sofistikovaný nástroj Toad For Oracle Base verze 11.5.1.2 od společnosti Quest Software128. Statistiky nad neuţivatelskými objekty se počítají vţdy po načtení dat, coţ bývá kaţdý den. Některé ze statistik se nastavují na určenou hodnotu. Nepouţívají se histogramy. Zdrojem dat datového skladu je několik systémů jako Oracle Siebel (CRM), SAP HR (ERP) atd. Dotazy byly spouštěny mimo hlavní pracovní dobu, kdy je zátěţ datového skladu niţší. Vliv aktuálního zatíţení databáze na výkon dotazů by měl být minimální. Testované SQL dotazy jsou kompletně uvedeny pouze v příloze na CD, z důvodu jejich velkého rozsahu.

5.1 Úloha č. 1 Naším prvním úkolem je vytvořit report, který by sledoval denní, týdenní a měsíční poměr mezi počtem vytvořených a vyřešených servisních poţadavků. Servisní poţadavek je nástroj CRM systému Siebel slouţící hlavně pro evidenci zákaznických poţadavků. Servisní poţadavek má svůj ţivotní cyklus, od vytvoření, delegování na kompetentní oddělení a osobu k vyřešení a uzavření. Reportovány budou pouze výsledky za tři měsíce a report bude distribuován prostřednictvím aplikace excel, který si budou uţivatelé sami aktualizovat. Do souboru bude napojen SQL dotaz nad zdrojovými daty. Zdrojem dat poţadavků je tabulka DW_SERVICE_REQUEST. Řádek v této tabulce je roven jednomu servisnímu poţadavku. Tabulka ukazuje vţdy aktuální stav poţadavku s dvoudenním zpoţděním. Počet řádků tabulky přesahuje čtyři milióny a není rozdělena na oddíly ani komprimována. Tabulka narůstá zhruba o stovky řádků denně. Pro účely našeho reportu potřebujeme spočítat počet vytvořených poţadavků agregovaných za jednotlivé dny a to 128

samé

pro

vyřešené

poţadavky.

Zakoupeno společností DELL v červenci 2012

66

K tomuto

účelu

pouţijeme

pole

DW_DAT_KEY_CREATED, kde je uloţeno datum vytvoření poţadavku ve formátu NUMBER a pole SOLVED_DATE s datem vyřešení ve formátu DATE. Tyto a pole X_GROUP pouţijeme v predikátu dotazu. Atribut X_GROUP vyjadřuje oddělení zodpovědné za servisní poţadavek. Po napsání dotazu si můţeme zobrazit jeho exekuční plán. Je pouţito grafické zobrazení z nástroje Toad: Plan SELECT STATEMENT CHOOSE Cost: 13 162 Bytes: 398 796 Cardinality: 1 194 8 VIEW CONSUMER_REPORTING. Cost: 13 162 Bytes: 398 796 Cardinality: 1 194 7 UNION-ALL 4 HASH GROUP BY Cost: 1 046 Bytes: 47 Cardinality: 1 3 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_SERVICE_REQUEST Cost: 1 045 Bytes: 1 269 Cardinality: 27 2 BITMAP CONVERSION TO ROWIDS 1 BITMAP INDEX RANGE SCAN INDEX (BITMAP) DW_LAYER_OWN.FK_SERREQ_DAT_CREATED 6 HASH GROUP BY Cost: 12 116 Bytes: 57 264 Cardinality: 1 193 5 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SERVICE_REQUEST Cost: 12 115 Bytes: 57 264 Cardinality: 1 193

Tabulka 5-1: Plán dotazu č.1

Z výše

uvedeného

plánu,

lze

odvodit,

DW_SERVICE_REQUEST. Jedenkrát

skrze

ţe

dvakrát

bitmapový

přistupujeme index a

do

tabulky

podruhé úplným

procházením tabulky. Sloupec DW_DAT_KEY_CREATED je totiţ indexovaný, kdeţto sloupec SOLVED_DATE ne. Dotaz přes SOLVED_DATE optimalizátor ohodnotil přibliţně jako desetkrát draţší. V reálu byly dotazu naměřeny časy odezvy od 15 do 30 minut, při spuštění na pozadí prostřednictvím aktualizace dat v excelu. Samy o sobě nejsou tyto časy nikterak závaţné z hlediska výkonu. Problém spočívá v tom, ţe nepřímé spuštění dotazu uţivatelem v reportu, způsobí dočasné zamrznutí aplikace excel. Dokud není dotaz proveden, uţivatelé nemohou s touto aplikací ve svém počítači pracovat, coţ je značně obtěţující. Vytvořit index nad atributem SOLVED_DATE nelze, protoţe nemáme na tabulku patřičná oprávnění. Jako vhodným řešením se zde jeví pouţití materializovaného pohledu. Nepotřebujeme udrţovat dlouhodobá historická data, ty budou uchovány v samotném reportu. Po uplynutí daných tří měsíců, bude report uzamčen a vytvoří se nový soubor na další tři měsíce. O aktualizaci dat v pohledu se nám bude automaticky starat databázový systém. V případě, ţe bychom chtěli data ukládat do tabulky, museli bychom vytvořit uloţenou proceduru, která by data vkládala do oné tabulky prostřednictvím dotazu a následně ještě provádět update stávajících dat, protoţe data v tabulce DW_SERVICE_REQUEST se mohou i zpětně změnit. Na druhou stranu pouţití materializovaného pohledu by nebylo vhodné, pokud bychom chtěli v pohledu uchovávat všechny záznamy od konkrétního data. Dotaz by 67

takto postupem času vracel stále větší mnoţství řádků a jeho aktualizace by stávala více a více náročnější. Pouţití dynamické podmínky v predikátu dotazu nám zajistí to, ţe dotaz pohledu vrátí vţdy přibliţně stejný objem dat: WHERE DW_DAT_KEY_CREATED >= TO_NUMBER (TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -3),'YYYYMMDD')) WHERE SOLVED_DATE >= ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -3) Nyní vytvoříme materializovaný pohled: CREATE MATERIALIZED VIEW CONSUMER_REPORTING.DK_ZAME_CARE_REPORT TABLESPACE USER_STORAGE_CCAT BUILD IMMEDIATE REFRESH COMPLETE NEXT TRUNC(SYSDATE+1)+6/24 AS SELECT ACT_DATE, … … ; Pouţitím parametru NEXT určíme, kdy se má pohled aktualizovat. V našem případě je to kaţdý den v 6:00. Databáze automaticky vytvořila pro tyto účely JOB, který pouţívá funkci balíku

DBMS_REFRESH.REFRESH.

Do

reportu

nyní

z materializovaného pohledu: SELECT * FROM CONSUMER_REPORTING.DK_ZAME_CARE_REPORT; Aktualizace dat v reportu nyní proběhne prakticky okamţitě:

Obrázek 5-1: Aktualizace dat v reportu

68

stačí

napojit

dotaz

V tabulkovém prostoru, který máme k dispozici, je dostatek místa pro uloţení pohledu. Není tedy zapotřebí porovnávat vliv pohledu na výkon dotazu s nároky na prostor. Velikost pohledu bude díky dynamické podmínce proměnlivá, avšak rozdíly budou naprosto minimální.

5.2 Úloha č. 2 Další úkolem je zváţit moţnosti optimalizace skriptu, poskytnutého kolegou. Jedná se o uloţenou proceduru, která vkládá a upravuje data z mnoha tabulek do jedné. Tato tabulka slouţí pro výpočet provizí obchodním partnerů za prodané sluţby. V tomto případě jde v podstatě o denormalizaci zdrojových tabulek pro účely snadnější aplikace samotné logiky výpočtu provizí. Data v tabulce také bývají často účelově měněny z důvodu udělování různých provizních výjimek apod. Prvním příkazem procedury je INSERT, který vkládá data do tabulky na základě dotazu. Dotaz napojuje na základní tabulku V4_VAS_BASE, dalších šest tabulek. Tabulka V4_VAS_BASE obsahuje přes 44 milionů řádků a většina zbylých tabulek má více neţ 10 miliónů záznamů. Ţádná z tabulek není komprimovaná a pouze V4_VAS_BASE je rozdělena na oddíly. Dotaz pouţívá dynamickou podmínku, podobně jako v předchozí kapitole, takţe z hlavní tabulky vrací pouze záznamy za poslední měsíc. Exekuční plán dotazu vypadá takto: Plan SELECT STATEMENT CHOOSE Cost: 44 312 Bytes: 286 Cardinality: 1 18 NESTED LOOPS OUTER Cost: 44 312 Bytes: 286 Cardinality: 1 16 NESTED LOOPS OUTER Cost: 44 312 Bytes: 280 Cardinality: 1 13 NESTED LOOPS OUTER Cost: 44 310 Bytes: 260 Cardinality: 1 10 NESTED LOOPS OUTER Cost: 44 308 Bytes: 221 Cardinality: 1 7 HASH JOIN OUTER Cost: 44 307 Bytes: 204 Cardinality: 1 5 NESTED LOOPS OUTER Cost: 41 623 Bytes: 177 Cardinality: 1 2 PARTITION RANGE ALL Cost: 41 621 Bytes: 156 Cardinality: 1 Partition #: 7 Partitions accessed #1 - #1305 1 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE Cost: 41 621 Bytes: 156 Cardinality: 1 Partition #: 7 Partitions accessed #1 - #1305 4 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_SUBSCRIBER Cost: 2 Bytes: 21 Cardinality: 1 3 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_SUB Cost: 1 Cardinality: 1 6 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SUB_SEG_H Cost: 2 560 Bytes: 198 668 700 Cardinality: 7 358 100 9 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_SEGMENT Cost: 1 Bytes: 17 Cardinality: 1 8 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_SEG Cost: 0 Cardinality: 1 12 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_CUSTOMER_ACCOUNT Cost: 2 Bytes: 39 Cardinality: 1 11 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_CUSACC Cost: 1 Cardinality: 1 15 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_ORDER_HEADER Cost: 2 Bytes: 20 Cardinality: 1 14 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_ORDHEA Cost: 1 Cardinality: 1 17 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_AGR Cost: 0 Bytes: 6 Cardinality: 1

Tabulka 5-2: Plán dotazu č.2 verze A 69

Při bliţším pohledu na plán dotazu nás zarazí kardinalita, která je téměř u všech operací 1. Znamená to, ţe optimalizátor nemá dostatečné informace, aby mohl kardinalitu správně určit. Po prozkoumání oddílů zjistíme, ţe základní tabulka má přes sto doposud nezanalyzovaných oddílů: SELECT COUNT(*) FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = 'V4_VAS_BASE' AND LAST_ANALYZED IS NULL; Spustíme tedy nad tabulkou V4_VAS_BASE sběr statistik: EXEC dbms_stats.gather_table_stats('CONSUMER_REPORTING','V4_VAS_BASE', cascade=>TRUE); Podíváme se na exekuční plán s aktuálními údaji: Plan SELECT STATEMENT CHOOSE Cost: 66 K Bytes: 875 K Cardinality: 3 K 17 NESTED LOOPS OUTER Cost: 66 K Bytes: 875 K Cardinality: 3 K 14 HASH JOIN RIGHT OUTER Cost: 60 K Bytes: 756 K Cardinality: 3 K 1 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SEGMENT Cost: 2 Bytes: 1 K Cardinality: 83 13 HASH JOIN OUTER Cost: 60 K Bytes: 704 K Cardinality: 3 K 11 NESTED LOOPS OUTER Cost: 57 K Bytes: 622 K Cardinality: 3 K 8 NESTED LOOPS OUTER Cost: 51 K Bytes: 558 K Cardinality: 3 K 6 NESTED LOOPS OUTER Cost: 51 K Bytes: 539 K Cardinality: 3 K 3 PARTITION RANGE ALL Cost: 46 K Bytes: 479 K Cardinality: 3 K Partition #: 8 Partitions accessed #1 - #1305 2 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE Cost: 46 K Bytes: 479 K Cardinality: 3 K Partition #: 8 Partitions accessed #1 - #1305 5 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_ORDER_HEADER Cost: 2 Bytes: 20 Cardinality: 1 4 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_ORDHEA Cost: 1 Cardinality: 1 7 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_AGR Cost: 0 Bytes: 6 Cardinality: 1 10 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_SUBSCRIBER Cost: 2 Bytes: 21 Cardinality: 1 9 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_SUB Cost: 1 Cardinality: 1 12 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SUB_SEG_H Cost: 3 K Bytes: 189 M Cardinality: 7 M 16 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_CUSTOMER_ACCOUNT Cost: 2 Bytes: 39 Cardinality: 1 15 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_CUSACC Cost: 1 Cardinality: 1

Tabulka 5-3: Plán dotazu č. 2 verze B

Optimalizátor přehodnotil některé přístupové cesty, např. v předchozím plánu vyhledával v tabulce DW_SEGMENT pomocí indexu Po aktualizaci statistik zvolil raději úplné procházení tabulky, coţ lze povaţovat za rozumný krok, protoţe tabulka DW_SEGMENT je velmi malá (204 řádků) a slouţí jako číselník. Další z tabulek, která je kompletně prohledávána je tabulka DW_SUB_SEG_H. Tato tabulka je spojována přes podmínku

70

rovnosti klíče indexu, ale index optimalizátor nepouţije. Přikaţme optimalizátoru pouţít index prostřednictvím hintu: SELECT /*+ INDEX (DW_SUB_SEG_H FK_SUBSEGH_SUB)*/…; Nový plán jiţ pouţívá index pro přístup do tabulky DW_SUB_SEG_H: Plan SELECT STATEMENT CHOOSE Cost: 117 K Bytes: 875 K Cardinality: 3 K 19 NESTED LOOPS OUTER Cost: 117 K Bytes: 875 K Cardinality: 3 K 17 NESTED LOOPS OUTER Cost: 117 K Bytes: 856 K Cardinality: 3 K 14 NESTED LOOPS OUTER Cost: 111 K Bytes: 795 K Cardinality: 3 K 11 NESTED LOOPS OUTER Cost: 105 K Bytes: 677 K Cardinality: 3 K 8 HASH JOIN RIGHT OUTER Cost: 98 K Bytes: 613 K Cardinality: 3 K 1 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SEGMENT Cost: 2 Bytes: 1 K Cardinality: 83 7 NESTED LOOPS OUTER Cost: 98 K Bytes: 561 K Cardinality: 3 K 3 PARTITION RANGE ALL Cost: 46 K Bytes: 479 K Cardinality: 3 K Partition #: 8 Partitions accessed #1 - #1305 2 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE Cost: 46 K Bytes: 479 K Cardinality: 3 K Partition #: 8 Partitions accessed #1 - #1305 6 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_SUB_SEG_H Cost: 98 K Bytes: 27 Cardinality: 1 5 BITMAP CONVERSION TO ROWIDS 4 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) DW_LAYER_OWN.FK_SUBSEGH_SUB 10 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_SUBSCRIBER Cost: 2 Bytes: 21 Cardinality: 1 9 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_SUB Cost: 1 Cardinality: 1 13 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_CUSTOMER_ACCOUNT Cost: 2 Bytes: 39 Cardinality: 1 12 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_CUSACC Cost: 1 Cardinality: 1 16 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_ORDER_HEADER Cost: 2 Bytes: 20 Cardinality: 1 15 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_ORDHEA Cost: 1 Cardinality: 1 18 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_AGR Cost: 0 Bytes: 6 Cardinality: 1

Tabulka 5-4: Plán dotazu č. 2 verze C

Dotaz byl spuštěn po aktualizaci statistik a s pouţitím hintu. Ačkoliv u druhé varianty dotazu stouply costy, podařilo se sníţit čas odezvy o 21% oproti původnímu dotazu a o 9% počet přístupů na disk. Zaměřme se nyní na hlavní tabulku dotazu a to V4_VAS_BASE. Na začátku této kapitoly jsme si řekli, ţe tato tabulka je rozdělena na oddíly. V uvedených plánech skutečně vidíme, ţe je procházeno všech 1305 oddílů, z čehoţ vyplývá, ţe náš dotaz neobsahuje v predikátu klíč oddílů. Toto je značně nevýhodné, protoţe data, se kterými chceme pracovat, se nachází jen v některých oddílech. Klíčem oddílů je sloupec DATE_KEY, pro kaţdou unikátní hodnotu tohoto sloupce je vytvořen samostatný oddíl. DATE_KEY vyjadřuje business datum, kdy došlo k aktivaci respektive deaktivaci nějakého produktu. Jak uţ bylo řečeno, dotaz sice pouţívá časovou podmínku, ale namísto sloupce DATE_KEY je pouţit sloupec DATE_TIME. Hodnoty v těchto atributech jsou naprosto ekvivalentní s výjimkou odlišného 71

datového typu. Nic nám tedy nebrání přepsat predikát dotazu s pouţitím DATE_KEY. Nový plán dotazu bude takovýto: Plan SELECT STATEMENT CHOOSE Cost: 90 K Bytes: 2 M Cardinality: 9 K 16 NESTED LOOPS OUTER Cost: 90 K Bytes: 2 M Cardinality: 9 K 14 NESTED LOOPS OUTER Cost: 90 K Bytes: 2 M Cardinality: 9 K 11 NESTED LOOPS OUTER Cost: 73 K Bytes: 2 M Cardinality: 9 K 8 HASH JOIN RIGHT OUTER Cost: 56 K Bytes: 2 M Cardinality: 9 K 1 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SEGMENT Cost: 2 Bytes: 1 K Cardinality: 83 7 HASH JOIN OUTER Cost: 56 K Bytes: 2 M Cardinality: 9 K 5 HASH JOIN OUTER Cost: 53 K Bytes: 2 M Cardinality: 9 K 3 PARTITION RANGE ITERATOR Cost: 46 K Bytes: 1 M Cardinality: 9 K Partition #: 8 Partitions determined by Key Values 2 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE Cost: 46 K Bytes: 1 M Cardinality: 9 K Partition #: 8 Partitions determined by Key Values 4 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SUBSCRIBER Cost: 7 K Bytes: 121 M Cardinality: 6 M 6 TABLE ACCESS FULL TABLE DW_LAYER_OWN.DW_SUB_SEG_H Cost: 3 K Bytes: 189 M Cardinality: 7 M 10 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_CUSTOMER_ACCOUNT Cost: 2 Bytes: 39 Cardinality: 1 9 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_CUSACC Cost: 1 Cardinality: 1 13 TABLE ACCESS BY INDEX ROWID TABLE DW_LAYER_OWN.DW_ORDER_HEADER Cost: 2 Bytes: 20 Cardinality: 1 12 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_ORDHEA Cost: 1 Cardinality: 1 15 INDEX UNIQUE SCAN INDEX (UNIQUE) DW_LAYER_OWN.PK_AGR Cost: 0 Bytes: 6 Cardinality: 1

Tabulka 5-5: Plán dotazu č. 2 verze D

Optimalizátor v tomto plánu uţ ví, s jakými oddíly bude dotaz pracovat a ostatní ignoruje. Dotaz spustíme a po dokončení se můţeme podívat na parametry jeho běhu všech tří verzí dotazu: SELECT PARSE_CALLS, DISK_READS, BUFFER_GETS, USER_IO_WAIT_TIME, OPTIMIZER_COST, CPU_TIME, (ELAPSED_TIME/1000000) / 60 AS ELAPSED_TIME FROM V$SQL WHERE SQL_ID IN ('fab78np45zhdw', 'a470hbzj2c1yh', 'byxhyjtp6gsrs') ORDER BY LAST_ACTIVE_TIME DESC; SQL_ID Přes podmínku klíče oddílu

PARSE_C ALLS

DISK_R EADS

BUFFER_ GETS

USER_IO_WA IT_TIME

OPTIMIZER _COST

2

167 655

171 916

61 334 770

89 716

S použitím hintu

2

667 048

738 849

251 041 893

116 664

Původní dotaz

2

714 668

3 593 631

330 908 382

66 419

CPU_ TIME 24 504 772 87 789 698 86 193 898

ELAPSED _TIME 13,722758 3333 53,757073 0000 67,198220 1667

Tabulka 5-6: Výsledky dotazu č. 2

Pouţití klíče oddílu v predikátu dotazu nám přineslo sníţení času odezvy o 80% a sníţení diskových operací o 77% oproti původnímu dotazu.

72

5.3 Úloha č. 3 Naším posledním úkolem je pokusit se optimalizovat některý z velmi často pouţívaných dotazů. Jako vhodný kandidát se jeví tabulka ORGANIZATION_CHART. Tato tabulka obsahuje login všech zaměstnanců, kteří jsou v kontaktu se zákazníkem a jejich přiřazení ke konkrétnímu oddělení. Tato tabulka se velice často napojuje na tabulky faktů jako např. jiţ zmiňované DW_SERVICE_REQUEST, V4_VAS_BASE a mnoho dalších. Klíčem spojení zde je sloupec LOGINNAME. Tento atribut se skládá z prvních čtyřech písmen křestního jména a příjmení. U mnoha zaměstnanců se však tento atribut můţe změnit, většinou to bývá u ţen, které se vdají a získají tak jiné příjmení. Jelikoţ zdrojový systém nedovoluje toto pole historizovat, byl pro tyto účely vytvořen atribut LOGINNAME_OLD, kam se vkládá původní hodnota, pokud došlo ke změně. Při napojení tabulky ORGANIZATION_CHART do tabulek faktů je nutné hledat login obsaţený v tabulce faktů jak ve sloupci LOGINNAME tak LOGINNAME_OLD. Většinou pracujeme i s historickými daty, kde se můţe v tabulce faktů nacházet i původní hodnota loginu. Našel jsem, ţe analytici pouţívají nejčastěji dva různé způsoby napojení tabulky ORGANIZATION_CHART, které danou problematiku dvou loginů řeší: Pouţití IN FROM CONSUMER_REPORTING.V4_VAS_BASE VAS JOIN CONSUMER_REPORTING.ORGANIZATION_CHART OC ON VAS.LOGINNAME IN (OC.LOGINNAME, OC.LOGINNAME_OLD) Pouţití OR FROM CONSUMER_REPORTING.V4_VAS_BASE VAS JOIN CONSUMER_REPORTING.ORGANIZATION_CHART OC ON VAS.LOGINNAME = OC.LOGINNAME OR VAS.LOGINNAME = OC.LOGINNAME_OLD) Vzpomeňme si nyní na kapitolu 2.3.3, kde jsme popisovali činnost jednotlivých částí optimalizátoru. Komponenta Query Transformer transformuje dotazy do jiné podoby s cílem nalézt alternativní dotaz vracející naprosto stejné výsledky, ale s menšími náklady. Konkrétně transformace OR Expansion přepisuje dotazy obsahující klauzuli OR v predikátu na dva dotazy sloučené operací UNION ALL. Pokusme se takto přepsat i naše spojení:

73

FROM CONSUMER_REPORTING.V4_VAS_BASE VAS JOIN ( SELECT oca.*, oca.loginname as IDX FROM CONSUMER_REPORTING.ORGANIZATION_CHART oca union all SELECT oco.*, oco.loginname_old as IDX FROM CONSUMER_REPORTING.ORGANIZATION_CHART oco where oco.loginname_old is not null ) OC ON VAS.LOGINNAME = OC.IDX Exekuční plány pro jednotlivé dotazy v módu BASIC: Plan SELECT STATEMENT CHOOSE 9 CONCATENATION 4 HASH JOIN 1 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.ORGANIZATION_CHART 3 PARTITION RANGE ITERATOR 2 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE 8 HASH JOIN 5 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.ORGANIZATION_CHART 7 PARTITION RANGE ITERATOR 6 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE

Tabulka 5-7: Plán dotazu č. 3 varianta s IN Plan SELECT STATEMENT CHOOSE 9 CONCATENATION 4 HASH JOIN 1 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.ORGANIZATION_CHART 3 PARTITION RANGE ITERATOR 2 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE 8 HASH JOIN 5 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.ORGANIZATION_CHART 7 PARTITION RANGE ITERATOR 6 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE

Tabulka 5-8: Plán dotazu č. 3 varianta s OR Plan SELECT STATEMENT CHOOSE 7 HASH JOIN 4 VIEW DAVIKAKR. 3 UNION-ALL 1 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.ORGANIZATION_CHART 2 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.ORGANIZATION_CHART 6 PARTITION RANGE ITERATOR 5 TABLE ACCESS FULL TABLE CONSUMER_REPORTING.V4_VAS_BASE

Tabulka 5-9: Plán dotazu č. 3 varianta s UNION ALL

74

Z porovnání jednotlivých plánů vyplývá, ţe zatímco u prvních dvou variant jsou procházeny obě

tabulky

dvakrát,

u

třetího

dotazu

je

dvakrát

procházena

pouze

tabulka

ORGANIZATION_CHART, která obsahuje jen něco okolo třech tisíc řádků. Přepsáním dotazu jsme odstranili nadbytečné prohledání větší z tabulek. V případě našich testovacích dotazů byl rozdíl odezvy naprosto minimální, protoţe jsme se ve všech případech dotazovali přes podmínku klíče oddílů tabulky V4_VAS_BASE. Avšak u leckterých komplikovanějších a obsáhlejších dotazů by mohlo jít o významné zlepšení výkonnosti. Podívejme se na rozdíly některých parametrů dotazu z pohledu V$SQL: SQL_ID OR UNION ALL IN

DISK_READS

USER_IO_WAIT_TIME 0 0 0 0 3 6 039 Tabulka 5-10: Parametry běhu dotazu č. 3

75

OPTIMIZER_COST 343 354 1 596 2 740

Závěr Téma ladění SQL dotazů a výkonu databázových aplikací obecně je velmi obsáhlé a rozebrat kompletně tuto problematiku by vydalo na celou knihu. Tato práce měla za úkol popsat nejdůleţitější moţné přístupy, techniky a nástroje, které je moţné vyuţít v procesu ladění SQL dotazů, coţ se mi, jak pevně věřím, podařilo. V práci nejsou zmíněny některé problematiky související spíše s vývojem databázových aplikací neţ SQL dotazů jako takových. Takovými oblastmi jsou např. minimalizace sporů a optimalizace V/V operací. Pro mne samotného byla tato práce velmi přínosná a pomohla mi získat nové znalosti nejen o databázovém systému Oracle, ale i o databázích obecně. Doufám, ţe získané a zde uvedené poznatky, najdou uplatnění i u mnoha mých kolegů a pomohou jim tak učinit jejich práci snadnější. U praktické části jsem se snaţil nastínit řešení některých problémů z kaţdodenní praxe. Je nutné si uvědomit, ţe řešení daného problému se vţdy odvíjí od konkrétního prostředí, ve kterém je dotaz spouštěn. Ne vţdy má vývojář moţnost měnit schéma databáze a vytvářet indexy, pomocné tabulky či materializované pohledy. V jiném případě můţe být zase jedinou moţností právě vytvoření databázových objektů, protoţe nelze měnit samotné SQL příkazy. Starší verze databázového systému Oracle také nemusí nabízet veškeré nástroje jako nejnovější verze. Přesvědčili jsme se také, ţe náklady spočtené optimalizátorem jsou pouze orientační a vyšší náklady nemusí nutně znamenat i horší výkon dotazu. Z těchto důvodů jsem se v diplomové práci vyvaroval obecným doporučením tvorby SQL příkazů, jako je nahrazení klauzule IN v predikátu dotazu, poddotazem v klauzuli EXISTS apod. Lze sice říci, ţe tyto obecně přijímané techniky vykazují ve většině případů lepší výkon, ale zásadně by neměly být přijímány jako dogmatické. Pro optimalizaci konkrétního dotazu je vţdy třeba testovat skutečný výkon dotazu např. pomocí pohledu V$SQL. Na závěr bych uţ jen všem čtenářům rád popřál, ať je pro ně případná optimalizace dotazů výzvou a moţností jak se zlepšit ve svém oboru a ne pouze nepříjemnou záleţitostí, kterou je třeba přejít.

76

Seznam pramenů a literatury I.

Monografie

BRYLA, Bob a Kevin LONEY. Mistrovství v Oracle Database 11g. 1. vyd. Brno: Computer Press, a.s., 2009. ISBN 978-80-251-2189-4. HARRISON, Guy. Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. Boston: Pearson Education, Inc., 2010. ISBN 978-0-13-701195-7. KYTE, Thomas. Oracle: Návrh a tvorba aplikací. Brno: Computer Press, a.s., 2007. ISBN 80-251-0569-5. LONEY, Kevin. Oracle Database: Kompletní průvodce. 1. vyd. Brno: Computer Press, a.s., 2010. ISBN 978-80-251-2489-5. R. GROFF, James a Paul N. WEINBERG. SQL: Kompletní průvodce. 1. vyd. Brno: CP Books, a.s., 2005. ISBN 80-251-0369-2.

II.

Elektronické zdroje

COLGAN, Maria. The Oracle Optimizer Explain the Explain Plan. White Paper Oracle [online].

2011

[cit.

2014-06-16].

Dostupné

z:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explainplan-052011-393674.pdf. DB-Engines Ranking. DB-Engines [online]. © 2012-2014 [cit. 2014-04-27]. Dostupné z: http://db-engines.com/en/ranking. DOHNAL, Ladislav. Efektivní využívání DB: Ladění schéma. [online]. 2013 [cit. 2014-0425]. Dostupné z: https://is.muni.cz/el/1433/jaro2013/PA152/um/slides10-ladeni-schema.pdf. ORACLE CORPORATION. Oracle Database: Documentation Library [online]. © 19942014 [cit. 2014-05-10]. Dostupné z: http://docs.oracle.com/cd/E16655_01/index.htm. Oracle

FAQ's

[online].

2007,

27.12.

2008

http://www.orafaq.com.

77

[cit.

2014-03-21].

Dostupné

z:

Seznam obrázků Obrázek 2-1: Segmenty, rozsahy, bloky v rámci tabulkového prostoru .................................. 14 Obrázek 2-2: Paměťové struktury databázového systému Oracle............................................ 17 Obrázek 2-3: Kurzor ................................................................................................................. 20 Obrázek 2-4: Sloţení paměti PGA ........................................................................................... 20 Obrázek 2-5: Komponenty optimalizátoru ............................................................................... 23 Obrázek 3-1: Vnitřní struktura indexu B*strom ....................................................................... 30 Obrázek 3-2: Indexově orientovaná tabulka............................................................................. 35 Obrázek 4-1: Jednotlivé fáze zpracování SQL příkazů ............................................................ 45 Obrázek 4-2: Strom řádkových zdrojů ..................................................................................... 48 Obrázek 4-3: Setřídění – slévání .............................................................................................. 54 Obrázek 4-4: Algoritmus automatického zachycení plánu ...................................................... 63 Obrázek 5-1: Aktualizace dat v reportu.................................................................................... 68

78

Seznam tabulek Tabulka 3-1: Tabulka zaměstnanců .......................................................................................... 32 Tabulka 3-2: Příklad bitmapy nad sloupcem POHLAVI ......................................................... 32 Tabulka 3-3: Obsah bitmapového indexu pro spojení .............................................................. 33 Tabulka 4-1: Datové struktury a přístupové cesty .................................................................... 50 Tabulka 4-2: Ukázka exekučního plánu ................................................................................... 59 Tabulka 5-1: Plán dotazu č.1 .................................................................................................... 67 Tabulka 5-2: Plán dotazu č.2 verze A....................................................................................... 69 Tabulka 5-3: Plán dotazu č. 2 verze B ...................................................................................... 70 Tabulka 5-4: Plán dotazu č. 2 verze C ...................................................................................... 71 Tabulka 5-5: Plán dotazu č. 2 verze D...................................................................................... 72 Tabulka 5-6: Výsledky dotazu č. 2 ........................................................................................... 72 Tabulka 5-7: Plán dotazu č. 3 varianta s IN ............................................................................. 74 Tabulka 5-8: Plán dotazu č. 3 varianta s OR ............................................................................ 74 Tabulka 5-9: Plán dotazu č. 3 varianta s UNION ALL ............................................................ 74 Tabulka 5-10: Parametry běhu dotazu č. 3 ............................................................................... 75

79

Seznam zkratek 3NF – Třetí normální forma ADDM – Automatic Database Diagnostic Monitor ASM – Automatic Storage Management ASMM – Automatic Shared Memory Management ASSM – Automatic Segment Space Management B*Tree – Balanced Tree CBO – Cost Based Optimizer CPU – Central Processing Unit DDL – Data Definition Language DML – Data Manipulation Language MSSM – Manual Segment Space Management OLAP – On-line Analytical Processing OLTP – On-line Transaction Processing PGA – Program Global Area PL/SQL – Procedural Language/Structured Query Language RAC – Real Application Clusters SGA – System Global Area SQL – Structured Query Language V/V – vstupní/výstupní

80

Seznam příloh na CD Dotaz č. 1 Dotaz č. 2 verze A Dotaz č. 2 verze C Dotaz č. 2 verze D Dotaz č. 3 varianta s IN Dotaz č. 3 varianta s OR Dotaz č. 4 varianta s UNION ALL

81

Optimalizace SQL dotazů

Recommend Documents