Sequence values are cached in instance memory and by default 20 values are cached. As instance cache is transient, loss of an instance can result in loss of cached sequence values. Permanent record of highest possible value from any instance is kept track in SEQ$ table.
SQLs accessing this sequence within an instance will access instance SGA. As each instance caches its own sequence values it is highly likely that SQLs accessing this sequence from different instance will create gaps in sequence values.
Common knee-jerk reaction to this issue is to set nocache or cache 1 for these sequences. In a single instance environment, this approach will backfire due to massive updates to SEQ$ tables, buffer busy waits, latch free waits etc. In a RAC environment, this issue is magnified and almost hangs the instance. I had the privilege of working with a client to resolve one of their performance issues.
Instances were hung. It was not possible to login to the database. Many existing connections are working fine though. We were lucky enough that one of the DBAs had active connection to the database. So, we took systemstate dump from that session to see whey there is an hang (or slowness).
Alert log was printing following lines at this time frame. So, we know that there is a problem with row cache enqueue.
Wed Feb 18 06:31:31 2008 >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=45 Wed Feb 18 08:59:31 2008 >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=44 Wed Feb 18 08:59:31 2008 >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=43 Wed Feb 18 09:01:00 2008 >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=46 Wed Feb 18 09:01:00 2008 >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=27
We took a systemstate dump with following command.
alter session set events 'immediate trace name systemstate level 4';