- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 73 Insurance
- 537.3K On-Premises Infrastructure
- 138.6K Analytics Software
- 38.6K Application Development Software
- 6K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Sequence performance with concurrent multi instance inserts
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?