ORACLE 11g AND 12c DATABASE CONSOLIDATION AND ...

6 downloads 5379 Views 5MB Size Report
Trademarks on EMC.com. Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC. XtremIO 4.0 Solution Guide. Part H13868.3 ...
Solution Guide

ORACLE 11g AND 12c DATABASE CONSOLIDATION AND WORKLOAD SCALABILITY WITH EMC XTREMIO 4.0  Consolidation—Oracle Production/Test/Dev/Reporting workloads in

physical and virtual environments  Simplicity—Easy Test/Dev/Reporting environments provisioning  Compatibility—XtremIO Compression with Oracle Advanced Compression 

EMC Solutions Abstract This solution guide describes how the EMC XtremIO™ all-flash array enables easy consolidation and storage provisioning for physical and virtualized environments with Prod/Test/Dev and Reporting workloads on one XtremIO array with Oracle RAC 11g and 12c databases. October 2015

Copyright © 2015 EMC Corporation. All rights reserved. Published in the USA. Published October 2015 EMC believes the information in this publication is accurate as of its publication date. The information is subject to change without notice. The information in this publication is provided as is. EMC Corporation makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose. Use, copying, and distribution of any EMC software described in this publication requires an applicable software license. EMC2, EMC, and the EMC logo are registered trademarks or trademarks of EMC Corporation in the United States and other countries. All other trademarks used herein are the property of their respective owners. For the most up-to-date listing of EMC product names, see EMC Corporation Trademarks on EMC.com. Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide Part H13868.3

2

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Contents

Contents Chapter 1

Executive Summary

6

Executive summary..................................................................................................... 7 Business case........................................................................................................ 7 Solution overview .................................................................................................. 7 Document purpose ................................................................................................ 8 Scope .................................................................................................................... 9 Audience ............................................................................................................... 9

Chapter 2

Solution Architecture

10

Solution architecture ................................................................................................ 11 Overview.............................................................................................................. 11 Solution architecture diagram .............................................................................. 12 Hardware resources ............................................................................................. 14 Software resources .............................................................................................. 14 Storage layer: EMC XtremIO 4.0 ................................................................................ 15 XtremIO features .................................................................................................. 15 Storage configuration .......................................................................................... 16 Storage design..................................................................................................... 16 Virtualization layer: ESXi and virtual machine ........................................................... 17 Oracle Database ....................................................................................................... 17

Chapter 3

Use Case 1: Easy Provisioning

18

Use case 1: Easy provisioning and reducing management efforts in the database lifecycle ............................................................................................................. 19 Description .......................................................................................................... 19 Configuration ....................................................................................................... 19 Testing detail ....................................................................................................... 21 Use case summary ............................................................................................... 21

Chapter 4

Use Case 2: Workload Consolidation

22

Use case 2: Consolidating production, test/dev, and reporting workloads onto the same XtremIO array............................................................................................ 23 Description .......................................................................................................... 23 Configuration ....................................................................................................... 24 Testing detail ....................................................................................................... 26 Results ................................................................................................................ 28 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

3

Contents

Use case summary ............................................................................................... 50

Chapter 5

Use Case 3: Validating Compression Methods

52

Use case 3: Validating how XtremIO compression works with Oracle Advanced Compression ..................................................................................................... 53 Description .......................................................................................................... 53 Configuration ....................................................................................................... 54 Testing detail ....................................................................................................... 55 Results ................................................................................................................ 56 Use case summary ............................................................................................... 62

Chapter 6

Conclusion

64

Conclusion ............................................................................................................... 65 Summary ............................................................................................................. 65 Findings ............................................................................................................... 65

Chapter 7

References

67

References ............................................................................................................... 68 EMC documentation ............................................................................................ 68 Oracle documentation ......................................................................................... 69 VMware documentation ....................................................................................... 69

Appendix A SLOB Configuration Parameters

70

SLOB configuration parameters ................................................................................ 71

Appendix B Provisioning Storage Volumes for a Virtualized Database

73

Provisioning storage volumes for the virtualized Oracle databases in their lifecycle . 74 Virtualized Oracle database initial deployment – Provisioning virtual machine .... 74 Virtualized Oracle database initial deployment – Provisioning VMFS datastores .. 78 Virtualized Oracle database initial deployment – Creating virtual disks ............... 85 Adding storage volumes to virtualized Oracle database – Provisioning VMFS datastores..................................................................................................... 92 Adding storage volumes to virtualized Oracle database – Creating virtual disks 100

Appendix C Provisioning Storage Volumes for a Physical Oracle Database106 Provisioning storage volumes for physical Oracle databases in their lifecycle .........107 Physical Oracle database initial deployment –Provisioning storage volumes .....107 Adding storage volumes to physical Oracle database – Provisioning storage volumes ......................................................................................................119

Appendix D ESX Host Performance Data

125

Viewing virtual machine, ESXi hosts, and ASM diskgroups settings ........................126 Viewing ESXi host performance information ...........................................................131

4

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Contents

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

5

Chapter 1: Executive Summary

Chapter 1

Executive Summary

This chapter presents the following topics:

Business case ............................................................................................................ 7 Solution overview ...................................................................................................... 7 Document purpose ..................................................................................................... 8 Scope ......................................................................................................................... 9 Audience .................................................................................................................... 9

6

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 1: Executive Summary

Executive summary Business case

Business needs are driving data growth, in both volume and velocity, more than ever before. At the same time, the need to quickly convert the data into useful information that reveals business opportunities and risks is also growing. Relational databases, such as Oracle Database 12c, are used to support these business-critical applications. To deliver faster response times across the range of applications, these databases require storage designed for both low-latency transactional input/output (I/O) and high-throughput analytic workloads. Virtualization enables greater consolidation of various types of database workloads. Often, because of consolidation, both online transaction processing (OLTP) and online analytical processing (OLAP) workloads share the same physical servers and storage. Therefore, to enable optimal performance, the underlying storage infrastructure must also be designed to handle these varying workloads within a consolidated infrastructure. Oracle databases consume too much time for database administrators, storage teams, and application owners striving to optimize end-to-end performance through complex database layout and configuration. This issue is more severe in mixed workload environments with OLTP, data warehouses, reports, analytics or test and development (test/dev). This problem is illustrated in the 2014 IOUG survey, “Efficiency Isn’t Enough,” which shows that 48 percent of the DBA’s weekly time is spent doing performance diagnostics on the database environments and 37 percent of the DBA’s weekly time is spent creating Oracle database copies. The EMC® XtremIO™ all-flash array addresses the effects of virtualization on I/Ointensive database workloads with impressive random I/O performance and ultra-low latency. This applies equally to OLTP and OLAP workloads and to the consolidation of multiple workloads onto a common storage platform. XtremIO also provides new levels of speed and provisioning agility to virtualized environments with spaceefficient virtual copies, inline data reduction, and accelerated provisioning and compression. The results include breakthrough simplicity for storage management and provisioning, and new capabilities for real-time analytics and test/dev cycles.

Solution overview

This solution demonstrates the benefits of deploying different versions of Oracle databases in both physical and virtual environments using VMware vSphere and XtremIO 4.0 storage. Once Oracle databases are deployed in production, it becomes necessary to copy instances for functions such as reporting, analytics, and test/dev. Traditional storage cannot deliver all these copies from a single platform with consistent performance and data reduction. XtremIO enables easy, on-demand, memory-based virtual copy creation and fast virtual machine provisioning from templates. XtremIO gives Oracle customers one platform with radical simplicity and no tuning for all workloads. Every database has all-flash arrays for predictable performance and low latency. XtremIO pioneered the concept of integrated copy data management (iCDM), which provides the ability to consolidate both primary data and its associated copies on the same scale-out all-flash array. With its consistent IOPS and latency, and its ability to

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

7

Chapter 1: Executive Summary

scale out more performance as needed with no application downtime, XtremIO delivers incredible performance to production and non-production applications, without impacting the production SLAs. Building on that unique performance foundation, XtremIO arrays leverage XtremIO Virtual Copy (XVC) technology. XVC abstracts the copy operations as unique inmemory metadata operations with no impact on any back-end resources. XVC allows instant, high performance copies of any data set of any application, in nearly any quantity desired. XVC is entirely space-efficient, with data services like inline deduplication and compression, and has no impact on production or other copies. XtremIO iCDM integrates with key applications by providing copies that are immediately useable by the application, enabling fast deployment, and ensuring those deployed copies are fully functional. XtremIO iCDM integrates easily into rich application workflows by making a development copy, instantly refreshing it to the latest production data, pushing the development copy to a QA host, pushing the QA copy to a scalability testbed, and rolling the output back into production. For analytics processes, production data can be extracted and pushed to all downstream analytics applications on-demand as a simple in-memory operation. XtremIO offers this workflow and scheduling CDM as both self-service and automated workflows for both application and infrastructure teams. As a no-cost, array-based application service, iCDM is unique to XtremIO and finally solves the most intractable CDM challenges: 

Copies are space-efficient, high-performance, and nearly unlimited



Copies can be instantly created, refreshed, deleted or recovered



Copy operations do not affect other copies or risk their SLAs



Copies are managed through a flexible, integrated application workflow

Note: For an in-depth review in an Oracle environment, see the following EMC whitepaper:

XtremIO Snapshot Refresh For Oracle Database Production Development And Test

The benefits of the solution include: 

Simplify database consolidation–Consolidate mixed-workload, test/dev, and reporting databases on a single storage platform with performance at scale. Response times remain consistently fast in both physical and virtual environments.



Maximize DBA productivity–Simplify provisioning of new Oracle databases. Reduce downtime for capacity planning and growth management. Accelerate Oracle Test/Dev and Reporting database provisioning with XtremIO snapshots.



Gain greater business agility–Provision business environments faster, consolidate business environments (prod/dev/test/patch/reporting) on a single storage platform that improves business efficiency.

Document purpose This solution guide highlights: 

8

EMC XtremIO performance optimization in both virtualized and physical Oracle databases including 11g and 12c

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 1: Executive Summary



Multiple physical and virtualized Oracle databases running Silly Little Oracle Benchmark (SLOB) random I/O workloads



The impact of Oracle’s Advanced Compression Option (ACO) and XtremIO compression working together



XtremIO snapshot capacity overhead, and its performance impact on a random I/O workload

Note: For more information about SLOB, refer to xtremio.com/slob and kevinclosson.net/slob

Scope

Audience

This solution guide describes the solution architecture and the procedures that were used to validate the following use cases: 

Easy storage provisioning in the lifecycle of the Oracle databases



Consolidation of various Oracle database workloads on the same XtremIO array



Physical storage capacity overhead of XtremIO snapshots



Use of Oracle Advanced Compression with XtremIO compression

This guide is intended for Oracle database administrators, storage administrators, virtualization administrators, system administrators, IT managers, and any others involved in evaluating, acquiring, managing, maintaining, or operating Oracle database environments.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

9

Chapter 2: Solution Architecture

Chapter 2

Solution Architecture

This chapter presents the following topics:

Solution architecture .................................................................................. 11 Overview ..................................................................................................................11 Solution architecture diagram..................................................................................12 Hardware resources .................................................................................................14 Software resources ..................................................................................................14

Storage layer: EMC XtremIO 4.0 .................................................................. 15 XtremIO features ......................................................................................................15 Storage configuration .............................................................................................. 16 Storage design.........................................................................................................16

Virtualization layer: ESXi and virtual machine ............................................. 17 Oracle Database ......................................................................................... 17

