Bump up sequences

I got a requirement to bump up more than 200 sequences in a database. Initially I developed SQL scripts to bump up based on the difference between the target value and the dba_sequneces.last_number column for each of the sequences. However, I soon found out that the last_number does not necessarily equal to the seq.nextval. This is apparently due to the cache_size option. For example:

TESTDB> create sequence temp_seq;

Sequence created.

TESTDB> select temp_seq.nextval from dual;

   NEXTVAL
----------
         1

TESTDB> select temp_seq.nextval from dual;

   NEXTVAL
----------
         2

TESTDB> select SEQUENCE_NAME, cache_size, last_number from user_sequences;

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TEMP_SEQ                               20          21

I finally decdied to adopt a PL/SQL solution. I first created a helper table (seq_bump), which stores the target values. Then, I created the following PL/SQL procedure:

declare
  cursor c is 
   select seq_owner, seq_name, bump_number 
   from seq_bump;

  stmt varchar2(300);
  l_seq_owner seq_bump.seq_owner%type;
  l_seq_name seq_bump.seq_name%type;
  l_bump_number seq_bump.bump_number%type;
  l_nextval number;
  l_inc   number;
  l_count number :=0;
begin
  open c;
  loop
   -- l_count := l_count + 1;
   --  dbms_output.put_line('**** conunter = ' || l_count );
    fetch c into l_seq_owner, l_seq_name, l_bump_number;
    stmt:= 'select ' || l_seq_owner || '.' || l_seq_name || '.nextval from dual';
    execute immediate stmt into l_nextval;
   --  dbms_output.put_line('next values is ' || l_nextval);
    l_inc := l_bump_number - l_nextval;
    stmt:= 'alter sequence ' || l_seq_owner || '.' || l_seq_name || ' increment by ' || l_inc ;
   --  dbms_output.put_line(stmt);
    execute immediate stmt;
    stmt:= 'select ' || l_seq_owner || '.' || l_seq_name || '.nextval from dual';
  --  dbms_output.put_line(stmt);
    execute immediate stmt into l_nextval;
    stmt:= 'alter sequence ' || l_seq_owner || '.' || l_seq_name || ' increment by 1 ';
    execute immediate stmt;
    exit when c%notfound;
  end loop;
  close c;
end;
/

I tested above procedure and this will be implemented in production this weekend, hopefully it will work as I expect.

Adjust Oracle sequence to use odd and even number respectively on a Production and DR database

Our production databases are replicated to DR databases through SharePlex replcation. Recently, We have a need to enable two-way replication. i.e. in addition to replication from production to DR, we also need DR to prodcution. I was thus assigned a task to adjust the sequences in production to use odd number and sequences in DR to use even number.

The sequneces in production and DR are out-of-sync currently, as we don't replicate sequence from production to DR. That means if on production a sequence's last number is 1000, whereas on DR, the number could be 1.

Testing for a while, I found that when generating the DDL for a sequence through DBMS_METADATA package, the number following the 'start with' clause is the last number of DBA_SEQUENCES view.

For example:

SQL> select sequence_name, last_number from dba_sequences
where sequence_owner=user;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
TEST2_SEQ                              107
TEST3_SEQ                              107
TEST_SEQ                               175


SQL> ;
  1* SELECT DBMS_METADATA.GET_DDL(upper('&OBJTYPE'), upper('&OBJNAME') , upper('&OWNER'))  ddl_string from dual
SQL> /
Enter value for objtype: sequence
Enter value for objname: TEST_SEQ
Enter value for owner: abc

   CREATE SEQUENCE  "ABC"."TEST_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27
 INCREMENT BY 98 START WITH 175 CACHE 20 NO
ORDER  NOCYCLE ;

I thus developed the following plan:

1. On Prod, run script seq_odd.sql to change seq number to odd and increment by to 2
2. On prod, run script seq_ddl_gen.sql to generate sequence ddl script: seq_ddl.sql
3. SCP seq_ddl.sql to DR server
4. On DR, run script seq_drop_gen.sql and then seq_drop.sql to drop sequence
5. On DR, run script seq_ddl.sql generated in step 2 to re-create all sequences
6. On DR, run script seq_incr1_gen.sql, seq_incr1.sql to modify sequence increment by to 1;
7. On DR, run script seq_nextval_gen.sql, seq_nextval.sql to make the currval of sequences to be even number,
8. on DR, run script seq_incr2_gen.sql, seq_incr2.sql to modify sequence increment by to 2;

The above scripts are simple execept for seq_odd.sql, I wrote PL/SQL code for this:

---- seq_odd.sql -----
declare
  seqown varchar2(30);
  seqname varchar2(30);
  sqlstmt varchar2(1000);
  cval  number;
  incr  number;
  cursor  seq_cur is
      select sequence_owner, sequence_name
        from dba_sequences
        where sequence_owner is [some_condition];
