Home » SQL & PL/SQL » SQL & PL/SQL » Lowest Cache value
Lowest Cache value [message #260743] Mon, 20 August 2007 18:16 Go to next message
florida
Messages: 82
Registered: April 2006
Member
What is the lowest I can go on sequence cache for my Oracle 9i Sequence? I tried 1 and it wouldnt work so now I am using 2.
Please advise if it is okay to use cache 2?

CREATE SEQUENCE emp_sequence 
INCREMENT BY 1 
START WITH 1 
NOMAXVALUE 
NOCYCLE 
CACHE 2;
Re: Lowest Cache value [message #260750 is a reply to message #260743] Mon, 20 August 2007 19:09 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The minimum is 2.
Re: Lowest Cache value [message #260809 is a reply to message #260743] Tue, 21 August 2007 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please advise if it is okay to use cache 2?

Why using the minimum number? Why not using a big number or no cache?

Regards
Michel
Re: Lowest Cache value [message #261105 is a reply to message #260809] Tue, 21 August 2007 19:12 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Please advise the difference between using cache and no cache in my sequence example?
Re: Lowest Cache value [message #261112 is a reply to message #261105] Tue, 21 August 2007 19:37 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Docs explain it well...The Number of Entries in the Sequence Cache.
Re: Lowest Cache value [message #261114 is a reply to message #261112] Tue, 21 August 2007 20:23 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks, in the past when I used cache it does skip sequence numbers for my primary key. That is okay but I was wondering if it means "skip" in the sense it will never use the same number again? I have used cache = 5 for my sequence that work with a trigger for "autonumbering" my primary key numbers in a primary key field and never had any problems but was wondering if there is a chance I could get the same number for my primary key which would be very bad. So the way I see it is the Sequence will always add to my primary key number and it will never have a duplicate number??

Choosing a high value for CACHE lets you access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.

If you use the NOCACHE option in the CREATE SEQUENCE statement, then the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence. This CREATE SEQUENCE statement creates the SEQ3 sequence so that its values are never stored in the cache:
Re: Lowest Cache value [message #261116 is a reply to message #261114] Tue, 21 August 2007 20:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can specify NOMAXVALUE for the sequence which would provide 1027 values for a sequence. If you actually use up that many values, even with values that you may loose from caching, then your business is booming. In addition, you can specify NOCYCLE for the sequence to ensure a sequence isn't reused.

[Updated on: Tue, 21 August 2007 20:56]

Report message to a moderator

Re: Lowest Cache value [message #261154 is a reply to message #261114] Wed, 22 August 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A sequence can never generate duplicates (unless you give the option "cycle" and exhaust all the numbers).

Regards
Michel
Re: Lowest Cache value [message #261257 is a reply to message #260743] Wed, 22 August 2007 04:10 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Please use nocache option.
Use nocycle for avoiding duplicate values in a sequence.
Re: Lowest Cache value [message #261259 is a reply to message #261257] Wed, 22 August 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please use nocache option

I beg to differ.
Never use NOCACHE unless you have a very good reason.
Use a high cache value to prevent from SQ lock waiting.

Regards
Michel
Re: Lowest Cache value [message #261264 is a reply to message #260743] Wed, 22 August 2007 04:21 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Michel

Nocache has effect on perfromance.If the table which is using sequence is having very less number of inserts then there is no use of having a cache value.This will unnecessarly put the gap in the values as cache will be out of memory if some other queries brings lots of data in memory.

Use cache
1. When insertions are very frequent
2. A number of users inserting to the same table simultaneqouly.
2. Have sufficient memory

Use Nocache

1. When insertions are very less.
2. The gap between the values makes some difference to the business logic.
Re: Lowest Cache value [message #261287 is a reply to message #261264] Wed, 22 August 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Use Nocache
1. When insertions are very less.
2. The gap between the values makes some difference to the business logic.

There are always gap with sequences so cache/nocache is irrelevant here.
Memory used by a sequence number is tiny regarding to the rest. So with a cache of 100 you will use less than 1KB. Who care about 1KB of memory.

Always use cache.

Regards
Michel
Re: Lowest Cache value [message #261297 is a reply to message #260743] Wed, 22 August 2007 05:09 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Gap will come in cache values when the sytem crashes somehow.
There are alwys gap it is irrelevent to cache/nocache.

This is wrong with nocache there wont be any gaps if system (memory)crashes.



Re: Lowest Cache value [message #261304 is a reply to message #261297] Wed, 22 August 2007 05:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
darshanmeel wrote on Wed, 22 August 2007 12:09
This is wrong with nocache there wont be any gaps if system (memory)crashes.
Rollback anyone?

MHE
Re: Lowest Cache value [message #261314 is a reply to message #261304] Wed, 22 August 2007 05:29 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Maaher wrote on Wed, 22 August 2007 06:21
darshanmeel wrote on Wed, 22 August 2007 12:09
This is wrong with nocache there wont be any gaps if system (memory)crashes.
Rollback anyone?

MHE

Agree with Maaher and in addition DELETE.

As I mentioned, you have 1027 sequences numbers to use per sequence. Don't really think you need to worry about gaps unless there is a business need NOT to have gaps.
Re: Lowest Cache value [message #261332 is a reply to message #261297] Wed, 22 August 2007 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There are alwys gap it is irrelevent to cache/nocache.

This is wrong with nocache there wont be any gaps if system (memory)crashes.

SQL> create table t (id integer);

Table created.

SQL> create sequence s nocache;

Sequence created.

SQL> insert into t values (s.nextval);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (s.nextval);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into t values (s.nextval);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;
        ID
----------
         1
         3

2 rows selected.

Full stop.

Regards
Michel

[Updated on: Wed, 22 August 2007 06:05]

Report message to a moderator

Re: Lowest Cache value [message #261335 is a reply to message #261332] Wed, 22 August 2007 06:08 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
I told nocahe wont produce gaps in case system crashes.I said use nocache when insertions are very infrequent.

Please read it again and then asnwer.
Re: Lowest Cache value [message #261340 is a reply to message #261335] Wed, 22 August 2007 06:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In case of a crash with uncommited transactions you will have a gap. The sequence value is spent, but it isn't used and it won't be used again. At least, that's what I understood.

MHE
Re: Lowest Cache value [message #261342 is a reply to message #261335] Wed, 22 August 2007 06:18 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten already answered.
Sequence = gaps, no way to workaround this.
I repeat, you will have gaps.

So NOCACHE is useless (but in exceptional cases).

Regards
Michel
Previous Topic: error when executing proc
Next Topic: Bulk inserts
Goto Forum:
  


Current Time: Wed Dec 07 16:14:45 CST 2016

Total time taken to generate the page: 0.11679 seconds