Top Five Ways Swiss Mobiliar Improved Business

Top Five Ways Swiss Mobiliar
Improved Business Value with Oracle
Database In-Memory
Thomas Baumann
Head of IT Performance Management
Paolo Kreth
Head of Data Management
1
In-Memory Making Headlines at Swiss Mobiliar
New business insights due to real-time analytics
REPORTS
AUF
OLTP
DATEN
ANALYTICS
ON
OLTP
DATA
In-Memory Making Headlines at Swiss Mobiliar
Massively reduced tuning efforts for SQL query texts
IN 9 VON 10 FÄLLEN KEIN SQL QUERY TUNING MEHR
9 OUT OF AUF
REPORTS
10 QUERIES
OLTP DATEN
DON‘T NEED SQL TUNING
3
In-Memory Making Headlines at Swiss Mobiliar
A paradigm change: “Bring Analytics to the Data“
REDUCED
VerzichtETL
aufFLOW
ETL
4
Agenda
 Swiss Mobiliar in a Nutshell
 Analytics on OLTP data – where we are today
 Analytics on OLTP data – PoC with Database In-Memory
 PoC Database In-Memory Results
 Further Experiences and Next Steps with Database
In-Memory at Swiss Mobiliar
 Summary
5
Swiss Mobiliar
Switzerland‘s most personal insurer

legal form of a cooperative association (mutual
company).

Switzerland’s number one insurer for
household contents, business and pure risk
life insurance.

close to customers throughout the country
thanks to around 80 general agencies at 160
locations.

over 1.7 million insured persons or firms.

over 4,400 employees and 325 trainees.
13x continuously 2003-2015
6
Overall Non-Life Insurance Market Growth in Switzerland
Close to 2/3 of Market Growth to Swiss Mobiliar in 2014
Growth Mobiliar
Market Growth
in Mio CHF. Source: Schweizerischer Versicherungsverband
7
Swiss Mobiliar‘s IT Misson Statement: Deploy and Run
innovative technologies for efficient business processes





Systems: iOS, Linux, Windows and z/OS
5000 Notebooks, 1800 iPhones, 300 iPads
DBMS: Oracle, DB2, IMS, MS SQL Server, Neo4j
Standard-SW (ArcGIS, SAP, BO, Siebel, COR Life etc.)
Many home-grown applications
8
The Speaker
Thomas Baumann
 Born in 1963
 MSc. at Swiss Federal Institute of Technology, Zurich
 Computer Sciences and Statistics
 Today, this mix is called Big Data
 Has been focusing on Database Management Systems and Performance since 1992
 Speaker at numerous international conferences
 „Performance Minister“ at SwissMobiliar
 „dedicated to performance – since 1963“
also produces this search result:
 dedicated to performance – since 1963
9
Agenda
 Swiss Mobiliar in a Nutshell
 Analytics on OLTP data – where we are today
 Analytics on OLTP data – PoC with Database In-Memory
 PoC Database In-Memory Results
 Further Experiences and Next Steps with Database
In-Memory at Swiss Mobiliar
 Summary
10
Analytics on OLTP Data at Swiss Mobiliar
Architecture 2014
Analytical
(OLAP)
Data Mart
Decision Support
Data
Warehouse
Cross
Information
Systems
Operational
(OLTP)
Business
Intelligence
Access
Information
Systems
OLTP
Core
Information
Systems
OLTP Analytics
Scope
Analytics on OLTP data at Swiss Mobiliar
Architecture 2020
Analytical
(OLAP)
Data Mart
Decision Support
Data
Warehouse
Cross
Information
Systems
Operational
(OLTP)
Business
Intelligence
Access
Information
Systems
OLTP
Core
Information
Systems
OLTP Analytics
Scope
Architecture 2014
IDAA (IBM DB2 Analytics Accelerator) Value Delivery
SQL Query
(to DB2)
Automatic query re-routing of searchintensive queries1) to data copy at Netezza
appliance
Result
In-Memory
Set  Similar design patterns for Oracle Database
For applications which don‘t require transactionally consistent data, and can
DB2similar
zOS results
IBMas well?
 Are there
