PoC Oracle Exadata - Retour d'expérience

BÂLE BERNE BRUGG DUSSELDORF FRANCFORT S.M. FRIBOURG E.BR. GENÈVE

HAMBOURG COPENHAGUE LAUSANNE MUNICH STUTTGART VIENNE ZURICH

Exadata x5-2 POC with OVM

Jacques KosticSenior Consultant IMS Lausanne

About me…

Senior Consultant, Trivadis AG, Lausanne-CH

Experience

• Oracle DBA since more than 25 years, initially with version 4

• High Availability and Backup & Recovery

• SQL and Instance Performance & Tuning

• License Audit and Consolidation

Teaching Courses at Trivadis

• Oracle Grid Infrastructure & RAC

• Oracle Data Guard

• Oracle SQL Performance & Tuning

• Oracle Instance Performance & Tuning

Agenda

1. Introduction

2. Current Oracle Architecture

3. Alternatives with Exadata X5-2

1. Without OVM

2. With OVM

4. POC execution and results

5. Proposed architecture

6. Five years projection plan

7. Q&A

Introduction

Customer Overview

Medium size customer from insurance sector

Several databases with different workload types

Lack of storage and resources with licensing constraints

Consolidation opportunities with the new Exadata X5-2

Very short time to run the POC (5 days!)

The name will not be disclosed but the most relevant characteristics to

the project are reported below.Customer

Environment

Current Oracle architecture

Current Oracle architecture

IBM AIX P7 PowerVM technologies, 1 LPAR per instance on uncapped CPU POOL

– 20 production Oracle instances

– 60 dev, qa, int instances

– ~25TB PROD/DEV/QA/INT

– 80 LPAR

– Max 700GB per database, generally OLTP workload except for Documentum

– Good SQL optimization for OLTP databases

Licensed 20 CPU Enterprise Edition with Diagnostic and Tuning packs

Uncapped CPU POOL is problematic for licensing compliance aspects

However, CPU POOL usage charts are not showing pics above 12 CPU

Alternatives with Exadata X5-2

Exadata X5-2: Without OVM

Pros.

Use the entire machine capacity

Less servers to manage

Pay-as-you-grow approach (COD) for software licensing is another way in which Exadata helps to align costs with

business growth

– Minimum 40% of the cores must be activated

– All additional options must follow the same allocation

Cons.

Isolation between databases and environments

License optimization

Exadata X5-2: With OVM

Pros.

Environment and database isolation

Hard partitioning facilitate licensing optimization

– Minimum 40% of the total cores must be licensed for Enterprise Edition product

– For other options, it’s linked to CPU allocation for each VM

– One core per database node dedicated to dom0 (out of software licensing)

Very flexible, dynamic vCPU allocation

Allow IO resource management between all database from all virtual machines.

Db_unique_name must be unique across the entire Exadata

Cons.

Might appear more complex to manage

New feature on Exadata X5-2 (backported to X4)

– Strong investment from Oracle on the technology representing the key solution for global

consolidation projects

Deployment

Create configuration (clusters) with Oracle Exadata Deployment Assistant (OEDA)

Configuration tool

– OEDA Configuration tool version Mar 2015 v15.084 - Patch 20645646

Prepare system

– IP allocation, customer requirements

Deploy configuration using OEDA Configuration tool

Exadata X5-2: OracleVM overview on Exadata

https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?parent=DOCUMENT&sourceId=888828.1&patchId=20645646

Exadata X5-2: Cluster deployment example

POC Execution and Result

POC Execution and Result

Environment

Exadata 1/8

OVM Configuration

Two-node cluster with 26 vCPUs per node and 90 GB of RAM

1 database of 300GB with 30 GB of SGA (OLTP)

1 database of 700GB with 30 GB of SGA (Documentum)

POC Execution and Result: OLTP batch processing

Job P7 with DS8000 Exadata *Gain

Generate account validation

Preparation 2m 31s 45s 336%

Execution (28536 accounts) 2h 29m 41s 1h 17m 26s 192%

Summary generation

Execution (28536 accounts) 3h 19m 29s 2h 10m 4s 154%

