Oracle Database 11g for Data Warehousing

1
(No Transcript)
2
Oracle Database 11g for Data Warehousing

• Presenters Name
• Presenters Title

3
Agenda
ltInsert Picture Heregt

• Technology
• Monitoring
• Information Life-cycle Management (ILM)?
• Oracle Optimized Warehouse Initiative
• Market

4
ltInsert Picture Heregt
Technology
5
Parallel Execution
select c.cust_last_name , sum(s.amount_sold)? from
customers c, sales s where c.cust_id
s.cust_id group by c.cust_last_name

Data on Disk
Parallel Servers
Coordinator
aggregate
join
scan
join
aggregate
scan
aggregate
join
scan
Scanners
Joiners
Aggregators
6
Partitioning Benefits
ORDERS
USA
ORDERS
ORDERS
EUROPE
FEB
FEB
JAN
JAN
Large Table Difficult to Manage
Partition Divide and Conquer Easier to
Manage Improve Performance
Composite Partition Better Performance More
flexibility to match business needs
Transparent to applications
7
Partitioning in Oracle Database 11gInterval
Partitioning

• Partitions are created automatically as data
  arrives

8
Partitioning in Oracle Database 11gComplete
Composite Partitioning
?

• Range range
• List list
• List hash
• List range

?
?
?
ORDERS
ORDERS
gt5000
ORDERS
Gold
gt5000
1000-5000
Silver
1000-5000
EUROPE
EUROPE
FEB
USA
USA
JAN
LIST-RANGERegion byOrder Value
RANGE-RANGEOrder Date by Order Value
LIST-LISTRegion by Customer Type
9
Partitioning in Oracle Database 11gReference
Partitioning

• Inherit partitioning strategy

FEB
JAN
PartitionORDERSby Date
APR
MAR
10
Partitioning in Oracle Database 11gVirtual
Column-Based Partitioning
ORDERS ORDER_ID ORDER_DATE CUSTOMER_ID... ----
------ ----------- ----------- -- 9834-US-14
12-JAN-2007 65920 8300-EU-97 14-FEB-2007
39654 3886-EU-02 16-JAN-2007
4529 2566-US-94 19-JAN-2007
15327 3699-US-63 02-FEB-2007 18733

• REGION requires no storage
• Partition by ORDER_DATE, REGION

11
Compression

• Tables and indexes can be compressed
• Can be specified on a per-partition basis
• Typical compression ratio 31
• Requires more CPU to load data
• Decompression hardly costs resources
• Compress for all DML operations
• Less data on disk
• Requires less time to read
• Completely transparent

Up To 3X Compression
12
SQL Query Result Cache

• Store query results in cache
• Repetitive executions can use cached result
• Data Warehouse queries
• Long-running, IO-intensive
• Expensive computations
• Return few rows
• Excellent opportunity for SQL Query Result Cache

--------------------------------------------------
---------------- Id Operation
Name
------------------------------------------------
------------------ 0 SELECT STATEMENT
1
RESULT CACHE fz6cm4jbpcwh48wcyk60m
7qypu 2 SORT GROUP BY ROLLUP
3 HASH JOIN
etc.
13
SQL Query Result CacheOpportunity

• Retail customer data (50 GB)?
• Concurrent users submitting queries randomly
• Executive dashboard with 12 heavy analytical
  queries
• Cache results only at in-line view level
• 12 queries run in random, different order 4
  queries cached
• Measure average, total response time for all users

14
Other Performance FeaturesTransparent to Your
Application

• Materialized Views
• Transparent rewrites of expensive queries
• Including rewrites on remote objects
• Incremental automatic refresh
• Bitmap Indexes
• Optimal storage
• Ideal for star or star look-a-like schemas
• SQL Access Advisor based on workload
• Materialized view advice
• Index advice
• Partition advice

15
Bring Algorithms to the DataNot Data to the
Algorithms

• Analytic computations done in the database
• SQL Analytics
• OLAP
• Data Mining
• Statistics
• Scalability
• Security
• Backup Recovery
• Simplicity

SQL analytics
OLAP
Data Mining
Statistics
16
Native Support for Pivot and Unpivot
SALESREP QU REVENUE ---------- --
---------- 100 Q1 230 100 Q2
240 100 Q3 260 100 Q4
300 101 Q1 200 101 Q2
220 101 Q3 250 101 Q4
260 102 Q1 260 102 Q2
280 102 Q3 265 102 Q4
310
SALESREP Q1 Q2 Q3 Q4 ----------
----- ----- ----- ----- 100 230 240
260 300 101 200 220 250 260
102 260 280 265 310

17
Native Support for Pivot and Unpivot
SALESREP QU REVENUE ---------- --
---------- 100 Q1 230 100 Q2
240 100 Q3 260 100 Q4
300 101 Q1 200 101 Q2
220 101 Q3 250 101 Q4
260 102 Q1 260 102 Q2
280 102 Q3 265 102 Q4
310
QUARTERLY_SALES SALESREP Q1 Q2 Q3
Q4 ---------- ----- ----- ----- ----- 100
230 240 260 300 101 200 220
250 260 102 260 280 265 310

select from quarterly_sales unpivot include
nulls (revenue for quarter in (q1,q2,q3,q4))? orde
r by salesrep, quarter
18
Native Support for Pivot and Unpivot
SALES_BY_QUARTER SALESREP QU
REVENUE ---------- -- ---------- 100 Q1
230 100 Q2 240 100 Q3
160 100 Q4 90 100 Q3
100 100 Q4 140 100 Q4
70 101 Q1 200 101 Q2
220 101 Q3 250 101 Q4
260 102 Q1 260
SALESREP 'Q1' 'Q2' 'Q3' 'Q4' ----------
----- ----- ----- ----- 100 230 240
260 300 101 200 220 250 260
102 260 280 265 310

