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: Use cache.
D: The KEEP procedure keeps an object in the shared pool. Once an object has been kept in the shared pool, it is not subject to aging out of the pool. This may be
useful for frequently used large objects. When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area
large enough.
References:
https://ora600tom.wordpress.com/2015/01/09/enq-sq-contention/
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_shpool.htm#i999221
A,C correct
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.
B) The sequence is already “ordered”
D) This is used to prevent to miss sequences when they are agedout and flushed from the cache
E) If you alter the sequence by specifying the KEEP or NOKEEP clause between runtime and failover of a request, then the original value of NEXTVAL is not retained during replay for Application Continuity for that request.
https://docs.oracle.com/database/121/SQLRF/statements_2014.htm#SQLRF00817
A & C is correct!
Answer: A,C
D81250GC11 : Always use CACHE with the NOORDER option for optimal performance in sequence number generation.
Keep_value(user_sequence) – Indicates whether sequence values are kept during replay after a failure (Y) or not (N)
DBMS_SHARED_POOL.KEEP is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.