Re: How to create pl/sql that run efficiently in background ?

From: Michael Austin <>
Date: Mon, 02 Feb 2009 22:12:05 -0600
Message-ID: <owPhl.15932$> wrote:
> On Feb 3, 10:43 am, Palooka <> wrote:

>> 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


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:

Adn then test to make sure you are satisfied with the results. Received on Mon Feb 02 2009 - 22:12:05 CST

Original text of this message