Skip to content

Oracle Database 12c

The Oracle Database application suite is still one of the most widely deployed commercial database apps today. It is used to support a very wide variety of usecases such as ERP systems, CRM systems, E-Commerce solutions etc.. Given the nature of the commercial licensing, performance and tuning are critical to ensure that a consumer gets the optimal performance per $ out of their investment.

On this page we provide the methodologies and tools used by us to setup and evaluate the performance of Oracle database running on different platforms and configurations. The more details are provided in the following sections.

  1. Testing Tool
  2. Platform and VM Instance
  3. Oracle Database Instance Setup
  4. Tests Description
  5. Results

Testing Tool

When the Oracle database is the choice for your application, it is needed to assess Oracle random physical I/O capability on a given environment/platform in preparation for OLTP/ERP style workloads. There is no shortage of transactional benchmarks such as Dominic Giles’ Swingbench and cost-options such as Benchmark Factory. However, the problem with these kits is these kits are transactional so they are more suitable to prove Oracle scales those applications, rather than test database I/O characteristics. These transactional test kits involve physical I/O but the ratio of CPU utilisation to handle physical I/O is generally low and most CPU resources are being used to execute Oracle intrinsic transaction code paths.

Oracle SLOB – the Silly Little Oracle Benchmark is a complete toolkit for generating I/O through an Oracle database. SLOB is the tool to analyse the I/O capabilities of your Oracle database. SLOB testing has been used by many industrial vendors to show the SLOB results in their use cases, which are available here as a collection of articles.

The focus of the tests covered in this page is the database I/O characteristics, so SLOB is utilised to analyse the performance of database I/O. Considering SLOB is just an Oracle I/O workload generation tool, rather than a database benchmark tool, there isn't a direct performance indicator provided by SLOB. The actual I/O performance will be assessed by the standardised set of performance metrics and resource metrics collected by Oracle database. The metrics and their indications will be introduced more in details when they are used for evaluating the performance in the sections below.

SLOB Setup and Configuration

The SLOB kit is available to be downloaded here at SLOB repository. A full documentation for usage of SLOB is available at this PDF file.

The SLOB's configurations involved in the performance tests are follows. For more guidance about the tunable parameters of SLOB please find them in the SLOB documents.

Parameter Description
UPDATE_PCT The UPDATE_PCT parameter controls what percentage of SLOB operations that will modify blocks of data (modify DML.) Setting UPDATE_PCT to zero results in a 100% SQL SELECT workload.
RUN_TIME The RUN_TIME parameter controls the wall-clock duration of a SLOB test in seconds.
SCALE The SCALE parameter controls the size of data to be loaded into each SLOB schema.
LOAD_PARALLEL_DEGREE parameter controls the number of Oracle Database sessions concurrently inserting data into the “base schema.”
THREADS_PER_SCHEMA The THREADS_PER_SCHEMA parameter controls how many SLOB threads (Oracle Database sessions) will be performing SLOB operations against each schema during a performance test.
DATABASE_STATISTICS_TYPE The default value is statspack. To generate Automatic Workload Repository (AWR) reports, set this parameter to “awr”.

Platform and VM Instance

The platforms and VM instances hosting the Oracle database to be tested are configured as shown in the following table.

Platform Hardware Type Instance Storage Number of Cores per Instance
AWS R5 EBS General Purpose SSD (gp2) - up to 3000 IOPS up to 96
AWS R5 EBS Provisioned IOPS SSD (io1) - up to 80000 IOPS up to 96
VMware on AWS i3.metal vSAN with NVMe SSD from i3.metal up to 36
Sunlight on AWS i3.metal SDS with NVMe SSD from i3.metal up to 70
Sunlight on premise Intel HNS2600BPB24 SDS with Intel P4610 NVMe drive up to 38
Sunlight on premise Intel HNS2600BPB24 SDS with Intel Optane NVMe drive up to 38

For the tests running across the different platform and instance setup listed above, the operating system of the instance hosting the Oracle database is always configured with the same approach. The Oracle database has been tested with the following host configuration.

Resource Value
Host OS CentOS Linux release 7.5.1804 (Core)
Kernel Version 5.2.14-1.el7.elrepo.x86_64
Package manager Yum
Application version Oracle Database 12c Enterprise Edition Release - 64bit Production

Oracle Database Instance Setup

The instance hosting the Oracle database is configured as the steps listed below.

Host Environment Configuration

Replace the kernel as stock kernel does not have multiqueue blkfront.

sudo rpm --import
sudo yum -y install
sudo yum --enablerepo=elrepo-kernel install kernel-ml
sudo grub2-set-default 0
sudo reboot

The host operating system has been configured in the /etc/sysctl.conf file as the following setups.

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 6180853
kernel.shmmax = 31645970432
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv6.conf.lo.disable_ipv6 = 1
vm.overcommit_ratio = 100

Oracle Database Installation

Install the prerequisite packages

sudo yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc.i686 libstdc.x86_64 libstdc-devel.i686 libstdc+-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip smartmontools bzip2 java-1.8.0
sudo yum groupinstall -y "X Window System"

Install the Oracle Database

Download the Oracle database package and install it using the installer wizard. More information about Oracle Database installation is available at Installation Guide provided by Oracle.

Tests Description

There are a set of variables involved in the tests to be adjusted for illustrating the performance metrics we are interested in. These variables are oriented from the resources required by the Oracle database and they are independent to any platform or hardware.

