Re: parallel load performance

This is great info from you as usual. I am using one controller on top of a T1 raid array. I'll recreate the tablespaces with direct IO files, and see how it goes. It's
a test system, so I can do what I want with it.

Thanks, Dave
On Fri, Apr 27, 2001 at 08:09:22PM -0700, Jared Still wrote:
>
> What's the load like on this machine when you're loading?
>
> Are other users on the box?
>
> Don't expect to get much help from parallel loads with
> only 2 processors. Personally, I wouldn't set parallel_max_servers
> above 4 on this box.
>
> Also, is the 50GB all on one mount point? Going through one controller?
> ( hint, hint )
>
> One Solaris you're not able to use async IO unless you're using raw
> disk or direct IO with Veritas.
>
> If you're on cooked f/s you should turn async io off and start up
> several db writers. Start with one per each physical disk that the
> data you're trying to load is striped across.
>
> Jared
>
>
> On Friday 27 April 2001 04:15, David Turner wrote:
> > 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).