Sequence performance with concurrent multi instance inserts

User_4S733

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

Luís Gustavo Lira

Hi,

the ideal is to identify the waits that are occurring, you can run an ASH report of the session or even do a trace of the session and from there check the events to identify where is the bottleneck and what can be improved.