Analýza efektivnosti BI systémů s použitím databáze Oracle

Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky Katedra informačních technologií

Studijní program: Aplikovaná informatika Obor: Informační systémy a technologie

Analýza efektivnosti BI systémů s použitím databáze Oracle DIPLOMOVÁ PRÁCE

Student

:

Bc. Tomáš Řeháček

Vedoucí

:

doc. Ing. Jan Pour, CSc.

Oponent :

Ing. Štěpán Kovář, Ph.D.

2013

Prohlášení: Prohlašuji, že jsem diplomovou práci zpracoval samostatně a že jsem uvedl všechny použité prameny a literaturu, ze které jsem čerpal.

V Praze dne 6. května 2013

.................................. Tomáš Řeháček

Poděkování Děkuji panu doc. Janu Pourovi za vedení mé práce, jeho odborné rady, připomínky a vstřícný přístup. Děkuji také své rodině za podporu při mém studiu.

Abstrakt Hlavním cílem diplomové práce je poskytnout rady a doporučení ke zvýšení efektivity systémů Business Intelligence s použitím databáze Oracle. Konkrétnějším zaměřením práce je optimalizace ETL procesů a objektů v datovém skladu. Ke splnění definovaného cíle jsou provedeny testy pro porovnání různých optimalizačních technik, které jsou založeny na běžně řešených úlohách v datových skladech, a dále podpořeny publikacemi významných autorů v oboru. Druhým cílem práce je implementace vhodných optimalizačních technik do reálného ETL procesu, za účelem zvýšení jeho efektivity. Následně jsou porovnány a změřeny provedené změny, na základě metrik definovaných pro ETL proces, a požadavků od zadavatele úlohy. K dosažení druhého cíle jsou použity optimalizační techniky, představené v rámci cíle hlavního. Přínos diplomové práce spočívá v představení nejdůležitějších optimalizačních technik a rad, který by měl každý vývojář znát a umět efektivně používat. Další přidanou hodnotu tvoří autorovi praktické zkušenosti ze správy reálného datového skladu.

Klíčová slova Optimalizace, databáze Oracle 10g, ETL, Business Intelligence, datový sklad, SQL

Abstract The main goal of this diploma thesis is to provide advice and recommendations to increase the effectiveness of the Business Intelligence using Oracle database. More specifically, the focus of the thesis is the optimization of ETL processes and objects in a data warehouse. Tests to compare different optimization techniques are performed to meet the defined goal. These tests are based on common tasks solved in data warehouses on daily basis. Tests are also supported by publications of leading authors in Oracle environment. The second goal of this work is the implementation of appropriate optimization techniques to real ETL process in order to increase its effectiveness. Performance changes are compared and measured using metrics defined for the ETL process and requirements from client. Optimization techniques presented for main objectives are used to achieve the second objective. Contribution of the thesis is to show the most important optimization techniques and tips that every developer should know and be able to use effectively. Another added value is author’s practical experiences in real data warehouse environment.

Keywords Optimization, Oracle database 10g, ETL, Business Intelligence, data warehouse, SQL

Obsah 1

Úvod ................................................................................................................... 6 1.1 1.2 1.3 1.4 1.5

Vymezení tématu práce a důvod výběru tématu ...........................................................6 Cíle práce a způsob jejich dosažení ...............................................................................6 Vlastní přínosy .................................................................................................................7 Cílová skupina..................................................................................................................7 Předpoklady a omezení práce.........................................................................................7

2

Rešerše ................................................................................................................ 9

3

Systémy Business Intelligence ........................................................................ 10 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8

4

Definice a účel BI ...........................................................................................................10 Zdrojové systémy ..........................................................................................................10 Datové pumpy ................................................................................................................11 Datový sklad ...................................................................................................................12 Operativní a dočasná úložiště dat ................................................................................12 Multidimenzionální databáze ........................................................................................13 Reporting a manažerské aplikace ................................................................................13 Vrstvy a architektura BI .................................................................................................14

Proces ETL a možnosti jeho optimalizace ...................................................... 15 4.1 Základy ladění v Oracle databázi ..................................................................................15 4.1.1 Oracle optimalizátor..........................................................................................15 4.1.2 Analýza objektů a sběr statistických dat ........................................................17 4.1.3 Generování exekučních plánů .........................................................................19 4.1.4 Principy přístupových cest ..............................................................................20 4.1.5 Metody spojování tabulek ................................................................................22 4.1.6 Hinty ...................................................................................................................24 4.1.7 Paralelní zpracování .........................................................................................25 4.1.8 Klauzule logging a nologging ..........................................................................27 4.1.9 Datové bloky a řetězení řádků..........................................................................28 4.1.10 Shrnutí ...............................................................................................................32 4.2 Definice ETL procesu ....................................................................................................33 4.2.1 Opětovné spouštění modulu ETL procesu .....................................................35 4.3 ETL - Fáze výběru ..........................................................................................................35 4.3.1 Materializované pohledy a pohledy .................................................................35 4.3.2 Klauzule WITH ...................................................................................................37 4.3.3 Operátory (NOT) IN a (NOT) EXISTS ................................................................41 4.3.4 Shrnutí ...............................................................................................................46 4.4 ETL - Fáze upravení .......................................................................................................47

4.4.1 Pokročilé agregace ...........................................................................................47 4.4.2 Funkce DECODE a příkaz CASE ......................................................................49 4.4.3 Analytické funkce..............................................................................................53 4.4.4 Identifikace duplicitních záznamů ...................................................................55 4.4.5 Shrnutí ...............................................................................................................56 4.5 ETL - Fáze nahrání .........................................................................................................57 4.5.1 Mazání dat příkazem TRUNCATE.....................................................................57 4.5.2 Režimy pro nahrávání dat ................................................................................59 4.5.3 Nahrávání dat příkazem INSERT ......................................................................59 4.5.4 Nahrávání dat příkazem MERGE ......................................................................62 4.5.5 Nahrávání dat s využitím externích tabulek ....................................................65 4.5.6 Nahrávání dat s využitím oddílů ......................................................................67 4.5.7 Vypínání indexů při nahrávání dat ...................................................................68 4.5.8 Shrnutí ...............................................................................................................70 4.6 Zpracování dimenzionálních dat ..................................................................................71 4.6.1 Obecná metoda zpracování dimenzionálních dat ..........................................71 4.6.2 Implementace metody zpracování dimenzionálních dat................................74

5

Optimalizace ETL procesu v praxi ................................................................... 78 5.1 5.2 5.3 5.4 5.5 5.6

Popis a účel ETL procesu .............................................................................................78 Popis současného stavu ...............................................................................................78 Požadavky na změnu .....................................................................................................80 Analýza problémové oblasti a volba optimalizace ......................................................80 Návrh a vývoj nového řešení ........................................................................................85 Zhodnocení dosažených výsledků ...............................................................................94

6

Závěr ................................................................................................................. 96

7

Terminologický slovník .................................................................................... 98

8

Seznam použité literatury ............................................................................... 101

9

Seznam obrázků a tabulek ............................................................................. 103

Příloha A: Oracle Partitioning ............................................................................... 104 Příloha B: Původní ETL proces měsíčních statistik ............................................ 112 Příloha C: Nový ETL proces měsíčních statistik ................................................. 115

1 Úvod

6

1 Úvod 1.1

Vymezení tématu práce a důvod výběru tématu

Již několik let se aktivně zabývám tématikou relačních databází. Určité znalosti jsem získal ještě před studiem předmětů 4IT218 (Databáze) a 4IT340 (Základy správy databázového systému Oracle), na jejichž základech jsem volil téma své bakalářské práce, na Vysoké škole ekonomické v Praze. V práci jsem se zabýval teoretickou i praktickou problematikou funkcionality Partitioning v Oracle databázích. V době psaní bakalářské práce jsem se v rámci předmětu 4IT314 (Podnikové informační systémy) více seznámil se systémy Business Inteligence (zkr. BI), jejichž základní stavební kámen tvoří datový sklad postavený nad relační databází. Když jsem si na magisterském studiu volil předmět 4IT435 (Business Intelligence), ještě jsem netušil, že ve stejné době budu mít příležitost pracovat na pozici specialista datového skladu pro společnost IBM, při které se mi podaří získat důležité praktické zkušenosti.

1.2

Cíle práce a způsob jejich dosažení

Cílem této práce je poskytnout rady a doporučení vedoucí ke zvýšení efektivity Business Inteligence systémů. Konkrétnějším zaměřením práce je optimalizace v ETL procesech a objektech v datových skladech. Dalším cílem práce je implementace vhodných optimalizačních technik do reálného ETL procesu za účelem zvýšení jeho efektivity, následné porovnání a změření provedených změn, na základě metrik definovaných pro ETL proces, a požadavků od zadavatele této úlohy. K dosažení stanovených cílů jsou využity informace z oficiálních dokumentací, odborných publikací specialistů v oboru a webových prezentací. Tyto materiály vhodně doplňují autorovi praktické zkušenosti z reálného provozu BI systému tím, že nabízejí další úhly pohledu na řešené problémové oblasti. Práce začíná teoretickým úvodem do systémů Business Intelligence. Zaměřuje se na základní prvky a vrstvy BI systémů. Z hlediska této práce jsou významné pojmy zejména datové pumpy (ETL procesy) a datový sklad, které tvoří základních stavební kameny BI řešení. Na teoretickou část navazuje druhá část práce, která kombinuje teorii s praktickými příklady a vlastními testy v oblasti optimalizačních technik v databázi Oracle. Kromě jednotlivých fází ETL procesu, je prostor věnován základním pojmům v oblasti optimalizace. S podporou odborných publikací, představených v kapitole 2, a vlastním měřením poskytu-

1 Úvod

7

je rady a doporučení. Ve třetí části práce je popsán reálný problémový ETL proces, na kterém je demonstrován proces optimalizace a skutečné implementace v produkčním BI systému. Výsledky optimalizace jsou posléze změřeny a zhodnoceny. V závěru se nachází shrnutí nejdůležitějších doporučení, jejich praktický přínos a míra vlivu jejich reálného použití na zvýšení efektivity BI systémů. Cíl této práce lze považovat za splněný v okamžiku, kdy jsou uspokojeny požadavky uživatelů na nové, vylepšené řešení ETL procesu, který připravuje data uživatelům pro jejich reporty.

1.3

Vlastní přínosy

Hlavním přínosem této práce jsou autorovi praktické zkušenosti z fungování reálného datového skladu. Tyto zkušenosti jsou doplněny publikacemi významných autorů, zabývajícími se stejnou oblastí, i vlastním měřením a testováním.

1.4

Cílová skupina

Tato práce je vytvářena z pohledu a určena převážně pro PL/SQL vývojáře, kteří mají omezené možnosti v oblasti samotné správy databáze, jejíž chod zajišťují databázoví administrátoři (DBA). Ti mají k dispozici nejvyšší práva a nástroje od Oracle, které jim pomáhají udržovat a optimalizovat chod databáze. Naproti tomu vývojář se snaží optimalizovat vlastní kód a optimálně využívat přidělené systémové zdroje pro vyvíjené ETL procesy. Mezi vývojáři a DBA přesto musí existovat jistá míra komunikace a kooperace, jelikož jsou na sobě závislí. Dále je tato práce vhodná i pro všechny zájemce o systémy Business Intelligence, zejména pak pro ty, kteří se specializují na provoz, údržbu a rozvoj ETL procesů, datových skladů s použitím databází Oracle a tvorbu reportů pro management.

1.5

Předpoklady a omezení práce

Práce předpokládá základní přehled čtenáře v oblasti relačních databází a znalost fungování databáze Oracle. Teoretická část práce představuje úvod do systémů Business Inteligence. Pro komplexnější pochopení problematiky řešených oblastí lze využít zdroje uvedené v kapitole 8 – seznam použité literatury. Důležité pojmy, které se nevyskytují v teoretické části, jsou vysvětlovány buď v místě jejich prvního užití, nebo je na ně odkázáno do terminologického slovníku, který lze nalézt v kapitole 7. Cílem této práce není seznámení s konkrétními softwarovými produkty, ani jejich srovnávání. Ačkoli je možno použít pro datové sklady různé databázové systémy, tato práce předpokládá použití databázových

1 Úvod

8

systémů Oracle, konkrétně ve verzi Enterprise Edition 10g Release 2. I když již existuje Oracle databáze verze 11g EE R2, veškeré analýzy, testy a optimalizační procesy jsou prováděny na reálném prostředí. To zatím nebylo upgradováno na novější verzi z prostého důvodu. V tuto chvíli již aktivně funguje celá řada projektů a není zaručena 100% zpětná kompatibilita. V případě výpadku některých komponent by mohlo dojít k problémům a finanční ztrátě. Je tedy třeba nejprve založit projekt, který by provedl analýzu dopadu upgradu databáze a připravil řešení. Takový projekt je v plánu, není však v tuto chvíli hlavní prioritou.

2 Rešerše

9

2 Rešerše Obecný popis BI systémů a jejich komponent lze nalézt v publikaci Podniková informatika od autorů Gála a spol. (Gála et al., 2006). Publikace slouží k získání velmi širokého pohledu po podnikové informatiky s vysvětlením všech významných pojmů. Další publikace The Data Warehouse Toolkit (Kimball, Ross, 2002) pochází od zakladatelů datových skladů Kimballa a Rosse a slouží k hlubšímu průniku do tématiky. V publikaci jsou popsány obecné metody pro tvorbu modelů se zaměřením na různé segmenty trhu. Doporučení pro optimalizační techniky, představené v této práci, je založeno na publikacích významných autorů. Jedním z nejznámějších autorů je Thomas Kyte a jeho publikace Expert Oracle Database Architecture (Kyte, 2005). T. Kyte se ve své práci zabývá především popisem vnitřního fungování Oracle databází. Tato publikace je využita především pro vysvětlení obsazování datových bloků, paralelního zpracování a možností Oracle Partitioning. Dalším významným autorem je Jonathan Lewis a jeho kniha Cost-Based Oracle Fundamentals (Lewis, 2006). Hlavním zaměřením knihy je efektivní práce s daty. Představen je optimalizátor Oracle, princip cen k sestavování exekučních plánů a podrobně jsou vysvětleny jednotlivé metody spojování tabulek. Autoři Loney a Bryla vydali publikaci, která je přeložena do českého jazyka pod názvem Mistrovství v Oracle database 10g (Loney, Bryla, 2006). Publikace obsahuje velmi kompletní a ucelené informace o verzi Oracle10g. V této práci slouží především v kapitolách, zabývajících se nahráváním dat a vhodně doplňuje publikaci Lewise. Dalším autorem, který doplňuje výše zmíněné oblasti je Richard J. Niemiec se svou publikací Oracle Database 10g Performance Tuning (Niemiec, 2007). Publikace je vhodná jak pro vývojáře, tak i databázové administrátory a v jednotlivých kapitolách je vždy uvedena cílová skupina. Jedná se o velmi rozsáhlou práci s celou řadou testů pro porovnání výkonnosti širokého spektra technik a podrobným zdůvodněním. Kapitoly zaměřené na optimalizaci SQL, staví především na základě publikací Oracle Database 11g SQL (Price, 2007), jejíž autor je Jason Price, a dále pak na Mastering Oracle SQL (Beaulieu, Mishra, 2004) od autorů Alan Beaulieu a Sanjay Mishra. Druhá publikace je zaměřená přímo na příkazy a funkce jazyka SQL s praktickými ukázkami. Price navíc přidává i představení a možnosti použití procedurálního jazyka PL/SQL. Rovněž je vhodné zmínit oficiální dokumentaci Oracle (ORACLE, 2013), která je velmi komplexní a přitom snadno dostupná a přehledná.

3 Systémy Business Intelligence

10

3 Systémy Business Intelligence Cílem této kapitoly je seznámit čtenáře se systémy Business Inteligence a základními pojmy v tomto odvětví. Definicí a účelem BI systémů se věnuje kapitola 3.1. Kapitoly 3.2 až 3.7 se věnují jednotlivým komponentám BI a kapitola 3.8 dává komponenty do celkového kontextu popsáním a zobrazením jednotlivých vrstev a celkové architektury BI systémů.

3.1

Definice a účel BI

Termín Business Intelligence (zkr. BI) poprvé definoval Howard. J. Dresner jako „sadu konceptů a metod určených pro zkvalitnění rozhodnutí firmy“. Pojem BI se však postupem času pro potřeby využití dále vyvíjel a definice České společnosti pro systémovou integraci více odpovídá dnešním systémům. Cssi.cz definuje BI jako „sadu procesů, aplikací a technologií, jejichž cílem je účinně a účelně podporovat řídící aktivity ve firmě. Podporují analytické a plánovací činnosti organizací a jsou postaveny na principech multidimenzionálních pohledů na podniková data. Aplikace BI pokrývají analytické a plánovací funkce většiny oblastí podnikového řízení, tj. prodeje, nákupu, marketingu, finančního řízení, controllingu, majetku, řízení lidských zdrojů, výroby“ (Gála et al., 2006). BI může být založeno na různých řešeních, ale jejich globální cíl je společný a to získat společnosti konkurenční výhodu na trhu. Základ pro BI systémy poskytují interní data, ze kterých lze těžit důležité informace, a která následně pomáhají činit důležitá rozhodnutí, mající vliv na chod společnosti. Data mohou pocházet z různých systémů, přičemž v každém z těchto systémů mohou existovat v různých formátech. Rovněž ne všechna data jsou pro účely analýzy důležitá a ty je potřeba odfiltrovat. Podnikové nebo také provozní či produkční systémy bývají optimalizovány pro transakční, a ne analytické zpracování dat. Aby mohli manažeři dělat rozhodnutí, je tedy třeba data vhodně připravit. BI systémy se skládají z komponent, které pracují s daty od jejich získání z podnikových systémů, přes následnou úpravu, až po prezentaci požadovaných dat uživatelům BI systémů.

3.2

Zdrojové systémy

Zdrojové systémy (někdy také označovány jako provozní, produkční, podnikové, transakční nebo OLTP) nejsou přímo součástí BI systémů. Slouží jako primární zdroj dat, které BI zpracovává a dále využívá. Zdrojové systémy mohou být vnitropodnikové i mimopodnikové. Nevyznačují se konkrétní architekturou ani formátem dat, ale jejich databáze (pokud data do databáze ukládají) bývají optimalizovány k transakčnímu zpracování. Může se jednat o podnikové systémy (ERP, SCM, CRM, SAP atd.), veřejné databáze


11

a číselníky (statistické údaje, vládní analýzy), datové výstupy z jiných podniků (obchodní partneři, klienti, apod.) a mohou mít velmi různorodý formát, od textových (flat), XML a CSV souborů až po normalizované databázové tabulky. Zpracování dat z těchto heterogenních prostředí zajišťují datové pumpy.

3.3

Datové pumpy

Datové pumpy1 (Extraction, Transformation and Loading - ETL) jsou nejdůležitější komponentou v BI systémem a zároveň nejvýznamnějším pojem v této práci. Jde o proces výběru (extraction), upravení (transformation) a nahrání (loading) dat ze zdrojových do cílových systémů. Zdrojové systémy jsou vysvětleny v předchozí kapitole. Cílovým systémem může byt datový sklad (kapitola 3.4), datové tržiště, operativní nebo dočasná úložiště dat (kapitola 3.5). Dle pořadí úpravy dat lze datové pumpy rozdělit na typy ETL a méně obvyklý ELT, přičemž v ELT předchází fáze nahrání dat vlastní transformaci. Jednotlivé fáze jsou následující: ● Fáze extrakce slouží k získání dat ze zdrojových systémů. Často se jedná o SELECT dat z jedné či více tabulek s filtrující WHERE podmínkou. V případě potřeby načítání dat z textových souborů se používají databázové tabulky, ze kterých si lze data snadněji vybírat a filtrovat. ● Fáze upravení obsahuje nástroje k další filtraci a čištění dat. Odstraňují se duplicitní záznamy, nahrazují se prázdné (NULL) hodnoty, přiřazují se DWH klíče (ID, čas nahrání, úpravy, smazání záznamu) pro pozdější identifikaci a integraci. Faktovým datům jsou přiděleny klíče na příslušné dimenze. Rovněž dochází ke sjednocení formátů. Během fáze transformace se často pracuje s dočasnými datovými úložišti (DSA). ● Fáze nahrání ukládá data do existujících i nových tabulek nebo jejich oddílů. V této fázi se rovněž pracuje s indexy, které bývají před nahráním vypínány a následně znovuvytvořeny. Vytvářejí se nové oddíly v rozdělených tabulkách. Datové pumpy jsou spouštěny v dávkovém režimu a zpravidla v pravidelných intervalech. Nejčastěji bývají intervaly denní a měsíční. Lze se však setkat i s týdenními a dalšími podnikově specifickými intervaly, souvisejícími například s fakturačním obdobím. K vývoji, spouštění a správě datových pump existují profesionální nástroje, z nichž nejznámějším je zřejmě PowerCenter společnosti Informatica. Při použití specifických nástrojů je třeba řešit, jaké výpočty v datovém toku bude provádět databáze, a jaké samotný nástroj. Jak databáze, tak i použitý nástroj obsahují vlastní analytické a transformační

1

V této práci jsou datové pumpy často nazývány jako ETL procesy.


12

funkce, a rovněž dočasné paměti, kam si ukládají data. Obě řešení je vždy potřeba předem analyzovat a vybrat jednu vhodnější, případně zvolit kombinací obou.

3.4

Datový sklad

Datové sklady (Data Warehouse, DWH) tvoří základní stavební kámen systémů BI. Jsou do nich nahrávána a transformována data z heterogenních provozních systémů za účelem sjednocení formátů a přípravě pro další analýzy. Datové sklady rovněž slouží k uchovávání historických dat za výrazně delší období, než transakční systémy, a proto obsahují obrovské množství dat. V současné době neexistuje technologie, která by dokázala optimálně plnit funkci datového skladu a transakčního systémů zároveň. Databáze uchovávající data transakčních systémů by měly být normalizovány alespoň na úroveň třetí normální formy (3NF), aby byly požadované změny provedeny co nejrychleji, a tím pádem jsou optimalizovány na přidávání, mazání a úpravu záznamů (OLTP systémy). Naproti tomu databáze datových skladů jsou objemnější, databázové tabulky jsou širší a normalizace nebývá obvyklá, nejvýše však do 3NF, protože je potřeba data především analyzovat, nejsou tedy měněna v reálném čase. Data v datových skladech se dělí na dvě základní skupiny – fakta a dimenze. Fakta (lze se setkat i s pojmem ukazatele) představují zpravidla číselně vyjádřené hodnoty, které jsou v rámci obchodních aktivit sledovány a měřeny. Dimenze jsou časové nebo textové hodnoty a slouží pro přidání kontextu faktovým datům. Podle dimenzí se třídí, kategorizují a agregují faktová data. U datových skladů se lze setkat s pojmy star či snowflake schéma. V případě schéma star obsahují tabulky faktů cizí klíče do denormalizovaných tabulek dimenzí. V případě schéma snowflake jsou tabulky dimenzí částečně normalizovány a obsahují cizí klíče i mezi sebou.

3.5

Operativní a dočasná úložiště dat

Operativní úložiště dat (Operational Data Store, ODS) je oblast datových tabulek mezi provozními systémy a datovým skladem. Může se však jednat i o část datového skladu spravovanou jinými osobami, než spravují datové sklady. Hlavním cílem ODS je poskytnout uživatelům okamžité analytické úlohy nad daty, které nelze snadno získat z provozního systému nebo datového skladu. Jelikož ODS obsahuje extrakt dat z provozních systémů, může zároveň plnit roli zdroje dat pro datový sklad (Kimball, Ross, 2002). Dočasné úložiště dat (Data Stage Area, DSA) je úložná oblast a sada procesů k čištění, upravování, slučování, odstraňování duplicit, archivování a přípravě dat k použití


13

v datovém skladu. DSA se může nalézat kdekoli mezi zdrojovým systémem a prezentační vrstvou. Z DSA by se neměly vybírat data do reportů, protože tyto objekty obvykle nebývají výkonnostně optimalizovány k těmto účelům. V tabulkách nebývají indexy, data nemusí být agregovaná a propojena s dalšími zdroji (Kimball, Ross, 2002).

3.6

Multidimenzionální databáze

Z datových skladů se vytvářejí multidimenzionální kostky. Ty jsou složeny z dimenzí a faktů, a umožňují vytvářet pohledy na data v různých kontextech (OLAP systémy). Například obchodní firma sleduje částky prodejů (fakt) a následně se chce podívat, jak se prodeje vyvíjeli za poslední měsíc, v rámci jednotlivých poboček, v různých zemích nebo kolik toho prodali jednotliví obchodníci (dimenze). Multidimenzionální databáze obsahují data, které jsou předpřipravena k analýze. Nejedná se o detailní data, ale různé úrovně jejich agregace či statistik (sumy, počty, průměry apod. za období, pobočku, zemi, obchodníka apod.).

3.7

Reporting a manažerské aplikace

Z OLAP kostek nebo i přímo z datových skladů se data zpracovávají a vytvářejí se reporty. Tyto operace zajišťují klientské aplikace. Klientské aplikace by měly podporovat několik základních funkcí. Reporty jsou generovány na základě ad hoc požadavku od uživatele nebo v pravidelných intervalech (intervaly obvykle souvisí s intervalem načtení nových dat). Důležitá je také flexibilita, která uživatelům umožňuje měnit typy, pořadí a úroveň dimenzí, podle kterých jsou faktová data agregována a zobrazována. Nástroje by měly rovněž umožňovat tyto data třídit a filtrovat. Grafické rozhraní aplikací by mělo být přehledné a snadno ovladatelné i pro uživatele se základní znalostí práce s PC. Reporty by měly umožňovat zobrazení i různých typů grafů pro vyšší přehlednost. (Gála et al., 2006) rozřazuje manažerské aplikace do dvou základních úrovní – reporting a manažerské aplikace. Reporting je určen především technickým uživatelům, obvykle na nižších úrovních řízení, jelikož se data vybírají přímo z databází pomocí dotazovacích jazyků (např. SQL). Manažerské aplikace (EIS – Executive Information System) bývají konstruovány pro potřeby vyššího vedení firmy a analytiků, kteří sledují firemní procesy, plnění krátkodobých i dlouhodobých cílů, a nahlížejí na data z různých pohledů a na několika úrovních. Příkladem manažerských aplikací může být MS Access, MS Excel s využitím kontingenčních tabulek nebo některé specifické aplikace jako ProClarity, Oracle Discoverer, apod.


3.8

14

Vrstvy a architektura BI

BI systémy nemají jednoznačnou či unifikovanou architekturu. Obsah a struktura vnitřních komponent se liší podle produktu, definovaného účelu nebo požadavků společnosti. Může se jednat o minimalistické řešení k vytvoření několika málo reportů z jednoho podnikového systému nebo naopak komplexní řešení s obrovskými datovými sklady a extrakcí, sjednocením a následnou prezentací dat z mnoha heterogenních systémů. Ať už se však jedná o jakkoli složité řešení, vždy lze identifikovat následující vrstvy, které na sebe navazují (převzato z (Novotný et al., 2005)): ● Vrstva pro extrakci, transformaci, čištění a nahrávání dat (ETL) slouží k načtení dat ze zdrojových systémů a nahrání do další vrstvy, která zajistí jejich uložení. Tato vrstva může mít rovněž několik úrovní složitosti od pouhého přesunu dat až po zabudované čištění, transformaci či kontrolu kvality. Výše uvedené patří pod BI komponentu ETL, ale lze se zde setkat také s pojmem EAI, což jsou nástroje sloužící pro integraci aplikací a přenášení dat v reálném čase. ● Vrstva pro ukládání dat představuje objekty v databázích, které jsou využívány k ukládání a správě dat z první vrstvy. Zde se nachází BI komponenty datové sklady, datová tržiště, operativní a dočasná datová úložiště. Na úrovni databáze Oracle se používají schémata a tabulkové prostory, pod kterými se nacházejí různé typy tabulek, pohledů a materializovaných pohledů. ● Vrstva pro analýzu dat obsahuje komponenty, které vybírají, analyzují a agregují data. Patří sem tedy reporting nebo systémy pro dolování dat (data mining). ● Prezentační vrstva slouží uživatelům BI systémů. Pomocí této vrstvy přistupují k již analyzovaným datům, které využívají k rozhodování nebo je dále prezentují. Zde se mohou nacházet velmi různorodé komponenty, protože sem patří portálové aplikace založené na technologiích WWW, systémy EIS a mnoho dalších analytických aplikací.

4 Proces ETL a možnosti jeho optimalizace

15

4 Proces ETL a možnosti jeho optimalizace Tato kapitola si klade za cíl představit, porovnat a doporučit možnosti, které nabízí databáze Oracle v oblasti ladění ETL procesů i obecně. Kapitola 4.1 se věnuje základní pojmům v oblasti ladění, jejichž znalosti jsou potřebné pro pochopení dalších kapitol. Rovněž představuje možnosti databáze Oracle, které nelze přímo přiřadit k jednotlivým fázím ETL procesu. Kapitola 4.2 popisuje ETL proces, který je sestavený z modulů obsahujících SQL a PL/SQL kódy. Tato definice je důležitá zejména pro praktické kapitoly 4.6 a 5, kde je takový ETL proces sestaven. Kapitoly 4.3 (fáze výběru), 4.4 (fáze upravení) a 4.5 (fáze nahrání) představují a porovnávají možnosti, které databáze Oracle a jazyk SQL nabízejí v jednotlivých fázích ETL procesu. Je však třeba zdůraznit, že zařazení jednotlivých metod do určité fáze je určeno četností jejích použití v této fázi. Metoda z jedné fáze však může být použita i v jiné fází, je-li to vhodné. Dále platí, že s těmito metodami se nelze setkat pouze u ETL procesů, ale lze je využívat v Oracle databázi obecně. V případech, kde dochází k vzájemnému srovnání několika možností k řešení stejného problému, jsou definovány vstupní objekty. Objekty jsou pro účely testu nově vytvořeny a mají stejné vlastnosti. Testované příkazy jsou následně spuštěny ve stejný čas, aby se omezily vnější vlivy (vytížení procesorů, pevných disků, SGA paměti apod.). V testech nejsou důležitá absolutní čísla, která se odvíjí od použitého hardware, nýbrž ty relativní, pomocí kterých lze porovnat výhodnost použití určité varianty.

4.1

Základy ladění v Oracle databázi

Cílem této kapitoly je představit hlavní pojmy v oblasti ladění. Aby mohl vývojář úspěšně ladit dotazy a příkazy v jazyce SQL, musí mít přehled o dostupných možnostech a funkcích, a vhodnosti jejich použití. Rovněž musí vědět, jaké možnosti nabízí Oracle databáze, znát fungování optimalizátoru a rozumět zpracování dotazů.

4.1.1

Oracle optimalizátor

SQL příkaz určuje „CO“ se má udělat - vybrat, nahrát, smazat, upravit data, vytvořit či odstranit tabulku nebo index atd. Exekuční plány naproti tomu určují „JAK“ se má to udělat. V oblasti ladění a optimalizace SQL příkazů představují exekuční plány první možnost, pomocí které lze hledat příčiny neefektivit a měly by být analyzovány před aplikací konkrétního řešení (paralelismus, hinty, apod.). Exekuční plány sestavuje optimalizátor Oracle a představují posloupnost kroků, které je třeba vykonat k provedení SQL příkazu. Jednotlivé kroky určují (Žák, 2010): ● pořadí, v jakém SQL příkaz přistupuje k tabulkám,


16

