Oracle 12c load testing withThe Silly Little Oracle Benchmark(SLOB)

SLOB is the best SQL workload to drive maximum physical random I/O from a database platform.

SLOB is an I/O intensive SQL generator, instead of a synthetic I/O generator, with the following characteristics:

Ø  Supports testing physical, random single-block reads (database file sequential read/DB file parallel read)

Ø  Supports testing random single-block writes (database file parallel write)

Ø  Supports testing extreme REDO logging I/O

Ø  Consists of simple PL/SQL

Installation:

Installation steps are below:

1.       Install the latest SLOB RPM

yum install http://yum.outrun.nl/outrun-extras.rpm

2.       Go to the url "https://kevinclosson.net/slob/" and then click on SLOB 2.3: Click Here link.

This link will redirect on " https://my.syncplicity.com/share/ize90aqgqy9yj1d/2015.07.16.slob_2.3.0.3-1.tar " page, then click on download button to download the SLOB 2.3 in .tar file format.

3.       Copy the downloaded tar file on the directory as you have ORACLE BASE.

4.       To unpack or extract a tar file need to execute below command:

[oracle@localhost u01]$ tar -xvf 2015.07.16.slob_2.3.0.3-1.tar.gz

It will create the SLOB directory with the below contents:

[oracle@localhost SLOB]$ ls

advanced_topics    create_sem    iostat.out    RELEASE    slob.conf    wait_kit   awr    cr_tab_and_load.out    LICENSE.txt    runit.sh    slob_data_load_summary.txt db_stats.out    misc    run.log    slob.sql     awr_rac.html.gz    doc mpstat.out   setup.sh tm.out   drop_users.log    mywait    simple.ora     trigger    drop_users.sql   README   slobAWR.html vmstat.out

[oracle@localhost SLOB]$

Execution:

Execution steps are below:

1.       Create a tablespace to load SLOB schemas.

2.       We have to make the wait kit which is used to kick off all of the SLOB session.

[oracle@localhost wait_kit]$ make all

3.       We can edit/tune the slob.conf file to modify the default parameter settings, if you needed. Because setup.sh/runit.sh will use slob.conf file to execute. Contents slof.conf it are:

UPDATE_PCT=25

RUN_TIME=300

WORK_LOOP=0

SCALE=80M

WORK_UNIT=64

REDO_STRESS=LITE

LOAD_PARALLEL_DEGREE=4

THREADS_PER_SCHEMA=1

# Settings for SQL*Net connectivity:

#ADMIN_SQLNET_SERVICE=slob

#SQLNET_SERVICE_BASE=slob

#SQLNET_SERVICE_MAX=2

#SYSDBA_PASSWD=change_on_install

#########################

#### Advanced settings:

#

# The following are Hot Spot related parameters.

# By default Hot Spot functionality is disabled (DO_HOTSPOT=FALSE).

#

DO_HOTSPOT=FALSE

HOTSPOT_MB=8

HOTSPOT_OFFSET_MB=16

HOTSPOT_FREQUENCY=3

#

# The following controls operations on Hot Schema

# Default Value: 0. Default setting disables Hot Schema

#

HOT_SCHEMA_FREQUENCY=0

# The following parameters control think time between SLOB

# operations (SQL Executions).

# Setting the frequency to 0 disables think time.

#

THINK_TM_FREQUENCY=0

THINK_TM_MIN=.1

THINK_TM_MAX=.5

#########################

export UPDATE_PCT RUN_TIME WORK_LOOP SCALE WORK_UNIT LOAD_PARALLEL_DEGREE REDO_STRESS

export DO_HOTSPOT HOTSPOT_MB HOTSPOT_OFFSET_MB HOTSPOT_FREQUENCY HOT_SCHEMA_FREQUENCY THINK_TM_FREQUENCY THINK_TM_MIN THINK_TM_MAX

********************************************************************

SLOB Tuneable Parameters: The description of some important parameters are:

UPDATE_PCT The UPDATE_PCT parameter controls what percentage of SLOB operations that will modify blocks of data (modify DML.)

Values between 51 and 99 are non-­deterministic. A value of zero is a 100% SQL SELECT workload.

RUN_TIME The RUN_TIME parameter controls the wall-­clock duration of a SLOB test in seconds. Set RUN_TIME to an integer value and the SLOB runit.sh script will terminate the execution of the test accordingly. Note, RUN_TIME can be overridden with WORK_LOOP. If RUN_TIME is set, then WORK_LOOP should be set to zero.

WORK_LOOP The WORK_LOOP parameter is used to control SLOB test duration based on iterations of the SLOB work loop.

 SCALE The SCALE parameter controls both data loading and test execution behaviour. SCALE can be assigned simple integer values or integer values modified by M/G/T (megabytes, gigabytes, terabytes) nomenclature.

