Next sequence number jumps sometimes 3

[evergreean43]

I have a sequence and trigger that automatically increases my primary key on my table after each Oracle 9i record insert.

It works but was wondering why it sometimes skips more than a thousand numbers on some inserts.

For example:

id    name
1     Jones
2     Barnes
1023  Johnson
2005  Baker
3006  Miller

If I insert records right after another (such as id 1 and id 2) it increments to the next number. But sometimes a record might be inserted a week later after example id 2 (such as id 1023) and that is when it jumps sometimes a 1000 in the next number.

Please advise.

 

[djbjr]

Post the script for your trigger and perhaps we can help

 

[SantaMufasa]

Also, evergreean, please post the results of this query:

select sequence_name
      ,increment_by 
      ,cache_size
  from user_sequences
where sequence_name = upper('<your sequence name>');

I believe that you will see that your CACHE is probably set to 1000 or something similar.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[evergreean43]

Yes the sequence cache is set to 1000 when I create my sequence. I have created about 4 other sequence and triggers and all of them are set to 1000 cache also.

Is this a problem or should I change the cache to 1??

Should I change all my past sequence scripts so they are cache to 1?

 

[SantaMufasa]

Here are some thoughts about sequences and caching from a recent thread in a similar vein in the Oracle 10g forum:

The overall benefit of sequence caching is speed: the higher the caching value, the fewer times that Oracle must go to the database and obtain a cache of sequences. The negative effect of caching is that once a cache of sequences exists, if there is a re-boot of the database, Oracle abandons any values that were in the cache.

Therefore, if you do not want to leave gaps in your sequence values, then you must pay the price by setting your sequence to NOCACHE.

My question is this: What business risk do you suffer by leaving gaps in your sequence values? For practical purposes, even if you assigned sequence values by "10,000", there are not too many business applications that would ever run out of the octillion (10[sup]27[/sup]) values that an Oracle sequence can provide.

If you do use too many numbers, let me know and I will be happy to pay your invoice from the Federal Bureau of Numbers.

...And Bill's excellent observations:

I had a case where I had an existing packaged application that used a maximum of 999999 document numbers. Since the particular database was being shut down every night for a cold backup, the cache was killing me, so I turned it off. (just an example). If you have a system that needs a sequence number every few minutes, your cache setting is not going to matter. If you need a hundred sequence numbers a second, it is going to make a major difference in performance.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[evergreean43]

Great info. Thanks.

I think we will be adding about 1000 records a year on this table so I assume I will be okay with 1000 cache.

My other table has about 20,000 records and will be adding about 100 a month.

Otherwise, it seems using Nocache cant hurt my database in anyway but would be an issue with speed?

 

[SantaMufasa]

Evergreean,

If you are adding such a few number of records (1000/yr and 100/mo), then you certainly will have no performance problem with no caching of those sequences. You can modify them to no longer cache with this syntax:

alter sequence yada nocache;

Sequence altered.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Now, the downside of no caching is that if you have an application requesting the next sequence and failing to do an immediate commit -- the sequence will be locked and other transactions wait on that lock (and you application stops).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Actually, LKBrwnDBA, the beauty of Oracle sequences is that under no circumstances (CACHE or NOCACHE) is a sequence "locked", per se, from providing a value. The fact that a sequence is in NOCACHE mode and a requesting transaction has not COMMITted (or ROLLedBACK) does not prevent any number of successful sequence requests/allocations. Additionally, NOCACHE does not ensure consumption of every sequence value...it just means that the database caches one value at a time versus multiple values at time.

Here is an illustration of what I am asserting:

(Session 1)
alter sequence yada nocache;

Sequence altered.

insert into x values (yada.nextval);

select * from x;

  ID
----
   1
   2
   3
   4
  21

5 rows selected.

(Notice that no COMMIT has occurred.)

(Session 2)

insert into x values (yada.nextval);

1 row created.

select * from x;

  ID
----
   1
   2
   3
   4
  22

(Session 1)

rollback;

Rollback complete.

select * from x;

  ID
----
   1
   2
   3
   4

4 rows selected.

rollback;

Rollback complete.

(Session 1)

select yada.nextval from dual;

NEXTVAL
-------
     23

