Mitigate COT using Oracle 12c features_05262016.pdf

(744 KB) Pobierz
Mitigate the Benefits of Exadata
Cell Offloading Technology using
Oracle 12c Features
Author: Debra Labanowski
Debra.Labanowski@hpe.com
May 1, 2016
Technical white paper
Technical white paper
Contents
Introduction.............................................................................................................................................. 4
Test Environment .................................................................................................................................... 5
SDX hardware...................................................................................................................................... 5
SDX software ....................................................................................................................................... 5
Exadata comparison model ................................................................................................................. 6
Workload Types ...................................................................................................................................... 6
SQL Statement Categories ..................................................................................................................... 6
Exadata Cell Offloading Technology ....................................................................................................... 6
Smart Scan .......................................................................................................................................... 6
Exadata Storage Indexes .................................................................................................................... 7
Hybrid Columnar Compression (HCC) ................................................................................................ 7
Oracle 12c Features that Mitigate the Benefits of Exadata Cell Offloading Technology ........................ 7
Prerequisite Components ........................................................................................................................ 8
Parameters common to all tests .......................................................................................................... 8
Parameter definitions ........................................................................................................................... 8
Statistics collection .............................................................................................................................. 9
SQL statements used for ad-hoc testing ............................................................................................. 9
Tables used for testing ...................................................................................................................... 10
In-Memory Parallel Query ..................................................................................................................... 10
Definition and benefits ....................................................................................................................... 10
Exadata feature targeted ................................................................................................................... 11
Parallel Degree Policy settings .......................................................................................................... 11
Autotrace statistics ............................................................................................................................. 12
Test 1: Direct Parallel I/O ................................................................................................................... 12
Test 2: Indirect Parallel IO ................................................................................................................. 17
Test 3: In-Memory Parallel update..................................................................................................... 20
Oracle 12c enhancement ................................................................................................................... 21
Summary............................................................................................................................................ 22
Recommendations ............................................................................................................................. 23
In-Memory Column Store ...................................................................................................................... 23
Definition and benefits ....................................................................................................................... 23
Comparison to Exadata Cell Offloading Technology ......................................................................... 24
Exadata Smart Scan and Storage Indexes ....................................................................................... 24
Exadata HCC ..................................................................................................................................... 24
Exadata features targeted ................................................................................................................. 25
Test configuration .............................................................................................................................. 25
In-Memory Column Store tests .......................................................................................................... 27
Test 1a: Verify population of the In-Memory area and compression ratio For Query Low
compression....................................................................................................................................... 27
Test 1b: Execute a query that accesses a table using For Query Low compression ........................ 28
Summary............................................................................................................................................ 32
For HPE Internal Use Only pg. 2
Technical white paper
Recommendations ............................................................................................................................. 35
Attribute Clustering ................................................................................................................................ 35
Definition and benefits ....................................................................................................................... 35
Exadata features targeted ................................................................................................................. 36
Attribute Clustering and the In-Memory Column Store ...................................................................... 36
Attribute Clustering test ..................................................................................................................... 36
Test 1a: Specify Attribute Clustering for a table and reorganize data ............................................... 36
Test 1b: Verify population of the In-Memory area and compression ratios For Query Low
compression....................................................................................................................................... 37
Test 1c: Execute a query that accesses a table using For Query Low compression ........................ 38
Test 1d: Update an In-Memory table using For Query Low compression ......................................... 40
Summary............................................................................................................................................ 41
Recommendations ............................................................................................................................. 42
The HammerDB Workload .................................................................................................................... 42
Test 1: In-Memory Parallel Query ...................................................................................................... 42
Test 2: In-Memory Column Store....................................................................................................... 42
Resources, contacts, or additional links ................................................................................................ 43
Appendix A: TPC-H schema ................................................................................................................. 44
For HPE Internal Use Only pg. 3
Technical white paper
Introduction
The HPE Integrity Superdome X Server blends trusted Integrity Superdome reliability with a standard
x86 architecture. Building on the mission-critical capabilities of Integrity Superdome 2, Superdome X
offers breakthrough scalability and resource utilization to optimize all Oracle database workload
types.
HPE commonly competes against vendors that runs Oracle on alternative platforms, in particular
Oracle database running on Oracle Exadata.
Oracle Exadata is a preconfigured combination of hardware and software that provides a platform for
running Oracle database software. It is engineered to limit IO bottlenecks by pushing some SQL
processing to the storage layer and by providing columnar compression types, which can provide
vastly higher compression ratios than traditional Oracle methods. Due to these enhancements,
Exadata has become very competitive in the Decision Support (DSS) workload space.
Oracle 12c is comprised of database features that directly target DSS workloads and mitigate the
benefits derived from Exadata specific technology. This paper will address how to tune an Oracle 12c
DSS workload on SDX to successfully compete against Exadata.
For HPE Internal Use Only pg. 4
Technical White Paper
Test Environment
Oracle publishes updated versions of the Database Performance Tuning Guide, Oracle Database
Data Warehousing Guide, and Oracle Database Administrator’s Guide with each new release of their
database software. These guides discuss methods for configuring Oracle instances for optimal
performance. This paper does not reproduce what has been covered elsewhere. Instead this paper
describes how to take advantage of SDX scale to tune an Oracle 12c decision support workload to
mitigate the benefits of Exadata Cell Offloading technology.
As with any tuning recommendations, the standard disclaimers apply - every DSS application
workload is different. So the actual amount of improvement seen in your production environment may
vary from the results documented here.
SDX hardware
The components of the hardware environment used for testing are shown in Figure 1. A two-blade
nPar on the Superdome X was used as the Oracle database server. Each blade was fully populated
with two 15-core CPUs and 1.5 TB of RAM for a total of 60 CPU cores and 3 TB of RAM for the nPar.
Hyperthreading was enabled on the CPUs during all tests. The database files were stored in ASM
disk groups, which were housed on a 3PAR 7450 All-Flash array. An HPE ProLiant DL360 Gen9
server was used as the driver system running HammerDB. The DL360 and Superdome X were
connected via 10GbE via a ProCurve 8212zl switch.
3PAR 7450 Array
ProCurve 8212zl Switch
Figure 1:
Oracle test environment
SDX software
The operating system used was Red Hat Enterprise Linux Server release 7.1 (Maipo) using the
default settings. Non-Uniform Memory Access (NUMA) optimizations and HugePages were
configured. See the
Oracle Database Administrator’s Guide
for more information.
Oracle database Release 12.1.0.2.0 was used for testing and the “external storage” option was
chosen when configuring Automatic Storage Manager (ASM); therefore, mirroring was managed by
the 3Par Storage Array.
The Load Generation tool used for testing was HammerDB. The HammerDB TPC-H benchmark kit
was used to build a schema and simulate complex ad-hoc queries on large data volumes.
For HPE Internal Use Only pg. 5

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin