Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Implications/side effects of changing cache size of sequence

orausern
Member Posts: 633
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,
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,
Answers
-
No impact on that even it will avoid the overhead of extra physical I/O and give tremendous performance boost to your system
-
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 . -
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. -
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 -
@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 -
Mustafa KALAYCI wrote:No. Sequences do not work this way.
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.
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. -
Triple posted somehow.
-
Wow, triple post.
-
@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_eventSQL> 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.
-
@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.
This discussion has been closed.