(“Mainframe“) Netezza
1)
accept data delayed by a few minutes
Design Patterns
Major Results
 Column oriented data storage
 Increased OLAP query performance
 100 times faster in average
 Data replication close to real time based on
log records, not transactionally consistent  Faster inserts on DB2 and higher
scalability
 Query Re-Routing decided by optimizer,
transparent for application
 Due to elimination of most indexes
 No need for indexes
 Very high compression rate
 Short timeframe between data ingress
and analysis
13
More Real-Time Analytics Headlines at Swiss Mobiliar
Reduced CPU consumption during peak time
IDAA in more detail
(or what is different to Oracle Database In-Memory)
 Massive Parallel Processing Architecture
 32 nodes on 2 blades at Swiss Mobiliar
 Max Capacity 1024 nodes (8 racks)
 Data Processed Locally
 FPGA (HW encoded logical arrays):
 Decompression, Projection, Predicate
Application all done locally within FPGA
 Joins processed locally if possible, data
redistribution among nodes otherwise
 Data clustering within node to support skips
for data scanning (zone maps)
 Tuning Knobs:
 Data distribution among nodes
 Data clustering within nodes
Both are independent of DB2
partitioning and clustering
30% REDUKTION
REDUCTION OF
MAINFRAME
MAINFRAME
CPUCPU
RESSOURCEN
 Tables completely replicated (all
rows/columns)
14
Step-by-Step IDAA Usage
12 month from installation to broad usage
 First Business Applications
 Ad-hoc reports from business end users
 Improved end-of-month processing
 Log analysis based on DB2 tables for access pattern analytics
Improved
ETL flow
 We expect
a similar
timeline for Oracle Database In-Memory (see later)
 Streamline Mainframe for OLTP
 Eliminating indexes used for analytics only
 Eliminate MQT and other auxiliary structures for analytics
 Reduced demand for reorg
 More efficient inserts
 New Business Functions
12 months
15
New Business Reports
(1 of 2)
How Swiss Mobiliar‘s CRM system is used?
47.8
“The more distant from HQ,
47.6
the less they care about
instructions“
47.4
47.2
47
46.8
HQ
How was that this morning
(CEO visit at Hochdorf
agency)?
46.6
46.4
46.2
46
report produced with 3.8sec response time
45.8
The
larger
the surface
of the8 ball,8.5the more
the
agency
is following HQ‘s
6
6.5
7
7.5
9
9.5
10
instructions on how to use Swiss Mobiliar‘s CRM system.
16
New Business Reports
(2 of 2)
Cross Selling
Owner of which car makes do also have many other contracts at Swiss Mobiliar?
with partner_MFZ (PNR, Make) as
(SELECT DISTINCT PAR.C97251 AS PNR, MFZ.D63538 as MAKE
FROM DB2PVIEW.VGESGPA2 PAR, DB2PVIEW.VALL0800 VER, DB2PVIEW.VPA08091 VER_OBJ,
DB2PVIEW.VOBMFZP1 MFZ
WHERE PAR.C95826 = VER.C95826 AND VER.C95826=VER_OBJ.C95826 AND VER_OBJ.C95836_1=MFZ.C95836
AND
VER_OBJ.C95862=MFZ.C95862 AND PAR.C97150 = 417 AND PAR.C99996 = 0 AND YEAR(PAR.C99992) =
9999
AND YEAR(PAR.C99995) = 9999 AND YEAR(VER_OBJ.C99995)=9999 AND YEAR(VER_OBJ.C99992)=9999
AND
YEAR(MFZ.C99995)=9999 AND YEAR(MFZ.C99992)=9999 AND VER.C95836 = 1 AND VER.C99996 = 0
AND
YEAR(VER.C99995) = 9999 AND YEAR(VER.C99992) = 9999 AND VER.D95893 IN (24,25,26,27) ),
partner_all (PNR, Number) as
(SELECT PAR.C97251 AS PNR, COUNT(*)
FROM DB2PVIEW.VGESGPA2 PAR, DB2PVIEW.VALL0800 VER
WHERE PAR.C95826 = VER.C95826 AND PAR.C97150 = 417 AND PAR.C99996 = 0 AND YEAR(PAR.C99992) =
9999 AND
YEAR(PAR.C99995) = 9999 AND VER.C95836 = 1 AND VER.C99996 = 0 AND YEAR(VER.C99995) =
9999 AND
YEAR(VER.C99992) = 9999 GROUP BY PAR.C97251)
select make, avg(number) as number_of_contracts, count(*) as number_of_customers
from Partner_mfz, partner_all
17 where partner_mfz.pnr=partner_all.pnr group by make having count(*) > 1000
order by 2 desc
Response time
in sec
10000
100
1
DB2
IDAA
17
Real-Time Analysis: Risks
Unvalidated correlations
No. of storks
observed
No. of new-born
children in same
area and year
Both statistics, number of storks observed, and number of new-born
children counted in same area and year, are correlated with time, and
both were decreasing, independent from each other.
18
Agenda
 Swiss Mobiliar in a Nutshell
 Analytics on OLTP data – where we are today
 Analytics on OLTP data – PoC with Database In-Memory
 PoC Database In-Memory Results
 Further Experiences and Next Steps with Database