So, notice that NOCACHE doesn't ensure NOGAPS...NOCACHE just "deals one card at a time", but once it deals a "card", the card is "burnt".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[evergreean43]

Thanks for all the info!

Just another question.

Can I lower my cache value to 100 or maybe to the value 1?

 

[SantaMufasa]

Sure, you can set your sequence to virtually any starting value you wish (even negative numbers) and increment (or decrement) by any value. Here is an example of how I would reset my sequence, YADA, to "-11", then decrement by "3" each time, and not cache:

SQL> drop sequence yada;

Sequence dropped.

create sequence yada start with -11 increment by -3 nocache;

Sequence created.

SQL> select yada.nextval from s_region;

   NEXTVAL
----------
       -11
       -14
       -17
       -20
       -23
       -26
       -29

7 rows selected.

Let us know if this answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

BTW, I forgot to confirm for you how to (re)set a sequence's values, including caching:

SQL> drop sequence yada;

Sequence dropped.

SQL> create sequence yada start with 102 increment by 2 cache 10;

Sequence created.

SQL> select yada.nextval from s_region;

   NEXTVAL
----------
       102
       104
       106
       108
       110
       112
       114

7 rows selected.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

...the beauty of Oracle sequences is that under no circumstances (CACHE or NOCACHE) is a sequence "locked",...

That is new for me, we have a "Forms" application that uses a NOCACHE - ORDER sequence and everytime a user enter data for a new row, all other "new" inserts will stop until the culprit transaction ends. And almost every day we have to kill some session that is holding a lock on the sequence because the user took a break without finishing the transaction.
But, maybe i'm wrong?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Actually, LK, your application could be doing what I have seen before under similar circumstances: Rather than taking advantage of Oracle sequences (which behave as I mentioned), your developers chose to use table-based sequences where your developers elected to create their "own" sequences (to ensure no gaps in numbering) and they allocate sequential ids from a table, which, of course, they lock until the user either COMMITs or ROLLsBACK their transaction, thus freeing the sequence for the next user.

I'll bet that's what is happening. I'd be very interested in hearing if that is the case. I am certainly not aware of any Oracle mechanism for locking a true Oracle sequence.

Could you let us know?

Cheers.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

No, it's a combination of "Forms" and a NOCACHE - ORDER sequence. A lock occurs where the next sequence is not allocated until the previous tranasction commits.


PS: It's 8i

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
"Forms" are just containers/interfaces to code and procedures..
What is the code behind that runs when the new record is set to be inserted...Is it using the sequencename.NextVal to generate the sequence # or a Select statement from some other table as Santa sus[ects..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

I know for a fact that oracle sequences use their own internal locking and they will never hold up other users. See the following link.

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#CNCPT611

I agree with Dave, your code must be using a table sequence if it is locking other users on a simple sequence.

Bill
Oracle DBA/Developer
New York State, USA

 

[LKBrwnDBA]

@ to all, RE: sequences.

I checked and the application is using a trigger to assign the next sequence number the clasical way:

SELECT TheSeq.NEXTVAL INTO NewSEQ FROM DUAL;

The sequence is defined as ORDER which means NO gaps.

From the SQL reference:

ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. You may want to use this clause if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

And a cautionary note from the link provided by Beilswth:

Caution:
If accountability for all sequence numbers is required, that is, if your application can never lose sequence numbers, then you cannot use Oracle sequences and you may choose to store sequence numbers in database tables.

Therefore to "guarantee" no gaps in the sequence, it seems I cannot use Oracle sequences; otherwise the sequence is being 'locked' throughout the duration of the transaction (in case of rollback).

Conclusion: Maybe using 'table' sequences will alleviate the issue.

Thanks to all.
MikeOM>





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
Just a note: ORDER does not mean no gaps..It means that the numbers are in the same sequence as requested..that way they can be used as timestamps by comparing RELATIVE numbers (a Later transaction will always have a Higher number than an earlier one and using 'where sequencenumberfield = MAX(sequencenumberfield)' will return the last record added -

As the doc indicated, the 'normal' sequence is generated in Order unless you are using RACs, so the ORDER clause is not needed.









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

But ORDER combined with NO CACHE does guarantee no gaps in sequence numbers.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb