IMCSummit 2015 - Day 1 IT Business Track - Oracle Database In-Memory: The Next Big Thing

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Oracle Database In-Memory
The Next Big Thing
Maria Colgan
Master Product Manager
#DBIM12c

Why is Oracle do this

Accelerate Mixed
Workload OLTP
Real Time
Analytics
No Changes to
Applications
Trivial to
Implement
Oracle Database In-Memory Goals
100x
4

What is an analytic query?
Which products
give us our highest
margins?
Who are the top 10
sales reps in the north
west region this
month?
If I get a 20% discount
on widget A, how
much will our margins
improve?

What is an analytic query?
• Scans a large amount of data
• Selects a subset of columns from wide tables
• Uses filter predicates in the form of =, >, <, between, in-list
• Uses selective join predicates that reduce the amount of data returned
• Contain complex calculations or aggregations

Row Format Databases vs. Column Format Databases
Rows Stored
Contiguously
 Transactions run faster on row format
– Example: Query or Insert a sales order
– Fast processing few rows, many columns
Columns
Stored
Contiguously
 Analytics run faster on column format
– Example : Report on sales totals by region
– Fast accessing few columns, many rows
SALES
SALES
7

OLTP Example
8
Row
 Query a single sales order in row format
– One contiguous row accessed = FAST
Column
 Query a sales order in Column Format
– Many column accessed = S L O W
SALES
StoresSALES
Query
Query
Query
Query
Query
Until Now Must Choose One Format and Suffer Tradeoffs

What is it

Breakthrough: Dual Format Database
• BOTH row and column
formats for same table
• Simultaneously active and
transactionally consistent
• Analytics & reporting use new
in-memory Column format
• OLTP uses proven row format
10
Normal
Buffer Cache
New In-Memory
Format
SALES SALES
Row
Format
Column
Format
SALES

How Does it work

Oracle In-Memory Columnar Technology
• Pure in-memory column format
• Not persistent, and no logging
• Quick to change data: fast OLTP
• Enabled at table or partition
• Only active data in-memory
• 2x to 20x compression typical
• Available on all hardware
platforms
12
SALES
Pure In-Memory Columnar

Early User - Schneider Electric
• Global Specialist in Energy Management™
• 25 billion € revenue
• 160,000+ employees in 100+ countries
13

0
5
10
15
20
Query Low Query High Capacity
Low
Capacity
High
Schneider General Ledger Compression
Factors
8.6x
Schneider In-Memory Compression
13x 16x 19x
• Over 2 billion
General
Ledger Entries
• 900 GB on
disk
Default
14

Why is an In-Memory scan faster than the buffer cache?
SELECT COL4 FROM MYTABLE;
15
X
X
X
X
X
RESULT
Row Format
Buffer Cache

Why is an In-Memory scan faster than the buffer cache?
SELECT COL4 FROM MYTABLE;
16
RESULT
Column Format
IM Column Store
RESULT
X
X
X
X

Oracle In-Memory Column Store Storage Index
• Each column is the made up
of multiple column units
• Min / max value is recorded
for each column unit in a
storage index
• Storage index provides
partition pruning like
performance for ALL queries
17
Memory
SALES
Column
Format
Min 1
Max 3
Min 4
Max 7
Min 8
Max 12
Min 7
Max 15
Example: Find all sales from stores with a store_id of 8
?

Orders of Magnitude Faster Analytic Data Scans
• Each CPU core scans local
in-memory columns
• Scans use super fast SIMD
vector instructions
• Originally designed for
graphics & science
• Billions of rows/sec scan
rate per CPU core
• Row format is millions/sec
18
VectorRegister
Load
multiple
region
values
Vector
Compare
all values
an 1 cycle
CPU
Memory
REGION
CA
CA
CA
CA
Example:
Find sales in
region of CAlifornia
> 100x Faster

Joining and Combining Data Also Dramatically Faster
• Converts joins of data in
multiple tables into fast
column scans
• Joins tables 10x faster
19
Example: Find total sales in outlet stores
SalesStores
StoreID
StoreID in
15, 38, 64
Type=‘Outlet’
Type
Sum
StoreID
Amount

