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-8TB-data-load

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
$

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



  1. Leave a Comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s




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 2,974 other followers

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: