yes it happen
ashraf gamak, April 23, 2001 - 4:03 am UTC
yes tom it happen when the sequance is cache when the database shutdown the gap is begin because som number in the cache so he must nit use cashe sequance
How to determine ideal cache size?
Paul Dong, July 20, 2001 - 12:14 pm UTC
Tom, noting your recommendation to "set the cache high", what would you consider to be an ideal cache size?
We use sequences to generate surrogate keys to facts and dimensions stored in the Data Warehouse. It would be normal for the process to needs thousands of sequence numbers at a time, (and more, when initial load is occuring). Rather than avoiding gaps, my goal is maximizing throughput.
Thanks!
and to add to Tom's last comment...
Connor, July 20, 2001 - 1:32 pm UTC
Its often thought that a large sequence cache somehow uses more memory but this is not the case - a large cache is (very) good
Really Good Point (last one)
sreenu, July 22, 2001 - 10:26 pm UTC
Thanks Tom for expanding that point. Great logic.
How can I avoid gap while a sequence ages out?
hahaer, July 22, 2001 - 11:08 pm UTC
I created a sequence at cache size 500. From 9pm to 10pm this sequence will be used frequently, out of that period, it's used little. So every day ,a gap about 420 to 450 will be created because it ages out of library cache. How can I avoid this gad for this sequence? Thanks in advance.
Response to comment 5 (sequence aging out)
Connor, July 23, 2001 - 3:36 am UTC
You can keep a sequence in the shared pool using DBMS_SHARED_POOL.KEEP. Keep the sequence using a startup trigger. When you need to shut the database, on a shutdown trigger, make the sequence nocache then back to cache. This "unloads" it from the shared pool and minimises (not eliminates) the sequence number gaps.
Thanks Connor
Gururaj Kulkarni, July 23, 2001 - 11:41 am UTC
For getting more from Tom :) and sharing good info with us.
Speciall y cacheing lot of numbers
Pichaimani Balaubramanian, July 23, 2001 - 8:36 pm UTC
Was a real eye opener
Metalink implies non-gap sequence
A reader, September 02, 2003 - 10:01 am UTC
Well, they're wrong of course, but Note:62002.1 says:
"...if an application requires a sequence which never skips any numbers, a non-cached sequence must be used. "
which kind of implies that you can actually achieve a non-gap sequence, doesn't it?
September 02, 2003 - 10:48 am UTC
it should say
"If an application requires a sequence which never skips any numbers, a sequence CANNOT BE USED"
non-cached will have gaps - i can 100% assure you of that.
sort of reserving numbers....
Mike, February 20, 2004 - 9:13 am UTC
Would it be possible to reserve numbers in a sequence???
My problem is that we are developing an "off line" version of our app, in this off line version users will be able to insert records into a table that will be syncronised up, the PK of this table is taken from a sequence...
I was thinking of doing some cheeky manipulation of the "increment by" of the sequence along the lines of...
when about to go off line...
1) alter sequence my_seq increment by 1000;
2) select my_seq.nextval into my_min from dual;
3) select my_seq.nextval into a_dummy from dual;
4) alter sequence my_seq increment by 1;
5) create sequence my_local_seq start with my_min increment by 1 maxvalue my_min + 999;
If the my_local_seq runs out of values then an ORA-08004 is what we want.
This seems a little messy though, any better ideas? Oh this is on 9i R2 and we'll be using Oracle Replication for the sync'ing if that helps.
Cheers,
Mike
February 20, 2004 - 10:07 am UTC
that would not work in a multi-user environment.
imagine what happens when some other session(s) touches the sequence at time 1.1, 2.1, 3.1....
A preferred method would be
a) decide how many offline systems you will have (say 1000)
b) multiply that by 10 (so 10,000) just to be "safe"
c) on the production site, "create sequence my_seq start with 1 increment by 10000"
d) on each of the remotes - "create sequence my_seq start with 2 increment by 10000", start with 3, start with 4 and so on (you could have a table you maintain that has the numbers 2, ... 10000 and upon "install" of the application onto a remote site you "select * from t for update", fetch the first row, delete it and commit. then create the sequence using that number you just got.
cunning...
A reader, February 20, 2004 - 10:40 am UTC
I hadn't thought of that very cunning...
I thought that aside from the locking of the sequence while I alter it I would be "safe" from users coming in at 1.1, 2.1, 3.1... Is that what you mean??
say the sequence is currently at 1
1.0 I alter the sequence to increment by 1000
1.1 they get a value that is now 1001
2.0 I get 2001 and call it my minimum - the nextval is now 3001
2.1 they get 3001 - this is fine, I won't use it, in fact I
3.0 get the next value in case no one else has to ensure the sequence "bumps along" (to 4001 in this case)
3.1 they get 5001
4.0 I change the sequence back down to increment by 1
4.1 They get 5002.
It doesn't seem to matter to me how many people "jump in" between my X.0 steps, I can be safe in the knowledge that no one can use the value I get in step 2.0 + 1000 (or what ever I changed the increment by too.
That said I think I'll go with your solution, the number of remote sites will be around 50... ;)
February 20, 2004 - 12:56 pm UTC
you cannot "lock" a sequence. right after you altered it (step 1) anyone could and would be able to select it again.
You are right -- since you only use min_val, you would be "safe" but -- think of the invalidations you would be doing (i'm dead against DDL in a real system)
create sequence my_seq;
alter session set sql_trace=true;
select my_seq.nextval from dual insql;
select my_seq.nextval from dual insql;
alter sequence my_seq increment by 1000;
select my_seq.nextval from dual;
alter sequence my_seq increment by 1;
select my_seq.nextval from dual insql;
select my_seq.nextval from dual insql;
tkprof with aggregate=no will show:
select my_seq.nextval from dual insql
Misses in library cache during parse: 1
********************************************************************************
select my_seq.nextval from dual insql
Misses in library cache during parse: 0
********************************************************************************
select my_seq.nextval from dual insql
Misses in library cache during parse: 1 <<<==== hard parse
********************************************************************************
select my_seq.nextval from dual insql
Misses in library cache during parse: 0
Sequence Cache
Anne, May 16, 2005 - 9:33 am UTC
This is awesome! If I had a sequence with a cache of say 1000, when I select myseq.nextval from dual , it would get 1..1000 loaded up. A subsequent database shutdown abort would lose 2 .. 1000, right ? By using a higher cache, I am losing more numbers. Why would you still recommend setting the cache high ?
May 16, 2005 - 12:54 pm UTC
who cares if you lose 1000 numbers? even if you generate numbers at the rate of 1,000,000 per second
ops$tkyte@ORA9IR2> select (to_number(rpad('9',27,'9'))/1000000)/60/60/24/366 from dual;
(TO_NUMBER(RPAD('9',27,'9'))/1000000)/60/60/24/366
--------------------------------------------------
3.1623E+13
it would take that many years to exhaust it. whats a few thousand here or there.
by using a higher cache size you are getting possibly much improved performance.
by not using it you are not getting possibly improved performance.
sequences have gaps (that is irrefutable), and it really doesn't matter if they are 1,000 bit or 10.
Anne, May 16, 2005 - 2:19 pm UTC
Thanks Tom for the explanation!
sequence cache size
Vineeth.M, August 02, 2012 - 5:59 am UTC
HI Tom,
We got the below warnings by running the raccheck script.. so please help me to get rid of this situation
## WARNING SQL Check SYS.IDGEN1$ sequence cache size < 1,000 All Databases View
**how to set this sequence cache size..
## Parameter Check pre_page_sga = true PORTALDB1, PORTALDB2 View
** how can i set the pre_page_sga=false, our db is running on spfile or else please provide me the exact command to set this..
## OS Check init.cssd open files limit (ulimit -n ) is NOT set to recommended value >= 65536---> it was set to unlimited..
bash-3.2$ ulimit -n
unlimited
August 02, 2012 - 9:15 am UTC
ignore anything about SYS objects, you may only modify them with the assistance of support. If you want to discuss that one, please open a service request.
your DBA would know how to change a parameter setting in their sleep (if they do not, you need a DBA). Please have them take care of this, they may have set it for a reason.