When creating sequences, most people give no thought to the size of the cache, just accepting the default of 20. That is perhaps a reasonable trade off for sequences where you have no idea about the velocity of allocation. After having been in production for a while, it is worth taking a look at the cache sizes and adjusting them to a more optimal value. The following SQL looks for sequences that have a cache set, but such a low number of allocations that it would be reasonable to turn off the caching.
SELECT sequence_owner,sequence_name,cache_size,last_number,’alter sequence ‘||sequence_owner||’.”‘||sequence_name||'” nocache;’ nocache_cmd
FROM dba_sequences WHERE last_number – min_value 0
AND sequence_owner NOT LIKE ‘APEX%’ AND sequence_owner NOT IN (SELECT owner FROM dba_logstdby_skip WHERE statement_opt = ‘INTERNAL SCHEMA’)
AND last_number < 1000 ORDER BY 1, 2;
You should manually look over this list before just executing the alter commands in the last column, but generally these are safe to run unless the sequence is newly created.
Now for the other end of the spectrum — which sequences would perform better if the cache size was increased? Try this SQL:
SELECT sequence_owner,sequence_name,cache_size,last_number,’alter sequence ‘||sequence_owner||’.”‘||sequence_name||'” cache ‘||cache_size * 10||’;’ cache_cmd
FROM dba_sequences WHERE last_number – min_value > cache_size * 500 AND cache_size 1000000 AND last_number > 1000000 ORDER BY 1, 2;
Increasing the size of the cache is a bit trickier, and you should think about the usage of the sequence before increasing this value. If the value is too low, increasing it can have a dramatic effect on insert performance. Be particularly careful about changing sequences from nocache (cache size 0) to cache. This can change the behavior of the sequence assignment in subtle ways, so make sure you understand that effect and confirm that it will not affect the application.