Application-Tier In-Memory Analytics Best Practices and Use Cases

Application-Tier In-Memory Analytics

Best Practices and Use Cases

Susan Cheung

Vice President Product Management Vice President Product Management Oracle, Server Technologies

Oct 01, 2014 Guest Speaker: Kiran Tailor

Senior Oracle DBA and Architect

Safe Harbor Statement

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 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.

Program Agenda

TimesTen In-Memory Database Overview

TimesTen for In-Memory Analytics

Common Use Cases and Best Practices

1 2

3

Common Use Cases and Best Practices

Real-life Customer Use Case at CIMA

Product roadmap

Q&A

Oracle TimesTen In-Memory Database

Microseconds Response Time in Application Tier

•

Persistent and Recoverable

– Database and Transaction logs

persisted on disk and flash storage

•

Relational Database

–

Pure in-memory

–

ACID compliant

–

Standard SQL

•

Extremely Fast

–Microseconds response time –Very high throughput

Standard SQL

•

Compatible with Oracle Database

– Data types, PL/SQL, OCI, ODP.NET, PHP, R – Integrated with RAC, Data Guard,

TimesTen In-Memory Database

Persistence and Recovery

•

TimesTen database persistence can be

configured on

–Flash, SSD, hard disk storage (HDD)

•

All transactions are logged and persisted

–Redo, undo, and recovery –Redo, undo, and recovery

•

Dual database Checkpoint files

–Database restart

–Database recovery after failures

•

Separate storage for Transaction Logs and

Checkpoint files if using HDD

TimesTen Checkpoint Files TimesTen Transaction Log Files

Application Development with TimesTen

J2EE App Servers OR Mapping Java/JDBC

Oracle R

TimesTen In-Memory Database

Low Latency - Microseconds Response Time

Millionths of a Second

9.07

ResponseTime

TPTBM Read and Update E7-4890 v2 @ 2.80GHz 1 socket, 15 cores/socket, 2 threads/core TimesTen 11.2.2.7.5 (100M rows, 17GB)

3.58

SELECT Query UPDATE Transaction

M ic ro se co nd s

25,192,607 15,000,000 20,000,000 25,000,000 30,000,000 Q ue ri es Pe rS ec on d

TimesTen In-Memory Database

Read Scalability – 25 Million Queries per Second 25 Million Queries per Second 279,262 0 5,000,000 10,000,000 15,000,000 1 10 20 30 60 90 120 Q ue ri es Pe rS ec on d ConcurrentProcesses TPTBM 100% Read E7-4890 v2 @ 2.80GHz 4 sockets, 15 cores/socket, 2 threads/core TimesTen 11.2.2.7.5 (100M rows, 17GB)

800,000 1,000,000 1,200,000 1,400,000 U pd at e Tr an sa ct io ns Pe rS ec on d

Update Throughput Per Processor

TimesTen In-Memory Database

1.3 Million Update Transactions Per Second Per Processor

1.3 Million Update TPS 0 200,000 400,000 600,000 800,000 1 5 10 15 20 25 U pd at e Tr an sa ct io ns Pe rS ec on d

Concurrent Update Processes TPTBM 100% Update E7-4890 v2 @ 2.80GHz 1 socket, 15 cores/socket, 2 threads/core TimesTen 11.2.2.7.5 (100M rows, 17GB)

TimesTen In-Memory Database

3.4 Million Transactions Per Second Per Processor

2,000,000 2,500,000 3,000,000 3,500,000 Tr an sa ct io ns Pe rS ec on d

Mixed Workload Throughput Per Processor

3.4 Million Transactions Per Second TPTBM 100% Mixed Workload (80-10-5-5) E7-4890 v2 @ 2.80GHz 1 socket, 15 cores/socket, 2 threads/core TimesTen 11.2.2.7.5 (100M rows, 17GB) 0 500,000 1,000,000 1,500,000 2,000,000 1 5 10 15 20 25 Tr an sa ct io ns Pe rS ec on d Concurrent Processes

TimesTen 6 TimesTen 11g TimesTen 11g 11.2.2.x

Oracle TimesTen – Pure In-Memory Relational Database

Very Fast Response Time for Very High Throughput in Application Tier

1996 | 2005 2006 | 2008 2009 | 2011 2012 | 2013 2014 TimesTen 6 TimesTen 7 Pre-Oracle acquisition TimesTen 11g 11.2.1 TimesTen 11g 11.2.2 • 1998 First commercial In-Memory RDBMS • HA Replication • Online Upgrades • Application-tier Cache

for Oracle Database

• PL/SQL and OCI Support • Oracle Clusterware Integration • Cache Grid for Scale Out • ODP .NET Support

• BLOB, CLOB, NCLOB data types

• Parallel Replication • In-Memory Analytics • Columnar Compression • Index Advisor

• Oracle R Support • In-Memory Star Join

• Oracle Golden Gate Integration

11.2.2.x Enhancements

• Parallel data import from Oracle Database

• Parallel database restart

• Highly concurrent range indexes

• Oracle RAC integration • National Language Support • Oracle Data Types support • SQL Developer Integration • Enterprise Manager integration

Analytics Use Cases for

Oracle TimesTen

In-Memory Analytics -

Summary Aggregates Scenarios

• Aggregations are suitable for analysis of higher-level grains of FACT data • Knowledge of query patterns required

• Aggregated tables and indexes typically much smaller than detail-tables

• Reports using summary aggregates typically provide exceptional response time (sub-second)

Reports Using Aggregate Set 1 Reports Using Aggregate Set 1

Reports Using Aggregate Set 2

Summary Aggregate Tables and Indexes stored in TimesTen

1 2

OLTP

Operational Data Store

In-Memory Analytics - Data Mart Use Cases

•

Data mart and operational data stores

–

For analysis where summary aggregates are not sufficient

–

Require access to detail source tables (fact and dimension tables)

•

Consider “hot” set of data*

Subset of Data from a Data Warehouse

–

Data volume constrained by availability of RAM in the system

–

May use compression to include more data

•

Common use cases have both aggregations and detail tables

In-Memory Analytics

ODS and Data Mart Detail Tables Scenarios

Operational Reports Operational Data Store

Reports with Aggregates AND Detail Tables

Aggregates + Detail tables + indexes

OLTP

Best Practices

Tips and Tricks

TimesTen In-Memory Database

Storage Speed Matters to Database Restart Time

•

Storage speed matters

•

Faster read operations using Flash and

Solid State Disks

•

Higher throughput with parallel threads

•

Significant time reduction for database

load to memory

TimesTen Checkpoint

Dual Parallelism – Oracle TimesTen and Intel NVMe P3700

Speed up Database Load Time to Memory

630 500

600 700

100GB Database Load to Memory Time in Seconds

Database Load Time

212 49 0 100 200 300 400

HDD 15K RPM Sata SSD Intel NVMe P3700

Dual Parallelism – Oracle TimesTen and Intel NVMe P3700

Multiple Flash Cards  Lower Load Time and Higher Throughput

42 40

50 60

100GB Database Load to Memory Time in Seconds

2.2 GB/s Single NVMe P3700

100GB Database Load to Memory Read Rate 30 0 10 20 30 40

Single NVMe P3700 Striped Two P3700

TimesTen 11.2.2.7 on Oracle Linux, Intel Xeon E7-4890 v2 2.8 Ghz, Two 15-core Processors

3.3 GB/s

0 1 2 3 4

Striped Two P3700 Cards

TimesTen Cost-Based SQL Optimizer

•

Hash indexes

– Best performance for equality matches

– Must be properly sized for good performance

– Undersized hash indexes can result in severe performance penalties

•

Range Indexes

Proper Indexes and Up-to-date Statistics  Optimized Query Execution Plans

•

Range Indexes

– Range indexes best for range search

– Can ALTER Unique PK range indexes Default index type for primary key indexes – to hash indexes

•

Up-to-date table and column statistics

Summary

•

TimesTen is an excellent choice for in-memory analytics in the application

tier

•

Correct setup and usage is vital to achieve good performance and easy

management

–OS, configuration, operations, persistence storage, tuning, etc. –OS, configuration, operations, persistence storage, tuning, etc.

•

Use the tools available to make your life easier and get the best results

• Many more best practices available

• Visit TimesTen product center on Oracle Technology Network

Most Widely Used Relational In-Memory Database

Charter Institute of Management Accountants (CIMA)

Charter Institute of Management Accountants (CIMA)

Real-life Use Case of In-Memory Analytics

Kiran Tailor

Senior Oracle DBA and Architect

CIMA, Exalytics and TimesTen

About Me

• Kiran Tailor, Senior Oracle DBA/Architect

• Sixteen years working with Oracle

