Re: Help with my sql load performance

From: <emdproduction_at_hotmail.com>
Date: 13 Apr 2007 08:44:52 -0700
Message-ID: <1176479092.002690.24840@y80g2000hsf.googlegroups.com>

On Apr 13, 11:37 am, DA Morgan <damor..._at_psoug.org> wrote:
> emdproduct..._at_hotmail.com wrote:
> > Dear group,
>
> > I am trying to increase my SQL Loader's performace. My table has a
> > lot of columns, 200 columns, but most of them are null. The typical
> > linesize is 800 bytes.
> > sqlldr userid=test/test_at_ftest
> > ntrol=load_temp.ctl bindsize=1000000
>
> > SQL*Loader: Release 9.2.0.8.0 - Production on Fri Apr 13 10:34:01 2007
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> > Commit point reached - logical record count 18
> > Commit point reached - logical record count 36
> > Commit point reached - logical record count 54
> > Commit point reached - logical record count 72
> > Commit point reached - logical record count 90
> > C................
>
> > Incrsease bindsize 10 times, got error that readsize is too small
>
> > sqlldr userid=test/test_at_test control=load_temp_table_v3.ctl
> > bindsize=10000000
>
> > SQL*Loader: Release 9.2.0.8.0 - Production on Fri Apr 13 10:35:35 2007
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> > specified value for readsize(1048576) less than bindsize(10000000)
> > Commit point reached - logical record count 64
> > Commit point reached - logical record count 128
> > Commit point reached - logical record count 192
>
> > Increase readsize, performance is the same
> > sqlldr userid=test/test_at_test co
> > ntrol=load_temp_table_v3.ctl bindsize=10000000 readsize=10000000
>
> > SQL*Loader: Release 9.2.0.8.0 - Production on Fri Apr 13 10:37:44 2007
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> > Commit point reached - logical record count 64
> > Commit point reached - logical record count 128
> > Commit point reached - logical record count 192
> > Commit point reached - logical record count 256
> > Commit point reached - logical record count 320
>
> > Setting rows=1000, but I only got 188 rows each commit
> > sqlldr userid=test/test_at_test control=load_temp_table_v3.ctl
> > bindsize=10000000 readsize=10000000 rows=1000
>
> > SQL*Loader: Release 9.2.0.8.0 - Production on Fri Apr 13 10:39:43 2007
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> > Commit point reached - logical record count 188
> > Commit point reached - logical record count 376
> > Commit point reached - logical record count 564
> > Commit point reached - logical record count 752
> > Commit point reached - logical record count 940
> > Commit point reached - logical record count 1128
> > Commit point reached - logical record count 1316
>
> > Directly set rows=1000, performance is even worse
> > sqlldr userid=test/test_at_test control=load_temp_table_v3.ctl rows=1000
>
> > SQL*Loader: Release 9.2.0.8.0 - Production on Fri Apr 13 10:46:52 2007
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> > Commit point reached - logical record count 4
> > Commit point reached - logical record count 8
> > Commit point reached - logical record count 12
> > Commit point reached - logical record count 16
> > Commit point reached - logical record count 20
> > Commit point reached - logical record count 24
>
> > How can I achieve rows=1000?
>
> > Another question, I use direct=yes, it is my understanding that this
> > will not through insert, but will this operation have any entries in
> > redolog? Will this have any impact on our DataGuard?
>
> > Thanks for your help.
>
> No control script and you want help with SQL*Loader?
>
> Personally I'd start with a table having 200 columns. That is likely
> indicative of a poor design.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Control script is very long

---

load data
infile 'load.txt'
badfile 'load.bad'
discardfile 'load.dsc'
replace into table temp_load
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
(

id	,
ssn	,
last_name	,
first_name	,
m_name	,
dob	,
zipcode	,
gender	,
ethnic	,
county_code	,
addr_state	,
country	,

......
)

---

Could you tell me, if direct load is so fast, why and when should we use conventional load? What is the cons of using direct load? Will that affect DataGuard because of no entries in redolog?

Thanks Received on Fri Apr 13 2007 - 10:44:52 CDT