10

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 2: Solution Architecture

Solution architecture Overview

In this solution, the ease of use, versatility, compression characteristics, and snapshot efficiency of the XtremIO array with Oracle 11g and 12c databases are tested using the following use cases: 

Easy storage provisioning for Oracle databases We show the steps required to provision storage from VMware vSphere Client with the VSI plugin installed, and steps required to provision storage from the XtremIO management console, demonstrating how easy it is to provision storage for physical and virtualized Oracle databases in their lifecycle. 

This solution guide documents the steps required to provision storage volumes for Oracle database during the initial deployment.



This solution guide documents the steps required to add storage volumes to an Oracle database as more storage space is required.

See Use case 1: Easy provisioning and reducing management efforts in the database lifecycle. 

Consolidate Oracle production, test/dev, and reporting database workloads on the same array We demonstrate how to consolidate the test/dev and reporting workloads with the production workload on the same array, and validate the resulting performance impact and the physical storage space overhead: 

Provision five virtualized Oracle test/dev databases using EMC AppSync® and validate the physical storage capacity overhead, then run the test/dev workload on these databases to validate the performance impact on the production workload.



Provision three virtualized Oracle reporting databases using AppSync and run the reporting workload on these databases to validate the performance impact on the production workload.



Run the production and test/dev workloads simultaneously, and add one reporting workload at each run to validate the performance impact on the production workload.

AppSync is advanced copy management software for EMC storage arrays that offers a better way to manage the protection, replication, and cloning of critical applications and databases. For more detailed information, refer to the EMC AppSync User and Administration Guide and the white paper Oracle 11g and 12c Database Consolidation and Workload Scalability with XtremIO 3.0 in the References section. For an in-depth analysis of the new functions of the XtremIO 4.0 snapshot/virtual copy feature, please refer to the paper XtremIO Snapshot Refresh for Oracle Database Production Development and Test in the References section. See Use case 2: Consolidating production, test/dev, and reporting workloads onto the same XtremIO array. Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

11

Chapter 2: Solution Architecture



Validating XtremIO compression with Oracle Advanced Compression We deployed a virtualized Oracle 12c R1 single instance database. In the database, we created four 1 TB tablespaces on separate ASM diskgroups. The same Swingbench1 Order Entry schema data was loaded into each of the tablespaces. 

The segments on the first tablespace were populated without Oracle Advanced Compression enabled, and the tables were created with PCTFREE=10.



The segments on the second tablespace were populated with Oracle Advanced Compression enabled, and the tables were created with PCTFREE=10.



The segments on the third tablespace were populated without Oracle Advanced Compression enabled, and the tables were created with PCTFREE=0.



The segments on the fourth tablespace were populated with Oracle Advanced Compression enabled, and the tables were created with PCTFREE=0.

We then validated the compatibility of XtremIO compression and Oracle Advanced Compression in terms of the physical capacity of the array and OLTP workload performance. See Use case 3: Validating how XtremIO compression works with Oracle Advanced Compression. Note: PCTFREE is a storage parameter of the database table that is essential to how the database manages free space. This parameter sets the minimum percentage of a data block reserved as free space for updates to existing rows. Thus, PCTFREE is important for preventing row migration and for avoiding wasted space.

Solution architecture diagram

Figure 1 shows the architecture of the solution, which is composed of the following layers: 

Compute layer—Consists of four servers. Two of them are installed and configured as a VMware ESXi 6.0 cluster and two are physical servers.



Network layer—Consists of two 1GbE IP switches that are used for public network, two 10GbE IP switches that are used for private network, and two director-class SAN switches that are used for storage network. The SAN switches are designed for deployment in storage networks supporting virtualized data centers and enterprise clouds.



Storage layer—Consists of an XtremIO array that is configured with two XBricks™ providing 30.49 TB of usable physical capacity. The storage layer also includes the XtremIO Storage Management Application, a powerful and visually intuitive XtremIO system dashboard that you can use to view the performance, capacity, and system health of the array.

1

For more information about Swingbench, refer to http://dominicgiles.com/swingbench.html.

12

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 2: Solution Architecture

Figure 1.

Solution architecture

Two physical servers are used to create the two-node Oracle 12c RAC database with the production workload running on it. Two other servers are configured as the VMware ESXi HA cluster on which VMware vSphere virtual machines were created.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

13

Chapter 2: Solution Architecture

Hardware resources

Table 1 lists the hardware resources used in this solution. Table 1.

Hardware resources

Hardware

Quantity

Configuration

Storage array

1

XtremIO array consisting of four X-Bricks

Servers

4

16 cores, 2.9 GHz processors, 512 GB RAM, including:  1 x 1 Gb Ethernet (GbE) network interface card (NIC)  2 x 10 GbE NIC

Software resources

14

LAN switches

2

1 GbE

Cluster interconnect switches

2

10 GbE

SAN switches

2

FC

Table 2 lists the software resources used in this solution. Table 2.

Software resources

Software

Version

Notes

VMware vSphere

6.0

Hypervisor hosting all virtual machines

VMware vCenter

6.0

vSphere manager

Oracle Enterprise Linux

6.5

Operating system for database servers

Oracle Database 12c Release 1

Enterprise Edition 12.1.0.2

Database

Oracle Grid Infrastructure 12c Release 1

Enterprise Edition 12.1.0.2

Clusterware with ASM for volume management

Oracle Database 11g Release 2

Enterprise Edition 11.2.0.4

Database

Oracle Grid Infrastructure 11g Release 2

Enterprise Edition 11.2.0.4

Clusterware with ASM for volume management

Silly Little Oracle Benchmark (SLOB)

2.2

Random I/O benchmark tool

XIOS

4.0.0-58

XtremIO operating system

Swingbench

2.5.0.952

OLTP benchmark tool

EMC PowerPath®

5.7 SP 5 (build 2)

Multipathing software used in physical environment

EMC PowerPath/VE

5.9 SP 1 (build 54)

Multipathing software used in virtualized environment

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 2: Solution Architecture Software

Version

Notes

AppSync

2.2.1.0

Advanced copy management software for EMC storage arrays

Oracle Enterprise Manager Cloud Control

12.1.0.5

Oracle management tool

EMC Virtual Storage Integrator (VSI) Plugin

6.5.1.18

A plugin for managing storage from vSphere client

VMware vSphere PowerCLI

5.5.0

A command-line tool that can automate all aspects of vSphere management

Storage layer: EMC XtremIO 4.0 XtremIO features

This solution demonstrates the following XtremIO features: 

Inline data reduction—XtremIO ensures that duplicate data blocks never translate into physical data writes and are replaced with in-memory metadata pointers that allow a single physical block on a Solid State Drive (SSD) to be referenced multiple times.



Thin provisioning—XtremIO allocates capacity to volumes on demand in finegrained increments. This feature automatically matches capacity allocations to host, operating system, and application demands for maximum efficiency without any post-reclamation operations or any impact on performance.



Inline data compression—XtremIO automatically compresses data after all duplications have been removed. This feature ensures that the compression is performed only for unique data blocks. Data compression is performed in real time and not as a post-processing operation.



XtremIO snapshots—XtremIO snapshot technology is implemented by using the content-addressing capabilities of the array and the in-memory and dual-stage metadata of the system. XtremIO snapshots are optimized for SSD media with a unique metadata tree structure that directs I/Os to the right data timestamp.



Fault protection—XtremIO delivers reliability and availability with redundant components and the ability to tolerate any component failure without a loss of service.



Scale-out—With the combination of the X-Brick building block, the Remote Direct Memory Access (RDMA) Infiniband fabric combined with in-memory global metadata, and the XtremIO operating system (XIOS) software, XtremIO enables linear increase in both aggregate capacity and aggregate performance with every additional X-Brick in the cluster.



In-memory metadata operations—XtremIO leverages its multi-controller scaleout design and direct memory-to-memory RDMA fabric to maintain all metadata in memory. Heavy metadata operations such as inline deduplication, thin provisioning allocations, and internal array copy operations are conducted

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

15

Chapter 2: Solution Architecture

entirely in memory, at instantaneous speed, without impacting I/O to the SSD portion of the array. 

Storage configuration

XtremIO data protection (XDP)—The XtremIO data protection scheme is different from traditional RAID protections. It provides high efficiency, "self-healing", and double-parity data protection. The cluster requires very little capacity overhead for data protection and metadata space.

For this solution, the XtremIO array was deployed in a two X-Brick configuration, with built-in, redundant, 40 Gb/s QDR InfiniBand switches providing back-end connectivity between the storage controllers. This ensures a highly available, ultralow latency RDMA network. With XtremIO data protection, XtremIO requires far less reserved capacity for data protection, metadata storage, snapshots, spare drives, and performance, leaving much more space for user data. This lowers the cost per usable GB. In this two X-Brick configuration, the XtremIO cluster was configured with one hundred 800 GB SSDs, which provides 30.49 TB of usable physical space.

Storage design

Traditional storage designs for an Oracle Database use multiple RAID groups of different drive types that are created with different levels of protection for each drive type. Protection is distributed across multiple controllers. With XtremIO, all drives are under XDP protection, which uses both the random access nature of flash and the unique XtremIO dual-stage metadata engine. This means that data blocks in the array are distributed evenly across the X-Bricks to maintain consistent performance, extending the longevity of the flash drives. Databases generate both random and sequential I/O, as shown in Figure 2. With XtremIO, these are treated equally, because data is randomized and distributed in a balanced fashion throughout the array.

Figure 2.

Database front-end random and sequential I/O

For more information about EMC XtremIO 4.0, refer to the References section of this solution guide.

16

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 2: Solution Architecture

Virtualization layer: ESXi and virtual machine The choice of a server platform for a virtualized infrastructure is based on both the supportability of the platform and the technical requirements of the environment. In production environments, the servers must have: 

Sufficient cores and memory to support the required number and workload of the virtual machines



Sufficient connectivity, both Ethernet and FC, to enable redundant connectivity to the IP and storage network switches



Sufficient capacity to withstand a server failure and support failover of the virtual machines

In this solution, we used two physical servers configured as a vSphere HA cluster, with each server running vSphere ESXi 6.0. We then deployed virtual machines to create multiple virtualized Oracle databases with different versions/releases, including both 11g and 12c. For further information about recommended practices for VMware virtualization, refer to the References section.

Oracle Database For this solution we2 created two sets of databases as follows: 

Physical databases—One 1 TB two-node Oracle 12cR1 RAC database used as the production database



Virtualized databases—Eleven single-instance databases were created for different test scenarios: 

One 1 TB 11g R2 database used as a production database



One 1 TB 12c R1 database used as a production database



Five 12c R1 databases were created from XtremIO snapshots of the physical RAC production database and used for development and testing purposes



Three 12c R1 databases was created from XtremIO snapshots of the physical RAC production database and used for reporting purposes



One 12c R1 database was used to validate the interoperability of XtremIO compression and Oracle Advanced Compression

Note: The production databases used for either the physical or virtual environments were configured with archive logging enabled to simulate real-world cases.

Oracle Best Practices with XtremIO provides more information about configuring Oracle databases on XtremIO. 2

“We” refers to the EMC Engineering Team who tested the solution.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

17

Chapter 3: Use Case 1: Easy Provisioning

Chapter 3

Use Case 1: Easy Provisioning

This chapter presents the following topics:

