UNICORN COLLEGE. Katedra informačních technologií BAKALÁŘSKÁ PRÁCE. Efektivní datové struktury pro implementaci aplikace typu Personal Finance Manager

UNICORN COLLEGE

Katedra informačních technologií

BAKALÁŘSKÁ PRÁCE

Efektivní datové struktury pro implementaci aplikace typu Personal Finance Manager

Autor BP: Jan Šinkmajer Vedoucí BP: Mgr. Pavel Zeman

2015

Praha

Čestné prohlášení Prohlašuji, že jsem svou bakalářskou práci na téma Efektivní datové struktury pro aplikace typu Personal Finance manager vypracoval samostatně pod vedením vedoucího bakalářské práce a s použitím výhradně odborné literatury a dalších informačních zdrojů, které jsou v práci citovány a jsou také uvedeny v seznamu literatury a použitých zdrojů. Jako autor této bakalářské práce dále prohlašuji, že v souvislosti s jejím vytvořením jsem neporušil autorská práva třetích osob 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 ………..

…….…………………………… (Jan Šinkmajer)

Poděkování Děkuji vedoucímu bakalářské práce Mgr. Pavlu Zemanovi za účinnou metodickou, pedagogickou a odbornou pomoc a další cenné rady při zpracování mé bakalářské práce.

Efektivní datové struktury pro aplikaci typu Personal Finance Manager

Efficient data structures for the implementation Personal Finance Manager applications

6

Abstrakt Práce si klade za cíl navrhnout a popsat možný způsob ukládání dat, která jsou předmětem aplikací typu Personal Finance Manager (PFM). Návrh je tvořen s ohledem na klíčový předpoklad, že aplikace bude provozována, jako online internetová služba čítající až stovky tisíc klientů a až stovky miliónů transakcí. Předmětem práce je návrh datového modelu, generování reprezentativního vzorku dat a měření vytěžování těchto dat. Tento postup je proveden v relační databázi Oracle a objektové databázi MongoDB, přičemž jsou popsány rozdíly obou přístupů.

Klíčová slova: databáze, datové modelování, relační databáze, objektová databáze, finance, účet, osobní výdaje

Abstract This work aims to design and describe a possible way of storing data of applications such as Personal Finance Manager (PFM) applications. Design is made with respect to the key precondition that the application will run as an online service (via Internet), counting hundreds of thousands of clients and hundreds of milions transactions. The goal is to design a data model, generate representative sample data and measure extraction of data. This procedure is performed in a relational database Oracle and object database MongoDB, while the differences are described for both approaches.

Keywords: database, data modeling, relational database, object database, finance, account, personal costs

7

Obsah Úvod

10

1.

10

Co představují systémy PFM 1.1.

Základní popis PFM a historie

10

1.2.

Požadavky na systém PFM

11

1.2.1.

2.

Funkční požadavky

11

1.2.1.1. Osoby a uživatelské účty

11

1.2.1.2. Účty a transakce

12

1.2.1.3. Osobní nastavení kategorií

13

1.2.1.4. Investice, úvěry, cashflow

13

1.2.1.5. Finanční cíle a rozpočtová omezení

13

1.2.2.

Nefunkční požadavky

14

1.3.

Neřešené funkčnosti

14

Datový model

15

2.1.

Entita

15

2.2.

Atribut

16

2.3.

Doména

16

2.4.

Vztah

16

2.5.

Metoda modelování a notace

17

3.

Volba vhodného typu databáze

19

4.

Předpoklady, generování testovacích dat a metoda měření

20

5.

4.1.

Zajištění reprezentativního vzorku dat

20

4.2.

Metody měření

23

Návrh a implementace v relačním modelu

24

5.1.

Základní popis entit

25

5.2.

Účty a pohyb financí

26

5.3.

Sumární účty a „memo“ účty

28

5.4.

Alternativa jednoduchého záznamu o výdajích a příjmech

29

5.5.

Transformace modelu do tabulek a relací

30

5.6.

Účetní období

31

5.7.

Konstrukce dotazů a nastavení indexů

32

5.8.

Partitioning

39

8

5.9.

Využití materializovaných pohledů

5.10. Organizace diskového prostoru

6.

42 45

5.10.1.

Tablespace

45

5.10.2.

Optimalizace využití prostoru pomocí PCTFREE

47

Návrh implementace v MongoDB

47

6.1.

Návrh objektů (dokumentů)

48

6.2.

Konstrukce dotazů a zavedení indexů

49

6.1.

Organizace diskového prostoru

52

7.

Závěr

54

8.

Seznam použitých zdrojů

56

9.

Seznam obrázků

57

10. Seznam tabulek

58

9

Úvod V dnešní době zaznamenáváme výrazný rozmach internetových portálů a projektů, orientujících se na nějakou úzce specifickou problematiku. Řada z těchto internetových technologií poskytuje služby, ke kterým bychom jinak měli obtížný přístup. Svůj rozmach zažívají různé systémy pro vedení poznámek, kalendářů, nástroje pro pořizování, sdílení dokumentů a mnohé další. Kromě samotného technického řešení nám tyto nástroje velmi často poskytují i konkrétní metodiku, jak dané informace spravovat, zálohovat a jak s nimi nakládat. Mezi tyto systémy lze řadit i tzv. manažery osobních financí, neboli PFM aplikace, což je zkratka z anglického Personal Finance Manager. Tato práce se zabývá popisem a návrhem datového úložiště pro aplikace typu PFM a to zejména s ohledem na efektivitu vytěžování těchto dat, snadnou udržovatelnost a rozšiřitelnost.

1. Co představují systémy PFM 1.1.

Základní popis PFM a historie

Systémy pro správu osobních financí mají významnou roli v každodenním osobním životě nespočtu jejich uživatelů. Jejich cílem je pomoci s plánováním a správou osobních financí, poskytnout přehled o příjmové a výdajové stránce rozpočtu jednotlivce či skupiny osob (typicky domácností). Veškeré osobní či rodinné finanční operace jsou kategorizovány do předem definovaných skupin, čímž poskytují přehled o struktuře výdajů a příjmů domácnosti. Umožňují vytyčení finančních cílů a sledování jejich naplnění. Pomáhají v rozhodování při investicích a sledování jejich výnosnosti v čase. Poskytují nástroje, které pomáhají rozhodovat o nutnosti zadlužení, či splácení úvěrů. Jmenovaných funkčností je celá řada. Pro inspiraci je vhodné nahlédnout na existující řešení a rozsah jejich funkčnosti. Historie systémů se začíná psát již v roce 1983, kdy Scott Cook a Tom Proulx založili společnost Intuit a v tehdejší prudce se rozrůstající obci vlastníků osobních počítačů vycítili příležitost uvést aplikaci pro správu osobních financí. Jejich produkt se jmenoval Quicken a na několik dalších let se stal etalonem ve světě PFM. V roce 1990 vydává Microsoft svou vlastní PFM platformu nazvanou Microsoft Money, kterou staví právě na spolupráci s firmou Intuit.

10

Tato práce se zabývá především online aplikací, tedy řešením, kde stejnou aplikaci obsluhují řady uživatelů a data jednotlivých uživatelů musí být izolována, přestože jsou ukládána ve stejné databází. Takto pracující online PFM vznikají kolem roku 2006 a jsou jimi systémy Wesabe a Mint. Nakonec ale původní firma Intuit skupuje jak Mint, tak Wesabe, který posléze silně prosazujícímu se Mintu ustupuje.

1.2.

Požadavky na systém PFM

V následující kapitole je shrnuto, jaké vlastnosti a funkčnosti lze od PFM systému očekávat, tedy konkrétně jaké vlastnosti a funkčnosti budeme uvažovat ve fázi tvorby datového modelu. Výčet nemá ambice rozkrýt kompletní sadu případů užití (use cases) nebo zahltit tuto práci nespočtem elementárních funkčních a nefunkčních požadavků. Jakkoliv jsou tyto výstupy běžně vnímány jako nedílná součást analýzy a návrhu systémů, zde se text omezuje na shrnutí nejdůležitějších oblastí, které by měl systém pokrývat. Jednotlivé oblasti tedy představují:

1.2.1. Funkční požadavky

1.2.1.1.

Osoby a uživatelské účty

Jelikož je v této práci pojednáváno o online PFM systému, je třeba zavést principy autentizace a autorizace. Je třeba evidovat identitu klienta, informace o něm a zajistit správu jeho osobního nastavení. Klient se do systému prvotně registruje pomocí svého emailu. Email by tak měl mít schopnost jednoznačně identifikovat klienta. Stejně tak je nezbytné evidovat osoby, které mají přístup ke správě systému, zajistit oblasti a operace, ke kterým má uživatel přístup. K této funkčnosti lze vhodně využít uživatelských rolí, které se skládají z elementárních oprávnění. Jednotliví uživatelé jsou pak přiřazování právě těmto rolím. Je třeba brát v úvahu i tu možnost, že osoba správce může být současně i klientem a to tak, že je v systému vedena pod jednou identitou (osobou). Potřeba sledovat výdaje domácností představuje nutnost zavést vazby mezi osobami, či určitá uskupení, kde zapsané výdaje a příjmy jednotlivců dávají možnost sestavit celkový pohled na finanční situaci domácnosti. 11

1.2.1.2.

Účty a transakce

Aplikace pro správu osobních financí poskytuje především přehled nad všemi finančními operacemi. K tomu je zapotřebí zachytit jednak účty, na kterých finanční operace probíhají a dále samotný pohyb financí. Účty nepředstavují pouze účty bankovní, ale jakýkoliv jednotný zdroj určitého množství peněz v určité měně. Lze je také vnímat jako saldo finančních operací (příjmových a výdajových) nad určitým zdrojem financí. Účet tak může být hotovost v peněžence, investice, účet v bance, poskytnutá půjčka jiné osobě. Úvěr je v podstatě také účtem se záporným saldem. Každopádně je nezbytné rozlišovat mezi sebou typy účtů, tedy co je účtem v bance, co hotovostí v peněžence, co úvěrem atp. Klíčové je ovšem rozlišit bilanční účty a příjmové účty 1. Bilanční účet, jak již název napovídá, představuje účet, jehož bilanci sledujeme. Chceme tedy znát stav účtu. Oproti tomu příjmový účet je účtem cizím, jehož stav není třeba znát. Transakce z něj přicházejí, nebo na něj odcházejí, ale neznáme jejich celkové saldo.

Transakce představuje událost, ke které dochází na účtu a souvisí s uskutečněním pohybu financí. Je nutné rozlišit dva základní směry pohybu, tedy příjem a výdej z účtu. Zvláštním případem je pohyb z jednoho účtu na jiný účet. Tedy např. reálně situaci, kdy dojde k vyzvednutí hotovosti z banky (a jejímu přesunu do peněženky). Je možné vnímat takovou transakci jakou jediný pohyb se svým zdrojem a cílem, či jako dva samostatné protisměrné účetní záznamy – příjmový a výdajový, což představuje uvažování a zápis podobný účetní praxi. V tomto pojetí má každá transakce dva účty. Účet, ze kterého jsou finance odčerpány a účet, na který jsou připsány.

V neposlední řadě bude třeba samotnou transakci klasifikovat, tedy určit o jaký pohyb se jedná. Jistě je třeba vnímat jinak transakci, která vzniká z důvodů výdaje financí, a jinak například transakci, která narovnává stav účtu z důvodů zjištění rozdílného stavu na skutečném účtu v bance.

1

FOWLER, Martin: Analysis Patterns – Reusable Object Models. ,Addison Wesley, 1998, s.

123

12

Vedle tohoto typu transakce bude chtít sám klient své výdaje i příjmy kategorizovat. Tedy zařadit, zda se jedná o výdaje za kulturu, potraviny atp. Stejně tak u příjmů lze sledovat jejich strukturu, zda se jedná o příjmy ze závislé činnosti, z pronájmu, úroky z úspor atp.

1.2.1.3.

Osobní nastavení kategorií

V předchozím odstavci je zmíněna kategorizace transakcí, na kterou je třeba klást také určité nároky. V první řadě si každý klient vytváří vlastní kategorizaci, tedy vlastní strukturu příjmů a výdajů, kterou chce sledovat. Dále pak bude třeba zachytit hierarchii ve výdajové struktuře. Je jistě zajímavé sledovat odděleně výdaje za palivo, servis automobilu a jeho pořízení, stejně tak jako může být významné sledovat celkové náklady za automobil. Je tedy vhodné, aby systém umožnil začlenit dílčí kategorie pod jednu souhrnnou.

