So?  Even if they are aged out (no likely if you a) size right b) use bind variables) so???

Why is a gap of 5 ok but 400 bad?  You do know that if you lost 400 a second -- it would take:

ops$tkyte@ORA920.US.ORACLE.COM> select (rpad('9',27,'9')/400) seconds,
  2         (rpad('9',27,'9')/400)/60 minutes,
  3         (rpad('9',27,'9')/400)/60/60 hours,
  4         (rpad('9',27,'9')/400)/60/60/24 days,
  5         (rpad('9',27,'9')/400)/60/60/24/365.25 years
  6    from dual;

   SECONDS    MINUTES      HOURS       DAYS      YEARS
---------- ---------- ---------- ---------- ----------
2.5000E+24 4.1667E+22 6.9444E+20 2.8935E+19 7.9220E+16

ops$tkyte@ORA920.US.ORACLE.COM>


7.9e16 YEARS to exhaust that sequence....

Yes you can ping

Yes that would most like be a waste of good code on a system that was sized and coded properly

Yes it would not matter in the long run.

search for

pinning sequence

on this site to see how to pin them and

connor "missing sequence number"

(and look for connor's comment in there) to see how to "unload" them.

But -- its a waste of really good coding time IMO
 

Tell you what -- I'll present the numbers and you can make the choice, refer to 

http://asktom.oracle.com/~tkyte/runstats.html

for details on this test harness I'm using:

ops$tkyte@ORA920.US.ORACLE.COM> create sequence cached cache 1000;
Sequence created.

ops$tkyte@ORA920.US.ORACLE.COM> create sequence not_cached nocache;
Sequence created.

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_num   number;
  7  begin
  8      insert into run_stats select 'before', stats.* from stats;
  9
 10      l_start := dbms_utility.get_time;
 11      for i in 1 .. 100000
 12      loop
 13          select cached.nextval into l_num from dual;
 14      end loop;
 15      l_run1 := (dbms_utility.get_time-l_start);
 16      dbms_output.put_line( l_run1 || ' hsecs' );
 17
 18      insert into run_stats select 'after 1', stats.* from stats;
 19      l_start := dbms_utility.get_time;
 20      for i in 1 .. 100000
 21      loop
 22          select NOT_cached.nextval into l_num from dual;
 23      end loop;
 24      l_run2 := (dbms_utility.get_time-l_start);
 25      dbms_output.put_line( l_run2 || ' hsecs' );
 26      dbms_output.put_line
 27      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 28
 29      insert into run_stats select 'after 2', stats.* from stats;
 30  end;
 31  /
2497 hsecs
14753 hsecs
run 1 ran in 16.93% of the time

PL/SQL procedure successfully completed.

<b>Hmm, elapsed time is quite HUGELY different.... the cached sequence is obviously "wall clock faster" but even more importantly:</b>



ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
...
STAT...consistent gets - exami        174     100005      99831
nation

STAT...consistent gets             300268     400125      99857
STAT...index fetch by key             129     100001      99872
STAT...commit cleanouts               100     100001      99901
STAT...commit cleanouts succes        100     100001      99901
sfully completed

STAT...calls to kcmgas                100     100009      99909
STAT...execute count               100120     200032      99912
STAT...parse count (total)            119     100033      99914
STAT...opened cursors cumulati        113     100033      99920
ve

STAT...deferred (CURRENT) bloc         99     100024      99925
k cleanout applications

LATCH.sequence cache               300200     200000    -100200
STAT...enqueue requests               303     200079     199776
STAT...enqueue releases               302     200079     199777
STAT...redo entries                   221     200113     199892
LATCH.row cache enqueue latch         312     200600     200288
LATCH.dml lock allocation             391     200956     200565
LATCH.redo allocation                 362     201615     201253
STAT...calls to get snapshot s     300329     600089     299760
cn: kcmgss

LATCH.undo global data                483     301121     300638
STAT...db block gets                  348     303296     302948
STAT...db block changes               429     400152     399723
LATCH.library cache pin alloca        703     401043     400340
tion

LATCH.enqueue hash chains             912     401842     400930
STAT...session logical reads       300616     703421     402805
LATCH.shared pool                  201067     701434     500367
LATCH.row cache objects               716     600609     599893
LATCH.library cache pin            401212    1201376     800164
LATCH.library cache                501960    1602596    1100636
LATCH.cache buffers chains         603008    1920502    1317494
STAT...recursive calls             101649    1500253    1398604
STAT...redo size                    99800   72413704   72313904

99 rows selected.

ops$tkyte@ORA920.US.ORACLE.COM>


<b>Remember -- LATCHES are locks, locks imply serialization, serialization implies less scalable -- longer waits.

Also -- look at the STATS as well -- only 72m more redo for "nocache"


Nocache looks like a lose lose proposition to me, how about you?</b>
 

And just exactly how would nocache help you achieve:

"...if somebody need to know exact order of events (e.g.
inserts into table..."

any better (or worse) the CACHE 1000??

The results would be EXACTLY the same, 100% exactly the same. The values returned to nextval just come back SLOWER with nocache -- but the values are the same (slower -- same results -- bummer)

Now what?

I do not believe that was ever the behavior - the sequences are cached in the shared pool.

Maybe -- in version 6, before the shared pool .... but i cannot remember back that far anymore ;)

no chance of creating duplicates, no.