Description ..............................................................................................................19 Configuration ...........................................................................................................19 Testing detail ...........................................................................................................21 Use case summary ...................................................................................................21

18

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 3: Use Case 1: Easy Provisioning

Use case 1: Easy provisioning and reducing management efforts in the database lifecycle Description

In this use case, we demonstrate how to provision storage volumes for various versions of Oracle databases in their lifecycle. This includes: 

Provisioning storage volumes for the VMware virtualized Oracle single instance databases through the vSphere Web Client, which has the EMC VSI plugin installed in it.



Provisioning storage volumes for the physical Oracle RAC database through the XtremIO management console.

For more information about EMC VSI, refer to the References section of this solution guide. Figure 3 shows the logical architecture for use case 1.

Figure 3.

Configuration

Logical architecture for provisioning storage volumes for Oracle databases

In this use case, we deployed a two-node Oracle 12cR1 RAC database on two physical servers. We also deployed a virtualized Oracle 11gR2 single instance database and a virtualized Oracle 12cR1 single instance database on the VMware ESXi HA cluster. The XtremIO volumes and ASM disk groups that were used for each of the physical and virtualized configurations are shown in Table 3.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

19

Chapter 3: Use Case 1: Easy Provisioning

Table 3.

XtremIO volumes and ASM disk groups used by the physical and virtualized databases

ASM disk groups

Volume size (GB)

Number of volumes

+DATA

300

4

Used for data files, temp files, and control files

+REDO

40

2

Used for online log files

+FRA

500

2

Used for archived log files

Description

In addition to the storage volumes allocated for the virtualized databases, we also created a 2TB volume from the XtremIO array and formatted it as a Virtual Machine File System (VMFS) datastore, which was used to store the virtual machines and the templates. Note: All volumes created from XtremIO and attached to the ESXi hosts support Hardware Acceleration: VMware vSphere Storage APIs – Array Integration (VAAI). For more information about VAAI, refer to the References section.

Table 4 and Table 5 detail the virtual machine templates created in this use case. Table 4.

Oracle 12cR1 virtual machine template configuration

Part

Description

CPU

16 vCPUs (Cores per Socket=1)

Memory

40 GB

Operating system

Oracle Enterprise Linux 6.5

Kernel

3.8.13-16.2.1.el6uek.x86_64

Virtual network interfaces

Eth0: Public/management IP network

Software preinstalled

 Oracle 12.0.1.2 Grid Infrastructure software (for standalone server)  Oracle 12.0.1.2 Database software

RPM packages installed (as Oracle prerequisites)

As specified in the Oracle installation guide

Disk configuration

100 GB virtual disk for root, /tmp, swap space, and Oracle Database binaries

Table 5.

20

Oracle 11gR2 virtual machine template configuration

Part

Description

CPU

16 vCPUs (Cores per Socket=1)

Memory

40 GB

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 3: Use Case 1: Easy Provisioning Part

Description

Operating system

Oracle Enterprise Linux 6.5

Kernel

3.8.13-16.2.1.el6uek.x86_64

Virtual network interfaces

Eth0: Public/management IP network

Software preinstalled

 Oracle 11.2.0.4 Grid Infrastructure software (for standalone server)  Oracle 11.2.0.4 Database software

Testing detail

RPM packages installed (as Oracle prerequisites)

As specified in the Oracle installation guide

Disk configuration

100 GB virtual disk for root, /tmp, swap space, and Oracle Database binaries

Details about how to provision storage volumes for a virtualized database are covered in Appendix B. Details about how to provision storage volumes for a physical Oracle database are covered in Appendix C.

Use case summary

In this use case, we demonstrated the following: 

With the VSI plug-in installed, you can provision XtremIO storage for the VMware virtualized Oracle databases from the vSphere Web Client, which reduces the storage management effort required in the lifecycle of the virtualized databases.



You can Provision storage for physical Oracle databases from the XtremIO management console. It is simple and intuitive, which reduces the storage management effort required in the lifecycle of the physical databases.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

21

Chapter 4: Use Case 2: Workload Consolidation

Chapter 4

Use Case 2: Workload Consolidation

This chapter presents the following topics:

Description ..............................................................................................................23 Configuration ...........................................................................................................24 Testing detail ...........................................................................................................26 Results .....................................................................................................................28 Use case summary ...................................................................................................50

22

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Use case 2: Consolidating production, test/dev, and reporting workloads onto the same XtremIO array Description

In this use case, we used the physical Oracle 12cR1 RAC database that we deployed in use case 1 as the production database. We validated the performance impact on the production workload as consolidating different types of workloads running together, including test/dev and reporting. The test/dev and reporting workloads were running on the snapshots created against the production volumes. We also validated the storage space efficiency of XtremIO Virtual Copy which was used to make copies of the production database and provision the test/dev databases. In this use case, we also showed how to view the virtual machine and ESXi host settings and related performance information, and how to view the ASM disk/diskgroup settings. Figure 4 shows the logical architecture of this use case.

Figure 4.

Logical architecture for consolidating physical and virtual databases on the same array

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

23

Chapter 4: Use Case 2: Workload Consolidation

Configuration

In this use case, we created a VMware virtual machine template with a 12c Oracle database software installed. Table 6 shows the virtual hardware resources that were allocated and the system configurations of the template that was used for the virtualized test/dev and reporting databases. Table 6.

24

Virtual machine template configuration for test/dev and reporting databases

Part

Description

CPU

4 vCPUs (Cores per Socket=1)

Memory

40 GB

Operating system

Oracle Enterprise Linux 6.5

Kernel

3.8.13-16.2.1.el6uek.x86_64

Virtual network interfaces

Eth0: Public/management IP network

Software preinstalled

Oracle Grid Infrastructure (for standalone server) Oracle Database

RPM packages installed (as Oracle prerequisites)

As specified in the Oracle installation guide

Disk configuration

100 GB virtual disk for root, /tmp, swap space, and Oracle Database binaries

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Table 7, Table 8, and Table 9 detail the workload profiles for this use case. Table 7.

Production workload profile

Profile characteristic

Details

Database type

OLTP

Oracle Database

Physical two-node Oracle 12cR1 RAC database on ASM

Instance configuration

SGA size: 16 GB Note: Because a larger database cache will buffer more data, we configured a very small buffer cache to generate a stable and high physical I/O workload.

I/O profile

SLOB random I/O workload with 80:20 read/write ratio and SLOB execution think time enabled. Refer to Appendix A for a full list of SLOB configuration parameters used during the tests.

Database block size

8 KB

Table 8.

Simulated test/dev workload profile

Profile characteristic

Details

Database type

OLTP

Oracle Database

Virtualized Oracle 12cR1 single instance database on ASM

Instance configuration

SGA size: 16 GB Note: Because a larger database cache will buffer more data, we configured a very small buffer cache to generate a stable and high physical I/O workload.

I/O profile

SLOB random I/O workload with 80:20 read/write ratio and SLOB execution think time enabled. Refer to Appendix A for a full list of SLOB configuration parameters used during the tests.

Database block size

8 KB

Table 9.

Simulated reporting workload profile

Profile characteristic

Details

Database type

OLAP

Oracle Database

Virtualized Oracle 12cR1 single instance database on ASM

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

25

Chapter 4: Use Case 2: Workload Consolidation Profile characteristic

Details

Instance configuration

SGA size: 16 GB Note: Because a larger database cache will buffer more data, we configured a very small buffer cache to generate a stable and high physical I/O workload.

Testing detail

I/O profile

SLOB read only sequential I/O workload. Refer to Appendix A for a full list of SLOB configuration parameters used during the tests.

Database block size

8 KB

We used the following steps to validate the consolidation of production, test/dev, and reporting workloads on the same XtremIO storage array: 1.

To get the performance baseline, we ran the production workload on the physical two-node Oracle RAC database that was deployed in Use Case 1.

2.

We deployed five virtual machines from template, as shown in Table 6.

3.

We captured a screenshot of the XtremIO management console to show the current space usage of the array.

4.

We created a first-generation copy and five second-generation copies of the production database using AppSync.

5.

We captured another screenshot of the XtremIO management console to illustrate the current space usage of the XtremIO array after a total of six copies of the production database were created.

6.

To provision the test/dev databases, we mounted the five second-generation copies to the five virtual machines separately using AppSync.

7.

To simulate the real world test/dev environments in which the resources allocated are strictly throttled to prevent the impact of erroneous operations, we configured Linux control groups (cgroups) to limit the total read IOPS to 4,800 for each test/dev database.

8.

We ran the simulated test/dev workloads to these five test/dev databases, and simultaneously ran the same production workload against the physical production database.

9.

We collected the performance statistics from the Oracle AWR reports and XtremIO management console.

10. We deployed three virtual machines as reporting servers from the template as shown in Table 6. 11. To provision the reporting databases, we created three second-generation copies of the production databases and mounted them to the three reporting virtual machines using AppSync.

26

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

12. To simulate the real world reporting environments in which the resources allocated are strictly throttled to prevent the impact of erroneous queries, we configured Linux control groups (cgroups) to limit the total read bandwidth to 1,024MB per second for each reporting database. 13. We ran the simulated reporting workloads to the three reporting databases, and simultaneously ran the same production workload against the physical production database. The reporting workloads were added gradually, which means: a.

We ran the reporting workload on one reporting database and ran the same production workload at the same time.

b.

We added one additional reporting database to run two reporting workloads with the production workload.

c.

We added the third reporting database to have three reporting workloads running with the production workload.

d.

We scaled up the production workload with more concurrent users to increase production workload, while running three reporting workloads.

14. We collected the performance statistics from the Oracle AWR reports and XtremIO management console. 15. We ran the simulated test/dev workloads against the five test/dev databases and simultaneously we ran the same production workload against the physical production database. We also added reporting workloads gradually, which means: a.

We ran the reporting workload on one reporting database, and ran the five test/dev workloads with production workload running together.

b.

We added one additional reporting database to run two reporting workloads with the five test/dev workloads and production workload running together.

c.

We added the third reporting database to get three reporting workloads running with the five test/dev workloads and production workload running together.

d.

We scaled up the production workload with more concurrent users to increase production workload, with three reporting workloads and five test/dev workloads running together.

16. We collected the performance statistics from the Oracle AWR reports and XtremIO management console. Note: Refer to the white paper Oracle 11g and 12c Database Consolidation and Workload Scalability with XtremIO 3.0 in the References section, for details on how to use AppSync to manage XtremIO Virtual Copies.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

27

Chapter 4: Use Case 2: Workload Consolidation

Results

Methodology To validate the space efficiency of the XtremIO array, we used statistics from the dashboard of the XtremIO Storage Management Application, as shown in Figure 5.

Figure 5.

XtremIO dashboard–storage

The results are as follows: 

Volume Capacity (box A) shows the total amount of space taken by all of the volumes created in the array. Note: Volumes in XtremIO array are all thin-provisioned.



28

Physical Capacity Used (box B) shows the amount of physical space allocated in the array; this is the amount of space occupied after compression and deduplication.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation



Volume Capacity used (box C) shows the amount of space allocated in the volumes; this is the amount of space occupied before compression and deduplication.

To validate the performance of the database workloads, we used the following statistics from the Automatic Workload Repository (AWR). Figure 6 shows performance data from the Automatic Workload Repository (AWR) report created during the physical production database performance baseline test.

Figure 6.

System statistics – AWR report

The following key metrics were extracted from the AWR Report, as shown in Figure 6. For SLOB random I/O workload, we used the following metrics to assess the performance: 