1.2.1.4.

Investice, úvěry, cashflow

Investice, stejně tak jako úvěr, představují specifickou variantu účtu, ke kterému jsou zaznamenávány období, úroková míra, či předpokládaná výnosnost investice a další. Takto definované parametry pak umožňují zobrazit projekci předpokládaných výnosů, či splátek v budoucnosti. Aby bylo možné získat ucelenou představu a predikci vývoje stavu účtů, umožní systém zadávat i pravidelné opakující se příjmy a výdaje. K tomu je v systému třeba pořídit pouze předpis těchto pravidelných transakcí, zahrnující časové období, periodicitu a částku. Investice, úvěry i předpis pravidelných transakcí posléze vygenerují transakce specifického prediktivního typu, které pak napomáhají k reportingu osobního, či domácího cash-flow a umožňují náhled na stav financí v jakémkoliv bodě časové osy.

1.2.1.5.

Finanční cíle a rozpočtová omezení

Zde se jedná o jednoduché vytyčení časových milníků, které mohou být pravidelné (např. měsíční), či ojedinělé. Cíl je možné, ale ne nezbytně nutné, definovat k jedné, nebo více kategoriím. Stejně tak je možné cíl provázat s konkrétními

13

účty. Cíle jsou pak dvojího typu. Tím prvním chceme dosáhnout určitého stavu na účtu, nebo naopak nechceme překročit stanovenou částku v součtu všech transakcí. Taková konstrukce by měla být schopná modelovat případy, kdy např. chceme dosáhnout určitých celkových úspor, popř. úspor na konkrétním účtu, nebo v druhém případě nechceme, aby např. výdaje v restauracích měsíčně překročily zvolenou částku.

1.2.2. Nefunkční požadavky Při zopakování klíčového atributu tohoto systému, tedy že se jedná o online systém dostupný velkému množství uživatelů, vyvstávají další požadavky, na které je třeba myslet v rámci návrhu databázové struktury. Tím prvním je skutečnost, že data mnoha uživatelů, která spolu nesouvisí, jsou ukládána v jedné databázi. To samo o sobě představuje nutnost zajistit a správně určit vlastníka jakýchkoliv dat. Dalším požadavkem bude zcela nesporně požadavek na výkonnost systému. Systém, který je díky fenoménu internetu celosvětově dostupný, může předpokládat řádově stovky tisíc, až miliony uživatelů (jako je tomu ve skutečnosti v případě aplikace Mint) a potažmo miliardy transakcí. Vyhledávání, zpracovávání a agregace dat tak operují se skutečně velkým množstvím záznamů a nároky na efektivní strukturu jsou tak zřejmé. Významným požadavkem z hlediska návrhu je i počet evidovaných finančních transakcí na jednoho uživatele. Většina dotazů do databáze se totiž týká právě jednoho uživatele a tak je tento předpoklad významný z hlediska dotazů na agregovaná data. Protože se jedná o aplikaci pro správu osobních financí, není předpokládán počet operací za měsíc větší než v řádu několika desítek, či stovek.

1.3. Neřešené funkčnosti Tato práce se věnuje jádru problematiky správy osobních financí. Celé řešení by mělo obsahovat i spoustu návazností. Řada obdobných služeb může být klientům úplně, či částečně zpoplatněna. To do celkového řešení vnáší potřebu zachytit poskytované služby, jejich objednávky, vyúčtování, platby a mnohé další. Životaschopnost takového řešení může být spjata s příjmy z reklamního prostoru a obchodních sdělení. Tyto budou pravděpodobně kontextově provázány se sledovanou aktivitou uživatelů, jejich výdaji v určitých oblastech a jejich zájmy o finanční 14

produkty. Protože obchodní model takového řešení může být skutečně různorodý, není součástí řešení obsaženého v této práci. Práce neřeší ani technické otázky, jakými jsou např. úložiště sessions a mnohé další otázky související se samotným nasazením na provozní infrastrukturu.

2. Datový model Stěžejní disciplínou, která je nezbytná při návrhu úložiště dat, je datové modelování a jedním ze základních výstupů práce je právě datový model. Tato kapitola popisuje, co lze vnímat jako datový model, včetně podrobného popisu jeho součástí. Datový model je abstraktní částí výsledné databáze. Ve své podstatě zachycuje myšlenkový model problémové domény, její elementární prvky a definici vztahů mezi těmito prvky. Jinými slovy je složen z entit, atributů, domén a vztahů. Tyto prvky jsou popsány v dalším textu této kapitoly.

2.1.

Entita

Je v celku nesnadné vymezit pojem entity pomocí přesné definice. Entita může být prakticky čímkoliv, tedy jakýmkoliv konkrétním či abstraktním prvkem, o kterém potřebujeme uchovávat nějaké informace. Podle toho hovoříme o konkrétních, či abstraktních entitách. Pokud tedy uvažujeme problémovou doménu aplikací PIM, pak entitou budou zcela jistě prvky jako účet, vlastník účtu, rozpočet, tedy podstatná jména z oblasti osobních financí, které chceme datově podchytit. Entitami budou ale i některá slovesa problémové domény, o kterých chceme uchovávat informace. Příkladem může být např. slovo nakupovat, tedy entita nákup. U těchto entit je třeba dávat pozor na jejich význam, tedy ke komu se vztahují. Např. prodej je různým pohybem financí z pohledu dodavatele a odběratele a slova prodej a nákup mohou mít stejný význam. V oblasti osobních financí může být takovou problematickou entitou příjem na účet, který je současně výdajem z jiného účtu. Velmi důležitou entitou jsou vztahy mezi jinými entitami. Příkladem může být vztah faktury k objednávce. Takový vztah může nést pouhou informaci o vztahu, popř. ji rozšiřuje o další atributy. 15

2.2.

Atribut

Atributy přestavují jednotlivé nositele elementární informace o entitě. Je to například jméno osoby, datum narození, či adresa. Konkrétně na atributu adresy lze dobře ukázat, jakou dělitelnost informace v systému chceme udržet. Adresa sama může být jedním atributem, ve kterém je uložen ucelený několikařádkový text adresy včetně ulice, čísla popisného, města, PSČ atd. Pokud ovšem v systému chceme pracovat samostatně např. s PSČ, je třeba mít tento atribut veden samostatně. Pokud ovšem chceme např. sledovat historii adres, dojdeme v čase k přesvědčení, že adresa nemusí být nezbytně atributem, ale můře být samotnou entitou o čemž se přesvědčíme později.

2.3.

Doména

Pojem doména se vztahuje k atributu a představuje obor hodnot atributu, kterých může nabývat. Někdy je tento pojem mylně zaměňován s datovým typem atributu. Obor hodnot má totiž širší význam. Pokud v našem návrhu systému budeme např. uchovávat titul před jménem osoby, pak typem atributu bude pravděpodobně řetězec o délce čtyřech znaků. Přípustný obor hodnot může být ale mnohem užší a může se omezovat na konkrétní existující tituly (MUDr, RNDr, Ing.)

2.4.

Vztah

Vztahy se definují mezi různými entitami. Jedná se o jakési asociace, které lze mnohdy jednoduše vyjádřit větou jako „klient vlastní účet“, která definuje vztah mezi klientem a účtem, tedy mezi dvěma účastníky vztahu. Vztah, ve kterém vystupují právě dva účastnící, je nejběžnější a hovoříme o něm jako o binárním vztahu. Známe ale i vztahy se třemi účastníky a ze světa financí tu lze uvést například transakci z pokladny na bankovní účet, kde vystupují tři entity: bankovní účet, pokladna a transakce (jako nositel informace o pohybu peněz). Vztah se třemi účastníky nazýváme ternárním vztahem. Počtu účastníků ve vztahu pak říkáme stupeň vztahu.

16

Entita může být ve vztahu se sebou sama, což představuje zvláštní případ binárního vztahu, který je užíván tam, kde chceme docílit jakési hierarchie. V našem případě tak např. budeme chtít rozdělit osobní výdaje do nějakých kategorií tak, aby bylo možné sledovat osobní výdaje v určité oblasti. Pokud připravíme kategorie „servis automobilu“ a „palivo“, budeme možná v čase chtít sledovat i celkové výdaje za automobil. V hierarchii kategorií tak zavedeme další kategorii „automobil“, která je nadřízená kategoriím „servis automobilu“ a „palivo“. Dalším parametrem vztahů, který je třeba vzít v úvahu, je násobnost vztahu neboli multiplicita. Hovoříme o vztahu jedna k jedné (1:1), jedna k více (1:N) a více k více (M:N). Někdy se tento termín mylně zaměňuje s termínem kardinalita. Kardinalita ovšem představuje konkrétní výskyt počtu vztahů. Vztah jedna ku jedné bude v našem případě představovat vztah entit uživatel a klient, pokud předpokládáme, že klient nemůže mít více uživatelských účtů. Za vztah jedna k více můžeme dosadit vztah entity výdaje (účetní záznam) k entitě účet. Tedy finanční pohyb, který představuje, že jsme z peněženky vynaložili na nákup určitou částku, je přiřazen právě jednomu účtu. Naopak na tomto účtu registrujeme více výdajů (1:N). V aplikaci pro správu osobních financí budeme pracovat s entitou rozpočtového omezení, které představuje nějaký finanční limit výdajů v určitých výdajových kategoriích. V tomto případě lze demonstrovat vztah více k více, kdy rozpočtové omezení a výdajová kategorie jsou právě v tomto vztahu M:N. Je zcela zřejmé, že k výdajové kategorii pořizujeme v čase několik rozpočtových omezení. Naopak jsme konstatovali, že pro rozpočtové omezení můžeme vyčlenit několik výdajových kategorií. Vlastností vztahu, kterou je třeba také zmínit, je účast entit, která může být úplná, nebo částečná. Úplná účast entity je tehdy, pokud entita nemůže existovat sama o sobě bez účasti ve vztahu.

2.5.

Metoda modelování a notace

Z řady existujících metod datového modelování je v této práci zvolen velmi rozšířený Entity Relationship Diagram (ERD), který se v softwarovém inženýrství využívá pro abstraktní a konceptuální znázornění dat. Znázornění pomocí ERD tak slouží především k popisu informační potřeby nebo k popisu typu informace uložené v databázi. Další etapy v tvorbě databázového modelu představují mapování konceptuálního modelu do jeho logické a následně fyzické podoby.

17

Existuje mnoho konvencí pro tvorbu ER diagramů, počínaje notací Chenovou z roku 1976 až po UML. Poměrně rozšířenou notací, která je použita dále v této práci, je tzv. notace „Crow’s foot“. Ta znázorňuje entity pomocí boxů. Ty jsou dále propojovány čarami, které reprezentují vztahy. Symboly na obou koncích těchto spojnicových čar vyjadřují multiplicitu. Příkladem na obrázku 2-1 je binární vztah klienta k bankovnímu účtu, který má v tomto případě multiplicitu 1:N.

Obrázek 2-1 Vztah dvou entit znázorněný v ER diagramu.

Relace je modelována tím, že vztah rozšíříme o primární relaci (Klient) a cizí relaci (BankovniUcet). Maximální počet instancí jedné entity, které můžeme asociovat s jednou instancí jiné entity, nazýváme kardinalitou vztahu 1. Pojmy stupeň a kardinalita mají odlišný význam u vztahů a relací. V záhlaví boxů je patrný název entity, pod ním jsou jednotlivé atributy a spojnice definuje vztah mezi entitami a jeho kardinalitu pomocí symbolů. Výčet symbolů, které určují kardinalitu, jsou zachyceny na obrázku 2-2.

1

RIORDAN, M, Rebecca: Vytváříme relační databázové aplikace, Computer Press, Praha

2000, str. 45

18

Obrázek 2-2 notace kardinality vztahů

Symboly vyjadřují jaký je maximální počet instancí jedné entity asociovaných s entitou druhou. První symbol označuje, že na této straně je očekáván právě jeden objekt. Další symbol představuje více možných objektů. Třetí v řadě představuje kombinaci, tedy žádný nebo jeden objekt (nepovinnost). V posledním případě jde opět o kombinaci – očekáváme jeden, či více objektů. V této kapitole jsme stručně shrnuli pojmy a notaci, která bude využívána v dalších kapitolách.

3. Volba vhodného typu databáze Při volbě vhodného úložiště dat se naskýtá celá řada možností, počínaje systémem souborů, přes relační po objektové databáze.

