Extreme Performance

1
Extreme Performance

• Thomas Kyte
• http//asktom.oracle.com/

2
The Beginning...

• Data Model with Structure
• Data Independent of Code
• Set-oriented
• 1977 the work begins

A Relational Model forLarge Shared Databanks
E.F. Codd - 1970
3
GPS 1978
4
GPS 1978
5
First RDBMS Version 2 June 1979

• FIRST Commercial SQL RDBMS
• Impressive First SQL
• Joins, Subqueries
• Outer Joins, Connect By
• A Simple Server
• No transactions, Limited Reliability
• Portability from the Start
• Written in Fortran
• But multi-platform PDP11, Dec VAX

6
IBM PC 1981
IBM model number 5150, introduced on August 12,
1981.
7
Internet (as we know it) 1983
The first TCP/IP-based wide-area network was
operational by January 1, 1983 when all hosts on
the ARPANET were switched over from the older NCP
protocols.
8
Portability Version 3 March 1983

• New Implementation Designed for Portability
• Written in C
• Single Source
• Architectural Changes
• Transactions, multi-versioning, no read
  consistency
• AI/BI files
• Oracle Corporation name established

9
(No Transcript)
10
Cooperative Server Version 5 April 1985

• My First Oracle Experience
• 1st Client/Server
• Cooperative Server
• Distributed Processing
• Parallel Server
• Portability
• V5 was first to go beyond 640K memory on PCs
• Single-user for Macintosh o/s
• SQL_TRACE
• select trace('sql',1),1 from dual

11
(No Transcript)
12
Transaction Processing Version 6 July 1988

• New Architecture
• Performance (first SMP)
• Availability
• TPO
• PL/SQL
• V6 Lays Architectural Groundwork for the Future
• This was a rewrite of the entire database
  fundamentally

13
World Wide Web 1990ish
The World Wide Web was created in 1989 by British
scientist Tim Berners-Lee, working at the
European Organization for Nuclear Research (CERN)
in Geneva, Switzerland, and released in 1992.
14
Oracle7.3 February 1996

• Partitioned Views
• Bitmapped Indexes
• Asynchronous read ahead for table scans
• Standby Database
• Deferred transaction recovery on instance startup
• Updatable Join View
• SQLDBA no longer shipped.
• Index rebuilds
• DBV introduced
• Context Option
• PL/SQL - UTL_FILE

• Spatial Data Option
• Tablespaces changes - Coalesce, Temporary
  Permanent,
• Trigger compilation, debug
• Unlimited extents on STORAGE clause.
• Some init.ora parameters modifiable -
  TIMED_STATISTICS
• HASH Joins, Antijoins
• Histograms
• Oracle Trace
• Advanced Replication Object Groups

15
(No Transcript)
16
(No Transcript)
17
(No Transcript)
18
Data Warehouses Growing RapidlyTripling In Size
Every Two Years
Size of the Largest Data Warehouses
Source Winter TopTen Survey, Winter Corporation,
Waltham MA, 2008.
19
Extreme Performance
20
The Performance ChallengeStorage Data Bandwidth
Bottleneck

• Current warehouse deployments often have
  bottlenecks limiting the movement of data from
  disks to servers
• Storage Array internal bottlenecks on processors
  and Fibre Channel Loops
• Limited Fibre Channel host bus adapters in
  servers
• Under configured and complex SANs
• Pipes between disks and servers are 10x to 100x
  too slow for data size

21
Data Warehouses Start Slowdown at 1TB
22
Solutions To Data Bandwidth Bottleneck

• Add more pipes Massively parallel
  architecture
• Make the pipes wider 5X faster than
  conventional storage
• Ship less data through the pipes Process data
  in storage

23
What We Announced at OpenWorld
Extreme Performance Unlimited Scalability
Enterprise Ready
24
HP Oracle Database MachineThe next step in DW
Hardware Solutions
Custom
Optimized Warehouse
Reference Configurations
HP Oracle Database Machine

• Complete Flexibility
• Any OS, any platform
• Easy fit into a companys IT standards

• Documented best-practice configurations for data
  warehousing

• Scalable systems pre-installed and
  pre-configured ready to run out-of-the-box

• Highest performance
• Pre-installed and pre-configured
• Sold by Oracle

25
Products Announced

• HP Exadata Storage Server Hardware
• Paired with Oracle Exadata Storage Server
  Software
• Delivers database intelligence in storage tier
• Supported for Oracle Enterprise and Red Hat
  servers running Oracle Database 11g Enterprise
  Edition
• HP Oracle Database Machine
• Simplicity of appliance seamlessly integrated
  with the database
• Eliminates all bottlenecks preventing high
  performance data scans
• Includes Exadata Storage Server

Dramatic performance improvement for data
warehouses
Linux 5.1 releases with appropriate Infiniband
drivers, Oracle Database 11g Enterprise Edition
vers. 11.1.0.7
26
FirstHP Oracle Database Machine
27
The HP Oracle Database MachineSales / Support
Model

• System Delivery
• Hardware Service

• System Sales
• System Support

Oracle Technology Sales Manager is the single
point for sales Oracle is single point for Support
28
SecondHP Exadata Storage Server Hardware

• 2 Intel processors, 8 cores
• 12 disk drives, up to 12 TB raw storage
• 2 Infiniband connections
• Oracle Enterprise Linux OS

29
Oracle Exadata Storage Server Software Reduces
Data Going through the Pipes

• Intelligent storage server
• Unique smart scan technology
• Returns query result set
• Not disk blocks