Physical read I/O requests shows 49,491 physical write IOPS and corresponds to “Read IOPS” in Table 10.



Physical write I/O requests shows 12,254 physical read IOPS and corresponds to “write IOPS” in Table 10.

For SLOB read only sequential I/O workload, we used the following metric to assess the performance: 

Physical read bytes is 405,439,242 bytes per second, which is 398.7MB/s of database file read I/O bandwidth.

In the test case, we observed the physical read I/O total requests as 49,501 per second, and the physical write I/O total requests as 12,901 per second. There was no RMAN or other extraneous I/O as there might be in real application systems. We disabled Oracle maintenance scheduler jobs, so the number of physical read total I/O Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

29

Chapter 4: Use Case 2: Workload Consolidation

requests is approximately equal to physical read I/O requests, and the number of physical write total I/O requests is approximately equal to physical write I/O requests. The same also applies to performance metric physical read bytes and physical total read bytes. All I/O is important to the DBA, but we focused on application I/O activity as a way to narrow the analysis. In our analysis, we used physical read I/O requests and physical write I/O requests for random I/O workload, using physical read bytes for read only sequential I/O workload, as they only include Application IOs. Test results: storage space efficiency of XtremIO snapshot In this use case, we created six copies of the production database using the XtremIO snapshot feature, including a first-generation copy and five second-generation copies which were snapshots of the first-generation copy (or “snap of a snap”). All these copies of the production database were managed by AppSync. Figure 7 shows the array space usage comparison before and after snapshot copies of the 1 TB production database were created.

Figure 7.

XtremIO array space usage before and after snapshot copies were created

As shown in Figure 7, after we created six snapshot copies of the 1 TB production database, the volume capacity of the array increased by the size of the database multiplied by the number of snapshot copies. However, the amount of used volume capacity and used physical capacity remains the same. When creating snapshots, the XtremIO array only generates a pointer to the ancestor metadata of the actual volume in the array. Copying data or metadata is not required; as a result, there is no physical capacity impact on the array.

30

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Detailed instructions regarding how to view virtual machine, ESXi, and ASM diskgroup settings are shown in Appendix D. Test results: Consolidating production and test/dev database workloads on the same array To validate the performance impact on the production workload as we consolidated the test/dev workloads to the same array, we first ran the production workload on the production database to get a performance baseline. Next, we ran the simulated test/dev workloads on the five test/dev databases, and simultaneously ran the same production workload on the production database. Notice that the Linux cgroup was configured on the test/dev virtual machines to limit the read IOPS issued by each test/dev database. Table 10 shows the performance impact on the production workload as we consolidated the test/dev workloads on the same XtremIO array. Table 10.

AWR performance statistics of consolidating production and test/dev workload Performance data

Performance metric

Baseline

Consolidation

PROD DB

PROD DB

Five test/dev DBs

Read IOPS

49,491

47,967

23,748

Write IOPS

12,254

11,902

5,908

Aggregate IOPS (Write + Read)

61,745

59,869

29,656

As Table 10 shows, when we ran the production workload to get the performance baseline, the production database generated 61,745 aggregate IOPS on the array. When we consolidated the test/dev workloads to the array, the five test/dev databases generated 29,656 aggregate IOPS. Together with the I/Os generated by the production workload, the array served a total of 89,525 aggregate IOPS for these mixed workloads.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

31

Chapter 4: Use Case 2: Workload Consolidation

Show test results from XtremIO GUI Figure 8 shows the XtremIO management console, with the XtremIO cluster-wide IOPS by block size as we ran the production and test/dev workloads in parallel. The production and test/dev databases generated 8KB read and write I/O to the array, and the IOPS number shown in the XtremIO management console fully match with the numbers in AWR report.

Figure 8.

XtremIO array-wide IOPS by block size – consolidating production and test/dev workloads

Figure 9 shows the XtremIO management console, with the XtremIO cluster-wide I/O latency of 8KB read and write I/Os as we ran the production and test/dev workloads in parallel. For write I/Os, the response time was around 500 microseconds. For read I/Os, the response time was around 360 microseconds.

Figure 9.

32

XtremIO arry-wide 8KB IO response time – consolidating production and test/dev workloads

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 10 shows the XtremIO management console, with the XtremIO array CPU utilization. With a total of 89,525 aggregate IOPS generated on the XtremIO array, its CPU utilization was well under 30 percent, which means the array still had room for more workloads to be consolidated.

Figure 10.

XtremIO array CPU utilization–consolidating production and test/dev workloads

Detailed instructions regarding how to show the performance information of the ESXi host on which the test/dev virtualized databases were running when we ran the production and test/dev workloads in parallel are shown in Appendix D. Test results: Consolidating production and reporting database workloads on the same array To validate the performance impact on the production workload as we consolidated the reporting workloads to the same array, we ran the simulated reporting workloads on the reporting databases, and simultaneously ran the same production workload on the production database. Notice that Linux cgroup was configured on the reporting virtual machines to limit the read bandwidth to 1GB/s, which was issued by the reporting database.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

33

Chapter 4: Use Case 2: Workload Consolidation

Table 11 shows the performance impact on the production workload as we consolidated the reporting workloads on the same XtremIO array. Table 11.

AWR performance statistics of consolidating production and reporting workloads–with the same production workload as baseline testing Performance data

Baseline Performance metric

Consolidation

PROD DB (IOPS)

PROD DB (IOPS)

One Reporting DB (MBPS)

PROD DB (IOPS)

Two Reporting DBs (MBPS)

PROD DB (IOPS)

Three Reporting DBs (MBPS)

Read

49,869

46,836

1,021

44,735

2,038

43,468

3,055

Write

12,384

11,635

N/A

11,114

N/A

10,798

N/A

Aggregate IOPS (Write + Read)

62,253

58,471

N/A

55,849

N/A

54,266

N/A

We then increased the production workload and re-ran the production and three reporting mixed workloads testing. Table 12 shows the performance statistics captured. Table 12.

AWR performance statistics of consolidating production and reporting workloads–with increased production workload Performance data Consolidation

Performance metric PROD DB (IOPS)

Three Reporting DBs (MBPS)

Read

52,598

3,060

Write

13,050

N/A

Aggregate IOPS (Write + Read)

65,648

N/A

As shown in Table 11 and Table 12, as we consolidated one reporting workload to the array, the performance of the production workload was slightly impacted, and the aggregate IOPS dropped to 58,471 from 62,253 (6% decreased). It dropped further to 55,849 (10% decreased) and 54,266 (13% decreased) when we added two and three reporting workloads running with the production workload. As we increased the production workload by adding more SLOB sessions with three reporting workloads running together, the aggregate IOPS increased from 54,266 to 65,648. The following figures show the screenshots taken from the XtremIO management console during the mixed workloads including the production workload (scaled up with more concurrent users) and three reporting workloads.

34

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 11 shows the 8 KB read/write IO response time observed for the data volumes of the production database, on which the data files were stored. As shown in the graph, the read and write response times are all less than 1 ms.

Figure 11.

8 KB read/write IO response time of production database data volumes

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

35

Chapter 4: Use Case 2: Workload Consolidation

Figure 12 shows the 8 KB read/write IOPS observed for the volumes of the production database on which the data files were stored. As shown in the graph, the read/write IOPS were evenly distributed across the four volumes.

Figure 12.

36

8 KB read/write IOPS of production database’s data volumes

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 13 shows the IOPS served by the XtremIO array. The production workload was a SLOB random read/write I/O workload, so the production database generated 8KB read/write I/Os to the array. The reporting workload was SLOB sequential read only I/O workload, so the reporting database generated 64KB read I/Os to the array.

Figure 13.

XtremIO array wide IOPS

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

37

Chapter 4: Use Case 2: Workload Consolidation

Figure 14 shows the I/O bandwidth observed for the volumes of the workloads on the three reporting databases.

Figure 14.

38

I/O bandwidth of the three reporting database volumes

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 15 shows the CPU utilization of the XtremIO array as we ran the mixed workloads. It indicated that the array was far from reaching its maximum processing capability, and was able to accommodate more workloads.

Figure 15.

XtremIO array CPU utilization

Figure 16, Figure 17, Figure 18, Figure 19, Figure 20, and Figure 21 show the performance statistics of the ESXi host on which one of the reporting database was running during the mixed workload testing.

Figure 16.

ESXi host overall performance – CPU utilization–production/reporting mixed workload

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

39

Chapter 4: Use Case 2: Workload Consolidation

40

Figure 17.

ESXi host advanced performance – CPU utilization–production/reporting mixed workload

Figure 18.

ESXi host advanced performance–disk read/write rate–production/reporting mixed workload

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 19.

ESXi host advanced performance–disk read/write latency–production/reporting mixed workload

Figure 20.

ESXi host advanced performance–storage adapter read/write rate– production/reporting mixed workload

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

41

Chapter 4: Use Case 2: Workload Consolidation

Figure 21.

ESXi host advanced performance–storage adapter read/write latency – production/reporting mixed workload

To show the performance information of the virtual machine on which the reporting virtualized databases were running when we ran the production and reporting workloads in parallel, click the name of the virtual machine, click the Monitor tab, and then click the Performance tab. Two types of performance information will be shown: Overview and Advanced. Figure 22 shows the Overview performance of virtual machine oracle12c_VM6.

Figure 22.

42

Virtual machine overall performance–CPU utilization–production/reporting mixed workload

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 23, Figure 24, and Figure 25 show the Advanced performance statistics of the virtual machine on which the reporting database was running as we ran the reporting workload on it during the mixed workload testing.

Figure 23.

Virtual machine advanced performance–CPU utilization–production/reporting mixed workload

Figure 24.

Virtual machine advanced performance–disk read/write rate production/reporting mixed workload

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

43

Chapter 4: Use Case 2: Workload Consolidation

Figure 25.

Virtual machine advanced performance–highest latency production/reporting mixed workload

Test results: Consolidating production and test/dev database workloads and reporting database workloads on the same array We also validated the performance impact on the production workload by consolidating the five test/dev workloads and three reporting workloads together on the same array. Notice that Linux cgroup was configured on the reporting virtual machines to limit the read bandwidth to 1 GB/s, which was issued by the reporting database. Linux cgroups were configured on each test/dev virtual machines to limit the total read IOPS to 4,800 for each test/dev database. Table 13 shows the performance impact on the production workload as we consolidated the reporting workload on the same XtremIO array. Table 13.

AWR performance statistics of consolidating production, test/dev and reporting workloads – with the same production workload as baseline testing Performance data Read

Write

Aggregate IOPS (Write + Read)

PROD DB (IOPS)

49,869

12,384

62,253

Five test/dev DBs (IOPS)

23,748

5,908

29,656

PROD DB (IOPS)

45,807

11,380

57,187

Five test/dev DBs (IOPS)

23,523

5,875

29,398

One Reporting DB (MBPS)

1,020

N/A

N/A

Test scenarios

Baseline

Consolidation with one reporting database

44

Databases

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation Test scenarios

Databases PROD DB (IOPS)

43,299

10,732

54,031

Five test/dev DBs (IOPS)

23,500

5,866

29,366

Two Reporting DB (MBPS)

2,041

N/A

N/A

PROD DB (IOPS)

42,765

10,590

53,355

Five test/dev DBs (IOPS)

23,512

5,870

29,382

Three Reporting DB (MBPS)

3,060

N/A

N/A

Consolidation with one reporting database