Nalézt vhodné řešení může pomoci matice databázových systémů 1. Ta vychází z ohodnocení složitosti dat a potřeby dotazovat se na data (vyhledávat). Na obrázku 3-1 jsou zachyceny jednotlivé kvadranty odpovídající kombinacím požadavků na systém.

1 STONEBRAKER,

Michael, BROWN Paul: Objektově relační SŘBD, analýza příští velké vlny,

BEN – technická literatura, Praha 2000, ISBN 80-86056-94-5,str. 19

19

s dotazy

bez dotazů

Jednoduchá data

Složitá data

Relační DB

Objektově-relační DB

Systém souborů

Perzistentní jazyk

Obrázek 3-1 matice DB systémů

Z tohoto rozdělení je zřejmé, že aplikace pro správu osobních financí bude potřebovat silné nástroje pro vyhledávání a dotazování do databáze. Ve volbě vhodného databázového nástroje lze hledat mezi relačními a objektovými systémy řízení báze dat (SŘBD). Relační model je naprosto rozdílný od objektového. Relační databáze interpretují data ve formě jednoduchých dvourozměrných tabulek. Jejich mapování do složitějších struktur objektové aplikace však bývá obtížné. Přesto se jedná o nejčastější a nejvíce podporované implementace databází. Oproti tomu mladší objektové databáze přinášejí přímé ukládání struktur objektů a jejich vazeb a vlastnosti známé z objektově orientovaných programovacích jazyků (dědičnost, polymorfismus, zapouzdření). Nenajdeme zde ale jednotný směr vývoje a společný standard.

4. Předpoklady, generování testovacích dat a metoda měření 4.1.

Zajištění reprezentativního vzorku dat

Aby bylo možné objektivně posoudit efektivitu navržených struktur, je třeba tyto struktury naplnit relevantními daty. Objem dat přitom vychází z dříve stanovených předpokladů, tedy předpokládaného počtu uživatelů a počtu měsíčních transakcí. 20

Způsob generování v databázi Oracle je postaven na jazyce PL/SQL (Procedural Language/Structured Query Language). Tento jazyk rozšiřuje standardní jazyk SQL o konstrukce procedurálního programování. Zejména díky možnosti iterativních operací a generátoru náhodných čísel lze docílit vygenerování požadovaného reprezentativního vzorku dat. V jednotlivých přiložených procedurách (v přiloženém souboru 04_functions_gen.sql) jsou zachyceny bloky kódu PL/SQL generující data a musí být spuštěny v následujícím pořadí: 

funkce GENERATEPERSONS – generuje data 100 000 klientů, jméno a příjmení jsou náhodně generované řetězce. Datum narození je náhodně generováno v rozsahu 1.1.1930 - 1.1.2005



funkce GENERATEACCOUNTS – pro každého klienta generuje náhodně jeho vlastní účty v počtu 1 až 10. Dále generuje nákladové kategorie v počtu 7 až 35 a příjmové kategorie v počtu 1 až 5. Všechny účty jsou uvažovány v jedné měně.



funkce GENERATETRANSACTION – pro každého klienta generuje náhodně transakce z účtu na účet v rozpětí cca dvou let (1.1.2013 - 31.12.2014). Transakce jsou v počtu 500 až 2400, což představuje různou aktivitu klientů v systému. Hodnota 2400 vychází z teoretického předpokladu: až 100 transakcí měsíčně po dobu 24 měsíců. Každá desátá transakce je příjmem v hodnotě 5 000 až 150 000 na náhodný účet. Ostatní transakce jsou výdajem z náhodného účtu na náhodnou nákladovou kategorii v hodnotě 10 až 2000. Tímto způsobem může dojít k nesmyslným záporným zůstatkům na účtech, což je ale z hlediska sledování efektivity struktur nepodstatné. Způsob generování dat po jednotlivých klientech nemusí ovšem nutně odpovídat struktuře reálné produkční databáze. V praxi budou data ukládána postupně v čase a nikoliv po jednotlivých klientech. Proto je nakonec v práci použita následující metoda GENERATETRANSACTIONS2



funkce GENERATETRANSACTIONS2 –tato funkce postupně v čase generuje 100 miliónů transakcí a to tak, že nejprve náhodně vybere klienta a poté zvolí náhodně 2 jeho účty, mezi kterými provede transakci. V průběhu iterace se zvyšuje datum transakce tak, aby bylo dosaženo simulace postupného ukládání dat v čase.



funkce GETRANDOMDATE – je pomocnou funkcí, kterou využívají další funkce pro generování dat. Generuje náhodné datum v určeném rozsahu.

21

Jakkoliv není předmětem práce docílit efektivního importu, či generování dat, je velmi vhodné odstranit patřičné indexy u tabulek, do kterých se právě provádí generování s masivním zápisem. To platí zejména v případě generování transakcí. Tvorba struktur indexů v době masivního zápisu generovaných dat velmi prodlužuje celkovou dobu operace. Protože součástí této práce je i porovnání relační databáze Oracle s objektovou databází MongoDB, je vhodné zajistit v obou systémech identický vzorek dat. Využijeme tedy data vygenerovaná v databázi Oracle. Přestože na trhu existují systémy, které umožňují snadnou migraci dat z Oracle do MongoDB, zde se v jednoduchých konstrukcích nabízí poměrně triviální způsob přenosu. Protože databáze MongoDB pracuje s daty ve formátu JSON, budeme se snažit zajistit na straně Oraclu zformovat výstup do této struktury. Zdrojem bude specifický select odpovídající struktuře dat v MongoDB. Exportní soubor pro MongoDB je vytvořen pomocí přesměrování výstupu příkazové konzole v Oracle (sqlplus) do textového souboru. Spolu s dalšími příkazy zajišťujícími formátování, vypnutí odezvy atp. je dosaženo kýženého výstupu. Takto např. vypadá příkaz pro export transakcí a účetních záznamů:

set echo off set pagesize 0 set term off set heading off col DPdz format a50 spool D:\clients.json; select '{ "_id" : ' || idclient || ', "name" : "' || name || '", "validfrom" : {"$date" : "' || to_char(validfrom,'yyyy-mm-dd') ||'T00:00:00.000+0100"}, "validto" : {"$date" : "' || to_char(validto,'yyyy-mm-dd') ||'T00:00:00.000+0100"}}' from client; cle col spool out set pagesize 100 set term on

Další skripty jsou obsaženy v příloze (export_to_mongodb.sql), Výstupem je struktura ve formátu JSON (je potřeba pouze z konce textu odmazat informaci o počtu dotazovaných záznamů, která je automaticky přidána na konec výstupu). Takový formát je pak možné importovat do MongoDB pomocí nativního nástroje MongoImport a to tímto způsobem:

mongoimport --db pfmtest --collection clients --file d:\clients.json

22

4.2.

Metody měření

Efektivitu struktur lze vnímat z mnoha pohledů. Tou nejsledovanější bude pravděpodobně rychlost budoucího systému, tedy rychlost operací a zejména rychlost, s jakou mohou být vykonány složitější dotazy na agregovaná data. Podstatná může být i efektivita související s objemem uložených dat. V neposlední řadě lze srovnávat i náročnost na implementaci zvoleného řešení. Co se týká rychlosti a prostoru, jde o měřitelné ukazatele a lze je tak dále objektivně srovnávat. Pro měření rychlosti vybereme některé typické funkčnosti systému, které dostatečně reprezentují způsob, jakým se budeme dotazovat na data. Z pohledu systému pro osobní správu financí lze jako typické vnímat tyto případy užití, související s konkrétním požadavkem na data: A. Přehled nákladů v jednotlivých nákladových kategoriích za určité období. V tomto případě se jedná o nejběžnější a stěžejní výstup PFM aplikací. Dotazovaným obdobím bývá zpravidla kalendářní měsíc, či rok. Data jsou v tomto období agregována na základě výdajových kategorií. B. Celková finanční bilance klienta. Představuje kumulovaný stav všech účtů vztažený k aktuálnímu času (či k jinému času v minulosti). C. Přehled transakcí za určité období - s filtrací dle účtu a rozmezí částky. Tyto funkčnosti reprezentované dotazem do databáze jsou předmětem měření. Měření probíhá na stejném PC s omezením paralelně běžících procesů a aplikací. Specifikace testovacího PC 

Procesor: Intel® Core(TM) i7-4710HQ 2,5 GHz – 4 jádra



Pamět: 16GB



OS: Windows 8.1 64Bit



Disk 1TB – pro testování vyhrazen a zformátován prostor 512 GB



MongoDB 3.0 Standard



Oracle database 11g 11.2.0.1.0 Enterprise Edition

Nastavení databáze Oracle: 

Automatic memory management – enabled



Memory target – 6 528 MB



Systém global area (SGA) – 4 256 MB (aktuálně automaticky přidělená)



Program global area – 2 272 MB (aktuálně automaticky přidělená)

23

Měření je provedeno vždy více (minimálně 5) tak, aby byla vyloučena chyba dlouhotrvajícího selectu z důvodů intervence jiného procesu, či aktivity disku. Výsledná časová hodnota uvedená v práci je pak mediánem ze všech provedených měření. Protože dotazovaná data jsou v databázi cachována, je třeba v rámci jednotlivých měření cache paměť vyprázdnit. Další významnou hodnotou, kterou lze sledovat v souvislosti s výkonností systému, je objem diskových operací. Hodnoty, jakými jsou počet přečtených bytů, či logických bloků disku mohou napovědět, nakolik může výsledné trvání dotazu ovlivnit rychlost disku.

5. Návrh a implementace v relačním modelu V této kapitole je popsáno konkrétní řešení systému PFM v databázi Oracle spolu s popisem jednotlivých rozhodnutí a naznačením některých možných variant řešení. V jednotlivých funkčních oblastech jsou nejprve nalezeny klíčové entity, určují se vztahy mezi nimi. Mezi nalezenými entitami jsou v práci vybrány takové, které jsou klíčové v otázce efektivity budoucího systému. Je např. zřejmé, že PFM systém by měl obsahovat část týkající se uživatelů, jejich uživatelských účtů a oprávnění, nicméně pokud se zabýváme otázkou efektivity u velkého objemu dat, pak je to oblast nezajímavá. Na úrovni návrhu tabulek je pak řešena klíčová část celého systému a to oblast účtů a finančních transakci. Pro tuto oblast jsou pak vyjmenovány atributy, určeny kandidátní, primární a cizí klíče. Výsledkem je konkrétní podoba datového modelu a DDL skript pro vytvoření struktury v databázi Oracle. Na obrázku 5-1 je zachycen celkový doménový model aplikace.

24

class Domain Model

UserAuthority

RoleAuthority

UserRole

UserRoleUser

UserProfile

Family

hotovost banka uver Person AccountCategory

IsFormer :bool

Account -

AccountT ype :string IdAccount :long

Inv estment

AccountType, bilancni transakcni

Proj ection Loan

Cashflow AccEntry

LimitAccountCategory LimitAccount

-

IdAccount :long Amount :decimal AmountInNativeCurrency :decimal

Transaction

Proj ectedTransaction

TransactionType

Limit -

Validfrom :DateTime ValidT o :DateTime

Currency Vyber, prevod, narovnani uctu, vydeh

Obrázek 5-1 entity PFM systému

5.1.

Základní popis entit



Person – entita nesoucí údaje o osobě



Client / Family – entita zastřešující skupinu osob, která manipuluje se stejnými účty (např. rodinu).



Account – účet, ať už se jedná o depozit v bance, hotovost, či např. úvěr. Rovněž ale přestavuje protiúčet finanční transakce, tedy nákladový účet, či příjmový účet.



AccountCategory – typ účtu, tedy např. hotovost, bankovní účet, úvěr, investice. Současně obsahuje příznak, zda se jedná o bilanční účet (vlastní

25

účet, kde sledujeme stav), či „statement“ účty, tedy protiúčty, kde nesledujeme stav (nákladové a příjmové účty). 

AccEntry – položka účetního záznamu. Odepisuje, či připisuje částku na účet (což je dáno znaménkem operace) k určitému datu.



Transaction – zapouzdřuje celou finanční operaci pohybu peněz, tedy většinou dva účetní záznamy (peníze odchází z fyzického účtu na nákladový účet). Lze realizovat transakce i na více účetních záznamech.



TransactionType – typ transakce může být standardní, vyrovnávací transakce účetního období (viz kapitola 5.6. Účetní období).



Currency – slouží pro zavedení účtů v různých měnách.



