Forum Stats

  • 3,825,929 Users
  • 2,260,581 Discussions
  • 7,896,738 Comments

Discussions

Sequence performance with concurrent multi instance inserts

Hi experts,

I have met a strange problem while insert data with sequence, could anybody helps to explain what caused it?

prepare:

  1. create table with 2 field (id number, name varchar),
  2. create sequence (increment by 1 ,no cache/cache 10000) (create sequence seqt1 increment by 1 start with 1 nomaxvalue nocache)
  3. a program that keeps doing insert operation for 30s to update the sequence (INSERT INTO OCCITEST VALUES(seqt1.nextval,’T1’)):

Then I write an batch scripts to run multi process of program to update the sequence (i.e. for /l %%i in (1,1,50) do (start cmd /c runtest.bat)), and query how much date have been insert into the table to check the performance of sequence while concurrent multi instance inserting data.

Here is a strange situation, the performance will decrease after the batch scripts executed , for example ,the first test there are 162364 data been inserted into table in 30s, the second test only 100593,  and the third time only 54877, if I drop the sequence and recreate it again, it will back to normal.

I have tested it with the sequence using cache 10000/ nocache , but it seems have the same situation.

So could any experts helps to answer it?


Thanks,

kurt

Answers