Consolidation with one reporting database

Performance data

We then increased the production workload and re-ran the production, five test/dev, and three reporting mixed workloads together. Table 14 shows the performance statistics captured. Table 14.

AWR performance statistics of consolidating production, test/dev and reporting workloads – with increased production workload Performance data Consolidation

Performance metric PROD DB (IOPS)

Five test/dev DBs (IOPS)

Three Reporting DBs (MBPS)

Read

57,704

23,551

3,050

Write

14,293

5,880

N/A

Aggregate IOPS (Write + Read)

71,997

29,431

N/A

As shown in Table 13 and Table 14, as we consolidated one reporting workload with the production workload and five test/dev workloads together on the array, the performance of the production workload decreased about 8%, and the aggregate IOPS decreased from 62,253 to 57,187. Aggregate IOPS dropped to 54,031 (13% decreased) and 53,355 (14% decreased) when two and three reporting workloads were running. As we increased the production workload by adding more SLOB sessions with five test/dev workloads and three reporting workloads running together, the aggregate IOPS increased from 53,355 to 71,997. The following figures show the screenshots taken from the XtremIO management console during the mixed workloads including the production workload (scaled up with more concurrent SLOB users), five test/dev workloads, and three reporting workloads. Figure 26 shows the 8KB read/write I/O response time observed for the data volumes of the production database, on which the data files were stored. From the screenshot, the read response time was under 1 ms while the write response time was around 1.1 ms.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

45

Chapter 4: Use Case 2: Workload Consolidation

Figure 26.

46

8KB read/write IO response time of production database data volumes

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 27 shows the 8 KB read/write IOPS observed for the volumes of the production database on which the data files were stored. The read/write IOPS were evenly distributed across the four volumes.

Figure 27.

8KB read/write IOPS of production database data volumes

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

47

Chapter 4: Use Case 2: Workload Consolidation

Figure 28 shows the IOPS served by the XtremIO array. The production and test/dev workloads were SLOB random read/write I/O workloads, so the production database and test/dev databases generated 8KB read/write I/Os to the array. The reporting workload was a SLOB sequential read only I/O workload, so the reporting database generated 64KB read I/Os to the array.

Figure 28.

48

XtremIO array wide IOPS

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

Figure 29 shows the I/O bandwidth observed for the volumes of the three reporting workloads.

Figure 29.

IO bandwidth of the three reporting database volumes

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

49

Chapter 4: Use Case 2: Workload Consolidation

Figure 30 shows the CPU utilization of the XtremIO array as we ran the mixed workloads. It indicates that the array has not yet reached its maximum processing capability, and is able to accommodate more workloads.

Figure 30.

Use case summary

50

XtremIO array CPU utilization

In this use case, we demonstrated the following: 

With AppSync, XtremIO snapshots provide a simple and fast way to provision test/dev, reporting, and non-production environments.



XtremIO snapshots are storage-efficient. As shown in this use case, provisioning five 1 TB test/dev databases using XtremIO snapshot consumes no physical storage capacity in the array.



The provisioning of virtualized test/dev and reporting Oracle databases can be accelerated by XtremIO snapshot, XtremIO VAAI support, and VMware templates, while the storage footprint and performance impact on the source database are reduced to a minimum.



The simulated test/dev workloads running on the provisioned test/dev databases have little to no performance impact on their source database.



The performance impact on the production workload is very little (less than 6%) if one simulated reporting workload running together with production workload. The performance of the production workload is impacted less than 13% if two or three reporting workloads running together with the production workload. But as long as we increase the production workload the IOPS is also increased while keeping the response time less than 1 ms.



When the production workload and five test/dev workloads are running together, with one reporting workload involved at the same time, the performance impact on the production workload is little (less than 8%). If two

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 4: Use Case 2: Workload Consolidation

or three reporting workloads are involved, the impact to the production workload is less than 14%. As long as we increase the production workload the IOPS is increased as well while keeping the read response time at less than 1 ms and write response time around 1ms.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

51

Chapter 5: Use Case 3: Validating Compression Methods

Chapter 5

Use Case 3: Validating Compression Methods

This chapter presents the following topics:

Description ..............................................................................................................53 Configuration ...........................................................................................................54 Testing detail ...........................................................................................................55 Results .....................................................................................................................56 Use case summary ...................................................................................................62

52

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 5: Use Case 3: Validating Compression Methods

Use case 3: Validating how XtremIO compression works with Oracle Advanced Compression Description

In this use case, we tested how, under different database table PCTFREE settings, the performance of OLTP workload and physical storage capacity was impacted when we enabled or disabled Oracle Advanced Compression Option (ACO) on the database segments stored on the XtremIO array. We created four tablespaces on separate ASM diskgroups, loaded the same Swingbench Order-Entry schema data separately into the four tablespaces with their different segment settings that are enabled with or without Oracle ACO compression, and set the values (10 or 0) to the PCTFREE of the tables. Because XtremIO compression is an always-on feature, we compared the space efficiency before and after Oracle Advanced Compression was enabled for the database segments stored on the XtremIO array. To further validate the interoperability of XtremIO compression and Oracle Advanced Compression, we ran the same Swingbench OLTP workload on the segments with and without Oracle ACO compression. The compatibility between XtremIO compression and Oracle Advanced Compression can be validated through the space efficiency and performance difference. Figure 31 shows the logical architecture of this use case.

Figure 31.

Logical architecture for testing interoperability of XtremIO compression and Oracle Advanced Compression

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

53

Chapter 5: Use Case 3: Validating Compression Methods

Configuration

In this use case, we deployed a virtual machine with the default settings from the template created in use case 1 (see Table 4 on page 20) and then created a single instance Oracle 12c database. We created volumes with different sizes and then presented these volumes to the virtual machine using Raw Device Mapping. Table 15 details the names and descriptions of the ASM diskgroups and the number and size of the XtremIO volumes we created in this use case: Table 15.

ASM diskgroups and XtremIO volumes

ASM diskgroup

Volume size (GB)

Number of volumes

+SYS

60

2

Used for system, sysaux, undo, temp tablespaces and control files

+SEED

300

4

Used to store the seed data which will be loaded to other tablespaces

+DATA1

300

4

Used for tablespace in which tables and indexes were not ACO compressed, and tables were created with PCTFREE=10

+DATA_ACO1

300

4

Used for tablespace in which tables and indexes were ACO compressed, and tables were created with PCTFREE=10

+DATA2

300

4

Used for tablespace in which tables and indexes were not ACO compressed, and tables were created with PCTFREE=0

+DATA_ACO2

300

4

Used for tablespace in which tables and indexes were ACO compressed, and tables were created with PCTFREE=0

+REDO

20

2

Used for online log files

+FRA

500

2

Used for archived log files

Description

Table 16 shows the segment configurations for each schema created for this use case. Table 16.

54

Segment configurations in each database schema

Database schema

Oracle Advanced Compression Database segments PCTFREE

SOE1

No

10

SOE2

Yes

10

SOE3

No

0

SOE4

Yes

0

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 5: Use Case 3: Validating Compression Methods

Table 17 shows the database configuration and workload profile used in this use case. Table 17.

Database configuration and workload profile

Profile characteristic

Details

Database type

OLTP

Tablespace size

1 TB for each of tablespaces created

Oracle Database

Single instance 12cR1 database on ASM

Instance configuration

SGA size: 16 GB Note: Because a larger database cache size will buffer more data, we configured a very small buffer cache to generate a stable and high physical I/O workload.

Testing detail

Workload profile

Swingbench OrderEntry TPCC-like benchmark, read/write ratio: 60/40

Database block size

8 KB

We used the following steps to validate the interoperability of Oracle Advanced Compression Option and XtremIO compression: 1.

We deployed a virtual machine from the template with the default configuration, as shown in Table 4.

2.

We created an Oracle 12c single instance database.

3.

We created a 1 TB tablespace named SEED on ASM diskgroup +SEED, and loaded the Swingbench Order Entry schema data into a schema named SEED.

4.

We noted the Physical Capacity Used metric shown in the XtremIO dashboard. For an explanation of each metric displayed on the XtremIO dashboard, refer to Methodology.

5.

We created a 1 TB tablespace named DATA1 on ASM diskgroup +DATA1, and then loaded the Swingbench Order Entry data from schema SEED into a schema named SOE1 without Oracle ACO compression. The tables and indexes in schema SOE1 were created with PCTFREE set to 10. Note: When loading data, we used CREATE TABLE AS SELECT (CTAS) to load the data from schema SEED into the tables created in the schema SOE1, and then created indexes on the tables of schema SOE1. We loaded SEED schema data into other tablespaces using the same method for each subsequent step.

6.

We noted the Physical Capacity Used metric shown in the XtremIO dashboard. To obtain the physical capacity delta, we compared the numbers shown in Physical Capacity Used before and after the data was loaded. The physical capacity delta indicates how much physical space in the XtremIO array was allocated for the data loaded in the previous step.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

55

Chapter 5: Use Case 3: Validating Compression Methods

7.

We created a 1 TB tablespace named DATA_ACO1 on ASM diskgroup +DATA_ACO1, then loaded the Swingbench Order Entry data from schema SEED into a schema named SOE2. Oracle Advanced Row Compression was enabled for the tables, and Oracle Advanced Index Compression was enabled for the indexes. The tables and indexes in schema SOE2 were created with PCTFREE set to 10.

8.

We noted the Physical Space Used metric from the XtremIO dashboard.

9.

We created a 1 TB tablespace named DATA2 on ASM diskgroup +DATA2. We loaded the Swingbench Order Entry data from schema SEED into a schema named SOE3 without Oracle ACO compression enabled. The tables and indexes in schema SOE3 were created with PCTFREE set to 0.

10. We noted the Physical Space Used metric shown in the XtremIO dashboard. 11. We created a 1TB tablespace named DATA_ACO2 on ASM diskgroup +DATA_ACO2, then loaded the Swingbench Order Entry data from schema SEED into a schema named SOE4, with Oracle Advanced Row Compression enabled for the tables and Oracle Advanced Index Compression enabled for the indexes. The tables and indexes in schema SOE4 were created with PCTFREE set to 0. 12. We noted the Physical Space Used metric from the XtremIO dashboard. 13. We ran the same Swingbench OLTP workload on schema SOE1 and SOE2 with different database cache sizes (db_cache_size), and recorded the performance data. Results

Relative physical storage space compression efficiency with and without Oracle ACO enabled on the XtremIO array We tested the physical storage compression and space efficiency between XtremIO only compression and XtremIO compression with Oracle ACO, with the database table PCTFREE set to either 10 or 0. Figure 32 shows the results.

56

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 5: Use Case 3: Validating Compression Methods

Figure 32.

Relative physical storage space required after compression between XtremIO compression and XtremIO compression with ACO

As XtremIO compression is an always-on feature, all the data must be compressed by XtremIO before it is written to the storage media of the array. This means that when data is loaded to the segments with Oracle ACO enabled, the data will first be compressed by Oracle, and then further compressed by XtremIO as it is written to the XtremIO array. Figure 32 shows the following results: 



When tables were created with PCTFREE=10, and the same amount of data was loaded separately to schemas SOE1 (without Oracle ACO enabled) and SOE2 (with Oracle ACO enabled): 

XtremIO reported that data in schema SOE2 required 1 percent more array physical space, compared to the data in schema SOE1.



