UNICORN COLLEGE BAKALÁŘSKÁ PRÁCE

UNICORN COLLEGE Katedra informačních technologií

BAKALÁŘSKÁ PRÁCE Nástroj na tvorbu testovacích dat pro zátěžové testování DB

Autor BP: Marek Chytrý Vedoucí BP: Ing. Miroslav Žďárský 2012 Praha

ČESTNÉ PROHLÁŠENÍ Prohlašuji, že svoji bakalářskou práci na téma Nástroj na tvorbu testovacích dat pro zátěžové testování DB jsem vypracoval samostatně pod vedením vedoucího bakalářské práce a s použitím odborné literatury a dalších informačních zdrojů, které jsou v práci citovány a jsou též uvedeny v seznamu literatury a použitých zdrojů. Jako autor uvedené bakalářské práce dále prohlašuji, že v souvislosti s vytvořením této bakalářské práce jsem neporušil autorská práva třetích osob, zejména jsem nezasáhl nedovoleným způsobem do cizích autorských práv osobnostních, a jsem si plně vědom následků porušení ustanovení § 11 a následujících autorského zákona č. 121/2000 Sb.

V ........................... dne ................

….…………… Marek Chytrý

PODĚKOVÁNÍ Děkuji vedoucímu bakalářské práce Ing. Miroslavovi Žďárskému za účinnou metodickou, pedagogickou a odbornou pomoc a další cenné rady při zpracování mé bakalářské práce. Dále chci velmi poděkovat své manželce za oporu, povzbuzení a vytvoření příjemného prostředí pro psaní této práce a rodičům, kteří mi umožnili studium a během studia mě podporovali.

NÁSTROJ NA TVORBU TESTOVACÍCH DAT PRO ZÁTĚŽOVÉ TESTOVÁNÍ DB TOOL FOR CREATING TEST DATA FOR STRESS TESTING DB

6

ABSTRAKT Cílem této bakalářské práce je navrhnout nástroj na generování dat pro zátěžové testy. Aby však tato data byla správně navržena, je potřeba porozumět databázi, jak s daty pracuje a jak můžeme na základě správného návrhu datové struktury tuto činnost co nejvíce zefektivnit. V teoretické části jsou nejprve popsány základní pojmy týkající se relačních databází a zátěžových testů. Poté jsou vysvětleny způsoby sběru dat z databáze a je ukázáno na příkladech, jak lze příkazy pro práci s daty optimalizovat. Dále navazují informace o způsobech, jak databáze volí co nejefektivnější přístup k datům za použití optimizeru a statistik databáze. Práce se nezaměřuje na konfiguraci databáze nebo hardwaru serveru, to by bylo už na téma jiné bakalářské práce. V praktické části je pak popsán navržený nástroj pro generování dat, jeho části a princip generování testovacích dat. Zde je také základně popsán jazyk PL/SQL, ve kterém je nástroj naprogramován. Klíčová slova: relační databáze, zátěžové testy, optimalizace, optimizer, statistiky, PL/SQL

7

ABSTRACT IN ENGLISH The aim of this bachelor thesis is to propose a tool for generating data for stress tests. In order that this data are properly designed, it is necessary to understand the database; how it works with data and how we can do this activity more effective based on right proposal of the data structure. In the theoretical section there are firstly described the basic concepts related to relational databases and stress tests. Then there is explained how we collect data from the database and shown in the examples, how the commands for working with data can be optimized. Further follow information how database chooses the most effective access to data using the optimizer and statistics database. The thesis does not focus on the configuration database or hardware; it would be the topic of another thesis. In the practical part there is described a tool designed to generate data, its parts and principles of generating test data. There is also described programming language PL/SQL, in which the tool is programmed. Keywords: relational database, stress tests, optimization, optimizer, statistics, PL/SQL

8

OBSAH

1.

Úvod .................................................................................................................. 11

2.

Základní pojmy k optimalizaci databází ........................................................... 12 2.1.

Databáze .................................................................................................. 12

2.1.1. 2.2.

Zátěžové testy .......................................................................................... 15

2.2.1. 2.3.

Exekuční plán ................................................................................. 18

Indexy ...................................................................................................... 21

2.4.1.

B-Tree index................................................................................... 21

2.4.2.

Bitmap index .................................................................................. 26

2.4.3.

Indexově organizované tabulky (Index organized tables) ............. 28

2.5.

Optimizer ................................................................................................. 30

2.5.1.

3.

Získání dat pro testování ................................................................ 15

Přístupy k tabulkám ................................................................................. 16

2.3.1. 2.4.

Relační databáze............................................................................. 13

RBO (Rule based optimizer) a CBO (Cost based optimizer) ........ 31

2.6.

Statistiky .................................................................................................. 34

2.7.

Hinty ........................................................................................................ 37

Praktická část - Generování dat ........................................................................ 38 3.1.

Úvod do PL/SQL ..................................................................................... 38

3.1.1. 3.2.

Balíčky, procedury a funkce .......................................................... 38

Princip generování ................................................................................... 43

3.2.1.

Zdrojová data pro generování ........................................................ 43

3.2.2.

Balíček fetch_source_data ............................................................. 44

9

3.2.3.

Balíček random_data ...................................................................... 46

3.2.4.

Balíček package_random_table ..................................................... 47

3.2.5.

Pomocné balíčky, funkce a procedury ........................................... 50

3.3.

Export a import velkého množství dat v databázích Oracle .................... 51

4.

Závěr ................................................................................................................. 52

5.

Conclusion ........................................................................................................ 54

6.

Zdroje ................................................................................................................ 56

7.

Seznam použitých symbolů a zkratek ............................................................... 58

8.

Seznam obrázků ................................................................................................ 59

9.

Seznam tabulek ................................................................................................. 60

10

1.

ÚVOD

Vysoká dostupnost a krátká doba odezvy informačních systémů se v dnešním světě pokládá za samozřejmost. Pokud se některá internetová stránka nenačte uživateli do tří sekund, uživatel pravděpodobně takovou stránku po této době zavře a podívá se raději po jiné stránce, která se mu dostatečně rychle zobrazí. Tím narážím na fakt, že jedním z nejdůležitějších vlastností informačního systému je jeho doba odezvy. Tato oblast informačních technologií není jednoduchá, ale dnešní uživatelé předpokládají a berou za samozřejmost, že vše probíhá bez velkých prodlev. Navíc požadavky na zajištění dostupností a zvládání zátěží v době špiček je stanoveno v provozních smlouvách a dokumentech SLA. Pokud jsou tato stanovení porušena, pak provozovatel informačního systému neplatí jen ušlé zisky společností, ale může navíc platit i smluvní pokuty. Na základě požadavků zajištění nabízeného výkonu je potřeba tyto vlastnosti systému otestovat. A testování je zapotřebí nejen při zavádění systému, ale třeba i při změně softwarových technologií. Podle internetového článku na System On-line (1) je zapotřebí testovat výkon v těchto případech: 1. Výkonnostní akceptace řešení 2. Optimalizace hardwarové a softwarové konfigurace 3. Zjištění limitů hardwarové a softwarové konfigurace 4. Hledání úzkých míst již provozovaného systému 5. Ověření výkonnosti po změně technologie, po upgradu na novou verzi (operačního systému, aplikace, databáze) nebo po významné změně konfigurace Na vysoké škole Unicorn College jsem se seznámil s databází Oracle a její administrací a také se způsoby testování. V obou kurzech byly probírány zátěžové testy, ale v každém z jiného úhlu pohledu. Velmi mě zaujala myšlenka spojit informace z těchto kurzů a vytvořit praktický nástroj pro generování dat pro zátěžové testy pro relační databáze.

11

2.

ZÁKLADNÍ POJMY K OPTIMALIZACI DATABÁZÍ

2.1. Databáze Data v jakémkoliv systému je potřeba perzistentně ukládat, aby bylo možné je později kdykoliv opět načíst. V jednoduchých systémech nebo domácích počítačích stačí na uložení dat souborový systém, pokud ale potřebujete například: 1. Data rychle vyhledávat 2. Přistupovat k datům paralelně 3. Pokročilejší nastavení práv přístupů k datům 4. Vybírat pouze část z celé množiny dat podle potřeb uživatele systému. V těchto případech souborový systém určitě nedostačuje a je potřeba použít databázi. Databáze slouží primárně k trvalému uložení dat. Dnes navíc poskytuje mnohé pokročilé funkce. Například zabezpečení, zálohování, archivaci, analýzu dat a další. Databáze je jedna z nejdůležitějších částí systému, a proto je potřeba tuto oblast nezanedbat. Většina lidí si pod pojmem databáze představí tabulky dat, ale toto je jen jeden typ databází, relační databáze. Tyto databáze jsou tvořeny relacemi. Tento typ databází ale není jediný, můžeme se ještě setkat s databázemi objektovými nebo objektově-relačními. Objektové databáze neukládají data do tabulek, ale přímo ukládají objekty, se kterými programátor pracuje. Proto pro programátory je mnohem intuitivnější ukládat jednotlivé objekty tříd a nemuset se zamýšlet nad tabulkami nebo převodem objektů do tabulek. Dalším možným přístupem je objektově-relační databáze. V tomto případě je potřeba nejprve nadefinovat způsob převodu mezi tabulkami a objekty. Aby bylo možné objekty do tabulky namapovat, zakládají se sloupce tabulky tak, aby odpovídaly jednotlivým atributům třídy, jejichž objekty se mají do tabulky vložit. Jednotlivé řádky tabulky pak odpovídají jednotlivým objektům. Tento přístup se snaží brát přednosti z obou zvýše zmíněných typů databází, ale v konečném důsledku je náročné samotné mapování tabulek na objekty a navíc musí programátoři popsat daný způsob mapování tabulek na objekty.

12