select from sales_by_quarter pivot
(sum(revenue)? for quarter in ('Q1','Q2','Q3','Q4'
))? order by salesrep
19
Transform Data Where Data ResidesIn-database ETL
technology
Extract
Load
Transform
Insert
Data Pump
Transportable Tablespaces
Partition Exchange Loading
Change Data Capture
Distributed Queries
SQLLoader
External Tables
Table Functions
Multi-Table Insert
MERGE
DML error logging
20
Asynchronous Change Data Capture
Oracle Database 11g

Log files

• Capture changes from redo archive logs
• No changes to source applications
• Minimal performance impact on source applications
• Store changes in change tables
• Provide (bulk) SQL interface to change data

OLTP DB
21
RAC Scale Incrementally

22
Automatic Storage Management

• Storage pool for database files
• Load-balanced across disks
• Capacity on demand
• Add/remove storage on-line
• Automatic IO load balancing
• Fault tolerant, high performance
• Automatically mirrors and stripes
• Low cost
• No IO tuning required
• No volume manager or file system needed

23
Mixed Workloads
report

• Concurrent small data loads and queries
• Looks like... OLTP
• Oracle's read consistency
• Readers never block writers
• Writers never block readers
• Queries are always consistent and auditable
• No deadlocks
• Introduced in Oracle V4 (1982) major
  improvements in V6 (1988)?

Budget table
update
Rollback Segment
Before Image
update
accurate report
24
Database Resource Manager

• Protect the system pro-actively
• Maximum number of concurrent operations
• Priority-dependent maximum Degree Of Parallelism
  (DOP)?

25
Oracle Database Security

?????????
?
Identity Management
26
Feature Usage for Large-Scale Data Warehouses
Partitioning, parallelism, and compression are
the foundation for large-scale data warehousing
Source TB Club Report A survey of 30 multi-TB
Oracle DWs data July 2006
27
ltInsert Picture Heregt
Monitoring
28
I/O MonitoringDatabase Control

29
I/O MonitoringDatabase Control

30
Parallel Execution MonitoringDatabase Control

31
Near Real-Time SQL MonitoringComing in Grid
Control

32
Parallel SQL MonitoringComing in Grid Control

33
ltInsert Picture Heregt
Information Life-cycle Management (ILM)?
34
Information Lifecycle Management
The policies, processes, practices, and tools
used to align the business value of information
with the most appropriate and cost effective IT
infrastructure from the time information is
conceived through its final disposition.
Storage Networking Industry Association (SNIA)
Data Management Forum
35
Information Lifecycle Management
Active High Performance Storage Tier
Less Active Low CostStorage Tier
Historical Online ArchiveStorage Tier
36
Traditional Storage ApproachAll data resides on
a single storage tier
High Performance Storage Tier 72 per Gb
All data on active 972,000!
37
Partitioning is the Foundation for ILMPartition
data onto appropriate storage tier
Low cost Storage Tier 14 per Gb
Read only Storage Tier 7 per Gb
High Performance Storage Tier 72 per Gb
Active
Historical
Less Active
38
Partitioning is the Foundation for ILMMove data
onto appropriate storage tier
Low cost Storage Tier 14 per Gb
Read only Storage Tier 7 per Gb
High Performance Storage Tier 72 per Gb
5 Active
60 Historical
35 Less Active
39
Partitioning is the Foundation for ILMReduce
storage costs accordingly
5 Active
60 Historical
35 Less Active
40
Introduce CompressionReduce storage costs across
all tiers
5 Active
60 Historical
35 Less Active
Lets use compression factor of 3
41
Cost Savings by Storage Tier
42
ltInsert Picture Heregt
Oracle Optimized Warehouse Initiative
43
Oracle Optimized Warehouse Initiative

• Goals for Oracle data warehouse solutions
• Provide superior system performance
• Provide a superior customer experience

44
Full Range of DW Solution Options
Custom

• Flexibility for the most demanding data warehouse
• Benefits
• High performance
• Unlimited scalability
• Completely customizable
• Industry-leading database and hardware

• Database Options
• Management Packs

45
ltInsert Picture Heregt
Market
46
Data Warehouse Market
Oracle is the Data Warehousing DBMS Market Leader

Source IDC, 2006 - Worldwide Data Warehousing
Tools 2005 Vendor Shares
47
Leading ScalabilityWintercorp VLDB Survey
Source http//www.wintercorp.com
48
Oracle DW 10TB Customers (3/2006)Various
Platforms and Architectures

• Acxiom 16 TB HP
• Allstate 15 TB Sun (RAC)?
• Amazon 61 TB HP (RAC)?
• Cellcom 14 TB HP
• CenturyTel 10 TB HP
• Chase 30 TB IBM (RAC)?
• Choicepoint 14 TB Sun
• Claria 38 TB Sun
• Experian 14 TB Sun
• KTF 14 TB HP
• Cingular 25 TB HP

• Mastercard 20 TB IBM (RAC)?
• NASDAQ 35 TB Sun
• NexTel 28 TB HP
• NYSE Group 15 TB HP (RAC)?
• Reliance Ltd 13 TB Sun
• Starwood 12 TB HP
• TIM (Italy) 12 TB HP (RAC)?
• Turkcell 14 TB Sun (RAC)?
• UBS AG 15 TB Sun
• UPS 10 TB HP
• Yahoo! 130 TB Fujitsu

Hundreds of Terabyte DW Customers!
49
Summary
ltInsert Picture Heregt

• Technology
• Monitoring
• Information Life-cycle Management (ILM)?
• Oracle Optimized Warehouse Initiative
• Market

50
For More Information
http//search.oracle.com
BI Data Warehousing
or oracle.com
51
(No Transcript)