Re: How to create pl/sql that run efficiently in background ?
Date: Mon, 02 Feb 2009 22:12:05 -0600
Message-ID: <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. Received on Mon Feb 02 2009 - 22:12:05 CST