Tato práce se zaměřuje pouze na relační databáze, a konkrétně na relační databázi Oracle. Pro tuto databázi jsou napsány i praktické příklady, a je pro ni primárně určen i nástroj pro generování dat. Tato databáze je zároveň nejrozšířenější na trhu komerčních databází a je používána pro rozsáhlé informační systémy. V této bakalářské práci je pojem „databáze“ používán dvěma různými způsoby, a to jednak tento pojem je používán ve smyslu trvalého úložiště dat a jednak ve smyslu komplexního systému, který se stará o kompletní správu všech dat uložených v daném systému. 2.1.1. Relační databáze Základem relačních databází jsou tabulky. Tabulky se skládají ze sloupců, ve kterých jsou uloženy jednotlivé proměnné. Jednotlivé sloupce jsou pojmenovány tak, aby co nejlépe vystihly data v nich uložená. Pro každý sloupec je definovaný datový typ, ten určuje, jaká data jsou ve sloupci uložena. V jednotlivých řádcích jsou pak uložené jednotlivé záznamy. Pro přístup k těmto datům se používá speciálně navržený jazyk SQL. Ten se skládá z několika skupin příkazů, které jsou rozděleny podle svého účelu. Skupiny příkazů: 1. DDL (Data Definition Language) – příkazy pro definice struktur databáze – tabulky, indexy 2. DML (Data Manipulation Language) – příkazy pro manipulaci s daty 3. DCL (Data Control Language) – příkazy pro řízení kontroly nad daty 4. TCC (Transaction Control Commands) – příkazy pro řízení transakcí O každé skupině jazyků by mohly být napsány celé knížky. V této práci jsou jednotlivé typy příkazů popsány pouze z pohledu optimalizace. V teoretické části práce je popisována skupina příkazů DML. Hlavně je zde popsáno, jakým způsobem databáze vrací řádky pro příkaz „SELECT“. Naváže kapitola o indexech, které slouží ke zrychlení přístupu do tabulek databáze a spadají do skupiny příkazů DDL. V praktické části pak bude popsáno, jakým způsobem budou generována data pro zátěžové testy a také jak jsou vkládána do tabulek. Data v tabulkách mohou mít velmi složitou strukturu a množství dat může být velmi rozsáhlé. Pro správné navržení datových struktur je potřeba zohlednit mnoho faktorů a po samotném navržení databázového systému je následně potřeba ověřit splnění požadavků. 13

Není možné zjistit až na produkci, že byla nevhodně navržena. Proto je maximálně důležité ještě před jejím zavedením do ostrého provozu ověřit pomocí zátěžových testů, že je schopna zpracovávat požadované objemy dat. (2)

14

2.2. Zátěžové testy Testování výkonu a možné zátěže databázového systému není jednoduchá činnost a často je zanedbávána nebo nedoceňovaná. Při práci na projektu pak bývá tato oblast nejčastěji ta, která je z nedostatku buď finančních, nebo časových zdrojů zrušena. Přesto pokud je požadován kvalitní software, má tato oblast nezastupitelné místo. Univerzální návod na testování obecně bohužel neexistuje a myslet si, že informační systémy neobsahují žádné chyby, také není správný přístup. Pokud jsme tedy již rozhodnuti, že chceme testovat, musíme se následně zamyslet, co konkrétně a jakým způsobem bude testováno. Rozhodně je také důležité vědět, z jakého důvodu budou testy prováděny. Tato bakalářská práce se zaměřuje na testování výkonu a zjišťování jeho možné optimalizace. Na základě znalostí o optimalizaci je pak navržen způsob efektivního generování dat. Aby data byla věrná datům produkčním, je potřeba je vygenerovat správným způsobem. Generování dat není jediným způsobem, jak získat data pro databázi. Data je možno získat i jinými způsoby. 2.2.1. Získání dat pro testování Jednou z možností je přímo použít produkční data. Tato varianta je někdy používána, ale není úplně vhodná kvůli velkému riziku zneužití těchto dat a musí být v takovém případě přijata zvýšená bezpečnostní opatření. Toto řešení bývá voleno z důvodu jednoduchosti a rychlosti získání dat, v případech kdy potřebujeme dodat data do druhého dne. Dalším řešením je data masking. Tímto způsobem se použijí data produkční, ale znehodnocená tak, aby nebylo možné je obnovit, ale struktura těchto dat je shodná s produkčními daty. Viz obrázek 1. Jinou možností je vlastní vygenerování dat. Takto získaná data musí odpovídat datům reálným, co se týká hodnot a pravděpodobnosti opakování. Proto jsou tato data také nejvěrnější datům reálným a navíc zde nejsou bezpečnostní rizika jako u použití produkčních dat. Jinou obrovskou výhodou těchto dat je jejich volitelný rozsah. Pokud je dat nedostatek, není problém je doplnit. Toto u předešlých způsobů není možné. Na druhou stranu někdy může být obtížné získat pravděpodobnostní rozdělení dat, abychom dokázali 15

vygenerovat totožná data. Pokud ale tyto pravděpodobnosti rozdělení máme, získáváme další nezanedbatelnou výhodu, a tou je možnost vygenerovat libovolně velkou množinu dat, která odpovídá produkčním datům, a tímto způsobem se můžeme připravit na budoucí zátěž databáze. Obrázek 1: Data masking LAST_NAME

SSN

SALARY

AGUILAR

203-33-3234

40 000

BENSON

323-22-2943

60 000

D SOUSA

989-22-2403

80 000

FI ORANO

093-44-3823

45 000

BIRTH_NUMBER

SALARY

ANSKELFD

111-23-1111

40 000

BEPFZPEFZ

111-34-1345

60 000

ERPVQAJEF

111-97-2749

80 000

GEPV

111-49-3849

45 000

LAST_NAME

Zdroj: Vlastní zpracování Visio 2007 podle zdroje: http://datamart.org/2010/03/12/data-masking/

Před popisem samotného generování bude popsán způsob zpracování příkazu od uživatele přes možné teoretické optimalizace dat v databázi.

2.3. Přístupy k tabulkám Přístupy do tabulky jsou způsoby, jakými jsou procházeny tabulky a vracena uživateli požadovaná data. Aby bylo možné vykonávat zátěžové testy, je nutné porozumět, jak databáze vyhledává žádaná data. Přístupů máme několik: Full table scan (FTS) Tento přístup načítá najednou všechna data z tabulek a následně je prochází, zda odpovídají zadaným kritériím. Což samozřejmě představuje náročné I/O operace. Databáze naštěstí data nenačítá postupně ale po blocích, čímž je načítání podstatně zrychleno. Tento přístup je zvolen, pokud má být načtena například celá tabulka nebo pokud máme malé množství dat a je rychlejší je všechna načíst, než je procházet pomocí některého z indexů.

16

Pokud nebyly nad sloupci tabulky vytvořeny indexy, nebo byly vytvořeny špatným způsobem, pak se také použije tento přístup. Rowid access Tento přístup je použit v případě, když jsou rowid řádky známy a databáze načte konkrétní blok, kde se požadovaná řádka přímo nachází. Proto se nejprve zjišťují rowid řádek pomocí full table scanu nebo index scanu. Pokud by byly známy rowid řádky, mohla by být zadána v podmínce where rowid = <řádek>, a poté by nebylo zapotřebí žádné další prohledávání tabulek, ale to většinou není známo. Příklad příkazu, kdy je známa rowid a databáze může k řádku přistoupit přímo přes ně: SELECT * FROM emp WHERE rowid = chartorowid('AAAW07AAEAAAAI7AAA');

V příkazu byla použita funkce chartorowid, která slouží k převodu řetězce na rowid. Index unique scan (IUS) Index unique scan je přístup, který využívá indexů (indexy budou popsány v samostatné kapitole). Tyto indexy jsou vloženy k některému ze sloupců tabulky a databáze v nich vyhledává v případech, kdy je nad tímto sloupcem „WHERE podmínka“ a vyfiltrovaných dat je malý počet. Index range scan (IRS) Tento přístup je podobný přístupu index unique scan, jen podle indexů nevyhledáváme unikátní hodnoty, ale interval hodnot. Většinou v podmínce příkazu jsou horní a dolní omezení pro vyhledávání v indexech. Fast full index scan (FFIS) Přístup je zvolen v případě, kdy hledaná hodnota je zároveň uložena v indexu a není potřeba přistupovat do samotné tabulky. FFIS je velmi podobný přístupu Full table scan. Stejně jako FTS načítá více bloků naráz, čímž urychluje získávání dat. Pokud je zvolen tento přístup, odpadá druhá fáze rowid access, která se ve všech ostatních případech vždy vykonává.

17

Databáze, pokud přistupuje k datům, jen zřídka volí pouze jeden přístup. Většinou volí kombinaci dvou a více přístupů. Které přístupy a v jakém pořadí jsou zvoleny, vyhodnocuje optimizer. Toto vyhodnocování je prováděno pro každý DML příkaz. Popis přístupů a dalšího zpracování dat, které jsou vracena databází, popisuje podrobně exekuční plán. 2.3.1. Exekuční plán Tento plán se skládá z několika kroků a tyto kroky neobsahují pouhý výběr dat. Může se jednat i o filtrování, řazení, seskupování nebo spojování tabulek. Exekuční plán je velmi důležitý, abychom dokázali pochopit navržené postupy optimizerem a mohli identifikovat případná slabá místa. Možné akce vykonávané v databázi a zobrazené v exekučním plánu: 

Pořadí tabulek vzájemně odkazovaných, ze kterých mají být data načtena.



Způsob spojení tabulek, na základě příkazu uživatele.



Operace s daty, například filtrování, řazení nebo seskupování.

Exekuční plán je prováděn pro příkazy INSERT, DELETE, UPDATE nebo SELECT. (3) Pro zobrazení exekučního plánu stačí před příkaz napsat „EXPLAIN PLAN FOR“. Například: EXPLAIN PLAN FOR SELECT last_name FROM customers; Na následujícím obrázku je příklad exekučního plánu: Obrázek 2: Exekuční plán

Zdroj: Vlastní zpracování

18

Z uvedeného exekučního plánu vyplývají následující operace. V prvním kroku jsou tabulky spojeny, pak je použit full table access a z těchto dat je vybrána pouze určitá data podle indexu ORD_CUSTOMER_IX. V následující tabulce je uvedeno, jaké sloupce může exekuční plán obsahovat a jejich stručný význam. Tabulka 1: Popis sloupců exekučního plánu Sloupeček exekučního

Popis

plánu

Operation

Name

Prováděná operace v rámci jednoho kroku exekučního plánu. Odkaz na objekt, se kterým databáze v daném kroku pracuje.

Rows

Počet řádek načtených v rámci daného kroku.

Bytes