On a live system, you would (as with all DDL) want to do this in a controlled fashion, during time of minimal user activity to avoid locking and contention issues.

1) when you do the ddl on the sequence, it puts the value (current value) out to seq$. so the next time, the sequence starts with that value -- not some higher number.

It is a vain attempt to have gap free sequences which is of course not possible.

2) its pretty easy -- I'll let you read about it:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76939/adg14evt.htm#998000 <code>

I'm totally against wasting your time on this futile effort though, it is just such a waste of time, energy and resources.

you could never assume that in single instance oracle really.


OPTIMALLY each instance has its own very big cache. and 101 might be hours newer than 55

1) only one of those two snippets of code actually work!  and the best way to do it is not shown :)


   do not:

   select s.nextval into l_n from dual;
   insert into t ( pk, .... ) values ( l_n, .... );


   DO

   insert into t ( pk, .... ) values ( s.nextval );

   OR, if you need the value:

   insert into t ( pk, ..... ) values ( s.nextval ) RETURNING pk into l_n;



but it is rare to need the value, for you have S.CURRVAL you can reference in subsequent DML


2) In this case, both work -- and the second option, if you need th year in a variable, is the only reasonable approach.

NEVER
   select <something> into VAR from dual;

WHEN
   VAR := <something>;

works.


3) In plsql, it'll short circut:

ops$tkyte@ORA9IR2> exec if ( 1=0 and 1/0 > 5 ) then null; end if;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec if ( 1=0 or 1/0 > 5 ) then null; end if;
BEGIN if ( 1=0 or 1/0 > 5 ) then null; end if; END;
 
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 1


In SQL, you have zero control over the order of anything, so do not rely on a predicate getting evaluated in any order.


 

A sequence is more or less

- a name
- last incremented value
- a current value
- an increment size (cache size)

when you say nextval, they just do the logical equivalent of:

  if current_value >= last_incremented_value + increment_size
  then
      update seq$ to get another increment_size reserved;
      commit;
      last_incremented_value = current_value
  end if;
  current_value = current_value + 1;
  return current_value;

so, they need about the same amount of memory, regardless of cache size (but a bigger cache size can massively impact your performance if you hit these sequences alot -- removing that update is great)

ops$tkyte@ORA9IR2> create sequence s1;
 
Sequence created.
 
ops$tkyte@ORA9IR2> create sequence s2 cache 10000000000000000000;
 
Sequence created.
 
ops$tkyte@ORA9IR2> select s1.nextval from dual;
 
   NEXTVAL
----------
         1
 
ops$tkyte@ORA9IR2> select s2.nextval from dual;
 
   NEXTVAL
----------
         1
 
ops$tkyte@ORA9IR2> @printtbl8 'select * from v$db_object_cache where owner = user';
OWNER                         : "OPS$TKYTE"
NAME                          : "S2"
DB_LINK                       : ""
NAMESPACE                     : "TABLE/PROCEDURE"
TYPE                          : "SEQUENCE"
SHARABLE_MEM                  : "868"
LOADS                         : "1"
EXECUTIONS                    : "0"
LOCKS                         : "1"
PINS                          : "0"
KEPT                          : "NO"
CHILD_LATCH                   : "1"
-----------------
OWNER                         : "OPS$TKYTE"
NAME                          : "S1"
DB_LINK                       : ""
NAMESPACE                     : "TABLE/PROCEDURE"
TYPE                          : "SEQUENCE"
SHARABLE_MEM                  : "868"
LOADS                         : "1"
EXECUTIONS                    : "0"
LOCKS                         : "1"
PINS                          : "0"
KEPT                          : "NO"
CHILD_LATCH                   : "1"
-----------------
 

this agrees 100% with what i said?

its stops when it can stop.


where do you see a conflict/difference in these statements?

IN PLSQL

I said "in SQL". to point out the difference between PLSQL and SQL.

PLSQL -- procedural.

SQL -- non procedural, any order we feel like doing it that day.

enable sql tracing with wait events.

trace it.

see what you are waiting on exactly.

define "decreased" and how you actually measured.

sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.
sequences have gaps, all of them, every single one, without a doubt.

you tell me, in your application - what RULE tells you what came first?

unless you serialize, one at a time, it is rather arbitrary. You have to look to your logic, your application and answer yourself "what one 'comes first', what does it mean to 'come first'".


We (the database) have commit ordering down to a science, for us, it is the SCN (system change number, system commit number). We maintain that for ourselves, so we know the commit order - for replication, recovery, etc. We care when the transaction ends.

But that is us, what are your exact needs here - why do you need an ordering 'thing', what is the underlying logic that you are performing.

Then, once we understand that, we can discuss how best to implement that requirement (and it probably won't involve 'ordering' anything).

why are you using nocache? You like things to go slow???????

I would use cache <some big number> for all of them - period.

why or how could you have 100,000 sequences???? seriously? How could that happen? I cannot imagine.


In any case, it takes as much memory to store a cache 10 sequence as a cache 10000000 sequence.

We only need to cache the current sequence value and the end point value - for example, if you have a sequence that caches 1000, we would store in the SGA:

current      highest
1            1000

when someone requests one, we just increment current. And we keep incrementing current until it is equal to highest - when it is - we update sys.seq$ and update the memory contents of current and highest to

1001,2000

and keep on going.