Reporting

Preparation 1m 16s 48s 158%

Execution 13h 38m 45s 10h 05m 10s 135%

Account validation batch on the OLTP database with 26 threads in parallel

*Test done with 26 vCPUs and 2 vCPUs, no differences on the execution time

POC Execution and Result: OLTP batch processing

AWR Extractions

Nothing to report!

POC Execution and Result: Documentum

select all doc.r_object_id, doc.a_content_type

from VFK_TST_DCTM.vfk_document_sp doc LEFT OUTER JOIN

VFK_TST_DCTM.dmi_0301d65580000206_sp ON

doc.r_object_id = dmi_0301d65580000206_sp.r_object_id

where

((doc.title!='office rendition error')

and (dmi_0301d65580000206_sp.c_status!='en traitement')

and doc.a_content_type in

('msw8', 'msw12', 'excel8book', 'excel12book', 'ppt12', 'ppt8', 'msg')

and not ( exists (select * from VFK_TST_DCTM.dmr_content_sp dmr_content

where (dmr_content.r_object_id in

(select r_object_id from VFK_TST_DCTM.dmr_content_r

where parent_id=doc.r_object_id)

and (dmr_content.full_format='pdf')

)

)

)

) and (doc.i_has_folder = 1 and doc.i_is_deleted = 0);

Query

Identify significant query

Execution time between 486 sec and 12,276 sec (average 1,226 sec)


Execution on production system:----------------------------------------------------------------------------------

| Id | Operation | Name | E-Rows |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | NESTED LOOPS | | 499 |

| 2 | NESTED LOOPS | | 494 |

|* 3 | HASH JOIN ANTI | | 494 |

| 4 | INLIST ITERATOR | | |

|* 5 | TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S | 49392 |

|* 6 | INDEX RANGE SCAN | DM_SYSOBJECT_S_INDX20 | 49392 |

| 7 | VIEW | VW_SQ_1 | 31M|

| 8 | NESTED LOOPS | | 31M|

| 9 | TABLE ACCESS FULL | DMR_CONTENT_R | 69M|

|* 10 | INDEX RANGE SCAN | DMR_CONTENT_S_INDX01 | 1 |

|* 11 | INDEX UNIQUE SCAN | D_1F01D65580000924 | 1 |

|* 12 | INDEX RANGE SCAN | DMI_0301D65580000206_S_INDX06 | 1 |

----------------------------------------------------------------------------------

2h30min!


Execution on Exadata:------------------------------------------------------------------------------


------------------------------------------------------------------------------


| 1 | NESTED LOOPS | | |

| 2 | NESTED LOOPS | | 841 |

| 3 | NESTED LOOPS | | 831 |

|* 4 | HASH JOIN ANTI | | 831 |


|* 6 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 83083 |


| 8 | VIEW | VW_SQ_1 | 30M|

|* 9 | HASH JOIN | | 30M|

|* 10 | INDEX STORAGE FAST FULL SCAN| DMR_CONTENT_S_INDX01 | 27M|

| 11 | TABLE ACCESS STORAGE FULL | DMR_CONTENT_R | 67M|



|* 14 | TABLE ACCESS BY INDEX ROWID | DMI_0301D65580000206_S | 1 |

1,21min!


Different execution time

2h30 min versus 1min 21sec

Different execution plan

Missing histograms in production on column PARENT_ID for table DMR_CONTENT_R


Collect missing histograms:

On Exadata(just to have the elapse time)

Begin

dbms_stats.gather_table_stats (

ownname => 'VFK_TST_DCTM',

TABNAME => 'DMR_CONTENT_R',

METHOD_OPT => 'for all columns size skewonly');

End;

Elapsed: 00:01:49.905

En Prod

Begin

dbms_stats.gather_table_stats (

ownname => 'VFK_TST_DCTM',

TABNAME => 'DMR_CONTENT_R',

METHOD_OPT => 'for all columns size skewonly');

End;

Elapsed: 00:10:02.628

Factor of 5 on

the same

dataset!


After having collected missing statistics, here is the result in Prod:------------------------------------------------------------------------------