This indicates that for the same amount of data loaded, XtremIO compression with Oracle ACO enabled required 1 percent more physical space in the array than XtremIO compression alone.

When tables were created with PCTFREE=0, and the same amount of data was loaded separately to schemas SOE3 (without Oracle ACO enabled) and SOE2 (with Oracle ACO enabled): 

XtremIO reported that data in schema SOE4 required 1 percent more physical space in the array, compared to the data in schema SOE3.



This indicates that for the same amount of data loaded, XtremIO compression with Oracle ACO enabled required 1 percent more physical space in the array than XtremIO compression alone.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

57

Chapter 5: Use Case 3: Validating Compression Methods

The results show that Oracle ACO and XtremIO compression can co-exist. Figure 33, Figure 34, Figure 35, Figure 36, and Figure 37, from Oracle Enterprise Manage Cloud Control 12c, show the space usage of tablespaces and schemas listed in Table 16.

58

Figure 33.

Tablespace space usage—Oracle Enterprise Manage Cloud Control 12c

Figure 34.

Segments space usage of schema SOE1—Oracle Enterprise Manage Cloud Control 12c

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 5: Use Case 3: Validating Compression Methods

Figure 35.

Segments space usage of schema SOE2—Oracle Enterprise Manage Cloud Control 12c

Figure 36.

Segments space usage of schema SOE3—Oracle Enterprise Manage Cloud Control 12c

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

59

Chapter 5: Use Case 3: Validating Compression Methods

Figure 37.

Segments space usage of schema SOE4—Oracle Enterprise Manage Cloud Control 12c

OLTP database workload performance with and without Oracle ACO enabled on XtremIO To further validate the interoperability of XtremIO compression and XtremIO compression with ACO, we ran the same Swingbench Order-Entry OLTP workload separately on schemas SOE1 and SOE2, with database cache size set to 128 MB, 512 MB, and 1 GB. Figure 38, Figure 39, and Figure 40 show the transaction per second (TPS) and transaction response time as the same OLTP workload ran on schemas SOE1 and SOE2 with different database cache sizes.

60

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 5: Use Case 3: Validating Compression Methods

Figure 38.

TPS and transaction response time with db_cache_size=128MB

Figure 39.

TPS and transaction response time with db_cache_size=512MB

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

61

Chapter 5: Use Case 3: Validating Compression Methods

Figure 40.

TPS and transaction response time with db_cache_size=1GB

As Figure 38, Figure 39, and Figure 40 show, with a small database cache size, there were slight performance differences between XtremIO compression and XtremIO compression with ACO. But as we increased the database cache size, the differences between these two configurations became less and less apparent. Oracle Database compresses blocks of data in batch mode rather than compressing data every time a write operation takes place. This means that a newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is reached again, the entire block is recompressed to achieve the highest level of compression. For the OLTP database workload, the test results show negligible differences in performance between XtremIO compression with Oracle ACO and XtremIO compression without Oracle ACO. According to the document, Oracle Advanced Compression with Oracle Database 12c, Advanced Row Compression has no adverse impact on read operations since it is able to read compressed blocks directly in memory without uncompressing the blocks. While there can be an additional performance overhead for write operations, Oracle has made several optimizations that minimize this performance overhead for write operations with Advanced Row Compression. Use case summary

62

In this use case, we demonstrated that: 

Oracle Advanced Compression Option and XtremIO compression are compatible.



The physical storage savings on the array stayed consistent between different database segment PCTFREE settings.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 5: Use Case 3: Validating Compression Methods



The difference in the OLTP workload performance between XtremIO compression on its own and XtremIO compression combined with Oracle ACO is negligible.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

63

Chapter 6: Conclusion

Chapter 6

Conclusion

This chapter presents the following topics:

Summary..................................................................................................................65 Findings ...................................................................................................................65

64

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 6: Conclusion

Conclusion Summary

Findings

This solution guide describes a solution that uses EMC XtremIO as the back-end storage for Oracle database servers, which can consist of both physical environments and virtualized environments (using VMware vSphere). The following use cases were explored: 

EMC VSI provides a simple and easy way to provision XtremIO storage for the VMware virtualized Oracle databases, enabling the administrators to manage storage from VMware vSphere Web Client.



EMC XtremIO management console provides a simple and easy to provision XtremIO storage for the physical Oracle databases, greatly reducing the effort required to manage the storage.



By leveraging the XtremIO snapshot, EMC AppSync provides a simple and efficient way to provision test/dev, reporting, and other non-production databases.



We validated the effect of consolidating the production, test/dev, and reporting workloads on the XtremIO array by provisioning multiple virtualized Oracle databases on the VMware vSphere environment, and running SLOB random I/O workloads against these virtualized databases while the physical production workload was running.



We validated the interoperability of Oracle ACO compression and XtremIO compression by loading the same amount of data into database segments that were created with Oracle ACO compression enabled or disabled and with database table PCTFREE set to different values (0 or 10). The interoperability of the two compression features was further validated by running the same OLTP workload to the segments created.

We validated the XtremIO snapshot array physical storage capacity overhead by taking multiple cross-consistent snapshots of a production. The conclusions we draw from the tests show that: 

Using EMC VSI and the XtremIO management console greatly improves the administrators’ efficiency in managing storage for Oracle databases. See Use case 1: Easy provisioning and reducing management efforts in the database lifecycle.



An XtremIO snapshot, at the moment of creation, has no capacity overhead. Therefore, a very large number of snapshots can be created with XtremIO without consuming any physical storage capacity. As we have shown in Use case 2: Consolidating production, test/dev, and reporting workloads onto the same XtremIO array, provisioning five 1TB test/dev databases using XtremIO snapshot consumes no physical storage capacity in the array.



Consolidating the test/dev workload to the same XtremIO had a negligible performance impact on the physical production database workloads. See Use case 2: Consolidating production, test/dev, and reporting workloads onto the same XtremIO array. The simulated test/dev workloads running on the provisioned test/dev databases have little to no performance impact on their source database. Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

65

Chapter 6: Conclusion



Consolidating the reporting workload to the same XtremIO had a negligible performance impact on the physical Oracle production database workloads. See Use case 2: Consolidating production, test/dev, and reporting workloads onto the same XtremIO array. The simulated reporting workloads running on the provisioned reporting database have little to no performance impact on its source database.



The combination of Oracle Advanced Compression and XtremIO compression was completely workable, had no significant performance impact, and saved additional physical space. See Use case 3: Validating how XtremIO compression works with Oracle Advanced Compression. The provisioning of virtualized test/dev and reporting Oracle databases can be accelerated by XtremIO snapshot, XtremIO VAAI support, and VMware templates, while the storage footprint and performance impact on the source database is reduced to a minimum.

Notes:  Benchmark results are highly dependent on workload, specific application requirements, and system design and implementation. Relative system performance will vary as a result of these and other factors. Therefore, the solution test workloads should not be used as a substitute for a specific customer application benchmark when critical capacity planning or product evaluation decisions are considered.  All performance data contained in this report was obtained in a rigorously controlled environment. Results obtained in other operating environments may vary significantly.  EMC Corporation does not warrant or represent that a user can or will achieve a similar performance expressed in transactions per minute.

66

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 7: References

Chapter 7

References

This chapter presents the following topics:

EMC documentation .................................................................................................68 Oracle documentation .............................................................................................. 69 VMware documentation ...........................................................................................69

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

67

Chapter 7: References

References EMC documentation

The following documents, available from the EMC Online Support or EMC.com websites, provide additional and relevant information. If you do not have access to a document, contact your EMC representative: 

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 3.0



Oracle Best Practices with XtremIO



EMC XtremIO Optimized Flash Storage for Oracle Databases



Introduction to XtremIO Snapshots



XtremIO data protection (XDP)



Introduction to the EMC XtremIO Storage Array (ver. 4.0)



EMC AppSync User and Administration Guide (Version 2.1)



EMC Virtual Storage integrator

For additional discussion and information about XtremIO and Oracle, see the following documents:

68



XtremIO Snapshot Refresh For Oracle Database Production Development And Test



XtremIO Performance Engineering Lab Report: Oracle Database Redo Logging Durability



Lab Report: Oracle Database on EMC XtremIO A Compression Technology Case Study



XtremIO Snapshots – Do you use film or digital photography?



XtremIO In-Memory Metadata



XtremIO Scale-out Design



XtremIO Thin Provisioning



XtremIO Inline Data Reduction



XtremIO Data Protection (XDP)



XtremIO Best Practices: Advanced Format 512e and Native modes



SLOB Deployment – A Picture Tutorial



SLOB Data Loading Case Studies – Part I. A Simple Concurrent + Parallel Example



SLOB Data Loading Case Studies – Part II. SLOB 2.2 For High-Bandwidth Data Loading

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Chapter 7: References

Oracle documentation

VMware documentation

For additional information, see the following documents available from: https://support.oracle.com (sign-in is required) 

Oracle Grid Infrastructure Installation Guide 11g Release 2(11.2) for Linux



Oracle Database Installation Guide 11g Release 2 (11.2) for Linux.



HugePages on Linux: What It Is... and What It Is Not... [My Oracle Support Doc ID 361323.1]



Oracle Advanced Compression with Oracle Database 12c



Oracle Database Concepts 12c Release 1 (12.1)



Grid Infrastructure Installation Guide



Database Installation Guide

Refer to the following topics on the VMware website: 

Understanding Oracle Certification Support and Licensing in VMware Environments



Oracle Databases on VMware Best Practices Guide



Performance Best Practices for VMware vSphere™ 5.5



VMware vSphere Storage APIs – Array Integration (VAAI)

Note: The links provided in this document were working correctly at the time of publication.

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

69

Appendix A: SLOB Configuration Parameters

Appendix A

SLOB Configuration Parameters

This appendix presents the following topic: SLOB configuration parameters ...............................................................................71

70

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix A: SLOB Configuration Parameters

SLOB configuration parameters Table 18, Table 19, and Table 20 show the SLOB configuration parameters used for production, test/dev, and reporting workload. Table 18.

SLOB configuration parameters for production workload

Parameters

Values

UPDATE_PCT

25

RUN_TIME

600

SCALE

400,000

WORK_UNIT

32

REDO_STRESS

LIGHT

LOAD_PARALLEL_DEGREE

8

SHARED_DATA_MODULUS

0

DO_UPDATE_HOTSPOT

FALSE

HOTSPOT_PCT

10

THINK_TM_MODULUS

21

THINK_TM_MIN

.1

THINK_TM_MAX

.5

Table 19.

SLOB configuration parameters for test/dev workload

Parameters

Values

UPDATE_PCT

25

RUN_TIME

600

SCALE

400,000

WORK_UNIT

32

REDO_STRESS

LIGHT

LOAD_PARALLEL_DEGREE

8

SHARED_DATA_MODULUS

0

DO_UPDATE_HOTSPOT

FALSE

HOTSPOT_PCT

10

THINK_TM_MODULUS

10

THINK_TM_MIN

.1

THINK_TM_MAX

.5

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

71

Appendix A: SLOB Configuration Parameters Table 20.

SLOB configuration parameters for test/dev workload

Parameters

Values

UPDATE_PCT

0

RUN_TIME

600

SCALE

400,000

WORK_UNIT

32

REDO_STRESS

LIGHT

LOAD_PARALLEL_DEGREE

8

SHARED_DATA_MODULUS

0

DO_UPDATE_HOTSPOT

FALSE

HOTSPOT_PCT

10

THINK_TM_MODULUS

