Transaction handling in Parallel jobs

Amuktamalyada · Post by **Amuktamalyada** » Sun Mar 11, 2007 6:22 pm

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

oacvb · Post by **oacvb** » Sun Mar 11, 2007 6:56 pm

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.

kumar_s · Post by **kumar_s** » Sun Mar 11, 2007 11:41 pm

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.

oacvb · Post by **oacvb** » Mon Mar 12, 2007 12:30 am

If he is doing Insert alone, no need to have the update query.So i asked him to write user defined sql for insertion alone and not to have update statements.

kumar_s · Post by **kumar_s** » Mon Mar 12, 2007 1:09 am

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.

oacvb · Post by **oacvb** » Mon Mar 12, 2007 2:46 am

Version 7 is different from 7.5, 7.5 has three properties like Upsert, Load. If we specify the method as upsert and Auto-Generated SQL, It will produce query for Insert as well as Upsert. Alternative solution is make Custom SQL and write Insert queries alone.

kumar_s · Post by **kumar_s** » Mon Mar 12, 2007 4:33 am

Where am I referring as Version 7? If 'Load' option can do the job, why to write a query of a own which might sometime end up in manual errors.

oacvb · Post by **oacvb** » Mon Mar 12, 2007 4:38 am

I didn't say you are referring Version 7. I just told the difference. Load will work as Oracle Bulk Loader. Hope it clears.

Shadab_Farooque · Post by **Shadab_Farooque** » Fri Sep 21, 2007 1:06 am

I also faced the same deadlock problem.
Making it sequential worked for me too.