-1

In our app everytime we're creating a new entity we're calling Oracle to get the next sequence for the entity's id. We're using OracleSequenceMaxValueIncrementer for this.

But we want to decrease the number of calls to oracle.

I was thinking to increase the "Incremented By" value on the sequence table. Right now it is 1. So if we'll increase to 10, we can make the call to oracle every 10 times, and not everytime we need a new sequence. I was hoping to get in the code the "Incremented by" value using OracleSequenceMaxValueIncrementer, but it not support it, so need to perform a regular query.

Is this a common solution for this, I guess common problem?

Asdditional information: The service that we're using to get the next sequence is used by all the entities, where for everyone there is a different sequences table that can have different "increment by". This is why I wrote I'll first query the DB to retrieve the "incremented by" of the specific entity.

YevgenyL
  • 219
  • 2
  • 16
  • 1
    No, having two sequences (the second being in memory of your app which isn't known to database) is by no means a common solution to this problem. – M. Prokhorov Mar 01 '18 at 12:43
  • Is there a better suggestion? – YevgenyL Mar 01 '18 at 12:45
  • 4
    Yes, there is. [Increment sequences during write operations](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle). – M. Prokhorov Mar 01 '18 at 12:46
  • Not sure it can help me, as in our case the entity's id only contains the sequence and not the sequence itself. Meaning, we're going to DB to get the next sequence, then using it to construct entity's id, setting it on the entity and then save. So we must get the id before saving the entity. – YevgenyL Mar 01 '18 at 12:53
  • 1
    @M.Prokhorov actually for JPA it is common to have a sequence increment by `100` instead of `1` and have JPA handle it for performance improvements. Of course that's not exactly the same as doing it yourself, since it's JPA's business to come up with the id for the entities anyway. – Kayaman Mar 01 '18 at 13:17
  • 1
    Actually I have seen that pattern (increment in application and use a sequence to allocate x slots) in many places. We had it in a COBOL banking system in 1994. It works quite well as long as all applications agree on the slot size. – ewramner Mar 01 '18 at 13:30
  • We're using JPA as well. But not when handling the sequences. As I wrote we're using OracleSequenceMaxValueIncrementer for it. – YevgenyL Mar 01 '18 at 13:31
  • @user2612030, I don't agree that it's "working well". If your app has multiple processing servers and one database, making it work requires far more coordination between processes to make that work than it's worth for the task easily solved within database itself *without* anything in addition to that. – M. Prokhorov Mar 01 '18 at 13:40
  • Why aren't you using a [sequence generator](https://stackoverflow.com/questions/2595124/java-jpa-generators-sequencegenerator)? That's how it's normally done. – Kayaman Mar 01 '18 at 13:42
  • @Kayaman, sure, JPA might do it like this. But why go through JPA at all if database can handle it itself? – M. Prokhorov Mar 01 '18 at 13:45
  • @M.Prokhorov he's using JPA and the database **can't** handle it itself. JPA generates those ids because it has to be in control (caching etc.). If you're suggesting he should *stop* using JPA, that's a different issue. – Kayaman Mar 01 '18 at 14:01
  • 1
    @M.Prokhorov I maintain that it is working well as long as the slot size is well defined. Each application reserves a range by incrementing a database sequence. Basically adding 1 to the sequence means you have 100 values to use, then you have to get a new sequence value. As long as all applications agree on 100 (for example) there is no extra coordination. Adding one to the sequence is the only coordination required and performance is increased by the slot size (all other things being equal). If this is a bottleneck (and it can be) it is worth it. – ewramner Mar 01 '18 at 14:18

1 Answers1

1
  • Why you want to decrease the number of calls to your application ? Facing any contention ?

You can cache your sequence, which will reserve the number of sequence number per session in memory, it's used to avoid contention.

ALTER SEQUENCE customers_seq 
   CACHE 5;

CACHE Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

Reference: https://docs.oracle.com/database/121/SQLRF/statements_6017.htm#SQLRF01314

Fábio Galera
  • 135
  • 10
  • But that's in the DB cache. You don't want to go over network for that. – Kayaman Mar 01 '18 at 13:23
  • Thanks. We're increasing the Sequences's Cache as well, but we still want also to decrease the number of calls to Oracle. It's causing us to significant overhead. – YevgenyL Mar 01 '18 at 13:28
  • So if you don't want to go to Oracle to have sequence value, why are you implementing sequence in the database side ? Why not implement in application ? Your number really to be in sequence ? Can't be an UID ? – Fábio Galera Mar 01 '18 at 13:37