0

THINK_TM_MIN

.1

THINK_TM_MAX

.5

Note: We made all the indexes invisible for the SLOB schemas in the reporting database. This caused all the queries run against the tables in the database to be full.

72

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

Appendix B

Provisioning Storage Volumes for a Virtualized Database

This appendix presents the following topics:

Virtualized Oracle database initial deployment – Provisioning virtual machine ................................................................ 74 Virtualized Oracle database initial deployment – Provisioning VMFS datastores ..........................................................................78 Virtualized Oracle database initial deployment – Creating virtual disks ........................................................................................85 Adding storage volumes to virtualized Oracle database – Provisioning VMFS datastores ..........................................................................92 Adding storage volumes to virtualized Oracle database – Creating virtual disks ......................................................................................100

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

73

Appendix B: Provisioning Storage Volumes for a Virtualized Database

Provisioning storage volumes for the virtualized Oracle databases in their lifecycle Virtualized Oracle database initial deployment– Provisioning virtual machine

To provision a virtualized Oracle database, on the vSphere Web Client, deploy a virtual machine from the Oracle 12cR1 virtual machine template: 1.

On the home page of vSphere Web Client, select Hosts and Clusters as shown in Figure 41.

Figure 41.

2.

Right-click on the template that is going to be used to provision the virtual machine, and select New VM from This Template, as shown in Figure 42.

Figure 42.

74

Provisioning a virtual machine – step 1

Provisioning a virtual machine – step 2

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

3.

Provide a name for the virtual machine, as shown in Figure 43.

Figure 43.

4.

Provisioning a virtual machine – step 3

Select the ESXi host on which the VM will be running, as shown in Figure 44.

Figure 44.

Provisioning a virtual machine–step 4

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

75

Appendix B: Provisioning Storage Volumes for a Virtualized Database

5.

Select the datastore on which the OS and Oracle binaries are stored, as shown in Figure 45.

Figure 45.

6.

Select Power on virtual machine after creation, as shown in Figure 46.

Figure 46.

76

Provisioning a virtual machine–step 5

Provisioning a virtual machine – step 6

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

7.

Review the settings and click Finish, as shown in Figure 47.

Figure 47.

8.

Provisioning a virtual machine – step 7

The virtual machine is successfully deployed, as shown in Figure 48.

Figure 48.

Provisioning a virtual machine–step 8

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

77

Appendix B: Provisioning Storage Volumes for a Virtualized Database

Virtualized Oracle database initial deployment – Provisioning VMFS datastores

To provision VMFS datastores from vSphere Web Client, follow these steps to create four 300 GB VMFS datastores, two 40 GB VMFS datastores, and two 500 GB datastores, which are required by the initial deployment of the virtualized Oracle 12cR1 database: 1.

On the vSphere Web Client home page, click vCenter Inventory Lists as shown in Figure 49.

Figure 49.

2.

Clicks Hosts, as shown in Figure 50.

Figure 50.

78

Provisioning VMFS datastores–step 1

Provisioning VMFS datastores–step 2

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

3.

Right click the ESXi host on which the datastores will be created, select All EMC VSI Plugin Actions, then select New EMC Datastore, as shown in Figure 51.

Figure 51.

Provisioning VMFS datastores–step 3

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

79

Appendix B: Provisioning Storage Volumes for a Virtualized Database

4.

In the input box, provide the datastore name, as shown in Figure 52.

Figure 52.

5.

Select the type of datastore. We chose VMFS, as shown in Figure 53.

Figure 53.

80

Provisioning VMFS datastores–step 4

Provisioning VMFS datastores–step 5

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

6.

Select the storage array on which the storage volumes will be created and click Next, as shown in Figure 54.

Figure 54.

7.

Provisioning VMFS datastores–step 6

Select the initiator of the ESXi host, as shown in Figure 55.

Figure 55.

Provisioning VMFS datastores–step 7

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

81

Appendix B: Provisioning Storage Volumes for a Virtualized Database

8.

Click Next, as shown in Figure 56.

Figure 56.

9.

Specify the size of each datastore, and the number of datastores, as shown in Figure 57.

Figure 57.

82

Provisioning VMFS datastores–step 8

Provisioning VMFS datastores–step 9

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

10. Review the settings and click Finish, as shown in Figure 58.

Figure 58.

Provisioning VMFS datastores–step 10

11. On the XtremIO management console, the newly created volumes are shown, as shown in Figure 59.

Figure 59.

Provisioning VMFS datastores–DATA volumes created

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

83

Appendix B: Provisioning Storage Volumes for a Virtualized Database

12. On the vSphere Web Client, the newly created datastores are shown, as displayed in Figure 60.

Figure 60.

Provisioning VMFS datastores–DATA datastores created

13. Repeat the same steps to create the rest of the datastores. On the XtremIO management console, the volumes created are shown as in Figure 61.

Figure 61.

Provisioning VMFS datastores–all storage volumes created

14. On the vSphere Web Client, the datastores created are shown as in Figure 62.

84

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

Figure 62.

Virtualized Oracle database initial deployment – Creating virtual disks

Provisioning VMFS datastores–all datastores created

To create virtual disks from the datastores using the vSphere Web Client: 1.

Select the virtual machine, click Settings > Edit, as shown in Figure 63.

Figure 63.

Creating virtual disks – step 1

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

85

Appendix B: Provisioning Storage Volumes for a Virtualized Database

2.

In the New Device menu at the bottom of the screen, click Select, select New Hard Disk, and click OK, as shown in Figure 64.

Figure 64.

86

Creating virtual disk–step 2

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

3.

Click Add, as shown in Figure 65.

Figure 65.

Creating virtual disk–step 3

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

87

Appendix B: Provisioning Storage Volumes for a Virtualized Database

4.

Expand the New Hard Disk drop down list, click Location, and select Browse from the Location list, as shown in Figure 66.

Figure 66.

88

Creating virtual disk–step 4

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

5.

Select a datastore, as shown in Figure 67.

Figure 67.

Creating virtual disk–step 5

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

89

Appendix B: Provisioning Storage Volumes for a Virtualized Database

6.

Specify the size of the virtual disk. According to VMware best practices, for an application that requires high performance such as Oracle database, create just one virtual disk from a single datastore. In this case, the datastore has a maximum of 298.8 GB free space, so we create a 298GB virtual disk from this datastore. On Disk Provisioning, select Thick provision eager zeroed; on the Virtual Device Node, select the SCSI controller on which the virtual disk will be attached; on Disk Mode, select Independent – Persistent, as shown in Figure 68.

Figure 68.

90

Creating virtual disk–step 6

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

7.

Repeat steps 1-6 for the rest of the datastores.

In addition to being able to create virtual disks from the vSphere Web Client, you can also create them from the vSphere PowerCLI command line. For example: a. Get a list of datastores that the virtual disks will be created from: PS C:\> get-datastore | where {$_.name -match "oracle12c_db1"}

b.

Name

FreeSpaceGB

CapacityGB

----

-----------

----------

oracle12c_db1_data-001

298.800

299.750

oracle12c_db1_data-002

298.800

299.750

oracle12c_db1_data-003

298.799

299.750

oracle12c_db1_data-004

298.799

299.750

oracle12c_db1_redo-001

18.851

19.750

oracle12c_db1_redo-002

18.850

19.750

oracle12c_db1_fra-001

498.799

499.750

oracle12c_db1_fra-002

498.799

499.750

Create the virtual disks using the following commands. In the commands, specify the size of the virtual disk, the controller to which the virtual disk will be attached, the datastore from which the virtual disk will be created, the storage format, and the persistence of the virtual disk: $vm = Get-VM oracle12c_db1 New-HardDisk -VM $vm -CapacityGB 298 -controller "SCSI controller 1" -datastore oracle12c_db1_data-001 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 298 -controller "SCSI controller 1" -datastore oracle12c_db1_data-002 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 298 -controller "SCSI controller 2" -datastore oracle12c_db1_data-003 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 298 -controller "SCSI controller 2" -datastore oracle12c_db1_data-004 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 18 -controller "SCSI controller 3" datastore oracle12c_db1_redo-001 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 18 -controller "SCSI controller 3" datastore oracle12c_db1_redo-002 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 498 -controller "SCSI controller 0" -datastore oracle12c_db1_fra-001 -StorageFormat EagerZeroedThick Persistence IndependentPersistent New-HardDisk -VM $vm -CapacityGB 498 -controller "SCSI controller 0" -datastore oracle12c_db1_fra-002 -StorageFormat EagerZeroedThick Persistence IndependentPersistent

8.

After all the virtual disks are created, log on to the virtual machine, partition the disks with an offset of 2,048, and create ASM disks using the ASMlib utility.

9.

Log on to the ASM instance and create ASM diskgroups. Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

91

Appendix B: Provisioning Storage Volumes for a Virtualized Database

10. According to best practices, a fine-grained stripe size provides better performance for the diskgroup that is used to store the online redo log. For this use case, use the following command to change the stripe size of the redo diskgroup from the default coarse stripe size to fine-grained stripe size:

alter diskgroup redo alter template onlinelog attributes (fine); 11. Create the database using the Database Configuration Assistant (DBCA) 12. Log on to the database instance and execute the following command:

alter system set “_disk_sector_size_override”=false; 13. Create multiple online redo log groups with a block size of 4,096 bytes, then drop the online redo log groups created with the default block size. Adding storage volumes to virtualized Oracle database– Provisioning VMFS datastores

To provision one 300 GB VMFS datastore from vSphere Web Client: 1.

On the vSphere Web Client home page, click vCenter Inventory Lists, as shown in Figure 69.

Figure 69.

92

Adding storage volumes–Provisioning VMFS datastore—step 1

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

2.

Clicks Hosts, as shown in Figure 70.

Figure 70.

Adding storage volumes–Provisioning VMFS datastore—step 2

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

93

Appendix B: Provisioning Storage Volumes for a Virtualized Database

3.

Right-click the ESXi host on which the datastores will be created, select All EMC VSI Plugin Actions, then select New EMC Datastore as shown in Figure 71.

Figure 71.

94

Adding storage volumes–Provisioning VMFS datastore—step 3

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

4.

In the input box, provide the datastore name, as shown in Figure 72.

Figure 72.

5.

Adding storage volumes–Provisioning VMFS datastore—step 4

Select the type of datastore: in this instance, VMFS, as shown in Figure 73.

Figure 73.

Adding storage volumes–Provisioning VMFS datastore—step 5

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

95

Appendix B: Provisioning Storage Volumes for a Virtualized Database

6.

Select the storage array on which the storage volumes will be created, as shown in Figure 74.

Figure 74.

7.

Select the initiator of the ESXi host, as shown in Figure 75.

Figure 75.

96

Adding storage volumes–Provisioning VMFS datastore—step 6

Adding storage volumes–Provisioning VMFS datastore—step 7

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

8.

Click Next, as shown in Figure 76.

Figure 76.

9.

Adding storage volumes–Provisioning VMFS datastore—step 8

Specify the Volume Capacity and Volume Count, as shown in Figure 77.

Figure 77.

Adding storage volumes–Provisioning VMFS datastore—step 9

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

97

Appendix B: Provisioning Storage Volumes for a Virtualized Database

10. Review the settings and click Finish, as shown in Figure 78.

Figure 78.

98

Adding storage volumes–Provisioning VMFS datastore—step 10

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

11. On the vSphere Web Client, the newly created datastores are shown in Figure 79.