Generates Reports Instantly
• Dynamically creates in-memory
report outline
• Then report outline filled-in
during fast fact scan
• Reports run much faster
• Without predefined cubes
• Also offloads report filtering to
Exadata Storage servers
20
Example: Report sales of footwear in outlet stores
Sales
Stores
Products
In-Memory
Report Outline
Footwear
Outlets
$
$$
$
$$$
Footwear
Sales
Outlets

0
20
40
60
80
100 Seconds per Query
Schneider Speedup Across 1545 Queries
7x to 128x faster
Million rows returned by query
2000M 300M 30M 5M 0.5M
Buffer Cache
IN-MEMORY
• 2 billion General
Ledger Entries
• 1545 queries
– Currently take 34
hours to complete
– Combination of filter
queries, aggregations
and summations
21

0
200
400
600
800
1000
Schneider Speedup vs. Disk
From 62x to 3259x faster
Million rows returned
2000M 300M 30M 5M 0.5M
Disk
IN-MEMORY
Seconds per Query
22

How does it impact
OLTP environments

Complex OLTP is Slowed by Analytic Indexes
• Most Indexes in complex OLTP
(e.g. ERP) databases are only used
for analytic queries
• Inserting one row into a table
requires updating 10-20 analytic
indexes: Slow!
• Indexes only speed up predictable
queries & reports
Table
1 – 3
OLTP
Indexes
10 – 20
Analytic
Indexes
24

OLTP is Slowed Down by Analytic Indexes
Insert rate decreases as
number of indexes
increases
# of Fully Cached Indexes (Disk Indexes are much slower)
25

Column Store Replaces Analytic Indexes
• Fast analytics on any columns
• Better for unpredictable analytics
• Less tuning & administration
• Column Store not persistent so
update cost is much lower
• OLTP & batch run faster
Table
1 – 3
OLTP
Indexes
In-Memory
Column Store
26

Schneider Update Transactions Speedup
From 5x to 9x faster
-
500
1 000
1 500
2 000
2 500
3 000
3 500
4 000
0
5
10
15
20
25
30
35
40
45
50
55
60
65
70
75
Millionsoftransactionperday
Millions of records in the target table
No Index
No Index + IM
Full Index
All Indexes
Primary Index Only
Primary Index Plus In-Memory Columns
• Data – Sales Accounts
• Main table has
1 Primary Key +
21 secondary indexes
• Test - 303 million
transactions
– Currently takes 21
hours
27

Schneider Storage Reduction
Over 70% reduction in storage usage due to analytic index removal
0
100
200
300
400
Objects Redo
Size in GBs
SECONDARY
INDEXES
TABLES &
PK INDEXES
28

How can I scale this
solution

Scale-Out In-Memory Database to Any Size
• Scale-Out across servers to grow
memory and CPUs
• In-Memory queries parallelized
across servers to access local
column data
• Direct-to-wire InfiniBand
protocol speeds messaging on
Engineered Systems
30

In-Memory Speed + Capacity of Low Cost Disk
DISK
PCI FLASH
DRAM
Cold Data
Hottest Data
Active Data
• Size not limited by memory
• Data transparently accessed
across tiers
• Each tier has specialized
algorithms & compression
• Simultaneously Achieve:
• Speed of DRAM
• I/Os of Flash
• Cost of Disk
31

Scale-Up for Maximum In-Memory Performance
M6-32
Big Memory Machine
32 TB DRAM
32 Socket
3 Terabyte/sec Bandwidth
• Scale-Up on large SMPs
• Algorithms NUMA optimized
• SMP scaling removes
overhead of distributing
queries across servers
• Memory interconnect far
faster than any network
32

Oracle In-Memory: Industrial Strength Availability
RAC
ASM
RMAN
Data Guard &
GoldenGate • Pure In-Memory format does not
change Oracle’s storage format,
logging, backup, recovery, etc.
• All Oracle’s proven availability
technologies work transparently
• Protection from all failures
• Node, site, corruption, human error,
etc.
33

Oracle Database In-Memory: Unique Fault Tolerance
• Similar to storage mirroring
• Duplicate in-memory columns
on another node
• Enabled per table/partition
• E.g. only recent data
• Application transparent
• Downtime eliminated by
using duplicate after failure
34
Only Available on Engineered Systems

