Caching a Sequence – Will it helps !

Yesterday, 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 = ‘OM_TEST’;

SEQUENCE_NAME                  CACHE_SIZE       LAST_NUMBER

—————————— ———-                         ———–

SEQ_AUDIT_DETAILS             0                                     6728991
SEQ_MTR_LOG                             0                                    1
SEQ_TRANS_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 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.