engineered systems what is it for dba’s systems - what is it... · exadata smart scans •...

© Copyright 2015. Apps Associates LLC. 1

Engineered Systems – What is it for DBA’s

February 23, 2015

© Copyright 2015. Apps Associates LLC. 2

Satyendra Kumar Pasalapudi

Associate Practice Director – IMS @ Apps Associates

Co Founder & President of AIOUG

@pasalapudi

© Copyright 2015. Apps Associates LLC. 3

Engineered Systems – What is it for DBA’s ?

© Copyright 2015. Apps Associates LLC. 4

How does the database process OLTP Caching

• Cache data in memory from disk to achieve fast query response

• For OLTP/ DSS workloads, memory should be big enough to hold as much as possible

SGA

Buffer Cache

100% cache hit ratio is ideal for

OLTP workloads

© Copyright 2015. Apps Associates LLC. 5

How does the database process OLTP?

• CPU time is mainly consumed by I/O wait

Cache miss Cache Hit

© Copyright 2015. Apps Associates LLC. 6

OLTP performance issues

2. User Volume is

increasing...

1. Data Size is

increasing...

SGA

1. Huge amounts of data

2. Not possible to cache

all data

3. Many I/O operations

Buffer

Cache

© Copyright 2015. Apps Associates LLC. 7

0.00

2.00

4.00

6.00

8.00

10.00

12.00

14.00

16.00

0

100

200

300

400

500

600

700

800

100 200 300 400 500 600 700 800

Response T

ime(N

orm

al-

100T

hre

ad=

>1.0

0)

TP

S(N

orm

al-

100T

hre

ad=

>100)

Thread

TPS RES

Typical Problem of busy systems

I/O bottleneck

Low Throughput and Slow Response

Low CPU usage

Why? • 3rd Platform drives new

demands on the database: – Global High Availability – Data volumes – Unstructured data – Transaction rates – Latency

• A single architecture cannot meet all those demands

Are traditional systems sufficient to handle workloads?

Complex technical support structure involving different vendors

Drove delays in resolving system outages Vendor finger pointing

Objective / Problem Statement

10

The current platform did not meet performance expectation Not meeting SLA’s Customers waiting in line

Database,OS,Siebel upgrade was overdue

Did not have room for growth or enough

capacity for additional users

Release process took up to 8 hrs of down time

No disaster recovery environment

Hardware was overdue to be replaced

1000+ IP’S 100+ Application Zones 16 Databases SOA ,Siebel ,OBIEE,OPA

Solution Approach

11

Data replication using Data Guard

Data replication using Data Guard

File replication using Zfs

File replication using Zfs

Dallas,TX Tulsa,OK

PROD STANDBY

STAGE PRIMARY

STAGE STANDBY

PROD PRIMARY

Near Zero down time using Golden Gate

1

2

Data replication using Data Guard

File replication using Zfs

Dallas,TX Tulsa,OK

Enable Golden Gate Capture

Code Deployment

Start Replicat

Trail

Files

Trail

Files

Extract

Dallas,TX Tulsa,OK

Data replication using Data Guard

File replication using Zfs Deployment Completed

Replicat Completed

Validation Passed

Oracle Engineered Systems

© Copyright 2015. Apps Associates LLC. 14

Hadoop Design Principles

• System shall manage and heal itself

– Automatically and transparently route around failure

– Speculatively execute redundant tasks if certain nodes are detected to be slow

• Performance shall scale linearly

– Proportional change in capacity with resource change

• Compute should move to data

– Lower latency, lower bandwidth

• Simple core, modular and extensible

© Copyright 2015. Apps Associates LLC. 21

Oracle Engineered Systems

Engineered for Database Physical I/O

Database CPUs Memory HBAs Switches Controllers

An unbalanced configuration

Exadata configuration

< 20% Possible

Efficiency

100% Possible

Efficiency

Disks

Database CPUs Memory HBAs Switches Controllers Disks

Designed To Avoid Bottlenecks

© Copyright 2015. Apps Associates LLC. 23

Exadata Eliminates Communication Bottlenecks

© Copyright 2015. Apps Associates LLC. 24

Exadata Evolution V1 to X5

2015

© Copyright 2015. Apps Associates LLC. 25

What’s Inside Exadata?

• Infiniband Switches

• Database Servers

• Storage Servers

© Copyright 2015. Apps Associates LLC. 26

Exadata Architecture

© Copyright 2015. Apps Associates LLC. 27

Exadata Smart Scans

• Exadata cells implement smart scans to greatly reduce the data that needs to be processed by database

– Only return relevant rows and columns to database

– Offload predicate evaluation

• Data reduction is usually very large

– Column and row reduction often decrease data to be returned to the database by 10x

• Join Filtering – Bloom filters used for join filtering in storage

• Telco wants to identify customers that spends more than Rs 200 per phone call

• The information about these customer occupies 2 MB in a Tera Byte Table

© Copyright 2015. Apps Associates LLC. 28

Traditional Scan Processing

© Copyright 2015. Apps Associates LLC. 29

Exadata Smart Scan Processing

© Copyright 2015. Apps Associates LLC. 30

Wait Event Description

Cell interconnect retransmit during physical read Database wait during retransmission for an I/O of a single-block or multiblock read

Cell single block physical read Cell equivalent of db file sequential read

Cell multiblock physical read Cell equivalent of db file scattered read

Cell smart table scan Database wait for table scan to complete

Cell smart index scan Database wait for index or IOT fast full scan

Cell smart file creation Database wait for file creation operation

Cell smart incremental backup Database wait for incremental backup operation

Cell smart restore from backup Database wait during file initialization for restore

Cell statistics gather Wait during query of v$cell views

Exadata Storage Server Wait Events

© Copyright 2015. Apps Associates LLC. 31

Smart Scan Execution Plan Example

© Copyright 2015. Apps Associates LLC. 32

Smart Scan Execution Plan Example

© Copyright 2015. Apps Associates LLC. 33

Unique Exadata Benefits are Increasing

New F40 Flash PCI Card in Storage

• New F40 eMLC card with 4X capacity

• eMLC is Enterprise grade Multi-level Cell. 2-bits per flash cell doubles capacity

– eMLC has excellent lifetime. As always, flash lifetime is guaranteed by Oracle. Any failed cards are replaced under Oracle Support contract

– Note this is true Enterprise MLC flash. Other vendors claim to use lower cost cMLC (consumer MLC) and extend its lifetime. This is not guaranteed by the flash manufacturer and performance towards end of lifetime is potentially degraded.

• Total read IOPs at the flash card level are much higher than quoted IOPs for DB Machine. We measure end-to-end SQL IOPs, not low level hardware IOPs.

• Read and Write latency improved by 40% or more

• Reduces Maintenance by replacing Energy Storage Module (ESM) with much longer lifetime conventional capacitors. Both guarantee writes are never lost.

Current F20 Card New F40 Card Improvement

Capacity* 96GB 400GB 4 X

Data Scan Rates 1 GB/s >1.4 GB/s 1.4X

Flash Cache Advantages over Flash Tiering

• All the existing Exadata flash advantages also apply

– Scale-out architecture

– InfiniBand 40Gb/sec connectivity

– Flash PCI Cards are much faster than flash disk

– EHCC compression enhances flash capacity

– Smart Scans on data in flash

– Smart Flash Logging

– Smart Caching avoids churning of cache by backups, loads, etc.

– Flash is shared across servers and works with RAC

• Unlike server flash cards

– Optionally Keep specific tables/indexes/partitions in flash with simple command

– Benefits from storage index

– Decompression and decryption in cell CPUs during scans

Faster Flash Scans for Data Warehousing

• Updated flash technology also enables faster Exadata Smart Scans

– Scan rate impoved 33% from 75 GB/sec to 100 GB/sec

• As always, Exadata flash cache technology enables scans to run simultaneously from disk and flash

– Delivers sum of the scan speeds of both

– Another unique benefit of flash cache over flash tiering

• Amazing Capacity when combined with compression

– Hybrid Columnar 10x Compression enables up to 200 TB of user data in the 20TB of physical flash capacity

– Advanced OLTP compression enables up to 80 TB of user data in flash

100 GB/sec

Scans

200 TB of

User Data

in Flash

© Copyright 2015. Apps Associates LLC. 37

I O Resource Manager – IORM

© Copyright 2015. Apps Associates LLC. 38

I/O Scheduling, the Traditional Way

Report Report Report Report

OLTP OLTP

Report

With traditional storage, disks service I/Os in FIFO order.

I/Os are reordered only to improve disk efficiency.

You cannot influence their behavior!

A burst of Report I/Os will be queued ahead of OLTP I/O.

And serviced ahead of OLTP I/Os!

© Copyright 2015. Apps Associates LLC. 39

I/O Scheduling, the Exadata Way

Report Report Report Report

OLTP OLTP

Report

I/O Resource Manager controls order that I/Os are issued to disk.

IORM issues enough I/Os to keep disks busy and efficient.

I/Os are queued per database, as necessary, within Exadata.

Uses Resource Plan to determine the order of I/O requests

Prevents a database from flooding the disk

I/O Resource Manager

OLTP

OLTP

OLTP

Exadata Storage Cell Resource

Plan

© Copyright 2015. Apps Associates LLC. 40

How Strong you are at ASM?

© Copyright 2015. Apps Associates LLC. 41

Oracle Database Exadata Machine X5

© Copyright 2015. Apps Associates LLC. 42

Exadata X5 is Sixth Generation Database Machine

© Copyright 2015. Apps Associates LLC. 43

New X5-2 Extreme Flash (EF)Storage Server

© Copyright 2015. Apps Associates LLC. 44

Elastic Configurations Scale Compute and Storage

© Copyright 2015. Apps Associates LLC. 45

Elastically Optimize Exadata for Each Workload

Operational RDBMS

(Oracle, SQL Server, …)

In-memory Analytics (HANA,

Exalytics …)

In-memory processing

(Spark)

Hadoop

Web DBMS (MySQL, Mongo,

Cassandra)

ERP & in-house CRM

Analytic/BI software

(SAS, Tableau

Web Server Data

Warehouse RDBMS

(Oracle, Terradata …)

Sample Enterprise Big data Architecture

© Copyright 2015. Apps Associates LLC. 47

Big Data Architecture

D A T A

S O U R C E S

DATA LAKE – On AWS Big Data Infra (Optrion2)

DATA CONNECTORS

A N A L Y T I C S

DATA LAKE on Oracle Big data Appliance (Option1)

DATA LAKE – On Premise Hadoop Infra(Option3) D A T A L A K E

© Copyright 2015. Apps Associates LLC. 48

Administration Skills Needed

System Administration 15%

Storage/Cell Administration

20%

Network Administration 5%

Database Administration 60%

DBA Mandatory Skills

Real Application Clusters (RAC)

Automatic Storage Management (ASM)

Recovery Manager (RMAN)

Partitioning, Oracle Secure Backup & Database Vault (Optional)

Skills Required

DBA should wear multiple hats ( Architect, DBA, Sys Admin, Storage Admin, Network Admin) +

Write back Flash cache, IORM, Smart Scan, Storage Indexes, Smart Flash Logging, Cell CLI)

© Copyright 2015. Apps Associates LLC. 49

DMA

© Copyright 2015. Apps Associates LLC. 50

Thank You!