RE: Sequence Skipping

From: Scott Graves <Scott.Graves_at_nisc.coop>
Date: Wed, 13 Jun 2012 19:51:40 +0000
Message-ID: <D79FF877634BC24196A0B3588D007FC79204A7D9_at_MOES1.nisc.lan>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kenneth Naim Sent: Wednesday, June 13, 2012 2:32 PM
To: oracle-l_at_freelists.org
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 - www.avg.com
Version: 2012.0.2180 / Virus Database: 2433/5065 - Release Date: 06/12/12

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 13 2012 - 14:51:40 CDT