In-Memory at Swiss Mobiliar
 Summary
19
Oracle Database In-Memory PoC
Objectives
 The PoC (Beta-test) of Oracle 12c Database In-Memory shall prove that
 comparable, if not better, response times might be achieved for the same data
and the same query compared with DB2/Netezza.
 the number of queries with demand for manual query statement tuning will
reduce by at least 90%.
 analytics on OLTP data will be, after migration DB2Oracle,
• very efficient.
• transparent for users and applications.
• without impact on OLTP processing.
20
Oracle Database In-Memory
 So far: Data organized in rows, data loaded into memory at first usage
 New: Additional: Data organized in columns, permanently stored in memory
automated, near real-time
replication
designed for
transactions
Memory
Memory
data
row-oriented
data
column-oriented
designed for
analytics
21
Oracle Database In-Memory PoC
Configuration
 Offer and Contract data
 Migration DB2Oracle of 97 tables with No. of rows between 0 and 2.4 billion
 Offer data (550K contracts in progress)
 Contract data (5.4M contracts)
 In other words: Every 10th contract is in progress.
 This data is not part of the traditional data warehouse.
 Analytics of this data needs to be close to real time
 All tests were performed by applying 13 real user reports with real data.
22
Oracle Database In-Memory PoC
The PoC should answer the following 6 questions
1. How expensive is the migration of the DB2 data into Oracle?
2. How much tuning effort was required after activating Oracle Database In-Memory?
3. Are the response times better than with DB2? Which part of the improvement is due to
Database In-Memory?
4. How much administration effort is necessary to deploy and run Database In-Memory?
5. Which are the prereq‘s to apply Database In-Memory?
6. Which are the strengths and weaknesses of Database In-Memory?
23
Agenda
 Swiss Mobiliar in a Nutshell
 Analytics on OLTP data – where we are today
 Analytics on OLTP data – PoC with Database In-Memory
 PoC Database In-Memory Results
 Further Experiences and Next Steps with Database
In-Memory at Swiss Mobiliar
 Summary
24
The Speaker
Paolo Kreth





Born 1968 in Genoa, Italy
Master Degree in Informatics at Genoa University
Working with Oracle since 1998 – Oracle 8i Object Relational Database
Since 2010 OracleTechnical Lead @Mobi
1968 – Ferrari 365 GTC Coupé
25
Question 1: Database migration from DB2 to Oracle
•
Data was successfully migrated from DB2 to Oracle: 3 days effort.
•
Adjustments from DB2 to ORACLE
 TIMESTAMP Data Type conversion 24:00  23:59:999999
(Oracle does not recognize 24:00)
 Adjustments on DDL-definitions for empty Strings/Numbers/Timestamps (DB2)
CREATE TABLE TEO_TBENPRO
( C43087 CHAR(30)
NOT NULL,
C….
NUMBER(30)
NOT NULL,
…. )
‘
0
•
DEFAULT ON NULL '
DEFAULT ON NULL
No runtime errors.
26
Question 2: How much tuning effort was required after
activating Database In-Memory?
•
•
No tuning was required.
One single optimization:
 We changed the partitioning layout of a table to optimize it for the required report
