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.

I can’t believe it has been 14 months since I finished my certificate in Data and Internet Security at the University of Washington. This site was my final project. I was so anxious to make Oracle security my main focus, and grow the site into a comprehensive set of security tutorials for busy DBAs. I created a Drupal blog and announced my intentions in my first post, which was also my last post. Life got in the way. There was shoulder surgery, followed by more software releases and block points than I can even remember, followed by RAC and Data Guard. In short, I’ve been keeping busy, which is what DBAs do, and why we need information that is direct and to the point.

While database security will continue to be the main focus of this site, the blog may occasionally wander into other aspects of Oracle database administration. This is mostly because of my failing memory, as it will give me a place to store information I think I will need again. As a bonus, it gets the information into Google, for other people who are asking the same questions. Google is quite possibly the most important tool I use in analyzing problems, and I am happy to give something back. I hope you find something useful while you are here.