12.1 and 12.2
Ive been having some excessive REDO issues lately, mined the archive logs and found SEQ$ is my problem. Frequent updates, meaning my caches arent high enough on sequences that are frequently select from.
We usually run with default of 10000 for caches so was very interested to see if we got some sequences that sneaked in over the years with caches lower than that.
So select * from dba_sequences, we see a number of sequences owned by application users with the default cache of 20, ok , no problem but I look at the last_number and determine theyre not really my issues as theyre low.
So then I see I have a number of sequences with really high numbers but cache_Size = 0, which are very probable culprits.
But how is that as I cant actually create a sequence with a cache of less than 2?
The only think I can think is that was allowed in earlier versions and theyve been there for quite some time. Anyone any ideas? Changing them isnt an issue but if thats a byproduct of how we create sequences Id be interested in seeing how they got in
SQL> create sequence s1 cache 0;
create sequence s1 cache 0
*
ERROR at line 1:
ORA-04010: the number of values to CACHE must be greater than 1
SQL> create sequence s1 cache 1;
create sequence s1 cache 1
*
ERROR at line 1:
ORA-04010: the number of values to CACHE must be greater than 1
SQL> create sequence s1 cache 2;
Sequence created.
SQL>