Transcript Slide 1

Oracle Database Performance: Latest Developments, What’s Next
Amit Ganesh
Vice President, Oracle Corporation
The following is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.
Agenda
• High-Performance Today
• Offloading and Caching for High
Performance
• High Performance with Large Data
Volumes
• End-to-end Performance Architecture
Quiz Question 1: Does Exadata utilize RAC
for scale-out or SMP for scale-up?
•
•
•
•
RAC
SMP
Both
None
<Insert Picture Here>
High Performance Today
Database Scaling
SMP Scale-Up
• Very mature
– 20 years of experience
• Many customers with largest SMPs on the
market
– 64 to 256 CPUs
– Sun M9000, HP Superdome, IBM Regatta
• Single System Image
– Easy to manage
– Easy to design applications
• Works great, but eventually hits a wall
• Need at least two for availability
RAC Scale-Out
HR
•
•
•
•
•
Runs all Oracle database applications
Highly available and scalable
No Idle Resources
Single System Image
Thousands of production customers
ERP
Leader in Industry Benchmarks
Benchmark
World Record
Leadership
TPC-C Price/Performance
Oracle
TPC-H @1000GB Non-Clustered
Oracle
TPC-H @ 10,000GB Non-Clustered
Oracle
TPC-H @ 30,000 GB
Oracle
SAP Sales and Distribution Parallel
Oracle
SAP Business Intelligence (BI-D)
Data Mart
Oracle
As of September 20, 2010: Source: www.tpc.org & www.sap.com/benchmark (SAP details on notes page): HP ProLiant ML350 G6, 290,040 tpmC, $.39/tpmC, 4.22 watts/KtpmC, available
8/16/10 (world record TPC-C price/performance). HP Integrity Superdome 2, 140,181 QphH@1000GB, $12.15/QphH@1000GB, available 10/20/10. HP Integrity Superdome server, 208,457.7
QphH@10000GB $27.97/QphH@10000GB, available 9/10/08 (world record TPC-H 10TB non-clustered). HP Integrity Superdome Server, 150,960 QphH@30000GB, $46.69/QphH@30000GB,
Best OLTP Price-Performance
Value Leadership Over Microsoft
0.49
Best Single Processor Result
0.50
0.39
0.45
0.40
$/tpmC
0.35
0.30
0.25
0.20
0.15
0.10
0.05
0.00
Oracle
SQL Server
As of September 20, 2010: HP ProLiant ML350 G6, 1 processor, 6 cores, 290,040 tpmC, $.39/tpmC, 4.22 watts/KtpmC, Oracle Database 11g Standard Edition One with OEL, available 8/16/10
(world record TPC-C price/performance). HP ProLiant DL580 G7, (4 processors, 32 cores) 1,807,347 tpmC, .49/tpmC, available 10/15/10
Source: Transaction Processing Performance Council (TPC), www.tpc.org
Best Scalability and Performance
World Record SAP SD-Parallel Benchmark
Near Perfect Scaling
50,000
40,000
4-Node RAC
Oracle Sun Fire
x4470
37,000
SD Users
40,000
2-Node RAC
Oracle Sun Fire
x4470
30,000
21,000
20,000
10,000
DB2 on P780
Oracle
Oracle
0
These results as of September 20, 2010: have been certified by SAP AG, www.sap.com/benchmark. Please see notes page for SAP benchmark certification details for the above
results.
Best Business Intelligence Performance
4-Node RAC
Fujitsu RX300
SAP BI-Data Mart Benchmark
1,165,742
Near Perfect Scaling
Query Navigation Steps/Hour
1,200,000
3-Node RAC
900,309
1,000,000
800,000
2-Node RAC
609,349
600,000
Single Node SMP
320,363
400,000
182,112
200,000
IBM DB2
Oracle
Oracle
Oracle
Oracle
8
8
16
24
32
0
# of CPU Cores
These results as of September 20, 2010: have been certified by SAP AG, www.sap.com/benchmark. Please see notes page for SAP benchmark certification details for the above
results.
Best Business Intelligence Performance
World Record SAP BI-Data Mart Benchmark
2-Node RAC
Fujitsu RX600 S5
Near Perfect Scaling
Query Navigation Steps/Hour
1800000
1,624,629
1600000
1400000
1200000
854,649
1000000
800000
600000
400000
182,112
200000
0
DB2
Oracle
Oracle
These results as of September 20, 2010: have been certified by SAP AG, www.sap.com/benchmark. Please see notes page for SAP benchmark certification details for the above
results.
What Oracle Runs
Storage
Example: Oracle Central e-Business DB
Texas
Colorado
Sun E25K 36 CPU
2 Cores/CPU
Total = 288 Cores
4 Node
RAC
Data Guard
76 TB Primary
76 TB Standby
• Worldwide Central E-business database for Fortune 200 company
• ERP, HR
– Payroll, orders, contracts, procurement, expense reports, hiring…
• Consolidated 70 separate Applications databases
– Estimated cost savings of over $1B
Oracle Beehive OLTP using Exadata
• Runs Oracle Email, Calendar,
Contacts, Chat, Documents, Web
Conferencing
Each of 3 Configurations:
16 Node RAC Cluster
2 quad-core Intel CPUs per Node
• 16-node production system
– Remote standby, testing system
• 1 PB of disk per system
– 50 SAS cells, 48 SATA cells
– 3 PB of total storage
Infiniband
17 Switches
• Complete Oracle Software Stack
– RAC, Streams, Active Data Guard,
Secure Backup, RMAN, Flashback
Database, ASM, Partitioning
– 2X space saved with compressed
SecureFiles
98 HP Exadata Storage Cells
1 PB Raw Storage
Offloading and Caching for
High Performance
Database, Client, Remote
<Insert Picture Here>
Quiz Question 2: What is the maximum total
server memory with Exadata
• < 10GB
• > 100GB, < 1 TB
• > 1TB
Server SQL Results Cache
• Database caches results of queries, sub-queries, or pl/sql
function calls
–
•
•
•
•
Cache is shared across statements and sessions on server
– Full consistency and proper semantics
2x speedup on hit for worst case of trivial query
100x speedup on hit for complex queries
Statement hints specify caching - /*+ result_cache +*/
Only for very read intensive tables
In-Memory Parallel Execution
• Database release 11.2 introduces parallel query
processing on memory cached data
– Queries run from tables in database buffer cache
– Harnesses memory capacity of entire database cluster for queries
• An affinity algorithm places fragments of a object
(partitions) in memory on different RAC nodes
• Data is kept compressed in memory
Memory has 100x more bandwidth than Disk
Database Smart Flash Cache
• Database Smart Flash Cache
transparently extends buffer
cache
Buffer Cache
Many
I/O’s
Enterprise Storage
Multiple Cabinets
Buffer Cache
Few
I/O’s
Database
Smart
Flash Cache •
– 10x Larger
– Uses flash disks or cards in
database host
– Cache eliminates most I/Os
– Available on Solaris and Oracle
Linux
Benefits
– Fewer disks needed
– Less powerful array needed
– Better response time
Mid-Range Storage
– Big jobs run faster
Few Shelves
– Lower Power
– High
ROI
Oracle is the First Flash
Optimized
Database
OCI Consistent Client Cache
Application Server
Database
Consistent
Caching
Simplest Queries can speedup:
• 50x in elapsed time
• 20x in CPU time
• Caches query results on client
• Primarily for caching small (10s or 100s of KB) read-intensive tables
–
–
Queries where network overhead dominates
e.g. lookup tables
• Cache is fully consistent
– Coherence messages bundled into responses to DB calls ensure
cache remain consistent
– Like Cache Fusion extended out to clients
Oracle TimesTen In-Memory Database Cache
Accelerates Oracle Database Applications
Telco Services
Financial Services
CRM, Portal,
SaaS,
Customer-facing
Applications
Real-Time
BAM & BI
Application
Application
In-Memory
Database
Cache
In-Memory
Database
Cache
Application
In-Memory
Database
Cache
• Runs in the middle-tier
• Caches subset of Oracle DB
• Full featured in-memory RDBMS
with standard SQL and PL/SQL
• Accelerates applications with
micro-second response time
• Scale up on SMP
• Scale out on commodity
hardware
• Supports read/write caching
• Automatic synchronization with
Oracle DB
• Built-in high availability
Order Matching Application
• Three different types of
transactions:
– Place market order
– Place limit order
– Process quote
• Business logic implemented in
PL/SQL stored procedures
• Application written in Java
• Execute 1000 times in one
thread
– Place an order
– Process a quote
PL/SQL
Executed on
Oracle DB
Trading
Application
Trading
Application
In-Memory
Database
Cache
PL/SQL
Executed in
IMDB Cache
Accelerate Order Matching Application
Lower Response Time and Higher Throughput
Lightning Fast Response Time
(run on Exalogic X2-2 server)
Average Response Time
TimesTen In-Memory Database
Microseconds
12
8
10
4
Millionths of
a second
2.5
Millionths of
a second
0
Read Transaction
Oracle TimesTen In-Memory Database 11g
Update Transaction
Intel Xeon 5670, 2 CPUs, 6 cores per CPU
Solaris 10
TimesTen 11g – Read Throughput Scaling
Scale Up on Multi-Processor / Multi-Core Hardware
3,500,000
TimesTen 11g - Read Throughput
3,112,020
Transactions Per Second
3,000,000
2,429,709
2,500,000
2,000,000
1,258,811
1,500,000
1,000,000
396,816
500,000
0
1
4
8
Concurrent Processes
Oracle TimesTen In-Memory Database 11g
12
Intel Xeon 5670, 2 CPUs, 6 cores per CPU
Solaris 10
TimesTen 11g – Write Throughput Scaling
Scale Up on Multi-Processor / Multi-Core Hardware
500,000
TimesTen 11g - Update Throughput
449,772
Transactions Per Second
450,000
400,000
338,511
350,000
270,369
300,000
250,000
200,000
150,000
98,106
100,000
50,000
0
1
4
8
12
Concurrent Processes
Oracle TimesTen In-Memory Database 11g
Intel Xeon 5670, 2 CPUs, 6 cores per CPU
Solaris 10
Active Data Guard Query Offload
• Users want to performance protect their production DBs
– Active Data Guard offloads high risk reporting & backup from OLTP
• Current approaches – Physical Copy Reporting DB (e.g. split mirror)
• Solution is simple but data is stale (day old)
– Logical Replica Reporting DB (e.g. replication)
• Replication provides real-time updates but is complex
Simple
• Active Data Guard enables a unique real-time solution
– Reporting using physical standby technology
– Real-time, simple, and fast – also provides DR
Continuous Redo
Shipment and Apply
Production
Database
RealTime
Concurrent
Real-time
Query
Queries
Reporting
Database
Web Scale Highly Available Reader Farm
Reporting, web content browsing
• Reader farm implemented
using Active Data Guard
–
–
–
–
Reader
Databases
Updates
Scale-out read queries
Isolate faults to each DB
High performance
Supports all types & DDL
• Automatic, zero loss failover
Redo Shipping
Redo Shipping
Primary
Database
Designated Fast-Start
Failover DB
– Readers follow automatically
• RAC can scale-out updater,
or centralize storage of
readers
High-Performance with
Large Data Volumes
<Insert Picture Here>
Data Growth Challenges
• IT must support exponentially
growing amounts of data
– With improved performance
– With lower cost
• Powerful and efficient
compression is key
Advanced OLTP Compression
• Compress large application tables
– Transaction processing, data warehousing
– Transparent to application
• Compress all data types
– Structured and unstructured data types
• Improve query performance
– Cascade storage savings throughout data center
Up To
4X
Compression
Real World Compression Results
- ERP Database 10 Largest Tables
Storage Utilization
Table Scan Performance
2500
0.4
2.5x Faster
2000
3x Smaller
0.3
1500
0.2
1000
0.1
500
DML Performance
0
40
30
20
10
0
Less than
3% Overhead
0
Exadata Hybrid Columnar Compression
Two Modes
Warehouse Compression
• 10x average storage savings
• 10x reduction in Scan IO
Archive Compression
• 15x average storage savings
– Up to 50x on some data
• Some access overhead
• For cold or historical data
Optimized for Speed
Optimized for Space
Smaller Warehouse
Faster Performance
Reclaim 93% of Disks
Keep Data Online
Can mix OLTP and Hybrid Columnar Compression by partition for ILM
Real-World Compression Ratios
Oracle Production E-Business Suite Tables
Size Reduction Factor by Table
52
50
45
40
35
30
25
20
15
10
5
0
OLTP Compression (avg=3.3)
43
Query Compression (avg=14.6)
Archive Compression (avg=22.6)
10
10
10
11
29
16
19
19
19
20
21
• Columnar compression ratios
• Query = 14.6X
• Archive = 22.6X
• Vary by application and table
© 2009 Oracle Corporationl
35
Files in the Database Reinvented
• Best of Both Worlds
• File Capabilities
–
–
–
–
–
–
File System Interface
High Performance
Compression
Encryption
Deduplication
HSM
• Database Capabilities
– Transactions
– Query Consistency
– Advanced Backup
and Recovery
– Powerful Security
– Flashback
– Scale up SMPs
– Scale out Clusters
• Files are an integral part of modern database applications
– Product images, contracts, XML, ETL files, manuals, etc.
• Application developers want to store business data files in
the database to benefit from transactional consistency,
and unify HA and Security
– Poor performance, limited functionality, and lack of access by
existing file based tools have held them back
• Oracle Database 11g reinvents files in the database
• SecureFiles provides super fast and powerful file storage
– Removes performance barrier to storing files in the database
• DBFS provides a file system interface to files in the DB
– Enables existing file based tools to easily access DB files
SecureFiles Performance
• Performance compared to Linux FS
– Tests run using both SecureFiles and ext3 in metadata
journaling only, no network
File Reads
File Writes
(MB/second)
(MB/second)
SecureFiles
100
80
60
40
20
0
Linux
Files
LOBs
0.01
0.10
1
File Size
(MB)
10
100
60
50
40
30
20
10
0
SecureFiles
Linux Files
LOBs
0.01
0.10
1
10
File Size (MB)
100
Database File System - DBFS
• Shared Linux file system
– Shared storage for ETL staging, scripts, reports and other
application files
• Files stored as SecureFiles in database tables
– Protected like any DB data – mirroring, DataGuard, Flashback, etc.
• 5 to 7 GB/sec file system I/O throughput on Database Machine
• Example use case:
Load into database
using External Tables
ETL Files in DBFS
ETL
More File Throughput than High-End NAS Filer
End-to-end Performance
Architecture
Exadata Hardware Architecture
Scaleable Grid of industry standard servers for Compute and
Storage
• Eliminates long-standing tradeoff between Scalability, Availability,
Cost
Database Grid
• 8 compute servers
(1U)
• 64 Intel cores
InfiniBand Network
Storage Grid
• 14 storage servers (2U)
• 112 Intel cores in storage
• 100 TB SAS disk, or
336 TB SATA disk
• Redundant 40Gb/s switches
• 5 TB PCI Flash
• Unified server & storage net
• Data mirrored across
storage servers
© 2010 Oracle Corporation
40
Scales to 8 Racks by Just Adding Cables
Full Bandwidth and Redundancy
© 2010 Oracle Corporation
41
Exadata Flash Warehousing
Fastest Query Throughput
50 GB/sec!
Query Throughput
Flash
GB/sec Uncompressed Data
Single Rack
• 50 TB of data fits in Flash
– Using 10x Query Compression
Disk
• Easily keep recent data in flash,
older data on disk
Teradata
Netezza
Exadata
2580
TwinFin 12
V2
Business answers in seconds, not hours
© 2010 Oracle Corporation
42
Quiz Question 3: Can Exadata help scan
even more than 50 GB/s per rack
• Yes
• No
Exadata Flash Warehousing
Comparison to Storage Arrays
Storage Data Bandwidth
Flash
• Storage Arrays bottleneck on
back-end connectivity and
controller performance
50 GB/sec!
(Uncompressed GB/sec)
– Flash provides no bandwidth
increase
Disk
• Exadata is fastest
– and scales with more racks
• Arrays don’t scale and:
–
–
–
–
Exadata
No CPU offload
No Columnar Compression
No InfiniBand
Expensive
V2
Multiple Racks
© 2010 Oracle Corporation
1 Rack
44
High Performance Backup with Exadata
• Backup runs at 7 TB/hr
• Both to tape and disk
• Less than 10% of Server
CPU utilized during
backup
• OLTP Compression triples
the effective backup rate
• EHCC gets an effective rate
of 70 TB/hr
Load Performance
• Reporting queries continue to run uninterrupted during
loading data into the database
– This is accomplished using Oracle’s unique Multi Version Read
consistency Transaction model.
– Oracle even has customers already doing “continuous loads”
24x7 while reporting queries are running in parallel
• Data loaded into Exadata as tables
– By loading from external files into tables in the database
– Loads into tables on Exadata runs over 5 TB/hour
• Data loaded into Exadata into external tables on DBFS
– By loading ETL flat files into DBFS using ftp/scp
– Data can be immediately queried using SQL on external tables
– Loads into DBFS on Exadata runs over 90 TB/hour
Oracle is Ready for the Future
• High-Performance Today
• Offloading and Caching for High
Performance
• High Performance with Large Data
Volumes
• End-to-end Performance Architecture