Oracle RAC and sequences
Exactly what do you mean by "ordered" in this context?
By default, each node in the cluster has a separate cache of sequence numbers. So node 1 may be handing out values 1-100 while node 2 is handing out values 101-200. The values returned from a single node are sequential, but session A on node 1 may get a value of 15 while session B on node 2 gets a value of 107 so the values returned across sessions appear out of order.
If you specify that the sequence has to be ordered, you're basically defeating the purpose of the sequence cache because Oracle now has to communicate among nodes every time you request a new sequence value. That has the potential to create a decent amount of performance overhead. If you're using the sequence as a sort of timestamp, that overhead may be necessary but it's not generally desirable.
The overhead difference in practical terms is going to be highly application dependent-- it will be unmeasurably small for some applications and a significant problem for others. The number of RAC nodes, the speed of the interconnect, and how much interconnect traffic there is will also contribute. And since this is primarily a scalability issue, the practical effect is going to limit how well your application scales up which is inherently non-linear. Doubling the transaction volume your application handles is going to far more than double the overhead.
If you specify NOCACHE, the choice of ORDER or NOORDER is basically irrelevent. If you specify ORDER, the choice of CACHE or NOCACHE is basically irrelevent. So CACHE NOORDER is by far the most efficient, the other three are relatively interchangable. They are all going to involve inter-node coordination and network traffic every time you request a sequence value which is, obviously, a potential bottleneck.
It would generally be preferrable to add a TIMESTAMP column to the table to store the actual timestamp rather than relying on the sequence to provide a timestamp order.
How can I guarantee sequential order in multi-server Oracle RAC environment
An Oracle sequence with ORDER specified is guaranteed to return numbers in order across a RAC cluster. So
create sequence my_seq
start with 1
increment by 1
order;
Now, in order to do this, that means that you're going to be doing a fair amount of inter-node communication in order to ensure that access to the sequence is serialized appropriately. That's going to make this significantly more expensive than a normal sequence. If you need to guarantee order, though, it's probably the most efficient approach that you're going to have.
Oracle Sequence value are not ordered
Secondly, Can I achieve the ordering if I alter the sequence to be
NOCACHE irrespective of ORDER/NOORDER.
yes as NOCACHE is effectively order as you're forcing a write to the sys.seq$ table on each increment, which has to serialise over nodes too.
--
I would dispute the accepted answer in that possible duplicate. there is a huge difference in CACHE + ORDER and NOCACHE in RAC. You are not negating the CACHE with ORDER; just reducing its effectiveness. I've personally seen performance of a middle tier application degrade drastically as they used NOCACHE on a sequence and were accessing on multiple nodes at once. We switched their sequence to ORDER CACHE (as they wanted an cross-rac order). and performance drastically improved.
in summary: The sequence speed will be from fastest to slowest as "CACHE NOORDER"->"CACHE ORDER" and way way WAY behind "NOCACHE".
This is easily testable too:
So we start with a standard sequence:
SQL> create sequence daz_test start with 1 increment by 1 cache 100 noorder;
Sequence created.
ie CACHE with no order. Now we fire up two sessions. I'm using a 4 node RAC database 10.2.0.4 in this test:
my test script is simply
select instance_number from v$instance;
set serverout on
declare
v_timer timestamp with time zone := systimestamp;
v_num number(22);
begin
for idx in 1..100000
loop
select daz_test.nextval into v_num from dual;
end loop;
dbms_output.put_line(systimestamp - v_timer);
end;
/
/
now we run the first test (CACHE NOORDER):
SESSION 1 SESSION 2
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
+000000000 00:00:07.309916000 +000000000 00:00:07.966913000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
+000000000 00:00:08.430094000 +000000000 00:00:07.341760000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
so 7-8 seconds to select 100,000 iterations of the sequence.
Now lets try NOCACHE (ORDER vs NOORDER is irrelavant for this, as we are forcing a write to seq$ for every call to the sequence).
SQL> alter sequence daz_test nocache;
Sequence altered.
SESSION 1 SESSION 2
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
+000000000 00:08:20.040064000 +000000000 00:08:15.227200000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
+000000000 00:08:30.140277000 +000000000 00:08:35.063616000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
so we've jumped from 8 seconds to 8 MINUTES for the same work set.
what about CACHE + ORDER?
SQL> alter sequence daz_test cache 100 order;
Sequence altered.
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
+000000000 00:00:25.549392000 +000000000 00:00:26.157107000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
+000000000 00:00:26.057346000 +000000000 00:00:25.919005000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
so in summary for 100,000 single call fetches
CACHE NOORDER = 8 seconds
NOCACHE = 8 minutes
CACHE ORDER = 25 seconds
for cache order, oracle does do a lot of pinging between the RAC nodes , but it DOESNT have to write stuff back to seq$ until the cache size is used up, as its all done in memory.
i would if i were you, set an appropriate cache size (p.s. a high cache size doesn't put a load on the box memory, as oracle doesn't store all the numbers in RAM; only the current + final number) and consider ORDER if required.
issue in Number generation in Sequence using Cache
From the documentation:
ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
You have specified noorder so sequences numbers are not guaranteed to be generated in the order of the request. From the behaviour you are seeing, and assuming your assertion that the sequence hasn't been modified is true, you seem to be using RAC and are seeing the effect of the way caching is implemented across RAC nodes.
And from the Real Application Clusters Administration and Deployment Guide:
If you use sequence numbers, then always use CACHE with the NOORDER option for optimal performance in sequence number generation. With the CACHE option, however, you may have gaps in the sequence numbers. If your environment cannot tolerate sequence number gaps, then use the NOCACHE option or consider pre-generating the sequence numbers. If your application requires sequence number ordering but can tolerate gaps, then use CACHE and ORDER to cache and order sequence numbers in Oracle RAC. If your application requires ordered sequence numbers without gaps, then use NOCACHE and ORDER. The NOCACHE and ORDER combination has the most negative effect on performance compared to other caching and ordering combinations.
If you are trying to use the sequence to show the order that rows were created then you could use order but that will slow things down. It might be more reliable to use a timestamp column, though that is limited by the precision supported by your operating system, and might not be unique.
Oracle Sequence nextval is jumping number back and forth
I will wager that your database is running RAC (Real Application Clusters). Assuming that is the case and that you create the sequence with all the default settings, that's the expected behavior.
The default setting is to cache 20 values. Each node in the RAC cluster, by default, will have a separate cache. Assuming that you have a cluster with two nodes A and B, the first time a nextval is requested on A, A will cache values 1-20 and return a value of 1. If the next request for a nextval is made on B, B will cache values 21-40 and return a value of 21. From there, the value you get will depend on the node that your connection happens to be running on.
Generally, this shouldn't be a problem. Sequences generate unique numbers. The numbers generally need not be consecutive. If you really need values to be returned sequentially because you are doing something like ordering by the sequence-generated value to determine the "first" or "last" row, you can use the ORDER clause when you create the sequence to force values to be returned in order. That has a negative performance implication in a RAC database, however, because it increases the amount of communication that needs to go on between the nodes to synchronize the values being returned. If you need to determine the "first" or "last" row, it's generally better to add a date or a timestamp column to the table and order by that rather than assuming that the primary key is generated sequentially.
Related Topics
Ora-01652: Unable to Extend Temp Segment by 128 in Tablespace System: How to Extend
SQL Like Condition to Check for Integer
How to Insert Arabic Characters into SQL Database
Regular Expression to Match All Comments in a T-SQL Script
Retrieve Column Names and Types of a Stored Procedure
Sql: Alias Column Name for Use in Case Statement
Show All Rows That Have Certain Columns Duplicated
Bigquery SQL for 28-Day Sliding Window Aggregate (Without Writing 28 Lines of SQL)
Performance of Like '%Query%' VS Full Text Search Contains Query
How to Drop a Default Value or Similar Constraint in T-Sql
Union the Results of Multiple Stored Procedures
SQL Server Cumulative Sum by Group
Redundant Data in Update Statements
Getting Max Value from Rows and Joining to Another Table