Clarity: Oracle RAC customers only - slow performance and max(id) on tables/nextval on sequences is increasing rapidly.
Article ID: 51157
Updated On:
Products
Issue/Introduction
Description:
DESCRIPTION:
Clarity customers who use Oracle RAC may see slow performances in messages/processes or other areas of Clarity. In addition, they will see the max(id) on affected tables and the nextval of the corresponding sequence increasing very rapidly.
The NMS_MESSAGE_DELIVERY and PRTIMEENTRY tables are the most likely tables to be affected by this problem.
TECHNICAL DETAILS:
This problem will only affect customers who use Clarity with Oracle RAC.
When the "NO ORDER" option is selected for a sequence, Oracle RAC will allocate separate sets of sequences for each server in the cluster.
Server 1 will be assigned 1 - whatever Server 2 will be assigned 1 - whatever . . .
When the sequence numbers on two different servers overlap, oracle will attempt to make adjustments by adding additional sequence numbers to rectify the situation. This can end up taking up lots of overhead in Oracle which accounts for the slow performance seen in Clarity and the high numbers in the sequences.
If "ORDER" is used instead, Oracle allocates sequence numbers to all servers from the same set of sequence numbers.
If Server 1 is allocated 1 - 1000, then Server 2 is allocated 1001 - 2000, ....
Although this choice will create some additional overhead for Oracle, it does not create the very bad performance issues we have seen with some customers.
Solution:
Change the Oracle setting for the affected sequence from the default of "NO ORDER" to "ORDER". You may use the following sql:
ALTER SEQUENCE CMN_SEC_USERS_S1 INCREMENT BY 1 ORDER;
NOTE: Change CMN_SEC_USERS_S1 to the name of the sequence that is growing rapidly.
One way to find out which sequences are affected is to do the following:
- Have your dba find the current val in all sequences and then sort largest to smallest.
- Look for values that are considerably larger than all of the others. In the case of one customer large values were over 50 million.
- Run the above sql for those sequences.
Unfortunately, there is no global setting that will changes this for all sequences at once.
Environment
Component:
Feedback
