Examine this query and output:
SQL> select order_flag, cache_size, session_flag, keep_value,
2 from user_sequences where sequence_name = ‘SEQ1’;
O CACHE_SIZE S K
— ——————- — —
Y 10 N N
Performance analysis revealed severe SQ enqueue contention on the SEQ1 sequence.
The SEQ1 sequence is incremented from all instances equally and is frequently used.
Which two statements should you execute to reduce SQ enqueue contention?
A.
alter sequence seq1 cache 10000;
B.
alter sequence seq1 order;
C.
alter sequence seq1 noorder;
D.
exec sys.dbms_shared_pool.keep (‘SEQ1’, ‘Q’)
E.
alter sequence seq1 keep;
Explanation:
A: Increasing sequence caches improves instance affinity to index keys deriving their values from sequences.
That technique may result in significant performance gains for multi-instance insert-intensive applications.
C: When creating sequences for a RAC environment, DBAs should use the noorder keyword to avoid an
additional cause of SQ enqueue contention that is forced ordering of queued sequence values. In RAC, a best
practice is to specify the “noordered” clause for a sequence. With a non-ordered sequence, a global lock not
required by a node whenever you access the sequence.
A,C