------------------------------------------------------------------------------



| 2 | NESTED LOOPS | | 841 |

| 3 | NESTED LOOPS | | 831 |

|* 4 | HASH JOIN ANTI | | 831 |




| 8 | VIEW | VW_SQ_1 | 30M|

|* 9 | HASH JOIN | | 30M|

|* 10 | INDEX FAST FULL SCAN | DMR_CONTENT_S_INDX01 | 27M|

| 11 | TABLE ACCESS FULL | DMR_CONTENT_R | 67M|




12,57min!


Same execution plan on Exadata for effective comparison:optimizer_index_caching=0;

optimizer_index_cost_adj=100;

PROD 12 min 57 sec

Exadata 1 min 21 sec

------------------------------------------------------------------------------


------------------------------------------------------------------------------



| 2 | NESTED LOOPS | | 841 |

| 3 | NESTED LOOPS | | 831 |

|* 4 | HASH JOIN ANTI | | 831 |




| 8 | VIEW | VW_SQ_1 | 30M|

|* 9 | HASH JOIN | | 30M|

|* 10 | INDEX STORAGE FAST FULL SCAN| DMR_CONTENT_S_INDX01 | 27M|

| 11 | TABLE ACCESS STORAGE FULL | DMR_CONTENT_R | 67M|




------------------------------------------------------------------------------

Default optimizer settings

Factor 9 on the

same dataset

with the same

execution plan

Major improvement due to smart scan usage (storage clause)


Change optimizer settingsoptimizer_index_caching=95;

optimizer_index_cost_adj=5;

PROD 4 sec

Exadata 1 sec

Less gain as smart scan is not used

------------------------------------------------------------------------------------


------------------------------------------------------------------------------------


| 1 | NESTED LOOPS | | 4203 |

| 2 | NESTED LOOPS | | 4154 |




| 6 | NESTED LOOPS | | 1 |

| 7 | TABLE ACCESS BY INDEX ROWID| DMR_CONTENT_R | 2 |

|* 8 | INDEX RANGE SCAN | D_1F01D65580000005 | 2 |

|* 9 | INDEX RANGE SCAN | DMR_CONTENT_S_INDX01 | 1 |


|* 11 | INDEX RANGE SCAN | DMI_0301D65580000206_S_INDX04 | 1 |

------------------------------------------------------------------------------------

Factor 4 on the

same dataset

with the same

execution plan

Required by Documentum

We were requested to remove one disk!OEM Alarm

Host=sgexaadm02vm01.customer.ch

Target type=Cluster ASM

Target name=+ASM_cluster-clu1

Categories=Availability

Message=2 disks are offline.

Severity=Critical

Event report

ed time=Apr 15, 2015 10:17:05 AM CEST

Disk re-insert (rebuild)INST_ID GROUP_NUMBER Operation PASS State R-Power A-Power W-Done E-Est

---------- ------------ ---------- --------- ----- -------- ------ -------- -------

2 2 REBAL RESYNC RUN 50 50 31,413 13

2 2 REBAL RESILVER WAIT 50 50 0 0

2 2 REBAL REBALANCE WAIT 50 50 0 0

2 2 REBAL COMPACT WAIT 50 50 0 0

1 2 REBAL RESYNC WAIT 50

1 2 REBAL RESILVER WAIT 50

1 2 REBAL REBALANCE WAIT 50

1 2 REBAL COMPACT WAIT 50

POC Execution and Result: Hardware tests

We were requested to unplug power cable of one storage cell!

OEM AlarmHost=sgexaadm02vm01.customer.ch




Message=Failure Group DATAC1.SGEXACELADM03 is unavailable.

Severity=Critical

Event reported time=Apr 15, 2015 5:00:25 PM CEST





Message=Failure Group RECOC1.SGEXACELADM03 is unavailable.

Severity=Critical






Message=12 disks are offline.

Severity=Critical


After plugging back

power cable, rebuild

starts few minutes

after…

POC Execution and Result: Hardware tests

POC Execution and Result: Conclusions

OLTP Batch– Significant gain even after huge vCPU reduction