• Started working with Oracle Forms, moved to databases • Oracle Databases for 15 years

• Our mission is to help people and businesses to succeed in the public and private sectors

• We have more than 218,000 members and students in 177 countries

• They work at the heart of business in industry, commerce and not for profit organisations

CIMA

• We have 28 offices around the world, employing 450 people

• We constantly update our qualification. This ensures that our members and students are first choice for employers who are recruiting financially trained business leaders

Our Challenges

Our Challenges

• As systems were slow. Staff/Business managers constantly phoning for information

Our Challenges

• Marketing Campaign Reports – 8 minutes

• Exam Reports – 2 minutes individually, thousands to do • Anything with Activities – 15 minutes+

CIMA Before Exalytics

Exalytics/TimesTen common Setup

OLTP Siebel CRM DW

TimesTen CIMA Set Up

TimesTen DAC CIMA Set Up

How DAC identifies records in TimesTen

TimesTen DAC CIMA Set Up

How DAC identifies records that need to be

TimesTen DAC CIMA Set Up

How DAC identifies records in TimesTen at records in TimesTen at

TimesTen DAC CIMA Set Up

How DAC identifies which records have How DAC identifies which records have changed in the warehouse that need to

Outcome

• Full warehouse approx 300gb loaded into memory

• Full load took approx 3 hours • Incremental nightly copy

approximately 6 minutes

• Achieved huge performance uplift with zero application code changes

• Enabled previously unachievable analysis, leading to greater

knowledge of customers and challenges across the company • 100x + performance improvement, in some cases much higher

• iPad apps roll-out ongoing

INTELLIGENCE AND BUSINESS RESULTS

100%

In-memory Daily load time

6 mins

Zero

Application code

Feedback

“It’s all a lot better. It’s so quick now it’s difficult to measure without an atomic stopwatch.” UK User

“I didn’t used to use the old dashboards to the extent that I do now- maybe that’s partly because I used to get so fed up with the speed that I gave up whereas get so fed up with the speed that I gave up whereas now it’s there at the press of a button” UK User

Report

UK - WAN connection on standard Analytics 228.6 seconds Outside of UK - Wifi connection on Analytics not possible due to error

Outside of UK - Wifi connection on Times Ten In Memory DW

Deployment Tips

• In RPD make sure you use correct TimesTen ODBC settings • We used DAC to create the table and index scripts

• INITIAL load: run the table scripts on the TimesTen Server, load the data using the DAC, run the create index scripts on the TimesTen server, update the stats on TimesTen

• OBIEE Caching: Linux Ram Disk – cache into memory! • OBIEE Caching: Linux Ram Disk – cache into memory!

• If using Exalytics and LDAP for OBIEE, follow the weblogic admin guide for configuration

• Keep checking support for Patch updates

• If any columns are being used to do updates make sure they are indexed. • Update the stats constantly especially if you are updating and inserting rows

Спасибо! Dziękujemy!

Thank you!

Product Roadmap

Product Roadmap

TimesTen 12c Roadmap

•

In-memory columnar storage

– Row-based and Columnar co-exist in the same TimesTen database – Entire database is in memory (just like today)

•

TimesTen Grid scale-out architecture

– Single image Distributed database, transactional and persistent

In-Memory Database Technology in Application Tier

– Single image Distributed database, transactional and persistent – Transparent data distribution and parallel query processing

TimesTen In-Memory Columnar

•

Process Billion rows per second

•

Columnar and row-based tables co-exist

– User choose preferred format at table level

•

Store more data in memory

– No Indexes required

Everything In Memory – Row-based Storage plus Columnar Storage

Row-based tables

plus

– No Indexes required

– Lower administration cost

•

Offload analytics processing

• Data and Operational isolations from production

data warehouse

• Complementary to Oracle Database In-Memory

option tables

Columnar Co-exist in same TimesTen in-memory database

TimesTen In-Memory Grid Database Architecture

•

Distributed In-Memory RDBMS

•

Single database image across all hosts

•

Application transparent data

distribution

•

Connect to any instance and access

Distributed – Scale Out – Always Available – Fault Tolerant

Instance 1

App App App App

Single Image IMDB

•

Connect to any instance and access

to ALL data on the entire Grid

•

Parallel query processing

•

High availability (k-safety, k >= 2)

•

Fault-tolerant

•

Scale-out to hundreds of machines

TimesTen Grid

TimesTen Grid

Q & A

Q & A