what could cause my oracle sequence nextval jump?
- From: Guang Mei <guangmei@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Oct 2008 08:26:47 -0700 (PDT)
ESAVE@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@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@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@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@herbie-SQL> select min(nonmemberid) from nonmembers where
ADDEDDATE_id=sysdateid;
MIN(NONMEMBERID)
----------------
78909
ESAVE@herbie-SQL> select max(nonmemberid) from nonmembers where
ADDEDDATE_id=sysdateid-1;
MAX(NONMEMBERID)
----------------
78818
ESAVE@herbie-SQL> select min(memberid) from members where
ADDEDDATE_id=sysdateid;
MIN(MEMBERID)
-------------
259251884
ESAVE@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
