RE: what could cause my oracle sequence nextval jump?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "guangmei@xxxxxxxxx" <guangmei@xxxxxxxxx>, oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Oct 2008 12:48:30 -0400
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@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
