I got a call from a customer who was concerned because he was using a sequence to order the activities he was tracking, and there were gaps in the list. He was concerned that the trigger was firing but transactions weren’t being committed. I did some quick research courtesy of Google, and discovered that Oracle typically caches 20 sequences in memory. The fact that there were gaps should have tipped me off to the underlying SGA issue, but I was still obsessed with the sequence. You know how it is, some days we are just so obsessed with putting out the immediate fire that we can’t see the leaky gas can sitting next to the open flame. Sequences take up very little memory, and this one was used so often that it shouldn’t have been aging out. But rather than ask why is this happening, I decided to “pin” the sequence into memory. It is very easy

exec dbms_shared_pool.keep(’schema.sequence’,'Q’);

You can verify this with the following query

select owner, name, type from gv$db_object_cache
where kept = ‘YES’ and owner = ’schema’;

I even followed it up with a nifty trigger to pin the sequence every time the database is started

create trigger pin_on_startup
after startup on database
begin
dbms_shared_pool.keep(’schema.sequence’,'Q’);
end;
/

The fire is out. I’m happy. Hey, what’s that smell? While basking in the glow of another job well done, I happened to fire up Enterprise Manager, and there on page 1 is an urgent warning telling me that the SGA is too small, and I need to increase it to 160M. Gee, do you suppose that was why the tiny little sequence that was frequently used was still getting tossed out of memory?

The sequence is still pinned in memory while we work out some issues with a new report that is running most inefficiently, and consuming all of the available resources. So the next time someone tells you there are gaps in their sequences, you might want to check your SGA size.

Oh, and a big thank you to Dan Morgan and his amazing library on the PSOUG site (http://psoug.org/library.html), as  I would never have figured out how to verify that the sequence was pinned.  As far as I’m concerned, Dan is one of the rock stars of Oracle database administration.