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 setup.sh 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 setup.sh 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 ./setup.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) LOAD_PARALLEL_DEGREE: 16 ADMIN_SQLNET_SERVICE: "" SQLNET_SERVICE_BASE: "" Connect strings to be used: ADMIN_CONNECT_STRING: "/ as sysdba" NON_ADMIN_CONNECT_STRING: " " 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 UPDATE_PCT=50 RUN_TIME=120 WORK_LOOP=0 SCALE=128G WORK_UNIT=3 REDO_STRESS=LITE LOAD_PARALLEL_DEGREE=16 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=2 # 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 $
This is the very simple init.ora file:
$ cat init.ora *.audit_file_dest='/u01/app/oracle/admin/PROD/adump' *.audit_trail='none' *.cluster_database=true *.compatible='12.1.0.2.0' *.control_files='+PROD_REDO/PROD/CONTROLFILE/current.256.884850215' *.db_block_size=8192 *.db_create_file_dest='+PROD_DATA' *.db_create_online_log_dest_1='+PROD_REDO' *.db_domain='' *.db_name='PROD' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)' PROD1.instance_number=1 PROD2.instance_number=2 *.open_cursors=300 *.pga_aggregate_target=2048m *.processes=2000 *.remote_login_passwordfile='exclusive' PROD2.thread=2 PROD1.thread=1 PROD2.undo_tablespace='UNDOTBS2' #PROD1.undo_tablespace='UNDOTBS1' undo_tablespace='UNDOTBS01' db_cache_size=32G shared_pool_size=8G db_files=3000 $
Kevin Closson's Blog: Platforms, Databases and Storage 
Just tried SLOB against 12.1.0.2 and the initial Loading user1 to Finished loading, indexing, etc… takes 22 minutes, .vs on 11.2.0.4 it takes 6 min. Any thoughts? Looks very serial, max 50MB/s .vs 300MB/s on 11.2.0.4.
Same slob.conf? Same init.ora? Same hardware?
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.
Good troubleshooting! Maybe you should blog that so others can benefit?
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 11.2.0.4 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.
First time using slob, when I run the setup for the first time I get the following:
oracle@linuxdb1.localdomain.com:/u01/app/oracle/product/slob/SLOB> ./setup.sh IOPS 16
NOTIFY : 2021.01.04-11:51:00 : Begin SLOB 2.5.2.4 setup.
NOTIFY : 2021.01.04-11:51:00 : ADMIN_CONNECT_STRING: “system/xyz”
NOTIFY : 2021.01.04-11:51:00 : Load parameters from slob.conf:
SCALE: 10000 (10000 blocks)
SCAN_TABLE_SZ: 1M (128 blocks)
LOAD_PARALLEL_DEGREE: 2
ADMIN_SQLNET_SERVICE: “”
SYSDBA_PASSWD: “xyz”
DBA_PRIV_USER: “system”
Note: setup.sh will use the following connect strings as per slob.conf:
Admin Connect String: “system/xyz”
Non-Admin Connect String: ” ”
NOTIFY : 2021.01.04-11:51:00 : Testing Admin connect using “sqlplus -L system/Oracleadmin!”
./setup.sh: line 250: [[: 0
1: syntax error in expression (error token is “1”)
./setup.sh: line 252: return: too many arguments
FATAL : 2021.01.04-11:51:03 :
FATAL : 2021.01.04-11:51:03 : Please run the following query:
SELECT COUNT(*) FROM V$SESSION WHERE USERNAME LIKE ‘USER%’;
FATAL : 2021.01.04-11:51:03 : There are existing connections to the database.
FATAL : 2021.01.04-11:51:03 : Cannot drop schemas while sessions are connected.
FATAL : 2021.01.04-11:51:03 : Abort.
When I run the select, it returns 0 rows.
Any help will be appreciated 🙂
Are you installing SLOB on Linux or Unix?