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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