Projection – předpis pro projekci financí do budoucna. U investic a půjček obsahuje úrokovou sazbu, délku období atp. Specializací je také entita CashFlow, která obsahuje předpis pro částky, které se pravidelně platí v čase, či pravidelně odcházejí z účtu.



Limit – slouží pro rozpočtová omezení jednotlivých nákladových účtů, či jiná upozornění při překročení částky na účtu.



UserProfile – entita udržující informace o přihlašovacích údajích osoby.



UserRole – uživatelská role v systému, která zahrnuje výčet oprávnění.



UserAuthority – elementární oprávnění, ze kterých se skládají uživatelské role.

5.2.

Účty a pohyb financí

Zachycení finančních toků, stavů jednotlivých účtů a přehledu vynaložených prostředků v různých výdajových kategoriích tvoří jádro osobních finančních systémů. Účet udržuje stav věcí, které mají nějakou hodnotu vyjádřenou v penězích. Snížit, či navýšit stav účtu lze pomocí jednotlivých operací na účtu. Tyto operace pak zajišťují kompletní historii účtu. Velmi jednoduchý model účtů je zachycen na obrázku 5-2. Představuje entitu účtu, na které jsou zachyceny jednotlivé účetní záznamy. Množství, či finanční částku nese atribut amount, přičemž skutečnost, zda se jedná o příchozí, či odchozí operaci je dána kladným, nebo záporným znaménkem množství. Další dva atributy jsou časové. V informačních systémech často rozlišujeme dvě časové značky pro událost. Jednou je okamžik, kdy k daná událost reálně nastala, druhou pak 26

okamžik, kdy byla tato událost zaznamenána do systému. Zachycení obou časů je velmi důležité například u zpětně zaznamenaných operací.

class ucet-zaznam

Entry

Account -

amount :quantity timeChanged :timepoint timeBooked :timepoint

Obrázek 5-2 účet a účetní záznam

Tento zjednodušený pohled má svá úskalí. Převod z jednoho účtu na jiný účet bychom museli zachytit jako dva separátní účetní záznamy na dvou účtech. Z jednoho účtu částku odebíráme a na jiný účet stejnou částku přičítáme. Snadno si lze přestavit, že každá finanční transakce má ve skutečnosti dvě strany, tedy v dříve používané sémantice dva účty: zdroj a cíl. Položky odečtené z jednoho účtu musí být načteny na jiný účet. Položky nemohou samovolně vzniknout, ani zaniknout. Pro tento účel je zavedena entita transakce. Transakce zapouzdřuje účetní záznamy obou účtů: zdrojového a cílového. Vztahy entit jsou zachyceny na obrázku 5-3.

class transakce

Entry

Account

Transaction 2

-

amount :quantity timeChanged :timepoint timeBooked :timepoint

Obrázek 5-3 transakce

Součet účetních záznamů v jedné transakci je vždy roven nule. Z jednoho účtu částka odchází, na jiný účet je připisována. Tento systém zachycení finančních pohybů vychází z běžné účetní praxe a pomáhá udržet konzistentní informaci o pohybu financí. Lze namítnout, že jeden ze dvou účtů v transakci nám nemusí být znám. Pokud např. pořizujeme lístky do divadla, odchází peníze z reálného účtu (peněženka) na účet obchodníka. Ten v systému zachycen není. Jako účet může být ale vnímána i nákladová kategorie, za kterou chceme své výdaje sledovat.

27

V tomto případě jsou to tedy „výdaje za kulturu“, které představují samostatný nákladový účet.

5.3.

Sumární účty a „memo“ účty

Pokud je využíván systém účtů a transakcí a zajímá nás bilance účtu, dojdeme velmi často k potřebě sledovat bilanci souhrnně za skupinu účtů, které spolu nějak souvisí. Např. v osobních financích můžeme sledovat náklady na údržbu vozu a náklady na spotřebu paliva. Souhrnně však chceme sledovat celkové náklady za údržbu a provoz vozů. Jedná se o nákladové účty, u kterých je využívána hierarchická struktura, zachycená na obrázku 5-4 class sumarni ucet

Account -

entries [abstract]

balance :quantity

Transaction

Entry

Detail Account

entries

2

Summary Account

Obrázek 5-4 sumární účty a detailní účty

Sumární účet se zde může sestávat z jednotlivých detailních, ale i sumárních účtů. K pohybu financí dochází pouze na detailních účtech. Pohyb na sumárních účtech je odvozen z pohybu na detailních účtech. V praxi je třeba řešit i specifický typ finančních transakcí, na jejichž základě vzniká jiný závazek. Typicky se jedná o daně. Na účet sloužící k podnikání připíšeme částku, ale současně chceme vědět, že nám celá částka nenáleží a v budoucnu bude třeba odvést daň z přidané hodnoty. Vzniká tedy závazek tuto daň uhradit. K zachycení takového případu poslouží specifický „memo“ účet. Ten eviduje objem peněz, ale nejedná se o reálné peníze, což mimo jiné znamená, že nemůže dojít k převodu z klasického účtu na memo účet a obráceně. V případě vzniku daňového závazku bude vypadat příklad následovně: na účet vyhrazený k podnikání přijmeme platbu. Současně vzniká závazek zaplatit daň, který musí 28

být zaznamenán na zvláštní typ memo účtu. Tento účet představuje budoucí závazek zaplatit daň. Jak bylo uvedeno dříve, nelze přenášet peníze ze standardního účtu na memo účet, což přináší otázku, co je vlastně protiúčtem v případě memo účtu, protože transakce má mít vždy dva účty. Zde se pak nabízejí dvě možnosti. Buď v případě memo účtu odstraníme dříve zavedení omezení, které říká, že každá transakce má vždy dva účty. Protože memo účet je veden jako účet zvláštního typu, lze pro tento typ účtu omezení zrušit. Druhým způsobem, který více odpovídá účetní praxi, je zavedení kontra účtu k danému memo účtu. Vznik závazku zaplatit daň je tak pohybem z kontra účtu daňových závazků na účet daňových závazků. Poslední transakci představuje vyrovnání závazku, kdy v tomto uvedeném příkladu provedeme transakci z reálného účtu v bance na nákladový účet „odvedená daň“ a současně s uskutečněním této platby musíme snížit závazek na memo účtu. To je provedeno opačným zápisem na memo účet, tedy převedením částky z účtu daňových závazků na příslušný kontra účet. Touto operací je pak bilance na memo účtu závazků vyrovnaná. Zavedením memo účtu máme úplnou informaci nejenom o skutečném stavu peněz, který je dán součtem záznamů na obyčejných účtech, ale i informaci o tom, kolik peněz nám skutečně náleží, což představuje součet záznamů na obyčejných i memo účtech.

5.4.

Alternativa jednoduchého záznamu o výdajích a pří-

jmech Zatímco předchozí model založený na účtech a transakcích představuje bezpečný a flexibilní model zachycení pohybu financí, v případě jednoduché aplikace pro správu osobních financí lze zvažovat i jednodušší model postavený na příjmech a výdajích a jejich zařazení do příjmových a výdajových kategorií. Tento zjednodušený koncept je zachycen na obrázku 5-5.

class prij my-v ydaj e

Client

Expense -

amount :Money date :Date

Obrázek 5-5 náklady a nákladové kategorie

29

Expense Category

Tento koncept bude fungovat dobře do chvíle, než se rozhodneme realizovat složitější operace. Dosud byl uvažován pouze jednoduchý případ, kdy transakce zahrnuje odchozí záznam z jednoho účtu a příchozí záznam na jiný účet, tedy vždy právě dva účetní záznamy. V praxi je ale běžné, že je např. nákup jedné položky realizován platbou ze dvou různých zdrojů. To lze u modelu založeném na účtech a transakcích realizovat pomocí transakce, která má více jak dva účetní záznamy. Tedy např. nákup v hodnotě 300, realizujeme platbou v hotovosti v hodnotě 100 a převodem z účtu v hodnotě 200. To jsou dva odchozí (záporné) účetní pohyby ze dvou účtů. Třetím účetním záznamem v transakci je pak připsání částky 300 na nákladový účet. Opět platí, že celková bilance v transakci je rovna nule. Ve zjednodušeném modelu nákladů a výdajů však takovou finanční operaci nelze zaznamenat. Je možné namítnout, že takové mnohočetné transakce nebudou v jednoduché PFM aplikaci potřeba. Je ale vždy dobré zvážit, jakým směrem se může systém ve svých funkčnostech rozrůstat a zvážit tak užití komplexnějších struktur. Obecné pravidlo říká, že použití modelu účtů a transakcí je vhodné všude tam, kde je struktura účtů více statická. 1

5.5.

Transformace modelu do tabulek a relací

Tato kapitola popisuje konkrétní návrh, jak budou data z oblasti účtů a transakcí zachycena v databázi Oracle. Na obrázku 5-6 je znázorněna struktura tabulek pokrývající danou oblast.

1

FOWLER, Martin: Analysis Patterns – Reusable Object Models. ,Addison Wesley, 1998, s.

131

30

Obrázek 5-6 tabulky a relace pro oblast účtů a transakcí

5.6.

Účetní období

Navrhovaný model účetních transakcí by mohl představovat zdroj výkonnostních problémů při velkém počtu účetních záznamů na jednom účtu. Záznamy nekonečně přibývají, přičemž je bilance na účtu determinována součtem všech těchto záznamů. To představuje vzrůstající složitost v čase. Pro omezení počtu záznamů, které budou vstupem pro určení bilance účtu, se využívá mechanismu účetních období. Ta mohou být nastavena v určité periodě, tedy např. měsíční, kvartální, či roční, podle očekávaného počtu záznamů ve zvolené periodě. Tato perioda rozděluje data na úseky, které mohou být vyhodnoceny samostatně. Děje se tak pomocí 31

transakcí specifického typu, které nejprve předchozí období uzavřou. Uzavření stavu účtu představuje zjištění aktuální bilance účtu a zápisem specifické transakce dorovnávající bilanci na nulu. V novém období je pak provedena stejná operace v rámci transakce, ale s opačným znaménkem. Nové období je tak otevřeno záznamem nesoucím souhrnnou částku bilance předchozího období. Pokud je tedy třeba zjistit stav účtu v novém období, není se třeba dotazovat na účetní záznamy všech předchozích období, ale postačí sečíst záznamy spadající do nového období. Pokud chceme zjistit stav účtu kdykoliv v historii, bude se jednat o součet všech záznamů od počátku daného období, až po datum, ke kterému dotazujeme stav.

5.7.

Konstrukce dotazů a nastavení indexů

Klíčovou kapitolou pro zajištění efektivního dotazování se na data, je správné nastavení indexů. To by mělo odpovídat způsobu, jakým budou konstruovány dotazy do databáze. Indexy přestavují aditivní strukturu k tabulkám a umožňují výrazně rychlejší spuštění SQL dotazů. Pokud však hovoříme o efektivitě, je třeba říci, že indexy zvyšují náročnost na diskovou kapacitu. Jako další neefektivní jev může být zvýšená náročnost zápisu záznamu do tabulky, která obsahuje indexy, či mazání záznamu z této tabulky. Protože jsou indexy samostatnou strukturou, mohou být smazány (DROP) a kdykoliv znovu vytvořeny (CREATE), aniž bychom ovlivnili samotná data v tabulce. Oracle poskytuje široké možnosti indexování, je ale třeba zdůraznit, že některé typy indexů nejsou dostupné ve všech edicích Oracle (např. jsou nedostupné bitmapové indexy v Oracle Standard One), což může představovat vysoké náklady při využití výkonnějších indexů. Můžeme volit mezi těmito indexy: 

B-tree index – je založen na B-tree vyhledávacích stromových strukturách a jedná se o nejčastěji využívaný typ indexu



b-tree cluster index – speciální B-tree index pro clustery



hash cluster index – speciální index pro Hash clustery



globální a lokální indexy – souvisí s partitioningem, který je přestaven v dalších kapitolách



bitmap indexy – jsou efektivní na sloupcích, kde je jen několik málo unikátních hodnot. Je však nutné vzít v potaz, že bitmapové in-

32

dexy výrazně omezují paralelismus. Při UPDATE operacích totiž dochází k uzamčení celého bloku indexu, tj. několik řádků. 

function-based indexy – obsahují předem spočtené hodnoty funkcí.

V průběhu návrhu, které sloupce tabulek budou indexovány a jakým způsobem, je vhodné ctít několik pravidel: 

