Post date: 14-Jun-2009 20:34:32
The use of sequences in Oracle Real Application Clusters (Oracle RAC) is much the same as it is for single-instance Oracle databases, but there are certain Oracle RAC-specific issues. With two or more database instances, sequences may require coordination to guarantee that the sequence numbers are allocated in the correct order.
Which of the following are always true regarding the use of sequences in an Oracle 10g RAC database? (Pick two.)
A. It is not possible to use the CACHE option, because each database has a row cache.
B. Sequence numbers may be out of order if multiple instances run the application program that uses the sequence.
C. Sequences add no extra overhead to traffic over the interconnect.
D. Using the CACHE and NOORDER options together results in the best performance for a sequence.
The correct answers are B and D.
Answer B is correct because if the CACHE option is used without the ORDER option, each instance caches a separate range of numbers and sequence numbers may be assigned out of order by the different instances. Answer D is correct because the CACHE option causes each instance to cache its own range of numbers, thus reducing I/O to the Oracle Data Dictionary, and the NOORDER option eliminates message traffic over the interconnect to coordinate the sequential allocation of numbers across all instances of the database.
Answer A is incorrect because even though each instance has a row cache, it is still possible to use the CACHE option with sequences. In such a case, each instance may cache numbers for that sequence in its row cache. Answer C is incorrect because if the CACHE and ORDER options are used together, all instances must allocate numbers in order by coordinating the assignment of the next value, using messages over the interconnect, thereby increasing interconnect traffic in proportion to the frequency of new-number assignment.