RE: Sequence Skipping
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <Scott.Graves@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 13 Jun 2012 16:33:02 -0400
That part I was aware of but the cache is only 50. If I lost 50 per day it's
not an issue. I read a reply on asktom indicating that it is expected
behavior of the statement with the update. Even rows that do not get updated
will waste a sequence value. I've increased my column definition to
number(16) from number(8) which gives me about 48 years to solve this issue
. With that said I am going try to join the two tables so the inner result
set only returns the rows to be updated. Assuming this performs well, it
should mitigate my problem. If not I'll convert it to bulk collect/forall
statement.
Thanks,
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Scott Graves
Sent: Wednesday, June 13, 2012 3:52 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 Naim
Sent: Wednesday, June 13, 2012 2:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Sequence Skipping
I 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 expected 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:=129;
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_descr
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
-----
Checked by AVG - <http://www.avg.com> www.avg.com
Version: 2012.0.2180 / Virus Database: 2433/5065 - Release Date: 06/12/12
--
<//www.freelists.org/webpage/oracle-l>
//www.freelists.org/webpage/oracle-l
--
<//www.freelists.org/webpage/oracle-l>
//www.freelists.org/webpage/oracle-l
-----
Checked by AVG - <http://www.avg.com> www.avg.com
Version: 2012.0.2180 / Virus Database: 2433/5065 - Release Date: 06/12/12
_____
Checked by AVG - www.avg.com
Version: 2012.0.2180 / Virus Database: 2433/5065 - Release Date: 06/12/12
--
//www.freelists.org/webpage/oracle-l
Other related posts:
