This SLOB recipe shows data loading of 8 terabyte scale in the SLOB Multiple Schema Model (64 schemas of 128GB each) with Oracle Database 12c.

The test platform was a single 2-socket E5-2690v2 server running Oracle Linux 6.5 attached to a single X-Brick XtremIO Array running 4.0 firmware.

Below you will find the output of along with the init.ora and slob.conf.

Before I started this data loading task I initialized a 10TB tablespace called PROD_DATA_LARGE in an ASM disk group comprised of a single XtremIO volume.


XtremIO GUI. 10TB Tablespace Creation Followed By 8TB SLOB Data Loading

As the output from shows the data loading procedure completed in roughly 5 hours, 45 minutes. SLOB data loading includes index creation and CBO statistics gathering as well as inserting data into tables.

$ sh ./ PROD_DATA_LARGE 64
NOTIFY  : 2015.07.15-15:11:26 :
NOTIFY  : 2015.07.15-15:11:26 : Begin SLOB setup.
NOTIFY  : 2015.07.15-15:11:26 : Load parameters from slob.conf: 

SCALE: 128G (16777216 blocks)

Connect strings to be used:

NOTIFY  : 2015.07.15-15:11:26 : Testing connectivity to the instance to validate slob.conf settings
NOTIFY  : 2015.07.15-15:11:26 : Testing Admin connect using "/ as sysdba"
NOTIFY  : 2015.07.15-15:11:26 : Successful test connection: "sqlplus -L / as sysdba"
NOTIFY  : 2015.07.15-15:11:26 : Dropping prior SLOB schemas. This may take a while if there is a large number of old schemas.
NOTIFY  : 2015.07.15-15:11:33 : Deleted 1 SLOB schema(s).
NOTIFY  : 2015.07.15-15:11:33 : Previous SLOB schemas have been removed
NOTIFY  : 2015.07.15-15:11:33 : Preparing to load 64 schema(s) into tablespace: PROD_DATA_LARGE
NOTIFY  : 2015.07.15-15:11:33 : Loading user1 schema
NOTIFY  : 2015.07.15-15:17:46 : Finished loading, indexing and gathering statistics on user1 schema in 373 seconds
NOTIFY  : 2015.07.15-15:17:46 : Commencing multiple, concurrent schema creation and loading
NOTIFY  : 2015.07.15-15:17:49 : Waiting for background batch 1. Loading up to user17
NOTIFY  : 2015.07.15-16:40:29 : Finished background batch 1. Load / index create / stats gather in 4960 seconds
NOTIFY  : 2015.07.15-16:40:32 : Waiting for background batch 2. Loading up to user33
NOTIFY  : 2015.07.15-18:07:38 : Finished background batch 2. Load / index create / stats gather in 5226 seconds
NOTIFY  : 2015.07.15-18:07:40 : Waiting for background batch 3. Loading up to user49
NOTIFY  : 2015.07.15-19:34:43 : Finished background batch 3. Load / index create / stats gather in 5223 seconds
NOTIFY  : 2015.07.15-19:34:46 : Waiting for background batch 4. Loading up to user64
NOTIFY  : 2015.07.15-20:57:35 : Finished background batch 4. Load / index create / stats gather in 4969 seconds
NOTIFY  : 2015.07.15-20:57:35 : Completed concurrent data loading phase: 20389 seconds
NOTIFY  : 2015.07.15-20:57:35 : Creating SLOB procedure
NOTIFY  : 2015.07.15-20:57:35 : SLOB procedure created
NOTIFY  : 2015.07.15-20:57:35 : Row and block counts for SLOB table(s) reported in ./slob_data_load_summary.txt
NOTIFY  : 2015.07.15-20:57:35 : Please examine ./slob_data_load_summary.txt for any possbile errors
NOTIFY  : 2015.07.15-20:57:35 :
NOTIFY  : 2015.07.15-20:57:35 : NOTE: No errors detected but if ./slob_data_load_summary.txt shows errors then
NOTIFY  : 2015.07.15-20:57:35 : examine /data1/4.0/DONOR/SLOB/cr_tab_and_load.out

NOTIFY  : 2015.07.15-20:57:35 : SLOB setup complete. Total setup time:  (20769 seconds)

This is the slob.conf file:

$ cat slob.conf



# Settings for SQL*Net connectivity:

#### Advanced settings:
# The following are Hot Spot related parameters.
# By default Hot Spot functionality is disabled (DO_HOTSPOT=FALSE).

# The following controls operations on Hot Schema
# Default Value: 0. Default setting disables Hot Schema

# The following parameters control think time between SLOB
# operations (SQL Executions).
# Setting the frequency to 0 disables think time.



This is the very simple init.ora file:

$ cat init.ora


5 Responses to “SLOB Recipe: Loading 8TB of SLOB Data on EMC XtremIO with a Single 2-Socket Linux Host”

  1. 1 jokomoto September 13, 2018 at 12:19 am

    Just tried SLOB against and the initial Loading user1 to Finished loading, indexing, etc… takes 22 minutes, .vs on it takes 6 min. Any thoughts? Looks very serial, max 50MB/s .vs 300MB/s on

    • 2 kevinclosson September 28, 2018 at 9:59 am

      Same slob.conf? Same init.ora? Same hardware?

      • 3 jokomoto September 28, 2018 at 1:24 pm

        Same slob.conf, same init.ora, same hardware. I did find out what the problem was. I messed up on my redo size config, which was way too small. After re-configuration, the overall load performance is actually faster on 12.1 than 11.2. I also upped my db_writer_process=4 and was able to trim the load time even further. Highest wait event when db_writer_process=2 (default) was buffer_busy_waits.

        • 4 kevinclosson October 1, 2018 at 3:50 pm

          Good troubleshooting! Maybe you should blog that so others can benefit?

          • 5 jokomoto October 3, 2018 at 11:08 am

            A prospect while evaluating our product had an Oracle 12 database running decided to run a comparison test with their existing product. Their result while running the SLOB load process on their previous product took 76 minutes. While it took 100 minutes when using our product. They did a simple ASM migration to move the data. I decided to run the same workload in my lab and had an existing Oracle database to run SLOB load against. Right out of the box, It ran in 55 minutes. I then stood up an Oracle 12 database (both using ASM), ran the test and it ran for several hours, so I knew I messed something up. After dumping an AWR I noticed that the database was switching logs like crazy, I realized I undersized my redo groups. They were 10M instead of 10G. After resizing, the 12c load took 48 minutes.

            I then wanted to reduce the front-end initial schema load, index creation and stats gather processes, which took 10 minutes. I noticed that I had a high buffer busy wait events during that time. So I upped the db_writer_process from the default of 2 to 4, and reduced the front-end process by 1/2 to 5 minutes. So the total run is now down to 43 minutes, with archiving turned on.

            Moral of the story, check and recheck your configs. Size your redo to match your desired log switch rate. Use AWR to monitor your progress. Make sure you take AWR snaps according to what portion of the test you want to gather data from.

Leave a Reply to jokomoto Cancel reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,006 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories


All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: