Re: How to create pl/sql that run efficiently in background ?
From: gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net>
Date: Tue, 03 Feb 2009 04:29:56 GMT
Message-ID: <8LPhl.927$eK2.17_at_nwrddc01.gnilink.net>
> Are you using RAC? if no, then:
> Nocache means that none of the sequence values are stored in memory. This
> option may sacrifice some performance, however, you should not encounter a
> gap in the assigned sequence values.
> You can use a simple TRIGGER to "apply" the nextval.
> Before the days of sequences (and yes that WAS a long time ago...)- I did
> have to create a one-row/one-column table that I pinned in memory to make
> it run faster.
> Same thing here - keep your transactions VERY short so as not to cause too
> much contention.
> here is the full syntax and examples:
> http://www.psoug.org/reference/sequences.html
>
> Adn then test to make sure you are satisfied with the results.
Date: Tue, 03 Feb 2009 04:29:56 GMT
Message-ID: <8LPhl.927$eK2.17_at_nwrddc01.gnilink.net>
"Michael Austin" <maustin_at_firstdbasource.com> wrote in message
news:owPhl.15932$yr3.8304_at_nlpi068.nbdc.sbc.com...
> krislioe_at_gmail.com wrote:
>> On Feb 3, 10:43 am, Palooka <nob..._at_nowhere.com> wrote: >>> krisl..._at_gmail.com wrote: >>>> Hi sql gurus, >>>> We have application on Oracle 10g where 150+users (from multiple >>>> branch) concurrently input transaction. The transaction number is >>>> centralized, means all user use same transaction number counter. >>>> To avoid the contention that occured, we think of using pl/sql >>>> procedure that run in background to do : >>>> - select transaction that not yet has number >>>> - query the last transaction number counter(a column in a table), >>>> increment by 1, update the transaction with the number >>>> - commit >>>> - process another transaction >>>> What is the best approach to do this ? >>>> (means a pl/sql that does not cause high CPU utilization, because it >>>> will run all day long to look for unnumbered transaction) >>> Ever heard of sequences?- Hide quoted text - >>> >>> - Show quoted text - >> >> Yes, we avoid sequence because we have to meet the classic >> requirement : there should not be any numbering gap. >> >> Thanks >> xtanto >> > >
> Are you using RAC? if no, then:
>
> CREATE SEQUENCE supplier_seq
> MINVALUE 1
> START WITH 1
> INCREMENT BY 1
> NOCACHE;
>
> Nocache means that none of the sequence values are stored in memory. This
> option may sacrifice some performance, however, you should not encounter a
> gap in the assigned sequence values.
>
> You can use a simple TRIGGER to "apply" the nextval.
>
> Before the days of sequences (and yes that WAS a long time ago...)- I did
> have to create a one-row/one-column table that I pinned in memory to make
> it run faster.
>
> Same thing here - keep your transactions VERY short so as not to cause too
> much contention.
>
> here is the full syntax and examples:
> http://www.psoug.org/reference/sequences.html
>
> Adn then test to make sure you are satisfied with the results.
Of course, the problem with no gaps is that if a transaction fails then
there will be a gap.
Jim
Received on Mon Feb 02 2009 - 22:29:56 CST