emdproduction_at_hotmail.com wrote:
> 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
Again ... a table with 200 columns is a strong indication of a bad
design.
Direct load has restrictions. If you don't know what they are it is
time to read the docs. http://tahiti.oracle.com.
--
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
Received on Fri Apr 13 2007 - 10:56:29 CDT