● přístupovou metodu pro každou tabulku, ● spojovací metodu pro každou operaci spojení, ● operace prováděné na datech (filtr, agregace, apod.). Již od verze Oracle7, představené v roce 1992, je optimalizátor založen na principu cen (cost-based optimizer2). K efektivnímu výběru dat však potřebuje, aby měl aktuální informace o objektech v databázi. Ty je třeba pro tento účet analyzovat a získat potřebné statistické údaje. Frekvence analýzy objektů se odvíjí od četnosti změn, které jsou nad daty prováděny. V OLTP systémech je obvyklé, že se data analyzují po určitých časových intervalech, ideálně při nižším vytížení databáze v nočních hodinách. Naproti tomu v datových skladech a systémech, kde dochází k dávkovým transakcím, je třeba analyzovat po každé větší transakci (Loney, Bryla, 2006) (Price, 2007). Sběr statistik je potřeba vypnout z původního automatického režimu a zajistit jeho provádění ručně. Analýza objektů se tak stává běžnou součástí ETL procesů. Co však znamenají ceny v optimalizátoru Oracle? Exekuční plány sestávají z několika kroků, které je zapotřebí provést k získání dat. Každý krok je na základě Oraclu dostupných údajů ohodnocen určitým číslem, jehož výpočet se každou verzí Oracle mění a kromě započítání V/V operací a využití CPU, do tohoto výpočtu vstupují statistické údaje o datech a indexech, a různé dočasné výpočty, které si databáze vytváří. Obecně však dle Lewise (Lewis, 2006) vždy platilo pravidlo, že ceny představují časový odhad k dosažení jednotlivých kroků v exekučním plánu. Toto tvrzení následně dokládá slovním překladem vzorce, jež Oracle uvádí jako metodu pro výpočet cen. Následuje volný překlad: „Cena je tvořena součtem časů potřebných pro jednobloková čtení, vícebloková čtení a času procesoru, to celé vyděleno časem pro jednoblokové čtení. Z toho plyne, že cena představuje celkový časový odhad potřebný k provedení příkazu, vyjádřený v jednotkách jednoblokového čtení“. Odhad vytvořený optimalizátorem však nemusí vždy odpovídat realitě, a ačkoli je každou verzí vylepšován, je třeba v některých případech provést ruční doladění. Lewis (Lewis, 2006) uvádí 6 hlavních důvodu, kdy a proč může optimalizátor vytvářet nesprávné odhady. ● Nesprávné předpoklady jsou již vytvořeny v samotném modelu cen. ● Statistiky jsou dostupné, avšak jejich údaje jsou zavádějící. ● Statistiky nejsou dostupné. ● Výkonové charakteristiky použitého hardware nejsou známé. ● Není známé aktuální vytížení databáze.

2

Do verze Oracle6 existoval pouze tzv. rule-based optimizer, který využíval k sestavování exekučních plánů seznam definovaných pravidel. Ten zůstává v nových verzích Oracle pouze z důvodu zpětné kompatibility a Oracle doporučuje využívat pouze cost-based optimizer (ORACLE, 2013).


17

● V použitém kódu jsou chyby (bugs). Uvedené příčiny mohou vést k tomu, že pro stejný SQL příkaz, může optimalizátor sestavit v různých časech různý exekuční plán. Z toho vedou dvě důležitá ponaučení nejen při modelování ETL procesů. ● SQL příkazy je vhodné doplnit hinty, i když optimalizátor sestavuje exekuční plán efektivně. ● Exekuční plány provedených SQL příkazů je vhodné ukládat pro pozdější porovnání při hledání příčin poklesu výkonosti.

4.1.2

Analýza objektů a sběr statistických dat

Výpočet statistických údajů je důležitý pro správné rozhodování Oracle optimalizátoru při výběru dat (příkaz SELECT). Oracle nabízí v balíčku DBMS_STATS sadu procedur, které slouží k získávání, úpravě, prohlížení, exportování, importování a mazání statistik. Lze provádět analýzy celé databáze, schéma nebo vybraných tabulek, oddílů či indexů. Pokud jsou statistiky nastaveny na automatický režim, databáze sleduje veškeré změny v tabulkách (vykonané DML příkazy) a na jejich základě rozhoduje o aktuálnosti statistických dat. Tyto změny lze získat v systémovém pohledu DBA_TAB_MODIFICATIONS. Statistické údaje o objektech jsou uloženy v datovém slovníku a lze je pohlížet v systémových pohledech DBA_TABLES, DBA_INDEXES, DBA_TAB_COL_STATISTICS a dalších (Loney, Bryla, 2006). Konkrétní údaje, které jsou analýzou shromažďovány lze nalézt v oficiální dokumentaci (ORACLE, 2013): ● Statistiky u tabulek tvoří: ● počet řádků, ● počet bloků, ● průměrná délka řádku. ● Statistiky u sloupců tvoří: ● počet rozdílných hodnot, ● počet prázdných (NULL) záznamů, ● distribuce dat (histogram). ● Statistiky u indexů tvoří: ● počet bloků v listech (nemající potomka v stromové struktuře) indexu, ● úrovně, ● clustering factor.


18

● Systémové statistiky tvoří: ● V/V výkon a jeho využití, ● výkon procesoru a jeho využití. Nejčastěji bývá analýza vytvářena nad tabulkou nebo oddílem. Syntaxe příkazu pro tabulku je následující. EXEC DBMS_STATS.gather_table_stats('DW_OWNER', 'DW_CUSTOMERS', estimate_percent => 15, cascade => TRUE); Jako vstupní parametry procedury jsou zadány vlastník tabulky, název tabulky, procento dat, které poslouží jako vzorek, a hodnota cascade určuje, zda se mají analyzovat i indexy, které jsou nad tabulkou vytvořeny. Kompletní analýzy velkých tabulek mohou být časově velmi náročné3, a proto se statistické údaje často vytváří pouze z procentuálního vzorku dat. Pokud je třeba provést analýzu dat nad určitými oddíly rozdělené tabulky, je vhodné načíst informace ze systémových pohledů a použít cykly v PL/SQL. Spuštěním následujícího kódu se nejprve načtou do kurzoru všechny oddíly vrácené na základě zadané podmínky (tzn. všechny oddíly od začátku roku 2012, které ještě nebyly analyzovány) a poté jsou postupně od nejnovější analyzovány i s příslušnými indexy. BEGIN FOR rec IN ( SELECT partition_name FROM all_tab_partitions WHERE table_owner = 'DW_OWNER' AND table_name = 'DW_CUSTOMER_ORDERS' AND partition_name >= ('PARTITION_20120101') AND last_analyzed IS NULL ORDER BY partition_name DESC) LOOP SYS.DBMS_STATS.GATHER_TABLE_STATS( OwnName => 'DW_OWNER', TabName => 'DW_CUSTOMER_ORDERS', PartName => rec.partition_name, Granularity => 'PARTITION', Estimate_Percent => 15, Cascade => TRUE); END LOOP; END;

3

K provedení analýzy dat je zapotřebí velké množství místa pro řadící operace a nebývají výjimkou ani kompletní průchody tabulkami (Loney, Bryla, 2006).


19

Běžnou praxí u rozdělených tabulek s velkým objemem dat v oddílech bývá i kopírování statistik mezi jednotlivými oddíly. V případech, kdy lze v oddílech očekávat velmi podobná data, je možné například jednou za měsíc nechat spočítat statistiky nad jedním denním oddílem, a ty v ETL procesu každý den kopírovat na oddíly nové. Ačkoli nemusí být tato metoda tak přesná, jako samotný výpočet statistických dat, lze pomocí ní ETL proces výrazně urychlit a zároveň odlehčit systémovým zdrojům, protože kopírování statistik je rychlejší a méně náročné, než jejich spočítání. Ke kopírování statistik lze použít proceduru, která je součástí balíčku DBMS_STATS od verze Oracle10g. Proceduru lze spustit následovně. exec dbms_stats.copy_table_stats('SCHEMA', 'NAZEV_TABULKY', srcpartname => 'NAZEV_ZDROJOVEHO_ODDILU', dstpartname => 'NAZEV_CILOVEHO_ODDILU');

4.1.3

Generování exekučních plánů

Oracle nabízí několik nástrojů, pomocí nichž lze zobrazit exekuční plány, sestavené optimalizátorem, pro SQL příkazy. Cílem této kapitoly je představit dva základní nástroje, které by měl každý vývojář znát. Jedním z nástrojů je příkaz EXPLAIN PLAN, který se vkládá před laděný příkaz SQL. Výhodou příkazu EXPLAIN PLAN je jeho rychlost, protože žádná data nejsou vybírána nebo měněna, pouze je do tabulky PLAN_TABLE uložen exekuční plán, který by použil optimalizátor v případě, že by byl SQL příkaz skutečně odeslán. Před prvním použitím příkazu EXPLAIN PLAN je třeba tabulku PLAN_TABLE vytvořit. K tomuto účelu lze použít skript utlxplan.sql, který je součásti instalace Oracle databáze. Pokud je tato tabulka sdílena mezi více vývojáři, je vhodné pojmenovat vlastní exekuční plány pomocí klauzule SET STATEMENT_ID (Niemiec, 2007). Zobrazení vygenerovaných exekučních plánů se provádí voláním funkce DBMS_XPLAN.DISPLAY. EXPLAIN PLAN SET statement_id = 'muj_expl' FOR SELECT * FROM DUAL; Explained. SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'muj_expl')); -------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------


20

Druhou možností, jak zobrazit exekuční plán je zapnutí AUTOTRACE v programu SQL*Plus. Uživatel musí mít přidělenou roli PLUSTRACE a musí být vytvořena tabulka PLAN_TABLE v aktuálním schématu nebo ve schématu SYSTEM. Příkazem SET AUTOTRACE ON se aktivuje vypisování exekučních plánu a statistik pro všechny prováděné příkazy. Pokud není žádoucí provedení příkazu, ale pouze zobrazení exekučního plánu, je třeba nastavit SQL*Plus příkazem SET AUTOTRACE TRACE EXPLAIN. Jednotlivé kroky v exekučním plánu jsou sestavené v hierarchii předek-potomek. Řádky jsou odsazeny dle úrovní, takže kořenový krok je odsazen nejméně a čím je potomek vzdálenější, tím je odsazení větší. Exekuční plán se čte od vnitřních úrovní směrem ven, a pokud jsou dva kroky na stejné úrovni, čtou se odshora dolu.

4.1.4

Principy přístupových cest

Ke správnému interpretování exekučních plánů a pochopení prováděných operací je třeba znát typy přístupových cest a vhodnost jejich použití v konkrétních případech. Přístupové cesty představují metody, jakým způsobem získává Oracle data. Autorem hlavního informačního zdroje použitého v této kapitole je Žák (Žák, 2010). Úplné prohledávání (TABLE ACCESS FULL) Při úplném prohledávání čte Oracle všechny datové bloky v segmentu od začátku do konce. Je možné číst více bloků současně a snížit tak čas zpracování dotazu. Metoda je vhodná především při čtení velkých objemů dat s více, než přibližně 5 % vrácených záznamů. Přístup pomocí adresy ROWID (TABLE ACCESS BY INDEX ROWID) Adresy ROWID představují fyzickou adresu dat. Jedná se o znakový řetězec o délce 18 znaků mající formát OOOOOOFFFBBBBBBRRR. Informace lze z tohoto formátu získat použitím balíčku DBMS_ROWID, přičemž význam jednotlivých částí adresy ROWID je následující. ● O - data object number a identifikuje segment databáze. ● F - datový soubor se záznamem. ● B - datový blok v rámci datového souboru, který obsahuje záznam. ● R - row, číslo záznamu v datovém bloku. V případě metody přístupu pomocí adresy ROWID, Oracle nejprve prohledá index u sloupce, který je v podmínce WHERE, získá ROWID adresu řádku a podle najde podle ní příslušný záznam. Oproti úplnému prohledávání tak nezáleží na počtu rozsahů v segmentu. Metoda je vhodná při hledání konkrétních záznamů malého objemu, u kterých je vytvořený index.


21

Zajímavou možnost představuje Žák (Žák, 2010) pro manuální nastavení paralelního zpracování dotazu. Vzhledem ke skutečnosti, že z adresy ROWID lze zjistit název datového souboru a jeho umístění na konkrétním pevném disku, lze spustit paralelně několik dotazů s úplným prohledáváním nad různými pevnými disky. SELECT * FROM test WHERE ROWID BETWEEN :a1 AND :a2 UNION ALL SELECT * FROM test WHERE ROWID BETWEEN :b1 AND :b2 UNION ALL SELECT * FROM test WHERE ROWID BETWEEN :c1 AND :c2;

Indexové prohledávání (INDEX SCAN) Indexové prohledávání obecně je často používanou metodou. Bloky na poslední úrovni indexu obsahují adresu ROWID, pomocí které lze záznam dohledat. Indexy jsou seřazené podle indexovaných hodnot a následně podle adresy ROWID, takže při nalezení prvního výskytu lze snadno určit, kterým směrem je potřeba hledat dále. V případě jedinečného indexu optimalizátor pozná, že je potřeba číst pouze do nalezení prvního záznamu. Počet úrovní v indexu se rovná počtu V/V operací, takže příliš úrovní v indexu znamená více větví k průchodu, a delší dobu potřebnou k nalezení záznamu. Z toho důvodu je vhodné indexy udržovat aktuální a v případě častých změn v záznamech je i znovu vytvářet, aby neobsahovaly příliš mnoho úrovní. Pokud jsou z tabulky vybírány jen určité sloupce, které jsou zařazeny v indexu, stačí přečíst pouze samotný index bez dalšího čtení tabulky. Proto bývá v určitých případech vhodné zařadit do indexu i sloupec, podle kterého se data nehledají (neobjevuje se v klauzuli WHERE), ale který je vybírán dotazem SELECT. Prohledávání rozsahů indexů (INDEX RANGE SCAN) Při prohledávání rozsahu indexu optimalizátor předpokládá nalezení žádného, jednoho nebo více záznamů. Rozsah lze procházet oběma směry a směr hledání je určen podle zadaného SQL dotazu. V případě následujícího dotazu optimalizátor rozpozná, že je vhodné číst index od konce a vyhnout se tak operaci řazení. SELECT cislo_zamestnance FROM zamestnanci ORDER BY cislo_zamestnance DESC;

Úplné prohledávání indexu (INDEX FULL SCAN) Použitím jednoblokových V/V operací se prochází pouze listové bloky indexu potřebné k nalezení prvního bloku. Mezi listy lze dále procházet pomocí ukazatelů a to oběma směry. Pořadí čtení dat z tabulky je určeno jejich seřazením v indexu. V následujícím dotazu optimalizátor použije úplné prohledávání indexu, začne od konce a zastaví se na první nalezené hodnotě, která je zároveň nejvyšší. SELECT MAX(cislo_zamestnance) FROM zamestnanci;


22

Úplné rychlé prohledávání indexu (INDEX FAST FULL SCAN) V porovnání s úplným prohledáváním indexu je při úplném rychlém prohledávání použita metoda víceblokového čtení všech bloků indexu a data nejsou načítána v seřazeném pořadí, ale tak, jak jsou uložena v tabulce. Z toho důvodu nejsou vrácená data seřazena, ale čtení indexů je rychlejší.

4.1.5

Metody spojování tabulek

Podobně jako v případě přístupových cest, je znalost metod spojování tabulek důležitá pro správné porozumění a schopnost efektivně ladit exekuční plány. Tyto metody popisují postup fyzického spojení tabulek v Oracle databázích, na rozdíl od logických metod, které patří k tématice relačních databází obecně (inner, outer, natural, equi-joins). Oracle nabízí několik metod spojování tabulek, a cílem této kapitoly je nejčastěji používané metody popsat a doporučit výběr pro konkrétní případy. Spojení vnořenými cykly (nested loop join) Metoda spojení vnořenými cykly pro každý řádek z vnější (outer nebo driving) tabulky prochází tabulku vnitřní (inner) a hledá shodu, která bude výsledkem spojení. Metoda tedy obsahuje dva cykly, jeden je vnořen druhého, a odtud vznikl název. K efektivnímu použití je třeba, aby měla vnitřní tabulka index vytvořený na sloupci, který slouží ke spojení. U vnější tabulky je index rovněž vhodný. V případě unikátního indexu (např. u primárního klíče) optimalizátor ukončí vnitřní cyklus po prvním vráceném záznamu a pokračuje v hledání dalšího záznamu z vnější tabulky. Pokud se v indexu hledá dle rozsahu, dochází ke snížení V/V operací logického a fyzického čtení omezením množství záznamů. Tato metoda je vhodná pro rychlé nalezení prvních řádků pro výstup, zatímco se hledají další. Ideálním případem použití je spojení malé vnější tabulky s větší tabulkou, která ke spojení využije sloupec s indexem s vysokou selektivitou. Naopak se doporučuje použití jiných metod v případech, kdy vnitřní tabulka nemá index nebo má index s malou selektivitou a vnější tabulka je relativně velká (Niemiec, 2007). V porovnání s dalšími metodami lze u této nejlépe odhadnout cenu za spojení tabulek. Cena se skládá z následujících faktorů (Lewis, 2006). ● Cena získání všech potřebných záznamů z vnější tabulky. ● Počtu záznamů ve vnější tabulce. ● Cena za nalezení záznamu ve vnitřní tabulce odpovídajícímu právě nalezené hodnotě ve vnější tabulce.


23

Vzorec má tedy následující podobu. Cena za získání dat z vnější tabulky + počet jedinečných výsledků z vnější tabulky × cena za nalezení záznamu ve vnitřní tabulce Použití spojení vnořenými cykly lze optimalizátoru vnutit pomocí hintu use_nl. Výkonnostní problém však může nastat v případě neexistence indexů, kdy je tato metoda velmi neefektivní. Vhodnějším řešením je hint use_nl_with_index, který vynutí použití tohoto typu spojení pouze v případě existence vhodných indexů (Burleson, 2009). Spojení sloučením po seřazení (sort merge join) Oracle v případě této metody seřadí podle spojovacího sloupce nejprve první vstup, poté druhý a následně shodné výsledky sloučí dohromady. Sloučení probíhá tak, že pro každý řádek z prvního vstupu se nalezne první shoda v druhém vstupu a pokračuje se v načítání záznamů až do prvního rozdílu, což je možné právě díky seřazeným vstupům. Tato metoda se používá v případě nevhodných nebo chybějících indexů a pro spojení dvou velkých tabulek. Podmínkou však je operátor rovnosti mezi klíči, které slouží ke spojení (tzv. equijoin – WHERE a.zak_id=b.zak_id). Z důvodu provádění operací řazení je třeba mít dostatek volného místa paměti a místa v dočasných segmentech. Je tedy třeba počítat s vyšším využitím SGA paměti a případně i větším množstvím V/V operací na pevný disk kvůli využití dočasných tabulkových prostorů (Niemiec, 2007). Optimalizátor tuto metodu může volit pro příkazy obsahující ORBER BY nebo GROUP BY, protože nemusí výsledek znovu řadit (Žák, 2010). Vynutit tuto metodu pro spojení objektů lze použitím hintu use_merge. Spojení hash (hash join) Obecně účinnější metodou v porovnání s předchozí je spojení hash, které provádí operaci seřazení pouze na první (build) tabulce. Oracle za optimálních podmínek vybere jako první tabulku tu menší, provede úplné prohledání (omezené podmínkami v klauzuli WHERE), a vytvoří v paměti nad spojovacím sloupcem tabulku hash. Poté provede úplné prohledání i druhé (probe), obvykle větší, tabulky, každý řádek porovná s hash tabulkou a shodné hodnoty vrátí. Metoda spojení hash je podobná metodě spojení vnořenými cykly, jelikož jsou použity cykly k dohledání záznamů mezi hash tabulkou a druhou tabulkou, se kterou se spojení provádí. Spojení hash potřebuje k optimálnímu fungování více operační paměti, než spojení vnořenými cykly, protože v případě, kdy se nevejde tabulka do paměti, dochází k ukládání do dočasných tabulkových prostorů. Dále pak lze metodu použít pouze v případě equijoin. Vrácení prvního řádků může trvat déle, protože je možné až po prohledání první tabulky a vytvoření tabulky hash (Niemiec, 2007).


24

Cenu za spojení hash lze spočítat součtem tří hodnot. Vzorec má následující podobu (Lewis, 2006). Cena za získání dat z build tabulky + cena za získání dat z probe tabulky + cena za využití procesoru na vytvoření hash tabulky a porovnávání Optimalizátoru lze vynutit použití spojení hash pomocí hintu use_hash.

4.1.6

Hinty

Hinty v Oracle představují instrukce pro optimalizátor, jakým způsobem si vývojář přeje, aby byla data vybrána. Používají se v případech, kdy optimalizátor vyhodnotí cestu k datům nesprávně nebo je třeba určit jinou metodu, než zvolí optimalizátor, protože má vývojář lepší znalosti o datech, než lze získat z jejich analýzy. Pokud je hint správně zadaný a proveditelný, je exekuční plán příslušně upraven. V opačném případě je bez oznámení chyby ignorován. Hinty se vkládají do SQL příkazů do komentářů s použitím znaménka „+“ (/*+ USE_HASH(t) */) a lze je rozdělit do následujících skupin. Nejedná se o kompletní výčet hintů, ale o přehled těch základních a nejčastěji používaných (ORACLE, 2013). Hinty pro určení cíle optimalizace V této skupině lze nalézt dva hlavní hinty. ● all_rows – optimalizátor zvolí exekuční plán s nejlepším průchodem dat a zároveň s nejnižším použitím zdrojů. ● first_rows(n) – optimalizátor zvolí takový plán, který jako první vrátí n záznamů. Při spojování tabulek tedy nejčastěji zvolí spojení vnořenými cykly (nested loop join). Poznámka: Pro určení optimálního exekučního plánu je třeba mít spočítané statistiky nad prohlíženými objekty. Bez statistik vychází optimalizátor z hodnot, které má k dispozici, jako například velikost alokovaného prostotu. Hinty pro výběr přístupové cesty4 Úplné prohledání tabulky lze zadat hintem full. K výběru konkrétního indexu pro indexové prohledávání lze použít hint index. Je možné nezadat název index nebo zadat jeden i více indexů. Optimalizátor vždy vybere jeden nebo kombinaci více indexů, které vyhodnotí s nejnižší cenou. V případě prohledávání rozsahů indexů, lze zadat směr čtení indexem hintem index_desc nebo index_desc. No_index zaká-

4

Principy přístupových cest popisuje kapitola 4.1.4.


25

že použití konkrétních indexů. Úplné rychlé prohledání indexu, resp. zakázání toho přístupu umožňuje hint index_ffs, resp. no_index_ffs. Poznámka 1:Pokud je zvolen některý z hintů pro indexové prohledávání, optimalizátor nezvažuje cenu úplného prohledání, i kdyby byla výhodnější. Poznámka 2: Schéma objektů se v hintech neuvádí. Pokud je v SQL příkazu za tabulkou uvedený alias, je nutné, aby byl v hintu definován alias, místo názvu tabulky. Hinty pro operaci spojení Základní metody pro spojování tabulek popisuje kapitola 4.1.5 a u každé metody je představen i hint, pro její zvolení - use_nl, use_nl_with_index, use_hash, use_merge. Optimalizátor může hint ignorovat v případech kdy zadanou metodu nelze pro spojení objektů použít. Použití konkrétní metody je možno i zakázat použitím prefixu no_ k hintu no_use_nl, no_use_hash, no_use_merge. Hinty pro pořadí spojování tabulek Pořadí tabulek pro jejich spojení může mít významný vliv na výkon a k jejímu určení existují dva hinty. ● leading – optimalizátor vybere tabulky ke spojení v pořadí definovaném v hintu. ● ordered – optimalizátor vybere tabulky ke spojení v pořadí, v jakém se vyskytují za klauzulí WHERE.

4.1.7

Paralelní zpracování

Funkce paralelního zpracování je dostupná pouze ve verzi Oracle Enterprise Edition. Jedná se o možnost fyzicky rozdělit velké úlohy (DDL, DML i příkazy SELECT) na menší části, které jsou zpracovány současně. Správným použitím paralelismu lze v některých případech výrazně snížit dobu zpracování (Kyte, 2005). Ačkoli je možné zpracovávat většinu příkazů paralelně, měla by být tato možnost využita pouze v nutných případech a s dostupnými zdroji (procesory, operační paměť, diskové pole). Existuje mnoho dalších možností, jak optimalizovat příkazy bez použití paralelismu, a některé z nich jsou představeny v této práci. Povolení paralelního zpracování pro více uživatelů, kteří budou tuto možnost často využívat, může vést k přetížení dostupných zdrojů. Kyte (Kyte, 2005) doporučuje uvažovat o paralelismu při splnění následujících podmínek: ● Jedná se o velkou úlohu, např. úplné prohledání tabulky o velikosti 50 GB.


26

● Je k dispozici dostatek volných zdrojů. Volné procesory jsou třeba pro spuštění paralelních procesů. Data musí být rozmístěna na více fyzických disků a čtení by mělo probíhat přes více V/V kanálů. Spustit SQL dotaz paralelně však lze i na serveru s jedním procesorem. SQL dotaz je možné paralelizovat použitím hintu parallel nebo lze nastavit stupeň paralelismu přímo na tabulku ALTER TABLE dw_orders PARALLEL 4. Stupeň paralelismu představuje počet paralelních serverů (parallel execution server) přidělených pro úlohu. Pokud není stupeň zadán, Oracle jej určí sám na základě dostupných zdrojů. Zda je paralelismus použit, lze zjistit z exekučního plánu. SELECT /*+ parallel(o, 4) */ COUNT (*) FROM dw_orders o; -----------------------------------------------------------| Id | Operation | Name | -----------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | | 4 | SORT AGGREGATE | | | 5 | PX BLOCK ITERATOR | | | 6 | TABLE ACCESS FULL | DW_ORDERS | -----------------------------------------------------------5. krok v exekučním plánu rozdělí úplný průchod tabulkou na několik částí. Pro ty je zjištěn počet záznamů a informace jsou následně zaslány koordinátoru paralelního zpracování (2. a 3. krok). Pro paralelní zpracování DML příkazů nestačí nastavit paralelismus na tabulce, ale je nutno nastavit session příkazem: ALTER SESSION ENABLE PARALLEL DML; Během paralelního zpracování DML příkazů existují následující omezení (Kyte, 2005). ● Triggery nejsou podporovány. ● Některá integritní omezení nejsou podporována. ● Tabulka není dostupná, dokud není proveden příkaz COMMIT nebo ROLLBACK. ● Pokud je tabulka rozdělená a obsahuje bitmapové indexy nebo sloupce typu LOB, nesmí stupeň paralelismu přesáhnout počet zainteresovaných oddílů. Pro paralelní vykonání DDL příkazů, je třeba přidat klauzuli PARALLEL, a lze ji použít pro následující příkazy: ● CREATE INDEX,


27

● CREATE TABLE t AS SELECT (CTAS), ● ALTER INDEX REBUILD | SPLIT [[SUB]PARTITION], ● ALTER TABLE t MOVE, ● ALTER TABLE t (SPLIT | COALESCE) PARTITION.

4.1.8

Klauzule logging a nologging

Často kladenou otázkou v databázi Oracle je, zda lze vypnout logování (ukládání dat do souborů protokolu). Úplné vypnutí není možné, protože se jedná o kritickou funkcionalitu v databázích. Pokud by to možné bylo, dostala by se databáze po selhání diskového úložiště nebo ztrátě napájení do nepoužitelného a neobnovitelného stavu. Na druhou stranu existují některé operace, které lze provést s generováním výrazně nižšího objemu souborů protokolu (redo log), než běžně5. U takových operací je třeba přidat klauzuli NOLOGGING namísto defaultního LOGGING. Hlavní výhody použití možnosti NOLOGGING dle oficiální dokumentace jsou (ORACLE, 2013): ● úspora místa v souborech protokolu, ● snížení času potřebného k vytvoření tabulky nebo indexu, ● zvýšení výkonu při paralelním vytváření velkých tabulek. Ačkoli lze prováděním NOLOGGING operací výrazně zvýšit výkon, jedná se o riskantní operaci, která může vést ke ztrátě dat6 při obnově databáze. Z toho důvodu je vhodné provést zálohu před i po provedení těchto operací, používat tuto klauzuli jen pro dočasné (stage) tabulky nebo v případech, kdy lze data snadno a rychle získat z jiného objektu. NOLOGGING nelze použít u DML příkazů. Výjimkou je direct path INSERT (viz kapitolu 4.5.2), INSERT vzniklý z příkazu MERGE a použití nástroje SQL*Loader. Na tabulku, oddíl, nebo tabulkový prostor je třeba nastavit atribut NOLOGGING. Ke změně NOLOGGING atributu u tabulky slouží příkaz ALTER TABLE NOLOGGING; Pro DDL příkazy je možno klauzuli NOLOGGING použít pro (ORACLE, 2013): ● CREATE TABLE t AS SELECT (CTAS) ● ALTER TABLE t MOVE ● ALTER TABLE t (ADD | MERGE | SPLIT | MOVE | MODIFY) PARTITION ● CREATE INDEX ● ALTER INDEX REBUILD | SPLIT [[SUB]PARTITION]

5

Toto platí v případě, že databáze není nastavená v módu FORCE LOGGING.

6

Může se vyskytnout chyba ORA-01578: ORACLE data block corrupted.


28

V případě chyby během tvorby nebo znovu sestavování indexu nehrozí riziko ze ztráty dat, jelikož je možné spustit příkaz znovu. To samé platí i v případě příkazu CTAS, pokud je stále k dispozici zdroj, ze kterého byla tabulka vytvářena.

4.1.9

Datové bloky a řetězení řádků

K pochopení databázových struktur lze využít schéma z (Best, Billings, 2005), které dělí struktury na fyzické a logické. Zatímco fyzické struktury jsou spravovány operačním systémem, logické patří databázovému systému. Grafické znázornění jednotlivých úrovní včetně vzájemné kardinality zobrazuje obrázek 1.

Obrázek 1 - Grafické znázornění logických a fyzických struktur (Best, Billings, 2005)

Existují tři základy typy bloků – volné, použité a nepoužité. Volný blok je připravený k vložení nebo úpravě dat. Blok zůstává volný, dokud nezbývá procento volného místa, definováno hodnotou PCTFREE při vytvoření segmentu. Jakmile je tato hodnota překročena, stává se blok použitým, a zbývající volné místo lze využít pouze pro úpravy stávajících dat. Blok se může stát opět volným, pokud objem dat klesne pod úroveň hodnoty PCTUSED. Nepoužitý blok je takový, do kterého nebylo ještě nikdy zapisováno. Pokud velikost řádku po úpravě překročí velikost bloku, je část řádku uložena do dalšího bloku tak, že v původním bloku vznikne odkaz na nový blok (zřetězení řádku) nebo jej Oracle přesune do nového bloku (migrace řádku). Pokud je velikost řádku větší, než velikost bloku je rovněž zřetězen. Zřetězené řádky mohou působit problémy s výkonem při čtení tabulky, jelikož roste počet V/V operací čtením více bloků. Migrace řádku zase ovlivňuje výkon transakcí, protože „Oracle musí dynamicky upravovat místa v různých blocích a přistupovat do seznamu volných bloků“ (Loney, Bryla, 2006). Zjistit počet zřetězených řádků je možné například se statistik, konkrétně je jedná o hodnotu table fetch continued row v systémovém pohledu V$SYSSTAT.


29

Během vytváření tabulky lze definovat velikost prvního a následujících rozsahů (extent). První rozsah je alokován okamžitě i pro prázdnou tabulku a následující rozsah je alokován, jakmile je obsazen předchozí. Pokud je třeba vytvořit tabulku s počátečním rozsahem 64 kB a každým dalším o stejné velikosti, je třeba použít klauzuli STORAGE s následujícími hodnotami. CREATE TABLE test (...) STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0); Hodnota PCTINCREASE určuje, o kolik procent má být nový rozsah větší, než předchozí. Niemiec (Niemiec, 2008) doporučuje volit velikost rozsahu tak, aby jeho hodnota byla násobkem datového bloku. Pojem high water mark (dále HWM) je klíčový v oblasti optimalizace využití logického a tím pádem i fyzického místa v databázi. Pokud by byly všechny bloky tabulky vyrovnány jeden vedle druhého v řadě zleva doprava, pak HWM představuje hodnotu bloku nejvíce vpravo (Kyte, 2005). Jedná se tedy o poslední blok tabulky. Oracle díky HWM přesně ví, kde jsou data z tabulky uloženy, a které bloky je třeba přečíst při prohledávání celé tabulky (full table scan). Po založení tabulky je hodnota HWM 0 a roste při obsazování nepoužitých bloků. Mazání dat příkazem DELETE může změnit blok na volný, ale hodnotu HWM nesníží. Pokud následně dojde k dalšímu vkládání dat, obsazují se nejprve volné bloky (pokud není použit režim direct path – viz kapitolu 4.5.2) a pokud jejich objem nestačí, data se vkládají do nepoužitých bloků, čímž hodnota HWM opět roste. Pod úrovní HWM se tedy může vyskytovat velký objem volného místa, které nemohou využít jiné segmenty (blok může být alokovaný právě jednomu segmentu), a které musí Oracle přečíst. Uvolnit bloky lze až odstraněním tabulky (DROP) nebo vymazáním dat ze segmentu (TRUNCATE). Pokud je například vytvořena nová tabulka, do které je vloženo 100 000 záznamů, zabere spočítání záznamů stejný čas, jako by trvalo spočítání záznamů i po následném odmazání poloviny záznamů. Hodnota HWM je totiž v obou případech stejná a Oracle musí číst stejný počet bloků, přestože čte i prázdné bloky. Platí tedy, že čím větší počet vykonaných příkazů DELETE, tím více je tabulka fragmentovaná a tím horší výkon obecně pro příkazy DML i SELECT (Kyte, 2005). Zřetězení řádků lze částečně snížit vhodným nastavením velikosti datového bloku, a hodnot PCTFREE a PCTUSED. Nesprávné nastavení těchto hodnot však může vést k opačnému účinku, kdy jsou bloky využívány pouze z části, a počet zřetězených řádků roste. Loney doporučuje výchozí hodnotu PCTFREE 10 „zvýšit v případě, kdy aplikace bude ve sloupcích velmi často provádět nahrazování hodnoty NULL nějakou neprázdnou hodnotou nebo při časté aktualizaci dlouhých textových hodnot (Loney, Bryla, 2006)“. Obecně je vhodné zvýšit hodnotu PCTFREE při častých změnách nebo naopak snížit, pokud jsou budoucí změny předpokládány minimální nebo žádné. Jednodušší a vývojáři dostupnější metodou k efektivnímu využití bloků v segmentu je použití příkazu MOVE. Příkaz MOVE slouží k přesunu dat z tabulek nebo oddílů do nových segmentů a tabulkových prostorů, a přesunem snížit množství zřetězených řádků (řádky,


30

které se nevejdou do jednoho bloku, budou zřetězeny automaticky) a rovněž snížit hodnotu HWM. Data jsou znovu přeskládána jako při prvním vložení do volných bloků a bloky jsou tak efektivně využity. Vzhledem k tomu, že při přesunu jsou záznamům přiděleny nové adresy ROWID, stávají se existující indexy neplatnými a je třeba je znovu sestavit. To samé platí i pro statistiky (ORACLE, 2013). Dalším příkazem, který souvisí s tématikou je DEALLOCATE UNUSED. Spuštěním příkazu ALTER TABLE test DEALLOCATE UNUSED lze uvolnit místo odebráním rozsahů (extent) nad úrovní HWM. K provedení příkazu není třeba přesouvat data, takže bloky pod úrovní HWM nejsou ovlivněna. To je vhodné v případech, kdy se nečekává další přísun dat do tabulky. V opačném případě by Oracle musel znovu vytvářet rozsahy pro tabulku a to by vkládání dat spíše zpomalilo. Pomocí příkazu MOVE lze výšit výkon SQL příkazů vykonávaných nad tabulkou a lépe uspořádat volné místo uvnitř a mezi bloky v segmentu. Nezmění se však celková velikost tabulky a neuvolní se místo, které by mohly využít další segmenty v rámci tabulkového prostoru, jako při použití příkazu DEALLOCATE UNUSED. Pro tento účel byl v Oracle10g představen příkaz pro zmenšení - SHRINK. Lze ho použít pro klasické (heap) tabulky, oba typy indexů i materializované pohledy v tabulkových prostorech typu ASSM (Automatic Segment Space Management). Spuštěním příkazu7 ALTER TABLE test SHRINK SPACE se zmenší segment tak, že se bloky přeskládají, posune se HWM, jako v případě MOVE, a dále se bloky zkomprimují. Rovněž se uvolní nevyužité místo dalším segmentům, jako při použití DEALLOCATE UNUSED. Pokud je zadána klauzule SHRINK SPACE COMPACT, není HWM změněn. Klauzulí SPACE CASCADE jsou zmenšeny i závislé objekty. Během zmenšování rovněž dochází ke změnám ROWID adres při přesunu bloků, ale indexy jsou aktualizovány, takže nebudou po dokončení operace zneplatněny (Niemiec, 2007). Následující příklad potvrdí výše popsané. Je vytvořena tabulka obsahující informace o dostupných objektech. CREATE TABLE test AS SELECT * FROM all_objects; Table created. Počet záznamů, datových bloků, rozsahů a objem zjistí následující dotazy. SELECT COUNT (*) FROM test; COUNT(*) ---------564782

7

Před spuštěním příkazu musí být povolen přesun řádků (ALTER TABLE test ENABLE ROW MOVEMENT).


31

SELECT segment_type, segment_name, blocks, extents, bytes / 1024 / 1024 "size MB" FROM user_segments WHERE segment_name = 'TEST'; SEGMENT_TYPE SEGMENT_NAME BLOCKS EXTENTS size MB ------------- ------------- ------- -------- -------TABLE TEST 5120 80 80 Z tabulky se odstraní všechny řádky, které jsou v pořadí 100 000 a výš. DELETE FROM test WHERE (ROWID, 6) IN (SELECT ROWID, length(ROWNUM) FROM test); 465471 rows deleted. Nová kontrola tabulky ověří, že počet záznamů klesl, ale segment zůstal beze změn. COUNT(*) ---------99999 SEGMENT_TYPE SEGMENT_NAME BLOCKS EXTENTS size MB ------------- ------------- ------- -------- -------TABLE TEST 5120 80 80 Příkaz MOVE znovu uspořádá tabulku a sníží HWM. ALTER TABLE test MOVE; Table altered. SEGMENT_TYPE SEGMENT_NAME BLOCKS EXTENTS size MB ------------- ------------- ------- -------- -------TABLE TEST 896 28 14 Pomocí klauzule DEALLOCATE UNUSED lze ještě uvolnit pár nevyužitých datových bloků. ALTER TABLE test DEALLOCATE UNUSED; Table altered. SEGMENT_TYPE SEGMENT_NAME BLOCKS EXTENTS size MB ------------- ------------- ------- -------- -------TABLE TEST 852 28 13.3125 Následně je celý postup zopakován až za bod, kdy došlo ke smazání dat. Pomocí klauzule SHRINK SPACE lze nahradit příkaz MOVE i klauzuli DEALLOCATE UNUSED. Výsledný počet bloků, rozsahů i velikost je stejná.


ALTER Table ALTER Table

32

TABLE test ENABLE ROW MOVEMENT; altered. TABLE test SHRINK SPACE; altered.

SEGMENT_TYPE SEGMENT_NAME BLOCKS EXTENTS size MB ------------- ------------- ------- -------- -------TABLE TEST 852 28 13.3125

4.1.10 Shrnutí Cílem této kapitoly je shrnout podstatné body z kapitoly 4.1 a následných podkapitol. Při ladění v databázích Oracle je vždy důležité sledovat exekuční plány a umět je interpretovat. Pro vývojáře by se mělo jednat o první údaj, který zkontroluje při poklesu výkonu, spolu se změnou objemu zpracovávaných dat. Exekuční plány z jednotlivých částí ETL procesu je vhodné ukládat, jelikož mohou při problémech s výkonem poukázat na příčinu. Ačkoli optimalizátor potřebuje k sestavování nejrychlejších exekučních plánu statistická údaje, je analýza dat časově náročná operace. Malý vzorek dat analýzu urychlí, avšak na úkor její přesnosti. Z toho důvodu se analýza provádí často až u cílových tabulek, do kterých uživatelé pouští své SQL dotazy. Vývojář by měl znát optimální cesty při zpracování dat, a používat hinty k jejich nastavení. To platí i v případě, kdy optimalizátor sestaví optimální exekuční plán, protože ten se může v budoucnu změnit, čemuž hinty zabrání. V oblasti přístupu k datům se lze řídit celkem jednoduchých pravidlem. Pokud je počet záznamů menší, než 5 % z celkového objemu, je vhodnější použít index, v opačném případě se provádí úplný průchod tabulkou. 5 % je však přibližná hodnota a pro opakované dotazy se vyplatí provést testy, které tuto hodnotu upřesní. Zvolení správné metody pro spojení tabulek je podstatné, jelikož má velký vliv na výkon. Spojení vnořenými cykly je vhodné u aplikací, které potřebují získat první výsledky, co nejdříve. Je však potřeba, aby vnější tabulka nebyla příliš velká, a u vnitřní tabulky byl vytvořený index s vysokou selektivitou nad spojovacím sloupcem. Při nesplnění těchto podmínek budou výhodnější jiné metody. Metoda spojení sloučením po seřazení je naopak efektivní v případě neexistence vhodných indexů a pro příkazy obsahující klauzuli ORDER BY nebo GROUP BY. Metoda hash je kombinací obou předchozích metod, jelikož používá cykly a provádí řazení jedné tabulky. Zda je hash nejvhodnější metodou záleží na velikosti paměti, objemu dat a vytvořených indexech. Ačkoli Oracle umožňuje provádět SQL příkazy paralelně, je vhodné nejprve hledat správnou cestu, a až poté přidělovat více zdrojů. Na druhou stranu při úplném prohledání velké tabulky neexistuje mnoho možností, jak příkaz urychlit, a pokud nejsou systémové zdroje


33

přetížené, lze použitím paralelismu čtení urychlit. V každém případě lze doporučit, nechat stupeň paralelismu určit Oracle, dle aktuálně dostupných zdrojů.

Použití klauzule NOLOGGING může být nebezpečné v produkčním systému, ale v oblasti dočasných (stage) objektů, lze získat vyšší výkon s velmi nízkým rizikem. Nejčasnějším případem použití je tvorba indexů a příkaz CTAS, který vytvoří tabulky na základě dat z jiné tabulky. I v případě nepravděpodobného selhání databáze, lze tyto operace zopakovat. Zřetězené řádky nebo vysoko posunutý HWM mohou způsobovat výkonnostní problémy pro dotazy nad takovými objekty, a neefektivní využití diskového prostoru. V obou případech je obvykle hlavním viníkem příkaz DELETE. Hromadné mazání dat příkazem DELETE však není v ETL procesech obvyklé, a problémy tak vznikají po manuálních zásazích nebo z dlouhodobého hlediska. Doporučeno je zejména použití jednoduchého a efektivního příkazu MOVE k odstranění těchto problémů.

4.2

Definice ETL procesu

Teoretickou definici ETL procesu nabízí kapitola 3.3 – datové pumpy. Pro účely této práce je však potřeba tento termín blíže specifikovat a vymezit. Nejedná se o uměle vytvořenou charakteristiku, nýbrž o popis reálné implementace v produkčním datovém skladu. Graficky lze nejlépe zobrazit ETL proces jako zakořeněný strom z teorie grafů. Jedná se o acyklický orientovaný graf složený z hran a uzlů. Jeden uzel představuje význačný vrchol, tzv. kořen, za ním následuje neomezený počet potomků a předků. Uzel, který nemá žádné potomky, se nazývá list. Listy obsahují spustitelný SQL nebo PL/SQL kód. ETL proces tedy představuje sekvenci po sobě jdoucích nebo paralelně běžících SQL nebo PL/SQL modulů (bloků), které zajišťují načtení, úpravu a uložení dat s použitím objektů a funkcí databáze Oracle. Pro tvorbu těchto modulů, jejich vzájemného propojení, ukládání kódů a definování proměnných se používá nástroj Oracle Designer. Moduly mají určité vlastnosti, z nichž je třeba pro účely této práce zmínit zejména následující dva. ● Modul musí být restartovatelný v případě neúspěšného vykonání alespoň jedné jeho části. Pokud tedy neproběhne úspěšně veškerý jeho SQL nebo PL/SQL kód, je třeba modul znovu spustit, aniž by jeho předchozí neúspěšný běh ovlivnil následující výsledek. Tato vlastnost je velmi důležitá a proto se ji podrobněji věnuje kapitola 4.2.1. ● Po úspěšném vykonání modulu se provede příkaz COMMIT; ● Každý modul má přiřazen aktuální status: ● NEW – nový, čekající na čas spuštění, ● RUNNING - aktuálně běžící,


34

● ERROR - skončil chybou, ● WAIT - čekající na událost, ● OK - úspěšně dokončený. ETL procesy obvykle zpracovávají velké množství dat (záleží však na oblasti jeho použití – některé procesy mohou zpracovávat malé množství dat, které se ani v budoucnu nebude či nemůže kvůli jeho účelu zvyšovat), proto je třeba se již od počátku zaměřit na jeho optimalizaci. Čas věnovaný optimalizaci ve vývoji ETL procesů však musí být úměrný (v rámci projektu obhajitelný) jejímu přínosu. Ne vždy je lepším řešením maximální optimalizace, pokud bude její implementace stát mnoho zdrojů (času a tedy i peněz). Rovněž záleží na periodicitě ETL procesu. Na denní proces jsou kladeny vyšší časové nároky, než na měsíční. Není přijatelné, aby zpracování denního procesu trvalo déle, než 24 hodin. Pro určení výkonu ETL procesů, je nejprve potřeba definovat metriky. Dle těchto metrik lze hodnotit výkon jednotlivých modulů, posoudit potřebu optimalizace, a po implementaci optimalizačních technik, zhodnotit přínosy. Hlavní metriky a cíle optimalizace jsou dva. ● Zkrátit dobu zpracování příkazu. Maximální doba běhu jednoho modulu by měla být akceptovatelná8 a v případě dlouhé doby trvání je třeba tento modul rozdělit do více menších částí. Příliš dlouhé zpracování často odhaluje nevhodný exekuční plán a jsou s ním spojené další problémy (omezení velikosti tabulkového prostoru TEMP, uzamčení zpracovávaných objektů a jejich nedostupnost, odrolování velkého množství dat (využití návratových segmentů) v případě chyby apod.) ● Snížit využití systémových prostředků. Do této metriky lze zahrnout optimalizaci využití procesoru, pamětí, fyzického a logického úložiště dat i omezení síťového provozu. Existují však i další, vedlejší, metriky. ● Zajištění vysoké datové kvality. Patří sem především ošetření duplicitních záznamů, očištění vstupů a převod na společný formát, a další úpravy dat. ● Rozšiřitelnost vyvíjeného kódu. Procedury a funkce by měly být psány co možná nejobecněji, aby bylo jejich využití víceúčelové. Kód by měl být „čistý“, přehledný, okomentovaný a zdokumentovaný. ● Cena hotového řešení se skládá z lidských zdrojů (interní, externí, outsourcing), licence za použitý software, ceny hardware.

8

Akceptovatelná doba běhu se odvíjí od konkrétní situace. V případě vysokého rizika chyby je třeba dobu snížit. Rovněž je potřeba brát v úvahu objem zpracovávaných dat a důležitost včasné přípravy dat.


4.2.1

35

Opětovné spouštění modulu ETL procesu

Jednotlivé moduly v ETL procesu mají předem definované pořadí. Příprava databázové tabulky tak předchází vkládání dat a poté následuje znovuvytvoření indexů. Modul může skončit chybou, například kvůli chybě v syntaxi, nedostatečnému oprávnění, nedostatku místa v tabulkovém prostoru, vypršení platnosti záznamů v souborech protokolu (redo log) kvůli dlouhé době čekání na uzamčený objekt, neočekávaným datům apod. Pokud modul skončí chybou je prováděná transakce odrolována. Po patřičném zásahu, který odstraní příčiny chyby, se celý proces restartuje tak, že úspěšně dokončené moduly se přeskočí a ty chybné se spustí znovu. Je tedy důležité každý modul byl restartovatelný a tuto vlastnost uvažovat při tvorbě SQL nebo PL/SQL kódu. Příkaz SELECT žádná data nemění a může být vykonaný opakovaně. Ačkoli DML příkazy už data mění, je příkaz COMMIT proveden až v případě úspěšného dokončení modulu. To znamená, že DML příkazů může modulu obsahovat více, nesmí však mezi nimi být vložený COMMIT. V případě DDL příkazů provádí Oracle dvoufázový COMMIT, a to před příkazem, a po něm. Ke kombinování DDL a DML příkazů v rámci jednoho moduly by tedy nemělo docházet. K zajištění restartovatelnosti lze tedy obecně doporučit následující pravidla, která platí i pro tvorbu procedur a funkcí. ● Jeden modul může obsahovat více příkazů typu DML i SELECT nebo maximálně jeden příkaz typu DDL. Výjimkou může být kombinace příkazů TRUNCATE a INSERT. ● Modul by neměl obsahovat příkaz COMMIT.

4.3

ETL - Fáze výběru

Hlavním zaměřením této kapitoly je efektivní výběr a filtrování dat za pomoci dostupných metod, operátorů a příkazů.

4.3.1

Materializované pohledy a pohledy

Materializované pohledy a pohledy slouží v datových skladech často nejen jako zdroj dat, ale vyskytují se i na opačné straně ETL procesu - jako výstup předpřipravených dat pro uživatelské dotazy nebo reporty. Výhodou pohledů je, že pro SQL dotazy se chovají jako tabulky. Avšak data, která vracejí dotazům, mohou být výsledkem spojení více tabulek, mohou projít agregacemi, filtracemi, úpravami, a mohou být vybrány jen určité sloupce. Pro oba typy objektů je třeba nastavit uživatelům oprávnění, což s sebou přináší vyšší zabezpečení určitých dat díky výběru konkrétních sloupců, ke kterým mohou uživatelé přistupovat. Hlavním zdrojem pro tuto kapitolu jsou vlastní zkušenosti a (Loney, Bryla, 2006).


36

Pohledy Pohled je definován SQL dotazem, který je uložený v datovém slovníku. Nezabírá fyzicky žádný prostor, jelikož k výběru dat dochází až při zaslání dotazu do pohledu. Nad pohledem lze provádět i DML příkazy, které budou provedeny do příslušné tabulky nebo tabulek, pokud jsou v pohledu spojeny. Výjimku tvoří pohledy, které jsou definované pouze pro čtení nebo obsahují agregovaná data (v případě klauzule GROUP BY, DISTINCT, apod.). Při zpracování dotazu mohou být využity indexy, které jsou nad tabulkami vytvořeny. Pokud pohled slouží uživateli pro zobrazení agregovaných dat z velké tabulky, může dotaz trvat velmi dlouho a je vhodné zvážit, zda není vhodnější data agregovat do klasické (heap) tabulky a vytvořit pohled nad ní, nebo použít materializovaný pohled. Materializované pohledy Materializovaný pohled je rovněž definován SQL dotazem, ale na rozdíl od pohledu si alokuje prostor v segmentech, do kterých fyzicky ukládá data, která jsou výsledkem SQL dotazu. Dotaz do materializovaného pohledu tak může rychle vrátit předpřipravená data, jejichž výpočet stojí mnoho času a systémových prostředků. Materializované pohledy jsou podobné indexům tím, že zabírají prostor, jsou vázány na tabulky a je třeba je udržovat aktuální. Obnovování dat může probíhat automaticky v časových intervalech, při změně dat v tabulce z SQL definice (po potvrzení transakce), nebo ručně - na vyžádání. Aktualizace mohou být buď kompletní (refresh complete), kdy jsou data v materializovaném pohledu nahrazena novými, nebo přírůstkově (refresh fast), k čemuž je třeba mít vytvořený protokol materializovaného pohledu, který zasílá seznam změn. Přírůstková aktualizace je možná pouze v případě jednoduchých dotazů, kdy je Oracle schopen snadno určit, který záznam v pohledu ovlivní změna záznamu na zdroji. Volba typu aktualizace záleží na náročnosti SQL dotazu, objemu dat a četnosti prováděných změn. V případě objektu, který se často nemění, lze využitím přírůstkové aktualizace omezit síťový provoz. Pokud slouží materializovaný pohled pro replikaci dat ze zdrojových systémů do datového skladu, lze se nejčastěji setkat s aktualizací na určité časové (např. denní) bázi, po které je možné spustit ETL proces. Dokonce je možné v ETL procesu nastavit kontrolu aktualizace materializovaného pohledu a jeho pokračování po aktualizaci dat. Pokud je poslán SQL dotaz do stejných tabulek a se stejnými podmínkami, které se vyskytují v definici materializovaného pohledu, může optimalizátor zvolit výběr dat z pohledu namísto tabulek. Pro tuto možnost však musí být inicializační parametr QUERY_REWRITE_ENABLED nastaven na hodnotu TRUE a zadavatel příkazu musí disponovat právem QUERY_REWRITE.


4.3.2

37

Klauzule WITH

Od verze Oracle 9i release 2 lze v SQL dotazech použít klauzuli WITH. Tato funkcionalita se nazývá subquery factoring a slouží ke zjednodušení komplexních SQL dotazů. Poddotaz, který je třeba v rámci SQL dotazu vyhodnocovat opakovaně, se definuje s vlastním jménem za klauzuli WITH. Optimalizátor jej na základě zhodnocení cen použije buď jako vnitřní pohled (in-line view) nebo pro něj vytvoří globální dočasnou tabulku (global temporary table – dále GTT). Z tohoto objektu se následně v samotném dotazu vybírají již předpřipravená data. Vytvoření GTT lze optimalizátoru zadat hintem materialize, naopak hint inline zadá optimalizátoru použití vnitřních pohledů. Následující příklad demonstruje výhodnost použití klauzule WITH. Uživatel si přeje zobrazit zákaznické účty, které v období 5 dnů na začátku roku (1. 1. 2013 až 5. 1. 2013) poslali více zpráv SMS, než byl průměr na zákazníka za dané období. Pod jedním zákaznickým účtem může být sdružen jeden či více zákazníků. Ve výsledném pohledu chce rovněž vidět počet odeslaných SMS zpráv a celkový průměr. Zákazníci jsou uloženi v tabulce DW_SUBSCRIBER, kde existuje primární klíč nad sloupcem DW_SUB_KEY a zákaznický účet zobrazuje sloupec SUB_ACC_KEY. Informace o zprávách SMS za zadané období jsou připraveny v tabulce DW_SMS, kde jeden řádek odpovídá jedné SMS zprávě a sloupec DW_SUB_KEY je cizím klíčem, odkazujícím se na primární klíč do tabulky DW_SUBSRIBER. Tabulka DW_SUBSCRIBER obsahuje přibližně 100 sloupců, 15 mil. záznamů, 800 000 datových bloků a její velikost je 12,5 GB. Tabulka DW_SMS obsahuje přibližně 20 sloupců, 55 mil. záznamů, 550 000 datových bloků a její velikost je 8,5 GB. SQL dotaz, který vyhoví zadání má následující podobu. Tento SQL dotaz je vykonán v příkladu č. 1. /* Příklad č. 1 */ SELECT s.sub_acc_key, COUNT (*) sum_sms, (SELECT SUM (1) FROM dw_sms) / (SELECT COUNT (*) FROM dw_subscriber) avg_sms FROM dw_subscriber s, dw_sms sm WHERE s.dw_sub_key = sm.dw_sub_key GROUP BY s.sub_acc_key HAVING COUNT (*) > (SELECT SUM (1) FROM dw_sms) / (SELECT COUNT (*) FROM dw_subscriber);


38

Exekuční plán pro příklad č. 1 má následující podobu. ---------------------------------------------| Id | Operation | Name | ---------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL | DW_SMS | | 3 | SORT AGGREGATE | | | 4 | TABLE ACCESS FULL | DW_SUBSCRIBER | | 5 | FILTER | | | 6 | SORT GROUP BY | | | 7 | NESTED LOOPS | | | 8 | TABLE ACCESS FULL| DW_SMS | | 9 | INDEX UNIQUE SCAN| PK_DW_SUB_KEY | | 10 | SORT AGGREGATE | | | 11 | TABLE ACCESS FULL | DW_SMS | | 12 | SORT AGGREGATE | | | 13 | TABLE ACCESS FULL | DW_SUBSCRIBER | ---------------------------------------------Tento SQL dotazu je třeba přepsat do podoby s klauzulí WITH. Tučně označeny jsou tři poddotazy, které jsou vypočítány před provedením samotného dotazu. Tento SQL dotaz je spuštěn jako příklad č. 2. Pro příklad č. 3 je použit shodný SQL dotaz, ale bez hintu materialize. Optimalizátor v takovém případě zvolí použití vnitřních pohledů namísto vytvoření tří GTT v příkladu č. 2. /* Příklad č. 2 a příklad č. 3 (bez hintu)*/ WITH number_subs AS (SELECT /*+ materialize */ COUNT (*) cnt_subs FROM dw_subscriber), total_sms AS (SELECT /*+ materialize */ SUM (1) all_sms FROM dw_sms), sub_sms AS (SELECT /*+ materialize */ s.sub_acc_key, SUM (1) sum_sms FROM dw_subscriber s, dw_sms sm WHERE s.dw_sub_key = sm.dw_sub_key GROUP BY s.sub_acc_key) SELECT sub_acc_key, sum_sms, all_sms / cnt_subs AS avg_sms FROM number_subs, total_sms, sub_sms WHERE sum_sms > (all_sms / cnt_subs);


Takto vypadá exekuční plán pro příklad č. 2. -----------------------------------------------------------------| Id | Operation | Name | -----------------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | TEMP TABLE TRANSFORMATION | | | 2 | LOAD AS SELECT | | | 3 | SORT AGGREGATE | | | 4 | INDEX FAST FULL SCAN | PK_DW_SUB_KEY | | 5 | LOAD AS SELECT | | | 6 | SORT AGGREGATE | | | 7 | TABLE ACCESS FULL | DW_SMS | | 8 | LOAD AS SELECT | | | 9 | HASH GROUP BY | | | 10 | HASH JOIN | | | 11 | TABLE ACCESS FULL | DW_SUBSCRIBER | | 12 | TABLE ACCESS FULL | DW_SMS | | 13 | NESTED LOOPS | | | 14 | MERGE JOIN CARTESIAN | | | 15 | VIEW | | | 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67C9_E88E4BEA | | 17 | BUFFER SORT | | | 18 | VIEW | | | 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67CA_E88E4BEA | | 20 | VIEW | | | 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D67CB_E88E4BEA | -----------------------------------------------------------------Pro příklad č. 3 byl vytvořen následující exekuční plán. -----------------------------------------------| Id | Operation | Name | -----------------------------------------------| 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | VIEW | | | 4 | SORT GROUP BY | | | 5 | NESTED LOOPS | | | 6 | TABLE ACCESS FULL| DW_SMS | | 7 | INDEX UNIQUE SCAN| PK_DW_SUB_KEY | | 8 | VIEW | | | 9 | SORT AGGREGATE | | | 10 | TABLE ACCESS FULL | DW_SMS | | 11 | VIEW | | | 12 | SORT AGGREGATE | | | 13 | TABLE ACCESS FULL | DW_SUBSCRIBER |

39


40

Z exekučního plánu pro příklad č. 1 lze vyčíst několik úplných průchodů oběma tabulkami, což může s ohledem na objem dat, představovat vyšší nároky na fyzické i logické čtení. Ačkoli exekuční plán pro příklad č. 2 vypadá nejsložitěji, jsou tabulky přečteny pouze za účelem vytvoření tří GTT. Pro tabulku DW_SMS je dvakrát zvoleno úplné prohledání, a tabulka DW_SUBSCRIBER je jednou přečtena úplným průchodem a jednou je přečten úplným rychlým prohledáním pouze index, ze kterého je možné získat informaci o počtu záznamů. Výsledek dotazu pak lze získat z již zmenšených GTT. První dvě GTT obsahují pouze informaci o počtu, resp. součtu záznamů a třetí je zúžena a agregována, takže následné cyklické procházení je výrazně zrychleno. V příkladu č. 3 je sestaven podobný exekuční plán jako v příkladu č. 1, k tabulkám je ovšem přistupováno jako k pohledům a je zapotřebí menšího počtu úplných průchodů. Tabulka 1 zobrazuje statistiky pro všechny tři příklady. Význam jednotlivých statistických metrik lze nalézt v terminologickém slovníku v kapitole 7. Nejlépe z hlediska času dopadl příklad č. 2, který ačkoli více využil soubory protokolu (redo) kvůli vytvoření GGT a Oracle musel provést více vnitřních volání (recursive calls), potřeboval provést méně fyzických a výrazně méně logických čtení. Rovněž veškeré operace řazení probíhaly v paměti. Příklad č. 3 byl rychlejší, než příklad č. 1 a potřeboval provést méně čtení, zejména fyzického, díky menšímu počtu úplných průchodů tabulkami. Tabulka 1 – Statistiky zpracování příkazů SELECT, WITH (materialized) a WITH (inline) (Autor)

Statistika

Příklad č. 1 SELECT

Příklad č. 2 WITH (materialize)

Příklad č. 3 WITH (inline)

recursive calls

224

2 432

120

db block gets

39

2 266

25

consistent gets

116 362 922

1 821 105

114 976 392

physical reads

3 263 445

1 793 227

2 217 263

360

4 588

360

sorts (memory)

0

5

0

sorts (disk)

1

0

1

1 206 158

1 206 158

1 206 158

00:15:31.24

00:08:14.46

00:13:52.18

redo size

rows processed celkový čas

Závěrem lze potvrdit výhodnost použití klauzule WITH s vytvořením GTT pro SQL příkazy, které opakovaně přistupují k tabulkám velkých objemů. Klauzule WITH je však výhodnější i s použitím vnitřních pohledů a to nejen z důvodu mírného zlepšení výkonu, ale i díky obecné přehlednosti SQL kódu. Tato možnost může být i lepší volbou v případě menších objemů dat, jelikož Oracle nemusí vytvářet GTT.


4.3.3

41

Operátory (NOT) IN a (NOT) EXISTS

Volba vhodného operátoru při výběru může mít nezanedbatelný vliv na výkon a proto je tato kapitola zaměřena na porovnání operátorů pro hromadný výběr dat a možnosti jejich použití. Operátor IN porovnává hodnoty se seznamem zadaných nebo poddotazem vrácených hodnot. Naproti tomu EXISTS ověřuje existenci záznamů v zadaném poddotazu, přičemž provádí porovnávání na úrovni spojovacího klíče. V závislosti na objemu dat a existenci indexů může být EXISTS efektivnější, ale operátoru IN lze zadat seznam hodnot přímo v dotazu (nemusí být uloženy v tabulce). Oracle optimalizátor však stále prochází vývojem a často bývá použití EXISTS a IN rovnocenné, což lze poznat v případech, kdy jsou sestaveny stejné exekuční plány. Efektivnější, než EXISTS, může být použití množinového operátoru MINUS. Vzhledem ke skutečnosti, že EXISTS ověřuje existenci záznamu, může být použit namísto příkazu DISTINCT. Příkaz DISTINCT musí provádět řazení dat, což zvyšuje čas i nároky na diskový prostor, a proto je EXISTS pro tyto účely efektivnější. Následující ukázka porovnává efektivitu EXISTS s DISTINCT. Uživatel si v tomto případě přeje zobrazit unikátní seznam zákazníků, kteří poslali SMS zprávu v určitém období. Časový řez pro SMS zprávy je uložený v tabulce DW_SMS_PART, která obsahuje přibližně 160 000 záznamů. Dále je v této tabulce vytvořený index nad sloupcem DW_SUB_KEY, aby dotaz doběhl v rozumném čase. Oba SQL dotazy vrátí stejná data, ale použijí jinou cestu k jejich získání. Následují SQL dotazy a jejich exekuční plány. Statistiky zpracování lze nalézt v tabulce 2. /* Příklad č. 1 – DISTINCT */ SELECT DISTINCT s.dw_sub_key, s.dw_sub_name FROM dw_subscriber s JOIN dw_sms_part sm ON s.subs_key = sm.subs_key; ------------------------------------------------------| Id | Operation | Name | ------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT UNIQUE | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | DW_SMS_PART | | 4 | TABLE ACCESS BY INDEX ROWID| DW_SUBSCRIBER | | 5 | INDEX UNIQUE SCAN | PK_DW_SUB_KEY | -------------------------------------------------------


42

/* Příklad č. 2 – EXISTS */ SELECT s.dw_sub_key, s.dw_sub_name FROM dw_subscriber s WHERE EXISTS (SELECT 1 FROM dw_sms_part sm WHERE s.dw_sub_key = sm.dw_sub_key); --------------------------------------------| Id | Operation | Name | --------------------------------------------| 0 | SELECT STATEMENT | | | 1 | FILTER | | | 2 | TABLE ACCESS FULL| DW_SUBSCRIBER | | 3 | INDEX RANGE SCAN | DW_SUB_KEY_IDX | --------------------------------------------Tabulka 2 - Statistiky zpracování příkazů DISTINCT a EXISTS (Autor)

Statistika

Příklad č. 1 DISTINCT

Příklad č. 2 EXISTS

recursive calls

1

1

db block gets

0

0

consistent gets

503 649

30 427 312

physical reads

90 389

562 160

redo size

0

0

sorts (memory)

1

0

sorts (disk)

0

0

91 567

91 567

00:19:38.03

00:03:09.56


Z výsledků je zřejmé, že EXISTS je pro větší objemy dat efektivnější. Výrazně nižšího času je dosaženo na úkor většího počtu fyzického i logického čtení. DISTINCT musí provádět náročnou operaci řazení, aby získat jedinečné záznamy. Použití operátoru IN nebo EXITS vždy vrátí stejná data. To samé však neplatí pro NOT IN vs. NOT EXISTS v případě, že sloupec z poddotazu obsahuje prázdné (NULL) hodnoty. Pokud vybíraný sloupec může obsahovat prázdné hodnoty, je třeba jej ošetřit například pomocí funkce NVL. Důkaz pro rozdílné fungování NOT IN a NOT EXISTS lze demonstrovat rozdílným zpracováním operátorů IN a NOT IN v následujícím příkladu. Poddotaz vytvoří jednoduchou tabulku s hodnotami, které zobrazuje tabulka 3.


43

Tabulka 3 – Tabulka v příkladu pro srovnání operátoru IN a NOT IN (Autor)

ID

TEXT

1

JEDNA

(null)

DVA

SELECT * FROM (SELECT 1 AS ID, 'JEDNA' AS text FROM DUAL UNION ALL SELECT NULL AS ID, 'DVA' AS text FROM DUAL) WHERE ID IN (1, NULL); /* (ID = 1 OR ID = NULL) => PRAVDA */ ID TEXT -- ----1 JEDNA SELECT * FROM (SELECT 1 AS ID, 'JEDNA' AS text FROM DUAL UNION ALL SELECT NULL AS ID, 'DVA' AS text FROM DUAL) WHERE ID NOT IN (3, NULL); /* (ID != 3 AND ID != NULL) => NEPRAVDA (NELZE ZJISTIT) */ no rows selected Důvodem pro tyto výsledky je booleovská logika a pravidla, kterými se Oracle řídí při vyhodnocování operátoru: ● 10 IN (10,20,30) je ekvivalentní (10 = 10 OR 10 = 20 OR 10 = 30) ● 10 NOT IN (10,20,30) je ekvivalentní (10 != 10 AND 10 != 20 AND 10 != 30) ● NULL != NULL, cokoli != NULL, NULL IS NULL NOT IN porovnává hodnotu s celým listem hodnot, a pokud je jedna hodnota z listu NULL, je vrácena NEPRAVDA, a nejsou vráceny žádné záznamy. Naproti tomu NOT EXISTS ověřuje existenci určitého záznamu v tabulce s druhou tabulkou postupně, přes klíč, který slouží ke spojení. Díky tomu NEPRAVDA vzniklá porovnáním se záznamem NULL nevrátí záznam, ale neovlivní ostatní porovnání. Kdy je lepší použít NOT IN a kdy NOT EXISTS velmi záleží na objemu dat, indexech a prázdných (NULL) hodnotách v poddotazu. Často mohou být oba typy dotazů stejně efektivní, protože pro ně optimalizátor sestaví stejné exekuční plány. Efektivnější, než NOT IN i NOT EXISTS, často bývá použití vnějšího spojení s výběrem prázdných hodnot z vnější tabulky. Další příklad slouží ke srovnání operátorů NOT IN, NOT EXISTS a vnějšího spojení. Uživatel si v tomto případě přeje zjistit počet zákazníků, kteří za dané období neposlali žádnou


44

SMS zprávu. Následující SQL příkazy vrátí stejné výsledky, ale používají odlišný přístup k jeho získání. Jsou zde demonstrovány i velké možnosti jazyka SQL k vytvoření odlišných dotazů se stejným cílem. Příklad používá stejné objekty, které byly použity pro srovnání EXISTS s DISTINCT za začátků této kapitoly. Výsledky zpracování zobrazuje tabulka 4. /* Příklad č. 1 - NOT IN */ SELECT COUNT (dw_sub_key) FROM dw_subscriber s WHERE dw_sub_key NOT IN (SELECT dw_sub_key FROM dw_sms_part sm); ------------------------------------------------| Id | Operation | Name | ------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | MERGE JOIN ANTI | | | 3 | INDEX FULL SCAN | PK_DW_SUB_KEY | | 4 | SORT UNIQUE | | | 5 | INDEX FAST FULL SCAN| DW_SUB_KEY_IDX | ------------------------------------------------/* Příklad č. 2 - NOT EXISTS */ SELECT COUNT (dw_sub_key) FROM dw_subscriber s WHERE NOT EXISTS (SELECT 1 FROM dw_sms_part sm WHERE sm.dw_sub_key = s.dw_sub_key); --------------------------------------------| Id | Operation | Name | --------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | FILTER | | | 3 | TABLE ACCESS FULL| DW_SUBSCRIBER | | 4 | INDEX RANGE SCAN | DW_SUB_KEY_IDX | ---------------------------------------------


45

/* Příklad č. 3 - OUTER JOIN */ SELECT COUNT (s.dw_sub_key) FROM dw_subscriber s LEFT JOIN dw_sms_part sm ON sm.dw_sub_key = s.dw_sub_key WHERE sm.dw_sub_key IS NULL; -----------------------------------------------| Id | Operation | Name | -----------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | HASH JOIN RIGHT ANTI | | | 3 | INDEX FAST FULL SCAN| DW_SUB_KEY_IDX | | 4 | INDEX FAST FULL SCAN| PK_DW_SUB_KEY | -----------------------------------------------Tabulka 4 - Statistiky zpracování příkazů - operátory NOT IN a NOT EXISTS, a vnější spojení (Autor)

Statistika

Příklad č. 1 NOT IN

Příklad č. 2 NOT EXISTS

Příklad č. 3 OUTER JOIN

recursive calls

7

1

7

db block gets

0

0

0

consistent gets

16 763

30 423 685

17 188

physical reads

16 659

564 639

16 672

redo size

0

0

0

sorts (memory)

3

0

2

sorts (disk)

0

0

0

rows processed

1

1

1

00:00:24.35

00:02:33.76

00:00:07.55

celkový čas

Z výsledků lze vyzdvihnout výhodnost použití vnějšího spojení, jehož zpracování trvalo necelých 8 vteřin. Příkaz s operátorem NOT IN trval 24,3 vteřin a potřeboval provést o jednu operaci řazení více. Ostatní statistiky jsou velmi podobné. Nejhorší variantou byl v tomto případě operátor NOT EXISTS, který trval 2,5 minuty a potřeboval provést výrazně více fyzického i logického čtení. Důvod lze odhalit v exekučním plánu, ze kterého je zřejmé optimalizátor zvolil úplné průchod tabulkou DW_SUBSCRIBER.


4.3.4

46

Shrnutí

Cílem této kapitoly je shrnout podstatné body z kapitoly 4.3 a následných podkapitol. V datových skladech se lze setkat s pohledy a materializovanými pohledy na začátku i na konci ETL procesu. Zatímco na začátku slouží jako zdroj dat, na konci procesu se vyskytují jako objekty, ke kterým přistupují uživatelé, aby získali data. Pohledy představují ideální vrstvu mezi daty a uživateli. Nemají téměř žádné nároky na fyzické místo, a lze s jejich využitím jednoduše a rychle provádět drobné i náročnější úpravy pro finální podobu dat bez potřeby změn ve fyzické tabulce. Jedná se především o výběr sloupců, změnu jejich názvů, menší úpravy pomocí příkazu CASE nebo funkce DECODE či NVL. Náročnější operace jako filtry nebo agregace je však třeba zvážit z hlediska výkonu, aby jednoduchý SQL dotaz do pohledu nebyl prováděn desítky minut. V takových případech je vhodnější připravit data v tabulce před pohledem nebo použít materializovaný pohled a zajistit jeho aktualizaci. SQL dotaz, který opakovaně přistupuje ke stejným objektům, lze optimalizovat pomocí klauzule WITH. Z uskutečněných testů je zřejmé, že v případě objemnějších tabulek, lze výrazně snížit dobu potřebnou k získání dat i využití systémových prostředků. Důvodem je automatické vytvoření a použití globálních dočasných tabulek. Avšak i v případě menších tabulek lze nepatrně zvýšit výkon použitím klauzule WITH a hlavně vytvořit čitelnější a snáze pochopitelnější SQL kód. V takovém případě není třeba vytvářet globální dočasné tabulky, ale stačí zvolit metodu s použitím vnitřních pohledů. Porozumění operátorům v SQL a Oracle je důležité pro tvorbu efektivních dotazů. Často lze zvolit více cest, jak získat stejná data a některé bývají vhodnější, než jiné. Použití příkazu DISTINCT k výběru jedinečných záznamů znamená nutnost provedení operace řazení, která je v případě velkých objemů dat náročná. Ke stejnému účelu je možné použít operátor EXISTS, který, jak dokazuje provedený test, umí na úkor vyššího využití zdrojů vrátit výsledek několikanásobně rychleji. Dalším důležitým a ověřeným faktem je, že operátory NOT IN a NOT EXISTS nemusí vrátit stejný výsledek v případech, kdy poddotaz obsahuje prázdné (NULL) hodnoty. Konkrétní doporučení, kdy je výhodnější použít NOT IN nebo NOT EXISTS, se pak odvíjí od objemu dat a existence indexů. Obecně však platí, že existuje i třetí možnost, a tou je použití vnějšího spojení s výběrem prázdných hodnot z vnější tabulky. Provedení testu s definovanými podmínkami dokazuje, že tato možnost je efektivnější, než použití operátorů. Nejen, že je výsledek vrácen v nejkratším čase, ale i využití systémových prostředků je srovnatelné s operátorem NOT IN. Druhým závěrem testu je již zopakovaná skutečnost, že operátor (NOT) EXISTS spotřebuje ke svému zpracování větší množství prostředků, než varianty s (NOT) IN nebo s vnějším spojením. Důvodem je potřeba úplného průchodu vnější tabulkou.


4.4

47

ETL - Fáze upravení

Tato kapitola představuje často používané funkce a techniky pro úpravu dat. Jedná se především o pokročilé funkce a podmíněné úpravy dat. Tyto funkce jsou však hojně používány i mimo ETL proces, zejména při vytváření agregací a úpravy dat pro uživatelské reporty.

4.4.1

Pokročilé agregace

Databáze Oracle obsahují velké množství funkcí, které lze použít při konstruování SQL nebo PL/SQL příkazů. V ETL procesech a datových skladech jsou často využívány, jelikož je jejich použití snadné, rychlé a není třeba žádných dalších specializovaných nástrojů. Rovněž jsou navrženy efektivně a často je jejich použití nejvhodnější z hlediska výkonu. Funkce se dělí do několika kategorií a lze se s nimi v ETL procesech často setkat a to nejen ve fázi upravení. Znakové a řetězcové funkce umí počítat délky řetězců, vybírat a upravovat jednotlivé znaky nebo celý řetězec. Datumové funkce umí přidávat či odebírat měsíce nebo vybrat poslední den v měsíci zadaného data. Konverzní funkce slouží k převodu mezi formáty. Tato kapitola je zaměřena na pokročilé možnosti agregace. Pokročilým funkcím DECODE a CASE se věnuje kapitola 4.4.2 a analytické funkce lze nalézt v kapitole 4.4.3. Agregační funkce se používají ve spojení s klauzulí GROUP BY. Pokud klauzule GROUP BY v příkazu uvedena není, považuje se celý výběr hodnot za jednu skupinu. Mezi základní funkce patří COUNT, SUM, MAX, MIN a AVG. Pokročilé agregace slouží k agregaci dat z několika pohledů a patří sem zejména ROLLUP a CUBE. Nejedná se však o samostatné funkce, nýbrž o rozšíření pro příkaz GROUP BY. Klauzule GROUP BY má omezení v počtu agregovaných dimenzí. Pro data lze provádět výpočty pouze v rámci jedné dimenze. Pro agregaci více dimenzí, by bylo třeba spojit výsledky několika dotazů s GROUP BY pomocí UNION ALL. Pro malé objemy dat je toto řešení použitelné, v oblasti datových skladů se však jedná o značně neefektivní metodu, jelikož je třeba v rámci jednoho dotazu přistupovat ke stejným objektům opakovaně. Další nevýhoda spočívá ve složitosti takového SQL dotazu. Klauzule ROLLUP umí provést agregaci na úrovní definovaných dimenzí. Stačí do klauzule zadat seznam dimenzí a jejich agregace jsou vráceny v dotaze. Jako příklad lze zvolit například tabulku DW_DATE, která obsahuje informace o rocích, měsících, dnech atd. Následující dotaz vybere rok, měsíc a počty dní v prvních kvartálech roku 2012 a 2013. Pro roky a měsíce jsou provedeny agregace za účelem zjištění počtu dnů.


SELECT FROM WHERE GROUP BY

48

year, month, COUNT (day_name) dw_date quarter_code in ('2013/Q1','2012/Q1') ROLLUP (year, month);

YEAR ---------2012 2012 2012 2012 2013 2013 2013 2013

MONTH COUNT(DAY) ---------------------------------------- ---------March 31 January 31 February 29 91 March 31 January 31 February 28 90 181

U klauzule ROLLUP záleží na pořadí dimenzí a proto, když je třeba spočítat dny pro každý měsíc, namísto roku, stačí změnit SQL dotaz následovně. SELECT FROM WHERE GROUP BY

year, month, COUNT (day_name) dw_date quarter_code in ('2013/Q1','2012/Q1') ROLLUP (month, year);

YEAR ---------2012 2013 2012 2013 2012 2013

MONTH COUNT(DAY) ---------------------------------------- ---------March 31 March 31 March 62 January 31 January 31 January 62 February 29 February 28 February 57 181

Přidání další dimenze, spočívá v přidání nového sloupce do klauzule ROLLUP, bez výrazného zvýšení nároků na provedení příkazu. Klauzule CUBE provádí agregace pro všechny možné kombinace dimenzí. Stejný dotaz s použitím ROLLUP a CUBE, tak v případě CUBE vrátí stejné výsledky jako ROLLUP a k tomu ještě další kombinace. Nezáleží tedy na pořadí dimenzí v dotazu pro získání dat.


SELECT FROM WHERE GROUP BY

49

year, month, COUNT (day_name) dw_date quarter_code in ('2013/Q1','2012/Q1') CUBE (year, month);

YEAR MONTH COUNT(DAY) ---------- ---------------------------------------- ---------181 March 62 January 62 February 57 2012 91 2012 March 31 2012 January 31 2012 February 29 2013 90 2013 March 31 2013 January 31 2013 February 28

4.4.2

Funkce DECODE a příkaz CASE

Data jsou v datových skladech uložena v různých formátech a často bývá potřeba provést určité transformace do jiné formy výstupu, nebo provést rozhodnutí na základě vybraných dat. Ačkoli je v databázích Oracle možné využít možnosti procedurálního jazyka PL/SQL či Java, často vhodně poslouží i interní funkce, které lze využít v SQL, umí nabídnout vysoký výkon a zachovat kód přehledný. Z výše uvedených důvodů jsou takové funkce často využívány a je tedy důvod je představit. Tyto funkce fungují na principu porovnání vstupních hodnot, na jehož základě vrátí hodnotu výstupní. Tabulka 5 zobrazuje syntaxi vhodných příkazu a její logický překlad (Beaulieu, Mishra, 2004). Tabulka 5 - Funkce a jejich logické výrazy (Autor)

Syntaxe funkce

Logický výraz

DECODE(E1, E2, E3, E4)

IF E1 = E2 THEN E3 ELSE E4

NULLIF(E1, E2)

IF E1 = E2 THEN NULL ELSE E1

NVL(E1, E2)

IF E1 IS NULL THEN E2 ELSE E1

NVL2(E1, E2, E3)

IF E1 IS NULL THEN E3 ELSE E2

V ETL procesech se často maskují prázdné (NULL) hodnoty, jelikož se s nimi hůře pracuje při následném zpracování (NULL se nerovná NULL) a rovněž nemusí být zřejmé, zda se taková data skutečně nachází ve zdrojovém systému nebo došlo k chybě v průběhu zpracování. Předem je definována náhrada za NULL hodnotu, ideální je samozřejmě vybrat tako-


50

vou, která se ve zdroji obvykle nenachází, např. „-99“ pro číselný datový typ (NUMBER), „N/A“ pro řetězec (VARCHAR2) nebo „1.1.1900“ pro datum (DATE). Pokud jsou ve faktových datech některá pole prázdná, jsou nahrazena definovaným ekvivalentem a k němu je vytvořen záznam v příslušných tabulkách dimenzí. A ten už lze snadněji porovnat (na rozdíl on NULL hodnot) při následném přiřazování klíčů dimenzí. Obvykle existuje jeden „NA“ záznam v dimenzích, který se takovým hodnotám přiřadí. Funkce DECODE umí nahradit ostatní funkce z tabulky 5, která i přesto naleznou své využití, zejména, chce-li vývojář, aby byl kód přehlednější a snáze pochopitelnější. U těchto příkazů je důležité dát pozor datové typy vstupních parametrů. Nelze např. porovnávat číslo s datem. Při porovnání čísla se znakem je číslo převedeno na znak (což je možné kdykoli). V opačném případě, když se převádí znak na číslo, se to již podařit nemusí a příkaz dotaz může skončit chybou. /* Příklad č. 1 */ SELECT DECODE(1,sysdate,1,0) AS VYSLEDEK FROM DUAL; * ERROR at line 1: ORA-00932: inconsistent datatypes: expected DATE got NUMBER /* Příklad č. 2 */ SELECT DECODE(1,'ABC',1,0) AS VYSLEDEK FROM DUAL; VYSLEDEK ---------0 /* Příklad č. 3 */ SELECT DECODE('ABC',1,1,0) AS VYSLEDEK FROM DUAL; * ERROR at line 1: ORA-01722: invalid number DECODE může obsahovat až 255 parametrů a pokud je počet parametrů lichý, poslední hodnota je definována jako výchozí pro případy, kdy není nalezena shoda u předchozích. Datový typ první vrácené hodnoty je určen jako výstupní a stejný se očekává i od hodnot následujících. Tento předpoklad je porušen v následujícím případě, protože „A“ nelze převést na číslo. SELECT DECODE(10,1,0,'A') AS VYSLEDEK FROM DUAL; * ERROR at line 1: ORA-01722: invalid number Podobně jako funkce DECODE dokáže nahradit funkce NULLIF, NVL a NVL2, lze použít příkaz CASE všude tam, kde se vyskytuje DECODE. Naopak to však neplatí. CASE byl


51

poprvé představen v roce 1992 ve specifikaci SQL-92. Oracle však implementoval tento příkaz až o osm let později ve verzi Oracle8i, nejspíše právě z důvodu existence podobné funkce - DECODE. Ze současného pohledu jsou však jediné výhody funkce DECODE oproti příkazu CASE v tom, že CASE není podporován v PL/SQL Oracle8i a DECODE je lépe čitelný při použití velmi jednoduché logiky (Beaulieu, Mishra, 2004). Naopak výhod příkazu CASE je celá řada (ORATABLE, 2010). ● CASE umí používat i jiné logické operátory, než porovnání. Umí vyhodnotit rozdíl, větší než, menší než atd. ● CASE lze použít v PL/SQL k vyhodnocení podmínek, zatímco DECODE může být pouze součástí SQL příkazu. ● CASE je na rozdíl od DECODE kompatibilní s ANSI standardem SQL. DECODE je vytvořený Oraclem. ● V případě netriviální logiky, bývá CASE přehlednější a čitelnější. ● CASE očekává jako parametry konzistentní datové typy. Takže příkaz, který DECODE dokázal zpracovat, skončí při použití CASE chybou a tazatel je zjistí, že třeba dotaz opravit. SELECT CASE 1 WHEN 'ABC' THEN 1 ELSE 0 END AS VYSLEDEK FROM DUAL; * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Pro příkaz CASE existují dva typy syntaxí (Price, 2007): ● Searched CASE vrací hodnotu na základě výrazu. ● Simple CASE vrací hodnotu na základě podmínky. Simple CASE byl implementován v Oracle9i je podobný funkci DECODE, kdy zadanou hodnotu porovná s množstvím dalších hodnot. Použitím typu searched CASE lze definovat množství podmínek s odlišnými vstupními hodnotami. Oba typy příkazů vyhodnocují rozdílně vstupní hodnotu NULL, což lze demonstrovat následujícími příkazy. SELECT DECODE (NULL, NULL, 'JE NULL', 'NENI NULL') TEST_NULL FROM DUAL; TEST_NU ------JE NULL


52

/* SQL dotaz s použitím searched CASE*/ SELECT CASE WHEN NULL IS NULL THEN 'JE NULL' ELSE 'NENI NULL' END TEST_NULL FROM DUAL; TEST_NULL --------JE NULL /* SQL dotaz s použitím simple CASE*/ SELECT CASE NULL WHEN NULL THEN 'JE NULL' ELSE 'NENI NULL' END TEST_NULL FROM DUAL; TEST_NULL --------NENI NULL

Z testu lze vyčíst, že searched CASE funguje podobně jako DECODE a porovnává hodnoty binárně. Simple CASE používá k porovnání operátor „=“ a v Oracle platí, že NULL != NULL. Pokud se upraví operátor v searched CASE, dotaz vrátí stejný výsledek jako simple CASE. SELECT CASE WHEN NULL = NULL THEN 'JE NULL' ELSE 'NENI NULL' END TEST_NULL FROM DUAL; TEST_NULL --------NENI NULL CASE může obsahovat až 127 podmínek. CASE i DECODE vyhodnocují podmínky od začátku a pokud naleznou shodu, vrátí výsledek a dále v porovnávání nepokračují. V případě složitější logiky je vhodné formulovat posloupnost podmínek podle pravděpodobnosti splnění od nejvyšší, aby se redukoval počet porovnávání a snížil se celkový čas k vyhodnocení dotazu. CASE lze využít i pro sloučení náročných dotazů, kde je třeba opakovaně procházet jednu velkou nebo časově náročné spojení více tabulek, k získání určitých údajů. V takových případech lze rozdílné podmínky definovat na úrovni příkazu CASE a provést načtení nebo spojení tabulek pouze jednou. Konkrétní příklad je použit pro optimalizaci ETL procesu v kapitole 5.4. Další příklady využití CASE lze nalézt v (Beaulieu, Mishra, 2004) a jedná se o: ● podmíněný příkaz UPDATE, ● transponování řádků se sloupci,


53

● spouštění časově náročné funkce pouze pro určité hodnoty, ● zjišťování existence hodnot, ● ošetření nuly ve jmenovateli.

4.4.3

Analytické funkce

Analytické funkce v Oracle databázích umožňují provádět složité dotazy nad různými pohledy do dat. Jsou optimalizovány pro agregaci a rychlý běh zejména při zpracování velkých objemů dat v datových skladech. Většina dotazů, které lze s jejich pomocí uskutečnit, lze sestavit i s použitím jazyka SQL (PL/SQL) se spojováním tabulek a vytvořenými poddotazy. Takové dotazy však mohou být nejen složité a nepřehledné, ale i značně neefektivní z hlediska času i využití systémových prostředků. Výpočty analytických funkcí jsou prováděny v databázi, dík čemuž lze dosáhnout vyššího výkonu, než při přenesení logiky výpočtu do nástrojů pro tvorbu reportů. Z toho důvodu je důležité analytické funkce znát a umět je používat. Příklady použité v této kapitole pocházejí z (Basu, 2004) Obecná syntaxe analytické funkce má následující tvar. Function(arg1,.., argn) OVER ([PARTITION BY <...>] [ORDER BY <...>] [<window>]) Mezi nejdůležitější analytické funkce patří ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE a LAST_VALUE. Oproti běžným funkcím (SUM, COUNT, AVG, MIN, MAX), záleží u analytických funkcí na pořadí dat ve skupinách definovaných pomocí následujících klauzulí. Klauzule PARTITION BY umožňuje definovat skupiny dat v tabulce na základě hodnoty jednoho nebo více sloupců. Jedná se o obdobu klauzule GROUP BY, která rovněž seskupí data dle definovaných sloupců. Klauzule ORDER BY seřadí data v rámci skupiny vzestupně nebo sestupně (ASC, DESC) podle jednoho či více sloupců. Pomocí klauzule NULLS FIRST|LAST lze určit, zda mají být prázdné (NULL) hodnoty po seřazení na začátku nebo na konci skupiny. Klauzule window umožňuje definovat oblast pro vyhodnocení funkce v rámci skupiny. Current row označuje aktuální řádek, preceding označuje předchozí řádek a following slouží k označení následujícího řádku. Počet předchozích, resp. následujících řádků lze definovat přirozeným číslem, SQL výrazem, který vrací přirozené číslo, nebo nastavit počet na neomezený (unbounded). Funkce ROW_NUMBER, RANK a DENSE_RANK slouží k přiřazení čísla záznamům v rámci skupiny dle jejich pořadí. ROW_NUMBER přiřadí číslo dle pořadí, bez ohledu na hodnotu, podle které se řadí. RANK a DENSE_RANK přiřadí číslo s ohledem na hodnotu, dle které se řadí s tím rozdílem, že DENSE_RANK při přiřazení stejné hodnoty u více zá-


54

znamů pokračuje v číslování inkrementem 1, RANK inkrementuje o tolik, kolik záznamů mělo stejnou pozici. Následuje použití těchto funkcí, se zobrazením výsledku, na tabulce zaměstnanců, kteří jsou sloučení do skupin dle čísla oddělení a seřazení dle platu sestupně. SELECT empno, deptno, sal, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal NULLS LAST) row_nmb, RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) renk, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) dense_rank FROM emp WHERE deptno IN (10, 20) ORDER BY 2, ROW_NMB; EMPNO DEPTNO SAL ROW_NMB RANK DENSE_RANK ------- ------- ------ -------- ------- ------------7839 10 5000 1 1 1 7782 10 2450 2 2 2 7934 10 1300 3 3 3 7788 20 3000 1 1 1 7902 20 3000 2 1 1 7566 20 2975 3 3 2 7876 20 1100 5 4 3 7369 20 800 5 5 4 Funkce LEAD slouží k získání hodnoty určitého sloupce z následujícího záznamu v rámci skupiny. LAG naopak vybírá hodnotu určitého sloupce předchozího záznamu. Je možné nastavit offset mezi řádky, přičemž výchozí hodnota je 1. V následujícím příkladu jsou vybírání stejní zaměstnanci, jako v předchozím. U každého z nich je zobrazena mzda následujícího kolegy s nižší mzdou a předchozího kolegy s vyšší mzdou, v rámci oddělení. SELECT deptno, empno, sal, LEAD (sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) next_lower_sal, LAG (sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) prev_higher_sal FROM emp WHERE deptno IN (10, 20) ORDER BY deptno, sal DESC; DEPTNO EMPNO SAL NEXT_LOWER_SAL PREV_HIGHER_SAL ------- ------ ----- -------------- --------------10 7839 5000 2450 0 10 7782 2450 1300 5000 10 7934 1300 0 2450 20 7788 3000 3000 0 20 7902 3000 2975 3000 20 7566 2975 1100 3000 20 7876 1100 800 2975 20 7369 800 0 1100


55

Funkce FIRST_VALUE, resp. LAST_VALUE vrací první, resp. poslední hodnotu určitého záznamu v rámci skupiny. Obvykle slouží ke srovnání hodnoty každého záznamu s první či poslední hodnotou ve skupině. Následující dotaz zobrazí zaměstnance a počet dnů mezi jejich nástupem a nástupem prvního zaměstnance na oddělení. SELECT empno, deptno, ROUND( FIRST_VALUE (hiredate) OVER (PARTITION BY deptno ORDER BY hiredate) - hiredate ) day_gap FROM emp WHERE deptno IN (20, 30) ORDER BY deptno, DAY_GAP; EMPNO DEPTNO DAY_GAP ---------- ---------- ---------7369 20 0 7566 20 106 7902 20 351 7788 20 722 7876 20 756 7499 30 0 7521 30 2 7698 30 70 7844 30 200 7654 30 220 7900 30 286 Závěrem kapitoly lze doporučit vytvoření indexů nad sloupci, podle kterých se data shlukují do skupin, a podle kterých se provádí řazení. To ještě dále může zvýšit rychlost SQL dotazů s analytickými funkcemi.

4.4.4

Identifikace duplicitních záznamů

Výskyt duplicitních záznamů v datových skladech obvykle značí rozdílné fungování zdrojových systémů s návrhem ETL procesu, nebo chybu v procesu zpracování. Pro odhalení možných chyb a zastavení ETL procesu při výskytu duplicitních záznamů na místě, kde jsou nežádoucí, existují různé možnosti kontrol. V ETL procesu sestaveného z SQL a PL/SQL bloků je nejčastější možností vytvoření primárního nebo unikátního klíče nad sloupcem nebo sloupci, jejichž kombinace musí být jedinečná. Unikátní klíč, se na rozdíl od primárního klíče vytváří v případě, kdy v datech mohou být prázdné hodnoty. Pokud se vytvoření unikátního indexu podaří, ETL proces pokračuje dál a často se v dalším kroku index opět odstraní. Pokud se vytvoření indexu nepodaří, znamená to výskyt duplicit, zastavení ETL procesu, a chybové hlášení.


56

Další krok je na správci ETL procesu, který musí duplicitní záznamy odhalit a dohledat příčinu jejich vzniku. K nalezení duplicitních záznamů je třeba vytvořit SQL dotaz s klauzulí GROUP BY a HAVING. Díky klauzuli HAVING lze u sloučených záznamů nalézt vícenásobný výskyt. SELECT FROM GROUP BY HAVING

<seznam_unikatnich_sloupcu>, COUNT (*) <seznam_unikatnich_sloupcu> COUNT (*) > 1;