(Amount: 1 hour)
27
Question 3 : Comparison Oracle Native – Oracle In-Memory
•
Comparison based on Elapsed Time:
… Oracle (native) vs. Oracle Database In-Memory
100.00
10.00
1.00
Oracle native
0.10
Oracle IM
Column Store
Total elapsed time (s):
ORACLE native
ORACLE IM
194.21
44.7
28
Question 3: HW-Comparison between IDAA and Oracle
IBM Netezza IDAA vs. Oracle 12c Database In-Memory
… in the blue corner….
IBM Netezza IDAA:
•
•
•
PureData System for Analytics
N1001-002
•
24 Cores
•
72 GB RAM
Netezza V 7.02
IDAA V3
… in the red corner….
Oracle 12c mit OIMO:
•
•
•
•
Hitachi Unified Compute
Platform
•
16 Cores
•
384 GB RAM
no attache PCI Flash Disks
OS: RedHat 6.4
Oracle 12.1.0.2 Beta 3
29
Question 3: Oracle 12c Database In-Memory vs. IBM Netezza
•
Comparison of elapsed time …
… Netezza IDAA vs. Oracle Database In-Memory
100.00
IDAA
10.00
1.00
Oracle IM 0.10
Column Store
Total elapsed time (s):
IDAA
76.74
ORACLE IM
44.73
30
Question 4: Adminstration of Oracle Database In-Memory
•
50 GB RAM was reserved for the in-memory column store
 …41 GB used (Memcompress for Query)
 …on SAN-these data occupied 170 GB.
 Compression Ratio : about 4.15
•
Data modifications were propagated near real time to the Column Store in our test.
•
Oracle Database In-Memory allows us to drop all „non-unique“ Indexes
•
We did not have to load the whole table into memory, we loaded only selected partitions or
even columns.
31
Question 5: Pre conditions for optimal usage
Which are the pre conditions for an optimal usage of Oracle Database In-Memory?
a) Use of ORACLE built in functionalities to restrict the data set (Partitioning)
 The optimal partitioning method can significantly improve the query performance
b) Find the optimal degree of parallelism in 12c
 In our tests we found that the optimal degree of parallelism was 8
(this value depends on hardware we tested on – the CPUs were unable to get more
data from memory)
 More parallel slaves produced only more overhead
 For different hardware this value could differ
c) Plan Stability
 Of relevant importance is the stability of execution plans. Therefore the optimizer
statistics have to be up to date.
32
Question 6: Strengths and Weaknesses of Database In-Memory

Strengths:
• It scales very good incrementing the degree of parallelism. Also small degree increments
show big performance improvements.
• Read and Filter of large data volumes
• Easy administration
 You can improve the performance of an application within minutes

Weaknesses:
• Sorts of large data volumes is still a bottleneck ( 100+ GB)
 Sorts still use temporary tablespace
•
Proposal to ORACLE:
 Temp Segment in Memory for Column store ?
 Feedback to Oracle has been provided.
33
Agenda
 Swiss Mobiliar in a Nutshell
 Analytics on OLTP data – where we are today
 Analytics on OLTP data – PoC with Database In-Memory
 PoC Database In-Memory Results
 Further Experiences and Next Steps with Database
In-Memory at Swiss Mobiliar
 Summary
34
Results within the MobiliarFIS Project
 What were the straits within the MobiFis Project:
 MobiFis eDWH is still in development
We could work only on a small set of queries and
reports
 Solution:
 We did the tests only on Queries on a fact table and the
corresponding dimension tables
 Beside the small query set on top of the reports we got from development we built
synthetic queries trying to simulate future reports by creating:
 Sums, averages and other group functions on the existing tables
 Grouping of values
 We issued these queries on the following data volumes
• 1 Fact-Table with1.9 Mrd. Rows
• Up to10 Dimension Tables between 100 and 2.5 Mio. Rows
35
Results on MobiliarFIS
 Comparison of elapsed times