Počet bytů načtených v rámci operace. Relativní náročnost dané operace, velmi

Cost

užitečné pokud chceme hledat slabá místa databáze.

CPU

Operation

Name

Očekávaná

náročnost

operace

z

pohledu

procesoru. Prováděná operace v rámci jednoho kroku exekučního plánu. Odkaz na objekt, se kterým databáze v daném kroku pracuje.

Rows

Počet řádek načtených v rámci daného kroku.

Bytes

Počet bytů načtených v rámci operace.

19

Exekuční plán je velmi komplexní popis operací prováděných databází. V příkladu a popisu uvedeného exekučního plánu jsem uvedl nejzákladnější informace, abych alespoň stručně nastínil jeho možnosti. Díky exekučnímu plánu dokážeme analyzovat všechny provedené operace k danému příkazu a dokážeme zjistit, která z operací je nejnáročnější, na tu se zaměřit a vyřešit dané slabé místo databáze. Proto pokud děláme výkonnostní testy, jedním ze základních nástrojů pro zjišťování průběhu je právě exekuční plán.

20

2.4. Indexy Indexy jsou pomocné databázové objekty, které zrychlují přístup k tabulkám. Nejjednodušší vysvětlení je na příkladu rejstříku knihy. Pokud čtenář hledá v knize pojem, podívá se do rejstříku, než aby pročítal řádek po řádku celou knihu a hledal daný pojem. Na úplně stejném principu fungují i indexy. Indexy jsou také seřazeny a neobsahují odkaz na stránku, ale odkaz na rowid. Indexy nám umožňují zrychlovat vyhledávání v tabulkách, ale bohužel na úkor vkládání, úprav a mazání dat, protože nemanipulujeme pouze s daty, ale zároveň i s indexy. Indexy jsou přiřazeny vždy k jednomu nebo více sloupcům a je důležité se zamýšlet, nad kterými sloupci vytvoříme index. Pokud bychom vytvářeli indexy automaticky nad každým sloupcem, vedlo by to ke zpomalení databáze, ne k jejímu zrychlení. Indexy jsou logicky i fyzicky odděleny od dat tabulky, proto je možné je vytvářet nebo mazat aniž bychom změnili data databáze. Pokud ale smažeme index, který databáze používala k přístupu k datům, pak se prodlouží přístupová doba k těmto datům. Společnost Oracle doporučuje vytvářet indexy nad primárními klíči tabulky a nad klíči, které odkazují na jiné tabulky. Obecné doporučení zní, aby indexy byly vytvořeny nad těmi sloupci, které často používáme pro prohledávání dat v tabulce. (4) Je důležité si uvědomit, že i v případě, kdy máme index nad tabulkou vytvořený, nemusí se využít pro přístup k datům. Proto bezhlavé přidávání indexů do tabulky není žádoucí. 2.4.1. B-Tree index Tento typ indexu je nejčastějším typem a je doporučováno ho používat pro sloupce v tabulce, které obsahují velký počet řádků a mají velkou kardinalitu dat (pojem kardinality bude popsán níže). Ideální případ použití je u tabulky, která obsahuje velký počet řádků, ale na základě dotazu do této tabulky je vybrán relativně malý počet dat. V takovém případě je index efektivně využit. Nad tímto typem sloupců se doporučuje indexy vytvářet. Pro který sloupec tabulky index použít? Jednak pro sloupec, který je často používán pro prohledávání dat a jednak na základě určení selektivnosti. Pojem „selektivnosti“ velmi úzce souvisí s pojmem „kardinality“. 21

Nejprve bude vysvětlen pojem „kardinality“. Ta určuje, jak různorodá data databáze obsahuje. Typickým příkladem dat s nízkou kardinalitou je určení pohlaví, kde jsou pouhé dva typy hodnot - muž a žena. Na druhou stranu typickým příkladem dat s vysokou kardinalitou je unikátní primární klíč tabulky, který jednoznačně určuje jednotlivé řádky. V takovém případě je kardinalita vysoká a počet řádek udává počet hodnot typů dat. Proto se indexy nad primárními klíči vytváří v databázovém systému Oracle automaticky. Pojem „selektivita“ udává procentuálně, kolik je vráceno dat pro jednu unikátní hodnotu vzhledem k celkovému počtu dat v tabulce. Tedy čím je vyšší kardinalita – větší rozrůznění dat, tím je menší selektivita – menší počet dat, které lze dostat pro jednu hodnotu. (5) Nejlépe pojem „selektivity“ popíše definice:

selektivita=

počet unikátní hodnoty celkový počet záznamů

Pokud toto pravidlo je použito na výše zmíněný příklad pohlaví a pokud je předpoklad rozdělení mužů a žen 50 na 50, pak je pro muže i ženy selektivita 50 %. Pro takto vysokou selektivitu rozhodně nelze doporučit vytvoření indexu. Druhým příkladem je unikátní primární klíč, zde za předpokladu že tabulka obsahuje 100 řádků, pak selektivita vychází na 1 %. Obecně pokud má být vytvořen b-tree index, pak selektivita daného sloupce by měla být maximálně 10 %. Pro zjištění selektivity daného sloupce tabulky může být použit tento příkaz: SELECT COUNT (DISTINCT first_name) / COUNT (first_name) * 100 “selectivita” FROM emp; V uvedeném příkladu je selektivita počítána na sloupci first_name tabulky emp. Je patrné, že při generování dat je důležité zachovat pravděpodobnost výskytů jednotlivých hodnot. Proto pokud jsou vygenerována data, kde jsou pouze 3 typy hodnot a navíc každá hodnota má pravděpodobnost výskytu 1/3, žádný hodnověrný výkonnostní test s takovými daty nelze provést.

22

Bylo vysvětleno, kdy je či není vhodné b-tree index použít. V následující kapitole bude popsán způsob uložení b-tree indexu v databázi, který je důležitý pro pochopení nutnosti přepočítávání indexů. Jak název napovídá, tento index je založen na principu b-stromu. Tento typ datové struktury se skládá z kořene (root), uzlů (node) a listů (leaf). Viz obrázek 3 níže. Obrázek 3: B - tree index

kořen 122 uzly 65

136 151

listy 16 – Rowid 35 – Rowid 65 – Rowid

66 – Rowid 97 – Rowid 122 – Rowid

123 – Rowid 135 – Rowid 136 – Rowid

139 – Rowid 150 – Rowid 151 – Rowid

160 – Rowid 190 – Rowid 650 – Rowid

Zdroj: Vlastní zpracování Visio 2007 podle zdroje: http://www.dba-oracle.com/art_9i_indexing.htm

Jak kořen, tak uzly mohou obsahovat větší počet hodnot. Tyto hodnoty jsou seřazeny, a pokud má kořen (uzel) n hodnot, pak má vazby na n+1 uzlů (listů). (Obrázek b-tree indexu je trochu zjednodušený, b-tree index může obsahovat větší počet „vrstev“ uzlů.) Hodnoty v dětech (podřízených uzlech/listech) mají stejnou nebo nižší hodnotu, pokud jsou vlevo a vyšší hodnotu pokud jsou vpravo oproti svému předchůdci (uzlu/kořenu, ze kterého vazby vycházejí). Na základě těchto pravidel se vytváří datová struktura b-strom. Výpočet hloubky jednotlivých větví (počet zanoření od kořene k listu) je trochu složitější a není předmětem této bakalářské práce. Jen pro úplnost doplním, že pro efektivní vyhledávání v b-stromu je potřeba, aby hloubka stromu jednotlivých větví se vzájemně lišila maximálně o 1. Pokud tento rozdíl hloubky v jednotlivých větvích b-stromu je výrazně větší, pak je potřeba indexy přepočítat. Jednotlivé hodnoty se v b-stromu vyhledávají tak, že se strom postupně prochází od kořene a porovnávají se jednotlivé hodnoty s hodnotou hledanou. V momentě kdy se dojde do

23

nejnižší části b-stromu do listů, tak se zde hledaná hodnota buďto nenachází nebo nachází a v listě je odkaz na rowid řádku, kde je hodnota uložena. Teď je možné si uvědomit, proč je neefektivní vytvářet b-tree index nad sloupci s nízkou kardinalitou. Pokud by se nad sloupcem pohlaví vytvořil b-tree index, pak by v tomto bstromu byl pouze kořen a dva listy, viz obrázek 4. Každý z těchto listů by obsahoval polovinu řádků tabulky, které by vraceli při zvolení dané hodnoty. Ve výsledku by tedy byl zvolen nejprve přístup indexový a následně by byla data postupně procházena. V takovém případě by bylo buď vhodnější nepoužít index žádný, pak by databáze k datům přistupovala přes full table scan nebo by byl použít bitmap index (popsán v následující kapitole). Tento příklad je pouze hypotetický, protože pokud by byl vytvořen index nad těmito daty, optimizer by spočítal neefektivnost indexového přístupu a použil by rovnou full table scan. Obrázek 4: B - strom s nízkou kardinalitou

Pohlaví

Muž

Žena

Zdroj: Vlastní zpracování Visio 2007

B-tree indexy můžeme vytvářet různými způsoby. Jedním ze způsobů je kompozitní index. Kompozitní index Tento typ indexu se vztahuje ke dvěma sloupcům. Je vhodný pro případy, kdy často používáme kombinaci dvou sloupců současně pro vyhledávání. Ale pozor, na druhou stranu je mylné se domnívat, že pokud je zvolen kompozitní index, tak že může být použit stejně, jako dva nezávislé indexy nad dvěma sloupci. Navíc při volbě sloupců záleží na tom, v jakém pořadí je zvolíme. (6) Uvedu a popíšu příklad kompozitního indexu: CREATE INDEX composite_index ON emp (surname, first_name); 24

