Sequence.nextval can really deceive you !!

Yes, they really can.


Yesterday, I noticed that one of the sequence number (pol_no_seq) in our IH application was giving different set of results when accessed (pol_no_seq.nextval) from two different machines. When I shared this observation with colleagues, the feedback was something like "It does not make sense" and frankly speaking, even I thought the same :-) But, how could I ignore what I actually saw with my own eyes ? I was really puzzled.


Given below is the explanation of what and why it happenned, based on my findings from some forums:



Definition of pol_no_seq :

CREATED 25-JAN-07

LAST_DDL_TIME 25-JAN-07

SEQUENCE_OWNER INSURANCE

SEQUENCE_NAME POL_NO_SEQ

MIN_VALUE 1

MAX_VALUE 1000000000000000000000000000

INCREMENT_BY 1

CYCLE_FLAG N

ORDER_FLAG N

CACHE_SIZE 200

LAST_NUMBER 2802765



Results :

Machine A : pol_no_seq.nextval->28000146

Machine A : pol_no_seq.nextval->28000147

Machine B : pol_no_seq.nextval->28000412

Machine B : pol_no_seq.nextval->28000413

Machine A : pol_no_seq.nextval->28000148

Machine B : pol_no_seq.nextval->28000414



Why it happened?

This could happen in a Oracle RAC environment.

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing a clustered database.In an Oracle RAC environment, two or more computers (each with an instance) concurrently access a single database. This allows an application or user to connect to either computer and have access to a single coordinated set of data.



Now coming back to our problem - Sequence being "noordered", oracle will not guaranteee the order in which numbers are generated. The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesn't make "ordered” the default for sequences ??



If we make the sequence "ordered" and increase the cache size to say, 1000; selecting the nextval from either of nodes will get the next number as expected. But, at the same time, there would be some performance implications due to cluster synchronization.



How does RAC synchronize sequences?

In Oracle 10g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.

This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequence's next value.

The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldn't be more obscure. That view shows overall statistics for the session.

However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1" parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.

In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency.



Two ways for determining whether your database is running in RAC environment or not :

1. BEGIN


IF dbms_utility.is_cluster_database THEN


dbms_output.put_line('Running in SHARED/RAC mode.');


ELSE


dbms_output.put_line('Running in EXCLUSIVE mode.');


END IF;


END;


/



2. show parameter CLUSTER_DATABASE - If the value of CLUSTER_DATABASE is FALSE then database is not running in RAC Mode else it is.



That is all for today. Feel free to provide feedback.........Thanks.
 
[I published this content on my official blog on 8/12/2010 11:46 PM]

Comments

Post a Comment

Popular posts from this blog

Traps in Decision Making

Decision making is an important activity, not only for executives but for everyone in the world. In our day to day life, we make lot of decisions. Starting from choosing between chocolate and strawberry to life and death, we make tons of quick decisions unconsciously. Some of these decisions may not be so important but some can be really vital and may result in disaster. We choose actions from opinions via mental processes which are influenced by biases, reason, emotions and memories.  While pursuing AMDA Silver certification, I came across a white paper on Decision Making which explained lot of interesting hidden traps that a decision maker may ignore while decision making. In this blog, I would like to share some of those common traps which we should avoid while making a decision. Anchoring Trap : When considering a decision, mind gives disproportionate weight to the first information it receives. You can try it!! If I ask you two questions: a)  ...
Read more

Attractive Reports from SQLPLUS

Image
Hi Everyone, On last Friday, I finished working on automating the generation and mailing of a report. Got to learn few interesting way of generating reports directly from SQLPLUS without any extra effort and would like to share that with you. SPOOL ON/OFF is a common functionality in SQLPLUS to write the output of query into a file. Following lines will send the output to C:\test.txt file. SQL> SPOOL C:\test.txt SQL> select * from employee; ---- assuming that employee is an existing table. SQL> SPOOL OFF Now, we might be interested in creating output report in excel format. Simplest way will be: SQL> SET COLSEP ‘,’ SQL> SPOOL C:\test.csv    --- filename with “.csv” as extension SQL> select * from employee; ---- assuming that employee is an existing table. SQL> SPOOL OFF Open test.csv in excel and you are there. But, what if we need output in HTML format????? ………………Well, there is a way to do this as well in SQLPLUS. See below: SQL > SET MARKUP HT...
Read more