Oracle Decision Support (DSS) Load Testing

This guide gives you an introduction to conducting Decision Support or analytical workloads on the Oracle

Database. This guide will equip you with the essentials for assessing the ability of any system that runs the

Oracle Database for processing query based workloads, such workloads are also known as Data

Warehousing, Business Intelligence and Analytical workloads. On completion of this guide you will be able

workload for testing the features of In-Memory Column Stores.

Introduction ...................................................................................................................................... 1

SUT Database Server Configuration ................................................................................................ 2

Installation and Configuration ......................................................................................................... 3

SUT Database Server Installation ................................................................................................. 3

Creating the Test Schema ................................................................................................................. 4

Build Options ................................................................................................................................ 7

Starting the Schema Build ............................................................................................................ 8

Pre-Testing and Planning ............................................................................................................... 16

Driver Options ............................................................................................................................ 18

Calculating QphH ........................................................................................................................ 39

Support and Questions ................................................................................................................... 40

The basis of Analytic or Decision Support Systems is the ability to process complex ad-hoc queries on large

volumes of data. Processing this amount of data within a single process or thread on traditional row-

oriented database is time consuming. Consequently Oracle employs Parallel Execution to break down such

queries into multiple sub tasks to complex the query more quickly. Additional features such as compression

As a consequence when planning analytic workloads for optimal performance you should consider both

your in-memory (from 12.1.0.2) and parallel execution configuration. In similarity to the HammerDB OLTP

workload, HammerDB implements a fair usage of a TPC workload however the results should not be

compared to official published TPC-H results in any way.

meet the standard requirements for an Oracle Database Server. Similarly Oracle can be installed on any

supported operating system, there is no restriction on the version of Oracle that is required.

Before running a HammerDB DSS test depending on your configuration you should focus on memory and

I/O (disk performance). Also in turn the number and type of multi-core and multi-threaded processors

installed will have a significant impact on parallel performance to drive the workload. When using in-

memory column store features processors that support SIMD/ AVX instructions sets are also required for

the vectorisation of column scans.

HammerDB by default provides TPC-H schemas at Scale Factors 1,10,30,100,300 and 1000 (larger can be

configured if required). The Scale Factors correspond to the schema size in Gigabytes. As with the official

TPC-H tests the results at one schema size should not be compared with the results derived with another

the SGA) reads into the sessions’ PGA. Consequently the emphasis is more on PGA sizing as opposed to SGA

for memory and high bandwidth read performance on the storage. With this traditional parallel execution

and modern CPU capabilities I/O read performance is typically the constraining factor. Note that also in

contrast to an OLTP workload high throughput redo log write performance is not a requirement, however in

similarity to the OLTP workload storage based on SSD disks will usually offer significant improvements in

performance over standard hard disks although in this case it is the benefits of read bandwidth as opposed

to the IOPs benefits of SSDs for OLTP.

When using the in-memory column store memory capacity and bandwidth feature and if fully cached in

memory storage performance is not directly a factor for query performance. Nevertheless data loads are an

important consideration for in-memory data and therefore I/O and SSD read performance remain important

This sections describes the procedure to install and configure the Load Generation Server (if one is used)

Installation and configuration of the Oracle Database on your chosen operating system is beyond the scope

of this document. You should have the Oracle database software installed, a test database created and

running and be aware of the database features you wish to configure for query performance. During the

installation make a note of your system user password, you will need it for the test schema creation. You

may at your discretion use an existing database however please note that HammerDB load testing can drive

your system utilization to maximum levels and therefore testing an active production system is not

recommended. In particular parallel execution workloads can use significant resources for low numbers of

users and result in table level checkpoints and therefore locking (TM enqueues) and caution is advised

before running on an instance shared with other workloads.

When your database server is installed you should create a tablespace into which the test data will be

the value for parallel_max_servers.

parallel_max_servers to a value of zero.