Novinky pro Datové sklady Erik Eckhardt Oracle Czech
1
Oracle a EMC v praxi
Zdroj: Winter Corporation – TopTen Program (WinterCorp’s independent survey of the world’s biggest and most heavily used databases), September 8th 2005
Databáze pro Datové sklady • 30 let vývoje a inovace Oracle databáze • Z toho 15 let soust ed ní na oblast BI/DW • Desítky vlastností a stovky funkcí pro BI/DW Úplná infrastruktura pro ETL Programování v PL/SQL Programování v Jav OCI BULK operations Regulerní výrazy Error loging tables Paralell Execution Parallel Index Scans Parallel Insert, Update, Delete Parallel Bitmap Star Query Parallel ANALYZE Parallel Constraint Enabling Data Pumps Export / Import PL/SQL API Transportable Tablespace TT Cross Platform CDC - Change Data Capture Streams Advanced Queuing External Tables SQL-Loader Table Function Multi-Table Insert Merge / Upsert Workflow asované spoušt ní úloh Gateways MS SQL Sybase Informix Teradata
XML Enterprise ETL Slowing Changing Dimensions Transportable ETL modules Multiple configurations Pluggable mapping Target load ordering Metadata lineage and impact analysis Metadata change propagation Deployment to BI Data Quality Data Profiling Data Rules Data Auditor Monitor Usage in Process Flows Data Corrections Applications Connectors E-Business Suite PeopleSoft SAP R/3 Siebel CRM
Vysoký výkon pro dotazování Paralell Execution Distribuované transakce Materialized Views Query-Rewrite Indexy Bitmap Bitmap-Join Function-based Index Skip Scan Index Organized Tables Cluster Tables
Partitioning Range Hash List Composite Partition Pruning Partition-Wise Join Partitioning Local/Global Indexes Table Compression Analytické funkce Statistické funkce Descriptive statistics Hypothesis testing Correlations analysis Ranking functions Cross Tabulations with Chi-square statistics Linear regression ANOVA Test Distribution fit Window Aggregate functions Statistical Aggregates Pareto analysis LAG/LEAD functions Reporting aggregate functions SQL Model No-Logging operace HINTs OLAP Options OLAP API SQL OLAP_TABLE
DataMining Options Naïve Bayes Decision Trees General Linear Models Support Vector Machines Enhanced k-Means Clustering Orthogonal Partitioning Clustering Association Rules or Market Basket Analysis Nonnegative Matrix Factorization Anomaly Detection Attribute Importance
Škálovatelnost a dostupnost RAC Data Guard Flashback Log Miner
Vysoká bezpe nost Advanced Security Virtual Private Database Label Security Auditování
Snadná administrace Automatic Memory & Storage Management Resource Management Resumable operations Online DDL operations Recovery Manager (RMAN) SQL Access Advisor System Managed Undo
...
2
Databáze pro Datové sklady Novinky
• V zá í 2007, uvoln na verze Oracle 11g • 3x více test než v 10g • Grid celkem 2.000 CPU • P es 235.000 funk ních test • Celkem 15.000.000 hodin test
• Co 11g p ináší pro BI&DW?
PARTITIONING
3
Oracle Partitioning 10 let vývoje a zkušeností Základní funkcionalita
Výkonnost
Administrace
Oracle8
Range partitioning Global range indexes
“Static” partition pruning
Basic maintenance operations: add, drop, exchange
Oracle8i
Hash and composite range-hash partitioning
Partition-wise joins “Dynamic” pruning
Merge operation
Oracle9i
List partitioning
Oracle9i R2
Composite range-list partitioning
Oracle10g
Global hash indexes
Oracle10g R2
1M partitions per table
Global index maintenance Fast partition split Local Index maintenance “Multi-dimensional” pruning
Fast drop table
Oracle Partitioning 10 let vývoje a zkušeností Základní funkcionalita
Výkonnost
Administrace
Oracle8
Range partitioning Global range indexes
“Static” partition pruning
Basic maintenance operations: add, drop, exchange
Oracle8i
Hash and composite range-hash partitioning
Partition-wise joins “Dynamic” pruning
Merge operation
Oracle9i
List partitioning
Oracle9i R2
Composite range-list partitioning
Oracle10g
Global hash indexes
Oracle10g R2
1M partitions per table
Oracle Database 11g
More composite choices REF Partitioning Virtual column Part.
Global index maintenance Fast partition split Local Index maintenance “Multi-dimensional” pruning
Fast drop table Interval Partitioning Partition Advisor
4
Oracle Partitioning 11g
Nové typy složeného partitioningu Typ
P íklad použití
List – Range
Geografie – Období
Range - Range
Datum dodání – Datum objednání
List - Hash
Geografie – íslo objednávky
List - List
Geografie – Produkt
Oracle Partitioning 11g Interval Partitioning
CREATE TABLE sales (order_date DATE, ...) PARTITON BY RANGE (order_date) INTERVAL(NUMTOYMINTERVAL(1,'month') (PARTITION p_first VALUES LESS THAN ('01-FEB-2006');
Table SALES
... Jan 2006
Feb 2006
Mar 2006
... Jan 2007
... Oct 2009
Nov 2009
Ostatní partition existují pouze v metadatech První partition je fyzicky vytvo ena
5
Oracle Partitioning 11g Interval Partitioning
CREATE TABLE sales (order_date DATE, ...) PARTITON BY RANGE (order_date) INTERVAL(NUMTOYMINTERVAL(1,'month') (PARTITION p_first VALUES LESS THAN ('01-FEB-2006');
Table SALES
... Jan 2006
Feb 2006
Mar 2006
... Jan 2007
... Oct 2009
Nov 2009
Nová partition je automaticky alokována INSERT INTO sales (order_date DATE, ...) VALUES ('04-MAR-2006',...);
Oracle Partitioning 11g REF Partitioning Table ORDERS
... Jan 2006
...
• RANGE(order_date) • Primary key order_id
Feb 2006
• •
Redundantní ulo ení order_date Redundantní údr ba partitioningu
Table LINEITEMS
... Jan 2006
...
• RANGE(order_date) • Foreign key order_id
Feb 2006
6
Oracle Partitioning 11g REF Partitioning Table ORDERS
... Jan 2006
...
• RANGE(order_date) • Primary key order_id
Feb 2006
PARTITION BY REFERENCE • Partitioning key je zd d n skrz vazbu PK-FK Table LINEITEMS
... Jan 2006
...
• Foreign key order_id
Feb 2006
Oracle Partitioning 11g Virtual Columns
• Základní tabulka se všemi sloupci ...
CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null, ...
12500 12507 12666 12875
Adams Blake King Smith
7
Oracle Partitioning 11g Virtual Columns
• Základní tabulka se všemi sloupci ... • ... je rozší ena o virtuální (odvozený) sloupec CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null, ... acc_branch number(2) generated always as (to_number(substr(to_char(acc_no),1,2)))
12500 12507 12666 12875
Adams Blake King Smith
12 12 12 12
Oracle Partitioning 11g Virtual Columns
• Základní tabulka se všemi sloupci ... • ... je rozší ena o virtuální (odvozený) sloupec • ... a ten je použit jako partitioning key CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null, ... acc_branch number(2) generated always as (to_number(substr(to_char(acc_no),1,2))) partition by list (acc_branch) ...
12500 12507 12666 12875
Adams Blake King Smith
12 12 12 12
...
32320 32407 32758 32980
Jones Clark Hurd Phillips
32 32 32 32
8
COMPRESSION
Data Compression • D je se mezi hodnotami ádk a sloupc na úrovni db bloku (eliminace duplicitních hodnot) • Kompresní pom r závisí na datech • B žn 2:1 až 4:1 (12:1 – Telco)
• Vhodn jší pro historická data (DWH) • Komprese pouze p i „BULK“ operacích (DML operace data nekomprimují) • Režie p i nahrávání dat
• Dekomprese dat až p i p ístupu k nim • Aplika n transparentní • Výhody • Šet í místo (disk / buffer cache) • Snižuje I/O • Zvyšuje výkon dotaz
9
Data Compression v praxi
Table
Partition
SALES
Kompresní pom r a výkonnost aplikace
MS_200330 MS_200331 MS_200332 MS_200333 MS_200334 MS_200335 MS_200336 MS_200337 MS_200338 MS_200339 MS_200340
Total
Segment size Non-compressed Compressed Compression size size factor [bytes] [bytes] 24 051 712 11 403 264 2,11 22 478 848 10 616 832 2,12 23 265 280 11 010 048 2,11 23 330 816 11 010 048 2,12 27 918 336 13 369 344 2,09 30 212 096 14 680 064 2,06 29 884 416 14 352 384 2,08 30 146 560 14 483 456 2,08 33 488 896 17 104 896 1,96 38 469 632 20 840 448 1,85 21 364 736 11 403 264 1,87 304 611 328 150 274 048 2,04
Segment size Non-compressed
350 000 000
Compressed
304 611 328
300 000 000 250 000 000 200 000 000
150 274 048
150 000 000 100 000 000 50 000 000 0
Query performance - Elapsed Time [s]
Query performance - CPU [s]
40
35
35
30 25 20 15 10 5
30 11,59
9,46
Q3
25
Q2
20
Q1
15
4,22
10
7,53
3,55 3,12
5
No
Yes
0
18,57
Q3 Q2 Q1
9,51
4,35 3,59 3,15
7,55
0 No
Yes Com pression
Compression
Query performance - Physical I/O 20 000 18 000 16 000 14 000 12 000 10 000 8 000 6 000 4 000 2 000 0
Query performance - Logical I/O 600 000 500 000
6 625 Q3 Q2
7 623
4 081 3 075
4 707
2 875
No
Yes Compression
Q1
400 000
193 812
Q3
300 000 200 000 100 000
Q2 Q1
167 814 133 889
17 764 35 673 19 670
0 No
Yes Compression
10
OLTP Table Compression • Oracle Database 11g rozši uje stávající kompresy o kompresy vhodnou pro OLTP (transak ní) systémy • Podpora DML operací (INSERT, UPDATE, DELETE)
• Nový algoritmus eliminuje režii (overhead) p i zápisu • Dávková komprese zajiš uje nulový vliv pro OLTP transakce
• Zvýšení výkonu p i tení • Díky mén I/O operacím (fyzické / logické) m že být tení (selecty) dat rychlejší
OLTP Table Compression Jak to funguje
Block header #
Free Space Uncompressed Compressed
!!"
!!"
• Jakmile zapln ní bloku dosáhne parametr PCTFREE, je automaticky na pozadí spušt na komprese dat • Komprese eliminuje „díry“ vzniklé mazáním dat, maximalizuje nep etržité volné místo v bloku, snižuje row-chaining a row-migration
11
Advanced Compression v 11g • OLTP Table Compression • Komprese dat vhodná i pro OLTP systémy
• Data Pump Compression • Komprese dat a metadat p i exportu
• Data Guard Network Compression • Komprese chyb jících archivních log posílaných do Standby databáze
• Fast RMAN Compression • Komprese obsahu „backup set “ p ed zápisem na disk nebo na pásku
• SecureFiles Compression&Deduplication • Komprese a deduplikace nestrukturovaných dat jako jsou obrázky, audio, video, dokumenty, ...
MOLAP
12
ROLAP vs. MOLAP
Rela ní uložení vs. multidimenzionální uložení
vs.
MOLAP
Klasický p ístup
13
MOLAP
P ístup Oracle • MDDS engine integrován do RDBMS • Spole ná bezpe nost, dostupnost, škálovatelnost s RDBMS • Jedno datové úložišt pro rela ní a multidim. data
• Stejná sada nástroj pro správu, vývoj, lad ní nebo zálohování • Oracle Enterprise Manager (OEM) • Oracle Warehouse Builder (OWB) • Recovery Manager (RMAN)
• OLAP API, AW/XML, PL/SQL nebo SQL rozhraní • Podpora writeback, komprese, partitioning
Zvýšení odezvy dotaz Dotazy oproti DWH
SQL Query Region
Date
Product
Channel
14
Zvýšení odezvy dotaz v 8i, 9i, 10g Dotazy oproti DWH s automatickým p episem do Materialized Views
Materialized Views
SQL Query Region
Date
Query Rewrite
Product
Sales by Region
Sales by Date
Sales by Product
Sales by Channel
Channel
Zvýšení odezvy dotaz v 11g
Dotazy oproti DWH s automatickým p episem do OLAP kostky (Cube Organized Materialized Views) SQL Query Region
Date
Query Rewrite
Product
Channel
Automatic Refresh
OLAP Cube
15
DATA INTEGRATION (E-LT)
Oracle Warehouse Builder • Grafické prost edí pro návrh, tvorbu a provoz konsolidovaných datových úložiš (DWH, DM, ...)
• Optimalizované generování kódu pro r zné verze Oracle • Od 10g zdarma – sou ástí jakékoli edice databáze
16
Oracle Warehouse Builder 11g
• OWB je zahrnuto ve standardní instalaci Oracle databáze • Out-of-the-box nastavení pro Workflow, repository/workspace mngmt.
• Siebel konektor a podpora Heterogenous Gateways 11g • Vylepšený „performance tuning“ • Explain plan support, sb r statistik a SQL tuning advisor
Oracle Data Integrator (d íve
)
Platforma pro jakékoli datové integrace v heterogenním prost edí, založena na • E-LT architektura
• Pro transformace není t eba dedikovaný HW a ETL server • Využívá stávajících zdroj a investic • Vysoký výkon a škálovatelnost
• Integrované integrace • Jedno prost edí pro r zné typy dat. integrací a projekt • Event, Service nebo Data Oriented Integrations • DWH, MDM, Migrace, Replikace, Federace, SOA
• Deklarativní návrh dat. pump • Zjednodušuje po et pot ebných krok p i návrhu dat. pump • Snižuje nároky na vývojá e a jejich znalosti; Automaticky generuje datové pumpy nezávisle na zdrojové a cílové db
• Knowledge moduly • „Best-practices“ pro r zné oblasti Datové integrace a používané technologie (CDC pro DB2, Oracle; SCD pro Teradata, MSSQL; Incremental Update pro Netezza ...) 34
17
Oracle Coffee
Ranní káva s Oracle Data Integratorem (ODI) • 18.6.2008 od 8:30 v Oracle Czech • Obsah • Unikátní vlastnosti • Architektura • Jak na • Event-oriented Integration • Service-oriented Integration • Data-oriented Integration • Knowledge moduly – jejich správa, tvorba a modifikace • Datová kvalita, Workflow, Monitorování proces , Data Lineage • P íklady nasazení • ODI a integrace s Oracle BI, EPM, BAM, SOA a OWB
Prostor pro vaše dotazy
18
19