– No I/O wait events

Documentum– Major improvement when smart scan is used

– Better system stability even with default optimizer settings not allays aligned with

vendor requirements

– Performance increase with a factor from 4 to 9 depending if smart scan is use or not

Hardware tests– Storage protection tested and verified as requested!

Proposed Architecture

Exadata X5-2 based architecture

Customer constraints

Isolation– Secure maintenance operation

– Control and adjust resource allocation

Continuity– No high availability required, Data Guard protection is enough

– Full capacity usage, distribute production database between the two data centers

Performance– Increase performances in particular for Documentum

– New application will come soon

Licensing– Optimize and control licensing

Propose a five years projection plan to absorb future growth

DBServer1 DBServer2

PROD1PROD2

PROD3 PROD5PROD6

PROD7

QAS1QAS2

INT2

DEV1DEV2

DRP1

IO Resource Manager: Category, Inter-Database, intra-Database (db_unique_name unique on all VClusters)

INT1

DRP1

QAS3QAS4

INT4

DEV3DEV4

DRP3

INT3

DRP4

10 vCPUs10 vCPUs

4 vCPUs 4 vCPUs 4 vCPUs4 vCPUs

2 vCPUs2 vCPUs

vClu2

vClu1 PRD

QA/DEVvClu3

vClu4INT

DRP

data

fra

data

fra

StorageServer1 StorageServer2 StorageServer3HD1 HD2 HD3 HD4 HD5 HD6 HD1 HD2 HD3 HD4 HD5 HD6 HD1 HD2 HD3 HD4 HD5 HD6

data

fra

data

fra

Exadata X5-2 based architecture with OVM

In total:

40 vCPUs for production databases

16 vCPUs for DEV/QA databases

16 vCPUs for INT databases

8 vCPUs for DRP databases

No additional licenses to purchase

Fix every VMs to max 14 vCPUs (to adjust power on demand)

Exadata X5-2 OVM Oracle infrastructure

Environment Exadata Storage Required Cores/Server Max Cores/Server Total Cores Threads CPU to License

PRD,INT,QAS,DEV,DRP I 30 TB 18 10 20 40 10

PRD,INT,QAS,DEV,DRP II 30 TB 18 10 20 40 10

Total 60 TB 25 TB 40 80 20

CPU, storage and licensing


Dynamic host cpu reconfiguration using: xm vcpu-set

Dynamic oracle CPU_COUNT adjustment as of Oracle Oracle 12c

– Dynamic resource management update

Adjust power on demand: MAX 14 vCPUs per VM

PROD

DBServer1

QA DEV DR

14 vCPUs

6 vCPUs

2 vCPUs

mini


5 years projection plan

Five Years Projection Plan

The five years projection plan is based on customer estimation with:

Up to 15% of storage increase per year

and

Up to 5% of processing increase per year

Solution: Exadata 1/8 de Rack with all Cores activated

In total:

72 vCPUs for production databases

24 vCPUs for DEV/QA databases

24 vCPUs for INT databases

16 vCPUs for DRP databases

Buy 14 additional EE + Options CPU licenses to fit the needs

Fix every VMs to max 28 vCPUs (to adjust power on demand)

Five Years Projection Plan

Exadata X5-2 OVM Oracle infrastructure

Environment Exadata Storage RequiredCores/Server

MaxCores/Server Total Cores Threads CPU to License

PRD,INT,QAS,DEV,DRP I 32 TB 50 TB 18 17 (18-1) 34 68 17

PRD,INT,QAS,DEV,DRP II 32 TB 50 TB 18 17 (18-1) 34 68 17

Total 64 TB 50 TB 68 136 34

CPU, storage and licensing

.

Conclusions

The proposed solution responds perfectly to customer requirements on all areas

Environment Isolation

Performance

Capacity usage and workload adjustments

Disaster recovery

Licensing optimization

Fulfil five year projection plan requirements

Questions…Jacques Kostic

Senior Consultant IMS Lausanne

Tél. +79 909 72 63

[email protected]

Technology

PoC Oracle Exadata - Retour d'expérience