FAQs:
1) What will be best DDL of sequence in RAC database and what will be impact on performance?
Ans:
You are basing conclusions on wrong assumptions. Documentation states: "A sequence is a schema object that can generate unique sequential values". I wish it would explain it in more detail since many non-math oriented (no offense to anyone) people get confused: "A sequence is a schema object that can generate unique sequential but not necessarily consecutive values". All you can do is change sequence attribute NOORDER to ORDER (keep in mind ORDER/NOORDER is only meaningful if you have RAC - which you do). However expect performance degradation - sequence ordering comes at a price. But even then you will get gaps - transaction can roll back and sequence value is lost or you shutdown database and unused portion of cached sequence values is lost (you can avoid that by changing sequence to NOCACHE but it will again come at a cost of performace degradation).
2) We migrated our database to 3 node RAC cluster and the application will generate sequences on each instance. What are the most common ways to reduce contention on busy RAC database where there is contention for sequence generation?
Ans:
Sequences in Oracle are the same as a single instance, and the RAC internals will manage the uniqueness of the sequence. First, see these Oracle best practices for Oracle RAC sequence usage.
The "contention that we see on busy RAC systems (with high insert DML are because each application of each node is competing for resources that exist on the database. Fortunately, Oracle has several tools for relieving Oracle RAC sequence contention:
a) Use the sequence “cache” option: Caching sequences is especially important for high-DML applications with lots on insert and update activity. You can easily cache as sequence with the "add/alter sequence xxx cache" command. The "cache" clause caches the specified number of sequence values into the buffers in the SGA, speeding-up sequence access speed.
b) Increase sequence index freelist groups (or use ASSM):
Note: But beware that ASSM performance may degrade under "massive" insert DML. (over 100 inserts per second)
c) Use sequence staggering: A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number.
3) What will be Proper Sequence Usage in RAC database?
Ans:
If sequence DDLs are not proper, then sequences can be a major headache in RAC. Non-cached sequences can be the cause of major performance issues on RAC. Another major performance issue can occur if the cached sequence’s cache_value is set too low. Tom Kyte wrote on his website, asktom.oracle.com, the following about proper sequence usage.
Note, however, that cache values are lost during shutdown. Generally speaking, sequences should be either CACHED or ORDERED, but not both. The preferred sequence is a CACHED, non-ordered sequence. If the ordering of sequences is forced, performance in a RAC environment will suffer unless ordering the sequence to a single node in the RAC
cluster isolates insert activity.
Oracle 11g RAC Sequence Example:
Create the sequence.
SQL> create sequence seq_rac_test cache 50;
Sequence created.
Select the sequence from node 1.
SQL> select seq_rac_test.nextval from dual;
NEXTVAL
----------
1
Again.
SQL> /
NEXTVAL
----------
2
Again.
SQL> /
NEXTVAL
----------
3
Again.
SQL> /
NEXTVAL
----------
4
Now select the sequence from node 2.
SQL> select seq_rac_test.nextval from dual;
NEXTVAL
----------
51
Again.
SQL> /
NEXTVAL
----------
52
Again.
SQL> /
NEXTVAL
----------
53
Again.
SQL> /
NEXTVAL
----------
54
Select again from node 1 when NEXTVAL is near the cache maximum of 50.
SQL> /
NEXTVAL
----------
48
Again.
SQL> /
NEXTVAL
----------
49
Again.
SQL> /
NEXTVAL
----------
50
Again.
SQL> /
NEXTVAL
----------
101
As can be seen, since node 2 has already cached values 51-100, it is logical that node 1 will then cache 101-150. Using the order clause when creating the sequence will guarantee sequence order across all RAC instances, but will likely cause performance problems.
Another method to optimize the use of sequences is to use a staggered sequence insert trigger. A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number. An example of a staggered sequence insert trigger is shown in the following script:
CREATE TRIGGER insert_EMP_PK
BEFORE insert ON EMP
FOR EACH ROW
DECLARE
INST_ID NUMBER;
SEQ_NUM NUMBER;
INST_SEQ_ID NUMBER;
BEGIN
select
INSTANCE_NUMBER INTO INST_ID
FROM
V$INSTANCE;
select
EMP_ID_SEQ.NEXTVAL INTO SEQ_NUM
FROM
DUAL;
INST_SEQ_ID:=(INST_ID-1)*100000 + SEQ_NUM;
:NEW.EMP_ID:=INST_SEQ_ID;
END;
/
A staggered sequence trigger will insert the values into indexes such that each instance’s values are staggered to prevent index node intra-node transfers. The formula to allow this is:
index key = (instance_number -1)* 100000+ Sequence number
One of the best ways to determine if sequences are a bottleneck on the system is to simply run the following query.
SELECT LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = ‘X’;
The above query will show the last sequence number that has been written to disk. A safe rule to follow is to ensure the LAST_NUMBER value changes only a few times per day. If the LAST_NUMBER is changing constantly, make sure the sequence is cached. If the sequence is cached, keep increasing the cache value until the LAST_NUMBER stabilizes.
In some applications, the sequence numbers used must be sequential. An example would be the line numbers for a purchase order or perhaps check numbers for an automatic check printer. In this case, a sequence table may have to be used to store the highest sequence number. The value is read from the sequence table, increased by one, and then
updated. While all of this occurs, the row for the sequence being used is locked, thus no one else can use it. If this type of logic must be used, the table should be placed in a tablespace with a small 2048 block size.
Key Note:
Great care must be taken to select the fastest interface and network components to get optimal performance from the cluster interconnect.
Designing for true high availability starts with redundant hardware. If there are multiple single-points of failure, the finest RAC implementation in the known universe will do little to achieve high availability.
4) How I ll overcome sequence issues in RAC database?
The old 80/20 rule applies here; 80% or more of the overhead results from 20% or less of the workload. If the 20% is fixed by observing some simple guidelines, tangible benefits can be achieved with minimal effort. Workload problems can be corrected by implementing any or all of the following:
a) Use the sequence “cache” option: Oracle has a method for caching frequently reference sequences, and you can also cache sequences with n-way Streams replication for fast access to sequence values. Caching sequences is especially important for high-DML applications with lots on insert and update activity. You can easily cache as sequence with the "add/alter sequence xxx cache" command. The "cache" clause caches the specified number of sequence values into the buffers in the SGA, speeding-up sequence access speed.
b) Use the noorder sequence clause: 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.
c) Increase sequence index freelist groups (or use ASSM): Indexes with key values generated by sequences tend to be subject to leaf block contention when the insert rate is high. One remedy is to alter the index to use additional freelists groups. You can often achieve the same relief from index block contention by using ASSM or
using ht sequence “cache” option”. If possible, reduce concurrent changes to index blocks. However, if index key values are not modified by multiple instances, or if the modification rate is not excessive, the overhead may be acceptable. In extreme cases, techniques like physical table partitioning can be applied.
d) Use sequence staggering: A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number.
5) How does RAC synchronize sequences?
In Oracle 10g/11g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.
This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequences next value.
The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldn’t be more obscure. That view shows overall statistics for the session.
However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1” parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.
In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency.
Test Case:
This is a RAC database and the sequence was created with the default values.
Not only the sequences cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated.
Example of “noorder” sequence in 10g RAC:
Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104
The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesn’t make “ordered” the default for sequences.
So I explained to the developer how sequences work in RAC and how each node has its own “cache”.
We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.
How does that change our example?
Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106
(more selects)
Session 1 on node-A: nextval -> 998
Session 1 on node-B: nextval -> 999 (DFS Lock handle)
Session 1 on node-B: nextval -> 1000 (CR read)
The cache size also has some RAC synchronization implications. When the cached entries for the sequence are exhausted, the sequence object needs to be updated. This usually causes a remote CR (current read) over the interconnect for the block that has the specific sequence object. So a bit more activity here.
create sequence test_rac;
declare
dummy number;
begin
for i in 1..50000 loop
select test_rac.nextval into dummy from dual;
end loop;
end;
/
Results:
50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time ordered = 30 seconds
50 000 loops with cache = 1000
1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time ordered = 20 seconds
With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes
conclusion:
By default sequences in 10g RAC are created without ordering. Beware of using applications that rely on sequences to be ordered and using it in a RAC environment.
Consider changing all user sequences to “ordered” as a precaution and increasing the cache size.
The default cache value is still very low and even not-ordered sequences will cause contention in a highly-active sequence even in non-RAC and causing an additional block exchange every 20 values in RAC.
For high volume insert operations where ordering is not performed on the value returned from the sequence, consider leaving the sequence “noordered” but increasing the cache size significantly.
Either way, the sequence parameters should be reviewed, as chances are, the defaults are not what you need.
I remember reading somewhere that in Oracle 9i the “ordered” clause in RAC was equivalent to “nochache”. I can’t imagine how bad that would be in concurrent selects from the same sequence.
It would be interesting if someone running 9i RAC performs the test case and I would appreciate if you post the results in the comments.
1) What will be best DDL of sequence in RAC database and what will be impact on performance?
Ans:
You are basing conclusions on wrong assumptions. Documentation states: "A sequence is a schema object that can generate unique sequential values". I wish it would explain it in more detail since many non-math oriented (no offense to anyone) people get confused: "A sequence is a schema object that can generate unique sequential but not necessarily consecutive values". All you can do is change sequence attribute NOORDER to ORDER (keep in mind ORDER/NOORDER is only meaningful if you have RAC - which you do). However expect performance degradation - sequence ordering comes at a price. But even then you will get gaps - transaction can roll back and sequence value is lost or you shutdown database and unused portion of cached sequence values is lost (you can avoid that by changing sequence to NOCACHE but it will again come at a cost of performace degradation).
2) We migrated our database to 3 node RAC cluster and the application will generate sequences on each instance. What are the most common ways to reduce contention on busy RAC database where there is contention for sequence generation?
Ans:
Sequences in Oracle are the same as a single instance, and the RAC internals will manage the uniqueness of the sequence. First, see these Oracle best practices for Oracle RAC sequence usage.
The "contention that we see on busy RAC systems (with high insert DML are because each application of each node is competing for resources that exist on the database. Fortunately, Oracle has several tools for relieving Oracle RAC sequence contention:
a) Use the sequence “cache” option: Caching sequences is especially important for high-DML applications with lots on insert and update activity. You can easily cache as sequence with the "add/alter sequence xxx cache" command. The "cache" clause caches the specified number of sequence values into the buffers in the SGA, speeding-up sequence access speed.
b) Increase sequence index freelist groups (or use ASSM):
Note: But beware that ASSM performance may degrade under "massive" insert DML. (over 100 inserts per second)
c) Use sequence staggering: A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number.
3) What will be Proper Sequence Usage in RAC database?
Ans:
If sequence DDLs are not proper, then sequences can be a major headache in RAC. Non-cached sequences can be the cause of major performance issues on RAC. Another major performance issue can occur if the cached sequence’s cache_value is set too low. Tom Kyte wrote on his website, asktom.oracle.com, the following about proper sequence usage.
Note, however, that cache values are lost during shutdown. Generally speaking, sequences should be either CACHED or ORDERED, but not both. The preferred sequence is a CACHED, non-ordered sequence. If the ordering of sequences is forced, performance in a RAC environment will suffer unless ordering the sequence to a single node in the RAC
cluster isolates insert activity.
Oracle 11g RAC Sequence Example:
Create the sequence.
SQL> create sequence seq_rac_test cache 50;
Sequence created.
Select the sequence from node 1.
SQL> select seq_rac_test.nextval from dual;
NEXTVAL
----------
1
Again.
SQL> /
NEXTVAL
----------
2
Again.
SQL> /
NEXTVAL
----------
3
Again.
SQL> /
NEXTVAL
----------
4
Now select the sequence from node 2.
SQL> select seq_rac_test.nextval from dual;
NEXTVAL
----------
51
Again.
SQL> /
NEXTVAL
----------
52
Again.
SQL> /
NEXTVAL
----------
53
Again.
SQL> /
NEXTVAL
----------
54
Select again from node 1 when NEXTVAL is near the cache maximum of 50.
SQL> /
NEXTVAL
----------
48
Again.
SQL> /
NEXTVAL
----------
49
Again.
SQL> /
NEXTVAL
----------
50
Again.
SQL> /
NEXTVAL
----------
101
As can be seen, since node 2 has already cached values 51-100, it is logical that node 1 will then cache 101-150. Using the order clause when creating the sequence will guarantee sequence order across all RAC instances, but will likely cause performance problems.
Another method to optimize the use of sequences is to use a staggered sequence insert trigger. A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number. An example of a staggered sequence insert trigger is shown in the following script:
CREATE TRIGGER insert_EMP_PK
BEFORE insert ON EMP
FOR EACH ROW
DECLARE
INST_ID NUMBER;
SEQ_NUM NUMBER;
INST_SEQ_ID NUMBER;
BEGIN
select
INSTANCE_NUMBER INTO INST_ID
FROM
V$INSTANCE;
select
EMP_ID_SEQ.NEXTVAL INTO SEQ_NUM
FROM
DUAL;
INST_SEQ_ID:=(INST_ID-1)*100000 + SEQ_NUM;
:NEW.EMP_ID:=INST_SEQ_ID;
END;
/
A staggered sequence trigger will insert the values into indexes such that each instance’s values are staggered to prevent index node intra-node transfers. The formula to allow this is:
index key = (instance_number -1)* 100000+ Sequence number
One of the best ways to determine if sequences are a bottleneck on the system is to simply run the following query.
SELECT LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = ‘X’;
The above query will show the last sequence number that has been written to disk. A safe rule to follow is to ensure the LAST_NUMBER value changes only a few times per day. If the LAST_NUMBER is changing constantly, make sure the sequence is cached. If the sequence is cached, keep increasing the cache value until the LAST_NUMBER stabilizes.
In some applications, the sequence numbers used must be sequential. An example would be the line numbers for a purchase order or perhaps check numbers for an automatic check printer. In this case, a sequence table may have to be used to store the highest sequence number. The value is read from the sequence table, increased by one, and then
updated. While all of this occurs, the row for the sequence being used is locked, thus no one else can use it. If this type of logic must be used, the table should be placed in a tablespace with a small 2048 block size.
Key Note:
Great care must be taken to select the fastest interface and network components to get optimal performance from the cluster interconnect.
Designing for true high availability starts with redundant hardware. If there are multiple single-points of failure, the finest RAC implementation in the known universe will do little to achieve high availability.
4) How I ll overcome sequence issues in RAC database?
The old 80/20 rule applies here; 80% or more of the overhead results from 20% or less of the workload. If the 20% is fixed by observing some simple guidelines, tangible benefits can be achieved with minimal effort. Workload problems can be corrected by implementing any or all of the following:
a) Use the sequence “cache” option: Oracle has a method for caching frequently reference sequences, and you can also cache sequences with n-way Streams replication for fast access to sequence values. Caching sequences is especially important for high-DML applications with lots on insert and update activity. You can easily cache as sequence with the "add/alter sequence xxx cache" command. The "cache" clause caches the specified number of sequence values into the buffers in the SGA, speeding-up sequence access speed.
b) Use the noorder sequence clause: 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.
c) Increase sequence index freelist groups (or use ASSM): Indexes with key values generated by sequences tend to be subject to leaf block contention when the insert rate is high. One remedy is to alter the index to use additional freelists groups. You can often achieve the same relief from index block contention by using ASSM or
using ht sequence “cache” option”. If possible, reduce concurrent changes to index blocks. However, if index key values are not modified by multiple instances, or if the modification rate is not excessive, the overhead may be acceptable. In extreme cases, techniques like physical table partitioning can be applied.
d) Use sequence staggering: A staggered sequence insert trigger is a specific constant added to the sequence value based on the instance number. This isolates each set of inserts and prevents inadvertent attempts to use the same sequence number.
5) How does RAC synchronize sequences?
In Oracle 10g/11g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.
This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequences next value.
The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldn’t be more obscure. That view shows overall statistics for the session.
However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1” parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.
In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency.
Test Case:
This is a RAC database and the sequence was created with the default values.
Not only the sequences cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated.
Example of “noorder” sequence in 10g RAC:
Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104
The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesn’t make “ordered” the default for sequences.
So I explained to the developer how sequences work in RAC and how each node has its own “cache”.
We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.
How does that change our example?
Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106
(more selects)
Session 1 on node-A: nextval -> 998
Session 1 on node-B: nextval -> 999 (DFS Lock handle)
Session 1 on node-B: nextval -> 1000 (CR read)
The cache size also has some RAC synchronization implications. When the cached entries for the sequence are exhausted, the sequence object needs to be updated. This usually causes a remote CR (current read) over the interconnect for the block that has the specific sequence object. So a bit more activity here.
create sequence test_rac;
declare
dummy number;
begin
for i in 1..50000 loop
select test_rac.nextval into dummy from dual;
end loop;
end;
/
Results:
50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time ordered = 30 seconds
50 000 loops with cache = 1000
1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time ordered = 20 seconds
With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes
conclusion:
By default sequences in 10g RAC are created without ordering. Beware of using applications that rely on sequences to be ordered and using it in a RAC environment.
Consider changing all user sequences to “ordered” as a precaution and increasing the cache size.
The default cache value is still very low and even not-ordered sequences will cause contention in a highly-active sequence even in non-RAC and causing an additional block exchange every 20 values in RAC.
For high volume insert operations where ordering is not performed on the value returned from the sequence, consider leaving the sequence “noordered” but increasing the cache size significantly.
Either way, the sequence parameters should be reviewed, as chances are, the defaults are not what you need.
I remember reading somewhere that in Oracle 9i the “ordered” clause in RAC was equivalent to “nochache”. I can’t imagine how bad that would be in concurrent selects from the same sequence.
It would be interesting if someone running 9i RAC performs the test case and I would appreciate if you post the results in the comments.
nice post.
ReplyDeletemulesoft training
linux training
This comment has been removed by a blog administrator.
ReplyDelete