Home » RDBMS Server » Server Utilities » sql loader - ROWS value being changed during execution
sql loader - ROWS value being changed during execution [message #249085] Tue, 03 July 2007 05:55 Go to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
Hi All,

I am using LOAD option in oracle stage of datastage(ETL Tool). This uses sql loader to load data into oracle.

I am using OPTIONS(DIRECT=FALSE, PARALLEL=TRUE, ROWS=20000)

For performance improvement i want a large commit interval. So I am using ROWS option.

But the rows are being committed at a value of 556(shown in the log created by sqlldr).

The log is showing :

Bind array:     20000 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
.....<table definition>
value used for ROWS parameter changed from 20000 to 556
....
Space allocated for bind array:  255760 bytes(556 rows)
Read   buffer bytes: 1048576


Can anyone please explain why the value of ROWS is getting changed?

Regards,
ssunda.
Re: sql loader - ROWS value being changed during execution [message #249096 is a reply to message #249085] Tue, 03 July 2007 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase bindsize parameter which is default to 256000 and can only contains 556 of your rows.

255760 / 556 * 20000 = 9200000

Regards
Michel

Re: sql loader - ROWS value being changed during execution [message #249300 is a reply to message #249096] Wed, 04 July 2007 00:37 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member

Thank you Michel.

Regards,
ssunda.
Re: sql loader - ROWS value being changed during execution [message #249352 is a reply to message #249300] Wed, 04 July 2007 03:04 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
Hi,

I tried increasing the BINDSIZE value.
First increased it to 35000000. This gave an error saying

 specified value for readsize(1048576) less than bindsize(35000000)
SQL*Loader-500: Unable to open file (ora.11878.43776.fifo.0)
SQL*Loader-569: READSIZE parameter exceeds maximum value 20971520 for platform
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.


As the above log clearly says that max value for READSIZE can be 20971520, I changed the value of BINDSIZE to 20000000.
(If the READSIZE value specified is smaller than the BINDSIZE value, the READSIZE value will be increased.)

But still it gave a warning
specified value for readsize(1048576) less than bindsize(20000000).
and committed at 64 rows(default) interval

I even tried by giving READSIZE=20000000 in the OPTIONS.
It is still committing at the value of 64 rows.

Please help.

Regards,
ssunda

[Updated on: Wed, 04 July 2007 03:05]

Report message to a moderator

Re: sql loader - ROWS value being changed during execution [message #249359 is a reply to message #249352] Wed, 04 July 2007 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the full options line.
Post all the important lines of your log (not the column description...).

Regards
Michel
Re: sql loader - ROWS value being changed during execution [message #249369 is a reply to message #249359] Wed, 04 July 2007 03:35 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
Hi,

When I gave BINDSIZE=35000000,
OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,BINDSIZE=35000000)

Errors allowed: 50
Bind array:     64 rows, maximum of 35000000 bytes
Continuation:    none specified
Path used:      Conventional
...
Space allocated for bind array:                  44672 bytes(64 rows)
Read   buffer bytes:35000000
..
SQL*Loader-500: Unable to open file (ora.11878.43776.fifo.0)
SQL*Loader-569: READSIZE parameter exceeds maximum value 20971520 for platform
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.


Log messages when I gave READSIZE
OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,READSIZE=20000000)

Errors allowed: 50
Bind array:     64 rows, maximum of 20000000 bytes
Continuation:    none specified
Path used:      Conventional
...
Space allocated for bind array:  44672 bytes(64 rows)
Read   buffer bytes:20000000


Please let me know if you need more info.

Regards,
ssunda

Re: sql loader - ROWS value being changed during execution [message #249388 is a reply to message #249369] Wed, 04 July 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you remove ROWS?
Use all the options.

Regards
Michel
Re: sql loader - ROWS value being changed during execution [message #249389 is a reply to message #249369] Wed, 04 July 2007 04:29 Go to previous messageGo to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member

Hi Michel,

I tried giving both ROWS and BINDSIZE parameters.
Now the commit interval got increased. But I am not able to analyze like how it is setting the value of commit interval.

OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,ROWS=30000,BINDSIZE=20970000)
..
Bind array:     30000 rows, maximum of 20970000 bytes
Continuation:    none specified
Path used:      Conventional
..
Space allocated for bind array:               20940000 bytes(30000 rows)
Read   buffer bytes:20970000


Now, the rows are being committed at 17,026 rows interval.

Regards,
ssunda.
Re: sql loader - ROWS value being changed during execution [message #249564 is a reply to message #249389] Thu, 05 July 2007 01:07 Go to previous message
ssunda6
Messages: 28
Registered: June 2007
Junior Member

Hi All,

Understood how the sqlldr is calculating the commit interval value.

When we are using both ROWS and BINDSIZE options, first sqlldr calculates the space(in bytes) a row can take (based on the column datatypes and the number of columns of the table).

And then multiplies that size by the number of ROWS. If that size is less than the BINDSIZE(bind array size) mentioned, it is taking the value of ROWS as commit interval.

If the size of the number of ROWS is large that it does not fit in the BINDSIZE value specified, then it uses small number of rows that can fit into the BINDSIZE and then commits at that interval.

BINDSIZE value should match with the READSIZE value. If we give very large value for BINDSIZE that exceeds the maximum limit set for READSIZE , it throws an error. If the BINDSIZE value does not exceed max value of Read buffer size(set on the platform), then the Read buffer size is automatically increased to the size of Bind array and it reads that many number of bytes from datafile.

Thank you Michel for the help.

Regards,
ssunda.
Previous Topic: Drop all users in the database
Next Topic: sqlloader number formatting
Goto Forum:
  


Current Time: Wed Dec 07 18:29:10 CST 2016

Total time taken to generate the page: 0.11019 seconds