Oracle Sequence value are not ordered [duplicate]

Question

This question already has answers here:

Closed 7 years ago.

Possible Duplicate:
Oracle RAC and sequences

I have a Oracle RAC configured in my local environment. I analyzed a problem with Sequnce that the number generated by nextVal are not ordered. Suppose First time I get value as 1 , the second time get get value as 21 (I have configured the sequence as with default CACHE 20 and NOORDER ).

On searching I found the solution that, I need to Order the sequence. I have question which is better option to go with,

1) CACHE and ORDER

2) NOCACHE and ORDER

I want to know which one of the above is better option and why?

Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.

Thanks

Answer 1

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.