Re: Sequence Skipping
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: kevin.lange@xxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 13 Jun 2012 20:38:02 +0000
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 maxsize on
said field, then nocache would be appropriate. Otherwise,dropping x amount of
sequence values could blow out the max that is setprematurely.Basically, any
infrequantly used system generated key field is acandidate 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 GormanSent: Wednesday, June 13, 2012 3:26 PMTo: Scott.Graves@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxxxxxxxxx: Re: Sequence SkippingThere is absolutely no
evidence that the sequence is being "flushed fromthe SGA", but rather more
likely that the WHEN NOT MATCHED condition ofthe MERGE statement (i.e. when the
sequence NEXTVAL is referenced) ispossibly 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 usefuldiagnostic technique, as a SQL trace of the MERGE statement would
thenclearly indicate whether the sequence is being accessed
inappropriatelyduring WHEN MATCHED, as the recursive UPDATE SEQ$ statement
should bevisible and countable.But setting a sequence to NOCACHE is almost
always due to a typo duringCREATE/ALTER SEQUENCE or a misunderstanding of when
to use/when not touse sequences. The only appropriate use of NOCACHE is
diagnostic, suchas described just above. I'd be curious to know if anyone has
any othervalid 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 PMTo: oracle-l@xxxxxxxxxxxxxxxxxxxx: RE: Sequence
SkippingWhen the sequence is flushed from the SGA, the unused numbers in
thecache are burnt. Set your sequences to nocache and that behavior
goesaway.-----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 datainto a dimension table nightly. Every night the process
runs thesequence (seq_procedure_key) gets incremented by approximetly
57,000values even though only 4-37 rows are added. There are about 57,000 inthe
procedures table, Millions in the medical_bill_detail tables and 17kin the cpt
table. It seems one of the two merge statements is wasting 1sequence value for
every row that does not get inserted. I've triedsearching metalink for bugs and
so far have come up empty. I amexperiencing this in 10.2.0.3 prod environment
and an 11.2.0.3 testenvironment. If this is ex pected behavior, any known
workarounds. Idon't mind losing some values but 57k per day is too many. The
loadfailed 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 sequenceis 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 seta.procedure_name=b.cpt_descr
wherenvl(procedure_name,'~')<>nvl(b.cpt_descr,'~') and cpt_descr is not
nullwhen 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
distinctreplace(cpt_code,' ') cpt_code, null cpt_de scr
fromwcis_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-lThis
e-mail, including attachments, may include confidential and/orproprietary
information, and may be used only by the person or entityto which it is
addressed. If the reader of this e-mail is not the intendedrecipient or his or
her authorized agent, the reader is hereby notifiedthat any dissemination,
distribution or copying of this e-mail isprohibited. If you have received this
e-mail in error, please notify thesender by replying to this message and delete
this e-mail immediately.--//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