V případě identických záznamů, se stejnými hodnotami ve všech sloupcích, může být obtížnější vybrat jen jeden z těchto záznamů. K tomuto účelu dobře poslouží adresa ROWID, přidělená Oraclem. K odstranění záznamů, které se v tabulce vyskytují opakovaně, lze použít následující příkaz. Ten smaže všechny záznamy kromě jednoho, s nejnižší hodnotou ROWID. DELETE FROM WHERE ROWID NOT IN (SELECT MIN (ROWID) FROM GROUP BY <seznam_unikatnich_sloupcu>); Nutno však připomenout, že mazání velkého objemu dat příkazem DELETE, může být časově náročné. V takovém případě je vhodnější vytvoření nové tabulky s výběrem jedinečných záznamů, a následné přejmenování s původní tabulkou. Je však třeba počítat s následným vytvořením indexů, integritních omezení, přidělení práv apod., korespondujících s původní tabulkou. K výběru duplicitních záznamů dle komplexnějších podmínek lze využít analytické funkce ROW_NUMBER, RANK nebo DENSE_RANK. Ty umožňují přiřazení numerických hodnot záznamům v SQL poddotazu a jejich následnou filtraci v nadřazeném dotazu. Následující dotaz vybere každého zaměstnance s posledním přiděleným a zároveň aktivním telefonním číslem. Důležitá je v tomto případě i funkce DECODE, pomocí které lze řadit status na základě definovaných hodnot. SELECT empno, emp_cell_no FROM (SELECT empno, emp_cell_no, ROW_NUMBER () OVER (PARTITION BY emp_cell_no ORDER BY DECODE (status, 'ACT', 0, 'DEACT', 1, 2) ASC, startdate DESC ) row_nmb FROM emp_contact) WHERE row_nmb = 1;

4.4.5

Shrnutí

Cílem této kapitoly je shrnout podstatné body z kapitoly 4.4 a následných podkapitol.


57

Funkce a příkazy pro podmíněné úpravy dat, které jsou součástí Oracle databáze, představují vhodnou volbu pro použití v ETL procesech, jelikož jsou optimalizované pro většinu úloh v oblasti transformace. Lze je použít v SQL kódech, kde mohou nabídnout vysoký výkon se zachováním přehlednosti kódu. Pro velmi komplexní úpravy pak lze využít možností procedurálního jazyka PL/SQL nebo Javy. Tyto jazyky je však vhodné použít, až pro nezbytně nutné případy, kdy interní funkce neumožňují provést některé úlohy. Mezi nejdůležitější možnosti transformace dat, kterým je věnována kapitola 4.4.2, patří funkce DECODE a příkaz CASE. Příkaz CASE umožňuje použít dva typy syntaxe a dokáže plně nahradit funkci DECODE. Hlavní výhodou DECODE je kompatibilita se staršími verzemi Oracle databáze a kratší kód v případě jednoduchých podmínek. Naopak CASE umí použít i jiné typy operátorů, než je porovnání. Lze jej použít pro tvorbu podmínek v PL/SQL kódech a je citlivější na konzistenci datových typů. Oracle dále nabízí řadu možností pro provádění pokročilých agregací a podporu analytických úloh, které jsou představeny v kapitolách 4.4.1 a 4.4.3. Podobně, jako v případě již zmíněných funkcí a příkazů jsou optimalizovány pro výkon nad velkým objemem dat. SQL dotazy, které by bylo nutné sestavit pro stejný účel, by byly nejen nepřehledné a pro další úpravy komplikované, ale spotřebovaly by i větší množství systémových prostředků, opakovaným čtením objektů. Mezi hlavní výhody patří také výpočet dat na databázovém serveru, bez nutnosti jejich přesunu do reportingových aplikací. Uživateli jsou zaslána vypočítaná nebo agregovaná data, jejichž objem může být výrazně redukován. Řešení duplicitních záznamů je častou úlohou v datových skladech. Vývojář potřebuje znát základní metody, pomocí nichž lze duplicitní záznamy nejen identifikovat, ale i následně ošetřit. Důležité je znát klauzuli GROUP BY HAVING, možnosti adres ROWID, využití pro ošetření dat indexů nebo analytické funkce a příkazy pro podmíněné výběry dat. Všem zmíněným technikám se věnuje kapitola 4.4.4.

4.5

ETL - Fáze nahrání

Tato kapitola je zaměřena na příkazy a techniky pro mazání, přesun a zpracování velkých objemů dat.

4.5.1

Mazání dat příkazem TRUNCATE

V datových skladech dochází k častému mazání velkých objemů dat. Ať už se jedná o odstraňování dat z dočasných tabulek nebo čištění oddílů rozdělených tabulek před vložením nových záznamů, bývá často množství těchto dat příliš velké pro použití příkazu DELETE. Příkaz DELETE je časově náročný, jelikož musí procházet záznamy, ověřovat integritní omezení, měnit datové bloky, znovu vytvářet indexy a to vše s generováním ná-


58

vratových segmentů. Mazání velkých objemů dat tedy často končí chybou, protože návratové tabulkové prostory nemusí být dostatečně velké, a to zvláště v datových skladech, kde jsou nadměrně využívány dalšími běžícími ETL procesy. Smazání (DROP) a znovu vytvoření celé tabulky nebo oddílu je rovněž neefektivní, jelikož se tím smažou i případně indexy, integritní omezení a práva přidělená těmto objektům. Obecně platí, že je vhodnější použití příkazu TRUNCATE všude, kde je to možné. Srovnání příkazů TRUNCATE a DELETE je shrnuto v tabulce 6. Tabulka 6 - Srovnání příkazů TRUNCATE a DELETE (Autor)

Příkaz

TRUNCATE

DELETE

Typ příkazu

DDL

DML

Commit

Automatický (před i po)

Manuální

Rollback

Ne

Ano

Flashback

Ne

Ano

Snížení hodnoty HWM

Ano

Ne

Výběr záznamů

Omezený (lze mazat data v jednotlivých oddílech)

Ano (Klauzule WHERE)

Uvolnění diskového prostoru

Dle klauzule REUSE/DROP STORAGE

Ne

Uzamčení objektu

Uzamčení celé tabulky/oddílů Uzamčení konkrétních řád(Exclusive table lock) ků (Shared table lock)

Potřebná práva

DROP ANY TABLE

DELETE

Spuštění DML triggeru

Ne

Ano

Přítomnost cizích klíčů

Příkaz nelze použít

Dle nastavení cizích klíčů

Využití návratových segmentů

Zanedbatelné

Dle objemu mazaných dat

Indexy

Vyprázdní lokální indexy

Zneplatní a znovu vytvoří

Lze použít pro vzdálenou databázi (DB link)

Ne

Ano

Z tabulky je zřejmé, že TRUNCATE je příkaz typu DDL, což jej činí v porovnání s DML příkazem DELETE výrazně rychlejší, protože změny jsou provedeny v datovém slovníku a nejsou využívány návratové segmenty pro případné obnovení smazaných dat (rollback). Kromě toho lze použitím příkazu TRUNCATE ihned uvolnit místo po odstraněných datech, protože sníží HWM objektů. Tato možnost však může být nevhodná v případech, kdy bude následovat vložení nových dat a opětovné alokování rozsahů (extents). Změnou pa-


59

rametrů příkazu lze vybrat, zda bude místo uvolněno či nikoli. Uvolnění místa (DROP STORAGE) je provedeno jako výchozí. TRUNCATE TABLE [DROP STORAGE|REUSE STORAGE]; Mazání dat v oddílech probíhá bez ovlivnění ostatních oddílů. Syntaxe příkazu je: ALTER TABLE TRUNCATE PARTITION DROP STORAGE; Příkaz TRUNCATE je použit jako jedna z vedlejších optimalizačních metod pro tvorbu ETL procesu v kapitole 5.5.

4.5.2

Režimy pro nahrávání dat

Nejznámějším a nejrozšířenějším Oracle nástrojem pro nahrávání větších objemů dat je SQL*Loader. Spouští se příkazem SQLLDR a pomocí příkazů lze nahrávat data z externích souborů do databázových tabulek. Existují dva režimy, jak data nahrávat. Režim conventional path funguje tak, že přečte data ze souboru, vytvoří pro ně sadu příkazů INSERT a pošle je jádru databáze. Oracle poté vkládá data do volných bloků tabulky a aktualizuje příslušné indexy. Naproti tomu v režimu direct path jsou data zapisována přímo do datových souborů, Oracle musí pravidelně kontrolovat umístění datových bloků, ale to jsou jediné V/V operace, které jsou prováděny. Z tohoto důvodu je režim direct path významně rychlejší, ale nelze ho použít nad seskupenou (clustered) nebo indexově orientovanou tabulkou a nesmí nad cílovou tabulkou probíhat žádné transakce. Rovněž jsou v průběhu nahrávání zajištěna pouze integritní omezení NOT NULL, UNIQUE a PRIMARY KEY. CHECK, FOREIGN KEY a trigerry je třeba deaktivovat a znovu aktivovat až po dokončení operace (Loney, Bryla, 2006).

4.5.3

Nahrávání dat příkazem INSERT

Hlavním zdrojem pro tuto kapitolu je oficiální dokumentace Oracle (ORACLE, 2013). Pro většinu případů je příkaz INSERT spouštěn v režimu conventional path, který byl představen v předchozí kapitole ve spojení s nástrojem SQL*Loader. Vkládaná data jsou nejprve ukládána do volných bloků po předchozích DELETE nebo UPDATE příkazech, čímž dochází k recyklaci uvolněného místa. Rovněž jsou v tomto režimu kontrolovány integritní omezení. Zadáním příkazu COMMIT po dokončené transakci jsou nově vložená data dostupná SQL dotazům. Příkaz INSERT lze však v určitých případech spustit i v režimu direct path a zvýšit tak výkon při vkládání záznamů. Použití této metody má své výhody i nevýhody. Oproti režimu conventional path insert, direct path insert zapisuje data přímo do datových souborů, aniž by využíval databázovou buffer cache, nerecykluje volné místo v tabulce (data jsou


60

vkládána nad hodnotu high water mark – viz kapitolu 4.1.9) a některá integritní omezení je třeba vypnout. Zadáním příkazu COMMIT po dokončené transakci se nejprve nastaví nová hodnota HWM a následně jsou nově vložená data dostupná pro SQL dotazy. V následujících případech je použití direct path insert nutný předpoklad a Oracle jej používá automaticky (ORACLE, 2013). ● Příkaz INSERT je prováděn paralelně. ● Do příkazu INSERT je vložen hint APPEND. ● Tabulka je vytvářena příkazem CTAS (CREATE TABLE AS SELECT). ● Příkaz INSERT je prováděn bez logování (klauzule NOLOGGING9, viz kapitolu 4.1.8) za účelem snížení množství zápisů do souboru protokolů (redo log). ● Vkládaná data jsou komprimována. Použití paralelismu lze rovněž kombinovat s direktivou NOLOGGING, což vede k dalšímu zrychlení nahrávání dat.

APPEND

a možností

V porovnání s hromadným vkládáním dat pomocí nástroje SQL*Loader má direct path insert následující výhody (ORACLE, 2013): ● Je zajištěna nedělitelnost (atomicita) transakce a to i v případě paralelního vkládání dat. ● Indexy jsou automaticky aktualizovány po vložení dat. Pokud se některý index nepodaří znovu sestavit, je celá transakce odrolována. Po použití nástroje SQL*Loader mohou zůstat indexy zneplatněné. Direct path insert lze spustit jak sériově, tak paralelně. Použití je rovněž možné pro nerozdělené i rozdělené tabulky. V případě paralelního vkládání do rozdělené tabulky může běžet pouze jeden proces nad jedním oddílem. Pokud jsou data vkládána paralelně do nerozdělené tabulky, tak se data nejprve vloží do nových dočasných segmentů a až po příkazu COMMIT jsou data sloučena do cílové tabulky. Z toho plyne jedna z nevýhod použití direct path režimu a to je vyšší náročnost na volné místo. Ať se už jedná o využití dočasných segmentů, nebo nevyužití volných bloků při vkládání dat nad hodnotu HWM oproti conventional path režimu. Další nevýhodou je potřeba uzamčení objektu, do kterého se data vkládají. Rovněž je třeba vzít v potaz velikost návratových segmentů, které jsou v tomto režimu využívány k údržbě indexů, protože jejich velikost omezí množství dat, které lze vložit (ORAFAQ, 2003). Je tedy vhodné zvážit, zda není výhodnější indexy před vkládáním dat smazat (případně zneplatnit) a po vložení je znovu vytvořit. Použití této metody doporučuje (Loney, Bryla, 2006) zvážit i v případě přesunu většího objemu dat mezi tabulkami v režimu conventional path, jelikož neustálá údržbou indexů přesun dat

9

Direct Path insert automatický nevytváří návratové segmenty pro tabulky v režimu NOLOGGING, pokud je databáze v režimu ARCHIVELOG a není zapnut FORCE LOGGING režim (ORACLE, 2013).


61

zpomalí (podobně jako u příkazů UPDATE a DELETE při zpracování většího objemu dat). V případě conventional path režimu je dále vhodné odstranit triggery, které by se spouštěly po každém vloženém řádku, a zrušit integritní omezení na cizí klíče a CHECK. Integritní omezení je vhodnější zkontrolovat ručně před vložením a operace triggerů spustit hromadně po vložení dat (ORACLE, 2013). Zda je příkaz prováděn v direct path režimu či nikoli lze poznat z jeho exekučního plánu, podle kroku LOAD AS SELECT. Následující příklad porovnává conventional a direct path INSERT, který zálohuje vybranou tabulku. Statistiky zpracování porovnává tabulka 7. Příklad č. 1 vkládá data v režimu direct path. INSERT /*+ append */ INTO dw_subscriber_bck SELECT * FROM dw_subscriber; -----------------------------------------------| Id | Operation | Name | -----------------------------------------------| 0 | INSERT STATEMENT | | | 1 | LOAD AS SELECT | DW_SUBSCRIBER_BCK | | 2 | TABLE ACCESS FULL| DW_SUBSCRIBER | -----------------------------------------------Příklad č. 2 vkládá data v režimu conventional path. INSERT INTO dw_subscriber_bck SELECT * FROM dw_subscriber; ------------------------------------------| Id | Operation | Name | ------------------------------------------| 0 | INSERT STATEMENT | | | 1 | TABLE ACCESS FULL| DW_SUBSCRIBER | ------------------------------------------Tabulka 7 - Statistiky zpracování příkazů INSERT v režimu direct a conventional path (Autor)

Statistika recursive calls

Příklad č. 1 Direct path INSERT

Příklad č. 2 Conventional path INSERT

3 330

0

db block gets

118 422

460 667

consistent gets

120 910

270 985

physical reads

117 902

136 259

redo size

469 100

1 805 170 440

sorts (memory)

1

1

sorts (disk)

0

0

91 567

91 567

00:01:53.18

00:03:51.35



62

Při srovnání provedených příkazů je vidět zejména obrovské množství souborů protokolu (redo), který generuje convetional path INSERT. Pokud by data byla vkládána do nově vytvořené tabulky nebo do tabulky vyprázdněné příkazem TRUNCATE, byl by HWM na hodnotě 0, a conventional path INSERT by pravděpodobně trval stejně dlouho jako direct path INSERT. I v takovém případě by však byly soubory protokolu výrazně větší.

4.5.4

Nahrávání dat příkazem MERGE

Příkaz MERGE byl představen ve verzi Oracle9i. Ve starších verzích bylo třeba nejprve vytvořit logiku, které rozeznala nová a změněná data. Následně se spouštěly buď příkazy INSERT, nebo UPDATE. Příkaz MERGE však obsahuje vlastní logiku a na základě vyhodnocení podmínek provede buď vložení, nebo upravení záznamu, a to vše v rámci jedné transakce. Příkaz MERGE provádí úpravu záznamů odlišně, v porovnání s příkazem UPDATE. UPDATE byl navržen pro transakční databáze (systémy OLTP), ale MERGE funguje daleko efektivněji pro objemnější objekty a je tedy vhodný pro použití v datových skladech. Pro demonstrování efektivity příkazu MERGE, lze jako příklad použít část ETL procesu z kapitoly 5.5. Tabulka STG_SUB_STAT_ATTR_M obsahuje bázi identifikačních čísel zákazníků (sloupec DW_SUB_KEY) a seznam statistik s předvyplněnými hodnotami 0. Tabulka STG_MSTAT_GRP02 obsahuje identifikační čísla zákazníků (DW_SUB_KEY) a dva sloupce s vypočítanými hodnotami statistik (MONSTAT_88 a MONSTAT_137). Tabulka STG_SUB_STAT_ATTR_M obsahuje 5 698 875 záznamů a tabulka STG_MSTAT_GRP02 má záznamů 3 486 530. Následuje demonstrace tří různých metod nahrání dat. Příklad č. 1 spouští příkaz UPDATE, který aktualizuje hodnoty obou statistik v tabulce STG_SUB_STAT_ATTR_M z tabulky STG_MSTAT_GRP02, pro všechny zákazníky z této tabulky. UPDATE stg_sub_stat_attr_m s SET (monstat_88, monstat_137) = (SELECT monstat_88, monstat_137 FROM stg_mstat_grp02 g WHERE g.dw_sub_key = s.dw_sub_key) WHERE EXISTS (SELECT 1 FROM stg_mstat_grp02 g WHERE g.dw_sub_key = s.dw_sub_key); Příklad č. 2 provádí stejný příkaz jako příklad č. 1 s tím rozdílem, že nad sloupcem DW_SUB_KEY v tabulce STG_MSTAT_GRP02 je vytvořený unikátní index. Tento sloupec slouží ke spojení v příkazu UPDATE. CREATE UNIQUE INDEX stg_mstat_grp02_idx ON stg_mstat_grp02 (dw_sub_key);


63

Příklad č. 3 spouští příkaz MERGE, který provede stejné změny jako příkaz UPDATE. Index v tom to případě vytvořený není. MERGE INTO stg_sub_stat_attr_m s USING stg_v4_mstat_grp02 g ON (s.dw_sub_key = g.dw_sub_key) WHEN MATCHED THEN UPDATE SET s.monstat_88 = g.monstat_88, s.monstat_137 = g.monstat_137; Následují exekuční plány pro všechny 3 příkazy. Tabulka 8 obsahuje statistiky pro provedené příkazy. Exekuční plán pro příklad č. 1 – příkaz UPDATE. --------------------------------------------------------------| Id | Operation | Name | --------------------------------------------------------------| 0 | UPDATE STATEMENT | | | 1 | UPDATE | STG_SUB_STAT_ATTR_M | | 2 | FILTER | | | 3 | TABLE ACCESS FULL | STG_SUB_STAT_ATTR_M | | 4 | TABLE ACCESS FULL | STG_MSTAT_GRP02 | | 5 | TABLE ACCESS FULL | STG_MSTAT_GRP02 | --------------------------------------------------------------Exekuční plán pro příklad č. 2 – příkaz UPDATE s vytvořeným indexem. -----------------------------------------------------------| Id | Operation | Name | -----------------------------------------------------------| 0 | UPDATE STATEMENT | | | 1 | UPDATE | STG_SUB_STAT_ATTR_M | | 2 | FILTER | | | 3 | TABLE ACCESS FULL | STG_SUB_STAT_ATTR_M | | 4 | INDEX UNIQUE SCAN | STG_MSTAT_GRP02_IDX | | 5 | TABLE ACCESS BY INDEX ROWID| STG_MSTAT_GRP02 | | 6 | INDEX UNIQUE SCAN | STG_MSTAT_GRP02_IDX | ------------------------------------------------------------


64

Exekuční plán pro příklad č. 3 – příkaz MERGE. ---------------------------------------------------| Id | Operation | Name | ---------------------------------------------------| 0 | MERGE STATEMENT | | | 1 | MERGE | STG_SUB_STAT_ATTR_M | | 2 | VIEW | | | 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL| STG_MSTAT_GRP02 | | 5 | TABLE ACCESS FULL| STG_SUB_STAT_ATTR_M | ---------------------------------------------------Tabulka 8 - Statistiky zpracování příkazů UPDATE, UPDATE (index) a MERGE (Autor)

Statistika

Příklad č. 1 UPDATE

recursive calls

Příklad č. 2 UPDATE+INDEX

Příklad č. 3 MERGE

-

780

773

*2 500

3 601 088

3 601 156

consistent gets

*111 318 690

27 842 623

114 664

physical reads

*9 451

117 404

110 622

redo size

-

934 211 016

934 183 420

sorts (memory)

-

1

3

sorts (disk)

-

0

0

rows processed

-

3 486 530

3 486 530

00:03:34.75

00:02:05.19

db block gets

celkový čas

> *01:30:00.00

Databázová session vykovávající příklad č. 1 byla po 90 minutách manuálně ukončena, jelikož samotný příkaz UPDATE je v tomto případě natolik neefektivní, že by jeho zpracování trvalo mnoho dalších hodin, nebo by nebyl dokončen vůbec. Hodnoty v tabulce jsou získané z běžící session těsně před jejím ukončením. Příkaz č. 2 byl dokončen za 3,5 minuty. Poslední příkaz č. 3 byl dokončen za pouhé 2 minuty a v porovnání s příkazem č. 2 má výrazně nižší hodnotu consistent gets. Hodnota consistent gets reprezentuje počet consistent reads (čtení datových bloků v konzistentním režimu), což znamená počet čtení z operační paměti a případně i z návratových segmentů. Čtení z operační paměti je výrazně rychlejší, než fyzické čtení, využívá však více procesorového času. Příkaz č. 3 (MERGE) byl tedy nejen nejrychlejší, ale i nejméně náročný na systémové prostředky. Rovněž není třeba vytvářet index při použití příkazu MERGE tak, jako v případě UPDATE. Důvod vysokého výkonu příkazu MERGE pro velké objekty lze objevit při srovnání exekučních plánů. MERGE potřebuje projít každou tabulkou pouze jednou a pro jejich spojení používá efektivní metodu spojení hash (viz kapitolu 4.1.5). Příkaz UPDATE nelze pro takto velké objemy dat použít bez existence indexu nad sloupcem. Tvorba a případná údrž-


65

ba takového indexu dále zvyšuje čas k provedení příkazu a rovněž zvyšuje nároky na prostor.

4.5.5

Nahrávání dat s využitím externích tabulek

Další nástroj pro nahrávání dat představují externí tabulky, existující od verze 9i. Ty jsou zvláště vhodné a efektivní pro ETL proces, sestavený z SQL a PL/SQL bloků. Jelikož se s nimi lze v oblasti datových skladů poměrně často setkat, je jim věnována tato kapitola. Ne všechna data, které je třeba nahrát do datového skladu, jsou k dispozici ze systémů mající vlastní databázi pro správu a ukládání dat, nebo nejsou pro načítání dat z různých důvodů přístupně. Ať už se jedná o důvody výkonnostní, bezpečnostní či jiné, jsou data do datového skladu dodávána ve formě exportů textových (tzv. flat) souborů. Externí tabulky jsou výborné řešení a v databázi nezabírají téměř žádné místo. V datovém slovníku jsou uloženy pouze meta data obsahující definicí tabulky (podobně jako v případě pohledů) a samotná data zůstávají v externím souboru. Kromě místa je uspořen i čas, který by byl potřebný k nahrání tabulky do databáze. Z pohledu uživatele lze využít naprosto stejné SQL dotazy nad externími tabulkami jako v případě databázových tabulek. Na druhou stranu nevýhody spočívají v nemožnosti vytvoření indexů (pro záznamy neexistují adresy ROWID) nebo využití partitioningu (obzvláště velké soubory je třeba rozdělit do více souborů v operačním systému). Z externích tabulek lze data pouze číst (změna dat je možná v Oracle11g) a při čtení se prochází vždy celý soubor, což je časově náročnější v porovnání s tabulkou, nad kterou je vytvořen index. Thomas Kyte doporučuje použití externích tabulek všude, kde je to možné a zároveň zmiňuje 3 situace, pro které je použít nelze (Kyte, 2005). ● Data je třeba načítat přes síť, protože se soubory nenacházejí na stejném serveru jako databáze. ● Více uživatelů přistupuje současně ke stejným externím tabulkám a zpracovávají rozdílné vstupní soubory. ● Je třeba pracovat s datovými typy LOB, které externí tabulky nepodporují. Následně vysvětluje výhody, které externí tabulky v porovnání s nástrojem SQL*Loader přinášejí. ● Lze využít komplexních podmínek pro výběr dat v klauzuli WHERE. SQL*Loader má jen omezené možnosti v podobě porovnávání rovnosti argumentů a výrazu AND. ● Z externích tabulek je možné upravovat (UPDATE) stávající záznamy. ● Je možné dohledávat (look up) data spojováním (JOIN) externích tabulek s databázovými.


66

● Vkládání dat do více tabulek s použitím podmínek (WHEN), kterou lze použít v syntaxi příkazu INSERT od verze Oracle 9i. Ačkoli SQL*Loader rovněž umožňuje vkládat data do více tabulek, syntaxe takovému příkazu v porovnání složitější. ● Na rozdíl od příkazu SQLLDR, stačí programátorovi umět pouze SQL, pokud chce použít externí tabulky pro nahrávání dat. Před tvorbou externí tabulky je potřeba definovat cesty, kde se budou v operačním systému data nacházet. Je vhodné zvolit různé cesty pro zdrojové soubory, logovací soubory a soubory s odmítnutými záznamy. Odmítnuté záznamy mají nejčastěji příčinu v nesprávném datovém typu nebo jeho délce. K vytvoření adresáře10 je třeba disponovat právem CREATE ANY DIRECTORY a syntaxe příkazu je následující. CREATE DIRECTORY AS 'cesta/k/adresari'; Syntaxe příkazu k vytvoření externí tabulky se skládá z databázové části (shodné s SQL příkazem pro tvorbu tabulky), klauzule ORGANIZATION EXTERNAL a definice externího zdroje (stejná syntaxe, kterou používá SQLLDR). SMS_LOAD, SMS_LOG a SMS_BAD jsou adresáře, kde jsou uloženy zdrojové soubory, a kam se ukládají soubory se záznamem logů a odmítnuté záznamy. V definici je rovněž vhodné, ne však povinné, definovat: ● jak poznat nový záznam (RECORDS DELIMITED BY NEWLINE), ● jak jsou odděleny sloupce (fields terminated by ';'), ● jak v tabulce označit chybějící hodnoty (missing field values are null), ● počet připustitelných odmítnutých záznamů (REJECT LIMIT 0) CREATE TABLE EXT_SMS20130101 ( SMSC_ID VARCHAR2 (3 BYTE), MESSAGE_TYPE VARCHAR2 (3 BYTE), ORIGINATOR VARCHAR2 (50 BYTE), SUBMIT_DATE_TIME VARCHAR2 (14 BYTE), STATUS VARCHAR2 (3 BYTE), LENGTH_OF_MESSAGE VARCHAR2 (30 BYTE)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY SMS_LOAD ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE 'SMS_BAD':'SMS20130101.bad' LOGFILE 'SMS_LOG':'SMS20130101.log' fields terminated by ';' missing field values are null (SMSC_ID CHAR, MESSAGE_TYPE CHAR, 10

Seznam již vytvořených adresářů lze získat ze systémového pohledu DBA_DIRECTORIES.


67

ORIGINATOR CHAR, SUBMIT_DATE_TIME CHAR, STATUS CHAR, LENGTH_OF_MESSAGE CHAR) ) LOCATION (SMS_LOAD:'SMS20130101')) REJECT LIMIT 0; Takto vytvořené tabulce lze pokládat SQL dotazy.

4.5.6

Nahrávání dat s využitím oddílů

Cílem této kapitoly je představit jednu z možností nahrávání dat s využitím funkcionality Oracle Partitioning. Jedná se o výměnu oddílu (partition exchange) a tato metoda představuje další a často využívanou možnost, jak zpracovat velké množství dat se snížením nedostupnosti tabulek v datových skladech. Toto je samozřejmě možné pouze u rozdělených tabulek a po splnění určitých předpokladů. K výměně dochází mezi oddílem a nerozdělenou tabulkou. Tyto objekty musí mít stejné sloupce s totožnými datovými typy. Jedná se o DDL operaci, takže výměna probíhá pouze úpravou v datovém slovníku, kde se aktualizují odkazy na datové segmenty, a nedochází k fyzickému přesunu dat (data zůstávají ve stejných tabulkových prostorech). Z toho důvodu je tato operace velmi rychlá a efektivní. Pokud je však nad jedním z objektů vytvořen primární nebo unikátní klíč, dochází k ověřování dat a doba trvání výměny může být navýšena. Tyto klíče je vhodné před výměnou buď zneplatnit nebo doplnit příkaz klauzulí WITHOUT VALIDATION a data zkontrolovat buď předem, nebo po výměně. Do výměny lze přidat, resp. nepřidat lokální indexy a to připojením klauzule INCLUDING INDEXES, resp. EXCLUDING INDEXES. (ORACLE, 2013). Základní syntaxe příkazu je: ALTER TABLE EXCHANGE PARTITION WITH TABLE ; Vhodným příkladem použití výměny oddílů je přesun předem připravených dat z dočasných (stage) tabulek do cílových tabulek v datovém skladu. Tento proces obvykle probíhá tak, že je nejprve vytvořen nový oddíl v rozdělené tabulky, příkazem CTAS je vytvořena dočasná tabulka a tyto objekty jsou vyměněny. Následně se data nahrají a upraví v dočasné tabulce, a poté je provedena druhá výměna. Tento způsob je efektivní zejména, jsou-li definovány rozdílné tabulkové prostory pro dočasné a cílové (rozdělené) tabulky, protože segmenty jsou po provedení dvou výměn tam, kde byly původně vytvořeny. Tato metoda je použita v praktické ukázce v kapitole 5.5. Poznámka: Funkcionalita Partitioning v Oracle databázích má velký vliv na výkon a přináší výhody pro databázové správce, vývojáře i koncové uživatele. Vzhledem k rozsahu a skutečnosti, že jsem toto téma zpracovával ve své bakalářské práce, se v této


68

práci nebudu věnovat teorii. Přikládám však základní představení Oracle Partitioning, jeho přínosů a typů do přílohy A.

4.5.7

Vypínání indexů při nahrávání dat

O indexech Indexy v databázích obecně tvoří uspořádanou strukturu nad tabulkami, jejichž data uspořádána nejsou. Data jsou v indexech uskupena dle jednoho či více sloupců. Cílem je urychlit operace11 potřebné pro vyhledání relevantních dat. Existují dva základní typy indexů a to B-Tree index, který je častěji používaný, a bitmapový index. Struktura B-Tree má podobu binárního stromu a počet jeho úrovní záleží na velikosti tabulky. Dle (Macák, 2008) "bývá B-Tree index užitečný do cca 6-10% nalezených řádků z tabulky. Bude-li SELECT vracet větší množství řádků, bude FULL TABLE SCAN účinnější způsob nalezení požadovaných řádků než index." Druhý typ indexu, bitmapový, je výhodné použít, pokud hodnoty ve sloupci nabývají několika málo hodnot. Pro každou hodnotu se vytvoří bitová mapa, s jejíž pomocí je možné dohledat požadovaný řádek. Tento typ indexu se nabízí pro použití ve spojení s list partitioningem. Oba typy indexu pracují s ROWID adresou, kterou má přidělenu každý řádek standardní tabulky, a dle které lze jednoznačně určit, kde se řádek v tabulce nachází. Nevýhodou indexů je, že zabírají místo na disku, a pokud jich máme pro jednu tabulku vytvořených více, jejich velikost může dokonce přesáhnout velikost tabulky samotné. Proto není dobré vytvářet indexy pro každý sloupec, ale pečlivě zvážit, jaká data budou často vyhledávána. Další nevýhodou je potřeba udržovat indexy aktuální, každý DDL příkaz index zneplatní a jeho aktualizace stojí čas i systémové prostředky (Loney, Bryla, 2006). Vypínání indexů v ETL procesu Vzhledem k velkým objemům dat, které se přesouvají do datových skladů, je vhodné vyhnout se automatickému obnovování indexů během DML operací nad tabulkou. Jednou z možností je indexy odstranit, a po dokončení přesunu dat, je opět vytvořit. Lepší variantou je však zneplatnění a znovu sestavení (rebuild) indexů. Druhá varianta pro znovu sestavení indexu využije původní index k sestavení nového, a ten poté odstraní. Ačkoli je zapotřebí více dostupného prostotu, protože v jednu chvíli existují dva indexy namísto jednoho, je nový index sestaven rychleji. Po zneplatnění indexu je třeba nastavit session příkazem ALTER SESSION SET skip_unusable_indexes = TRUE. Tím se v rámci takto nastavené session povolí DML opera11

