Transaction handling in Parallel jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Mon May 01, 2006 11:27 am
Good evening,
The commit level was set to 1.
The table had HASH partition enabled while in Parallel mode of execution.
The job basically does a select from a source table, a lookup with 5 other tables and finally does an insert into the target table abcd(this table was getting geadlocked due to concurrent inserts)
I truly get inspired by seeing the dedication all of you show for this site and in helping the fellow dsxchangers.
Thanks,
Mukthi
The commit level was set to 1.
The table had HASH partition enabled while in Parallel mode of execution.
The job basically does a select from a source table, a lookup with 5 other tables and finally does an insert into the target table abcd(this table was getting geadlocked due to concurrent inserts)
I truly get inspired by seeing the dedication all of you show for this site and in helping the fellow dsxchangers.
Thanks,
Mukthi
Is that job still running? If so, stop the job and make sure with your DBA that the table has not locked.
Hope you are using OCI stage to insert the data. If so, What is the write method and Upsert Mode?. Instead of Auto genearated, use the User-Defined update Only option and write the user defined query only to insert the record. Make sure you are not using any update query to the same table.
In general, tables won't be locked for insert. Anyways Please check the following also.
Recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.
Note: "ITL waits" can be monitored per segments by querying the sys.v_$segment_statistics view.
Hope you are using OCI stage to insert the data. If so, What is the write method and Upsert Mode?. Instead of Auto genearated, use the User-Defined update Only option and write the user defined query only to insert the record. Make sure you are not using any update query to the same table.
In general, tables won't be locked for insert. Anyways Please check the following also.
Recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.
Note: "ITL waits" can be monitored per segments by querying the sys.v_$segment_statistics view.
Even for Upsert, the query can be generated using SQL builder individually for Insert as well as Update. And I dont get the reason behind you suggestion to convert it to user defined SQL, if at all its going to be Update and Insert any ways. ![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
If its going to be only Insert alone, its not Upsert. By the where you got the idea that Mukthi is doing Upsert?
As far as I could see, it is referred to Insert.
Even in that case inclusion of User defined SQL is not recommended, unless required otherwise. I wish, I could quote Craig's point, but Iam too lazy to search the post.![Wink :wink:](./images/smilies/icon_wink.gif)
As far as I could see, it is referred to Insert.
Even in that case inclusion of User defined SQL is not recommended, unless required otherwise. I wish, I could quote Craig's point, but Iam too lazy to search the post.
![Wink :wink:](./images/smilies/icon_wink.gif)
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 21
- Joined: Tue Apr 24, 2007 12:39 am