4

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.

faghani
  • 569
  • 1
  • 10
  • 25
  • 1
    Hibernate's default sequence generator gets the next sequence value from the database every 50 inserts, and keeps a counter in memory. It's thus (by default) 50 times faster than what you see there. – JB Nizet Jan 06 '19 at 08:24
  • 1
    Do you mean the third line from top on this printscreen ? Average time = 396 μs. μs = microsecond. 396μs = 0,4 ms (miliseconds). This is 2500 operations per second. Do you really think it is slow ? I think it is a waste of time for such micro-optimization, when you reduce the time by 50%, you will gain at most 0.2 ms per one operation. – krokodilko Jan 06 '19 at 08:47
  • 2
    If you typically need 200K sequence values for this process then you might consider increasing the cache size from 20 to several hundred. Also I don't know Hibernate (or Java) but it would be nice if you could have the sequence generated as part of the `insert` instead of fetching it in a separate operation (e.g. call a procedure which performs all the database processing and returns the generated ID). From Oracle 12c you can start using identity columns and forget all this sequence micro-management. – William Robertson Jan 06 '19 at 10:49
  • @WilliamRobertson Glad to hear that Oracle 12c supports auto id column. I saw this in SQLite! – faghani Jan 06 '19 at 11:09
  • @krokodilko yes, compared to the update queries with average time of 8 and 9 μs I think 396 μs is very slow for a query that is sounds trivial, at least at prima facie! Again, compared to a big inner join with almost equal time I pretty believe it is too slow. I'm not looking for a way to reduce the time by 50% or 500%, I'm suspected that me and probably many other oracle users are using sequences somehow like how beginner java developers use `String += ` instead of using `StringBuilder` or `StringBuffer`! If so, you know what happens. – faghani Jan 06 '19 at 11:50
  • @faghani then please show a code snippet when this sequence is used in your application - if it comes from Hibernate, then there are probably `@GeneratedValue` + `@SequenceGenerator` annotations in a mapping of one of entities which use this sequence. If yes, then it should be easy to speed up this code 50 times. – krokodilko Jan 06 '19 at 15:05
  • 2
    Regardless of other means of making it more efficient, it would be interesting to see the effect - in isolation - of simply changing the `CACHE` value from 20 to, say, 1000. If you can try that easily and report back, I believe many of us will found that to be useful information. –  Jan 06 '19 at 15:26
  • 2
    You are speaking about 1+ minute elapsed time to get nearly 200K keys from a sequence. You choose the *Hibernate* which is probably the problem. You must manage bulk insert the rows without passing the key between the DB and the client. Currently you ask the DB to assing the new key, send it to client, insert row and loop again - **row by row - slow by slow**! – Marmite Bomber Jan 06 '19 at 22:07

3 Answers3

0

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

Kristian Saksen
  • 94
  • 5
0

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.

dsaswal
  • 1
  • 1
0

I faced the same issue 10 years ago but within the oracle Procedure. It was a cursor for loop first like you are doing now, then I replaced it with Direct Insert still no use and then I found the issue was with the cache size of default 20. I altered the Cache to 100,200, 300 and then finally settled with 500..

Venkat
  • 107
  • 12