Re: Help with my sql load performance

emdproduction_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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org