V tomto příkladě je vytvořen index, který je nad sloupci surname a first_name a pokud je použit příkaz: SELECT * FROM emp WHERE surname='Novak' AND first_name='Jan'; je použit index tak, jak předpokládáno. V případě, že by bylo potřeba vyhledávat pouze podle jména, například takto: SELECT * FROM emp WHERE first_name='Jaroslav'; index nebude použit! Index nebo spíše jeho část se použije pouze v případě, kdy je v tabulce vyhledáváno pouze podle příjmení. Kompozitní indexy se mohou skládat i z více než dvou hodnot a to tak, aby tyto skupiny sloupců co nejvíce usnadňovaly vyhledávání. Proto se doporučuje používat kompozitní indexy tak, aby sloupce v nich obsažené byly řazeny sestupně podle důležitosti (četnosti použití). Indexovaná funkce Dalším způsobem jak vytvořit b-tree index je indexovaná funkce, která umožňuje vytvořit index nad hodnotou vrácenou z funkce. Příklad: CREATE INDEX function_index ON emp(lower(surname)); V tomto příkladě se vytváří index nad funkcí lower, díky které se zjednoduší vyhledávání příjmení. Lze vynechat kontrolu, zda nebyla příjmení zadána s velkým písmenem. Ukázka dotazu nad tabulkou: SELECT first_name, surname FROM emp WHERE lower(surname) = 'muller'; Obecně indexové funkce zjednodušují používání funkcí v rámci dotazů nad tabulkami. Mohou se tak jednoduše třídit data, dále se nemusí řešit velká a malá písmena v zadaných datech nebo se mohou přidat vlastní funkce, které zjednoduší zpracování dat. Používání indexů má jeden háček, a tím je hledání řádků, které obsahují hodnotu null. Pokud se zadá příkaz, který má vrátit všechny řádky, kde v některém ze sloupců je 25

podmínka null, vždy databáze k tabulce přistoupí přes full table scan. Tento háček lze jednoduše řešit pomocí indexových funkcí. Tato funkce nahradí null hodnotu řetězcem 'null', následně jsou pak hledány v tabulce řetězce ‘null’. Předpokladem je, že se jedná o proměnnou typu řetězec. U sloupců s jinými datovými typy by bylo řešení analogické. Toto nahrazení můžeme provést pomocí funkce nvl, která vrací předdefinovanou hodnotu, pokud jí nebyla předána žádná jiná hodnota. Ukázka použití pomocí index funkce: CREATE INDEX indx_find ON emp(nvl(degree, 'null')); Díky takto použité indexové funkci je možné za pomocí indexů najít uživatele, kteří nedosáhli žádného titulu (degree). Příklad: SELECT first_name, surname FROM emp WHERE nvl(degree, 'null') = 'null'; I když je vytvořen index nad sloupcem správným způsobem, nemusí být použit pro vrácení dat. Databáze přístupy k datům vyhodnocuje a vrátí vždy data předpokládaným nejefektivnější způsobem. Výběr nejefektivnějšího přístupu k datům obstarává optimizer (popsán v samostatné kapitole). Proč databáze nevolí přístup přes b-tree indexy, i když se to může zdát výhodné? Důvody: 1. Malé množství řádků – je rychlejší přistupovat k malému množství dat přes full table scan než přes index scan 2. Velké množství řádků, ale načítání velkého množství dat – i v tomto případě je výhodnější použít full table scan, protože FTS načítá data po blocích na rozdíl od IRS, který načítá data sekvenčně Ze zmíněných důvodů je zřejmé, že je důležité nejen volit indexy pro správné sloupce, popřípadě kombinace sloupců, ale i správně psát dotazovací příkazy pro databázi. Dále je velmi důležité nepodceňovat správu a údržbu databáze, díky které ji pak můžeme snadněji optimalizovat nebo odstraňovat vzniklé problémy. 2.4.2. Bitmap index Dalším typem indexu je bitmap index. Tento typ indexu je stejně jako b-tree index uložen v b-stromu. Tento index je vhodný pro tyto typy dat:

26



Data s nízkou kardinalitou.



Data, kde převládají read-only operace nebo úpravy dat jsou jen výjimečné.



Data, kde často vyhledáváme pomocí OR podmínek.

Jak bylo uvedeno, bitmapové indexy jsou uloženy také v b-stromu. To zaručuje rychlé vyhledávání jednotlivých hodnot. V čem je ale tento index od b-tree indexu odlišný, je způsob uložení hodnot v listech b-stromu. V listu je uložena bitmapa hodnot místo seznamu rowid. Tato bitmapa se vztahuje k zadanému rozsahu řádků, které jsou uloženy v paměti. V bitmapě má nastavený bit na „1“ ten řádek, který zadanou hodnotu obsahuje a „0“ ten řádek, který zadanou hodnotu neobsahuje. Princip bitmap indexu viz Obrázek 5. Obrázek 5: Bitmap index

Zdroj: http://www.siue.edu/~dbock/cmis565/module12-indexes.htm

Vysvětlení principu tohoto indexu bude nejjednodušší na příkladu. Snažíme se najít všechny muže v naší firmě. Průchod b-stromem bude rychlý, jsou v něm pouze dva listy. V listu s hodnotou „muže“ bude bitmapa mapující hodnoty v paměti a bude obsahovat jedničky tam, kde se hodnota muž nachází a nuly, kde se nenachází. Bitmapa nám vrátí sadu řádek, které obsahují hledanou hodnotu. Jak tento index vypadá, je možno se podívat 27

na obrázku níže, kde je podrobně rozkreslen. Díky tomuto nízko úrovňovému přístupu k datům (přístup přes bitmapu) je načítání dat rychlejší. Způsob uložení a pracování s bitmapou je speciálně navržen společností Oracle a patentově chráněn. (4) Jediným háčkem bitmapových indexů je jejich případná úprava. Sice není tak velký problém data měnit z jedné hodnoty na jinou. To znamená pouhou změnu bitů z nuly na jedničku. Ale pokud bude potřeba přidat novou hodnotu do sloupce, pak se musí přepočítat úplně celá indexová struktura. To není jednoduchá operace. Z tohoto vyplývá nemožnost použití těchto indexů v OLTP databázích, které jsou zaměřeny na snadnou modifikaci dat. 2.4.3. Indexově organizované tabulky (Index organized tables) Indexově organizovaná tabulka se vzdáleně týká indexů a jedná se o speciální strukturu dat, kam se ukládá tabulka. Ale na základě principu indexů byla vyvinuta. Tento typ tabulky vznikl na základě těchto myšlenek: 

Je neefektivní vytvářet index nad každým sloupcem tabulky



Zároveň byl využit koncept FFIS (Fast full index scan), kdy přístup přes indexy k datům je velmi rychlý.

Tak vznikl koncept index organized table, kdy řádky tabulky nejsou uspořádány podle rowid, ale jsou vloženy do b-stromu, kde jsou uspořádány podle primárních klíčů. Zároveň v každém listě jsou uloženy i neklíčové hodnoty řádku, proto přes primární klíč přistupujeme rovnou ke všem hodnotám. To zaručuje velmi rychlý a efektivní přístup. Bohužel tento koncept má i nějaké nedostatky. Například tento typ tabulek nelze ukládat do clusterů. (4) Pokud by se měřila rychlost přístupů, mohlo by být na první pohled zarážející, že tabulky s indexy oproti index organized table nejsou zas o tolik pomalejší. Pak by jejich jedinou praktickou výhodou byla úspora místa. Protože indexově organizovaným tabulkám stačí ukládat jedinou strukturu dat oproti ukládání samotných dat a navíc jejich indexů. Ovšem index organized table lze dále optimalizovat, a to následujícími způsoby:

28



Dodefinováním overflow area (oblast přetečení), díky které je v listě uloženo jen omezené množství dat a data, která se do listu nevejdou, jsou uložena na jiném místě.



Přidáním sekundárních indexů, které nám dále zrychlují přístup k datům.

Využití tohoto typu tabulek je doporučováno pouze pro kratší řádky, kde v jednotlivých řádcích není uloženo větší množství dat. Částečně lze toto optimalizovat pomocí overflow area, ale i přesto je lepší používat tyto tabulky spíše pro ukládání krátkých řádků. Dalším omezením je nemožnost ukládat datové proměnné typu long, místo něj musíte použít datový typ lob. (7)

29

2.5. Optimizer Aby byla jasná úloha optimizeru v rámci zpracování sql příkazu, bude nejprve popsán obecný popis komponent, které s optimizerem souvisí. Nejprve uživatel zadá sql dotaz, ten je zkontrolován parserem z hlediska korektnosti syntaxe, zda dotaz obsahuje všechny povinné položky a zda mají korektní formu. Pak přichází na řadu optimizer, který vybere způsob optimalizace vykonání dotazu na základě toho, zda existují statistiky nebo ne. Pokud statistiky vygenerovány v databázi jsou, použije se výpočet optimální cesty pomocí CBO (Cost-Based Optimizer) přes statistiky. Pokud ne, CBO použije dynamic sampling pro dopočítání chybějících nebo neúplných statistik. V případě požadavku na zpětnou kompatibilitu může být zvolen i druhý typ optimizeru – RBO (Rule Based Optimizer). (8) Obrázek 6: Diagram zpracování sql dotazu

Zdroj: http://docs.Oracle.com/cd/B10501_01/server.920/a96533/optimops.htm

30

Optimizer vrací návrh nejefektivnějšího způsobu jak načíst data z databáze. Následující komponenta Row Source Generator na základě tohoto vygenerovaného postupu vrací exekuční plán. Exekuční plán popisuje podrobně načítání a zpracování dat. Posledním logickým krokem je vykonání příkazu podle exekučního plánu pomocí SQL Execution Engine („stroje pro vykonání sql“). Postupné kroky tohoto procesu je možné si prohlédnout na obrázku 6 diagramu zpracování sql dotazu. Databáze může volit různé přístupy k tabulkám. Může být zvolen přístup full table scan nebo některý z indexových přístupů. Optimizer volí na základě znalosti tabulek z těchto přístupů ten nejefektivnější. V jeho konfiguraci můžeme zvolit jeden ze dvou modů cílů optimalizace. A to buď nejlepší propustnost (the best throughput) nebo nejlepší dobu odezvy (the best response time). Na příkladech bude vysvětlen rozdíl těchto dvou modů. (9) Pokud zvolíme nejlepší propustnost, optimizer volí takové přístupy, aby všechna data dostal uživatel co nejdříve. Tento model je volen tehdy, kdy uživatel požaduje ihned načíst všechna data z databáze v nejkratším možném intervalu. Typickým a často voleným přístupem v tomto modu je full table scan, který dokáže data načítat po blocích, a tím načíst velmi rychle velké množství dat. Druhý mode (nejlepší doba odezvy) je volen tehdy, kdy uživatel systému chce mít k dispozici co nejdříve první řádek nebo prvních pár řádků. Typickým přístupem, který je volen v tomto modu, je index scan. Tento přístup je schopen velmi rychle vrátit první požadovaný řádek. Z řádků napsaných výše by mohlo vyplývat, že přístup full table scan je výhradně volen v modu optimizeru „nejlepší propustnost“ a index scan v modu „nejlepší doba odezvy“. Takto přístupy voleny nejsou. Optimizer má nastaveny určité preference, díky kterým volí jednotlivé přístupy, ale rozhodně nejsou v jednotlivých modech voleny pouze některé přístupy, to by databázový přístup k datům nebyl dostatečně flexibilní a optimalizovatelný. 2.5.1. RBO (Rule based optimizer) a CBO (Cost based optimizer) Jak bylo zmíněno výše, Rule based optimizer je volen z důvodu požadavku zpětné kompatibility. Naproti tomu Cost based optimizer je novější a technologicky vyspělejší. I společnost Oracle doporučuje používat CBO. RBO by měl být použit pouze ve 31