Variable Where to adjust Influence
Number of cores Specifying the number of cores when the instance is created. Deciding the CPU resources available for the Oracle database to process both queries (SQL) and I/O.
Number of schemas Specifying the number of schemas when running the setup script of SLOB and starting the SLOB workload. Deciding the number of schemas loaded to the database during setup, and controlling the number of users to stress load the database when running the SLOB workload, since each schema belongs to a user.
Schema size The SCALE parameter in the slob.conf file. Deciding the size of the schema in the database.
Buffer cache size The sga_target parameter of Oracle database defined in the create.ora file. Deciding the amount of data to be cached in the memory, resulting in the cached data could be read from memory instead of physical disk.
Read/Write ratio The UPDATE_PCT parameter in the slob.conf file. Allow to test read/write performance only or a combination of read and write with certain ratio. The UPDATE_PCT parameter controls what percentage of SLOB operations that will modify blocks of data. Note: Oracle database will first read a block of data before modifying it.

There are also a set of metrics which are indicators of the performance or resource on certain aspects in Oracle database. The value of these metrics are available in the awr report when the SLOB workload is completed. These metrics are analysed coherently to understand the specific performance metric when the resource metric(s) is within certain range.

Metric Indication
Executes (SQL) per second This metric represents the rate of SQL command executions over one second.
Read+Write/Second This metric represents the number of data blocks read from disk and written to disk per second during one second.
db file sequential read This metric represents a wait event initiated by SQL statements that perform single-block read operations, i.e. waiting for a physical I/O call to return.
Buffer hit ratio This metric is a measure of the effectiveness of the Oracle data block buffer. The higher the buffer hit ratio, the more frequently Oracle found a data block in memory and avoid a disk I/O.
Idle CPU percentage This metric represents the percentage of CPU cycles which are idle.

Performance with increased number of cores

Test Setup

In order to test the performance of Oracle database when different number of cores are used by the instance, the other variables are selected accordingly to reflect the real-world setup as much as possible. With the increased number of cores, the number of schemas in the Oracle database are also increased to increase the load proportionally. The memory allocated to the instance and the buffer cache size of Oracle database are also increased. However, since the focus of this test is physical I/O performance, the instance memory and in particular the buffer cache size are configured in relatively small values. These variables are summarised in the table below.

Number of cores Memory size Disk size Oracle sga_target parameter SLOB number of schemas SLOB number of threads Read/Write ratio
8 16 500GB 1500M 4 1 1
16 32 500GB 2000M 8 1 1
32 64 500GB 3000M 16 1 1

The AWS R5 instance type has fixed configuration on number of cores and memory size, so the memory size on the AWS R5 instance is larger than other platforms, which however shouldn't cause difference as the memory usage by Oracel has been limited by the sga_target parameter. For the provisioned IOPS SSD attached to the AWS R5 instance, it is designed to deliver a consistent baseline performance of up to 50 IOPS/GB to a maximum of 64,000 IOPS. Thus, the disk size for the instance on AWS R5 configured to be large enough to achieve the maximum IOPS of each instance type.


The following results are the average performance and resource metrics captured during the SLOB generates workloads for 5 minutes.

Number of cores Executes (SQL) per second Read+Write/Second db file sequential read Idle CPU % Buffer Hit %
8 314.9 18724.4 2170 97.1 6.13
16 321.7 18709.9 3210 98.5 7.48
32 497.4 29800.4 2270 98.8 5.83
96 1040.6 63577 2300 98.1 4.85
VMware on AWS
Number of cores Executes (SQL) per second Read+Write/Second db file sequential read Idle CPU % Buffer Hit %
8 1063.6 65282.1 570.99 89.8 5
16 1150.6 70192.6 1450 93.7 5.88
32 1075.3 65908.1 3420 96 5.37
Sunlight on AWS
Number of cores Executes (SQL) per second Read+Write/Second db file sequential read Idle CPU % Buffer Hit %
8 2127.9 131870.3 178.41 68.5 4.73
16 3099.4 192851 334.97 70.9 4.32
32 3375.3 207703.3 501.69 69.9 5.31
Sunlight on prem - Intel P4610
Number of cores Executes (SQL) per second Read+Write/Second db file sequential read Idle CPU % Buffer Hit %
8 1739.6 106221.8 206.31 64.1 5.89
16 2649.2 161918.7 248.62 63.2 5.86
32 3492.6 212258.5 315.99 61 5.94
Sunlight on prem - Intel Optane
Number of cores Executes (SQL) per second Read+Write/Second db file sequential read Idle CPU % Buffer Hit %
8 2041.4 126299.5 58.65 52.4 4.68
16 3010.1 185156.2 110.18 56.4 5.52
32 3570.5 218713.2 106.38 53.3 5.5

Oracle DB performance metric - Executes (SQL) per second Oracle DB performance metric - read latency Oracle DB performance metrics - executes per second & generated IOPS

Maximum random read IOPS of the instances on each platforms (measured by Fio)

Number of cores AWS R5 VMware on AWS Sunlight on AWS
8 18800 87000 200000
16 18800 105000 422000
32 30000 103000 351000
96 64000 n/a n/a

Oracle DB performance metric - Oracle generated IOPS vs. maximum IOPS capable on the instance

Performance with increased load

Oracle Database Performance measured by SLOB

Oracle Database Performance measured by SLOB

Oracle Database Performance measured by SLOB

Oracle Database Performance measured by SLOB