Re: asynchronous PL/SQL development; DBMS_AQ ??
- From: Cosmin Ioan <cosmini@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 1 May 2007 14:54:55 -0700 (PDT)
ahh... I knew I had read it somewhere, already...
that's what happens when you have 30+ books on Oracle-everything ;-) ...
you loose track of "who-dunnit" ;-)
thx for the reference reminder, Alberto ;-)
Alberto Dell'Era <alberto.dellera@xxxxxxxxx> wrote:
Oh yes. A variant (piped servers) is illustrated on Tom Kyte's
"Expert One on One: Oracle", page 1038 - it should fit
Cosmin's requirements quite well, better than AQ probably.
On 5/1/07, Christo Kutrovsky wrote:
> Hi Cosmin,
>
> You can use dbms_pipe to communicate and dbms_lock to synchronize.
>
> I used them to create a parallel stats gathering package for standard
> edition Oracle. There would be a master process that distributes work
> amongs multiple "worker" processes.
>
> --
> Christo Kutrovsky
> Senior Database/System Administrator
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
>
> On 4/27/07, Cosmin Ioan wrote:
> > hi Alberto, thx much for the reply --
> >
> > what I'm actually looking for is an extremely fast/responsive AQ mechanism
> > for a scenario where we have a big module that could be (written) in a
> > modular fashion. This module contains many snippets of code that could be
> > written asynchronously, but at the end, I need to retrieve data, bits and
> > pieces, from each sub-procedure.
> >
> > This **entire** main procedure may take 1-3 seconds, but in a large OLTP
> > fast-transact environment, squeezing/parallelizing any operations that could
> > be deemed asynchronous could be a life-saver.
> >
> > don't think that dbms_job is fast enough to account for milli or
> > microseconds.... pooling every second or so, is definitely something we
> > can't entertain....
> >
> > Any thoughts?
> > thx,
> > Cos
> >
> > Alberto Dell'Era wrote:
> > If I have understood what you want - I usually use dbms_job
> > for this, in a nutshell
> >
> > create table remaining (counter int);
> > insert into remaining (counter) values (2);
> > dbms_job.submit ( ... proc_1 );
> > dbms_job.submit ( ... proc_2 );
> > commit;
> >
> > each proc will decrement counter upon completion (ok/ko).
> >
> > Then in the "main thread" (the session that fired the jobs)
> > i simply poll, say every second, table "remaining" until counter is zero.
> >
> > You can get as sophisticated as you want from here (logging
> > proc errors in the table as well is quite useful for example).
> >
> > I think that examples of this technique can be found on asktom,
> > I can search there for you if you want.
> >
> > hth
> > alberto
> >
> > On 4/27/07, Cosmin Ioan wrote:
> > > hi all,
> > > what's the best & most robust package these days, whether Oracle supplied
> > > (DBMS_AQ?) or third party, to do asynchronous PL/SQL development (9i or
> > > 10g). I have various scenarios, multiple procedures that I'd like to fire
> > > at once and upon their validated completion to do some further
> > computations.
> > >
> > > thx much,
> > > Cos
> > >
> > >
> > >
> >
> >
> > --
> > Alberto Dell'Era
> > "dulce bellum inexpertis"
> >
> >
>
>
> --
> Christo Kutrovsky
> Senior Database/System Administrator
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Alberto Dell'Era
"dulce bellum inexpertis"
Other related posts:
