To cache or not to cache an Oracle Sequence?

Last week, when I was reviewing AWR report of a busy OLTP database, I came across a dictionary related “Update” statement. This update statement was listed in top 10 SQL’s under “SQL ordered by Parse Calls” and “SQL ordered by Executions” sections of the AWR report and was parsed and executed 698 times during 30 minutes (A 30 minutes AWR report).

Here is the complete SQL Statement:

“update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1”

Immediately, I queried Oracle data dictionary for a list of sequences of one of our most important schema. Following is the query and its output:

SQL> select sequence_name, cache_size, last_number from dba_sequences where sequence_owner = 'PISYS';

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_AUDIT_DETAILS                   0     6728991
SEQ_TRANS_LOG                       0           1
SEQ_BATCH_LOG                      20         991

Sequence “SEQ_AUDIT_DETAILS” seems to be a culprit here, it has been used for more than 6 Million times and is not cached. Upon further investigation, it was revealed that this sequence is used by an audit detail table to generate unique serial.

I altered the sequence definition to cache 1000 values. Caching instructs Oracle to pre-allocate (in my case 1000) values in the memory for faster access.

SQL> alter sequence pisys.seq_audit_details cache 1000;

The above ALTER command did a magic and the UPDATE statement vanished from the AWR reports.

We need to cache sequences whenever possible to avoid extra physical I/O.

Thanks for reading and comments are welcome.