Re: How to create pl/sql that run efficiently in background ?
Date: Tue, 03 Feb 2009 07:09:03 GMT
<krislioe_at_gmail.com> wrote in message
On Feb 3, 11:29 am, "gym dot scuba dot kennedy at gmail"
> "Michael Austin" <maus..._at_firstdbasource.com> wrote in message
> > krisl..._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:
> > 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- Hide quoted text -
> - Show quoted text -
Yes, the requirement does not tolerate gap, that is why we use the 'old' solution using a special table/column for the counter.
That is also why we want to put the process of the numbering into a stand alone session (background process)
I think of DBMS_SCHEDULER, but how to prevent it hogging cpu utilzation, because it has to always run to give number the unnumbered transactions.
It is a dumb requirement dreamed up by a moron. In the real world this
never is a requirement. If you have a check book there can be instances
where there are gaps. Someone spills coffee on a check. Someone loses a
check book. A physical location has a fire and some checks are lost. You
now have gaps. Big woop.
Jim Received on Tue Feb 03 2009 - 01:09:03 CST