Updating an oracle sequence via plsql
I include it here because it allows us to compare the cost of context-switches to the cost of updates.
A great deal of information about Oracle exists throughout the net.
We can see that, in this case, the sequence pseudo-column is used in a similar manner to a function call.
Note that we can also use CURRVAL in the same manner as NEXTVAL, as the following example demonstrates.
With SQL_ID 4m7m0t6fjcs5x that’s easy to accomplish by changing the sequence’s cache value.
In our case, seeing SQL_ID 4m7m0t6fjcs5x as the top SQL statement quickly lead us to check the sequence settings and saw that almost all sequences had been created with the NOCACHE option.
The sequence’s last value is stored in memory and a certain definable number of values are pre-fetched and cached in memory (default is CACHE=20). The resulting SQL_ID and statement for this recursive SQL is: In our case it was the #1 top statement in terms of cumulative CPU.