Sequence cache and RAC

Sequence cache and RAC

The sequence database object needs special attention for applications running on Oracle RAC. It is common for an Oracle sequence to be a point of contention when the application is deployed on Oracle RAC. Most applications create a sequence similar to the following example.

SQL> create sequence

  2     emp_pk_id_seq

  3     start with 1000

  4     increment by 1

  5     nocache;

Sequence created.

It has become a standard practice for many sequences to have the nocache directive. The main reason for specifying nocache is so that the next sequence value is not lost when the instance abnormally terminates. If both cache and nocache are omitted, the default is to cache 20 sequence values in memory.

In the next example, a sequence is created with the default cache value and the first two values are selected.

SQL> create sequence              

  2     cache_example_sequence

  3     start with 1

  4     increment by 1;

Sequence created.

SQL> select     

  2     cache_example_sequence.nextval

  3  from

  4     dual;

   NEXTVAL

----------

         1

SQL> select     

  2     cache_example_sequence.nextval

  3  from

  4     dual;

   NEXTVAL

----------

         2

The database is abnormally terminated and after startup, the next value of the sequence is selected.

SQL> select     

  2     cache_example_sequence.nextval

  3  from

  4     dual;

   NEXTVAL

----------

        21

It's important to note that a gap now exists in the values generated by this sequence. The sequence has generated values 1, 2, and 21. The first twenty values were in the cache but only the first two were actually used. When the instance abnormally terminated, sequence values 3 through 20 were lost.

For sequences that are used to generate primary key values, the gap in ID values is inconsequential, as most end users won't notice the gap. Sequence gaps are most noticeable for entities like invoices or check numbers. Accountants determine if a check has yet to clear the bank by gaps in the check number sequence. Unless you are dealing with accountants, almost every other occurrence of missing numbers in a sequence is irrelevant. Yet humans tend to like order and gaps in the numbers mean disorder, chaos. It often takes training to educate users that gaps in sequences are truly meaningless. It is easier for the database designer to use nocache so that there will be no missing sequence values than it is to take the time to teach users that sequence gaps are perfectly acceptable. There are other reasons for ensuring no gaps in the sequence numbers, but most are for convenience not factual business requirements. Over time, the database designer simply started using nocache without any second thoughts. For many, nocache is now a simple habit.

The problem with nocache sequences in Oracle RAC lies with the sequence being a global database object. Any node in the cluster can generate the next value. In a database where the sequence is used frequently by many sessions, the sequence can become a bottleneck. While a session is generating the sequence's next value, other sessions must wait, and coordinate through the Cluster Interconnect for access to the resource. In a single-instance database, all of this coordination is done locally, entirely in memory. In an Oracle RAC database, processes on different nodes need to talk to each other through the private network, which can really slow the process down.

To show the effect of nocache on sequences in Oracle RAC, we will have two sessions, each of which will select from the same sequence in a loop, over and over again. The sequence is altered to use nocache.

SQL> alter sequence          

  2     cache_example_sequence

  3     nocache;

Sequence altered.

Next, an anonymous PL/SQL block is executed concurrently in two sessions, one session attached to instance orcl1 and the other session attached to instance orcl2.

SQL> set timing on

SQL> declare

  2     curr_val number;

  3     cnt       number;

  4  begin

  5     cnt := 0;

  6     while cnt <= 100000

  7     loop

  8        select cache_example_sequence.nextval

  9           into curr_val

 10        from

 11           dual;

 12        cnt := cnt + 1;

 13     end loop;

 14  end;

 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:19:59.61

The elapsed runtime of 19:59.61 is from the session on the orcl1 instance. The code executed on the orcl2 instance completed in 20:43.22. Each session required approximately 20 minutes to gather 100,000 sequence values when run concurrently.

Next, the sequence is modified to cache 20 values. The instances are bounced to eliminate any memory caching in the instances from skewing the results.

SQL> alter sequence

  2     cache_example_sequence

  3     cache 20;

Sequence altered.

The same anonymous PL/SQL block is executed concurrently in both instances.

SQL> set timing on

SQL> declare

  2     curr_val number;

  3     cnt       number;

  4  begin

  5     cnt := 0;

  6     while cnt <= 100000

  7     loop

  8        select cache_example_sequence.nextval

  9           into curr_val

 10        from

 11           dual;

 12        cnt := cnt + 1;

 13     end loop;

 14  end;

 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.19

The first session completed in 00:41.19 and the second session completed in 00:49.18. Just by making a simple, small change in the sequence's cache value (setting ?cache 20?), the runtime of this sample program decreased from 20 minutes to less than 1 minute!

To further illustrate the effect of the sequence's cache value, the sequence will be modified with larger cache sizes and the same PL/SQL block will be executed concurrently in two instances. When the cache value was increased to 100, the PL/SQL block completed in 00:10.98 for the first session and 00:11.57 for the second session, now around 10 seconds. When the sequence's cache value was increased to 1000, the PL/SQL block's run times were 00:01.06 and 00:02.62, each less than 3 seconds. The following table summarizes the results.

