SLOB Data Loading Case Studies – Part I. A Simple Concurrent + Parallel Example.

Introduction

This is Part I in a short series of posts dedicated to loading SLOB data.  A link to Part II is provided below.

The SLOB loader is called setup.sh and it is, by default a concurrent, data loader. The SLOB configuration file parameter controlling the number of concurrent data loading threads is called LOAD_PARALLEL_DEGREE. In retrospect I should have named the parameter LOAD_CONCURRENT_DEGREE because unless Oracle Parallel Query is enabled there is no parallelism in the data loading procedure. But if LOAD_PARALLEL_DEGREE is assigned a value greater than 1 there is concurrent data loading.

Occasionally I hear of users having trouble with combining Oracle Parallel Query with the concurrent SLOB loader. It is pretty easy to overburden a system when doing something like concurrent, parallel data loading–in the absence of tools like Database Resource Management I suppose. To that end,  this series will show some examples of what to expect when performing SLOB data loading with various init.ora settings and combinations of parallel and concurrent data loading.

In this first example I’ll show an example of loading with LOAD_PARALLEL_DEGREE set to 8. The scale is 524288 SLOB rows which maps to 524,288 data blocks because SLOB forces a single row per block. Please note, the only slob.conf parameters that affect data loading are LOAD_PARALLEL_DEGREE and SCALE. The following is a screen shot of the slob.conf file for this example:

SLOB-data-load-3

The next screen shot shows the very simple init.ora settings I used during the data loading test. This very basic initialization file results in default Oracle Parallel Query, therefore  this example is a concurrent + parallel data load.

SLOB-data-load-6

The next screen shot shows that I directed setup.sh to load 64 SLOB schemas into a tablespace called IOPS. Since SCALE is 524,288 this example loaded roughly 256GB (8192 * 524288 * 64) of data into the IOPS tablespace.

SLOB-data-load-1

As reported by setup.sh the data loading completed in 1,539 seconds or a load rate of roughly 600GB/h. This loading rate by no means shows any intrinsic limit in the loader. In future posts in this series I’ll cover some tuning tips to improve data loading.  The following screen shot shows the storage I/O rates in kilobytes during a portion of the load procedure. Please note, this is a 2s16c32t 115w Sandy Bridge Xeon based server. Any storage capable of I/O bursts of roughly 1.7GB/s (i.e., 2 active 8GFC Fibre Channel paths to any enterprise class array) can demonstrate this sort of SLOB data loading throughput.

SLOB-data-load-2

 

After setup.sh completes it is good to count how many loader threads were able to successfully load the specified number of rows. As the example shows I simply grep for the value of slob.conf->SCALE from cr_tab_and_load.out. Remember, SLOB in its current form, loads a zeroth schema so the return from such a word count (-l) should be one greater than the number of schemas setup.sh was directed to load.

SLOB-data-load-4

The next screen shot shows the required execution of the procedure.sql script. This procedure must be executed after any execution of setup.sh.

SLOB-data-load-7

Finally, one can use the SLOB/misc/tsf.sql script to report the size of the tablespace used by setup.sh. As the following screenshot shows the  IOPS tablespace ended up with a  little over 270GB which can be accounted for by the size of the tables based on slob.conf, the number of schemas and a little overhead for indexes.

 

SLOB-data-load-5

Summary

This installment in the series has shown expected screen output from a simple example of data loading. This example used default Oracle Parallel Query settings, a very simple init.ora and a concurrent loading degree of 8 (slob.conf->LOAD_PARALLEL_DEGREE) to load data at a rate of roughly 600GB/h.

Part II in the series: Part II (Click it)

 

 

3 Responses to “SLOB Data Loading Case Studies – Part I. A Simple Concurrent + Parallel Example.”


  1. 1 Hamid Reza Moradi February 7, 2018 at 1:07 pm

    during the installation I get following error:

    SQL> SQL> ALTER TABLE cf1 MINIMIZE RECORDS_PER_BLOCK
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-12805: parallel query server died unexpectedly

    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
    FATAL : 2018.02.07-20:00:21 :
    FATAL : 2018.02.07-20:00:21 : f_setup_base_table: Failed to create base table
    FATAL : 2018.02.07-20:00:21 :
    FATAL : 2018.02.07-20:00:21 :
    FATAL : 2018.02.07-20:00:21 : f_setup: Failed to load user1 SLOB table
    FATAL : 2018.02.07-20:00:21 :

    It is a 4 nodes RAC, can you guide about the possible problems?


  1. 1 SLOB Data Loading Case Studies – Part II. SLOB 2.2 For High-Bandwidth Data Loading. | Kevin Closson's Blog: Platforms, Databases and Storage Trackback on December 15, 2014 at 11:44 pm

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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.




DISCLAIMER

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 744 other subscribers
Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

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: