Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feed.tweaknews.nl!postnews.google.com!n33g2000pri.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:02:47 -0800 (PST) Organization: http://groups.google.com Lines: 30 Message-ID: <14bf69ad-ac27-4119-9e67-accdb78d483d@n33g2000pri.googlegroups.com> References: <640a561b-529e-4a0b-b1af-726cdb0890f2@z27g2000prd.googlegroups.com> 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 1233633767 11670 127.0.0.1 (3 Feb 2009 04:02:47 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 3 Feb 2009 04:02:47 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: n33g2000pri.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, 10:43=A0am, 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