WORK_UNIT During SLOB testing WORK_UNIT controls the scope of blocks being manipulated by each SLOB operation. For example, if WORK_UNIT is set to 32 then each SQL SELECT and UPDATE will scope and manipulate 32 random blocks of data in the tablespace. When testing with high levels of UPDATE_PCT (e.g., 50%) one generally sets WORK_UNIT small (e.g., 16) so as to not burden the UNDO functionality of Oracle Database.

REDO_STRESS Set this parameter to either HEAVY or any other non-­null value. When set to HEAVY SLOB will generate significant amounts of redo logging (e.g., hundreds of megabytes per second on high performance platforms).

LOAD_PARALLEL_DEGREE The LOAD_PARALLEL_DEGREE parameter serves two purposes depending on loading SLOB Single or Multiple Schema.

THREADS_PER_SCHEMA this parameter controls how many SLOB threads (Oracle Database sessions) will be performing SLOB operations against each schema during a performance test.

ADMIN_SQLNET_SERVICE If you want all SYSDBA connections to go through a specific TNS names service then set this parameter accordingly. For example

ADMIN_SQLNET_SERVICE=SLOBTEST

SQLNET_SERVICE_BASE This parameter serves multiple purposes. If SQLNET_SERVICE_BASE is set but SQLNET_SERVICE_MAX is NULL, then SQLNET_SERVICE_BASE will be used during execution of runit.sh to direct all SLOB sessions to this service.

 Round Robin SQL*Net Connections

SQLNET_SERVICE_MAX If set to a non-­zero integer SQLNET_SERVICE_MAX is the highest integer value appended to SQLNET_SERVICE_BASE in a Real Application Clusters testing scenario.

SYSDBA_PASSWD If ADMIN_SQLNET_SERVICE is set then the scripts (setup.sh and runit.sh) will need a password to connect to the database instance via a SQL*Net service as SYSDBA.

SQL*Net connectivity Configuration

4.       There are two scenarios of SQL*Net connectivity configuration, and we can follow any one.

a)      No need to edit/set any SQL*Net connectivity parameters in slob.conf file. In this case require to configure TNS entries and set the SYS password according to default given values in slob.conf.

TNS entries:

[oracle@localhost admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

SLOB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT =1555))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = slob)

    )

  )

[oracle@localhost admin]$

Password

Set the password of SYSDBA as “change_on_install”

b)      Edit/set the SQL*Net connectivity parameters in slob.conf file as per your database.

Data Loading

5.       [oracle@localhost SLOB]$ ./setup.sh slobtest 128

The setup.sh script is used to load SLOB data. The script takes two mandatory options:

·         Tablespace into which SLOB will create and load the test schemas

·         The number of schemas to create and load.

 The setup.sh script takes directives from slob.conf. We have to tell setup.sh which tablespace to use and how many SLOB users (schemas) load. The slob.conf file tells setup.sh how much data to load. This example is 128 SLOB schema and slobtest tablespace.

 Performance Testing:

6.       [oracle@localhost SLOB]$ ./runit.sh 128

The runit.sh script is the performance test driver. By default, it is uses the parameter settings in slob.conf. However, one can override certain slob.conf settings with command line options.

Nos of schema of slob to test is 128 in above runit.sh parameter. It will show the output on the screen after completion the script execution, and generate the awr.txt (Workload repository report) file to check the performance.

 NOTIFY : 2017.04.05-07:24:50 : Testing SYSDBA connectivity to the instance to validate slob.conf settings.

NOTIFY : 2017.04.05-07:24:50 : Testing connectivity. Command: "sqlplus -L / as sysdba"

NOTIFY : 2017.04.05-07:24:50 : Testing connectivity. Command: "sqlplus -L user1/user1"

NOTIFY : 2017.04.05-07:24:50 : Testing connectivity. Command: "sqlplus -L user128/user128"

NOTIFY : 2017.04.05-07:24:50 : Performing redo log switch.

NOTIFY : 2017.04.05-07:24:55 : Redo log switch complete.

NOTIFY : 2017.04.05-07:24:55 : Setting up trigger mechanism.

NOTIFY : 2017.04.05-07:25:05 : Running iostat, vmstat and mpstat on current host--in background.

NOTIFY : 2017.04.05-07:25:05 : Connecting 1 sessions to 128 schema(s) ...

NOTIFY : 2017.04.05-07:25:09 :

NOTIFY : 2017.04.05-07:25:09 : Pausing for 5 seconds before triggering the test.

NOTIFY : 2017.04.05-07:25:16 :

NOTIFY : 2017.04.05-07:25:16 : List of monitored sqlplus PIDs written to /tmp/.22460_slob_pids.out

NOTIFY : 2017.04.05-07:25:22 : Waiting for 292 seconds before monitoring running processes (for exit).

NOTIFY : 2017.04.05-07:30:14 : Entering process monitoring loop.

NOTIFY : 2017.04.05-07:30:24 : There are 12 sqlplus processes remaining.

NOTIFY : 2017.04.05-07:30:56 :

NOTIFY : 2017.04.05-07:30:56 : SLOB test is complete.

Result (Output)

Generated AWR Report.

Note: We have used default parameters of slof.conf in the above test case.