You should probably be looking at the GV$ROWCACHE row for dc_sequences to measure the performance impact of a hot ORDERed sequence. The activity that you see in GV$LOCK_ACTIVITY relates to PCM instance locks, presumably on the lock element covering the data dictionary block containing the row for TEST_SEQ. Downgrades due to BASTs against PCM instance locks are deferred for 10 centiseconds by default (or the value of gc_defer_time) to reduce the cost of pinging in situations just like this. The fact that you did not see a conversion when using the sequence manually may be due to the fact that the default value for _upconvert_from_ast is TRUE.

Of course, what you are trying to do is horrible for performance, and I would question the ORDER requirement.

I am doing same testing in a Parallel Server Environment having in mind a future billing migration. We are using HPUX 11 with Oracle 8.0.6. One of the issues of major concern for us is about the sequences. We must preserve the order and we change them from CACHE to NOCACHE. We ran a couple of tests regarding ordered, nocache sequences. The tests were using a simple SQL statement:

select test_seq.nextval from dual;

inside a small piece of PL/SQL which ran it 1,000,000 times. Each node in our cluster ran this statement at the same time and in the basic scenario (ie. with no method employed to alleviate pinging) the test ran for 48 minutes. The delta from gv$lock_activity is like 1 lock conversion per 1000 sequence nextvals. I've no idea why there isn't one lock conversion per sequence nextval because both processes were running at the same time. (As a side note I manually ran the SQL statement once on each node and did not observe a lock conversion).

What can be happening? Should we look at other views? Is any event that we can enable to see what's happening?