Hi All,
We hae a Px job that inserts 6M rows. For this job we increased the Insert Array size to 100000. The job completed in less than 1/10th of the time it normally takes, but did not insert more than half of the rows. It did not give any error either.
My question is what is the purpose of this parameter? What are the +ve/-ve impacts of setting it to a high number?
Any ideas?
Px Insert Array Size for Oracle
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What happened to the other rows? Were there any warnings logged about their being rejected by Oracle? Did you set up a rejects link to capture them?
For so many rows, I'd counsel using bulk loader techniques.
Array size is a function of row size (which you can calculate). It's how many rows' worth of data to send in one batch. For example, for a row size of 100 bytes and a network packet size of 1000 bytes, the ideal setting for array size is a multiple of the quotient, 10 (1000/100). This property is about getting best use out of the transmission medium.
For so many rows, I'd counsel using bulk loader techniques.
Array size is a function of row size (which you can calculate). It's how many rows' worth of data to send in one batch. For example, for a row size of 100 bytes and a network packet size of 1000 bytes, the ideal setting for array size is a multiple of the quotient, 10 (1000/100). This property is about getting best use out of the transmission medium.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Request the DBA to check in the Oracle error log what happened to those records that were rejected. The transaction log segment should be sufficient enough to hold 100000 rows but Oracle will throw the error saying that 'Transaction log size is full'.
Hope this will give a clue what are the ares to check to find out the reason for rejection.
Regards
Saravanan
Hope this will give a clue what are the ares to check to find out the reason for rejection.
Regards
Saravanan
Datastage did not give any rejects or any warnings for this job. I would check with the DBAs to see if there are any warnings at their end.ray.wurlod wrote:What happened to the other rows? Were there any warnings logged about their being rejected by Oracle? Did you set up a rejects link to capture them?
For so many rows, I'd counsel using bulk loader techniques.
We use bulk load for the initial load - however for the incremental we would have to do upserts because there would be changes.