index je vhodné vytvářet tam, kde budeme dotazy získávat méně než 15% všech řádek tabulky (platí pro velké tabulky)1. Tento práh je velmi relativní a může jej ovlivnit řada dalších charakteristik ukládaných dat.



index je třeba tam, kde se tabulky spojují pomocí JOIN. Primární a unikátní klíče mají indexy automaticky. U cizích klíčů je vhodné zvážit jejich zavedení.



malé tabulky nepotřebují index

Pro vytipování správných sloupců Oracle dokumentace uvádí následující doporučení. Indexy jsou vhodné tam, kde: 

jsou převážně unikátní hodnoty



je široký rozsah hodnot (vhodné pro B-tree indexy)



je malý rozsah hodnot (vhodné pro bitmapové indexy)



sloupce obsahují velký počet null hodnot, ale dotazy převážně získávají řádky, které obsahují hodnoty 1. V tomto případě je navíc vhodné nepoužívat v dotazech konstrukci: WHERE MUJSLOUPEC IS NOT NULL

ale je vhodnější dotazovat se na konkrétní hodnotu tak, aby byl index využit WHERE MUJSLOUPEC >= minimalni_hodnota

Po vytvoření struktur tabulek a určení primárních a cizích klíčů jsou v databázi Oracle založeny indexy pro primární a unikátní klíče. Pro cizí klíče je třeba indexy zavést, stejně tak jako pro hodnoty, podle kterých budou dotazy omezovat data. Efektivitu nasazení jednotlivých indexů sledujeme na typických dotazech, které jsou podchyceny v kapitole 4.2.

1 http://docs.oracle.com/cd/B14117_01/server.101/b10739/indexes.htm

33

Následující dotaz vrací seznam všech nákladových kategorií klienta (tedy i těch, které ve sledovaném období nemají žádné výdaje, což je docíleno spojením LEFT JOIN) a výdajů v uvedeném období jednoho měsíce. Seznam je dále seřazen sestupně podle výše výdajů:

Dotaz A:

select a.idaccount, a.accountname, nvl(sum(e.amount),0) amnt from account a left join (select idaccount, amount from accentry where REALIZATIONDATE between to_date('1.6.2014','dd.mm.yyyy') and to_date('1.7.2014','dd.mm.yyyy') ) e on e.idaccount = a.idaccount join ACCOUNTCATEGORY ac on ac.CODE = a.CODECATEGORY where a.idclient = 2087 and a.CODECATEGORY = 'COST' and ac.BALANCESTATEMENT = 'S' group by a.idaccount, a.accountname order by amnt desc;

V této podobě trvá spuštění selectu nad vygenerovanými daty a načtení úplného výsledku 186 vteřin. Potřeba zavést vhodné indexy je tedy zřejmá. Pro další sledování výkonnosti dotazů budeme používat funkci autotrace dostupnou např. v nástroji Oracle SQL Developer. Pomocí této funkce získáme úplný přehled výsledků měření bez použití indexů, který je zachycen v tabulce 5-1. Dalším dotazem zmiňovaným v kapitole 4.2. je zjištění aktuálního stavu všech účtů klienta. Jedná se o součet všech položek finančních operací nad daným účtem.

Dotaz B:

select a.idaccount, a.accountname, nvl(sum(e.amount),0) amount from account a left join accentry e on e.idaccount = a.idaccount join ACCOUNTCATEGORY ac on ac.CODE = a.CODECATEGORY where a.idclient = 2087 and ac.BALANCESTATEMENT = 'B' group by a.idaccount, a.accountname;

Pokud bychom navíc implementovali i účetní období, zmiňované v kapitole 5.6, pak nepůjde o součet všech finančních operací, ale pouze o součet operací po34

sledního účetního období a to proto, že vycházíme z předpokladu, že každé účetní období obsahuje řádku, která sumarizuje předchozí stav účtu. Dotaz se pak změní následovně: select a.idaccount, a.accountname, nvl(sum(e.amount),0) amount from account a left join (select idaccount, amount from accentry where REALIZATIONDATE between to_date('1.6.2014','dd.mm.yyyy') and to_date('1.7.2014','dd.mm.yyyy') ) e on e.idaccount = a.idaccount join ACCOUNTCATEGORY ac on ac.CODE = a.CODECATEGORY where a.idclient = 2087 and ac.BALANCESTATEMENT = 'B' group by a.idaccount, a.accountname;

Bez použití indexů

čas (s)

physical reads

physical read total bytes

select A

186,047

1 313 914

10 763 583 488

select B

169,390

1 313 908

10 763 534 336

select C

223,922

1 797 766

14 727 299 072

Tabulka 5-1 hodnoty měření selectu bez nasazení indexů

Poslední z dotazů poskytuje prostý seznam operací za uvedené období:

Dotaz C:

select t.idtransaction, t.name , e.amount, e.realizationdate, a.accountname, a.codecategory from transaction t join accentry e on t.idtransaction = e.idtransaction join account a on e.idaccount = a.idaccount where e.REALIZATIONDATE between to_date('1.6.2014','dd.mm.yyyy') and to_date('1.7.2014','dd.mm.yyyy') and a.idclient = 2087 and a.codecategory in ('COST','INCOME')

Oracle poskytuje nástroj, pomocí nějž lze sledovat tzv. exekuční plán, tedy jednotlivé kroky, které představují, jak je dotaz před svým spuštěním vyhodnocen, jaké indexy budou použity a jak jsou časově náročná dílčí vyhledávání a sloučení (JOIN) tabulek. Pro načtení exekučního plánu se využívá příkazu „EXPLAIN PLAN“, za kterým následuje select (popř. update, insert, delete), který je předmětem analýzy. Mimo to lze využít komfortnějších náhledů na exekuční plány po35

mocí nástrojů integrovaných v řadě existujících aplikací (např. v Oracle SQL Developer). V nich jsou pak lépe stromově zobrazeny jednotlivá vnoření iterací. Výsledek příkazu EXPLAIN PLAN pro select A je vidět na obrázku 5-7.

Obrázek 5-7 výsledek funkce EXPLAIN PLAN v Oracle SQL Developer

Varovná čísla jsou v tomto exekučním plánu vidět ve sloupcích CARDINALITY a COST. COST představuje míru využití prostředků, jakými jsou CPU, a I/O operace. Hodnota nemá žádnou specifickou jednotku měření a je spíše váženou hodnotou, která slouží pro srovnání náročnosti a využití prostředků jednotlivých kroků exekučního plánu. Detailněji lze pak sledovat dílčí hodnoty CPU_COST (jak byl využit daném kroku procesor) a IO_COST (počet datových bloku načtených v operaci). Hodnota CARDINALITY pak představuje počet řádek, ke kterým daná operace přistupuje. Pokud trvá spuštěný dotaz nepřiměřeně dlouho, pak hledáme operace s nejvyšší hodnotou COST, které jsou příčinou dlouhých naměřených časů. V uvedeném příkladu lze vyčíst, že operace s nejvyšší hodnotou má ve svém popisu TABLE ACCES (FULL) u tabulky ACCENTRY. Operace nesoucí v popisu TABLE ACCES (FULL) mohou být často zdrojem problémů. Znamená to, že operace procházela všechny řádky tabulky ACCENTRY, aby byly nalezeny řádky vyho-

36

vující podmínkám, přičemž nebyl využit žádný index. To nutně nemusí znamenat problém, pokud tabulka obsahuje malé množství záznamů. Pro takové tabulky není použití indexu vhodné. Tabulka ACCENTRY je ovšem tabulkou s nejvyšším počtem záznamů a sloupec CARDINALITY odhaluje, že jich operace projde přes 8 mil. V tabulce ACCENTRY jsou vyhledávány položky podle data REALIZATIONDATE a dotaz je omezen na účty jednoho klienta. Z toho je patrné, že schází vhodný index nad datumovými sloupci a především indexy pro cizí klíče. Index nad cizím klíčem zavedeme pomocí příkazu: create index accen_fk_idacc on accentry(idaccount);

Pokud není v příkaze CREATE INDEX blíže specifikováno, jedná se o nejběžnější B-Tree index a protože tabulka obsahuje více jak 100 miliónů záznamů, může být vytvoření stromu časově náročné. Dále nebylo v příkazu specifikováno fyzické umístění indexů, které jsou v Oracle řešeny skrze tzv. tablespaces. Je vhodné, aby měly indexy úložiště oddělené od tabulek, ale samotné fyzické rozložení datových objektů bude řešeno následně v samostatné kapitole. Indexy tak budou založeny v implicitním tablespace USERS. Velikost tohoto tablespace již v tuto chvíli vytváření indexů může překročit hranici 32GB, což představuje maximální velikost datového souboru .DBF (maximální velikost je ovlivněna Oracle parametrem db_block_size, který je v testovací DB na hodnotě 8192). Je tedy třeba pro tablespace USERS vytvořit další datový soubor pomocí:

alter tablespace users add datafile 'D:\ora11g\oradata\orcl\USERS02.DBF' size 2M autoextend on;

Dále je třeba uvést způsob, jakým lze eliminovat vliv cache paměti na opakované spouštění dotazů. Cache databáze Oracle je třeba před každým jednotlivým měřením promazat pomocí příkazu: ALTER SYSTEM FLUSH BUFFER_CACHE;

Bez jeho spuštění vrací opakovaná spuštění dotazu data z cache paměti okamžitě, bez ohledu na složitost. Dalším krokem je eliminace cache souborového systému implementovaná přímo v operačním systému Windows. Tu lze vyčistit např. pomocí externího nástroje RamMap (www.sysinternals.com) a to volbou z menu aplikace (Empty Standby List). V souvislosti se zavedením nových indexů je třeba zmínit, že index nemusí zafungovat okamžitě po jeho vytvoření. Při rozhodování, jaké indexy se použijí, využívá DB Oracle statistik, jejichž přepočtení lze vynutit. Nad tabulkou ACCENTRY vypadá přepočet statistik takto: 37

EXECUTE dbms_stats.gather_table_stats ( ownname=>'PFMTEST', tabname=>'ACCENTRY', estimate_percent=>100, cascade=>true);

Pokud zavedeme výše zmíněný index v tabulce ACCENTRY nad sloupcem IDACCOUNT, který představuje cizí klíč a přepočteme statistiky, dojde okamžitě ke zrychlení selectu. . Obdobným způsobem jsou zavedeny další indexy nad cizími klíči. Jejich výčet a patřičné skripty pro vytvoření jsou přílohou této práce. Kromě indexů u cizích klíčů je vhodné zavést indexy tam, kde budeme často při vyhledávání omezovat data a z toho lze vyvodit nutnost indexovat čas provedených finančních transakcí. Téměř vždy totiž agregujeme záznamy za nějaké období. Index je tak zaveden pro sloupec REALIZATIONDATE v tabulce ACCENTRY.

Všechny doposud zavedené indexy jsou typu B-tree. Je ovšem třeba zvážit, zda nebude vhodné i nasazení jiných typů indexů. U sloupců, kde se vyskytuje několik málo unikátních hodnot, nejsou B-tree indexy dostatečné. Takové sloupce jsou pak vhodnými kandidáty na bitmapový index. V navrženém schématu je většina dat omezena na konkrétního klienta a čas, což nakonec představuje malý objem dat, ve kterém bude efekt bitmapového indexu minimální. Jeho efektivita by byla zřetelná, pokud bychom položili např. dotaz „kolik máme v systému celkem nákladových účtů“. Data v tomto případě nejsou omezena jinak, než na typ účtu, což je právě sloupec s malým počtem unikátních hodnot. Dotaz by vypadal takto: select count(idaccount) from account where codecategory = 'COST';

Pokud bude sloupec CODECATEGORY využívat B-tree index, dostaneme hodtnotu za 1,36 vteřiny. V případě bitmapového indexu je ale odpověď mnohem rychlejší (0,42s). Využijeme-li dříve představených účetních období, které mají specifické typy transakcí, pak i typ transakce je vhodný kandidát na bitmapový index. Po nasazení všech zmíněných indexů jsou výsledné časy zachyceny v tabulce 5-2)

38

S použítím indexů

čas (s)

physical reads

physical read total bytes

select A

5,187

846

6 930 432

select B

6,203

947

7 757 824

select C

7,726

1 056

8 650 752

Tabulka 5-2 hodnoty měření selectu po nasazení indexů

5.8.

Partitioning

