Analytic%20Extensions%20to%20SQL%20in%20Oracle9i

1
(No Transcript)
2
Building a Terabyte Data Warehouse, Using Linux
and RAC
Session id 40177

• George Lumpkin
• Director Product Management
• Oracle Corporation

3
Do More with Less

• More performance
• More scalability
• More users
• Less capital cost
• Less administration cost

4
RAC for Scalability, Availability, and
Flexibility
5
Linux and RAC for DW Scalability

• Linux Starter Cluster
• Two nodes
• One shared database

Data Warehouse DB
6
Linux and RAC for DW Scalability
As the Business Grows
Data Warehouse DB
7
Linux and RAC for DW Scalability

• so does your
• Environment
• Three Nodes
• One Database

As the Business Grows
Data Warehouse DB
8
Linux and RAC for DW Scalability

• and again
• Four Nodes
• One Database

As the Business Grows
Data Warehouse DB
9
Linux and RAC for DW Availability
When one node fails
Data Warehouse DB
10
Linux and RAC for DW Availability
the load is rebalanced and 3/4th of the
cluster continues the work
When one node fails
Data Warehouse DB
11
Linux and RAC for DW Flexibility
ETL
ETL
ETL
ETL
Query
Query
Query
Query
The Cluster can share all workload ubiquitously
Data Warehouse DB
12
Linux and RAC for DW Flexibility
Query
ETL
ETL
Query
Query
Query
ETL
ETL
or do workload partitioning
Data Warehouse DB
13
Linux and RAC for DW Flexibility
Query
ETL
ETL
Query
ETL
Query
Query
ETL
ETL
ETL
Workload Management and Provisioning made easy
Christmas Data Season for Retail
Data Warehouse DB
14
Linux and RAC for DW Flexibility
Query
ETL
Query
ETL
Query
Query
Query
ETL
ETL
Query
Workload Management and Provisioning made easy
January Analysis Season
Data Warehouse DB
15
RAC and Parallel Execution
16
RAC and Parallel Execution

• Very large queries utilize all resources on the
  cluster

17
RAC and Parallel Execution

• Many large-scale DWs have many concurrrent jobs
• Multiple small-to-medium size queries
• Degree of parallelism lt CPUs-per-node
• With Oracle, queries will automatically run on a
  single node, eliminating traffic over the
  interconnect

18
Recipe for a RAC Linux DW

• Processors
• I/O
• Interconnect

19
Recipe for a RAC Linux DW Processors

• Data warehouse workload determines total number
  of CPUs
• Same sizing considerations as non-clustered DW
• How many processors per node?
• Enough CPUs so that a single node can handle
  most database operations
• Often, 4 cpus is a good balance

20
Recipe for a RAC Linux DWI/O

• I/O is typically the primary determinant of data
  warehouse performance
• Storage configurations for a data warehouse
  should always be chosen based on I/O bandwidth
  not storage capacity
• Rule of thumb at least 100 MBytes/sec of IO
  bandwidth per gigahertz of processing power
• Every component of the IO system should provide
  enough bandwidth disks, IO channels, IO adapters

21
Recipe for a RAC Linux DWI/O

• CPU power and IO bandwidth should be balanced
  within a server
• Example
• Each node has 4 x 2ghz processors ? each node
  can utilize at least 800 MB/sec
• Each node should have enough slots to accommodate
  the necessary IO throughput
• If one host bus adapter drives 150 MB/sec, then 6
  HBAs should accommodate the needed IO bandwidth
• Note that at least one slot is required for the
  interconnect

22
Recipe for a RAC Linux DWInterconnect

• Gigabit ethernets are generally sufficient for
  data-warehouse workloads
• Oracle minimizes interconnect traffic for
  multi-user workloads
• Workloads requiring inter-node parallel query
  will utilize more interconnect bandwidth
• 10Gb ethernet, fibre channel, Infiniband

23
Typical Cluster configuration
1 Gigabit ethernet
4 nodes, each with 4 x 2 Ghz CPUs 5 PCI slots
16-port switch
16 Storage arrays, each with 10-20 disks
24
Oracle Linux/RAC DW Customers
25
RAC/Linux DW Customers

• Euronext
• Database size 1.5 TB
• Hardware 2 x HP DL580 (4 CPUs)
• Storage HP MSA 1000
• Interconnect 1 Gb ethernet
• OS Red Hat
• AOK Berlin
• Database size 780 GB
• Hardware 2 x HP DL580 (4 CPUs)
• Storage EMC Symmetrix
• Interconnect 2 x 1Gb ethernet
• OS SuSE
• Vanderbilt University
• Database size 50 GB
• Hardware 3 x HP DL580 (4 CPUs)
• Storage EMC Symmetrix
• Interconnect 1 Gb ethernet
• OS Red Hat

• National Bank AG
• Database size 75 GB
• Hardware 3 x IBM Express5800 (2 CPUs)
• Interconnect 100 Mb ethernet
• OS SuSE
• Ellis Island Foundation
• Database size 60 GB
• Hardware 2 x HP DL580 (4 CPUs)
• Storage NetApp
• Interconnect 1Gb ethernet
• OS Red Hat

26
Euronext

• Data warehouse supporting Euronext options
  exchange
