Re: How to create pl/sql that run efficiently in background ?
From: gazzag <gareth_at_jamms.org>
Date: Tue, 3 Feb 2009 03:05:45 -0800 (PST)
Message-ID: <ae6ec398-7f84-4e5e-81ed-a89a65e84c5d_at_e18g2000vbe.googlegroups.com>
On 3 Feb, 04:49, krisl..._at_gmail.com wrote:
> 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
Date: Tue, 3 Feb 2009 03:05:45 -0800 (PST)
Message-ID: <ae6ec398-7f84-4e5e-81ed-a89a65e84c5d_at_e18g2000vbe.googlegroups.com>
On 3 Feb, 04:49, krisl..._at_gmail.com wrote:
> 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
I agree with Jim. Very often the "no gap" rule is dreamt up by someone who hasn't really thought it through. I have never encountered a situation where this turns out to be a genuine requirement upon closer scrutiny. Oracle sequences are a solution that you're refusing to use because someone in your organisation is not thinking properly.
HTH -g Received on Tue Feb 03 2009 - 05:05:45 CST