K urychlení operací dochází při použití u příkazu SELECT, UPDATE nebo DELETE při nižším objemu vybíraných nebo měněných dat.


69

ce nad tabulkou s neplatnými indexy. Po dokončení přesunu dat je vhodné nastavit tento inicializační parametr zpět na hodnotu FALSE. Příkaz pro zneplatnění indexu (oddílu) je: ALTER INDEX (MODIFY PARTITION ) UNUSABLE ; Zneplatnit lze i všechny lokální indexy nad určitým oddílem tabulky: ALTER TABLE MODIFY PARTITION UNUSABLE LOCAL INDEXES; Pro znovu sestavení indexu (oddílu indexu) slouží příkaz ALTER INDEX REBUILD (PARTITION ); Operace zneplatnění i znovu sestavení lze v rámci ETL procesu automatizovat pomocí PL/SQL a systémových pohledů, ze kterých lze informace o indexech a oddílech načíst. Zejména jde o pohledy ALL_TAB_PARTITIONS, ALL_IND_PARTITIONS a ALL_INDEXES. Příklad PL/SQL kódu pro znovu sestavení neplatných indexů u tabulky DW_SUBSCRIBER nad oddílem PARTITION_20130101 je následující. DECLARE l_sql_stmt varchar2(4000); cursor SELECT FROM WHERE AND AND AND AND AND

crs is ip.index_name, ip.partition_name all_indexes i, all_ind_partitions ip i.index_name = ip.index_name ip.index_owner = i.owner i.owner = 'DW_OWNER' i.table_name = 'DW_SUBSCRIBER' ip.partition_name = 'PARTITION_20130101' ip.status = 'UNUSABLE';

BEGIN FOR c IN crs LOOP l_sql_stmt := 'ALTER INDEX '||c.INDEX_NAME||' REBUILD PARTITION '||c.PARTITION_NAME; execute immediate(l_sql_stmt); END LOOP; END;


4.5.8

70

Shrnutí

Cílem této kapitoly je shrnout podstatné body z kapitoly 4.5 a následných podkapitol. Pro hromadné mazání dat byl vytvořen příkaz TRUNCATE. Jedná se o jedinou možnost, jak vyprázdnit data v tabulce bez jejího smazání a znovuvytvoření. V porovnání s příkazem DELETE, je TRUNCATE DDL příkaz a z toho plynou výhody jeho použití. Oproti příkazu DELETE, neukládá TRUNCATE data do návratových segmentů pro případný rollback. Důležité je znát dva režimy pro nahrávání dat – conventional path a direct path. Vkládání dat je možné výrazně urychlit v režimu direct path, za předpokladu, že v cílové tabulce byly předtím prováděny operace mazání příkazem DELETE. Vložení dat v tomto režimu ovšem nevyužije volné místo v datových blocích, protože jsou nová data vkládána nad hodnotu HWM. Vkládání dat v režimu conventional path generuje data v souboru protokolu (redo log) a Oracle se snaží data efektivně rozmístit do volných bloků. Z toho důvodu je tento režim pomalejší v tabulkách, ve kterých existují volné bloky po předchozích operacích mazání. Další možnost jak ještě zvýšit rychlost vkládání dat, spočívá v použití paralelního zpracování, nebo klauzule NOLOGGING. Pro tyto metody je režim direct path nutný předpoklad. Kombinovat je lze i vzájemně za účelem dalšího zrychlení. Příkaz MERGE je navržený pro zpracování velkých objemů dat a je tak vhodný k použití v datových skladech. Obsahuje vlastní logiku pro rozlišení nových a existujících záznamů. Funguje však rozdílně, než příkaz UPDATE, a pro aktualizaci dat v jedné tabulky na základě hodnot z druhé tabulky, používá efektivní metodu spojení hash. V případě neexistujících nebo nevhodných indexů je příkaz MERGE jedinou možností, jak aktualizovat data v rozumném čase. V případě existence indexů, je možné použít příkaz UPDATE, ale i v tomto případě bude MERGE rychlejší a využije méně systémových prostředků s dosažením nižšího času. Tato skutečnost je ověřena uskutečněnými testy v této kapitole. Ke zpracování externích textových souborů jsou v Oracle databázích k dispozici externí tabulky. Ty představují záznamy v datovém slovníku s definicí struktury a umístěním externího souboru. Z toho důvodu nezabírají fyzicky žádné místo. K textovým souborům lze přistupovat jako ke klasickým tabulkám a lze provádět SQL dotazy, spojování s jinými tabulkami, a ve verzi Oracle11g je možné i jejich prostřednictvím provádět úpravy v datech. Použití externích tabulek je velmi snadné, efektivní a vývojář nepotřebuje žádné speciální znalosti. S využitím partitioningu lze také provádět rychlé přesuny velkých objemů dat. Jedná se o metodu výměny oddílů (partition exchange). Použití je rovněž vhodné k omezení nedostupnosti cílového objektu. Data jsou nahrána a upravena v předem vytvořené tabulce, která má strukturou shodnou s tabulkou cílovou. Po dokončení všech potřebných operací lze zahájit výměnu. Ta proběhne pomocí úpravy záznamu v datovém slovníku a tudíž velmi rychle.


71

Při hromadném nahrávání dat je třeba řešit i indexy, protože jejich existence tyto operace zpomaluje. Vhodnější a rychlejší variantou, než smazání indexů před nahráním dat a opětovné vytvoření, je zneplatnění a znovu vytvoření (rebuild) indexů. Tato varianta potřebuje více fyzického prostoru, ale nabídne rychlejší dokončení. Tabulka může mít mnoho vytvořených indexů, a v případě rozdělené tabulky s lokálními indexy, se může jednat o velké množství objektů ke zneplatnění a znovu vytvoření. Tento problém je vhodné řešit načtením informací ze systémových pohledů a následným spouštěním vytvořených příkazů pomocí cyklů v jazyce PL/SQL.

4.6

Zpracování dimenzionálních dat

Předchozí kapitoly se zabývaly metodami přesunu velkých objemů dat, což je využíváno především v tabulkách faktů. Tyto tabulky bývají nahrávány přírůstkově na časové (nejčastěji denní) bázi a typickým příkazem je INSERT. Nahrávání dat dimenzí vyžaduje odlišný přístup. Nové záznamy jsou rovněž vkládány, ale jejich množství je oproti faktovým datům výrazně menší. Mnohem častěji dochází k aktualizaci stávajících záznamů a využití příkazu UPDATE. Cílem této kapitoly je popsat metodu, kterou lze použít pro porovnání zdrojových dat s dimenzionálními daty v datovém skladu, a provést potřebné kroky k nahrání nových, aktualizaci změněných a označení smazaných dat. Záznamy smazané ve zdrojových systémech se v datovém skladu obvykle nemaží, pouze je u nich ukončena platnost. To se zpravidla provádí tak, že se nastaví datum ukončení v patřičném sloupci. Metoda bude založena na použití jazyka SQL, a bude tak nezávislá na použitém ETL nástroji a databázovém systému. K dosažení cíle této kapitoly je třeba nejprve definovat metodu zpracování dimenzí obecně, čemu je věnována kapitola 4.6.1. Následně je v kapitole 4.6.2 představeno konkrétní zadání, na které bude obecný postup z metody aplikován, a pro který je vypracován ETL proces jako posloupnost SQL modulů.

4.6.1

Obecná metoda zpracování dimenzionálních dat

Obecná metoda zpracování dimenzionálních dat, se zakládá na provedení rozdílové analýzy, mezi zdrojovou a cílovou tabulkou, pomocí jednoznačného identifikátoru mezi tabulkami. Metoda se skládá ze tří fází, z nichž každá obsahuje několik kroků. Objekty jsou rozděleny do tří vrstev12, podle kterých jsou názvům objektů přiděleny prefixy. Vrstva interface (prefix INT_) umožňuje přístup ke zdrojovým datům, vrstva stage (prefix STG_)

12

V případě použití databáze Oracle má každá vrstva má své vlastní schéma a tabulkové prostory.


72

obsahuje dočasné tabulky či pohledy, a vrstva DWH (prefix DW_) představuje datový sklad s cílovou tabulkou. Pro snazší orientaci v postupu jsou jednotlivé objekty pojmenovány nejen pomocí těchto prefixů, ale jako sufix je použita zkratka ZAK (dimenze zákazník). Unikátní identifikátor (může se jednat o primární klíč) záznamů ve zdrojové tabulce je jmenuje SRC_ZAK_KEY. Cílová tabulka obsahuje unikátní identifikátor se jménem DW_ZAK_KEY a v tabulce je rovněž uložena hodnota unikátního identifikátoru ze zdroje. Všechny tabulky ve vrstvě stage jsou dočasné, a proto bývají před vytvořením nejprve odstraněny. Tento krok je z důvodu přehlednosti v metodě vynechán. Následují jednotlivé fáze a kroky metody. 1. Fáze přípravy V této fázi se připraví dva objekty ze zdrojové a cílové tabulky pro následné využití ve druhé fázi. a) Vytvořit pohled STG_LOOK_ZAK do cílové tabulky s výběrem sloupců DW_ZAK_KEY a SRC_ZAK_KEY. b) Vytvořit tabulku STG_CUR_ZAK s výběrem dat ze zdrojové tabulky. Volitelně lze provést některé transformace (ošetření prázdných záznamů pomocí funkce NVL) a přidat „N/A“ řádek, který se bude přiřazovat neznámým hodnotám ve faktových datech (hodnoty bez nebo s dimenzím dosud neznámým klíčem DW_ZAK_KEY). 2. Fáze rozdílové analýzy Cílem této fáze je porovnat data mezi zdrojovou a cílovou tabulkou, a vytvořit tři dočasné tabulky pro synchronizaci (INSERT a UPDATE) dat do cílové tabulky ve třetí fázi. c) Vytvořit pohled STG_PREV_ZAK do cílové tabulky s výběrem všech sloupců. d) Vytvořit tabulku STG_INUP_ZAK a naplnit ji daty, které jsou v tabulce STG_CUR_ZAK a zároveň nejsou v pohledu STG_PREV_ZAK s porovnáním dat dle všech sloupců. Výsledná tabulka obsahuje rozdíl mezi cílovou a zdrojovou tabulkou. Data v této tabulce mohou být nová, smazaná nebo změněná ve zdroji. e) Vytvořit tabulku STG_INS_ZAK a naplnit ji daty, které jsou v tabulce STG_INUP_ZAK a zároveň nejsou v pohledu STG_PREV_ZAK s porovnáním dat dle hodnoty SRC_ZAK_KEY. Jedná se o nová data, a proto jsou jim během výběru zároveň přiřazeny nové hodnoty DW_ZAK_KEY (obvykle ze sekvence), které budou sloužit jako identifikátor v cílové tabulce. f) Vytvořit tabulku STG_DEL_ZAK a naplnit ji hodnotami SRC_ZAK_KEY, které jsou v tabulce STG_PREV_ZAK a zároveň nejsou v tabulce STG_CUR_ZAK.


73

g) Vytvořit tabulku STG_UPD_ZAK a naplnit ji daty z tabulky STG_INUP_ZAK, které jsou zároveň v pohledu STG_PREV_ZAK. K porovnání je použit sloupec SRC_ZAK_KEY. h) V tabulkách STG_DEL_ZAK a STG_UPD_ZAK je dále třeba aktualizovat sloupec DW_ZAK_KEY, protože tyto hodnoty v tabulce dosud nebyly. Aktualizace se provede z pohledu STG_LOOK_ZAK, dle hodnoty SRC_ZAK_KEY. i) Volitelně lze nad tabulkou STG_UPD_ZAK vytvořit primární klíč nad sloupcem DW_ZAK_KEY. Tento krok slouží jednak k ověření unikátnosti sloupce a následný bezproblémový UPDATE do cílové tabulky, a dále může vést k vyššímu výkonu příkazu UPDATE při větším objemu dat. Význam tohoto kroku je však vhodné předem zvážit, protože kontrola dat může být zajištěna jiným způsobem a výkon příkazu nemusí být třeba zvyšovat v případě malého objemu dat. 3. Fáze provedení změn V poslední fázi metody se použijí dočasné tabulky (STG_INS_ZAK, STG_UPD_ZAK a STG_DEL_ZAK), které byly vytvořeny v druhé fázi, k provedení změn v cílové tabulce. Kromě přesunu již připravených dat se v datových skladech obvykle aktualizují i logovací sloupce. Jde především o datum a čas vložení, aktualizace a ukončení platnosti záznamu. Další sloupce mohou uchovávat i identifikační číslo ETL procesu, který příslušnou změnu způsobil. j) Nejprve se v cílové tabulce označí odstraněné řádky nastavením data a času u záznamů, které lze identifikovat dle existence hodnoty DW_ZAK_KEY v tabulce STG_DEL_ZAK. k) Aktualizace záznamů v cílové tabulce se provede nahráním nových hodnot sloupců z tabulky STG_UPD_ZAK pro existující hodnoty DW_ZAK_KEY v této tabulce. l) Nakonec jsou do cílové tabulky nahrány nové záznamy jednoduchým výběrem všech záznamů z tabulky STG_INS_ZAK.


74

Vhodnou grafickou ilustraci s jednotlivými vrstvami, označenými kroky a objekty poskytuje obrázek 2. VRSTVA INTERFACE

INT_ZAKAZNIK

VRSTVA STAGE

b

STG_CUR_ZAK

d

VRSTVA DWH

j

STG_DEL_ZAK

f

a

c STG_PREV_ZAK

DW_ZAKAZNIK

h STG_LOOK_ZAK

e

STG_INUP_ZAK

h STG_UPD_ZAK

k

g STG_INS_ZAK

l

Obrázek 2 – Model metody pro zpracování dimenzionálních dat (Autor)

4.6.2

Implementace metody zpracování dimenzionálních dat

Cílem této kapitoly je vytvořit ETL proces sestavený z modulů SQL kódů, který bude pravidelně načítat data o zákaznících ze CRM systému do datového skladu. Pro vytvoření ETL procesu bude použita metoda zpracování dimenzionálních dat pomocí rozdílové analýzy z kapitoly 4.6.1. Pro lepší přehlednost a větší názornost je model reality značně zjednodušený. Objekty obsahují pouze významné a základní sloupce. Názvy objektů a sloupců jsou shodné s těmi, které byly představeny v předchozí kapitole, a model reality je shodný s modelem na obrázku 2. Obrázek 3 detailněji zobrazuje zdrojový a cílový objekt.

Obrázek 3 – Zobrazení zdrojového a cílového objektu (Autor)

Do CRM systému se nepřistupuje přímo, ale data jsou předpřipravena v materializovaném pohledu INT_ZAKAZNIK, jehož obnovení probíhá v pravidelných denních intervalech


75

v čase 1:00. Noví zákazníci se vloží do tabulky DW_ZAKAZNIK jako nové záznamy a čas uložení se nahraje do sloupce DW_INS_DATETIME. Změny u zákazníků se aktualizují do stávajících záznamů a čas aktualizace se nahraje do sloupce DW_UPD_DATETIME. U odstraněných zákazníků se pouze vloží datum ukončení do sloupce DW_DEL_DATETIME. Platný záznam lze poznat pro hodnoty 1. 1. 3000 ve sloupci DW_DEL_DATETIME. Následuje tabulka 9, která dle metody v předchozí kapitole obsahuje SQL příkazy pro jednotlivé kroky. Každému příkazu CREATE TABLE ... ; předchází příkaz DROP TABLE ; pro zajištění opakovatelnosti ETL procesu. Tento krok není v tabulce zobrazen z důvodu vyšší přehlednosti. Sekvence, která generuje hodnotu DW_ZAK_KEY pro nové záznamy se jmenuje DW_ZAK_SEQ. Tabulka 9 – Seznam SQL kódů pro jednotlivé fáze ETL procesu zpracování dimenzí (Autor)

Krok

SQL příkaz 1. Fáze přípravy

a)

CREATE OR REPLACE VIEW stg_look_zak AS SELECT dw_zak_key, src_zak_key FROM dw_zakaznik;

b)

CREATE TABLE stg_cur_zak NOLOGGING AS SELECT src_zak_key ,NVL(jmeno, 'XNA') jmeno ,NVL(prijmeni, 'XNA') prijmeni ,NVL(adresa, 'XNA') adresa ,NVL(kategorie, 'XNA') kategorie ,TO_DATE(30000101, 'YYYYMMDD') dw_del_datetime FROM int_zakaznik UNION ALL SELECT 'XNA' src_zak_key ,'XNA' jmeno ,'XNA' prijmeni ,'XNA' adresa ,'XNA' kategorie ,TO_DATE(30000101, 'YYYYMMDD') dw_del_datetime FROM DUAL;


76

2. Fáze rozdílové analýzy

c)

CREATE OR REPLACE VIEW stg_prev_zak AS SELECT src_zak_key, jmeno, prijmeni, adresa, kategorie, dw_del_datetime FROM dw_zakaznik;

d)

CREATE AS SELECT FROM MINUS SELECT FROM

e)

f)

g)

CREATE AS SELECT FROM WHERE

CREATE AS SELECT FROM MINUS SELECT FROM CREATE AS SELECT FROM WHERE

TABLE stg_inup_zak NOLOGGING src_zak_key, jmeno, prijmeni, adresa, kategorie, dw_del_datetime stg_cur_zak src_zak_key, jmeno, prijmeni, adresa, kategorie, dw_del_datetime stg_prev_zak; TABLE stg_ins_zak NOLOGGING dw_zak_seq.NEXTVAL stg_inup_zak stg NOT EXISTS (SELECT FROM WHERE

dw_zak_key, stg.* 1 stg_prev_zak prev stg.src_zak_key = prev.src_zak_key);

TABLE stg_del_zak NOLOGGING -1 dw_zak_key, src_zak_key stg_prev_zak -1 dw_zak_key, src_zak_key stg_cur_zak; TABLE stg_upd_zak NOLOGGING -1 dw_zak_key, stg.* stg_inup_zak stg EXISTS (SELECT 1 FROM stg_prev_zak prev WHERE stg.src_zak_key = prev.src_zak_key);


77

UPDATE stg_upd_zak upd SET upd.dw_zak_key = (SELECT look.dw_zak_key FROM stg_look_zak look WHERE look.src_zak_key = upd.src_zak_key); h)

i)

UPDATE stg_del_zak del SET del.dw_zak_key = (SELECT look.dw_zak_key FROM stg_look_zak look WHERE look.src_zak_key = del.src_zak_key); ALTER TABLE stg_upd_zak ADD (CONSTRAINT pk_zak_key PRIMARY KEY (dw_zak_key) USING INDEX); 3. Fáze provedení změn

j)

UPDATE dw_zakaznik dw SET dw.dw_del_datetime = SYSDATE, dw.dw_upd_datetime = SYSDATE WHERE dw.dw_del_datetime = TO_DATE (30000101, 'YYYYMMDD') AND dw.dw_zak_key IN (SELECT del.dw_zak_key FROM stg_del_zak del);

k)

UPDATE dw_zakaznik dw SET (jmeno, prijmeni, adresa, kategorie, dw_del_datetime, dw_upd_datetime) = (SELECT upd.jmeno, upd.prijmeni, upd.adresa, upd.kategorie, upd.dw_del_datetime, SYSDATE FROM stg_upd_zak upd WHERE upd.dw_zak_key = dw.dw_zak_key) WHERE dw.dw_zak_key IN (SELECT stg.dw_zak_key FROM stg_upd_zak stg);

l)

INSERT INTO dw_zakaznik (dw_zak_key, src_zak_key, jmeno, prijmeni, adresa, kategorie, dw_del_datetime, dw_ins_datetime, dw_upd_datetime) SELECT dw_zak_key, src_zak_key, jmeno, prijmeni, adresa, kategorie, dw_del_datetime, SYSDATE, SYSDATE FROM stg_ins_zak;

V případě implementace metody v databázi Oracle je dále vhodné u dočasných i zdrojové tabulky provést analýzu a vypočítat statistiky. Klauzuli NOLOGGING lze naopak odebrat v databázích, kde není podporována.

5 Optimalizace ETL procesu v praxi

78

5 Optimalizace ETL procesu v praxi Hlavním cílem této kapitoly je zlepšit výkon ETL procesu a dosáhnout tak vyšší spokojenosti koncových uživatelů. K dosažení cíle je třeba uplatnit vhodné optimalizační techniky z této práce v praktickém použití. Existuje problémový ETL proces, který je významný pro uživatele, kteří na základě výsledných dat provádí důležité odhady a zahajují marketingové akce. Hlavním problémem tohoto ETL procesu je doba jeho trvání. Ta je natolik dlouhá, že negativně ovlivňuje kvalitu uživatelských rozhodnutí. Následující kapitoly slouží k seznámení s ETL procesem, zobrazením současného stavu a s ním souvisejících problémů. Poté následuje návrh a implementace nového řešení a závěr posuzuje a zhodnocuje dosažené výsledky.

5.1

Popis a účel ETL procesu

ETL proces, který je třeba optimalizovat, nepatří do základní vrstvy procesů, které načítají data z externích zdrojů, provádí úpravy a následně ukládají data v tabulkách datového skladu. Tento patří do vyšší vrstvy procesů, kterým slouží jako zdroj právě datový sklad a již načtené tabulky spojují, data agregují a připravují pro koncové uživatele. Takovýmto cílovým tabulkám se obecně říká statistiky nebo reporty. V případě řešeného ETL procesu se jedná o měsíční statistiky zákaznických dat (dále jen měsíční statistiky). Proces je spouštěn v pravidelných měsíčních intervalech, jakmile jsou připraveny zdrojové tabulky. Výstupem procesu je tabulka, která obsahuje celou bázi zákazníků s napočítanými statistikami dle definovaných požadavků od uživatelů. Aktuálně se počítá celkem 90 statistik, z nichž každá je definována SQL kódem. Z SQL kódů lze zjistit zdrojové tabulky, které jsou potřebné pro výpočet, podmínky pro výběr dat, i prováděné úpravy. Včasná příprava těchto dat je důležitá pro uživatele, kteří na jejich základě vytvářejí závěry úspěšnosti marketingových a jiných akcí, sledují snižování či zvyšování aktivity uživatelů v různých oblastech a vytvářejí prognózy budoucího vývoje. Jedná se o důležité metriky pro budoucí vývoj společnosti, kde záleží nejen na kvalitě dat, ale i jejich včasné přípravě.

5.2

Popis současného stavu

V současné době zajišťuje běh ETL procesu měsíčních statistik nástroj PowerCenter 9.1 společnosti Informatica. Pro ukládání dat je použita Oracle Database 10g Enterprise Edition Release 10.2. Nástroj PowerCenter obsahuje logiku a spouští jednotlivé části ETL procesu, hlavní operace jsou však prováděny v databázi, protože PowerCenter pouze spouští


79

uložené Oracle procedury. Tím je většina náročných úloh přenechána výkonnějšímu serveru, na kterém databáze běží, a SQL příkazy jsou optimalizované pro Oracle. Následuje stručný popis technického řešení se zaměřením na významné části. Veškeré informace o statistikách (identifikační číslo, název a SQL kód) jsou uloženy v relační tabulce DW_STAT_ATTRIBUTE_DEFINITION. PowerCenter pro každou statistiku spustí proceduru STAT_RUN_STATISTICS, která nejprve vyprázdní a poté naplní dočasnou tabulku TMP_STAT_ daty, dle příslušného SQL kódu. Z výkonnostních důvodů není možné spustit výpočet všech 90 statistik najednou, a proto jsou rozděleny na 10 paralelních větví, v nichž probíhají jednotlivé výpočty za sebou – sériově. Zobrazení posloupnosti statistik lze nalézt na obrázku 11 v příloze B. Každá dočasná tabulka obsahuje identifikační číslo zákazníka a hodnotu příslušné statistiky. Jakmile je takto naplněno všech 90 dočasných tabulek, je spuštěna druhá procedura STAT_RUN_SNAPSHOT_MONTHLY. Ta načte aktuální bázi identifikačních čísel zákazníků z tabulky DW_SUBSCRIBER, ke které provede levé vnější spojení (left outer join) s jednotlivými dočasnými tabulkami. Výsledek spojení vloží do nového oddílu cílové tabulky DW_SUB_STAT_ATTR_M. Tato tabulka je rozdělena do oddílů pomocí rozsahu (range partitioning) po měsících a obsahuje sloupec, který slouží jako partition key (identifikátor měsíce – sloupec PERIOD_KEY), druhý sloupec s identifikačním číslem zákazníka (sloupec DW_SUB_KEY), dalších 90 sloupců s vypočítanými hodnotami jednotlivých statistik a nakonec sloupec s časem vložení (sloupec DW_INS_DATETIME). První dva sloupce představují přirozený klíč celé tabulky, což znamená, že jeden zákazník má v každém měsíci právě jeden záznam. Pro zajištění tohoto přirozeného klíče je nad sloupci PERIOD_KEY a DW_SUB_KEY vytvořen složený unikátní klíč a tedy i index. Celý výše popsaný proces je pro snazší pochopení zobrazen na obrázku 4. VRSTVA DWH DW_STAT_ATTRIBUTE_DEFINITION

Zdrojové tabulky DWH

SQL

DW_SUBSCRIBER

DW_SUB_STAT_ATTR_M

ETL proces DW_SUB_KEY

DW_TAB_01

DW_TAB_02

DW_TAB_n

TMP_STAT_01

STAT_RUN_ STATISTICS

TMP_STAT_02

PERIOD_KEY, DW_SUB_KEY, <m × statistika>, DW_INS_DATETIME

STAT_RUN_SNAPSHOT_ MONTHLY

TMP_STAT_m

Obrázek 4 – Model původního ETL procesu měsíčních statistik (Autor)


5.3

80

Požadavky na změnu

Ačkoli je ETL proces zpracování měsíčních statistik v pořádku co se týče datové kvality, jsou uživatelé nespokojeni s dobou jeho trvání. Průměrná hodnota za posledních 5 měsíců je 68 hodin, a tedy téměř 3 dny. Požadavek na optimalizaci procesu a zkrácení doby trvání je tedy na místě. Uživatele jsou schopni akceptovat dobu, pohybující se okolo 12 hodin, maximálně však 24 hodin. Druhým požadavkem je, aby z pohledu uživatele zůstalo vše beze změn a rovněž, aby nebyly změněny zainteresované objekty. Nesmí dojít ke změně názvů zdrojových a cílových objektů, jejich sloupců, datových typů, indexů, integritních omezení, atd. Rovněž nesmí být přidány nebo odebrány objekty, aby nedošlo k ovlivnění výkonu jiných procesů. To platí zejména pro indexy a integritní omezení, jejichž (ne)existence může významně ovlivnit výkon. Shrnutím tohoto požadavku je skutečnost, že jediné, co je povoleno upravit, je samotný ETL proces, bez ovlivnění objektů v datovém skladě. Třetím a posledním požadavkem je vytvoření nového ETL procesu v nástroji Oracle Designer, namísto stávajícího PowerCenter. Tento požadavek plyne ze současného ETL procesu, kde sloužil nástroj PowerCenter převážně pro spouštění Oracle procedur, což lze zajistit i přímo v databázi, bez nákladů za licenci na software společnosti Informatica.

5.4

Analýza problémové oblasti a volba optimalizace

Při hledání řešení stávající nevyhovující situace, je třeba nejprve identifikovat problémové části ETL procesu. To znamená odhalit statistiky, jejichž výpočet trvá nejdelší dobu. V kapitole 5.2 je zmíněna procedura STAT_RUN_STATISTICS, která vymaže a následně naplní dočasné tabulky aktuálními hodnotami statistik. Kromě této činnosti obsahuje procedura také logovací mechanismus, který do tabulky ETL_MON_STAT_EXECUTION_LOG zapisuje časové údaje (datový typ DATE) před začátkem a po naplnění dočasné tabulky. Rozdílem těchto dvou údajů lze snadno zjistit celkový čas potřebný k naplnění jednotlivých dočasných tabulek a tedy i čas k výpočtu jednotlivých statistik. SQL dotaz k zjištění těchto časů má následující podobu, přičemž celkový čas je převedený na minuty a zaokrouhlený na dvě desetinná místa. SELECT DW_STATTR_KEY, ROUND((STG_FILLED-CLC_STARTED)*24*60,2) AS TOTAL_TIME_MIN FROM ETL_MON_STAT_EXECUTION_LOG WHERE LOAD_ID=503 ORDER BY 2 DESC; Výstup prvních řádků zobrazuje tabulka 10. Celkový výstup pro všechny statistiky je kvůli své délce zobrazen v tabulce 16 v příloze B.


81

Tabulka 10 – Zobrazení statistik s nejdelší dobou trvání (Autor)

DW_STATTR_KEY 173 156 94 127 129 158 144 157

Celkový čas (min.) 2 905,98 1 576,27 1 539,22 1 353,85 1 314,13 1 304,72 1 286,95 1 215,20

Po získání seznamu nejdéle trvajících statistik je třeba nahlédnout do SQL kódů a hledat příčinu. Následující dotaz vrátí SQL kód pro statistiku č. 173. SELECT sql_statement FROM dw_stat_attribute_definition WHERE dw_stattr_key = 173; Obecně všechny statistiky jsou buď součtem záznamů (COUNT) nebo součtem hodnot (SUM) v určitém sloupci. Takto vypadá SQL kód statistiky č. 173. SELECT /*+ FULL(D) FULL(B) PARALLEL(D 4) PARALLEL(B 4) */ D.DW_SUB_KEY, COUNT(*) AS MONSTAT_173 FROM DW_CDR_DATA D, DW_CDR_BILLED B, DW_JURISDICTION J WHERE 1=1 /* Spojení mezi CDR_DATA a DW_JURISDICTION */ AND D.DW_JUR_KEY = J.DW_JUR_KEY /* Spojení mezi CDR_DATA a CDR_BILLED */ AND B.DATE_KEY = D.DATE_KEY AND B.MSG_ID = D.MSG_ID AND B.MSG_ID_SERV = D.MSG_ID_SERV AND B.SPLIT_ROW_NUM = D.SPLIT_ROW_NUM /* Další podmínky */ AND D.TYPE_ID_USG IN (401410,401107, 401303,401202,401101,401000, 401006,402005,401004) AND J.JURISDICTION NOT IN (10027,10029,10057,10060,10071,10031,10032) /* Výběr období = omezení oddílů ke spojení*/ AND B.DATE_KEY BETWEEN 20130101 AND 20130131 AND D.DATE_KEY BETWEEN 20130101 AND 20130131 GROUP BY D.DW_SUB_KEY; Tato statistika sčítá záznamy pro každého zákazníka (sloupec DW_SUB_KEY), které vzniknou po spojení tří tabulek a vyhovují zadaným podmínkám (tučně jsou zvýrazněny


82

jedinečné podmínky pro konkrétní statistiku). Tabulky DW_CDR_DATA a DW_CDR_BILLED jsou rozdělené dle rozsahu na denní oddíly a sloupec DATE_KEY je partition key. Z podmínek lze vyčíst, že výběr dat probíhá od prvního do posledního dne v měsíci, pro který je statistika počítána. Počet záznamů u obou tabulek za jeden den se pohybuje okolo 10 miliónů. Tabulka DW_JURISDICTION obsahuje přibližně 700 záznamů a slouží k určení typu záznamu. Pro obě spojení tabulek je použita metoda hash, rozdělené tabulky jsou čteny úplným průchodem a s využitím paralelismu. Tabulka DW_JURISDICTION je čtena přes index s dohledáním ROWID adres. Z těchto informací je zřejmé, že spojení takto objemných tabulek vyžaduje přečtení obrovského množství datových bloků13 a rovněž využití operační paměti i dočasných segmentů (kvůli spojení hash) je nezanedbatelné. Metoda spojení hash je v tomto případě nejrychlejší, takže zde prostor k optimalizaci není. Při procházení dalších SQL kódů lze odhalit, že celkem 55 statistik získává data ze spojení tabulek DW_CDR_DATA a DW_CDR_BILLED, přičemž podmínky spojení i podmínky pro výběr období (oddílů) jsou shodné. To znamená, že Oracle musí pro 55 statistik přečíst znovu ty stejné datové bloky. Vzhledem ke skutečnosti, že některé tyto statistiky běží zároveň a některé až později, mohou být datové bloky čteny i z databázové buffer cache, což pozitivně ovlivňuje výkon. Rozdíl mezi těmito 55 statistikami je tvořen pouze podmínkami, které jsou jedinečné pro konkrétní statistiky, a v některých případech dochází ke spojení s menší tabulkou dimenzí za účelem filtrace dat pomocí dalších podmínek. Na základě dosud zjištěných faktů lze určit hlavní metodu, která zefektivní a urychlí ETL proces měsíčních statistik. Tato metoda spočívá ve sloučení SQL kódu těch statistik, které počítají data ze stejných zdrojových tabulek. Společné spojovací podmínky a podmínky pro výběr oddílů zůstanou ve společném SQL kódu zachovány a jedinečné podmínky pro konkrétní statistiky lze definovat na úrovni statistiky pomocí příkazu CASE. Spojení s menšími tabulkami lze realizovat levým (případně pravým) vnějším spojením s tím, že výběr dat je rovněž dále upřesněn až na úrovni konkrétní podmínky, která počítá hodnoty statistiky. Hlavním cílem celé této metody je zamezit opakovanému čtení stejných datových bloků kvůli výpočtu více statistik jednotlivě. Sloučení SQL kódu je vhodné demonstrovat na konkrétním příkladu. SQL kód statistiky č. 156, která má druhou nejdelší dobu trvání, je následující.