výjimečných případech a můžeme očekávat, že v budoucích verzích bude z databáze odstraněn. Navíc Cost based optimizer podporuje rozdělené tabulky, materializované pohledy a další. Krátce k Rule based optimizeru: Tento typ optimizeru je založen na pravidlech. Jednotlivé přístupy jsou vybírány podle pořadí, a zda je možné je nad tabulkou vykonat. Tedy pokud je možné zvolit více přístupů, je zvolen ten, který je v pořadí dříve. Část seřazeného seznamu přístupů, tak jak jej využívá RBO: Tabulka 2: Pořadí přístupů, které jsou voleny v RBO

Pořadí

Přístup

1.

Jednotlivé řádky přes Rowid

... Jednotlivé řádky přes 4.

unikátní hodnotu nebo primární klíč

... 8.

Přes kompozitní indexy

9.

Indexový přístup

... 15.

Přes full table scan

Z tabulky je tedy patrné, že pokud bude možné zvolit přístup přes index, bude vždy zvolen, a to i v případě, kdy by byl přístup přes full table scan efektivnější. Proto je doporučováno tento typ optimalizátoru nepoužívat. (10)

32

Cost based optimizer je založen na ohodnocování jednotlivých přístupů do databáze na základě statistik tabulek, sloupců, indexů a dalších (statistiky popsány v samostatné kapitole). Na základě hodnot těchto statistik CBO navrhne exekuční plány a zároveň je ohodnotí. Databáze pak z plánů vybere ten nejvýhodnější, který je proveden pro zpracování zadaného příkazu. Součástí hodnocení statistik není jen složitost datové struktury databáze, ale i možnosti jednotlivých zdrojů hardwaru serveru, například propustnost I/O nebo výkonnost CPU. (8) Samotný CBO se skládá z několika částí, které si vzájemně dělí jednotlivé činnosti zpracování hodnocení přístupů. Popis těchto částí je však nad rozsah této práce.

33

2.6. Statistiky Statistiky jsou pomocná data, která podrobně popisují strukturu databáze. Popisují tabulky, sloupce, indexy atd. Na základě těchto dat, je pak optimizer (konkrétně CBO) schopen najít nejefektivnější exekuční plán. Statistiky popisují různé objekty v databázi a podle toho se dělí na: 

statistiky tabulek (table statistics),



statistiky sloupců (column statistics),



statistiky indexů (index statistics),



statistiky výpočetních možností serveru (System statistics).

Ke spravování statistik se používá balíček DBMS_STATS. Tento balíček neslouží jen ke sběru statistik, ale také k importu, exportu popřípadě i jejich smazání nebo zamknutí, pro nemožnost jejich úpravy. Jednoduchý export nebo import statistik je umožněn díky tomu, že statistiky jsou uloženy v databázových slovnících (11). Je zřejmé, že data se v databázi často mění a pokud by jednou vygenerované statistiky měly být používány delší dobu, ztratila by se jejich schopnost optimalizovatelnosti. Proto je důležitá jejich pravidelná správa. O tu nejzákladnější se stará automatic optimizer statistics collection. Tato funkcionalita se aktivuje následujícím příkazem: EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); Tato komponenta monitoruje počet operací INSERT, UPDATE, DELETE a pokud je vyhodnoceno, že statistiky již neodpovídají datům, jsou přepočítány. Většinou se statistiky přepočítávají, pokud se změní víc jak 10 % dat v tabulce. Tato automatická detekce je postačující, pokud není databáze z velké části neustále modifikována. Pokud ano, je pak důležité statistiky aktualizovat navíc manuálně. Manuální sběr statistik je nutný v případě, kdy třeba změníme podstatnou část tabulky pomocí bulk operací. (Pojem bulk operace je vysvětlen v kapitole 3.1. Úvod do PL/SQL.) Proto je nutné 34

při těchto operacích nezapomínat na statistiky. Vynucení přepočítání statistik způsobí následující příkaz: EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>tableowner, tabname=>'tablename', method_opt=>'FOR ALL INDEXED COLUMNS'); Na základě tohoto příkazu jsou přepočítány statistiky nad tabulkou „tablename“ vlastněnou uživatelem „tableowner“. Statistická data jsou posbírána ze všech jejích sloupců. Tento příkaz patří do skupiny statistiky tabulek. V příkladu je volána metoda GATHER_TABLE_STATS.

Pokud

by

byla

použita

jiná

procedura

balíčku

DBMS_STATS, například procedura GATHER_SYSTEM_STATS, pak by došlo k vygenerování systémových statistik. U této procedury by samozřejmě byly nastaveny jiné parametry (11). V ukázkových příkladech byl vždy použit balíček dbms_stats, který ale není jediný, který můžeme pro práci se statistikami volat. Ale jeho používání je doporučováno společností Oracle. Kromě tohoto balíčku lze například použít příkaz ANALYZE, který je v databázovém systému udržován již jen z důvodů zpětné kompatibility. Na konci této kapitoly jsou zmíněny funkcionality, které se statistikami souvisí už jen okrajově, ale jsou důležité pro pochopení souvislostí. Jedná se o dynamic sampling a histogramy. Dynamic sampling Tato funkcionalita byla zavedena od verze 10g a vyřešila velký problém optimalizace pro tabulky, kde nejsou vygenerované statistiky. Do této verze v takovém případě byl použit RBO, který nemusel vrátit optimální exekuční plán. Dynamic sampling umožňuje dopočítat chybějící data statistik a poskytnout tak v každém případě co možná nejvíce optimální exekuční plán. CBO se během každé kompilace dotazu rozhoduje, do jakého stupně použije dynamic sampling. Toto rozhodování je založeno na mnoho faktorech. Ale hlavním důvodem proč optimizer volí tuto metodu, jsou zastaralé nebo chybějící statistiky. (8) 35

Histogramy Histogramy jsou speciálním druhem statistik a jsou zde uvedeny pro úplné doplnění výčtu. Histogramy jsou důležité pro případy, kdy potřebujeme zjistit přesnější rozmístění dat v databázi. Tento typ statistik nám dává přehled, jak jsou data asymetricky rozložena a na základě těchto dat se optimizer rozhoduje, který přístup zvolí.

36

2.7. Hinty Hinty nám umožňují ovlivňovat běh exekučního plánu. Je možné tak databázi nadefinovat, který přístup má zvolit. Vychází z předpokladu, že uživatel má více informací o databázi než optimizer, proto může zvolit efektivnější přístup. Například máte podrobnější informace o selektivnosti indexů pro určitý SQL příkaz. Hinty mohou být vyvolány jen v případě, kdy používáte CBO a pokud je zadáte v příkazu ve špatném formátu, pak budou ignorovány a neprovedou se. Pozor, optimizer neupozorňuje na to, že nebyly použity. Hinty se zadávají do SQL příkazů za příkazy UPDATE, SELECT nebo DELETE. Přes hinty lze ovlivnit: 

Přístup k tabulkám.



Jakým způsobem se mají tabulky spojit.



Určit pořadí spojení tabulek.



Nastavení optimalizačních parametrů.

Příklad použití hintu: SELECT /*+ FULL(users) */ firstname, surname FROM users WHERE firstname = 'Hana'; O tabulce users víme, že obsahuje velký počet uživatelů s křestním jménem „Hana“, proto pomocí hintu optimizer nastavíme tak, aby k této tabulce přistoupil přes FTS, který v tomto konkrétním případě bude nejrychlejší. (12)

37

3.

PRAKTICKÁ ČÁST - GENEROVÁNÍ DAT

Praktická část bakalářské práce se zaměřuje na samotný popis „Nástroje na tvorbu testovacích dat pro zátěžové testování DB“. V této části bude popsán jazyk PL/SQL, ve kterém je nástroj napsán. Dále zde je zmíněno, odkud byla získána zdrojová data a jak byla upravena. A v neposlední řadě bude rozebrán princip, jakým nástroj generuje náhodná data pro zátěžové testy. Vysvětlení principů je doplněno o ukázky zdrojového kódu nástroje.

3.1. Úvod do PL/SQL V kapitole „Relační databáze“ byl zmíněn jazyk SQL a skupiny příkazů, které obsahuje. Díky tomuto jazyku je možné navrhnout i spravovat databáze. Pokročilejší programování nebo práce s větším množstvím dat ale není možná. Pro tyto případy společnost Oracle vyvinula procedurální nadstavbu jazyka SQL, PL/SQL. Jiné databázové technologie mají také své procedurální jazyky, například Microsoft SQL server má Transact SQL neboli T-SQL. (13) Tento krátký úvod se zaměřuje na základy programovacího jazyka PL/SQL, které jsou nutné pro následné pochopení principu generátoru dat pro zátěžové testy.

3.1.1.

Balíčky, procedury a funkce

Program v PL/SQL je složen z bloků, které mohou být vnořeny jeden do druhého. Každý blok spouští jednu logickou akci v programu. Blok se skládá z následujících částí: a) DECLARE – v této části bloku jsou definovány jednotlivé proměnné a jejich datové typy b) BEGIN – výkonná sekce – v této části se vykonávají funkce, procedury a SQL příkazy (tato jediná část je v bloku povinná) c) EXCEPTION – v této části se zpracovávají výjimky, ke kterým došlo ve výkonné části Ve výkonné části je povolena skupina příkazů DML, to znamená SELECT, INSERT, 38

