Add title by clicking

HCC – A turbo seldom recognized as such

Implementation Operation

Conceptual design Consulting

1

Nuremberg, 11/18/2015, C. Trieb, P. Böhme

Profile and Aspirations

Personal - Experienced - Efficient

2

60 employees at headquarters in Friedrichsdorf and Munich

Turnover of €11 millions

Industries: retail, logistics, public sector, pharma, banks

Flat hierarchies, open communication, flexibility Comprehensive consulting with a focus on practice Individually optimized solutions

We are a fair, professional partner on par with you

Strong partner network

Product Portfolio

Oracle in Use

3

ORACLE Real Application Clusters

ORACLE Text

ORACLE Data Guard

ORACLE Partitioning ORACLE Enterprise Manager Cloud Control

Oracle Exadata

Oracle ZFS Appliance ZS3-2

Introduction

Speakers

4

Patrick Böhme DBA OCS

Christian Trieb

DBA OCS, OCA, OCP, OCE

Companies and References

Overview

5

Turnover: €11 M.

Employees: 60

Strong partner network

Agenda

Overview

6

Paragon‘s architecture Use of HCC HCC in a nutshell HCC Fields of application Summary

Paragon‘s Architecture

Engineered Architecture

7

ADS Gateway Server (Beta)

Storage Server HCC

DB 2 Nodes RAC

Back-up

Standby Server RAC

RZ2 Platinum Support

Gateway Server RZ1

Nagios Server

Cold Data

Data Guard

Paragon Data Exadata

ZS 3

Storage

ZS 3

Storage

HCC

OEM 12c Server

RMAN Repository

ZS 3

Storage

HCC

Use of HCC

Reasons for and Ways of Compression

8

Saving disk space Improving performance through faster I/O Reducing costs Ways of compression:

Index Key Compression BASIC OLTP (Advanced Compression Option) HCC Query Low HCC Query High HCC Archive Low HCC Archive High

HCC in a Nutshell

Hybrid Columnar Compression – When and what for?

9

BASIC -Only for bulk load operations

OLTP (Advanced Compression Option) -To be paid extra Warehouse Compression (optimized for performance): HCC Query Low HCC Query High Archive Compression (optimized for storage savings): HCC Archive Low HCC Archive High Combination of column- and row-based data storage Data sets are organized into logical compression groups and sorted into columns Repeated data is grouped in the same blocks

HCC in a Nutshell

Hybrid Columnar Compression – When and what for

10

Data with similar characteristics Bulk load operations

Parallel DML CTAS SQL*LDR Insert with Append Hint

Many read operations Archived data

HCC in a Nutshell

Compression with HCC

11

HCC Works only with Exadata or ORACLE Storage Not possible with the data type “Long“! Great benefits / high compression ratio only with bulk load operations Lower compression ratio with INSERT / UPDATE Tables are organized into compression units, a compression unit is larger than

an ORACLE block Within compression units, data is organized by column instead of by row =>

similar values together, enhancing compression

Müller Müller Schultz Meier

Müller

Müller

Müller

Schultz

Schultz

Schultz

Meier

Meier

HCC in a Nutshell

Hybrid Columnar Compression Syntax

12

HCC Query Low •Create Table ... column store compress for query low ...; •Alter Table ... column store compress for query low ...;

HCC Query High •Create Table ... column store compress for query high ...; • Alter Table ... column store compress for query high ...;

HCC Archive Low •Create Table ... column store compress for archive low ...; •Alter Table ... column store compress for archive low ...;

HCC Archive High •Create Table ... column store compress for archive high ...; •Alter Table ... column store compress for archive high ...;

HCC in a Nutshell

Hybrid Columnar Compression Syntax

13

In that case the “Alter Table” command only applies to data sets that have been newly inserted afterwards. Therefore the existing data needs to be compressed explicitly: •Alter Table ... move compress for ...;

Undo: •Alter table ... nocompress;

HCC in a Nutshell

How do I know if a table was compressed?

14

Check which type of compression there is: select TABLE_NAME, COMPRESSION, COMPRESS_FOR from user_tables; (or also dba_tables) TABLE_NAME: Description of the object COMPRESSION: DISABLED / ENABLED COMPRESS_FOR: Type of compression, e.g.: BASIC / ADVANCED / QUERY HIGH / ARCHIVE HIGH

HCC in a Nutshell

Compression Overview

15

28

18

14

9 9 8

0

5

10

15

20

25

30

Unkomprimiert OLTP HCC Query Low HCC Query High HCC Archive Low HCC Archive High

GB

100%

64%

50%

32% 32% 29%

0%

20%

40%

60%

80%

100%

120%

Unkomprimiert OLTP HCC Query Low HCC Query High HCC Archive Low HCC Archive High

%

16 16 16 16

HCC in a Nutshell

HCC – Results of a Partitioned Table

16

0

50

100

150

200

250

300

DA

F_A

BH

OLF

AC

H_M

AX

VA

LUES

DA

F_A

BH

OLF

AC

H_8

00

1_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

2_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

3_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

4_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

5_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

6_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

7_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

8_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

00

9_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

0_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

1_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

2_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

3_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

4_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

5_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

6_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

7_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

8_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

01

9_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

0_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

1_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

2_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

3_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

4_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

5_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

6_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

7_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

8_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

02

9_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

0_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

1_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

2_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

3_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

4_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

5_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

6_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

03

7_

13

_1

3_

13

DA

F_A

BH

OLF

AC

H_8

09

1_

13

_1

3_

13

Unkomprimiert

Komprimiert

Result/ Savings Compression: All: 52% Selective: 70%

17

HCC in a Nutshell

HCC – The Success Story

Bibliography/ catalogue system: 1 master for processing 10 slaves for read applications 3500 users at the same time Very good response times of the application expected Oracle Text full-text search + Oracle snapshots/ materialized views

BUB Master

C1 C2 C3 C4 C5

C6 C7 C8 C9 C0 High effort for hardware and administration!

Consolidation BUB on Exadata? Oracle Text almost only block IO performance? Oracle HCC on Exadata fewer reads PoC

BUB Master

Exadata with 2 BUB

RAC instances

Application without HCC: critical performance with HCC: Performance gain of 30% Less hardware but standby and HCC? Oracle ZS3-2!

HCC Field of Application

What kind of hardware is needed for HCC?

18

Works only on Engineered Systems und ORACLE Storage But without additional licensing fees there

ZFS Storage ZS3-2 Oracle FS1 Flash Storage System Exadata Database Machine

HCC Field of Application

What can HCC achieve?

19

Saving disk space Improving performance => Reducing costs

Pufferlesezugriffe

Unkomprimiert OLTP Query Low Query High Archive Low Archive High

Hybrid Columnar Compression

Questions and Answers

20

Q & A

HCC in a Nutshell

Hybrid Columnar Compression Syntax

21

Thank you!

Christian Trieb, c.trieb@paragon-data.de and Patrick Böhme p.boehme@paragon-data.de