As I've described in my blog post roots of the problems related to gapless sequences must be sought much before coding and tuning.
I think the trigger should be changed so that it also sets the last Id in sequence as max id from inserted records.And here comes the most critical point - you have to make your decision very fast to at least a bit relief other people's pain.The former naturally produces timestamps, while the later produces a binary string. Of course both timestamps and binary strings can be represented as numbers. For example a naive representation of a timestamp could be the number 2012CREATE TABLE order(id INTEGER, customername VARCHAR(20), amount INTEGER); INSERT INTO order VALUES (NEXT VALUE FOR orders.orderid, 'Serge', 23), (NEXT VALUE FOR orders.orderid, 'John', 200); SELECT * FROM order ORDER BY id; ID CUSTOMERNAME AMOUNT----------- ---- command.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.However when those cached values are exhausted the current sequence value must be written to disk (so duplicate values aren’t given upon restarts after instance crashes). The report would select a large number of rows and was using a sequence value and the NEXTVAL call to form a surrogate key. Well like most SQL tuning initiatives one of the best ways to tune a statement is to run it less frequently.