Followup
A reader, May 02, 2019 - 1:13 pm UTC
1. Why do sequence values age out?
2. What does it mean if this happens?
Thanks.
May 02, 2019 - 1:39 pm UTC
1. Because memory is a finite resource. The database ages rarely used objects out of the various caches to make way for more recently accessed ones.
2. It means you lose all the preallocated values. So if you have a sequence with cache 3000, get the values 1-100 out, then don't access the sequence for "a while", the eventually values 101-3000 will be flushed from the cache. So the sequence will never return these values.
Review
Geraldo Peralta, May 02, 2019 - 2:11 pm UTC
Ok.
So, it means that I am not using a lot the cache value. Right?
If so, how can I decrease the cache size of a sequence without altering the start with parameter in an identity column, just alter the cache size?
May 02, 2019 - 4:32 pm UTC
Yes.
alter sequence cache 100;
Review
Geraldo Peralta, May 02, 2019 - 9:29 pm UTC
ALTER SEQUENCE "USER"."ISEQ$$_61309" cache 20;
I am getting the error: ORA-32793: cannot alter a system-generated sequence.
Downside of reducing cache size
Jonathan Taylor, May 03, 2019 - 12:15 pm UTC
I would be wary of reducing the cache size:-
* Why was it set to 3000? A cache this high may suggest that this table was designed for a high volume of inserts. Maybe not day-to-day, but e.g. annually. Reducing the cache could reduce performance to some extent, although a cache of 20 should be reasonable.
* Why worry about gaps? With RAC and even a cache of 20 (or even nocache) - you will get gaps. If this is an arbitrary primary key ID (as opposed to something like a customer reference number) - there is no real need to keep them "compact".
May 03, 2019 - 2:00 pm UTC
If the OP is regularly seeing large chunks of values "missing", it's a sign they probably oversized the cache.
But yes, there's probably little to be gained by making the cache size smaller.
Review
Geraldo Peralta, May 04, 2019 - 12:48 am UTC
Well, We just went out to production. The load is not high but it is expected in a near future. For now, We just have one customer. But I saw that I was losing many sequences (here you explained me why), so I decided to set cache back to 20 (for all sequences) and increase them little by little.
May 06, 2019 - 4:40 pm UTC
You can also pin a sequence using dbms_shared_pool, but that reduces not eliminates loss of sequences
upon DB bounce
Rajeshwaran, Jeyabal, May 06, 2019 - 2:41 pm UTC
....
If you access the sequence rarely, then eventually the database will age these pre-generated values out. Meaning you've lost them. The next access will get the values 6001-9000.
This is normal and expected.
This will also happen if you restart your database instance.
....Upon restart the DB instance, dont see that sequence getting aged out. Kindly advice.
demo@PDB1> create table t(x int generated always as identity (start with 1 cache 500) ,y varchar2(10) );
Table created.
demo@PDB1> insert into t(y) values('Hello');
1 row created.
demo@PDB1> insert into t(y) values('World');
1 row created.
demo@PDB1> select * from t;
X Y
---------- ----------
1 Hello
2 World
demo@PDB1> commit;
Commit complete.
demo@PDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\admin>set oracle_sid=ORA12CR2
C:\Users\admin>sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 19:58:38 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
idle> conn sys/Password-1 as sysdba
Connected.
sys@ORA12CR2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA12CR2> startup
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8922040 bytes
Variable Size 398462024 bytes
Database Buffers 109051904 bytes
Redo Buffers 7852032 bytes
In-Memory Area 104857600 bytes
Database mounted.
Database opened.
sys@ORA12CR2> conn demo/demo@pdb1
Connected.
demo@PDB1> select * from t;
X Y
---------- ----------
1 Hello
2 World
demo@PDB1> insert into t(y) values('Hello2');
1 row created.
demo@PDB1> select * from t;
X Y
---------- ----------
1 Hello
2 World
3 Hello2
demo@PDB1>
demo@PDB1>
May 06, 2019 - 4:34 pm UTC
A clean shutdown will cleanly flush things out.
SQL> create sequence seq cache 500;
Sequence created.
SQL> select seq.nextval from dual;
NEXTVAL
----------
1
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5117049392 bytes
Fixed Size 9038384 bytes
Variable Size 1040187392 bytes
Database Buffers 4060086272 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
SQL> conn mcdonac/*******@db18_pdb1
Connected.
SQL> select seq.nextval from dual;
NEXTVAL
----------
501
SQL>