Re: Sequence Skipping
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: "Lange, Kevin G" <kevin.lange@xxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 13 Jun 2012 21:43:44 +0000
Gotcha on the explanation for "hitting boundaries early", but the consequences
of NOCACHE are otherwise quite dire, with excessive enqueuing on that recursive
UPDATE SEQ$ statement resulting in serious performance problems. So, using
NOCACHE for the situation you cite is clearly a case where the cure hurts as
badly or worse than the problem. Better to resolve the instance crashes which
cause lost sequence values.
Sequences are inappropriate where functional requirements prohibit "gaps" or
skipping in sequence values (i.e. check numbers), because if a transaction
fails and is rolled back, the sequence value obtained is not rolled back for
reuse. Sequences violate the atomicity part of the transactional ACID
properties, regardless of the setting of CACHE/NOCACHE.
-----Original Message-----
From: Lange, Kevin G [mailto:kevin.lange@xxxxxxxxxx]
Sent: Wednesday, June 13, 2012 02:54 PM
To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Subject: RE: Sequence Skipping
I was only saying that , there is a likely chance that , at some point, you
will lose some values from your sequence if you have caching turned on. So, if
your application is designed in such a way as to have a limited range for the
sequence, then the use of cacheing could cause the sequence to hit the upper
boundry prematurely. Not saying its the best design for the sequence to have an
upper value like that, but sometimes you have no choice.
I only mention this because I have seen it happen under just those
circumstances.
------------------------------------------------------------
From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Wednesday, June 13, 2012 3:38 PM
To: Lange, Kevin G; oracle-l@xxxxxxxxxxxxx
Subject: Re: Sequence Skipping
I don't understand the implied relationship between the sequence value returned
and caching. Could you explain?
And why would it be useful to use NOCACHE (except for diagnostics), even for
infrequently-used sequences? What would be the advantage?
-----Original Message-----
From: Lange, Kevin G [mailto:kevin.lange@xxxxxxxxxx]
Sent: Wednesday, June 13, 2012 02:31 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Sequence Skipping
If the sequence is used to set the key in lookup tables that have a max size on
said field, then nocache would be appropriate. Otherwise, dropping x amount of
sequence values could blow out the max that is set prematurely. Basically, any
infrequantly used system generated key field is a candidate for nocache. Of
course, that's only my opinion. Kevin -----Original Message----- From:
oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Tim Gorman Sent: Wednesday, June 13, 2012 3:26 PM To:
Scott.Graves@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: Re: Sequence Skipping
There is absolutely no evidence that the sequence is being "flushed from the
SGA", but rather more likely that the WHEN NOT MATCHED condition of the MERGE
statement (i.e. when the sequence NEXTVAL is referenced) is possibly being
accessed unnecessarly during the WHEN MATCHED condition. Setting NOCACHE on a
heavily-accessed sequence as a perceived "solution" is the classic situation of
the "cure" causing more pain than the "problem". However, *temporarily* setting
the sequence to NOCACHE might be a useful diagnostic technique, as a SQL trace
of the MERGE statement would then clearly indicate whether the sequence is
being accessed inappropriately during WHEN MATCHED, as the recursive UPDATE
SEQ$ statement should be visible and countable. But setting a sequence to
NOCACHE is almost always due to a typo during CREATE/ALTER SEQUENCE or a
misunderstanding of when to use/when not to use sequences. The only appropriate
use of NOCACHE is diagnostic, such as described just above. I'd be curious to
know if anyone has any other valid use of NOCACHE or even under-caching a
sequence? -----Original Message----- From: Scott Graves
[mailto:Scott.Graves@xxxxxxxxx] Sent: Wednesday, June 13, 2012 01:51 PM To:
oracle-l@xxxxxxxxxxxxx Subject: RE: Sequence Skipping When the sequence is
flushed from the SGA, the unused numbers in the cache are burnt. Set your
sequences to nocache and that behavior goes away.-----Original
Message-----From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kenneth NaimSent:
Wednesday, June 13, 2012 2:32 PMTo: oracle-l@xxxxxxxxxxxxxxxxxxxx: Sequence
SkippingI use the following two merge statements to load data into a dimension
table nightly. Every night the process runs the sequence (seq_procedure_key)
gets incremented by approximetly 57,000 values even though only 4-37 rows are
added. There are about 57,000 in the procedures table, Millions in the
medical_bill_detail tables and 17k in the cpt table. It seems one of the two
merge statements is wasting 1 sequence value for every row that does not get
inserted. I've tried searching metalink for bugs and so far have come up empty.
I am experiencing this in 10.2.0.3 prod environment and an 11.2.0.3 test
environment. If this is ex pected behavior, any known workarounds. I don't mind
losing some values but 57k per day is too many. The load failed as the column
related to the sequence was defined as number(8) and the sequence was into the
9th digit. BTW the cache on the sequence is 50.merge into procedures a using
(select nvl(replace(cpt_code,' '),'None') cpt_code, cpt_descr from cpt where
cpt_code is not null) b on (a.cpt_code=b.cpt_code) when matched then update set
a.procedure_name=b.cpt_descr where
nvl(procedure_name,'~')<>nvl(b.cpt_descr,'~') and cpt_descr is not null when
not matched then insert (procedure_key, cpt_code, procedure_name) values
(seq_procedure_key.nextval, b.cpt_code, b.cpt_descr);
v_number_of_records:=sql%rowcount; pkg_etl.p_end_process(v_process_id,
v_number_of_records, null, false); v_process_type_id: 9;
pkg_etl.p_start_process(v_process_type_id, load_id_in, null, null, null,
v_process_id); merge into procedures a using (select distinct
replace(cpt_code,' ') cpt_code, null cpt_de scr from
wcis_dba.medical_bill_detail where cpt_code is not null) b on
(a.cpt_code=b.cpt_code) when not matched then insert (procedure_key, cpt_code,
procedure_name) values (seq_procedure_key.nextval, b.cpt_code,
b.cpt_descr);Thanks,Ken -- //www.freelists.org/webpage/oracle-l This
e-mail, including attachments, may include confidential and/or proprietary
information, and may be used only by the person or entity to which it is
addressed. If the reader of this e-mail is not the intended recipient or his or
her authorized agent, the reader is hereby notified that any dissemination,
distribution or copying of this e-mail is prohibited. If you have received this
e-mail in error, please notify the sender by replying to this message and
delete this e-mail immediately. -- //www.freelists.org/webpage/oracle-l
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
