Re: How to create pl/sql that run efficiently in background ?
From: <krislioe_at_gmail.com>
Date: Mon, 2 Feb 2009 20:49:40 -0800 (PST)
Message-ID: <be00d4ce-40d7-4c71-a8d3-0f20ee895039_at_w1g2000prk.googlegroups.com>
On Feb 3, 11:29 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Michael Austin" <maus..._at_firstdbasource.com> wrote in message
>
> news:owPhl.15932$yr3.8304_at_nlpi068.nbdc.sbc.com...
>
>
>
>
>
> > 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:
> >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- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 2 Feb 2009 20:49:40 -0800 (PST)
Message-ID: <be00d4ce-40d7-4c71-a8d3-0f20ee895039_at_w1g2000prk.googlegroups.com>
On Feb 3, 11:29 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Michael Austin" <maus..._at_firstdbasource.com> wrote in message
>
> news:owPhl.15932$yr3.8304_at_nlpi068.nbdc.sbc.com...
>
>
>
>
>
> > 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:
> >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- 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.
Thank you
xtanto
Received on Mon Feb 02 2009 - 22:49:40 CST