lock during insert

[amir_magdy]

we have a process, which is launched in 32 sessions in parallel, doing parralel insert in couple of tables, during processing I found that there is locks between these processes, specially while inserting in one of the tables.
I had to recreate the tables being used in the inserts.
Storage parametres of the table currently are :
initrans=32
freelists=32
pctfree=20
pctused=40

and for indexes
initrans=64
freelists=64
pctfree=20

this table has a primary key but it's generated by a sequence, and it's not referenced by any other table.

I even tried to launch the process on 16 session only instead of 32, but we still get the same locks.
I can't find any reason for this lock, It's not row level lock, and it shouldn't be block level lock since the parameters seems to be correct.

[slimdave]

If you have an index on your sequenced column then it's likely to be the source of your locking problem.

If this is so, then rebuild the index using the REVERSE keyword to modify it to avoid this problem.

[abhaysk]

Originally posted by slimdave
If this is so, then rebuild the index using the REVERSE keyword to modify it to avoid this problem.

Will that really help?

[amir_magdy]

Can you please explain why a reverse index may solve the problem?

anyway, I tried it and didn't work

[DaPi]

The idea is that although you are inserting in the table, you will be updating the index. If the index is on a sequence, then there is a good chance that all the updates will hit the same "leaf" of the index (e.g. 123456, 123457, 123458 are stored together) causing contention. The reverse values 854321, 754321, 654321 are more likely to go into different blocks.

[abhaysk]

Its more or like load balancing.. but the Q is will it help in reducing Locking Issues? .. -- NO i guess

[DaPi]

If 32 processes are trying to update the same "leaf" there will be contention over the locks.

[robertbalmer]

which is launched in 32 sessions in parallel, doing parralel insert in couple of tables, during processing

Have you checked the values of the parallel init. parameters.
This could be your problem?

[abhaysk]

Dapi :

Well well.. Original Poster said .. "I found that there is locks between these processes" -- Which is not clear as to what he means here.. ( i assumed it to be lockwaits, if so then its basically some design probs )

In this regard i mentioned "Will that really help (--Reverse Keys )"

Abhay.

[amir_magdy]

it's not because of the index.
I dropped the index, but the problem still presist,

the strange part is that at the begning processes insert records without any problem but after 30,000 record, we start detecting locks, and performance goes down.