| sql loader - ROWS value being changed during execution [message #249085] |
Tue, 03 July 2007 05:55  |
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 #249352 is a reply to message #249300] |
Wed, 04 July 2007 03:04  |
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).
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 #249369 is a reply to message #249359] |
Wed, 04 July 2007 03:35 |
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 #249389 is a reply to message #249369] |
Wed, 04 July 2007 04:29 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