Koncepce „partition“ je zajímavá nejenom proto, že může zefektivnit SQL dotazy a tím zvýšit výkonnost systému, ale přináší řadu dalších výhod, jakými jsou snazší správa dat a levnější ukládání velkých objemů dat. Partitions umožňují rozdělení tabulek a indexů (popř. index-organized tables) na menší části a přistupovat k nim s jemnější úrovní granularity. Takto oddělené části se nazývají partitions. K tabulce, která je rozdělena do několika partitions, přistupujeme standardně jako k běžné tabulce a to jak pomocí SQL dotazů, tak pomocí příkazů DML (Data Manipulation Language). Každá řádka tabulky spadá do jedné partition a to na základě předem stanoveného klíče (partition key). Jako klíč lze velmi často vnímat čas. Data finančních transakcí rozdělená na partitions po měsících přinesou značné zrychlení, pokud víme, že pracujeme s omezenými daty právě jednoho období. Za 5 let běhu systému dostáváme tabulku rozdělenou po měsících na 60 částí a data budeme dotazovat pouze v jedné z nich. To zmenšuje 60ti násobně objem dat, ve kterých budeme vyhledávat. Pokud spojujeme dvě tabulky (JOIN) a obě jsou rozděleny do partition na základě klíče, dochází k významnému zrychlení JOIN operace (tzv. partition-wise join). Dalším benefitem souvisejícím se zavedením partitions je snazší správa dat. Data rozdělená do jednotlivých období lze efektivněji zálohovat, či po čase ze systému odebrat. Jednotlivým partitions lze navíc vyhradit samostatné úložiště pomocí tablespaces a tak lze např. aktuální data provozovat na výkonnějších avšak dražších SSD discích a data starší udržovat na pomalejších a levnějších discích. Schopnost pracovat samostatně s časově oddělenými bloky dat lze využít, pokud plánujeme systém s vysokou dostupností. S jednotlivými partitiony lze pracovat nezávisle a výpadek jedné neovlivní funkčnost systému (pokud s těmito daty není nezbytné pracovat). Existují tři různé přístupy k rozdělení dat do partitions:

39



Range partitioning – je asi nejběžnějším přístupem. Pro každou partition vymezíme rozsah hodnot a klíč, podle kterého budou řádky distribuovány mezi jednotlivé partitions. Typicky se využívá pro datumy.



Hash partitioning – zvolíme klíč, který je dále interpretován pomocí hash algoritmu. Tato hash hodnota je dále mapována do jednotlivých partition, přičemž se algoritmus snaží zajistit rovnoměrnou distribuci mezi jednotlivé partition.



List partitioning – určí se klíč a explicitní výčet hodnot, které vymezují, že řádka spadá pod tuto partition.

Kromě těchto jednoduchých přístupů (single-level partitioning) lze ještě využít jejich vzájemných kombinací (composite partitioning). Oracle dokumentace uvádí doporučení pro zavedení partitions u tabulek: 

u tabulek s objemem větším než 2GB dat je vždy vhodné zvážit zavedení partition



tabulky obsahující historická data, kdy zapisována jsou pouze data z posledního období, je vhodné rozdělit do partition



když je třeba obsah tabulek distribuovat na různé typy paměťových zařízení, je vhodné zavést partitions v různých tablespacech přestavujících jednotlivá zařízení.

Motivací pro zavedení partition u indexů může být jeden z těchto případů: 

potřeba vyhnout se přestavbě celého indexu při odmazání dat



potřeba udržovat data po částech bez nutnosti zneplatnění celého indexu



snížení efektu nerovnoměrné distribuce hodnot (tzv. index skew)

V aplikaci pro správu osobních financí, která ukládá data o finančních operacích provedených v čase, se nabízí zavedení range partitions, kde dělícím klíčem partition bude právě čas. V tomto případě pro datově nejobjemnější tabulku ACCENTRY zavedeme range partition s rozdělením podle hodnot ve sloupci REALIZATIONDATE. Lze uvažovat rozdělení na kalendářní roky. Pokud však běžné dotazy nepřekračují období jednoho měsíce, může být rozdělení partition jemnější, tedy po měsících. Je důležité zmínit, že zavedení partitions nelze jednoduše provést nad existující tabulkou, ale pouze spolu s vytvořením nové tabulky. Proto je vhodné parametry partitions rozmyslet předem. Pokud bychom plánovali zavést partitions, či změnit její parametry později, muselo by dojít k zavedení nové

40

struktury tabulky a přenosu dat. Zavedení partition vypadá pro tabulku ACCENTRY následovně: CREATE TABLE AccEntry ( IdAccEntry NUMBER(20) NOT NULL, IdAccount NUMBER(20) NOT NULL, Amount NUMBER(20,2) NOT NULL, RealizationDate DATE NOT NULL, AccountedDate DATE NOT NULL, IdTransaction NUMBER(20) ) partition by range (realizationdate) ( partition acc_012013 values less than(to_date('1.2.2013','dd.mm.yyyy')), partition acc_022013 values less than(to_date('1.3.2013','dd.mm.yyyy')), partition acc_032013 values less than(to_date('1.4.2013','dd.mm.yyyy')), partition acc_042013 values less than(to_date('1.5.2013','dd.mm.yyyy')), partition acc_052013 values less than(to_date('1.6.2013','dd.mm.yyyy')), …

) Nebo lze zavést partitions automaticky pomocí intervalového partitioningu, tedy následujícím způsobem. Při zápisu dat vznikají další partitions automaticky podle nastaveného intervalu (systém tyto partitions automaticky pojmenovává)

… partition by range (realizationdate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION part_01 values LESS THAN (TO_DATE('1.1.2013','dd.mm.yyyy')) );

Protože tabulka ACCENTRY již existuje, není možné dodatečně takovou tabulku rozdělit do partitions. Proto je vhodné odhadnout potřebu zavedení partitions na počátku. Je ale možné vytvořit novou strukturu rozdělenou do partitions, do které se původní nerozdělená tabulka zkopíruje. Kompletní postup, který využívá funkcí modulu DBMS_REDEFINITION, je uveden v přiloženém souboru (08. rozedeleni_do_partition.sql).

Dále je třeba rozhodnout, jakým způsobem převedeme indexy, zda se bude jednat o indexy globální, či lokální. V následujících měřeních jsou pro stěžejní sloupec IDACCOUNT tabulky ACCENTRY zkoumány a porovnány oba způsoby.

41

Globální index je vytvořen standardním způsobem, lokální pak za použití klíčkového slova LOCAL.

create index accen_inx_acnt_lcl on accentry(idaccount) local;

Efektivitu rozdělení dat do partition lze sledovat opět měřením doby vykonání selectu, kde došlo k dalšímu časovému snížení. V tabulce 5-3 je ale zejména patrné snížení počtu diskových operací, což lze vysvětlit tím, že byla použita právě jedna partition. Že byla skutečně použita jen jedna partition je zase možné ověřit v exekučním plánu, kde lze pod příslušnou operací nalézt název „PARTITION RANGE (SINGLE)“. Dále je zřetelné, že zavedení partitions nemělo vliv na dotaz B. To proto, že aktuální stav je součtem za všechna období, tedy dotaz směruje skrze všechny partitions. Toto lze eliminovat zavedením účetních období, kdy se budeme dotazovat pouze na poslední účetní období a dotaz tak bude směrován právě do jedné partition. ddddfsdfsdDáDá partition global index čas (s)

physical reads

physical read total bytes

select A

0,723

82

671 744

select B

6,328

948

7 766 016

select C

2,266

205

1 679 360

partition local index

čas (s)

physical reads

physical read total bytes

select A

0,468

52

425 984

select B

6,422

948

7 766 016

select C

1,204

163

1 335 296

Tabulka 5-3 hodnoty měření selectu po nasazení partitions

Uvedené časové rozdělení dat navíc skýtá možnost další optimalizace. PFM systém pracuje převážně s aktuálními daty. Pokud vymezíme pro jednotlivé časové období různé diskové prostory pomocí TABLESPACE, lze tak data aktuální uchovávat na rychlých SSD discích a postupně v čase přesouvat do TABLESPACE na pomalejších discích.

5.9.

Využití materializovaných pohledů

Stejně jako řada jiných relačních databází, nabízí i Oracle nástroje pro materializaci dat do materializovaných pohledů. Materializované pohledy jsou ve své pod42

statě tabulky, které vznikají na základě definice pomocí select konstrukce. Výsledek takto zkonstruovaného dotazu je potom fyzicky uložen do tabulky, ke které lze opět přistupovat pomocí dotazů. Možností využití materializovaných pohledů je celá řada a mimo jiné je lze využít tam, kde je třeba urychlit složitější dotazy, ve kterých dochází k agregaci, či transformaci dat. Data jsou pak v materializovaném pohledu předem připravena v podobě blízké tomu, jak je chceme dotazovat, tedy např. již transformovaná, či agregovaná. Dotaz na materializovaný pohled je pak již rychlý, vzhledem k tomu, že složitější operace již byly vykonány. Nevýhodou materializovaných pohledů může být objem dat, která pak v databázi udržujeme duplicitně.

Rovněž je

třeba

zvážit

způsob, jakým

chceme

mít data

v materializovaném pohledu aktualizovaná. Aktualizaci lze zajistit explicitním vyvoláním, nebo lze nastavit aktualizaci automatickou, na základě manipulace s daty některé ze zdrojových tabulek. Je několik způsobů jak zajistit aktualizaci. Jednou z možností je COMPLETE REFRESH, při kterém dojde ke spuštění selectu, načtení zdrojových dat a k uložení jeho výsledku do materializovaného pohledu. To ale může být časově náročný proces, zejména v případech, kdy je zpracováván velký objem dat. Efektivním přístupem k aktualizaci struktur je FAST REFRESH, který na rozdíl od COMPLETE REFRESH aktualizuje inkrementálně pouze část dat, kde došlo ke změnám. To představuje výrazně rychlejší časy aktualizace než COMPLETE REFRESH. K tomu, aby mohl být využit FAST REFRESH, je třeba nad zdrojovými tabulkami vytvořit MATERIALIZED VIEW LOG, který zajišťuje sledování změn nad příslušnými sloupci, a tyto změny pak iniciují aktualizaci materializovaného pohledu. LOG je ve své podstatě tabulka, do které jsou zapisovány jednotlivé změny, kdykoliv je vyvolána DML operace (data manipulation language) nad zdrojovou tabulkou.

V PFM aplikaci je možné využít materializovaných pohledů pro složitější agregace finančních transakcí. Lze tak například připravit měsíční agregace jednotlivých nákladových kategorií, či sumarizovat aktuální stavy účtu, které jsou jinak výsledkem agregace transakcí probíhajících na účtu. Nejprve vytvoříme log nad tabulkou účetních záznamů ACCENTRY:

CREATE MATERIALIZED VIEW LOG ON accentry WITH SEQUENCE, ROWID (idaccount,amount, realizationdate) INCLUDING NEW VALUES;

a následně samotný materializovaný pohled MV_COSTS:

43

CREATE MATERIALIZED VIEW mv_costs PCTFREE 10 BUILD IMMEDIATE REFRESH FAST AS select e.idaccount, to_number(to_char(e.realizationdate,'YYYY')) year, to_number(to_char(e.realizationdate,'MM')) month, sum(e.amount) amnt from ACCENTRY e group by e.idaccount, to_number(to_char(e.realizationdate,'YYYY')), to_number(to_char(e.realizationdate,'MM'));

Parametr PCTFREE je popsán v následující kapitole a pomáhá nám optimalizovat diskový prostor. BUILD IMEDIATE zajistí, aby byl materializovaný pohled vystavěn okamžitě. REFRESH FAST poskytne částečnou aktualizaci na základě vytvořeného logu ve zdrojové tabulce ACCENTRY. Výsledkem je materializovaný pohled se sloupci: 

IDACCOUNT – id účtu



YEAR – rok



MONTH – měsíc



AMNT – součet účetních záznamů na účtu v uvedeném roce a měsíci

Následný dotaz nad nově vzniklým materializovaným dotazem nebude optimální, dokud nebudou zavedeny potřebné indexy. Ty lze zavést stejným způsobem, jako u běžné tabulky. Vyhledávání bude probíhat na základě složeného klíče, vždy vyhledáváme podle ID účtu a období: create index mv_costs_inx1 on mv_costs(idaccount, year, month);

Dotaz na náklady v jednotlivých kategoriích a v daném měsíčním období pak bude vypadat následovně:

Dotaz A: select cst.* from mv_costs cst join account ac on cst.idaccount = ac.idaccount where ac.idclient = 2087 and year = 2014 and month = 6 and ac.CODECATEGORY = 'COST';

Pro uvedený příklad jsou pak naměřené hodnoty v tabulce 5-4. Pro dotaz B budeme předpokládat zavedení účetních období tak, abychom se mohli dotazovat na jednu agregovanou hodnotu v materializovaném pohledu.

44

Dotaz B: select cst.* from mv_costs cst join account a on cst.idaccount = a.idaccount join ACCOUNTCATEGORY ac on ac.CODE = a.CODECATEGORY where a.idclient = 2087 and year = 2014 and month = 12 and ac.BALANCESTATEMENT = 'B';

s materializovaným pohledem

čas (s)

physical reads

physical read total bytes

select A

0,297

26

212 992

select B

0,312

18

147 456

select C

-

-

-

Tabulka 5-4 hodnoty měření s použitím materializovaných pohledů

Dotaz C nelze realizovat pomocí tohoto materializovaného pohledu, protože nedotazuje agregované hodnoty, ale jednotlivé transakce. Z uvedených měření je zřejmé, že zavedení materializovaných pohledů je v tomto objemu dat jednoznačně efektivní. Všechny doposud naměřené časy jsou měřeny s eliminací vlivu cache paměti a to jak vlastního cachovacího mechanismu implementovaného v DB Oracle, tak cache operačního systému. V praxi to znamená, že časy dotazů budou výrazně lepší a to tím více, čím bude k dispozici větší prostor paměti pro cache.

5.10.

Organizace diskového prostoru

5.10.1. Tablespace Databáze Oracle poskytuje nástroje pro správu úložiště datových souborů a to pomocí tabulkových prostorů „TABLESPACE“. Jednotlivé tabulky, indexy, partitiony, materializované pohledy a další lze přiřazovat předem definovaným tablespace a určit tak jejich fyzické umístění. Datové objekty lze umístit do různých tablespace, k čemuž vede řada motivací, ať již s ohledem na výkon, snadnou údržbu, či možnosti zálohování. Protože pro jednotlivé tablespace lze definovat fyzické uložení, lze oddělit aktuální provozní data rozdělená do partitions a umístit je na rychlé disky SSD, zatímco historická data mohou být umístěna na levnější 45

a pomalejší disky. Je třeba zjistit, jak velký je objem dat a nároky na udržení partition s aktuálními daty. Oracle umožňuje přistupovat k řadě vlastních metadat pomocí systémových view. Obsazenost diskového prostoru lze např. sledovat v systémovém pohledu USER_EXTENTS. Dotaz na segmenty, které využívají největší diskový prostor, pak vypadá následovně:

select segment_name, segment_type, sum(e.bytes)/(1024*1024) MBytes from user_extents e group by segment_name, segment_type order by mbytes desc

Výsledný seznam v tabulce 5-5 ukazuje největší segmenty a typ segmentu. Z něj je zřejmé, že nejobjemnější tabulkou je tabulka účetních záznamů a jejich indexů. Dva roky dat tak v celkovém úhrnu obsadí cca 50GB diskového prostoru.

# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SEGMENT_NAME ACCENTRY ACCEN_FK_IDTRANS PK_ACCENTRY ACCEN_FK_IDACC ACCEN_INX_RDATE_LCL TRANSACTION TMP$$_ACCEN_FK_IDACC0 TMP$$_PK_ACCENTRY0 PK_TRANSACTION TRANS_FK_IDPERS I_SNAP$_MV_COSTS MV_COSTS MV_COSTS_INX2 MV_COSTS_INX1 ACCOUNT PERSONACCOUNT PERSACC_FK_IDACC ACC_FK_IDCLIENT PERSACC_FK_IDPERS PK_ACCOUNT

SEGMENT_TYPE TABLE PARTITION INDEX INDEX INDEX INDEX PARTITION TABLE INDEX INDEX INDEX INDEX INDEX TABLE INDEX INDEX TABLE TABLE INDEX INDEX INDEX INDEX

MBYTES 10333 4860 4642 4568 4321 3819 3732 3725 1884 1769 1730 1440 1218 1093 136 72 56 53 53 52

Tabulka 5-5 seznam 20ti největších segmentů v MB

Další doporučenou praktikou je oddělení samostatné tablespace pro indexy a tedy jejich fyzické oddělení od vlastních dat v tabulkách. Tímto způsobem lze data lépe spravovat. Indexy totiž např. z hlediska archivace nejsou nijak významné a lze je kdykoliv zrekonstruovat.

46

5.10.2. Optimalizace využití prostoru pomocí PCTFREE Z hlediska optimalizace diskového prostoru je třeba zmínit atribut, který ovlivňuje obsazenost diskového prostoru. Data jsou v Oracle ukládána po blocích, což je nejmenší jednotka úložiště v Oracle. Databáze má vždy svou implicitní velikost bloku. Tu je ale možné měnit pro jednotlivé tablespace. PCTFREE je pak parametr pro blokové ukládání a udává, jak velký prostor má být rezervován při ukládání pro budoucí update operace. Hodnota PCTFREE = 10 tak například říká, že bude blok využíván, dokud není naplněn na 90%. Hodnota 10 je implicitní hodnotou u tabulek, u kterých není hodnota explicitně nastavena. Z hlediska optimalizace prostoru tak lze uvažovat nastavení hodnoty nižší (např. 0, tedy úplné využití bloku) a to tam, kde víme, že nedochází k updatům, ale jsou pouze vkládány nové záznamy (insert).

6. Návrh implementace v MongoDB Princip objektových databází je diametrálně rozdílný proti relačním databázím. Přesto lze mezi těmito databázovými principy hledat souvislosti. Běžnou tabulku relačního systému zde představuje kolekce objektů. V případě MongoDB jsou objekty reprezentovány dokumenty. Dokument lze tedy vnímat jako řádku tabulky v relační databázi, současně ale může reprezentovat několik řádek různých tabulek. U relačních databází je struktura tabulky předem definována, MongoDB však u kolekce fixní strukturu nepředepisuje. Je tak možné do kolekce vkládat objekty o různé struktuře. V praxi však dává smysl, aby dokumenty v jedné kolekci měly alespoň podobnou strukturu. Mezi jednotlivými dokumenty existují vazby a ty lze zachytit dvojím způsobem: 

Reference – zachycuje relaci odkazem jednoho dokumentu na jiný.



Vnořené dokumenty – dokumenty jsou ukládány jako vnořené struktury nadřízených dokumentů (embedded documents)

Princip, jakým bychom měli volit mezi referencemi a vnořenými dokumenty, vychází především z potřeby izolace atomických operací. V MongoDB je operace atomická na úrovni dokumentu, tedy jedna atomická operace nemůže ovlivnit několik dokumentů. Struktura samotného dokumentu je ve formátu BSON, což je binární zápis formátu JSON (JavaScript Object Notation). 47

Při návrhu objektů v databázi MongoDB je třeba vzít na vědomí několik omezení, která předurčují rozdíly ve struktuře dat oproti relační databázi. MongoDB nepodporuje atomickou operaci (respektive operaci dodržující ACID) nad více dokumenty. Atomicita operace je zajištěna právě nad jedním dokumentem. Stejně tak dotazování se provádí právě nad jednou kolekcí dokumentů a nejsou možné joiny mezi dokumenty tak, jak jsou známy v relačních databázích. To ve svém důsledku znamená, že atributovou součástí dokumentu se stávají i atributy jiných kolekcí, protože je třeba se na ně dotazovat. To vede k redundanci dat a porušení normální formy. Příkladem může být konstrukce uvedená v návrhu relační databáze, kde je definován vztah mezi klientem, účtem a transakcí. Pokud je třeba se v MongoDB dotazovat na transakce klienta, musí být atribut IDCLIENT součástí objektu transakce. Pokud budeme dotazovat u transakcí i názvy účtů, měl by být součástí transakce i název účtu. Lze samozřejmě postupovat i jinak a názvy účtu dodatečně doplnit na základě jejich ID postupným dotazováním nad výslednou kolekcí.

6.1.

Návrh objektů (dokumentů)

V této kapitole jsou popsány návrhy stěžejních dokumentů, které budou předmětem kriticky exponovaných dotazů. Návrh vychází z účetních transakcí dříve popsaného relačního modelu, které transformuje do objektů databáze MongoDB. Clients { "_id" : 170105, "name" : "Jan Novak", "validfrom" : ISODate("2015-04-07T23:00:00Z"), "validto" : ISODate("9999-03-31T23:00:00Z") } Accounts { "_id" : 6089750, "accountname" : "COST_6089750", "codecategory" : "COST", "codecurrency" : "CZK", "idclient" : 198185 }

Dokument transakce je příklad emedded dokumentu, který v sobě zapouzdřuje jednotlivé účetní operace: Transactions { "_id" : 592696726, "idclient" : 166275,

48

"codetransactiontype" : "N", "idperson" : 166276, "name" : "Trans_592696726", "note" : "", "accentries" : [ { "codecategory" : "CASH", "balancestatement" : "B", "idaccount" : 5452746, "amount" : -5214.69, "realizationdate" : ISODate("2012-1231T23:00:00Z"), "accounteddate" : ISODate("2012-1231T23:00:00Z"), "accountname" : "ACCOUNT_5452746" }, { "codecategory" : "COST", "balancestatement" : "S", "idaccount" : 5452766, "amount" : 5214.69 "realizationdate" : ISODate("2012-1231T23:00:00Z"), "accounteddate" : ISODate("2012-1231T23:00:00Z"), "accountname" : "COST_5452766", } ] }

6.2.

Konstrukce dotazů a zavedení indexů

I v případě objektové databáze je měřena výkonost třech základních dotazů, odpovídajících dotazům v relační databázi. Ve dvou prvních dotazech (dotazy A,B) jde o agregační funkci. Ta je v MongoDB zastoupena metodou aggregate dostupnou u každé kolekce objektů. Agregační funkce je implementována jako pipeline postupně jdoucích operací.

Dotaz A:

db.transactions.aggregate ( [ {$match : {idclient:2087}}, {$unwind:"$accentries"}, {$match : {"accentries.codecategory":"COST", "accentries.realizationdate" : {$gte: ISODate("2014-0601T00:00:00.000Z"),$lt: ISODate("2014-07-01T00:00:00.000Z")}}}, {$group:{ _id : {idaccount:"$accentries.idaccount",accountname:"$accentries.accountname"}, total : {$sum : "$accentries.amount"}}},{$sort:{"accentries.accountname":1}}])

V tomto konkrétním případě jsou záznamy omezeny na konkrétního klienta na úrovni objektu transakce. Následně je pomocí $unwind postoupeno o úroveň ní-

49

že, tedy na embedded objekt accentries představující jednotlivé účetní pohyby a zde je dodatečně kolekce omezena na datum období, které poptáváme a konkrétní typ nákladových účtů (accentries.codecategory“COST“). Sumu nad hodnotami z atributu amount pak zajišťuje naplnění parametru $sum. Obdobně vypadá i dotaz na zjištění aktuálního stavu všech účtů:

Dotaz B: db.transactions.aggregate ([ {$match : {idclient:2087}}, {$unwind:"$accentries"}, {$match : {"accentries.balancestatement":"B"}}, {$group:{ _id : {idaccount:"$accentries.idaccount",accountname:"$accentries.accountname"}, total : {$sum : "$accentries.amount"}}},{$sort:{"accentries.accountname":1}}])

Poslední dotaz na prostý výpis transakcí v určitém období je zajištěn pomocí metody find a vypadá následovně:

Dotaz C:

db.transactions.find ( { idclient:2087, "accentries.realizationdate" : {$gte: ISODate("2014-0601T00:00:00.000Z"),$lt: ISODate("2014-07-01T00:00:00.000Z")}, "accentries.codecategory" : { $in : ["COST","INCOME"] }})

Výsledky měření bez použití indexů jsou zachyceny v tabulce 6-1. bez použití indexů

čas (s)

select A

759,501

1 635 657

1 492

-

-

select B

800,008

1 634 985

696

-

-

select C

932,993

9 291 465

21 389

numYield

nScannedObjects nReturned

responseLength

100 122 113

Tabulka 6-1 hodnoty měření na MongoDB bez použití indexů