How easy is it to get
started

Oracle In-Memory: Simple to Implement
1. Configure Memory Capacity
• inmemory_size = XXX GB
2. Configure tables or partitions to be in memory
• alter table | partition … inmemory;
3. Later drop analytic indexes to speed up OLTP
36

“In terms of how easy the in-memory
option was to use, it was actually
almost boring. It just worked –
just turn it on, select the tables,
nothing else to do.”
– Mark Rittman
Chief Technical Officer
Rittman Mead
37

Oracle In-Memory Requires Zero Application Changes
Full Functionality - ZERO restrictions on SQL
Easy to Implement - No migration of data
Fully Compatible - All existing applications run unchanged
Fully Multitenant - Oracle In-Memory is Cloud Ready
Uniquely Achieves All In-Memory Benefits With No Application Changes
38

“Oracle Database In-Memory
made our slowest financial
queries faster out-of-the box;
then we dropped indexes and
things just got faster.”
– Evan Goldberg
Co-Founder, Chairman, CTO
NetSuite Inc.
39

“We see clear benefit from the
Oracle In Memory for our users.
Our existing applications were
transparently able to take
advantage of them and no
application code changes were
required”
– Scott VanValkenburgh, SAS
40

Oracle Applications In-Memory Examples
Oracle Application Module Improvement Elapsed Time
In-Memory Cost Management 1003x Faster 58 hours to 3.5 mins
In-Memory - Financial Analyzer 1,354x Faster 4.3 hours to 11 seconds
In-Memory Sales Order Analysis 1,762x Faster 22.5 minutes to < 1 sec
Subledger Period Close
Exceptions
200x Faster 600 seconds to 3 secs
Call Center Ad-hoc query pattern 1247x Faster 129 seconds to < 1 secs
41

What’s the catch

Getting The Most From In-Memory
• Fast cars speed up travel, not meetings
• In-Memory speeds up analytic data access, not:
– Network round trips, logon/logoff
– Parsing, PL/SQL, complex functions
– Data processing (as opposed to access)
• Complex joins or aggregations where not much data is filtered before processing
– Load and select once – Staging tables, ETL, temp tables
Understand Where it Helps
43
Know your bottleneck!

Getting The Most From In-Memory
• Avoid stop and go traffic
– Process data in sets of rows in the Database
– Not one row at a time in the application
• Plan ahead, take shortest route
– Help the optimizer help you: Gather representative set of statistics using DBMS_STATS
• Use all your cylinders
– Enable parallel execution
– In-Memory removes storage bottlenecks allowing more parallelism
The Driver Matters
44

In-Memory Use Cases
OLTP
• Real-time reporting directly on
OLTP source data
• Removes need for separate ODS
• Speeds data extraction
Data Warehouse
• Staging/ETL/Temp not a candidate
• Write once, read once
• All or a subset of Foundation Layer
• For time sensitive analytics
• Potential to replace Access Layer
45

Where can I get
more information

Join the Conversation
47
https://twitter.com/db_inmemory
https://blogs.oracle.com/in-memory/
Related White Papers
• Oracle Database In-Memory White Paper
• Oracle Database In-Memory Aggregation Paper
• When to use Oracle Database In-Memory
• Oracle Database In-Memory Advisor
Related Database In-Memory Free Webcasts
• Oracle Database In-Memory meets Data Warehousing
Related Videos
• In-Memory YouTube Channel
• Database Industry Experts Discuss Oracle Database
In-Memory (11:10)
• Software on Silicon
Any Additional Questions
• Oracle Database In-Memory Blog
• My email: maria.colgan@oracle.com
https://www.facebook.com/OracleDatabase
http://www.oracle.com/goto/dbim.html
Additional
Resources

IMCSummit 2015 - Day 1 IT Business Track - Oracle Database In-Memory: The Next Big Thing

IMCSummit 2015 - Day 1 IT Business Track - Oracle Database In-Memory: The Next Big Thing

Recommended

Recommended

More Related Content

More from In-Memory Computing Summit

More from In-Memory Computing Summit (20)

Recently uploaded

Recently uploaded (20)

IMCSummit 2015 - Day 1 IT Business Track - Oracle Database In-Memory: The Next Big Thing