UPDATE, DELETE a další příkazy pro správu transakcí. Příkazy pro definování struktury databáze (příkazy DDL) například CREATE, ALTER nejsou ve výkonném bloku povoleny. (14) Tímto způsobem jsou definovány tak zvané anonymní bloky, které jsou zpravidla vykonány pouze jedenkrát. Dalším typem bloků jsou pojmenované bloky. Pojmenované bloky je možné opakovaně používat a tyto bloky jsou procedurami a funkcemi, které jsou známy ze strukturovaného programování. Jen pro zpřesnění: rozdíl mezi funkcí a procedurou je ten, že funkce na rozdíl od procedury vrací výsledek. V dalším textu jsou zmiňovány jen funkce, ale jsou tím míněny funkce i procedury. (15) Příklad jedné funkce z praktické části práce: CREATE OR REPLACE FUNCTION my_divide (n1 IN number, n2 IN number) RETURN NUMBER IS n3 NUMBER; n4 NUMBER; BEGIN n4 := MOD(n1,n2); n3 := (n1-n4)/n2; IF n4 > 0 THEN n3 := n3+1; END IF; RETURN n3; EXCEPTION WHEN ZERO_DIVIDE THEN RETURN n1; END; V praktické části práce byla potřeba funkce, která by dělila dvě čísla tak, aby vrácený výsledek byl vždy zaokrouhlen nahoru. Standardní funkce pro dělení výsledek automaticky zaokrouhluje nahoru nebo dolů, podle hodnoty za desetinnou čárkou. Funkce my_divide převezme dva parametry n1 a n2. Nejprve zjistí celočíselný zbytek po dělení n1, n2. Tento zbytek je poté odečten od n1 a upravená hodnota n1 je dělena hodnotou n2, pokud byl celočíselný zbytek větší než jedna, je k výsledku přičtená jednička. 39

Tento příklad obsahuje všechny možné části, které byly pro blok definovány, a je na něm vidět způsob použití jednotlivých bloků. Další výhodou definovaných funkcí je možnost jejich použití přímo v SQL příkazech. Například: SELECT CONCAT (first_name, surname) ‘whole name’ FROM employees; Tento příkaz vrátí místo dvou sloupců jména a příjmení sloupec jeden, který obsahuje celé jméno. Jiným způsobem je použití funkcí v rámci indexů. Příklady, jak použít funkce v indexech, byly popsány v kapitole „Indexovaná funkce.“ Bylo popsáno, jak vypadají anonymní a pojmenované bloky používané v programovacím jazyce PL/SQL. Pokud by byly používány buď jen funkce, nebo dokonce pouze anonymní bloky, bylo by náročné se v kódu vyznat. Proto jsou v tomto jazyce zavedeny balíčky, které obsahují logicky související funkce a procedury. Balíčky se skládají z hlavičky a těla. V hlavičce se deklarují veřejné funkce a v těle jsou pak definovány funkce soukromé. Soukromé funkce jsou deklarované i definované v jeho těle a nejsou přístupné z vnějšku. Veřejné funkce pak mohou být volány uživatelem nebo funkcemi i z jiného balíčku. Příklad jednoduché definice hlavičky a těla: CREATE PACKAGE my_package IS FUNCTION sum (number1 NUMBER, number2 NUMBER, number3 NUMBER); END my_package; CREATE PACKAGE BODY my_package IS FUNCTION sum (number1 NUMBER, number2 NUMBER, number3 NUMBER) IS BEGIN RETURN number1 + number2 + number3; END; END my_package;

40

Uvedený balíček obsahuje jedinou veřejnou funkci, která slouží pro sečtení tří čísel. Tento balíček neobsahuje žádnou soukromou funkci. Tento příklad je pouze názorný, a pokud by měla být v balíčku pouze jedna veřejná funkce, nemělo by význam balíček zakládat. Generátor náhodných dat, který je předmětem této bakalářské práce, je napsán pomocí funkcí, které jsou vloženy do balíčků. Jejich přehled: 1. fetch_source_data_package – balíček sloužící k načtení zdrojových dat, na základě kterých se generují náhodná data, 2. log_package – balíček sloužící k logování událostí vzniklých během procesu generování dat, 3. random_data_package – balíček ke generování specifických dat, která mají specifické omezující parametry (například generování rodného čísla), 4. random_table_package – balíček, který slouží k vytvoření tabulky a následnému jejímu naplnění generovanými daty, tento balíček je stěžejním a jednotlivé funkce a procedury z ostatních balíčků jsou volány odsud. Pro zrychlení operací s velkým množstvím dat nabízí PL/SQL bulk operace. Tyto operace snižují počet přepínání mezi enginem pro SQL a enginem pro PL/SQL. Jednotlivé enginy slouží k vykonávání buď SQL nebo PL/SQL kódu. Princip přepínání enginů viz obrázek 7. Obrázek 7: PL/SQL a SQL engine

Zdroj: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm

41

Pro snadnější pochopení významu bulk operací bude vysvětlen jejich princip zpracování příkazů na příkladu cyklu. V každé iteraci cyklu je vložen jeden řádek do tabulky pomocí příkazu INSERT, dochází tak k neustálému přepínání mezi enginy. Bulk operace v tomto případě nejdříve nahromadí data pro vložení a následně je všechna vloží, proto je provedení pomocí bulk operací mnohonásobně rychlejší. V praktické části jsou používány bulk operace pro zefektivnění nástroje a optimalizované generování dat. Toto byl krátký úvod do programování v jazyce PL/SQL, který má sloužit pro usnadnění orientace v praktické části bakalářské práce. V následující kapitole již bude popsán samotný kód „Nástroje pro generování dat pro zátěžové testy relačních databází“. (15)

42

3.2. Princip generování Před samotným vysvětlením principu generování dat bude popsáno, jak byla získána a zpracována zdrojová data, pomocí kterých se výsledná data generují. Bez těchto zdrojových dat by nebylo možné generovat tak, aby vygenerované tabulky odpovídaly realitě. Zdrojová data pro generování

3.2.1.

Data byla získána ze stránek Ministerstva vnitra České republiky. Tabulka 3: Internetové stránky, ze kterých byla získána zdrojová data

Internetová stránka

Data

www.mvcr.cz/clanek/cetnost-jmen-a-

Četnosti jmen, příjmení a roků narození,

prijmeni-722752.aspx

počet obyvatel ve městech

http://aplikace.mvcr.cz/adresa/xml.html Adresy v ČR Data byla po stažení převedena do formátu csv. Ze zdrojových dat byly vytvořeny tyto soubory: 

mesta.csv – seznam měst ČR s počty obyvatel,



roky_narozeni.csv – rozdělení počtu obyvatel ČR podle roku narození,



zrjs_jmena.csv – počty obyvatel s křestními jmény v ČR,



zrps_prijmeni.csv – počty obyvatel s příjmeními v ČR,



…

V těchto souborech byla ponechána pouze data, která jsou použita pro samotné generování, ostatní informace byly smazány. Například v souboru „mesta.csv“ jsou pouze dva sloupce, a to názvy všech měst a jejich počty obyvatel. Podobné jsou struktury i ostatních souborů. Všechny soubory se zdrojovými daty jsou součástí přílohy. Upravené soubory se zdrojovými daty jsou postupně importovány do tabulky „basic_source“. Tato tabulka je velmi jednoduchá, obsahuje pouze tři sloupce. Sloupec pro klíčové hodnoty (názvy měst, křestní jména atd.), sloupec pro četnosti jednotlivých hodnot

43

a sloupec pro typ pohlaví pro rozlišení jmen mužských a ženských. Jednotlivé soubory dat (města, příjmení atd.) jsou dále přesunuty z tabulky „basic_source“ do tabulky source_data, o který se stará procedura migrate_data. Při tomto převodu dat se v tabulce source_data k jednotlivým datům každé skupiny vloží jejich typ. Hodnota tohoto typu dat je předána proceduře vstupním parametrem, která ji vkládá do sloupce type_data. Pojem typ dat je dále často v textu používán a jsou jím míněna tato data: 

příjmení,



křestní jména,



města,



předvolby mobilních čísel,



roky narození.

Sloupec type_data je používán při každém generování dat pro získání jednoho typu dat, proto je důležité nad tímto sloupcem vytvořit index. B-tree index v tomto případě není úplně vhodný pro nízkou kardinalitu dat, jejíž hodnota je pouze pět. Pro tento sloupec se výborně hodí index bitmapový, jednak proto, že data mají nízkou kardinalitu a jednak proto, že předpokládáme nízkou frekvenci změn těchto dat. Díky tomuto indexu je načítání dat z tabulek rychlejší. Jak toto načítání dat funguje, bylo zmíněno v odstavci o indexech. S tabulkou source_data pracuje balíček fetch_source_data, který je popsán v následující kapitole. Jen pro úplnost zmíním na závěr této kapitoly tři tabulky, které jsou také používány jako zdroje dat. Jedná se o tyto tabulky: towns, streets a house_numbers, v nichž jsou uloženy adresy až na úroveň popisných čísel domů a jsou používány pro generování reálných adres ČR. 3.2.2.

Balíček fetch_source_data

Balíček fetch_source_data obsahuje funkce, které načítají zdrojová data do kompozitní proměnné, kterou funkce vrací. V této datové struktuře nejsou již data uložena s četnostmi, ale hodnoty jednoho typu dat jsou v tabulce uloženy několikrát, a to v závislosti na četnostech, které jsou navíc děleny. Díky dělení četností je omezen rozsah dat. Dělitel četností je odhadován na základě množství dat daného typu a jejich četností.

44

Následující zdrojový kód je jednou z implementovaných funkcí balíčku fetch_source_data: function source_for_random (source_type in varchar2, divide in NUMBER) RETURN type_random IS CURSOR cursor_c(data_type varchar2) IS SELECT * FROM source_data WHERE type_data = source_type; j number(10,0); repetition number(10,0); random_data type_random; BEGIN random_data := type_random(); -- external loop for retrieving the individual values -- of one data type FOR data_record IN cursor_c(source_type) LOOP j := random_data.count; repetition := data_record.count_data/divide; random_data.extend(repetition); -- inner loop for inserting a single value based on -- its frequency FOR i IN 1..repetition LOOP random_data(suma(j,i)) := data_record.data1; END LOOP; END LOOP; RETURN random_data; END source_for_random; Funkce přijímá jediný parametr, který je určen pro identifikaci typu dat, která mají být načtena z databáze. Funkci tohoto balíčku využívá balíček package_random_table, který pomocí funkce source_for_random inicializuje své proměnné, v kterých jsou hodnoty jednotlivých typů 45

