parallel load performance

From: David Turner <turner_at_tellme.com>
Date: Fri, 27 Apr 2001 04:03:32 -0700
Message-ID: <F001.002F3EBF.20010427031549@fatcity.com>

I'm getting slower loads using parallel than a serial load. I set up a test case and am pasting it for you to see what I am doing wrong.

I'm on a Sun 220R, 2 CPUs, 2 GB Ram, 50GB Raid 5 with Veritas, and Oracle 8.1.7 Standard Edition.

Here's my parallel parms

parallel_server FALSE
parallel_server_instances 1
recovery_parallelism 0

fast_start_parallel_rollback LOW
partition_view_enabled TRUE

parallel_broadcast_enabled FALSE
parallel_adaptive_multi_user TRUE
parallel_threads_per_cpu 2
parallel_automatic_tuning FALSE

optimizer_percent_parallel 0

parallel_min_percent 0
parallel_min_servers 4
parallel_max_servers 20
parallel_instance_group
parallel_execution_message_size 2148

#File that calls scripts to load

#/bin/ksh

rm -f *.log

sqlplus -s / <<EOF
truncate table test_tbl;
select degree from user_tables where table_name='TEST_TBL'; exit
EOF #Parallel
sqlldr userid=/ control=parallel.ctl log=parallel.log parallel=true direct=true

sqlplus -s / <<EOF
truncate table test_tbl;
alter table test_tbl parallel (degree 1); exit
EOF #Serial
sqlldr userid=/ control=serial.ctl log=serial.log parallel=false direct=true

grep "time was" *

--Create table file

conn /

drop table test_tbl;

create table test_tbl(id number(4))
storage(initial 2m next 2m pctincrease 0 minextents 2) parallel (degree 2);

--

Serial load ctl file

LOAD DATA
INFILE 'serial.dat'
APPEND
INTO TABLE TEST_TBL
(id)

--

Parallel load ctl file

LOAD DATA
INFILE 'parallel1.dat'
INFILE 'parallel2.dat'
APPEND
INTO TABLE TEST_TBL
(id)

--

The dat files are just files with a zero on each line.

The serial file is simply a cat of parallel1.dat and parallel2.dat

I was using a million rows in the parallelX.dat files and 2 million in the serial.

The serial load actually runs faster.

Never could get the parallel to run faster.

Thanks for any help, Dave Turner

--

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

And you thought James Bond's watch was cool... http://www.tellme.com Call 1-800-555-TELL for stocks, sports, news...& much more!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: David Turner
  INET: turner_at_tellme.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Apr 27 2001 - 06:03:32 CDT