RE: what could cause my oracle sequence nextval jump?
Date: Fri, 3 Oct 2008 12:48:30 -0400
Message-ID: <667C10D184B2674A82068E06A78382B5280BE4DD@AAPQMAILBX01V.proque.st>
Hi Guang,
First off, keep in mind that sequences do not guarantee a gap-free stream of numbers, so, if you were to ask Oracle, they'd tell you it's not a bug....and they'd be right.
However, I can understand how it may be annoying or perplexing, so, let's consider what may cause it. You've already ruled out instance crash, shared pool flush, or script or code that changes it. In addition, it's possible that, under shared memory pressure, a particular sequence is aged out of the cache. If that happens, the cached numbers are lost. Also, I see that all your _cache_size parameters are set to 0. This implies you're using SGA_TARGET, and letting Oracle manage the size of the buffer cache, shared pool, etc. It's possible that, overnight (for whatever reason) there's pressure on the buffer cache, which causes a shrink of the shared pool, and forces the three sequences to age out. The next morning, you run the load, they get read in again, and the previously cached values are lost. This is all hypothetical, of course, but gives you some probable scenarios.
Bottom line, you don't have a problem, sequences w/ gaps are a fact of life, and not a bug.
Hope that helps,
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak_at_proquest.com
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei
Sent: Friday, October 03, 2008 11:27 AM
To: oracle-l-freelists
Subject: what could cause my oracle sequence nextval jump?
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 SYS03-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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 03 2008 - 11:48:30 CDT