dat určené pro konečné generování. Než přejdeme k balíčku package_random_table, bude popsán ještě jiný balíček, který slouží pro generování dat se specifickými vlastnostmi. 3.2.3.

Balíček random_data

Tento balíček slouží pro generování rodných čísel a celých adres. Tato data nelze pouze vytáhnout z předpřipravených dat, ale je potřeba nejprve část dat vygenerovat a následně je dopočítat podle omezujících podmínek. Generování rodných čísel Rodné číslo se skládá z deseti čísel, kde prvních šest označuje den narození a další čtyři jsou přidělována jednotlivým porodnicím. Toto desetimístné číslo musí být navíc dělitelné jedenácti. Pomocí rodného čísla lze rozlišit muže a ženu. Žena v rodném čísle má k měsíci narození přičteno číslo padesát. Všechny tyto podmínky funkce pro generování rodných čísel splňuje. Generování rodných čísel funguje na tomto principu: Funkce dostane parametrem rok narození a pohlaví generované osoby. Z těchto parametrů generuje náhodný den v roce, kde zohledňuje přestupné roky a následně dopočítá rodné číslo tak, aby bylo dělitelné jedenácti. Toto vygenerované rodné číslo je funkcí vráceno. Roky narození, které jsou předávány funkci jako parametr, jsou generovány podle četností věkových skupin obyvatel ČR, proto rozdělení věkových skupin vygenerovaných dat odpovídá reálnému rozdělení věkových skupin obyvatel ČR. Následující ukázka kódu je vyňata z funkce pro generování rodných čísel a je ukázkou funkce, která generuje poslední čtyřčíslí tak, aby rodné číslo bylo dělitelné jedenácti. LOOP b_number_end := dbms_random.value(1,999); birth_number := b_year * 10000000 + b_month * 100000 + b_day * 1000 + b_number_end; divide_modulo := mod(birth_number,11); IF divide_modulo != 10 then 46

birth_number := birth_number * 10 + divide_modulo; exit; END IF; END LOOP; Kód předcházející ukázanou část obstarává vygenerování reálného data. Generování adres Funkce generuje kompletní adresu, jediným jejím vstupním parametrem je město. Tomuto městu je náhodně vygenerována jedna existující ulice tohoto města a jedno existující číslo popisné, které reálně existuje v dané ulici. Města předávaná funkci jsou generována na základě počtu obyvatel jednotlivých měst ČR. Vygenerované adresy jsou proto reálné a existují na území ČR. Ve vygenerovaných datech tedy logicky budou nejčastěji adresy Prahy, kde žije nejvíce obyvatel. Generování adres má drobný nedostatek, který je zapříčiněn odchylkou od reálných adres. Ze zdrojových dat Ministerstva vnitra ČR nelze zjistit četnosti obyvatel v daných ulicích a domech označených číslem popisným, proto zde jsou možné nesoulady. Je možné, že některý reálný malý rodinný dům je považován za panelový dům a obráceně. Přesto generování celých adres je velmi věrné realitě. Drobný nesoulad je dán omezeností zjistitelných údajů a vůbec ničemu nevadí. Tyto jednotlivé funkce, které vracejí pouze určitá vygenerovaná data, jsou volána hlavním balíčkem package_random_table, který generuje samotnou tabulku a náhodná data. Tento stěžejní balíček bude popsán v následujícím odstavci. 3.2.4.

Balíček package_random_table

Tento balíček je stěžejní a jeho funkce je volána pro vytvoření tabulky a vygenerování potřebných dat. Tato operace je spuštěna tímto příkazem: EXECUTE package_random_table.generate(100000,'user_list'); Takto je vytvořena tabulka s názvem „user_list“, do které je vloženo sto tisíc řádků 47

náhodných dat. Při spuštění příkazu z tohoto balíčku dojde nejprve k jeho inicializaci, v rámci které se volají funkce, které nahrávají jednotlivá zdrojová data do kompozitních proměnných. Balíček těchto funkcí byl vysvětlen v kapitole „3.2.2. Balíček fetch_source_data“. Ukázka části inicializace: random_surnames_women := fetch_source_data.source_for_random('surname',50,'Z'); random_first_names := fetch_source_data.source_for_random('first_name',100); Podobným způsobem jsou inicializovány i proměnné pro ostatní typy dat. Po inicializaci je spuštěna volaná funkce generate. V její deklarační části jsou definovány kompozitní proměnné, kam se ukládají jednotlivé vygenerované hodnoty a další pomocné proměnné. Je zde také možné nastavit parametr, který určuje, kolik dat se vloží do tabulky v jednom cyklu. Implicitně je nastaven jeden tisíc řádek pro jeden cyklus. Prvním příkazem spuštěným v sekci výkonného bloku je funkce pro vytvoření tabulky. Tato funkce je soukromou funkcí balíčku package_random_table. Vytvořená tabulka je nazvána podle předaného parametru funkci generate. Následně může začít generování náhodných dat. Data jsou generována a vkládána do nové tabulky v jednotlivých cyklech. Počet cyklů je určen na základě požadavků na počet generovaných řádků v tabulce. V rámci jednoho cyklu je vygenerován určitý počet dat (implicitně nastaveno na tisíc) a následně jsou tato data vložena do nové tabulky. Vkládání dat probíhá pomocí bulk operací a kvůli těmto operacím je generování a vkládání rozděleno na cykly tak, aby se data jednoho cyklu vešla do operační paměti a nedošlo ke swapování na pevný disk. Pokud by k tomu došlo, celý proces generování by byl velmi zpomalen. Pro generování jednotlivých řádků volaných během každé iterace je implementována další soukromá procedura, generate_row. Tato procedura generuje jeden řádek. Z ní jsou volány další funkce pro generování všech dat daného řádku. Zdrojový kód této funkce: procedure generate_row (sur OUT VARCHAR2, fir OUT 48

VARCHAR2, b_number OUT NUMBER, mob_number OUT NUMBER, town OUT VARCHAR2, street OUT VARCHAR2, house_number OUT NUMBER) IS index_f_name PLS_INTEGER; index_surname PLS_INTEGER; index_birth_year PLS_INTEGER; index_mobile_part PLS_INTEGER; index_address_town PLS_INTEGER; type_s VARCHAR2(1); BEGIN -- generating first name index_f_name := dbms_random.value(1,random_first_names.COUNT); fir := random_first_names(index_f_name); SELECT type_sex INTO type_s FROM source_data WHERE type_data='first_name' AND data1=fir; -- generating surname IF type_s = 'Z' THEN index_surname := dbms_random.value(1,random_surnames_women.count); sur := random_surnames_women(index_surname); ELSE index_surname := dbms_random.value(1,random_surnames_men.COUNT); sur := random_surnames_men(index_surname); END IF; -- generating year of birth index_birth_year := dbms_random.value(1,random_birth_year.COUNT); b_number := random_data.generate_birth_number(random_birth_year(inde x_birth_year),type_s); -- generating mobile number index_mobile_part := dbms_random.value(1,random_part_mob_number.COUNT); mob_number := random_part_mob_number(index_mobile_part) * 1000000 + dbms_random.value(100000,999999);

49

-- generating address index_address_town := dbms_random.value(1,random_town.count); town := random_town(index_address_town); random_data.generate_whole_address(town, street, house_number); END; Z procedury „generate_row“ jsou volány funkce pro generování rodného čísla a adres zmiňované v kapitole „3.2.3. Balíček random_data“. Tato procedura vrací data, která jsou poté vložena do vytvořené tabulky. 3.2.5.

Pomocné balíčky, funkce a procedury

Dalším balíčkem je log_package, který sice není stěžejní pro samotné generování, ale je důležitý pro logování vykonaných cyklů. Díky němu je možno odhalit místo vzniku problému. Procedury tohoto balíčku obsahují autonomní transakce a díky tomu jsou logovací data zachována, i když dojde během vkládání vygenerovaných dat do nové tabulky k chybě a následnému roll backu. Můžeme tak zaznamenávat a odhalovat chyby. Pro proces generování byly použity ještě některé funkce a procedury, které byly naimplementovány pro tento účel. Tyto metody logicky nesouvisí s žádným z výše popsaných balíčků a až zde jsou zmíněny pro úplnost. Jedna z těchto funkcí byla popsána v kapitole „3.1.1. Balíčky, procedury a funkce“. Jednalo se o upravenou funkci pro dělení dvou čísel.

50

3.3. Export a import velkého množství dat v databázích Oracle Pro snadný a rychlý přesun velkého množství dat mezi databázemi jsou v technologiích Oracle navržené datové pumpy. Tyto nástroje slouží k exportu a importu dat na různých databázích Oraclu. V případě této práce je potřeba přenést vygenerovaná data mezi testovacím prostředím a produkčním prostředím. Přenesena bude pouze tabulka vygenerovaných dat. Pro tuto úlohu je potřeba, aby přihlášený uživatel měl dostatečné oprávnění pracovat s definovaným souborem na databázovém serveru. Nejprve byl vytvořen ukazatel na existující soubor na databázovém serveru a následně byla uživateli přidělena práva pracovat s tímto souborem. Potřebné příkazy: CREATE OR REPLACE DIRECTORY EXP_DIR AS ‘/u01/app/Oracle/oradata’; GRANT READ, WRITE ON DIRECTORY EXP_DIR TO scott; Tímto příkazem je uživateli scott uděleno právo pracovat s adresářem exp_dir. Po přidělení oprávnění lze vygenerovaná data v testovacím prostředí exportovat. Export se spustí tímto příkazem: EXPDP scott/tiger tables=GENERATED_TABLE DIRECTORY=EXP_DIR dumpfile=GENERATED_TABLE.dmp logfile=expdpGENERATED_TABLE.log Pomocí následujícího příkazu jsou data importována do produkční databáze. IMPDP scott/tiger tables=GENERATED_TABLE DIRECTORY=EXP_DIR dumpfile=GENERATED_TABLE.dmp logfile=expdpGENERATED_TABLE.log Je důležité klást důraz na oddělování činností pro testovací a produkční databázi. Je nutné nezatěžovat produkční databázi činnostmi, které je možné vykonat na testovací databázi. Toto oddělování činností je důležité i z důvodu bezpečnosti. (16)

51

4.

ZÁVĚR

Cílem mé bakalářské práce bylo vytvořit efektivní nástroj pro generování zátěžových dat a porozumět principům optimalizace databáze. Této optimalizaci napomáhají i data vygenerovaná navrženým nástrojem. Při nejobecnějším pohledu není pro zákazníka důležité, kde se slabé místo informačního systému nachází. Z pohledu uživatele takový informační systém není prostě optimalizovaný. V tomto případě, kdy problém není přímo lokalizován, nemusí být chyba v samotné databázi, ale například až u uživatele, který přistupuje do databáze neefektivně. Proto je velmi důležité porozumět komplexně procesům databází. V předchozím odstavci byl zmíněn jeden z důvodů pro testování. Potřeba testování není důležitá jen při hledání nejslabších míst, ale i při návrhu a zkoušení stability pro budoucí zátěž systému. Systém nasazovaný na produkční databázi by měl být otestovaný, a pro takové performance testy je nutné mít potřebná data. Data pro tyto účely se nejlépe získávají generováním. Jednak můžeme libovolně měnit množství dat, a tím simulovat různé zátěže, a jednak u tohoto typu testovacích dat nehrozí riziko zneužití citlivých údajů. Je samozřejmě důležité, aby generovaná data byla správně navržena a odpovídala datům z reálného provozu databáze. Nejdůležitější vlastností vygenerovaných dat je proto jejich pravděpodobností rozdělení, které by co nejvíce mělo odpovídat skutečným datům. Čemu je nutné porozumět před samotným návrhem nástroje pro generování dat a co všechno je možné v databázi optimalizovat? Jednak je potřeba nástroj správným způsobem navrhnout, a jednak netestovat pomocí zátěžových testů něco, co není tímto způsobem testovatelné. Je nutné porozumět principům běhu databáze. Je potřeba porozumět těmto oblastem: 1. Databázi a způsobu její optimalizace 2. Datovému modelu a business problematice, pro kterou je určen 3. Jak optimalizovat dotazy nad tabulkami 4. Jak efektivně spravovat databázi a datový model

52

Nejdůležitějším z těchto bodů je optimalizace dotazů a optimalizace datového modelu pomocí indexů. Pokud byly indexy vytvořeny nad správnými sloupci a jsou správně v dotazech využívány, pak je zvládnuta velká část optimalizace. Vytipovaná místa databáze dle předešlých bodů by měla být optimalizována a až poté lze přejít k samotnému testování. Navíc na základě těchto bodů je možné snadněji vyhodnotit výsledky performance testů. Samotný nástroj pro generování dat pro zátěžové testy byl navržen tak, aby ke své činnosti používal indexy tabulek a prostředky PL/SQL pro práci s velkým množstvím dat. „Nástroj na tvorbu testovacích dat pro zátěžové testování databází“ generuje data, která odpovídají svým pravděpodobnostním rozdělením statistickým datům o obyvatelích ČR k 1. 1. 2011.

53

5.

CONCLUSION

The aim of my bachelor thesis was to create an effective tool to generate data for stress tests and understand the principles of database optimization. The data generated by the proposed instrument also help to optimize the database. In the most general point of view is not important for the customer, where the weak point of the information system is located. From the user´s perspective such information system is not simply optimized. In this case, when the problem is not directly located, it does not have to be in the database itself, but the user can access the database inefficiently. Therefore it is very important to understand the complex processes of databases. One of the reasons for testing was mentioned in the previous paragraph. The need for testing is not important only for finding the weakest points, but also in the design and testing for the future stability of the system load. The system deployed on the production database should be tested and there is necessary to have data for these performance tests. The data for these purposes are best obtained by generating. First, we can change the amount of data, and thus simulate various loads, and also in this type of test data there is no risk of misuse of sensitive data. Of course it is important that the generated data were properly designed and reflect the data from the real database. The most important property of data is the distribution probability, which has to be the same as the probability of the real data. What must be understood before design of tool for data generating and what is possible to be optimized in the database? Firstly, it is necessary to design the tool by the right way, and secondly it is important that we do not test the stress tests using something that is not testable in this way. It is necessary to understand the principles of running the database. You need to understand these areas: 1. Database and the optimization method 2. Data model and business issue, for which it is intended 3. How to optimize queries for tables 4. How to manage the database and data model effectively 54

The most important of these points is the query optimization and the model optimization by using indexes. If the indexes were created for the right columns and are properly used in queries, then a large part of optimization is managed. The highlighted points of database should be optimized, and then we can go to the actual testing. Moreover, it is easier to evaluate the results of performance tests on bases of these points. The tool for data generating for the stress tests was designed to use for its activity indexes and sources of PL/SQL. The PL/SQL sources are used to work with large amount of data. “The tool for creating test data for stress testing DB” generates data, which correspond to probability distribution of statistical data on the Czech Republic population dated to 1. 1. 2011.

55

6.

ZDROJE

1. Sýkora, Jiří and Sibrt, Tomáš. Analýza pro výkonnostní a zátěžové testy. System online. [Online] Březen 1, 2009. [Cited: Březen 10, 2012.] http://www.systemonline.cz/sprava-it/vykonnostni-testy-podnikovych-aplikaci-2-dil.htm. 2. Lacko, L. Správa, programování a použití databázového systému. 2. vydání. Brno : Computer Press, 2007. p. 576. 3. Using explain plan. Oracle Database Documentation Library 11g Release 2 (11.2). [Online] Oracle. [Cited: Březen 17, 2012.] http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm. E16638-06. 4. Indexes and Index-Organized Tables. Oracle Database Documentation Library. [Online] Oracle. [Cited: Březen 19, 2012.] http://docs.oracle.com/cd/E14072_01/server.112/e10713/indexiot.htm. E10713-02. 5. Fleming, Matt. How b-tree database indexes work. mattfleming.com. [Online] Únor 22, 2007. [Cited: Březen 29, 2012.] http://mattfleming.com/node/192. 6. BRYLA, Bob and LONEY, Kevin. Mistrovství v Oracle® database 11g. Brno : Computer Press, a.s., 2009. p. 700. ISBN 978-80-251-2189-4. 7. Solař, Tomáš. Oracle Database 11g: hotová řešení. Brno : Computer Press a.s., 2010. p. 288. ISBN 978-80-251-2886-2. 8. Oracle Database Performance Tuning Guide, 11g Release 2 (11.2). Oracle documentation. [Online] [Cited: Březen 15, 2012.] http://docs.oracle.com/cd/E11882_01/server.112/e16638.pdf. E16638-06. 9. Introduction to the Optimizer. Oracle 9i Database Online Documentation. [Online] Oracle. [Cited: Březen 18, 2012.] http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#721. A96533-02. 10. Using the Rule-Based Optimizer. Oracle9i Database Online Documentation. [Online] Oracle. [Cited: Březen 19, 2012.] http://docs.oracle.com/cd/B10500_01/server.920/a96533/rbo.htm. A96533-02. 11. Managing Optimizer Statistics. Oracle Database Documentation Library. [Online] Oracle. [Cited: Březen 23, 2012.] http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm. E16638-06. 12. Oracle Hints. PSOUG.org. [Online] [Cited: Březen 25, 2012.] 56

http://psoug.org/reference/hints.html. 13. Turley, P., Wood, D. Beginning T-SQL with Microsoft SQL Server 2005 and 2008. Indianapolis : Wrox, 2008. p. 672. 14. PL/SQL. Wikipedie: Otevřená encyklopedie. [Online] Únor 12, 2012. [Cited: Duben 6, 2012.] http://cs.wikipedia.org/wiki/PL/SQL. 15. Feuerstein, S., Pribyl, B. Oracle PL/SQL Programming. 5. vydání. North Sebastopol : O'Reily Media, 2009. p. 1232. 16. Eckhardt, Erik. Tipy a triky pro Oracle VII. – jak na přenosy velkých dat poprvé. Databázový svět. [Online] Listopad 21, 2006. [Cited: Duben 5, 2012.] http://www.dbsvet.cz/view.php?cisloclanku=2006112103.

57

7.

SEZNAM POUŽITÝCH SYMBOLŮ A ZKRATEK Tabulka 4: Použité symboly a zkratky

Pojem

Popisek

Rowid

Jednoznačná identifikace řádek v tabulce, která umožňuje nejrychlejší přístup k datům v řádku přímo na místo na disku.

Funkce/Procedura

Logický celek, který obsahuje jednu funkčnost.

Perzistentní

Ekvivalent je trvalý, většinou se používá ve spojení s trvalým úložištěm

I/O operace

Operace zápisu a čtení z pevného disku

SLA

Service level agreement je obvykle součástí smlouvy nebo Technického projektu. Stanovuje parametry výkonu systému, jeho dostupnosti, rychlosti atd.

OLTP

(Online Transaction Processing) technologie pro ukládání dat v databázích, která umožňuje snadnou a rychlou modifikaci dat.

58

8.

SEZNAM OBRÁZKŮ

Obrázek 1: Data masking ..................................................................................................... 16 Obrázek 2: Exekuční plán .................................................................................................... 18 Obrázek 3: B - tree index ..................................................................................................... 23 Obrázek 4: B - strom s nízkou kardinalitou ......................................................................... 24 Obrázek 5: Bitmap index ..................................................................................................... 27 Obrázek 6: Diagram zpracování sql dotazu ......................................................................... 30 Obrázek 7: PL/SQL a SQL engine ...................................................................................... 41

59

9.

SEZNAM TABULEK

Tabulka 1: Popis sloupců exekučního plánu........................................................................ 19 Tabulka 2: Pořadí přístupů, které jsou voleny v RBO ......................................................... 32 Tabulka 3: Internetové stránky, ze kterých byla získána zdrojová data .............................. 43 Tabulka 4: Použité symboly a zkratky ................................................................................. 58

60