begin
  open seq_cur;
  loop
    fetch seq_cur into seqown, seqname;
    exit when seq_cur%notfound;
    sqlstmt := 'select '  seqown  '.'  seqname  '.nextval  from dual';
    execute immediate sqlstmt into cval;

    if ( mod(cval, 2) = 0 )
    then
-- ensure the current val is odd number
-- first change increment by 1
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 1';
       execute immediate sqlstmt;

       sqlstmt := 'select '  seqown  '.'  seqname  '.nextval  from dual';
       execute immediate sqlstmt into cval;
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 2';
       execute immediate sqlstmt;
    else
-- already an odd number
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 2';
       execute immediate sqlstmt;
    end if;
  end loop;
  close seq_cur;

-- follwing code is to verify ----
---- end of seq_odd.sql --------

Recursive SQL statement: update seq$

Last Friday, one of our production database experienced slowness in about 1 hours time period. During that period of time, there were 60+ blocking sessions. One of the blocking session was issuing the following statment:

update seq$ set
         increment$=:2,minvalue=:3,maxvalue=:4,cycle#=
         :5,order$=:6,cache=:7,highwater=:8,audit$=:9,
         flags=:10 where obj#=:1

This looks like having somthing to do with the sequences. Our team leader suggested that we should increase the cache size for two relevant sequences to proactively avoid the problem. I acutally opened a SR with Oracle, asking what this statment is doing. But they did not give direct answer to this specific question so far. On the other hand, the guy suggested we should increase the log file size to reduce "log file sync" wait event, which appears at the top of the wait event in our statspack report. His suggestions is obviously nonsense. He must have no idea about what "log file sync" wait event is. I am disappointed with the support.

To understand this update statment, I did a test today. I created a table T and a sequence T_SEQ:

labadmin@DB10G> desc t;
 Name                      Null?    Type
 ------------------------- -------- ------------------------------------
 ID                                 NUMBER
 VALUE                              VARCHAR2(20)

labadmin@DB10G> select * from user_sequences;

SEQUENCE_NAME          MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------------- ---------- ---------- ------------ - - ---------- -----------
T_SEQ                          1 1.0000E+27            1 N N         20      904821

Then I have 20 sessions repeatedly executing the following insert statment simutaneouly:

isnert into t values(t_seq.nextval, 'xxxx');

I can observe there is one blocking sessions from time to time, for example:

                 Lock                Time Held
   SID Lock Type Requested Lock Held (minutes) Block
------ --------- --------- --------- --------- -----
   126 SQ        None      Exclusive         0 Yes
    99 SQ        Exclusive None              0 No
   124 SQ        Exclusive None              0 No
   123 SQ        Exclusive None              0 No
   128 SQ        Exclusive None              0 No
   158 SQ        Exclusive None              0 No
   109 SQ        Exclusive None              0 No
   159 SQ        Exclusive None              0 No
   130 SQ        Exclusive None              0 No
   114 SQ        Exclusive None              0 No
   131 SQ        Exclusive None              0 No
    ....k

I traced one of the 20 sessions, found that:

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10
where
 obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      561      0.03       0.05          0          0          0           0
Execute    561      2.04       8.81          0        561       1132         561
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1122      2.07       8.87          0        561       1132         561

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  SEQ$ (cr=1 pr=0 pw=0 time=339 us)
      1   INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=40 us)(object id 102)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  latch: undo global data                         2        0.00          0.00
  latch: library cache                            1        0.01          0.01
  log buffer space                                8        1.00          6.39
  latch: library cache pin                        2        0.10          0.10
********************************************************************************

INSERT INTO T
VALUES
 (T_SEQ.NEXTVAL, :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute   9026      8.51     351.24          0        211      21192        9026
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9026      8.51     351.24          0        211      21192        9026

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 102     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: SQ - contention                          775        3.07        258.38
  latch: enqueue hash chains                      6        0.03          0.06
  log buffer space                               38        1.36         21.72
  latch: library cache pin                        3        0.01          0.01
  latch free                                     12        0.40          1.52
  free buffer waits                            1717        0.08         25.67
  latch: library cache                           10        0.15          0.62
  latch: In memory undo latch                     8        1.27          2.56
  latch: redo copy                                6        0.33          0.41
  buffer busy waits                              19        0.98          4.37
  latch: undo global data                         4        0.07          0.09
  enq: FB - contention                            2        0.82          0.98
  enq: TX - contention                            2        0.22          0.22
  enq: HW - contention                            2        0.45          0.45
  latch: session allocation                       1        0.00          0.00
********************************************************************************

So it can be seen that the "update seq$ ..." statement is a recursive sql issued by Oracle sys user to update dictionary table about sequence.

I did a further test:

I set the cache of t_seq to be 20, and I trace the session when executing following statement:

insert into t
select t_seq.nextval, 'qqq' from all_objects where rownum <=100;

I found the 'update seq$' statment executed 5 times.

Then I set the cache of t_seq to be 50, I found the 'update seq$' executed 2 time.

Thus, it looks like that every time that the cached sequence number is used up, Oracle should do the caching again and issue this statment to update dictionary table.