Oracle

December 9, 2010

Determining appropriate Sequence Cache value

Filed under: Uncategorized — srivenu @ 10:21 am

While monitoring one of the oracle instances, i observed a lot of “row cache lock” events. Several consurrent insert sessions were waiting on this event and p1 pointed out to the sequence cache. The insert statement showed which sequence it was and setting the proper cache value for that sequence (it was set to 0) resolved the issue.

The question that many of us face is the appropriate value to set for the sequence cache.

I normally use one of the following procedures to set the cache values for the sequences (presuming that i dont need ORDER and don’t mind loosing some of the cached values).

Procedure 1 – I assume that all sequences are used from the day of creation.

You can use sql like this to find the sequence usage per minute. You can increase the CACHE value for those sequences where USEDPERMIN exceeds present CACHE value.

select	ds.SEQUENCE_OWNER, ds.SEQUENCE_NAME, 
	CEIL(CEIL(ds.noused/do.noofdays)/1440) usedpermin, 
	ds.CACHE_SIZE
from	
	(select SEQUENCE_OWNER, SEQUENCE_NAME, 
		ROUND(LAST_NUMBER-MIN_VALUE/INCREMENT_BY) noused, 
		CACHE_SIZE
	from  	dba_sequences
	where  	SEQUENCE_OWNER not in 
		('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
	) ds,
	(select	OWNER, OBJECT_NAME, sysdate-CREATED noofdays
	from	dba_objects
	where  	OBJECT_TYPE = 'SEQUENCE'
	and	OBJECT_NAME not in 
		('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
	) do
where	ds.SEQUENCE_OWNER = do.OWNER
and	ds.SEQUENCE_NAME = do.OBJECT_NAME
order	by ds.SEQUENCE_OWNER, ds.SEQUENCE_NAME

You can modify the value 1440 to 720, if you want to CACHE 2 mins worth of sequence data.
Beware of RAC instances where the sequence might be used from several instances which might need a reduced CACHE per instance.

Procedure 2 – In this i try to be more scientific.

During peak hours i create a table

create table seq_start as select * from dba_sequences;

After some time, say 1 hour i create another table (you could put the whole thing in a script)

create table seq_end as select * from dba_sequences;

Then if i decide to cache atleast 2 mins worth of sequence data, i use this to generate the DDL.

select  'alter sequence '||ss.SEQUENCE_OWNER||'.'||
	ss.SEQUENCE_NAME||' cache '||
 	ROUND( CEIL((se.LAST_NUMBER - ss.LAST_NUMBER) / 30), -2)||
	' ;'
from  	seq_start  ss,
 	seq_end  se,
	dba_sequences ds
where  	ss.SEQUENCE_OWNER = se.SEQUENCE_OWNER
and 	ss.SEQUENCE_NAME = se.SEQUENCE_NAME
and	ds.SEQUENCE_OWNER = ss.SEQUENCE_OWNER
and	ds.SEQUENCE_NAME = ss.SEQUENCE_NAME
and	ds.CACHE_SIZE < ROUND( CEIL((se.LAST_NUMBER - ss.LAST_NUMBER) / 30), -2)
and 	se.LAST_NUMBER > ss.LAST_NUMBER
and 	se.SEQUENCE_OWNER not in
	('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
order 	by (se.LAST_NUMBER - ss.LAST_NUMBER) / 30
/

Just keep a note of these things

– RAC instances, which might need a reduction in the above cache’s as the sequence might be used by sessions from all or some of the RAC sessions.

– Sequences that might not be used during the time we monitored (in which case we might increase the time between the creation of seq_start & seq_end).

– Sequence usage might not be consistently equal over the time interval

Advertisements

4 Comments »

  1. Nice doc… I was wondering for sequence cache usages from long time …. thanks.. dear

    jatinder

    Comment by Jatinder — March 26, 2012 @ 3:36 am | Reply

  2. Hi,
    your first sql is good, however when I run this the usedpermin counts down, is this expected behaviour?

    regards

    Alan

    Comment by Alan — May 30, 2012 @ 2:07 am | Reply

    • Hi Alan,
      My guess is that the particular sequence was used very frequently (due to some loader job etc) sometime in the past.
      So as time goes on, the average usage comes down.
      regards
      srivenu

      Comment by srivenu — May 30, 2012 @ 5:40 am | Reply

  3. It’s very simple to find out any matter on web as compared to books, as I found this piece of writing at this website.

    Comment by ferrari deals — January 22, 2013 @ 3:44 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: