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.
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