13

Jeden oddíl tabulky DW_CDR_DATA obsahuje průměrně 220 000 datových bloku a jeden oddíl tabulky DW_CDR_BILLED jich obsahuje průměrně 20 000. Každý blok má velikost 32 kB.


83

SELECT /*+ FULL(D) FULL(B) PARALLEL(D 4) PARALLEL(B 4) */ D.DW_SUB_KEY, SUM(B.BILLED_AMOUNT/10000) AS MONSTAT_156 FROM DW_CDR_DATA D, DW_CDR_BILLED B WHERE 1=1 /* Spojení mezi CDR_DATA a CDR_BILLED */ AND B.DATE_KEY = D.DATE_KEY AND B.MSG_ID = D.MSG_ID AND B.MSG_ID_SERV = D.MSG_ID_SERV AND B.SPLIT_ROW_NUM = D.SPLIT_ROW_NUM /* Další podmínky */ AND D.TYPE_ID_USG IN (101000,101101,101202,101303,101408,101106,102009,102010,101005,101004) AND D.POINT_CLASS_TARGET NOT IN (16000,16001,16003,16004,16005,16006,16007,16008,16009) /* Výběr období = omezení oddílů ke spojení*/ AND B.DATE_KEY BETWEEN 20130101 AND 20130131 AND D.DATE_KEY BETWEEN 20130101 AND 20130131 GROUP BY D.DW_SUB_KEY; Tučně zvýrazněné řádky opět označují specifické podmínky pro tuto statistiku. Ve srovnání s kódem statistiky 173, dochází v tomto případě ke spojení pouze dvou velkých rozdělených tabulek, bez dalšího spojení s tabulkou DW_JURISDICTION. Pokud se tedy k vnitřnímu spojení (inner join) tabulek DW_CDR_DATA a DW_CDR_BILLED připojí tabulka DW_JURISDICTION levým vnějším spojením (left outer join), je možné sloučit oba SQL kódy do jednoho, bez ovlivnění výsledných dat. Pro záznamy, k nimž se nedohledá příslušný spojovací klíč z tabulky DW_JURISTICTION, vznikne prázdný sloupec (s hodnotou NULL). Tyto prázdné sloupce lze následně odfiltrovat v definici podmínek pro statistiku. SQL kód, po sloučení kódu statistik 156 a 173, má následující podobu. SELECT /*+ FULL(D) FULL(B) PARALLEL(D 4) PARALLEL(B 4) */ D.DW_SUB_KEY, /* Podmínky pro výpočet statistiky č. 156*/ SUM( CASE WHEN D.TYPE_ID_USG IN (101000,101101,101202,101303,101408,101106,102009,102010,101005,101004) AND D.POINT_CLASS_TARGET NOT IN (16000,16001,16003,16004,16005,16006,16007,16008,16009) THEN B.BILLED_AMOUNT/10000 END ) AS MONSTAT_156,


84

/* Podmínky pro výpočet statistiky č. 173*/ COUNT( CASE WHEN D.TYPE_ID_USG IN (401410,401107,401303,401202,401101,401000, 401006,402005,401004) AND J.JURISDICTION NOT IN (10027,10029,10057,10060,10071,10031,10032) THEN 1 END ) AS MONSTAT_173 FROM DW_CDR_DATA D, DW_CDR_BILLED B, DW_JURISDICTION J WHERE 1=1 /* Spojení mezi CDR_DATA a CDR_BILLED */ AND B.DATE_KEY = D.DATE_KEY AND B.MSG_ID = D.MSG_ID AND B.MSG_ID_SERV = D.MSG_ID_SERV AND B.SPLIT_ROW_NUM = D.SPLIT_ROW_NUM /* Spojení mezi CDR_DATA a DW_JURISDICTION */ AND D.DW_JUR_KEY = J.DW_JUR_KEY (+) /* Výběr období = omezení oddílů ke spojení*/ AND B.DATE_KEY BETWEEN 20130101 AND 20130131 AND D.DATE_KEY BETWEEN 20130101 AND 20130131 GROUP BY D.DW_SUB_KEY; Poznámka: Na tomto místě je vhodné zdůraznit, že podmínka JURISDICTION NOT IN (10027, 10029, 10057, 10060, 10071, 10031, 10032) nevybere NULL hodnoty vzniklé po levém vnějším spojení s tabulkou DW_JURISDICTION. Tuto podmínku lze totiž přepsat do následujícího ekvivalentu. (JURISDICTION != 10027) AND (JURISDICTION != 10057) AND (JURISDICTION != 10060) AND (JURISDICTION != 10071) AND (JURISDICTION != 10031) AND (JURISDICTION != 10032) V Oracle platí, že porovnávání čehokoli s NULL je vždy vyhodnoceno jako nepravda, což bylo již dokázáno v kapitole 4.4.2, takže příkaz vybere všechny neprázdné (nejsou NULL) hodnoty, které se nerovnají hodnotám v definovaném seznamu. Aby bylo možné aplikovat metodu sloučení SQL kódů na všechny měsíční statistiky, je třeba rozdělit statistiky do skupin, které čtou data ze stejných zdrojových tabulek. Již bylo zjištěno, že u statistik 173, 156 a 53 dalších lze SQL kódy takto sloučit. Celkem 55 statistik tedy tvoří skupinu č. 1. Po analýze zbývajících 35 statistik vzniklo dalších 5 skupin. Všech 6 skupin s rozdělením jednotlivých statistik a seznamem zdrojových tabulek zobrazuje tabulka 11.


85

Poznámka1: Názvy zdrojových tabulek nejsou pro účely této práce podstatné, slouží pouze pro představu o počtu používaných zdrojů. Poznímka2: Znak (+) představuje starší syntaxi pro vnější spojení, se kterou se lze v SQL kódech pro databáze Oracle setkat. Pro výběr dat z ostatních tabulek je použito spojení vnitřní. Pro každou skupinu statistik je třeba vytvořit nový SQL kód, který nahradí všechny původní kódy. Pro tento účel je použita stejná metoda, která byla použita pro sloučení statistiky 173 a 153, dříve v této kapitole. Výstupem je 6 nových SQL kódů pro každou skupinu. Tím je hlavní část optimalizační metody hotova, jelikož se výrazně sníží doba k přečtení zdrojových tabulek, které je nyní potřeba přečíst pouze jednou. Další kapitola se věnuje tvorbě nového ETL procesu, který zajistí výpočet měsíčních statistik pomocí optimalizovaných SQL kódů, a využije i dalších optimalizačních technik z této práce. Tabulka 11 - Rozdělení statistik do skupin (Autor)

Skupina

Seznam statistik

Zdrojové tabulky

1

91, 92, 93, 95, 94, 99, 100, 101, 104, 105, 106, 107, 108, 112, 113, 114, 115, 116, 127, 128, 129, 130, 131, 132, 134, 135, 140, 141, 142, 143, 144, 148, 149, 150, 154, 155, 156, 157, 158, 159, 160, 161, 162, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180

DW_CDR_DATA, DW_CDR_BILLED, DW_JURISDICTION (+)

2

88, 137

DW_BILL_INVOICE_DETAIL, DW_SUBSCRIBER (+)

3

89, 90, 96, 97, 98, 102, 103, 109, 110, 111, 117, 122, 123, 124, 125, 126, 138, 139, 145, 146, 147, 151, 152, 153, 165

DW_SUBSCRIBER, OUT_SUB_MONTHLY_STATS (+)

4

163, 164

5

167, 168

6

118, 119, 120, 121

5.5

DW_ACTIVITY, DW_SUBSCRIBER DW_SUB_PAYMET_H, DW_PAYMENT_METHOD, DW_SUBSCRIBER, DW_PAYMENT DW_SUB_SEG_H, DW_SEGMENT

Návrh a vývoj nového řešení

Cílem této kapitoly je vytvořit návrh nového ETL procesu pro měsíční statistiky a vyvinout SQL kódy pro jeho jednotlivé moduly. ETL proces použije sloučené SQL kódy pro 6 sku-


86

pin statistik (vytvořené pomocí metody z předchozí kapitoly) k výběru dat ze zdrojových tabulek, a s využitím dočasných tabulek data doručí do cílové tabulky DW_SUB_STAT_ATTR_M. Jak je již popsáno v kapitole 5.2, jedná se o tabulku rozdělenou dle rozsahu na měsíční oddíly se složeným unikátní indexem nad sloupci PERIOD_KEY a DW_SUB_KEY. Na správu indexů a oddílů je třeba myslet při tvorbě ETL procesu. Důvodem použití dočasných tabulek v ETL procesu je minimalizace času nedostupnosti afektovaných oddílů cílové tabulky. Pro maximální rychlost doručení vypočítaných dat do cílových oddílů je využita metoda přesunu oddílů (partition exchange), která je popsána v kapitole 4.5.6. Návrh nového ETL procesu zobrazuje obrázek 5. Vzhledem ke skutečnosti, že sloučené SQL kódy ukládají data do různých dočasných tabulek, je možné spustit všechny kódy zároveň. Data jsou uložena do 6 dočasných tabulek STG_MSTAT_GRPxx, kde xx představuje číslo skupiny. Z těchto tabulek se následně data sloučí pomocí vnějšího spojení do tabulky STG_SUB_STAT_ATTR_M, ve které je již připravená aktuální báze identifikačních čísel zákazníků. Posledním krokem je výměna této tabulky s oddílem cílové tabulky. Poznámka: Dočasné tabulky v tomto příkladu i obecně slouží k tomu, aby proběhla extrakce a transformace mimo cílovou tabulku a následný přesun dat byl co možná nejrychlejší a tím doba uzamčení (kvůli DML příkazům, neplatným indexům pro SQL dotazy, atd.) cílové tabulky co možná nejkratší.

VRSTVA DWH DW_SUBSCRIBER

DW_SUB_STAT_ATTR_M DW_SUB_KEY

Zdrojové tabulky DWH

ETL proces INSERT AS SELECT

STG_MSTAT_ GRP01

MERGE INTO

DW_TAB_02

INSERT AS SELECT

STG_MSTAT_ GRP02

MERGE INTO

DW_TAB_n

INSERT AS SELECT

STG_MSTAT_ GRP06

MERGE INTO

DW_TAB_01

EXCHANGE PARTITION

STG_SUB_STAT_ATTR_M

Obrázek 5 – Model návrhu nového ETL procesu měsíčních statistik (Autor)

Celý ETL proces lze rozdělit na 3 fáze, jejichž detailní řešení s komentáři zobrazuje tabulka 12 - fáze přípravy, tabulka 13 - fáze výpočtu a tabulka 14 - fáze dokončení. Tabulky obsahují vytvořené SQL kódy, které lze následně implementovat do nástroje Oracle Designer. Řešení předpokládá výpočet dat za měsíc leden 2013. Období je hlavní parametr, se


87

kterým se ETL proces spouští a je předáván jako číslo posledního dne v měsíci ve formátu YYYYMMDD14. V aktuálním případě se tedy jedná o hodnotu 20130131. Tabulka 12 - Seznam SQL kódů pro 1. fázi ETL procesu měsíčních statistik (Autor)

1. Fáze přípravy SQL nebo PL/SQL příkaz Krok

1

Komentář BEGIN EXECUTE IMMEDIATE 'DROP TABLE STG_SUB_STAT_ATTR_M'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; Krok č. 1 odstraní tabulku, která zůstala vytvořena po předchozím běhu. V případě úplně prvního spuštění ETL procesu nebo neexistence tabulky je ignorována Oracle chyba15 vzniklá z důvodu nemožnosti odstranění neexistujícího objektu.

2

CREATE TABLE stg_sub_stat_attr_m PCTUSED 95 PCTFREE 5 AS SELECT * FROM dw_layer_own.dw_sub_stat_attr_m WHERE 1 = 2;

14

V masce YYYYMMDD představuje Y rok (year), M měsíc (month) a D den (day)

15

ORA-00942: table or view does not exist.


88


Komentář Krok č. 2 vytvoří tabulku STG_SUB_STAT_ATTR_M, která bude mít strukturu shodnou s tabulkou cílovou, což je nutný předpoklad k provedení výměny oddílů. Hodnota PCTUSED je nastavena na vyšší hodnotu 95 i přesto, že se do tabulky bude provádět UPDATE hodnot z dočasných tabulek. Důvodem je rezervování rozsahů již při prvním vložení dat. V kroku č. 6 dochází totiž kromě vložení zákaznických čísel také k nastavení všech statistik na hodnotu 0. Tím dojde k obsazení datových bloků, které by jinak byly s prázdnou hodnotou (NULL) nepoužité. Z toho důvodu se nepředpokládá další výrazné přidělování rozsahů. ALTER TABLE dw_sub_stat_attr_m ADD PARTITION partition_20130131 VALUES LESS THAN ('20130201');

3

4

Krok č. 3 vytvoří nový oddíl v cílové tabulce, do něhož mají být nahrána nová data. ALTER TABLE dw_sub_stat_attr_m EXCHANGE PARTITION partition_20130131 WITH TABLE stg_sub_stat_attr_m EXCLUDING INDEXES; Krok č. 4 provede výměnu nového oddílu (z krok č. 3) s novou tabulkou (z krok č. 2) bez přesunu lokálních indexů. ALTER INDEX dw_sub_stat_attr_m_uk REBUILD PARTITION partition_20130131 PARALLEL 4 COMPUTE STATISTICS;

5

16

Krok č. 5 provede znovu sestavení lokální indexů v cílové tabulce, protože při výměně oddílů dochází ke změně ROWID adres, a tím pádem i ke zneplatnění indexů nad příslušným oddílem. Neplatné indexy vedou k chybě16 při SQL dotazech, které vyhledávají data dle indexovaného sloupce. Pro některé dotazy uživatelů by tak mohla být dostupnost cílové tabulky omezena.

ORA-01502: index '' or partition of such index is in unusable state.


89


Komentář DECLARE V_SQL VARCHAR2 (10000); CURSOR columnlist IS SELECT column_name FROM DBA_TAB_COLUMNS WHERE OWNER = UPPER ('etl_owner') AND TABLE_NAME = UPPER ('stg_sub_stat_attr_m') AND column_name NOT IN ('PERIOD_KEY','DW_SUB_KEY','DW_INS_DATETIME') ORDER BY column_id; BEGIN V_SQL := 'INSERT /*+ append */ INTO STG_SUB_STAT_ATTR_M' || CHR (10) || '(PERIOD_KEY,DW_SUB_KEY,';

6

FOR col IN columnlist LOOP V_SQL := V_SQL || col.column_name || ',' || CHR (10); END LOOP; V_SQL := V_SQL || 'DW_INS_DATE_KEY,DW_INS_DATETIME)' || CHR || '(select' || CHR || '20130131 AS PERIOD_KEY,' || CHR || 'DW_SUB_KEY,' || CHR FOR col IN columnlist LOOP V_SQL := V_SQL || '0 AS ' || col.column_name || END LOOP;

(10) (10) (10) (10);

',' || CHR (10);

V_SQL := V_SQL || 'to_date(''19000101'',''YYYYMMDD'') AS DW_INS_DATETIME' || CHR (10) || 'from (select dw_sub_key from dw_subscriber))'; EXECUTE IMMEDIATE V_SQL; END;


90


Komentář Krok č. 6 sestaví SQL příkaz, který do tabulky STG_SUB_STAT_ATTR_M vloží aktuální bází identifikačních čísel zákazníků (sloupec DW_SUB_KEY), hodnotu 20130131 do sloupce PERIOD_KEY (partition key), datum 1. 1. 1900 do sloupce DW_INS_DATETIME (pomocí této hodnoty bude možné po dokončení ETL procesu vybrat záznamy, kterým nebyla vypočítána žádná statistika) a pro každou statistiku nastaví hodnotu 0 (viz důvod v kroku č. 2). Aby nebylo nutné přepisovat tento kód v případě, že v budoucnu přibydou nové statistiky, jsou sloupce načítány ze systémového pohledu DBA_TAB_COLUMNS do kurzoru a kód je sestaven s využitím cyklů. ALTER TABLE stg_sub_stat_attr_m ADD CONSTRAINT stg_sub_stat_attr_m_uk UNIQUE (period_key, dw_sub_key) USING INDEX;

7 Krok č. 7 nastaví kombinaci PERIOD_KEY a DW_SUB_KEY jako unikátní a zároveň vytvoří index, který v další fázi ETL procesu urychlí sloučení dat příkazem MERGE. Tento krok slouží rovněž k ověření, že žádné identifikační číslo zákazníka nebylo vloženo vícekrát.


91

Tabulka 13 - Seznam SQL kódů pro 2. fázi ETL procesu měsíčních statistik (Autor)

2. Fáze výpočtu SQL nebo PL/SQL příkaz Krok

Komentář

V této fázi jsou provedeny kroky č. 8 až 13 souběžně (paralelně) pro každou z 6 skupin statistik. Zobrazené SQL kódy jsou napsány pro skupinu č. 1 a tučně jsou zvýrazněny části kódu, které je třeba pro každou skupinu nastavit. Před prvním spuštěním ETL procesu, je nutné vytvořit pro každou skupinu tabulku, do které se nahrávají data ze zdrojových tabulek. CREATE TABLE STG_MSTAT_GRPxx ( PERIOD_KEY NUMBER NOT NULL, DW_SUB_KEY NUMBER NOT NULL, <seznam statistik pro skupinu xx>, DW_INS_DATETIME DATE ) PCTUSED 99 PCTFREE 1 NOLOGGING; TRUNCATE TABLE stg_mstat_grp01; 8 Krok č. 8 vyprázdní tabulku pro skupinu č. 1.

9

INSERT /*+ append */ INTO stg_mstat_grp01 (SELECT 20130131 AS period_key, dw_sub_key, monstat_91, .. monstat_180, /*seznam statistik skupiny č. 1*/ SYSDATE AS dw_ins_datetime FROM (<SQL kód pro získání statistik ze skupiny č. 1>) ); Krok č. 9 použije sloučený SQL kód pro výpočet statistik skupiny č. 1 a vloží je do tabulky STG_MSTAT_GRP01.


92