Query
Elapsed Time(s)
11g
12c
12c InMemory
Overview Tb Level (BO-Report)
949
183
135
Numer of contracts for one product in 01/14
32
29
4
Building blocks per single contract
274
306
0,3
Sums on single contracts
270
310
0,053
Sums and group by on single contracts
289
245
0,5
1
0,045
0,018
1815
~ 1073
~ 140
1
~ 1.7
~ 13
All contracts for a product
Sum
Factor
36
Resume MobiliarFIS
 Summary
• The Results from the PoC on Oracle 12c
"In-Memory Column Store" showed an improvement of one or two magnitudes.
• Typical DWH queries like sums, averages etc. can be improved dramatically .
• The assumptions made at the start of the PoC could be confirmed.
 Next Steps
• MobiliarFis has been migrated on 12.1.0.2 and we are beginning to develop reports which
will benefit from Oracle Database In-Memory.
37
Test on an existing Application - RICO
 We tested on the database of our risk controlling application (RICO)
 The Rico Database occupies about 1 Tb of Data
 The Rico application is over 20 Years in production
 We collected the most important queries from the user‘s perspective
 These queries were the candidates for improvement.
38
Tests on RICO – Before starting
 We analyzed the existing application and investigated performance optimization we could get
before 12c:
 Compression
 Optimization of PCTFREE parameter
 Change of the partitioning schema of some tables
 We started using In-Memory after these changes
39
Results RICO
Speed Up Oracle 12c (without In-Memory) vs. Oracle 11g
Parallel Degree
Query 1
Query 2
Query 3
Query 4
Query 5
All
1
3.53
1.82
0.78
2.52
2.32
2.57
2
2.66
0.94
5.82
2.42
1.63
2.23
4
2.87
1.06
5.49
2.39
2.44
2.56
8
2.65
1.40
5.89
1.95
2.70
2.44
16
2.72
1.73
6.41
2.79
2.73
2.75
32
2.69
2.16
7.79
2.74
2.74
2.73
On average the queries were running 2.5 times faster.
40
Results RICO
Speed Up Oracle 12c In-Memory vs. Oracle 12c
Parallel Degree
Query 1
Query 2
Query 3
Query 4
Query 5
All
1
108.89
25.31
48.77
38.50
46.12
46.89
2
138.89
4.66
6.25
51.20
49.80
49.10
4
261.17
4.81
5.67
88.12
87.58
86.15
8
280.25
4.28
4.67
154.45
144.61
135.51
16
370.25
3.46
3.40
212.10
233.79
191.44
32
404.91
2.58
0.32
262.35
234.53
130.24
On average the queries were running between 46 and 191 times faster.
41
Results RICO
Both slides combined: Speed Up Oracle 12c In-Memory vs. Oracle 11g
Parallel Degree
Query 1
Query 2
Query 3
Query 4
Query 5
All
1
384.04
46.16
37.84
96.91
107.03
120.40
2
370.11
4.39
36.38
124.10
81.15
109.43
4
750.78
5.13
31.11
210.42
213.33
220.82
8
743.44
6.00
27.50
301.76
390.77
330.33
16
1007.25
6.00
21.80
591.14
638.32
525.94
32
1087.73
5.58
2.48
717.65
642.95
354.92
On average the queries were running between 109 and 525 times faster.
42
Summary RICO
 Rico has been migrated on July on 12.1.0.2
 A development environment has been built to test the upgrade and the effects of InMemory
 We are using the SQL Performance Analyzer of the Real Application Testing option
to identify improvements and regression
 We are involved in the Oracle 12c Reference Program and we are working closely
with Oracle.
 Oracle In Memory will be activated after the Mobiliar Software Release by end of
October
43
Agenda
 Swiss Mobiliar in a Nutshell
 Analytics on OLTP data – where we are today
 Analytics on OLTP data – PoC with Database In-Memory
 PoC Database In-Memory Results
 Further Experiences and Next Steps with Database
In-Memory at Swiss Mobiliar
 Summary
44
Oracle Database In-Memory Benefits
End User Perception
 Like an additional, fast, access path
 Fastest way from San Francisco to Chicago?
31 hours
4h 30min
Beam me up, Scotty
(+1h 30min)
SQL Tuning
DB In-Memory
45
Thank you!
Thomas Baumann
[email protected]
[email protected]
Paolo Kreth
[email protected]
[email protected]
46