Shulou(Shulou.com)06/01 Report--

Discussed with netizens yesterday, the SEQUENCE,awr report in RAC is as follows, with an excerpt of key parts:

In the RAC environment, the Cache problem of the sequence may have a decisive impact on performance, and the default sequence Cache value is 20, which is far from enough for the RAC environment.

If there is competition for serial number use, you may see obvious queue waits in the database:

Enq: SQ-contention

In the case of RAC, the frequently used sequence Cache value can be increased to 10000 or higher to 50000, all of which are used in the customer's environment.

This is the basic common sense of RAC settings and RAC usage, and should not be forgotten.

In the following tests, you can show the impact of the Cache sequence on performance:

RAC two sessions are in different node and simultaneous concurrent cycle is interrupted to take 40,000 values:

Nocache: 2100s

Cache = 1000: 55s

But there's a big difference.

The process of uninterrupted testing of 10,000-40,000 values in a single Instance database single session cycle (the test results on a notebook at home) is as follows:

Nocache: 37.7s 10000

Cache: 20 4.31s 10000

Cache: 100 2.92s 10000

Cache: 1000 5.56s 40000

Nocache: 97.7s 40000

Basically, when the cache is greater than 20, the performance is basically acceptable. It is best to set it above 100.

The performance of nocache is really poor, with a maximum difference of 20 times.

Sorting parameters: oracle defaults to NOORDER. If it is set to ORDER;, it has no effect in single instance environment. In RAC environment, multiple instances actually cache the same sequence. In this case, when multiple instances send the sequence at the same time, there will be a brief competition for resources to synchronize multiple instances. Therefore, the performance is worse than that of noorder, so do not use ORDER if the RAC environment is not necessary, especially avoid NOCACHE ORDER combinations

The existence of BUG in some versions can lead to excessive enq: SQ competition.

If there is a problem that the cache setting of IDGEN$ sequence is too small in Oracle Database 11g, which may lead to serious competition, it is recommended to increase the setting of Cache value of this sequence.

Sequence-related locks in RAC environment

For the consistency of sequence in rac environment, oracle uses three kinds of locks: row cache lock, SQ lock and SV lock.

The purpose of row cache lock is to ensure the sequence sequence in the process of calling sequence.nextval when sequence specifies nocache.

The SQ lock is applied to the process of calling sequence.nextval when cache+noorder is specified.

The SV lock (dfs lock handel) is the lock you have during the call to sequence.nextval. The premise is that the cache and order attributes (cache+order) are specified when the sequence is created. The purpose of the order parameter is to ensure the order in which sequence is generated between nodes on the RAC.

The value of enq:sq-contention assigned by the creation sequence is small, and there is a tendency to wait for it to increase.

The default value of cache is 20. 0. Therefore, when creating a sequence with more concurrent access, the cacheh value should be larger. Otherwise, an enq:sq-contention wait event occurs.

When creating a sequence on rac, if the cache size is specified and the noorder attribute is assigned, each node will cache a different range of sequence values to memory.

Create sequence TX_SEND_SEQ_ACC

Minvalue 1

Maxvalue 999999999999999999999999999

Start with 673560

Increment by 1

Cache 20

RAC1 fetch sequence

SQL > select tx_send_seq_acc.nextval from dual; NEXTVAL- 673560SQL > select tx_send_seq_acc.nextval from dual; NEXTVAL- 673561RAC2 fetch sequence SQL > select tx_send_seq_acc.nextval from dual; NEXTVAL- 673580SQL > select tx_send_seq_acc.nextval from dual; NEXTVAL- 673581

If sequence must be used incrementally between two nodes, the following order attribute must be assigned

In the case of a sequence,oracle that has been assigned the cache+order attribute, the SV lock is used for synchronization. The solution to the SV lock contention problem is the same as in the case of sq locks, which is to adjust the cache value appropriately.

In the RAC multi-node environment, the Cache attribute of Sequence has a great impact on performance. The cache+noorder attribute should be given as much as possible, and sufficient cachevalues should be given. If you need to guarantee the order, you must assign the cache+order attribute. But at this time, in order to ensure the order, the instances need to exchange data constantly. Therefore, the performance is slightly worse.

Sequence inconsistency in oracle RAC environment

Sequences in Oracle 10g RAC Just recently I got a call from a developer. He had a table with a primary key populated by a sequence, a timestamp column with the current date and some other columns. He had a specific set of data that, when ordered by the primary key had out of order timestamps. He was puzzled how this could be. 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-> 101Session 2 on node-A: nextval-> 102Session 1 on node-B: nextval-> 121Session 1 on node-B: nextval-> 122Session 1 on node-A: nextval-> 103Session 1 on node-A: nextval-> 104The 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 doesnt 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 RAC synchronize sequences? In Oracle 10g 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. 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. Test case: 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 The 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 cant 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.

The following is the corporate environment. We can see that for frequently updated tables, the cache is very large, and the default noorder is the default. In rac, cache+noorder

Select 'create sequence' | | Sequence_Name | | 'minvalue' | | Min_Value | | 'maxvalue' | | Max_Value | | 'start with' | | Last_Number | | 'increment by' | | Increment_By | | 'cache' | | Cache_Size | |'; 'From Dba_Sequences;create sequence SEQ_CMS_ACCESSORY minvalue 1 maxvalue 999999999999999 start with 1 increment by 1 cache 10000; create sequence SEQ_CMS_CHANNEL minvalue 1 maxvalue 9999999999999 start with 100606 increment by 1 cache 10000; create sequence SEQ_CMS_IMAGE minvalue 1 maxvalue 99999999999 start with 260430 increment by 1 cache 10000 Create sequence SEQ_CMS_INFO minvalue 1 maxvalue 9999999999999999999 start with 671134 increment by 1 cache 10000; create sequence SEQ_CMS_INFO_CHANNEL_LINK minvalue 1 maxvalue 99999999999999 start with 210007 increment by 1 cache 10000; create sequence SEQ_CMS_INFO_PROP minvalue 1 maxvalue 99999999999999 start with 60001 increment by 1 cache 10000; create sequence SEQ_CMS_INFO_PROP_CONFIG minvalue 1 maxvalue 9999999999999 start with 50003 increment by 1 cache 10000; create sequence SEQ_CMS_INFO_USER_LINK minvalue 1 maxvalue 99999999999 start with 460003 increment by 1 cache 10000 Create sequence SEQ_CMS_LONG_TEXT minvalue 1 maxvalue 9999999999999999999 start with 681286 increment by 1 cache 10000; create sequence SEQ_CMS_TEMPLATE minvalue 1 maxvalue 99999999999999 start with 20187 increment by 1 cache 10000; create sequence SEQ_CMS_TEMPLATE_VIEW_VERSION minvalue 1 maxvalue 999999999999 start with 44 increment by 1 cache 10000; create sequence SEQ_CMS_WEBSITE minvalue 1 maxvalue 9999999999999 start with 40001 increment by 1 cache 10000; create sequence SEQ_CMS_WEBSITE_DOMAIN minvalue 1 maxvalue 99999999999 start with 100 increment by 1 10000 Create sequence SEQ_SCH_DISTRIBUTE_TASK_EXEC_C minvalue 1 maxvalue 999999999999999 start with 181 increment by 1 cache 2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.