Komentář DECLARE v_start_time DATE := SYSDATE; /*uložen čas začátku*/ BEGIN LOOP UPDATE etl_locks SET lock_value = UPPER ('LOCKED_GRP01') WHERE lock_name = UPPER (MONSTAT') AND lock_value = 'NO_LOCK' EXIT WHEN SQL%ROWCOUNT = 1; IF

10

SQL%ROWCOUNT <> 1 AND SYSDATE < v_start_time + 1 /*pokud nelze provést update a neuběhlo 24 hodin os začátku*/ THEN DBMS_LOCK.sleep (seconds => 60 * 5); /*čekej 5 minut*/ ELSE raise_application_error(-20001,'Lock MONSTAT is already used.'); END IF; END LOOP; END; Krok č. 10 existuje z důvodu paralelního spouštění výpočtu 6 skupin statistik. V případě, kdy by se spustilo sloučení dvou a více skupin statistik do společné tabulky STG_SUB_STAT_ATTR_M zároveň, mohl by Oracle skončit chybou17, protože není možné provádět více příkazů UPDATE/MERGE do jednoho segmentu zároveň. Pro ošetření této chyby existuje tabulka ETL_LOCKS, která má dva sloupce (LOCK_NAME a LOCK_VALUE). Skupina, která bude první připravena provést sloučení, si v této tabulce nastaví svou hodnotu LOCK_VALUE, posune se k provedení dalšího kroku – sloučení, a v kroku 12 svůj zámek opět odebere. Skupina, která bude připravena provést sloučení až po první skupině, zjistí, že si nemůže svůj zámek nastavit a zkusí to znovu za 5 minut. Maximální doba, kterou se může pokoušet nastavit svůj zámek je nastavena na 1 den.

17

ORA-00060: deadlock detected while waiting for resource.


93


11

Komentář MERGE INTO etl_owner.stg_sub_stat_attr_m stg USING etl_owner.stg_mstat_grp01 grp ON (stg.dw_sub_key = grp.dw_sub_key) WHEN MATCHED THEN UPDATE SET monstat_91 = NVL (grp.monstat_91, 0), .. monstat_180 = NVL (grp.monstat_180, 0), dw_ins_datetime = dw_ins_datetime; Krok č. 11 zajišťuje sloučení dat z tabulky STG_MSTAT_GRP01 do tabulky STG_SUB_STAT_ATTR_M. K tomuto účelu je použit příkaz MERGE, jehož účinnost je otestována v kapitole 4.5.4 pro skupinu č. 2. Prázdné hodnoty statistik jsou nahrazeny hodnotou 0.

12

UPDATE SET WHERE AND

etl_owner.etl_locks lock_value = 'NO_LOCK' lock_name = UPPER ('MONSTAT') lock_value = UPPER ('LOCKED_GRP01');

Krok č. 12 uvolní zámek, nastavený v kroku č. 10, po úspěšném sloučení dat.

Tabulka 14 - Seznam SQL kódů pro 3. fázi ETL procesu měsíčních statistik (Autor)

3. Fáze dokončení SQL nebo PL/SQL příkaz Krok

13

Komentář BEGIN dbms_stats.gather_table_stats ( ownname => 'ETL_OWNER', tabname => 'STG_SUB_STAT_ATTR_M', estimate_percent => 20, cascade => TRUE ); END;


94

3. Fáze dokončení SQL nebo PL/SQL příkaz Krok

Komentář Krok č. 13 provede analýzu tabulky se sloučenými hodnotami, která se v následujícím kroku vymění s oddílem cílové tabulky. ALTER TABLE dw_sub_stat_attr_m EXCHANGE PARTITION partition_20130131 WITH TABLE stg_sub_stat_attr_m EXCLUDING INDEXES;

14 Krok č. 14 provede výměnu napočítané tabulky s oddílem cílové tabulky bez přesunu lokálních indexů. Tento krok provede stejný příkaz, který byl proveden v kroku č. 4. ALTER INDEX dw_layer_own.dw_sub_stat_attr_m_uk REBUILD PARTITION partition_20130131 PARALLEL 4 COMPUTE STATISTICS; 15

5.6

Krok č. 15 provede znovu sestavení indexu po jeho zneplatnění výměnou oddílů. Rovněž se jedná o obdobu kroku č. 5.

Zhodnocení dosažených výsledků

Pro řádné zhodnocení dosažených výsledků je třeba stručně shrnout uživatelské požadavky, které byly definovány v kapitole 5.3, a které musí nový ETL proces splňovat. 1. Celková doba trvání ETL procesu by měla snížena na 12 hodin. Maximálně lze tolerovat 24 hodin za nepříznivých podmínek. 2. Změny je třeba provést pouze v samotném procesu. Zdrojové a cílové tabulky a příslušné objekty nesmí být změněny. 3. Pro implementaci nového ETL procesu je třeba použít nástroj Oracle Designer. Na 2. požadavek byl během návrhu ETL procesu brán ohled a mimo dočasných tabulek v ETL procesu nedošlo k žádným dalším změnám. Ke splnění 3. požadavku byly v předchozí kapitole vytvořeny SQL kódy jednotlivých modulů procesu, a ty byly implementovány v nástroji Oracle Designer. Detailní popis tohoto nástroje a postup implementace je již nad rámec této práce. Pro posouzení 1. a zároveň nejdůležitějšího požadavku je třeba nejprve zobrazit časy trvání původního procesu. Ty zobrazuje tabulka 15 pro posled-


95

ních 5 měsíců. Průměrná doba je 68 hodin, přičemž nejnižšího času dosáhl poslední měsíc, a to 41 hodin. Tabulka 5.6 Tabulka 15 - Doby trvaní původního ETL procesu za posledních 5 měsíců (Autor)

Číslo

Název ETL procesu

Měsíc

533

wkf_run_statistics_monthly

20130228

41

503


20130131

55

473


20121231

77

442


20121130

72

381


20120930

93

Doba běhu (hodiny)

Nový ETL proces byl spuštěn za měsíc leden 2013 a časy jednotlivých modulů lze nalézt v tabulce 17 v příloze C. Při zkoumání časů lze vidět, že operace probíhající na úrovni datového slovníků (DROP, TRUNCATE, EXCHANGE, ADD PARTITION), vytváření prázdné tabulky nebo znovu sestavování indexu nad prázdným oddílem, trvají pouze v řádu vteřin. Dále lze vidět, že se vyplatilo spustit výpočet jednotlivých skupin statistik souběžně, protože zatímco byl prováděn nejnáročnější výpočet statistik z první skupiny, byly ostatní skupiny dokončeny. Celkový čas zpracování ETL procesu byl 6 hodin a 10 minut a 39 vteřin. V porovnání s časem zpracování původního ETL procesu za stejné období, se jedná o pokles času o 49 hodin, což výrazně překročilo požadavek uživatelů na 12 hodin a ponechalo výkonnostní rezervy do budoucna.

6 Závěr

96

6 Závěr Hlavním cílem diplomové práce bylo poskytnout rady a doporučení ke zvýšení efektivity systémů BI s použitím databáze Oracle. Práce byla zaměřena především na analýzu komponent datové pumpy (ETL proces) a databázových objektů, které jsou součástí datových skladů. Po teoretickém úvodu, který přibližuje úlohu BI systémů a zasazuje jednotlivé komponenty do kontextu, následuje druhá část práce, která se zabývá nejčastěji používanými technikami optimalizace. Po úvodu a seznámení se se základními pojmy v oblasti ladění v kapitole 4.1, následují kapitoly s jednotlivými fázemi ETL procesu. Kapitola 4.3 se zaměřuje na často používané optimalizační techniky ve fázi výběru, kapitola 4.4 obsahuje techniky pro fázi upravení, a nakonec kapitola 4.4 slouží k představení technik ve fázi nahrání. Tyto techniky jsou vysvětleny a ve vhodných případech podpořeny vlastními testy z reálného prostředí datového skladu, na běžně prováděných úlohách. Často jsou srovnány a změřeny různé metody, které vedou k dosažení stejného cíle. Na konci každé z těchto kapitol se nachází shrnutí, které vyzdvihuje podstatné body z představených optimalizačních technik s doporučeními k jejich použití. V kapitole 4.6 je vytvořena obecná metoda pro zpracování dat dimenzí, která je následně prakticky aplikována při tvorbě ETL procesu zajišťujícího tuto problematiku. ETL proces je tvořen z jednotlivých bloků SQL kódu a je tak použitelný pro většinu databázových systémů. Kapitola 5 je zaměřena na splnění druhého cíle této práce. Tím je implementace představených optimalizačních technik do skutečného ETL procesu za účelem zvýšení jeho efektivity. Původní ETL proces je s optimalizovaným procesem třeba následně porovnat na základě vhodných metrik a posoudit míru jeho přínosu. Vzhledem ke skutečnosti, že došlo k reálné implementaci optimalizovaného ETL procesu do produkčního datového skladu, je jeho přínos využitelný. Zhodnocení výsledků je možné zejména na základě zákazníkem definovaných podmínek. Mezi tyto podmínky patřilo snížení doby trvání ETL procesu z původních průměrných 68 hodin na 12 hodin. Druhým požadavkem bylo provést změny pouze v ETL procesu, bez provádění jakýchkoli úprav v již existujících objektech, ke kterým přistupovali uživatelé. Třetím požadavek spočíval v použití nástroje pro vytvoření ETL procesu. Dobu trvání nového procesu se použitím vhodných optimalizačních technik podařilo snížit na pouhých 6 hodin. Zbylé dva požadavky byly uvažovány již při návrhu nového ETL procesu a tak nebyl problém s jejich dodržením. Tento cíl práce tedy považuji za splněný, jelikož zákazník výsledek optimalizace patřičně ocenil. Pro splnění hlavního cíle nyní sestavím několik nejdůležitějších doporučení. Doporučení pro použití konkrétních optimalizačních technik lze nalézt v příslušných kapitolách a dále pak v jednotlivých shrnutích. Další doporučení a rady ke zvýšení efektivity BI systémů s databází Oracle jsou následující. Kromě znalosti optimalizačních technik je třeba vědět, jak funguje Oracle databáze, jakým způsobem dochází ke spojování tabulek, čtení objektů, jak jsou uloženy data nebo jaké operace využívají které systémové prostředky. Exekuční

6 Závěr

97

plány představují základní stavební kámen k nalezení příčin většiny problémů s výkonem. Rovněž je třeba znát dostupné funkce a příkazy, které Oracle nabízí, jelikož k získání a úpravě dat existuje mnoho cest a možností, a některé jsou efektivní při splnění určitých podmínek, zatímco další je vhodnější použít za jiných podmínek. Vždy je třeba zvážit, jaký mají data objem, jaký je jejich charakter a účel, a zda lze použít indexy. Neméně důležitou radou je „optimalizuj efektivně“. To znamená, optimalizuj hlavně tam, kde to má význam. Snížit dobu potřebnou pro vykonání příkazu z 10 na 5 vteřin, nemá význam, když příkaz, který běží paralelně s tímto, trvá 10 minut, a další se spustí až ve chvíli, kdy jsou tyto dokončeny. Hlavní cíl této práce mohu považovat za splněný ve chvíli, některá z těchto doporučení pomohou skutečně zvýšit výkon v reálném systému BI. Hlavní přínos práce spatřuji v představení nejdůležitějších optimalizačních technik a rad, který by měl každý vývojář znát a umět efektivně používat. Rovněž vlastní praktické zkušenosti z dvou a půl let dosavadní praxe v oblasti datových skladů považuji na velký přínos. Na základě získaných zkušenosti jsem se snažil sestavit hlavní kapitoly a obsah této práce, který jsem podpořil publikacemi významných autorů s daleko rozsáhlejšími zkušenostmi. Přínos práce by měl být podstatný zejména pro začínající vývojáře, nebo zájemce o danou problematiku. Náměty pro budoucí rozšíření práce lze hledat v optimalizačních technikách, kterými se tato práce nezabývala. Rovněž nová verze Oracle databáze nabízí množství nových možností, které by bylo možné prozkoumat a zhodnotit z hlediska použitelnosti a porovnat je s aktuálními řešeními.

7 Terminologický slovník

98

7 Terminologický slovník Termín

Zkratka

Význam (zdroj)

Clustering factor

Číselná hodnota, kterou Oracle přiděluje indexům na základě rozmístění indexovaných záznamů v datových blocích (Autor).

Consistent gets

Oracle metrika určující počet logických čtení z buffer cache (ORACLE, 2013).

Cost Base Optimizer

Data Definition Language

CBO

Softwarový nástroj implementovaný v databázi, který před vykonáním SQL dotazu rozhoduje, jakým způsobem bude vykonán (Macák, 2008).

DDL

Podmnožina SQL příkazů zaměřených na definování, změny a rušení objektů. Typickými zástupci jsou SQL příkazy: CREATE, ALTER, DROP (Macák, 2008). „Zlaté stránky“ Oracle. Katalog všech důležitých informací jako např. seznam všech tabulek, pohledů, sekvencí, uživatelů, uživatelských práv, apod (Macák, 2008).

Data dictionary (Systémový katalog)

Data Manipulation Language

DML

Podmnožina SQL příkazů zaměřených na manipulaci s daty. Typickými příkazy jsou INSERT, UPDATE, DELETE (Macák, 2008).

Database buffer cache

Část paměti SGA, kde se „kešují“ nejčastěji používané datové bloky (Macák, 2008).

Datový blok

Nejmenší datová jednotka v Oracle databázích (Autor).

Datový sklad (Data Warehouse)

Db block gets

Exekuční plán (Explain Plan)

Technologie, která reagovala na potřebu pracovat v analytických aplikacích s daty na stále vyšší úrovni detailu, a tedy i s jejich stále většími objemy (Gála et al., 2006). Oracle metrika určující počet logických čtení z buffer cache v current módu (ORACLE, 2013). Zobrazuje seznam operací, vybraných na základě CBO, které budou provedeny k vykonání SQL příkazů SELECT, UPDATE, INSERT a DELETE (ORACLE, 2013).


99

Zkratka

Význam (zdroj)

On Line Analytical Processing

OLAP

Databáze optimalizovaná pro získávání dat příkazem SELECT založená na principu multidimenzionálních databází, kde jednotlivá data jsou uložena redundantně (zdvojeně) a předpočítána tak, aby nalezení požadované informace příkazem SELECT bylo co nejrychlejší (Macák, 2008).

On Line Transaction Processing

OLTP

Databáze optimalizovaná pro zpracování velkého množství transakcí a rychlého zpracování příkazů typu INSERT, UPDATE, DELETE (Macák, 2008).

Termín

Physical reads

Oracle metrika určující počet přečtených datových bloků z pevného disku do buffer cache (ORACLE, 2013).

Recursive calls

Vnitřní příkazy, které potřebuje Oracle v některých případech provést pro vykonání SQL příkazu (ORACLE, 2013).

Relational Database Management System

RDBMS

Typ databázového systému, který je založený na relačním modelu (Autor).

Rozsah (extent)

Skupina po sobě jdoucích datových bloků (Macák, 2008).

Segment

Databázový objekt (tabulka, oddíl, index, pohled, sekvence, apod.), který je tvořen nejméně jedním rozsahem (Autor).

Shared Global memory

SGA

Konzolový program, který spouští zadané SQL a PL/SQL příkazy (Autor).

SQL*Plus

Structured Query Language Tabulkový prostor (tablespace)

Sdílená paměť instance, k jejímž informacím můžou přistupovat různé procesy (Macák, 2008).

SQL

Strukturovaný dotazovací jazyk, pomocí kterého se v databázi manipuluje s daty, spravují objekty, vytvářejí uživatelé a nastavují práva (Macák, 2008).

TS

Prostor pro segmenty typu tabulka, index a další. Je tvořen jedním nebo více datovými soubory (Macák, 2008).


Termín

100

Zkratka

Význam (zdroj)

Transakce

Transakce je jistá posloupnost nebo specifikace posloupnosti akcí, jako jsou čtení, zápis, výpočet, se kterou se zachází jako s jedním celkem (Pokorný, a další, 2004).

Transakční aplikace

Aplikace primárně určeny pro pořizování a aktualizace dat, tomu odpovídá jejich architektura i uspořádání dat v databázi (Gála et al., 2006).

Trigger

Trigger je událostí spouštěná procedura. DML Trigger čeká na událost typu INSERT, UPDATE nebo DELETE. Poté co událost nastane, vykoná svůj programový kód (Macák, 2008).

8 Seznam použité literatury

101

8 Seznam použité literatury (Basu, 2004)

BASU, Shouvik. 2004. Oracle FAQ | Analytic functions by Example. [online]. 15.11.2004 [cit. 2013-04-12] Dostupné z: .

(Beaulieu, Mishra, 2004)

MISHRA, Sanjay; BEAULIEU, Alan. 2004. Mastering Oracle SQL, 2nd Edition. O'Reilly Media, Second Edition edition, 2004. ISBN: 9780596006327.

(Best, Billings, Tom Best, M.J.Billings: ORACLE Database 10g: Administration 2005) Workshop I. ORACLE Corporation 2005, D17090GC30 (Burleson, 2009)

BURLESON, Donald K. 2009. Burleson Consulting | Tuning SQL to invoke nested loops joins. [online]. 4.9.2009 [cit. 2013-03-28] Dostupné z: < http://www.dba-oracle.com/t_tuning_sql_nested_loops_joins.htm>.

(Gála et al., 2006)

GÁLA, Libor; POUR, Jan a TOMAN, Prokop. 2006. Podniková informatika. Praha : Grada Publishing, 2006. ISBN: 80-247-1278-4.

(Kimball, Ross, 2002)

KIMBALL, Palph; ROSS, Margy. 2002. The Data Warehouse Toolkit The Complete Guide to Dimensional Modeling Second Edition. Wiley Computer Publishing, 2 edition, 2002. ISBN 0-471-20024-7.

(Krch, 2004)

KRCH, David. Partitioning v souvislostech. Databázový svět - informační portál ze světa databázových technologií [online]. 13.04.2004 [cit. 201206-21]. Dostupné z: .

(Kyte, 2005)

KYTE, Thomas. 2005. Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions. Apress; 2 edition. ISBN-13: 9781430229469.

(Lewis, 2006)

LEWIS, Jonathan. 2006. Cost-Based Oracle Fundamentals. Apress, 2006. ISBN: 978-1590596364.

(Loney, Bryla, 2006)

LONEY, Kevin a BRYLA, Bob. 2006. Mistrovství v Oracle database 10g. Brno : Computer Press, 2006. ISBN: 80-251-1277-2.

(Macák, 2008)

MACÁK, Petr. 2008. Administrace databázového systému ORACLE. Praha : Nakladatelství Oeconomica, 2008. ISBN: 978-80-245-1441-3.

(Niemiec, 2007)

NIEMIEC, Richard J. 2008. Oracle Database 10g Performance Tuning Tips & Techniques. McGraw-Hill Osborne Media, 1 edition, 2007. ISBN: 978-0072263053.

(Novotný et al., 2005)

NOVOTNÝ, Ota, POUR, Jan a SLÁNSKÝ, David. 2005. Business Intelligence, Management v informační společnosti ed. vol. 1. Praha: Grada Publishing, 2005. ISBN 80-2471094-3.

8 Seznam použité literatury

102

(ORACLE, 2013)

ORACLE Corporation. ORACLE Database 10g Release 2 (10.2) Documentation (Online). 2013 [cit. 2013-02-01] Dostupné z: .

(ORA-CZ, 2010)

ORACLE Corporation. 2010. Oracle Partitioning Úložiště | Oracle | Segmentace Oracle (Oracle Partitioning). [online]. [cit. 2013-03-28] Dostupné z: .

(ORAFAQ, 2003)

ORAFAQ. 2003. Oracle FAQ | Direct Load Insert. [online]. [cit. 2013-0208] Dostupné z: .

(ORATABLE, 2010)

ORATABLE. 2010. ORATABLE | The Difference Between DECODE and CASE. [online]. 10.7.2010 [cit. 2013-03-14] Dostupné z: .

(Price, 2007)

PRICE, Jason. 2007 Oracle Database 11g SQL: Master SQL and PL/SQL in the Oracle database. McGraw-Hill Osborne Media, 1 edition, 2007 ISBN: 978-0071498500.

(Žák, 2010)

ŽÁK, David. 2010. Architektury a techniky DS: Efektivní navrhování schémat. [online]. 2010 [cit. 2013-04-18] Dostupné z: .

9 Seznam obrázků a tabulek

9 Seznam obrázků a tabulek

Obrázek 1 - Grafické znázornění logických a fyzických struktur (Best, Billings, 2005)....... 28 Obrázek 2 – Model metody pro zpracování dimenzionálních dat (Autor) ........................... 74 Obrázek 3 – Zobrazení zdrojového a cílového objektu (Autor) .......................................... 74 Obrázek 4 – Model původního ETL procesu měsíčních statistik (Autor) ............................ 79 Obrázek 5 – Model návrhu nového ETL procesu měsíčních statistik (Autor) ..................... 86 Obrázek 6 - Grafické zobrazení nerozdělené a rozdělené tabulky (ORACLE, 2013) ....... 104 Obrázek 7 - Grafické zobrazení základních metod partitioningu (ORACLE, 2013) .......... 107 Obrázek 8 - Lokální rozdělený index (ORACLE, 2013) .................................................... 110 Obrázek 9 - Globální rozdělený index (ORACLE, 2013) .................................................. 110 Obrázek 10 - Globální nerozdělený index (ORACLE, 2013) ............................................ 111 Obrázek 11 - Zobrazení posloupnosti spouštění jednotlivých statistik (Obraz z nástroje Informatica PowerCenter) ................................................................................................ 112 Tabulka 1 – Statistiky zpracování příkazů SELECT, WITH (materialized) a WITH (inline) (Autor) ................................................................................................................................ 40 Tabulka 2 - Statistiky zpracování příkazů DISTINCT a EXISTS (Autor) ............................ 42 Tabulka 3 – Tabulka v příkladu pro srovnání operátoru IN a NOT IN (Autor) .................... 43 Tabulka 4 - Statistiky zpracování příkazů - operátory NOT IN a NOT EXISTS, a vnější spojení (Autor) ................................................................................................................................ 45 Tabulka 5 - Funkce a jejich logické výrazy (Autor) ............................................................. 49 Tabulka 6 - Srovnání příkazů TRUNCATE a DELETE (Autor) ........................................... 58 Tabulka 7 - Statistiky zpracování příkazů INSERT v režimu direct a conventional path (Autor) ........................................................................................................................................... 61 Tabulka 8 - Statistiky zpracování příkazů UPDATE, UPDATE (index) a MERGE (Autor) .. 64 Tabulka 9 – Seznam SQL kódů pro jednotlivé fáze ETL procesu zpracování dimenzí (Autor) ........................................................................................................................................... 75 Tabulka 10 – Zobrazení statistik s nejdelší dobou trvání (Autor) ....................................... 81 Tabulka 11 - Rozdělení statistik do skupin (Autor) ............................................................. 85 Tabulka 12 - Seznam SQL kódů pro 1. fázi ETL procesu měsíčních statistik (Autor) ........ 87 Tabulka 13 - Seznam SQL kódů pro 2. fázi ETL procesu měsíčních statistik (Autor) ........ 91 Tabulka 14 - Seznam SQL kódů pro 3. fázi ETL procesu měsíčních statistik (Autor) ........ 93 Tabulka 15 - Doby trvaní původního ETL procesu za posledních 5 měsíců (Autor) .......... 95 Tabulka 16 – Výpis časů výpočtu statistik v původním ETL procesu (Autor) ................... 113 Tabulka 17 - Časy jednotlivých modulů nového ETL procesu měsíčních statistik ........... 115

103

Příloha A: Oracle Partitioning

104

Příloha A: Oracle Partitioning O partitioningu obecně Oracle Partitioning (dále jen partitioning) je funkcionalita Oracle databází a oficiální popis o partitioningu tvrdí, že „je klíčovým požadavkem pro vysoký výkon a vysokou dostupnost databázových prostředí, rozděluje tabulky a indexy do menších komponent, které se snadněji zpracovávají (ORA-CZ, 2010)“. Tyto celky neboli oddíly (z angl. partitions) jsou fyzicky nezávislé. Velikosti oddílů se mohou lišit, mohou být fyzicky uloženy na rozdílných místech a ke každému z nich lze přistupovat samostatně. Naproti tomu datová struktura těchto oddílů je stejná. To znamená, že všechny oddíly mají stejný počet sloupců, shodují se jejich názvy, datové typy a integritní omezení. Obrázek 6 zobrazuje příklad jedné metody rozdělené tabulky a tabulky nerozdělené. Další metody pro dělení tabulek jsou popsány v následujících kapitolách. Jak je z obrázku možné vidět, nad oběma typy tabulek lze vytvářet rozdělené i nerozdělené indexy. Indexům a možnostem jejich rozdělení je věnována poslední kapitola této přílohy.

Obrázek 6 - Grafické zobrazení nerozdělené a rozdělené tabulky (ORACLE, 2013)


105

Přínosy partitioningu Vlastnost partitioningu, rozdělit objekty na menší celky, přináší dle (ORA-CZ, 2010) následující výhody: Vyšší výkon je získán snížením doby potřebné pro zpracování dotazu, a snížením počtu logických a fyzických operací čtení. SQL příkazy se provádí pouze nad relevantními oddíly a tím je snížen objem dat, který se musí prohledat. Tato technika se nazývá Partition Pruning. Parallel Execution, neboli paralelní zpracování18, je technika, při které je jeden příkaz rozdělen na několik menších, a ty jsou vykonány paralelně. Tuto techniku je však oproti jiným databázím (kde je partitioning podmínkou) možné využít u tabulek rozdělených i nerozdělených (Krch, 2004). Konkrétně se jedná o SQL příkazy SELECT a INSERT. Příkazy UPDATE, DELETE a MERGE lze pustit paralelně na nerozdělené tabulky pouze v případě, že nemají vytvořený bitmapový index. Pro rozdělené tabulky takové omezení neplatí (ORACLE, 2013). Další technika sloužící ke zvýšení výkonu se nazývá Partition-wise joins. Jedná se o optimalizační techniku využívanou při spojování (JOIN) více tabulek, kde klíčem k jejich spojení je zároveň partition key (klíč definující, jak mají být tabulky rozděleny). Při spojení takovýchto tabulek jsou pak využity pouze potřebné oddíly a příkaz je dokončen v kratším čase. Nárůst výkonu je znát nejen v případě paralelního zpracování příkazu, ale i v případě zpracování sériového. Větší dostupnost znamená zajištění nepřetržitého přístupu ke kritickým informacím. Operace spojené se správou (např. zálohování a obnovování) je možné provádět nad jedním oddílem a zbytek tabulky je po tuto dobu dostupný. Pokud je tabulka rozdělena a uložena na více pevných disků, lze i při havárii jednoho z nich přistupovat k oddílům uloženým na ostatních discích. Lepší spravovatelnost je dosažena tím, že se spravují menší bloky dat a je možné zjednodušit některé operace. Dle (Krch, 2004) je příkladem koncept rolling window, který se často objevuje v datových skladech. Cituji: „V pravidelných intervalech (např. každý týden) se do datového skladu přihrávají data za poslední období a naopak se z něj mažou, resp. odsouvají do archivu data, která jsou již příliš stará. Partitioning umožňuje rychle odstranit nebo přesunout odpovídající část dat v konkrétním oddíle, zatímco ostatní data (a indexy) nejsou touto operací omezena. Zatímco operace DELETE procházející celou tabulku bude velmi náročná na zdroje, příkaz DROP PARTITION provede pouze změnu na úrovni systémového katalogu (Krch, 2004).“

18

Paralelní zpracování SQL příkazů dokáže významně zvýšit výkon. Zvláště v případě příkazů UPDATE, DELETE a MERGE pracující s velkými objemy dat (ORACLE, 2013)


106

Náklady na partitioning Možné důvody pro nepoužití partitioningu plynou především z počátečních nákladů spojených s jeho zavedením. Partitioning totiž není součástí ani nejvyšší verze Enterprise Edition19 produktu Oracle databáze a pro jeho použití je nutné dokoupit licenci, jejíž cena20 se pohybuje okolo 25% z licence Enterprise Edition. Proto se o možnostech partitioningu vyplatí přemýšlet ve větších firmách, kde jeho přínosy převýší počáteční náklady. Další náklady vznikají v podobě zaplacení času databázového administrátora (DBA). Před zavedením partitioningu je potřeba nejprve analyzovat stávající databázi, tzn. zjistit, jakým způsobem je využívána a jaká metoda partitioningu se v konkrétním případě nabízí jako vhodná. Použití partitioningu totiž nepřináší zvýšení výkonu pro všechny typy SQL dotazů proto je potřeba zvážit, zda se jeho nasazení v konkrétních případech vyplatí.

Kde přemýšlet o partitioningu Použití partitioningu je klíčové pro objemné databáze, ale jeho přínosů lze využít i v případě objektů menších velikostí. „Oracle Corporation doporučuje, aby se všechny tabulky s velikostí větší, než 2 GB staly vážnými kandidáty na rozdělení do oddílů (Loney, Bryla, 2006).“ Další doporučení uvádí, že: „nasazení partitioningu má obvykle smysl u velkých tabulek s řádově desetitisíci až miliony záznamů. U malých tabulek může režie spojená s partitioningem převážit přínosy. Většinou se tedy dělí několik největších tabulek v databázi a ostatní se ponechávají v celku (Krch, 2004).“ Partitioning lze zavést kdykoli. Není tedy nutné už při návrhu databázového schéma vědět, jak veliké budou naše tabulky a zda je tedy budeme chtít dělit či nikoli. Ani při návrhu aplikace pracující nad databází není nutné brát možné budoucí použití partitioningu v potaz. SQL příkazy pro oba typy tabulek jsou totožné21. Rozdělená tabulka totiž z hlediska přístupu vypadá stejně jako nerozdělená.

Rozdělené tabulky Tabulky je možné dělit do oddílů podle tří základních metod zobrazených na obrázku 7. Další možnosti dělení tabulek již vycházejí z těchto základních metod nebo jejich kombinace. Pro dosažení nejlepšího výkonu je dobré mít rozdělit určité skupiny oddílů do vlast19 20

21

Enterprise Edition je verze Oracle database, obsahující nejbohatší výčet funkcí a možností rozšíření. Aktuální cena Oracle database EE s Partitioningem je přibližně 250 000 CZK / 1 procesor / 1 rok bez podpory ze strany Oracle. Více informací lze nalézt na webové stránce https://shop.oracle.com. Totožnost SQL příkazů však není podmínkou v prostředí partitioningu. Existují i speciální příkazy, které lze použít pouze na rozdělené objekty.


107

ních tabulkových prostorů (angl. tablespace). S tím souvisí i výše zmíněné přínosy v podobě zvýšené dostupnosti. Abychom mohli provádět obnovování a zálohování jednoho nebo určité skupiny oddílů bez omezení dostupnosti oddílů ostatních, musí být tyto oddíly uloženy do odlišných tablespace. To samé platí i v případě havárii pevného disku. Vybraná metoda by měla zajistit rovnoměrné rozložení dat mezi oddíly. Pokud data rovnoměrně rozdělena nejsou, nabízí se zvážení výběru vhodnější metody. Každá metoda má definovaný tzv. klíč rozdělení (z angl. partition key), dle kterého se řádky dělí do příslušných oddílů. Tento klíč může být složen z 1 až 16 sloupců. Žádný řádek se po rozdělení nemůže nacházet ve více oddílech. K rozdělení určité tabulky je třeba dle (Loney, Bryla, 2006) určit následující kritéria: ● Metoda dělení (RANGE, LIST, HASH) ● Sloupec nebo sloupce, podle kterých má být dělení provedeno (v případě list partitioningu lze dělit pouze podle jednoho sloupce) ● 3. kritérium se již liší dle použité metody partitioningu: ● RANGE - definují se názvy a meze oddílů ● LIST - definují se názvy oddílů a seznam hodnot ● HASH – definuje se počet oddílů

Obrázek 7 - Grafické zobrazení základních metod partitioningu (ORACLE, 2013)

Range partitioning Historicky první uvedenou a nejpoužívanější metodou partitioningu je range partitioning. Oddíly jsou definovány rozsahem (z angl. range) číselných hodnot. Dělit tabulku je možno dle sloupců typu DATE, NUMBER a dalších datových typů, jejichž hodnoty udávají urči-


108

tou posloupnost (rozsah). Jak již bylo napsáno v předchozí kapitole, v range partitioningu lze vytvořit partition key z více sloupců. Např. datum může být uloženo ve třech sloupcích (den, měsíc a rok). Pomocí rozsahu je možno také oddělit historická data od dat současných. Starší záznamy vytvořené podle data mohou být rozděleny na oddíly po časovém úseku jeden rok a aktuální záznamy mohou oddíly pro každý měsíc, jelikož se s nimi aktuálně pracuje a jsou tedy vytíženější.

List partitioning Při použití list partitioningu je rozložení dat do oddílů dáno seznamem (z angl. list) hodnot z partitioning key definovaným při tvorbě tabulky. Řádky se tedy rozdělí do příslušných oddílů na základně hodnoty v určitém sloupci. Na rozdíl od range partitioningu, kde je určitý rozsah hodnot přidělen jednomu oddílu, list partitioning dává možnosti kontroly nad rozložením dat do oddílů. Ať se jedná o různé oblasti, kategorie či typy (např.: státy, okresy, města apod.). Příkladem použití list partitioningu může být tabulka obsahující rozsáhlý seznam prodejů z různých států.

Hash partitioning Pokud je potřeba rozdělit tabulku, která neobsahuje sloupce vhodné k použití range (ve sloupci nejsou spojité hodnoty), nebo list partitioningu (hodnoty spolu logicky nesouvisí), nebo by jejich použití vedlo k nerovnoměrnému rozdělení dat, zbývá k dispozici hash partitioning. U této metody partitioningu je partition key použit jako vstup pro interní hashovací algoritmus. Ten se postará o rovnoměrné rozložení dat do oddílů. Toto rozdělení je však náhodné a nelze ho řídit, takže nepomůže urychlit některé vyhledávací SQL příkazy. Výhodou však je ideálně rovnoměrné rozložení a snadná změna počtu oddílů. Ostatní výhody partitioningu jsou také zachovány.

Kombinovaný partitioning Dalšími možnostmi, jak rozdělit tabulku, je zvolit kombinaci výše uvedených metod. Toho lze využít, pokud je tabulka i po rozdělení stále příliš velká nebo by bylo vhodné, vzhledem k povaze dat, vytvořit z oddílů další pododdíly (SUBPARTITION). K dispozici ve verzi Oracle databáze 10g jsou kombinace range-list, range-hash, range-range, list-list, listrange, list-hash.


109

Při výběru určité kombinace vždy dochází k rozdělení tabulky do oddílů dle první metody partitioningu (list nebo range) a následuje rozdělení do dalších pododdílů dle druhé metody. V případě range-list lze tedy rozdělit data o prodejích do měsíců a následně pro tyto měsíce vytvořit pododdíly dle místa prodeje. Výběr metody kombinovaného partitioningu tedy závisí na tom, jaké množství dat je uloženo v tabulce a dle čeho jsou často vyhledávána.

Rozdělené indexy Partitioning je možné využit kromě tabulek i na indexy. Rozdělené indexy se dělí na dva typy, a to lokální a globální. Grafické zobrazení indexů u rozdělené tabulky zobrazují obrázky 8 až 10. Lokální rozdělený index je takový index, jehož rozdělení je shodné s rozdělením tabulky. Využívá se u něj tedy stejný partition key a počet oddílů indexu odpovídá počtu oddílů tabulky. Tento typ indexu má výhodu ve snadném vytvoření, údržbě a flexibilitě. Jeho dostupnost při provádění operací (DDL příkazy, zálohování, apod.) je zajištěna pro ty oddíly tabulky, se kterými se aktuálně nepracuje, protože nejsou uzamčeny. Při odstranění oddílu se odstraní také příslušná část indexu (v případě použití globálního indexu by vedlo k jeho zneplatnění) a naopak, pokud vytvoříme nový oddíl tabulky, automaticky se k němu vytvoří příslušný oddíl indexu. Velmi výhodné použití lokálních indexů je pro historická data v oddílech rozdělených pomocí range partitioningu. Není totiž nutné provádět znovu sestavení (REBUILD) indexu při smazání oddílu s nejstaršími daty, které byly smazány (například z důvodu přesunutí do archivu). Další vhodné použití je v OLAP databázích (ORACLE, 2013). Příkaz pro vytvoření lokálního rozděleného indexu je shodný s příkazem pro vytvoření indexu u nerozdělené tabulky, s výjimkou dodatku LOCAL. Při vytváření indexu lze definovat, do jakých tabulkových prostorů se má index uložit. Podobně jako u tabulek vede toto uložení ke zvýšení výkonu a dostupnosti. Globální rozdělený index je rozdělen na základě jiného klíče, než tabulka samotná. „Globální indexy můžeme rozdělit podle rozsahu nebo na základě hashovací funkce, což je novinka, která se objevila ve verzi 10g (Loney, Bryla, 2006).“ Dále platí, že: „globální indexy, rozdělené hašovací funkcí mohou vést ke zvýšení výkonu tehdy, kdy v OLTP prostředí pracujeme s malým počtem bloků nerozdělených indexů, mezi nimiž jsou značné rozdíly. Největší zlepšení však poznáte u dotazů, jejichž klauzule WHERE obsahuje buď podmínku rovnosti, anebo operátor IN (Loney, Bryla, 2006).“ Platí, že pokud by nad související tabulkou byly spuštěny nějaké DDL příkazy, globální indexy by byly označeny jako UNUSABLE a musely by být znovu sestaveny. Ačkoli lze


110

kterýkoli index znovu sestavit ručně, je možné zadat za DDL příkazem volbu UPDATE INDEXES. Vykonání příkazu bude trvat déle, ale index zůstane během této doby přístupný (Loney, Bryla, 2006). (ORACLE, 2013) doporučuje zvolit lokální indexy v případech, kdy je partition key součástí sloupců, nad nimiž je index vytvořený nebo pokud je naší prioritou snadná správa. Naopak globální index je vhodné zvolit v případě, že je index unikátní (UNIQUE) nebo v aplikacích typu OLTP. Poznámka: Nad rozdělenou tabulkou lze samozřejmě vytvořit i globální nerozdělený index, který je naprosto shodný s indexem na nerozdělené tabulce. To samé platí i pro syntaxi DDL příkazu, kterým se vytváří. Lokální rozdělený index vytvořený u rozdělené tabulky:

Obrázek 8 - Lokální rozdělený index (ORACLE, 2013)

Globální rozdělený index vytvořený u rozdělené tabulky:

Obrázek 9 - Globální rozdělený index (ORACLE, 2013)


Globální nerozdělený index vytvořený u rozdělené tabulky:

Obrázek 10 - Globální nerozdělený index (ORACLE, 2013)

111

Příloha B: Původní ETL proces měsíčních statistik

Příloha B:

Původní ETL proces měsíčních statistik

Obrázek 11 - Zobrazení posloupnosti spouštění jednotlivých statistik (Obraz z nástroje Informatica PowerCenter)

112


113

Tabulka 16 – Výpis časů výpočtu statistik v původním ETL procesu (Autor)

DW_STATTR_KEY 173 156 94 127 129 158 144 157 159 91 92 105 161 107 99 140 134 88 112 130 116 135 143 137 132 155 142 141 178 176 162 175 160 174 131 128 100 172 177 113 154 171 90 104 89 101 96

Celkový čas (min.) 2 905,98 1 576,27 1 539,22 1 353,85 1 314,13 1 304,72 1 286,95 1 215,20 716,07 512,77 504,42 459,65 386,70 372,68 332,92 299,13 256,38 246,78 182,88 166,80 157,48 150,57 140,97 134,68 132,60 123,70 123,42 109,78 108,33 86,58 73,63 64,77 63,42 61,98 59,08 57,12 56,52 55,30 54,63 53,27 50,95 50,60 38,98 32,50 31,23 30,73 30,50


DW_STATTR_KEY 170 149 150 115 148 93 180 123 169 110 147 179 106 97 102 95 103 122 153 114 108 126 151 138 146 168 124 165 98 120 109 152 167 111 145 117 139 125 164 118 119 163 121

114

Celkový čas (min.) 29,70 28,33 27,07 25,30 24,62 24,57 23,85 23,65 22,93 22,23 19,75 19,53 16,48 14,62 10,13 9,13 8,98 8,70 7,40 7,37 7,10 6,22 5,77 5,68 4,83 4,83 4,50 2,95 2,92 2,38 2,12 2,08 2,02 1,98 1,97 1,92 1,65 1,62 1,37 1,22 0,65 0,40 0,25

Příloha C: Nový ETL proces měsíčních statistik

115

Příloha C: Nový ETL proces měsíčních statistik Tabulka 17 - Časy jednotlivých modulů nového ETL procesu měsíčních statistik

2. Fáze výpočtu 3. Fáze dokončení Skupina č. 6 Skupina č. 5 Skupina č. 4 Skupina č. 3 Skupina č. 2 Skupina č. 1

1. Fáze přípravy

Fáze

# Název modulu 1 2 3 4 5 6 7 8 9 10 11 12 8 9 10 11 12 8 9 10 11 12 8 9 10 11 12 8 9 10 11 12 8 9 10 11 12 13 14 15

DROP_TABLE CREATE_STG_TAB ADD_PART_V4 EXCHANGE_EI_PART REBUILD_INDEX_PART MSTAT_STG_PREP MSTAT_STG_UK TRUNCATE_TABLE MSTAT_GRP01_INSERT LOCK_SET MSTAT_GRP01_MERGE LOCK_RELEASE TRUNCATE_TABLE MSTAT_GRP02_INSERT LOCK_SET MSTAT_GRP02_MERGE LOCK_RELEASE TRUNCATE_TABLE MSTAT_GRP03_INSERT LOCK_SET MSTAT_GRP03_MERGE LOCK_RELEASE TRUNCATE_TABLE MSTAT_GRP04_INSERT LOCK_SET MSTAT_GRP04_MERGE LOCK_RELEASE TRUNCATE_TABLE MSTAT_GRP05_INSERT LOCK_SET MSTAT_GRP05_MERGE LOCK_RELEASE TRUNCATE_TABLE MSTAT_GRP06_INSERT LOCK_SET MSTAT_GRP06_MERGE LOCK_RELEASE ANALYZE_TABLE EXCHANGE_EI_PART REBUILD_INDEX_PART

Čas začátku

Čas ukončení

12:00:00 12:00:00 12:00:00 12:00:01 12:00:01 12:00:03 12:01:14 12:01:44 12:01:45 17:38:27 17:38:27 18:04:22 12:01:44 12:01:45 12:39:59 12:39:59 12:44:38 12:01:44 12:01:45 12:20:53 12:25:54 12:36:46 12:01:45 12:01:45 12:04:23 12:19:24 12:21:15 12:01:45 12:01:45 12:09:05 12:14:05 12:16:11 12:01:45 12:01:45 12:04:54 12:04:55 12:10:00 18:04:27 18:10:14 18:10:21

12:00:00 12:00:00 12:00:01 12:00:01 12:00:02 12:01:14 12:01:41 12:01:45 17:38:27 17:38:27 18:04:22 18:04:23 12:01:45 12:39:59 12:39:59 12:44:38 12:44:38 12:01:45 12:20:53 12:25:54 12:36:46 12:36:46 12:01:45 12:04:21 12:19:24 12:21:15 12:21:15 12:01:45 12:09:05 12:14:05 12:16:11 12:16:11 12:01:45 12:04:51 12:04:55 12:10:00 12:10:00 18:10:14 18:10:21 18:10:39

Celkový čas 0:00:00 0:00:00 0:00:01 0:00:00 0:00:01 0:01:10 0:00:27 0:00:01 5:36:42 0:00:00 0:25:55 0:00:01 0:00:01 0:38:13 0:00:00 0:04:39 0:00:00 0:00:01 0:19:08 0:05:01 0:10:52 0:00:00 0:00:00 0:02:35 0:15:01 0:01:51 0:00:00 0:00:00 0:07:20 0:05:00 0:02:06 0:00:00 0:00:00 0:03:05 0:00:01 0:05:05 0:00:00 0:05:47 0:00:07 0:00:17

Analýza efektivnosti BI systémů s použitím databáze Oracle

Recommend Documents