Implications/side effects of changing cache size of sequence

orausern

Hi Experts,

We are on Oracle 11.2.0.2 on Solaris 10. I have a question about sequences. They are defined as NOCACHE in my database and we are seeing a lot of "row cache lock". So I am thinking of changing the sequences to cache 20 values. Does this have any other implication -except that during db restart the cached values can be lost.

Thanks,

Osama_Mustafa

No impact on that even it will avoid the overhead of extra physical I/O and give tremendous performance boost to your system

Osama_Mustafa

Handle: orausern
Status Level: Newbie (40)
Registered: Mar 27, 2006
Total Posts: 405
Total Questions: 119 (81 unresolved)
Name nirav


What is this !!!!!!!!!!!!!!!
try to close your thread at least .

802709

I dont think that row cache lock event is relevant with sequence cache size.

sequence cache is useful when you get lots of value from sequence in a plsql code (loop). this is not an exact side effect but just consider: for ex

when a session get a value from sequence and sequence cache is 20, next 20 value of sequence will be reserve for your session, other words, if you get 450 as value of sequence next 20 value (untill 470) will be yours to, if someone else try to get a value (after you get 450) other session will get 471.
that is the only side effect if that counts and this is avaliable if you use sequence in a PLSQL code like loop.

so, if you get lots of value in a loop, you should set cache size because if you dont you will see lots of log file sync event. if your plsql codes doesnt use sequences much, then it is not necessary.

ps: these info is valid for single instance. on a rac system it will be different.

VenkatB

so, if you get lots of value in a loop, you should set cache size because if you dont you will see lots of log file sync event. if your plsql codes doesnt use sequences much, then it is not necessary.

What has sequence or sequence cache got anything to do with log file sync waits? Log file sync is a client event where the user processes wait while LGWR flushes the redo logs. It's caused most probably by frequent commits (especially commits within a loop)

Sequence caching is always useful in applications when concurrent sessions request sequence value where Oracle has to update SEQ$ data dictionary. This will cause heavy contention in database particularly when there are concurrent users working on it. All sequences have to be cached, unless, the application (poorly written one) cannot afford to have any gaps within the primary key value or any other keys maintained by sequences

Regards
Venkat

802709

@VenkatB, when you get a value from sequence, to provide consistency, sequence execute an internal commit, works like kind of autonomous transaction.
edit: this is also how, after a crash sequence still get his last value.

that is because if you dont set cache and use in a loop the sequence, that cause to log file sync event. (of course you must get many values from sequence)

edit:

Edited by: Mustafa KALAYCI on 19.Tem.2012 17:17

AdamMartin

Mustafa KALAYCI wrote:
when a session get a value from sequence and sequence cache is 20, next 20 value of sequence will be reserve for your session, other words, if you get 450 as value of sequence next 20 value (untill 470) will be yours to, if someone else try to get a value (after you get 450) other session will get 471.

No. Sequences do not work this way.

The sequence cache is instance-wide, not session specific.

The DB pulls into memory two numbers (regardless of the size of the cache). It pulls in the next value of the sequence and another number that is the next value + cache size (-1) of the sequence. When the numbers match, the DB does an autonomous commit and gets the next range of values. With a cache of 20, if the next value is 451, the DB will have in memory 2 high-low numbers (for a total range of 20 reserved sequence values) of 451 and 470. Then it will have 452 and 470; then 453 and 470; etc... incrementing the low value until it reaches the high value. Any session can grab the next value.

AdamMartin

Triple posted somehow.

AdamMartin

Wow, triple post.

VenkatB

@VenkatB, when you get a value from sequence, to provide consistency, sequence execute an internal commit, works like kind of autonomous transaction.

edit: this is also how, after a crash sequence still get his last value.

that is because if you dont set cache and use in a loop the sequence, that cause to log file sync event. (of course you must get many values from sequence)
>

Prove it.

Log file sync may only be incidental but may happen only from the application code when they get sequence and then do a commit within a loop. In fact, log file sync need not happen just because it's committed in loop.

When user process gets sequence value, Oracle updates SEQ$ with the value to update the value. In fact, Oracle does it for almost all of the foreground code. When Oracle has to add an extent, when Oracle has to update a new datafile header, when Oracle has to autoextend a file, etc...it always inserts/updates one of the dictionary views all the time. So it's nothing new or just with sequence.

And don't confuse yourself with a timed event with a wait.

I have tested on a 11g and 10g and I don't see any log file sync waits in tkprof or v$session_event

SQL> create sequence seq_test start with 1 nomaxvalue nocache;

Sequence created.

SQL> Declare
  2   v_cnt number;
  3  Begin
  4   For i in 1..10000
  5   Loop
  6     select seq_test.nextval into v_cnt from dual;
  7   End Loop;
  8  End;
  9  /

PL/SQL procedure successfully completed.

802709

@VenkatB, if I am not wrong, what I said is from Tom Kyte's book. when i find it i will write here. also I had tests about seqs too and I was able to see log file sync event in my top events (and some other log events too). also your code is too small, there is almost nothing to create redo log. while running with a highly loaded plsql code (dml operations in the loop) and after using sequences probably will cause to what i said.

@Adam Martin, I had some tests about that too maybe I'm wrong but I will re-check my codes and documentation and if wrong, i will make myself correct.