Figure 79.

Adding storage volumes–Provisioning VMFS datastore—datastore created

Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

99

Appendix B: Provisioning Storage Volumes for a Virtualized Database

To create virtual disks from the datastores on the vSphere Web Client, follow these Adding storage steps: volumes to virtualized Oracle 1. Click Hosts and Clusters on the home page, as shown in Figure 80. database–Creating virtual disks

Figure 80.

2.

Adding storage volumes–Creating virtual disks–step 1

Select the virtual machine, click the Settings tab, and click Edit as shown in Figure 81.

Figure 81.

Adding storage volumes–Creating virtual disks—step 2

100 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

3.

In the New Device menu at the bottom of the screen, click Select. From the resulting popup menu, select New Hard Disk and click OK, as shown in Figure 82.

Figure 82.

Adding storage volumes–Creating virtual disks–step 3

Oracle 11g and 12c Database Consolidation and 101 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

4.

Click Add, as shown in Figure 83.

Figure 83.

Adding storage volumes–Creating virtual disks–step 4

102 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

5.

Expand the New Hard Disk drop down list, click Location, and select Browse from the Location list, as shown in Figure 84.

Figure 84.

Adding storage volumes–Creating virtual disks–step 5

Oracle 11g and 12c Database Consolidation and 103 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

6.

In the Select a datastore cluster or database screen, select the datastore from which the virtual disk will be created, as shown in Figure 85.

Figure 85.

Adding storage volumes–Creating virtual disks–step 6

104 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix B: Provisioning Storage Volumes for a Virtualized Database

7.

Specify the various settings for the virtual disk to be created, as shown in Figure 86.

Figure 86.

Adding storage volumes–Creating virtual disks–step 7

8.

Log on to the virtual machine, partition the disk with an offset of 2,048, then create ASM disks using the ASMlib utility

9.

Log on to the ASM instance and add the new ASM disk to the corresponding diskgroup, for example:

alter diskgroup data add disk 'ORCL:DATA5' rebalance power 1; Although the steps shown above are for Oracle 12cR1 database, they can also be used to provision storage volumes for virtualized Oracle 11gR2 databases in their lifecycle.

Oracle 11g and 12c Database Consolidation and 105 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

Appendix C

Provisioning Storage Volumes for a Physical Oracle Database

This appendix presents the following topics:

Physical Oracle database initial deployment – Provisioning storage volumes .........................................................................107 Adding storage volumes to physical Oracle database – Provisioning storage volumes .........................................................................119

106 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

Provisioning storage volumes for physical Oracle databases in their lifecycle Physical Oracle database initial deployment – Provisioning storage volumes

To provision storage volumes from the XtremIO management console, follow these steps to create four 300 GB volumes, two 40 GB volumes, and two 500 GB volumes, which are required by the initial deployment of the physical Oracle 12cR1 RAC database 1.

On the XtremIO management console, click Configuration, as shown in Figure 87.

Figure 87.

Provisioning storage volumes—step 1

Oracle 11g and 12c Database Consolidation and 107 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

2.

Click Create Volume, as shown in Figure 88.

Figure 88.

3.

Provisioning storage volumes–step 2

Click Add Multiple, as shown in Figure 89.

Figure 89.

Provisioning storage volumes–step 3

108 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

4.

Specify the Number, Name, Size, and Logical Block Size of volumes to be created. Figure 90 shows that four 300 GB volumes will be created; these volumes will be used to create the +DATA diskgroup.

Figure 90.

Provisioning storage volumes–step 4

Oracle 11g and 12c Database Consolidation and 109 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

5.

The volumes are created, as shown in Figure 91.

Figure 91.

Provisioning storage volumes–step 5

110 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

6.

Create other volumes that are used for diskgroups +REDO and +FRA, and click Next, as shown in Figure 92.

Figure 92.

Provisioning storage volumes–step 6

Oracle 11g and 12c Database Consolidation and 111 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

7.

Click Create Tag. Creating a tag is optional. You can assign the newly created volumes to an existing tag, or create a new tag for them. See Figure 93.

Figure 93.

Provisioning storage volumes–step 7

112 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

8.

Specify the name of the tag, as shown in Figure 94.

Figure 94.

Provisioning storage volumes–step 8

Oracle 11g and 12c Database Consolidation and 113 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

9.

Select the newly created tag for the volumes created in previous step, as shown in Figure 95.

Figure 95.

Provisioning storage volumes–step 9

114 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

10. Select the newly created volumes and click Create/Modify Mapping, as shown in Figure 96.

Figure 96.

Provisioning storage volumes–step 10

Oracle 11g and 12c Database Consolidation and 115 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

11. Select the initiator groups of the corresponding servers and click Next, as shown in Figure 97.

Figure 97.

Provisioning storage volumes–step 11

116 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

12. Click Finish, as shown in Figure 98.

Figure 98.

Provisioning storage volumes–step 12

Oracle 11g and 12c Database Consolidation and 117 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

13. The volumes can be seen by the servers and can be accessed now, as shown in Figure 99.

Figure 99.

Provisioning storage volumes–volumes created and mapped to host

14. Log on to one of the physical servers, scan the disks, and partition the disks with an offset of 2,048 bytes, then create ASM disks using the ASMlib utility. 15. Install the Oracle grid infrastructure software, create the ASM instance, and then create the ASM diskgroups. 16. For the diskgroup that is used to store the online redo log, a fine-grained stripe size will provide better performance, according to best practices. Run the following command to change the stripe size to fine-grained:

alter diskgroup redo alter template onlinelog attributes (fine); 17. Install the Oracle database software and create the database. 18. Log on to the database instance and execute the following command:

alter system set “_disk_sector_size_override”=false; 19. Create multiple online redo log groups with a block size of 4,096 bytes, then drop the online redo log groups created with default block size.

118 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

Adding storage volumes to physical Oracle database– Provisioning storage volumes

To create one 300 GB storage volume from XtremIO management console: 1.

On the XtremIO management console, click Configuration, as shown in Figure 100.

Figure 100. Adding Storage Volume – step 1

2.

Click Create Volume, as shown in Figure 101.

Figure 101. Adding Storage Volume–step 2

Oracle 11g and 12c Database Consolidation and 119 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

3.

Specify the Name of the volume, the Size, the Logical Block Size and click Next, as shown in Figure 102.

Figure 102. Adding Storage Volume–step 3

120 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

4.

Select the tag for the volume created, as shown in Figure 103.

Figure 103. Adding Storage Volume–step 4

Oracle 11g and 12c Database Consolidation and 121 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

5.

Click Create/Modify Mapping, as shown in Figure 104.

Figure 104. Adding Storage Volume–step 5

122 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

6.

Select the initiator groups of the servers and click Next, as shown in Figure 105.

Figure 105. Adding Storage Volume–step 6

Oracle 11g and 12c Database Consolidation and 123 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix C: Provisioning Storage Volumes for a Physical Oracle Database

7.

Click Finish, as shown in Figure 106.

Figure 106. Adding Storage Volume–step 7

8.

Log on to one of the physical servers, scan the disk, and partition the disks with an offset of 2,048 bytes, then create ASM disks using the ASMlib utility.

9.

Log on to an ASM instance and add the new ASM disk to the corresponding diskgroup using the following command:

alter diskgroup data add disk 'ORCL:DATA5' rebalance power 1;

124 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Appendix D

ESX Host Performance Data

This appendix presents the following topics:

Viewing virtual machine, ESXi hosts, and ASM diskgroups settings ........... 126 Viewing ESXi host performance information .............................................. 131

Oracle 11g and 12c Database Consolidation and 125 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Viewing virtual machine, ESXi hosts, and ASM diskgroups settings To view the settings of the virtual machine: 1.

On the vSphere Web Client, click the name of the virtual machine on the left side of the panel, then click Manage >e Settings.

2.

The general hardware settings are shown on the right side of the panel. For more details on the hardware settings, click Edit, as shown in Figure 107.

Figure 107. View VM settings–general settings

126 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

3.

To view the detailed CPU settings, click CPU, as shown in Figure 108.

Figure 108. View VM settings–detailed CPU settings

Oracle 11g and 12c Database Consolidation and 127 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

4.

To view the detailed memory settings, click Memory, as shown in Figure 109.

Figure 109. View VM settings–detailed memory settings

128 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

5.

To view the detailed disk settings, click the name of the disks, for example Hard disk 2, as shown in Figure 110. To view the detailed settings for other disks, click Manage other disks.

Figure 110. View VM settings–detailed disk settings

Oracle 11g and 12c Database Consolidation and 129 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

6.

To view the ESX host hardware settings, click the name of the ESXi host, then click Summary, as shown in Figure 111.

Figure 111. View ESXi hardware settings

7.

To view the storage devices that presented to the ESXi host, click the name of the ESXi host, click Manage > Storage and select Storage Devices, as shown in Figure 112.

Figure 112. View ESXi host storage devices

8.

To view the ASM diskgroup settings, log on to the ASM instance using SQLPLUS, and execute the following SQL query, as shown in Figure 113:

130 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Figure 113. View ASM diskgroup settings

9.

To view the ASM disk settings, execute the following SQL query, as shown in Figure 114.

Figure 114. View ASM disk settings

Viewing ESXi host performance information To show the performance information of the ESXi host on which the test/dev virtualized databases were running when we ran the production and test/dev workloads in parallel, click the name of the ESXi host, and click Monitor > Performance. Two types of performance information, Overview and Advanced, are shown in Figure 115. Figure 115 shows the Overview performance information of the ESXi host.

Oracle 11g and 12c Database Consolidation and 131 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Figure 115. ESXi host overall performance – CPU utilization (%)–consolidating production and test/dev workloads

Figure 116, Figure 117, Figure 118, Figure 119, and Figure 120 show the Advanced performance information of the ESXi host.

Figure 116. ESXi host advanced performance–CPU–consolidating production and test/dev workloads

132 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Figure 117. ESXi host advanced performance–physical device average read/write requests per second–consolidating production and test/dev workloads

Figure 118. ESXi host advanced performance–physical device read/write latency– consolidating production and test/dev workloads

Oracle 11g and 12c Database Consolidation and 133 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Figure 119. ESXi host advanced performance–storage adapter average read/write requests per second–consolidating production and test/dev workloads

Figure 120. ESXi host advanced performance–storage adapter read/write latency – consolidating production and test/dev workloads

To show the performance information of the virtual machine on which the test/dev virtualized databases were running when we ran the production and test/dev workloads in parallel, click the name of the virtual machine, and click Monitor >e Performance. Two types of performance information will be shown: Overview and Advanced. Figure 121 shows the Overview performance of virtual machine oracle12c_VM1.

134 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Figure 121. Virtual machine overall performance–consolidating production and test/dev workloads

Figure 122, Figure 123, and Figure 124 show the Advanced performance information of virtual machine oracle12c_VM1.

Figure 122. Virtual machine advanced performance–CPU performance–consolidating production and test/dev workloads

Oracle 11g and 12c Database Consolidation and 135 Workload Scalability with EMC XtremIO 4.0 Solution Guide

Appendix D: ESX Host Performance Data

Figure 123. Virtual machine advanced performance–disk read/write requests per second– consolidating production and test/dev workloads

Figure 124. Virtual machine advanced performance–disk highest latency–consolidating production and test/dev workloads

136 Oracle 11g and 12c Database Consolidation and Workload Scalability with EMC XtremIO 4.0 Solution Guide