30
Traditional Scan Processing
? SELECT customer_name FROM calls WHERE amount gt
200

• With traditional storage, all database
  intelligence resides in the database hosts
• Very large percentage of data returned from
  storage is discarded by database servers
• Discarded data consumes valuable resources, and
  impacts the performance of other workloads

? Rows Returned
? DB Host reduces terabyte of data to 1000
customer names that are returned to client
? Table Extents Identified
? I/Os Executed 1 terabyte of data returned to
hosts
? I/Os Issued
31
Exadata Smart Scan Processing
? SELECT customer_name FROM calls WHERE amount gt
200

• Only the relevant columns
• customer_name
• and required rows
• where amountgt200
• are are returned to hosts
• CPU consumed by predicate evaluation is offloaded
• Moving scan processing off the database host
  frees host CPU cycles and eliminates massive
  amounts of unproductive messaging
• Returns the needle, not the entire hay stack

? Rows Returned
? Consolidated Result Set Built From All Cells
? Smart Scan Constructed And Sent To Cells
? Smart Scan identifies rows and columns within
terabyte table that match request
? 2MB of data returned to server
32
Additional Smart Scan Functionality

• Join filtering
• Star join filtering is performed within Exadata
  storage cells
• Dimension table predicates are transformed into
  filters that are applied to scan of fact table
• Backups
• I/O for incremental backups is much more
  efficient since only changed blocks are returned
• Create Tablespace (file creation)
• Formatting of tablespace extents eliminates the
  I/O associated with the creation and writing of
  tablespace blocks

33
Smart Scan Transparency

• Smart scans are transparent to the application
• No application or SQL changes required
• Returned data is fully consistent and
  transactional
• If a cell dies during a smart scan, the
  uncompleted portions of the smart scan are
  transparently routed to another cell
• Smart Scans correctly handle complex cases
  including
• Uncommitted data and locked rows
• Chained rows
• Compressed tables
• National Language Processing
• Date arithmetic
• Regular expression searches
• Partitioned tables

High Throughput, Reduced Overhead, No Complex
Tuning
34
Massively Parallel Storage Grid

• Exadata Storage servers are organized into a
  massively parallel storage grid
• Scalable
• Scales to hundreds of storage servers
• Data automatically distributed across storage
  servers by ASM
• Transparently redistributed when storage servers
  are added or removed
• Data bandwidth scales linearly with capacity
• Available
• Data is mirrored across storage servers
• Failure of disk or storage server transparently
  tolerated
• Simple
• Works transparently - no application changes

Exadata bandwidth scales linearly with capacity
35
Exadata Performance Scales
10 Hour

• Exadata delivers brawny hardware for use by
  Oracles brainy software
• Performance scales with size
• Result
• More business insight
• Better decisions
• Improved competitiveness

Table Scan Time
Typical Warehouse
5 Hour
1 Hour
Exadata
Table Size
1TB
10 TB
100TB
36
Exadata Co-Existence and Migration

• Databases can be concurrently deployed on Exadata
  and traditional storage
• Tablespaces can exist on Exadata storage,
  traditional storage, or a combination of the two,
  and is transparent to database applications
• SQL offload processing requires all pieces of a
  tablespace reside on Exadata
• Online migration if currently using ASM and ASM
  redundancy
• Migration can be done using RMAN or Data Guard

37
M-Tel Exadata Speedup 10X to 72X Performance
Improvement
28x Average Speedup
38
Plamen Zyumbyulev Head of Database
Administration M-Tel
Every query was faster on Exadata compared to
our current systems. The smallest performance
improvement was 10x and the biggest one was an
incredible 72x.
39
Grant Salmon Chief Executive Officer LGR
Telecommunications
Call Data Record queries that used to run for
over 30 minutes now complete in under 1 minute.
That's extreme performance.
40
Giant Eagle Exadata Speedup 3X to 48X
Performance Improvement
16x Average Speedup
41
Walt Litzenberger Director Enterprise Database
Systems The CME Group
Oracle Exadata outperforms anything weve tested
to date by 10 to 15 times. This product flat-out
screams.
42
Where Can You Try It?

• North America Enterprise Technology Centers (ETC)
• Atlanta, GA
• Two HP Oracle Database Machines, each with 8
  database server nodes and 14 HP Exadata Storage
  Server cells holding 12 drives of 1 TB in size
  each
• Capacity of about 46 TB of uncompressed data /
  rack
• Reston, VA
• Three HP Oracle Database Machines, each with 8
  database server nodes and 14 HP Exadata Storage
  Server cells holding 12 drives of 300 GB in size
  each
• Capacity of about 14 TB of uncompressed data /
  rack

A queue has already formed!
43
Exadata Benefits

• Extreme Performance
• 10X and more speedup for data warehousing
• Database Aware Storage
• Smart Scans
• Massively Parallel Architecture
• Dynamically Scalable to hundreds of cells
• Linear Scaling of Data Bandwidth
• Transaction/Job level Quality of Service
• Mission Critical Availability and Protection
• Disaster recovery, backup, point-in-time
  recovery, data validation, encryption

44
Resources

• Oracle.comhttp//www.oracle.com/exadata
• Oracle Exadata Technology Portal on OTN
  http//www.oracle.com/technology/products/bi/db/e
  xadata
• Oracle Exadata white papers http//www.oracle.co
  m/technology/products/bi/db/exadata/pdf/exadata-te
  chnical-whitepaper.pdf
• http//www.oracle.com/technology/products/bi/db/e
  xadata/pdf/migration-to-exadata-whitepaper.pdf

45
(No Transcript)