what could cause my oracle sequence nextval jump?

From: Guang Mei <guangmei_at_yahoo.com>
Date: Fri, 3 Oct 2008 08:26:47 -0700 (PDT)
Message-ID: <569634.78339.qm@web39503.mail.mud.yahoo.com>

ESAVE_at_herbie-SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

I have a situation that I can not explain. I have serveral sequences that I set chche size to 100 (to speed up insert).However each day in the morning I found the seq.nextval jumps from the previous night's seq.currval, leaving gap in between. It happens every night and on all three sequences (which means this gap is not due to application ligic, becuase sequence is used in dirrent code). I did several tests and this does not happen during the day, no matter how many sql sessions are calling the sequence. There is no db bounce overnight, nor SGA flush by any script that I know. I am wondering if anyone has seen this before, and what could cause it.

I looked at cache size of the instance, here they are:

ESAVE_at_herbie-SQL> show parameter cache

NAME                                 TYPE        VALUE


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

db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400

NAME                                 TYPE        VALUE


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

session_cached_cursors               integer     20

• There is only one job:

ESAVE_at_herbie-SQL> select job,log_user,priv_user,this_date,next_date,what from dba_jobs

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------

THIS_DATE NEXT_DATE
--------- ---------
WHAT

---

   1269922 SYS                            SYS

03-OCT-08 03-OCT-08
next_date := sys.dbms_aqadm.aq$_propaq(job);

• Here is the query that shows the sequnece gap (seq is used as ID value, here sysdateid is similar to sysdate)

ESAVE_at_herbie-SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,CACHE_SIZE from dba_sequences where CACHE_ SIZE>=99 and SEQUENCE_OWNER='ESAVE';  

SEQUENCE_OWNER                 SEQUENCE_NAME                  CACHE_SIZE


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

ESAVE                          MEMBERS_SEQ                           100
ESAVE                          NONMEMBERS_SEQ                        100
ESAVE                          VISITOR_SEQ                           100

 

ESAVE_at_herbie-SQL> select min(nonmemberid) from nonmembers where ADDEDDATE_id=sysdateid;  

MIN(NONMEMBERID)

---

           78909  

ESAVE_at_herbie-SQL> select max(nonmemberid) from nonmembers where ADDEDDATE_id=sysdateid-1;  

MAX(NONMEMBERID)

---

           78818  

ESAVE_at_herbie-SQL> select min(memberid) from members where ADDEDDATE_id=sysdateid;  

MIN(MEMBERID)

---

    259251884  

ESAVE_at_herbie-SQL> select max(memberid) from members where ADDEDDATE_id=sysdateid-1;  

MAX(MEMBERID)

---

    259251780

• However on another oracle 10.2.0.1 instance, I have sequences set the same way and they don't have gaps:

SQL> select SEQUENCE_NAME,CACHE_SIZE from user_sequences where CACHE_SIZE>=99 ;  

SEQUENCE_NAME                  CACHE_SIZE


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

MEMBERS_SEQ                           100
NONMEMBERS_SEQ                        100
 

SQL> select min(memberid) from members where ADDEDDATE_id=sysdateid;  

MIN(MEMBERID)

---

    292699223  

SQL> select max(memberid) from members where ADDEDDATE_id=sysdateid-1;  

MAX(MEMBERID)

---

    292699222  

SQL> select min(nonmemberid) from nonmembers where ADDEDDATE_id=sysdateid;  

MIN(NONMEMBERID)

---

       416103005  

SQL> select max(nonmemberid) from nonmembers where ADDEDDATE_id=sysdateid-1;  

MAX(NONMEMBERID)

---

       416103004
       
       

Any ideas?

Guang       

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 03 2008 - 10:26:47 CDT