#Oracle scratchpad update#

If I attempt to consume 1,000,000 sequence values per second and the sequence is defined with the default cache of 20, it means that I now need to update the SEQ$ table in the data dictionary 50,000 times per second! That defeats the whole idea of caching to reduce data dictionary overheads.

Of course, modern systems are now measured thousands or even millions of transactions per second. Systems were measured in tens of transactions per second, so a sequence cache value of 20 was typically little cause of concern. When sequences first arrived in the Oracle database, 50Mhz was a fast speed for a database server CPU. This is why there is always the possibility of gaps in sequence number generation. In this way, if the database gets bounced, or the sequence values are flushed out of the SGA, we can go back to the data dictionary table to work out where the start the next round of sequence values. When you get the first sequence value (typically “1”), the data dictionary will show the sequence LAST_NUMBER of 21, indicating that values 1 to 20 are consumed from the SGA, and that when those are exhausted (and someone gets the value of 21), we will return to the SEQ$ and update the LAST_NUMBER to become 41 and so forth. The caching is more a measure of how often we will synchronise the database dictionary definition of the sequence usage with what is happening in memory.įor example, if you create a sequence with the default cache of 20, this means that every time 20 sequence values are consumed by clients, we will return to the data dictionary and update the internal SEQ$ table with details on the sequence usage.

This is not how the caching implementation works. When people hear the term “cache” when it comes to sequence, they can mistakenly think of a “bucket” of numbers loaded into the SGA that are then fed out to database client processes as they request them. Sequence cachingīy default sequences are cached in the Oracle database in order to improve their performance. Topics included index selection, block contention, partitioning and also the issue of sequence contention. A few months back I gave a talk on the importance of good physical design if you need to meet extreme levels of performance in your Oracle database.