I have met a strange problem while insert data with sequence, could anybody helps to explain what caused it?
create table with 2 field (id number, name varchar),
create sequence (increment by 1 ,no cache/cache 10000) (create sequence seqt1 increment by 1 start with 1 nomaxvalue nocache)
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?