Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feed.tweaknews.nl!postnews.google.com!w1g2000prk.googlegroups.com!not-for-mail From: krislioe@gmail.com Newsgroups: comp.databases.oracle.server Subject: Re: How to create pl/sql that run efficiently in background ? Date: Mon, 2 Feb 2009 20:49:40 -0800 (PST) Organization: http://groups.google.com Lines: 90 Message-ID: References: <640a561b-529e-4a0b-b1af-726cdb0890f2@z27g2000prd.googlegroups.com> <14bf69ad-ac27-4119-9e67-accdb78d483d@n33g2000pri.googlegroups.com> <8LPhl.927$eK2.17@nwrddc01.gnilink.net> NNTP-Posting-Host: 117.102.81.202 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1233636580 20532 127.0.0.1 (3 Feb 2009 04:49:40 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 3 Feb 2009 04:49:40 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: w1g2000prk.googlegroups.com; posting-host=117.102.81.202; posting-account=RjnUyQoAAACCiWrPIXa2p7GWSnLLYQ8D User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Feb 3, 11:29=A0am, "gym dot scuba dot kennedy at gmail" wrote: > "Michael Austin" wrote in message > > news:owPhl.15932$yr3.8304@nlpi068.nbdc.sbc.com... > > > > > > > krisl...@gmail.com wrote: > >> On Feb 3, 10:43 am, Palooka wrote: > >>> krisl...@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? =A0if no, then: > > > CREATE SEQUENCE supplier_seq > > =A0 =A0 MINVALUE 1 > > =A0 =A0 START WITH 1 > > =A0 =A0 INCREMENT BY 1 > > =A0 =A0 NOCACHE; > > > Nocache means that none of the sequence values are stored in memory. Th= is > > option may sacrifice some performance, however, you should not encounte= r 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 d= id > > have to create a one-row/one-column table that I pinned in memory to ma= ke > > 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