It should be obvious from these results the effect the sequence's cache value. These results were obtained on a two-node RAC database running on a laptop, definitely not production hardware. Your results will most likely differ depending on the system's configuration. No matter the resources available to the RAC cluster, this simple test can be used to illustrate the effect of the cache values for a sequence.

Some readers may be thinking, ?When am I ever going to code a loop that does nothing more than select the sequence's next value?? This is a fair question. The simple example above is only meant to demonstrate sequence contention. This example had only two concurrent sessions running on two Oracle RAC nodes. In practice, there may be more than 100 concurrent sessions on three or four nodes. Sequence contention can certainly exist and can exhibit performance not that different than this simple example.

While a real application would never code selecting the sequence's next value in a loop like this example, other than as an academic exercise, an application would code a statement similar to the following.

insert into

   destination_table

select

   my_sequence.next_val,

   column

from

   source_table;

In the case of the sample insert statement above, the sequence's next value will be generated for each row of the result set which is not that different from a loop as used in this example. Insert 100,000 rows into a table that gets one column populated from a sequence database object and we have effectively leveraged the sequence similar to our example. Alternatively, there could be a trigger on the destination table that generates the sequence's next value for every row inserted into that table with a similar effect.

Using nocache for a sequence in an Oracle RAC database can lead to poor application performance. It is best to set a high cache value. Most sequences will benefit from a cache value of 100 or higher. Obviously, the more contention for this resource, the higher its cache value should be. It is not out of the ordinary to see a cache value of 1,000 for a sequence in an Oracle RAC database. The following query can be used to determine which sequences have nocache specified.

select

   sequence_owner,

   sequence_name

from

   dba_sequences

where

   cache_size = 0;

Any sequences owned by application users should be checked to ensure the cache value would not cause the sequence to be an application bottleneck. Note that we never attempt to change any sequences that are part of the Oracle Data Dictionary.

Examining the wait events for a session can identify contention for sequences objects. Querying v$session_wait will show all of the events our session waited on. It is often useful to see the top 5 wait events.  The script below shows the top five wait events for one of the sessions involved in the sequence example.

SQL> select

  2     *

  3  from

  4     (select

  2         event,

  3         total_waits,

  4         time_waited

  5      from

  6         v$session_event

  7      where

  8         sid=SYS_CONTEXT('USERENV','SID')

  9      order by

 10         time_waited desc)

 11  where

 12     rownum <= 5;

EVENT                          TOTAL_WAITS TIME_WAITED

------------------------------ ----------- -----------

row cache lock                       94154       40306

gc cr block lost                       393       17826

gc current block lost                  371       17462

gc cr block busy                     37397        6731

gc current block 2-way               93214        5587

Four of the top five wait events for this session all start with ?gc?. The previous chapter reviewed some of these global cache wait events seen above. The remainder of these Cache Fusion waits will be discussed in a later chapter.

The top wait event is the row cache lock event.  So how does this wait event prove the contention is for a sequence database object? To know for sure, the specific parameter values of the row cache lock wait event need to be examined for that session. While the session was running and experiencing contention, this session's specific wait event was captured.

SQL> select

  2     event,

  3     p1,

  4     p2,

  5     p3

  6  from

  7     v$session_wait

  8  where

  9     sid=SYS_CONTEXT('USERENV','SID');

EVENT                             P1        P2       P3

------------------------- ---------- ---------- ----------

row cache lock                    13         0        5

In the output above, the session is experiencing the row cache lock wait event. Notice the P1 value is 13. Examining the v$rowcache view shows the Data Dictionary object type that is involved. For the row cache lock wait event, the P1 value links to the cache# column of the v$rowcache view

SQL> select          

  2     parameter,

  3     gets,

  4     getmisses

  5  from

  6     v$rowcache

  7  where

  8     cache#=13;

PARAMETER                  GETS  GETMISSES

-------------------- ---------- ----------

dc_sequences              64740      64283

The output above shows that the session is experiencing contention for the dc_sequences object type. Also notice that the number of get misses is very high compared to the number of gets. However, the metric shown in this query may have a low get misses ratio compared to total gets and the application can still have sequence contention. The v$rowcache metric shown is for all sequence database objects, even ones that have little or no contention. In the end, the above has shown that our session was experiencing a bottleneck, a wait, on something in the row cache which subsequently confirmed this to be the part of the row cache that deals with sequences.

What has yet to answered is which sequence is involved in the bottleneck. To answer that question, one would have to look at the code being executed for that session. If access to the code is not available, then one can always start a SQL trace in session to determine the sequence involved.

It should be noted that some versions of Oracle RAC that are experiencing contention for sequences may not manifest themselves as the row cache lock wait event shown in this chapter. These examples were run on Oracle 11.2.0.4 and Oracle 12.1.0.1 and obtained similar results. Some versions may show the wait event to be enq: SQ - contention which is a fancy way of indicating sequence (SQ) enqueues (lock) contention.