Oracle select sequence.nextval from dual sounds too slow

user_1210878 0
Viewed 4381

A while ago I had a database performance problem for inserting/updating several million records using jdbc. To increase performance I changed the code to use batch. Then I decided to monitor the code using jprofiler to know how much the performance is increased ... but meanwhile the monitoring I found an odd thing!

enter image description here

As you can see from the above screenshot, generating new id from a sequence is very slow. The screenshot is so descriptive just I must say the second row is an inner join query on a table with ~8 milion records with itself and some computations (compare its time with the time of third query!).

I asked the problem from our dba and he said something about oracle recommendation for caching sequences but when I checked the sequence I saw it's already cached.

CREATE SEQUENCE  "XXXXXXXXXXXX_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

Any thought?

p.s. I think Hibenate uses sequences for inserting records similarly, and actually I'm looking for best practices to use sequences to improve the performance of our project that uses hibernate. The above jdbc task is terminated.

sql-tuning database-performance oracle11g oracle hibernate
2 Answers

As suggested in comments by others - it is not the generation of numbers that takes time. Consider the example below - which eliminates network and network latency from the time consumption. 

SQL>  create sequence tst_seq start with 1 increment by 1; 

Sequence created.

SQL> set timing on
SQL> declare
  seqNo number(38,0); 
begin
  loop 
    select tst_seq.nextval into seqNo from dual; 
    exit when seqNo>=100000; 
  end loop; 
end;   2    3    4    5    6    7    8  
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.86

without caching it takes 5.86 seconds to generate 100.000 numbers. if you reproduce the test above you will get a brief estimate of what you can achieve if you change your implementation to eliminate the extra round trip for sequence numbers

user_104500530

As suggested by your DBA and others too, increasing cache size makes a difference. There is no harm in increasing the cache size if you don't mind gaps in sequences.

user_9802270
Related