• Oracle9i Release 2 (2-node RAC)
• HP DL580 G2 w 4 cpus each
• HP MSA1000 storage arrays

27
AOK Berlin

• 780 GB prodn Linux DW Technical Overview
• Uses RAC for scalability, consolidation
• Oracle9i Release 2 (2-node RAC)
• new implementation
• HP DL580 G2 w 4 cpus each
• 2 x 1Gb Ethernet interconnect
• Linux O/S (SuSE)
• Oracle Cluster Manager, Oracle Cluster File
  System
• EMC Symmetrix
• Plan to grow to 1 TB
• additional plans for 2-node 2 TB test system

28
National Bank AG

• 75 GB prodn Linux DW Technical Overview
• Customer portfolio management system
• Oracle9i Release 2 (3-node RAC)
• new implementation
• IBM Express5800/120Me BULL w 2 cpus, 6GB each
• 100 Mb Ethernet interconnect
• Linux O/S (SuSE)
• Oracle Cluster Manager OCFS
• Fiber-connected storage

29
Dell Global IT

• 35 GB prodn Linux DW Technical Overview
• 500 users
• Datamart consolidation
• Single-instance upgrade
• Starting by moving Unix datamarts into single
  instance
• Oracle9i Release 1 (2-node RAC)
• Dell servers
• Linux O/S (RedHat Advanced Server)
• Oracle Cluster Manager
• EMC Clarion storage

30
Vanderbilt University

• 50 GB prodn Linux DW Technical Overview
• 1000 users
• Oracle9i Release 2 (3-node RAC)
• single-instance upgrade
• Query and Reporting for GL and Labor Data
• mixed workload, primarily query
• BusinessObjects is query tool
• HP Proliant DL580 w 4x2GHz cpus 8 GB RAM each
• 1Gb Ethernet interconnect
• Moved from HP-UX to Linux
• Linux O/S (RedHat Advanced Server)
• Oracle Cluster Manager
• EMC Symmetrix

31
Ellis Island Foundation

• 60 GB prodn Linux DW Technical Overview
• 1000 users
• Immigration records for the 22 million people who
  entered America through the port of New York and
  Ellis Island from 1892-1924
• Oracle9i Release 1 (2-node RAC)
• single-instance upgrade
• HP Proliant DL580 w 4 cpus each
• 1 Gb interconnect
• Linux O/S (RedHat Advanced Server)
• Oracle Cluster Manager
• NetApp Filer storage

32
eachnet.com Network Information Service

• 20 GB prodn Linux DW Technical Overview
• The largest C2C website in China who has about
  more than 1 million users.
• Oracle9i Release 2 (2-node RAC)
• upgrade from single-instance Oracle8i
• Dell PowerEdge 6650 w 4 cpus each
• 1Gb Ethernet interconnect
• Moved from HP-UX to Linux
• Linux O/S (RedHat Advanced Server)
• Oracle Cluster Manager
• EDI Technology
• EDI 3500 high-availability disk array

33
Linux-RAC and the Grid
34
Evolution of Business Intelligence with Oracle

• Increasingly common customer theme these days is
  provisioning
• Customers want more value out of their hardware
  expenditures they want to take advantage of
  unused capacity
• Oracles architecture is unique in being able to
  truly support flexible provisioning of processing
  power across multiple databases
• Oracle will be widely deployed in large
  commercial computing grids in the future

35
Real Application Clusters
36
Resource ProvisioningDecember Order
Processing Heavy Analytics Light
ETL processing, Query Reporting, Data Mining,
Order Entry, Shipments, Procurement, Inventory,
37
Resource ProvisioningJanuary Order Processing
Light Heavy Analytics
Order Entry, Shipments, Procurement, Inventory,
ETL processing, Query Reporting, Data Mining
and Scoring, Cube Creation and OLAP Analysis
38
Oracle RACBrings Flexible Processing Power to
Databases on the Grid
39
Next Steps Data Warehousing DB Sessions
Tuesday
Monday

• 1100 AM
• 40153, Room 304
• Oracle Warehouse Builder
• New Oracle Database 10g Release
• 330 PM
• 40176, Room 303
• Security and the Data Warehouse
• 400 PM
• 40166, Room 130
• Oracle Database 10g
• SQL Model Clause

• 830 AM
• 40125, Room 130
• Oracle Database 10g
• A Spatial VLDB Case Study
• 330 PM
• 40177, Room 303
• Building a Terabyte Data Warehouse,
• Using Linux and RAC
•  
• 500 PM
• 40043, Room 104
• Data Pump in Oracle Database 10g
• Foundation for Ultrahigh-Speed Data Movement

For More Info On Oracle BI/DW Go To
http//otn.oracle.com/products/bi/db/dbbi.html
40
Next Steps Data Warehousing DB Sessions
Thursday
830 AM 40179, Room 304 Oracle Database 10g
Data Warehouse Backup and Recovery 1100
AM 36782, Room 304 Experiences with Real-Time
Data Warehousing using Oracle 10g
100PM 40150, Room 102 Turbocharge your
Database, Using the Oracle Database 10g SQLAccess
Advisor
For More Info On Oracle BI/DW Go To
http//otn.oracle.com/products/bi/db/dbbi.html
41
Reminder please complete the OracleWorld
online session surveyThank you.
42
(No Transcript)