Měření probíhá pomocí metod třídy db.system.profile. Ta poskytuje jednak časové údaje o spuštěných dotazech, ale i další statistiky o průběhu vyhledávacích, či agregačních algoritmů. Je třeba zdůraznit, že rozsah poskytovaných informací se pro agregační a vyhledávací funkce různí. Stejně tak jako v případě měření nad databází Oracle i zde je eliminován efekt cache paměti pomocí aplikace RamMap 50

45

a současně uzavřením a znovuspuštěním instance mongod.exe, kdy dojde k zahození veškerých struktur v paměti. Zachytávání informací o probíhajících dotazech je třeba aktivovat pomocí příkazu:

db.setProfilingLevel(2)

a hodnoty naposledy spuštěného dotazu lze vyžádat pomocí této konstrukce:

db.system.profile.find({},{

ns:1,query:1, nscannedObjects:1, numYield:1,nreturned:1, responseLength:1,millis:1} ).limit(1).sort({ts : -1}).pretty()

Jednotlivé výstupní hodnoty představují: 

nscannedObjects – počet dokumentů, které algoritmus prošel při vyhledávání. Není dostupné u agregací.



numYield – počet čekání na dokončení operace. Ve většině případů jde o čekání při čtení z disku.



nreturned – počet získaných záznamů. Není dostupné u agregací.



millis – celková doba provedení dotazu v milisekundách



responseLength – délka odpovědi v bytech

Obdobně jako relační databáze, nabízí i objektově založená MongoDB možnosti pro indexování záznamů, založenou na principu stromů B-tree. Možnosti nastavení indexů jsou ovšem výrazně jednodušší. MongoDB poskytuje tyto typy indexů: 

Indexy nad jedním polem



Compound indexy – složené indexy nad více poli



Multikey indexy – slouží k indexování atributů, které obsahují pole



Text indexy – pro indexování textových řetězců a řešení vyhledávání v textech



Geofrafické indexy – pro řešení vyhledávání geografických lokací

51



Hash Indexy – nad několika atributy je vypočten hash, který je následně indexován. Používá se např. pro hash sharding (vysvětlen později)

Pro vyhledávání transakcí určitého klienta přidáme index nad atributem idclient následovně:

db.transactions.createIndex( { idclient: 1} )

Výsledek naměřených hodnot je zachycen v tabulce 6-2: index idclient

čas (s)

numYield

responseLength

select A

8,063

935

1 492

select B

8,063

935

696

select C

8,645

937

21 389

nScannedObjects nReturned 935

Tabulka 6-2 hodnoty měření na MongoDB s použitím indexu

Další testované variace indexů zahrnují compound index nad atributy realizationdate a idclient, ale v měření nedochází k žádnému zlepšení oproti hodnotám naměřeným v tabulce 6-2. Stejně tak, jako dva založené samostatné klíče nad idclient a realizationdate, kdy MongoDB proklamuje efekt tzv. index intersection, nepřináší výrazně lepší výsledky. Je třeba znovu zdůraznit, že i při měření výsledků v MongoDB je eliminována cache paměť.

6.1.

Organizace diskového prostoru

Obsazení diskového prostoru získáme u jednotlivých kolekcí pomocí příkazu db.collections.stats(). Pro kolekci transakcí je pak požadavek na velikost obsazeného prostoru v megabytech zjišťována takto:

db.transactions.stats({scale:1024*1024})

Obsazenost je zachycena v tabulce 6-3

52

45

. transactions accounts clients

data v MB indexy v MB 47 360 7 206 419 70 10 2

Tabulka 6-3 obsazení diskového prostoru daty jednotlivých kolekcí a jejich indexů

MongoDB poskytuje metodu rozdělení velkých dat na menší části na základě dělícího klíče. Jednotlivým blokům se říká shards a jde v podstatě o obdobu partitions popsanou v části řešení Oracle. Význam je ovšem především v distribuci jednotlivých shards na různé servery. Podle dokumentace MondoDB tedy shards řeší především problém velké zátěže generované velkým objemem dotazů, které jsou pak rozloženy na jednotlivé servery a dále problém uložení velkého objemu dat na jednom serveru. V tomto případě tedy shards neřeší problém rozdělení vyhledávání na menší části, jako je tomu v případě partitions v databázi Oracle. Z hlediska návrhu datové struktury je ale třeba tuto vlastnost zmínit.

53

7. Závěr Aplikace typu Personal Finance Manager pracují s relativně velkým množstvím dat účetního charakteru. Sleduje totiž toky financí na jednotlivých účtech. Problematiku sledování výdajových kategorií, která je stěžejní poskytovanou funkčností PFM aplikací, lze vnímat jako pohyb financí na nákladových účtech. Práce představuje zjednodušený přístup k pohybu financí, postavený na pohledu příjmů a výdajů a současně představuje a dále rozvíjí myšlenku účetních transakcí, které zachycují pohyb mezi jednotlivými účty. V práci je zmíněna celá řada funkčností a identifikována řada entit nezbytných pro plnohodnotný systém. Současně jsou ale identifikovány klíčové entity, které mohou mít dopad na výkonost celého systému. Právě u těchto entit je dále sledována efektivita vytěžování dat a nároky na kapacitu úložiště. Jsou to tedy zejména účty, účetní operace a transakce, které je zastřešují.

V práci jsou dokumentovány známé postupy v implementaci uložení dat. Přínosem této práce je zasazení těchto obecných postupů do konkrétního řešení využitelného v aplikacích, které pracují se záznamem o přenosu finančních dat. Představeno je řešení v relační databázi Oracle a také řešení pro zástupce objektových databází MongoDB.

Relační databáze Oracle je ve své verzi 11g (nejedná se o poslední verzi) vyspělým systémem pro řízení a správu dat. Pro potřeby úložiště PFM aplikace bylo vyžito několik funkčností, které vedou k efektivnější práci s daty. Je třeba zmínit bohatou škálu různých typů indexů, jimiž Oracle disponuje. Správné nastavení indexů má potom zcela zásadní vliv na výkonnost celého systému. Po nasazení vhodných indexů dochází k řádovému zlepšení odezvy databázových dotazů. Oproti MongoDB byly využity i některé specifické indexy, jako např. bitmapový index, který je vhodný u sloupců s velmi malou variabilitou hodnot. Protože PFM aplikace pracují většinou s omezeným množstvím dat určitého období, byla využita i schopnost rozdělit data do menších logických celků na základě období . tzv. partitioning. Rozdělením dat do menších bloků, nejenom že zmenšujeme množinu dat, se kterou dotaz pracuje, ale jsme schopni data rozdělit na různá fyzická úložiště (různě výkonná a nákladná). Další aplikovanou funkčností Oracle jsou materializované pohledy. Ty zde poskytují rychlý přístup k předem agregovaným datům. 54

Databáze MongoDB zdaleka neposkytuje takovou škálu nástrojů, jakou oplývá databáze Oracle. Přesto kromě základní výbavy poskytuje nástroje, které bychom jinde těžko hledali. I zde dochází k výraznému poklesu v odezvě dotazu za použití indexů. Pro navrhované struktury a konstrukce dotazů nedosahovaly dotazy v implementaci MongoDB takové odezvy, jako v případě implementace v Oracle. Nedocenitelnou vlastností MongoDB je však možnost distribuce databáze na několik serverů. Vlastnost se nazývá sharding a jde o mocný nástroj, který skýtá široké možnosti při škálování výkonu. Není smyslem této práce a dost dobře ani není možné hledat vítěze porovnání objektové a relační databázi. Práce pouze poukazuje na rozdílné možnosti obou implementací a dokladuje, že PFM aplikace lze provozovat v obou databázích.

55

8. Seznam použitých zdrojů

[1]

FOWLER, Martin: Analysis Patterns – Reusable Object Models. ,Addison

Wesley, 1998, ISBN: 0-201-89542-0 [2]

RIORDAN, M, Rebecca: Vytváříme relační databázové aplikace, Computer

Press, Praha 2000, ISBN: 80-7226-360-9 [3]

STONEBRAKER, Michael, BROWN Paul: Objektově relační SŘBD, analýza

příští velké vlny, BEN – technická literatura, Praha 2000, ISBN 80-86056-94-5,str. 19 [4]

HEY, David C.: Data model patterns: Conventions of Though, New York:

Dorset House 1996, ISBN 0-932633-29-3 [5]

ŠEŠERA, L., MIČOVSKÝ, A., ČERVEŇ J. Datové modelování v příkladech.

Praha: Grada Publishing 2001, ISBN 80-247-0049-2 [6]

HERNANDEZ, Michael J., Návrh databází, Praha: Grada Publishing 2005,

ISBN 80-247-0900-7 [7]

KNUTH, Donald E., Art of Computer Programming, Volume 3: Sorting and

Searching, Addison-Wesley 1998 | ISBN 0201896850 [8]

International Journal of Computer Science and Electronics Engineering

(IJCSEE) Volume 1, Issue 2 (2013) ISSN 2320–4028, Software Architecture of Online Personal Finance Management System, Hossain. Syed Akhter, Akter. Nasrin, and Akhter. Maria [9]

LANE, Paul, Oracle Database Data Warehousing Guide, 11g Release 2

(11.2), Oracle 2013, dostupné z: http://docs.oracle.com/cd/E11882_01/server.112/e25554/toc.htm [10]

CHAN, Immanuel, ASHDOWN Lance, Oracle Database Performance Tuning

Guide, Oracle 2013, dostupné z: http://docs.oracle.com/cd/E11882_01/server.112/e41573/toc.htm [11]

KIMBALL, Ralph, ROSS, Margy, The Data Warehouse Toolkit: The Complete

Guide to Dimensional Modeling, Wiley 2002, ISBN 0471200247

[12] HERNANDEZ, Michal J., Database Design for Mere Mortals, Addison-Wesley 2003, ISBN 0-201-75284-0

[13] The MongoDB 2.4 Manual, Dostupné z: http://docs.mongodb.org/manual/

56

9. Seznam obrázků Obrázek 2-1: Vztah dvou entit znázorněný v ER diagramu. Obrázek 2-2: Notace kardinality vztahů Obrázek 3-1 matice DB systémů Obrázek 5-1 entity PFM systému Obrázek 5-2 účet a účetní záznam Obrázek 5-3 transakce Obrázek 5-4 sumární účty a detailní účty Obrázek 5-4 sumární účty a detailní účty Obrázek 5-5 náklady a nákladové kategorie Obrázek 5-6 tabulky a relace pro oblast účtů a transakcí Obrázek 5-7 výsledek funkce EXPLAIN PLAN v Oracle SQL Developer

57

10. Seznam tabulek

Tabulka 5-1 hodnoty měření selectu bez nasazení indexů Tabulka 5-2 hodnoty měření selectu po nasazení indexů Tabulka 5-3 hodnoty měření selectu po nasazení partitions Tabulka 5-4 hodnoty měření s použitím materializovaných pohledů Tabulka 5-5 seznam 20ti největších segmentů v MB Tabulka 6-1 hodnoty měření na MongoDB bez použití indexů Tabulka 6-2 hodnoty měření na MongoDB s použitím indexu Tabulka 6-3 obsazení diskového prostoru daty jednotlivých kolekcí a jejich indexů

58

11. Přílohy 01_ddl.sql – zakládací skripty datových struktur v Oracle 02_seq.sql – založení sekvence na Oracle 03_inserts.sql – založení řádek číselníků v Oracle 04_functions_gen.sql – funkce pro generování testovacích dat v Oracle 05_constraint_drop_begore_generating.sql – pomocný skript pro vypnutí constraints při generování dat v Oracle 06_indexes.sql – vytvoření indexů v Oracle 07_constraint_reenable.sql – obnovení constraints po generování dat 08. rozedeleni_do_partition.sql –

skripty které převedou existující tabulku

ACCENTRY do tabulky rozdělené na partition 08_materialized_views.sql – skript pro založení materializovaného pohledu a jeho indexů export_to_mongodb.sql – skripty PL/SQL pro vytvoření exportní dávky ve formátu JSON indexy_vypnout_zapnout – zneplatnění a obnovení indexů, které slouží pro testování chování DB Oracle bez indexů měření.xlsx – záznamy naměřených hodnot MongoDB skripty – různé mongoDB skripty, dotazy, parametry pro mongoimport pred_po_migraci.sql – pomocné skripty pro Oracle, které slouží pro akceleraci migrace, popř. přesunu datových souborů na jiná úložiště select_for_longloops.sql – select, který ověřuje průběh exportu do JSON selecty.sql – dotazy do pro DB Oracle, které jsou zmíněny v práci velikosti_db_objektu.sql – zjištění velikosti objektů v DB Oracle

59