SLOB

Reference

https://kevinclosson.net/2012/02/06/introducing-slob-the-silly-little-oracle-benchmark/

Introduction

SLOB is an Oracle I/O workload generation tool kit to test physical and logical I/O in terms of speed and performance. In this post I explain how I used SLOB as a benchmark to determine if Oracle Direct NFS was a good option to use on our Solaris database servers. But there are plenty of other scenario's you can use with SLOB.

Step 1 Download the tool

You can download the latest tar set from the following location: https://github.com/therealkevinc/SLOB_distribution

Step 2 Upload & stage the SLOB Tool

Upload the SLOB tool to the database server and unpack:

$ pwd
/home/oracle/dbadir/rob/slob

$ ls
2018.04.16.slob_2.4.2.1.tar.gz

$ gunzip 2018.04.16.slob_2.4.2.1.tar.gz

$ ls
2018.04.16.slob_2.4.2.1.tar

$ tar -xf 2018.04.16.slob_2.4.2.1.tar

$ ls
2018.04.16.slob_2.4.2.1.tar  SLOB

Edit the file SLOB.conf and change

DATABASE_STATISTICS_TYPE=statspack

to:

DATABASE_STATISTICS_TYPE=awr

Step 3 Compile the SLOB utility

Workaround for Solaris:

$ export PATH=PATH=$PATH:/usr/sfw/bin
$ which gcc
/usr/sfw/bin/gcc

-- Edit the Makefile. Da a global replace:
$ 1,$ s/cc/gcc/g

Then run the make command:

$ /home/oracle/dbadir/rob/slob/SLOB/wait_kit>make
rm -fr *.o mywait trigger create_sem 
gcc   -c  mywait.c
gcc -o mywait mywait.o 
gcc   -c  trigger.c
gcc -o trigger trigger.o
gcc   -c  create_sem.c
gcc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o

Step 4 Create a SLOB tablespace & sample data

Create the tablespace:

SQL> set echo on
SQL> set timing on

SQL> @/home/oracle/dbadir/slob/SLOB/misc/ts.sql

SQL> create BIGFILE tablespace IOPS datafile'/u02/app/oracle/oradata/TSTDB/TSTDB/iops01.dbf' size 1G
  2  NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ;

Tablespace created.

Elapsed: 00:00:14.20

Create the sample data:

SQL> !cat gen_data.sql
set timing on
spool gen_data.log

create table robla.t1 as
select 
rownum as id, 
'Just some text' as textcol,
mod(rownum,5) as numcol1, 
mod(rownum,1000) as numcol2 , 
5000 as numcol3, 
to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018' ,'dd.mm.yyyy') as time_id  
from dual connect by level<=1e7 ; 
 
select count(*) from robla.t1;

select bytes/1024/1024 as mb from user_segments where segment_name='T1';
spool off

SQL> @gen_data.sql
Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:01:11.52

  COUNT(*)
----------
  10000000

Elapsed: 00:00:14.94

SQL> select bytes/1024/1024 as mb from dba_segments where segment_name = 'T1';

        MB
----------
       480

Elapsed: 00:00:00.38

Now copy table t1 to some other tables

SQL> create table robla.t2 as select * from robla.t1;

Table created.

Elapsed: 00:00:18.00
SQL> create table robla.t3 as select * from robla.t1;

Table created.

Elapsed: 00:00:23.19

Step 2 Prepare the database for SLOB

The following steps can be followed to prepare the database for the SLOB tests:

• Turn off archive log mode in the database. There is no point in generating gigabytes of archivelogs while setting up your test.
• Create the SLOBDATA tablespace. I recommend using a BIGFILE tablespace allowing up to 32TB of data.
• Switch the database to use a BIGFILE temporary tablespace. SLOB does not generate temporary data like Swingbench does, but some temporary data is generated.
• Resize the redo log files to at least 1GB. The default 50MB redo logs will cause excessive log file swicthes which will slow the data generation.
• Set the parameter filesystemio_options to SETALL to faciliate parallel queuing of redo writes.
• Set the DB_WRITER_PROCESSES parameter to at least the CPU count divided by 4 to maximize write performance during the load. If your server can handle a greater write load then increase the value of DB_WRITER_PROCESSES.
• Set the LOAD_PARALLEL_DEGREE in the slob.conf file to twice the number of CPUs as seen by Oracle. This will allow SLOB to generate data in parallel.
• Create a public directory on the /tmp directory.