yes it happen
April 23, 2001 - 4am Central time zone
Reviewer: ashraf gamak from kuwait
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?
July 20, 2001 - 12pm Central time zone
Reviewer: Paul Dong from New York
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...
July 20, 2001 - 1pm Central time zone
Reviewer: Connor from UK
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)
July 22, 2001 - 10pm Central time zone
Reviewer: sreenu from singapore
Thanks Tom for expanding that point. Great logic.
How can I avoid gap while a sequence ages out?
July 22, 2001 - 11pm Central time zone
Reviewer: hahaer from Chine
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)
July 23, 2001 - 3am Central time zone
Reviewer: Connor from UK
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
July 23, 2001 - 11am Central time zone
Reviewer: Gururaj Kulkarni from Norcross GA,USA
For getting more from Tom :) and sharing good info with us.
Speciall y cacheing lot of numbers
July 23, 2001 - 8pm Central time zone
Reviewer: Pichaimani Balaubramanian from San Jose, CA USA
Was a real eye opener
Metalink implies non-gap sequence
September 2, 2003 - 10am Central time zone
Reviewer: A reader
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?
Followup September 2, 2003 - 10am Central time zone:
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....
February 20, 2004 - 9am Central time zone
Reviewer: Mike from England
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
Followup February 20, 2004 - 10am Central time zone:
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...
February 20, 2004 - 10am Central time zone
Reviewer: A reader
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... ;)
Followup February 20, 2004 - 12pm Central time zone:
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
May 16, 2005 - 9am Central time zone
Reviewer: Anne from MN
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 ?
Followup May 16, 2005 - 12pm Central time zone:
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.
May 16, 2005 - 2pm Central time zone
Reviewer: Anne from MN
Thanks Tom for the explanation!
|