Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Sure is...

Sure is...

From: David Sisk <DSisk_at_ehomecare.com>
Date: Fri, 8 Jun 2001 14:17:45 -0400
Message-ID: <9ZEuQdE8AHA.2092@corp01.nc.ehomecare.com>

See below.

Regards,
Dave Sisk
OCP-DBA -----Original Message-----
From: Shibu Mathew [mailto:shibumathew_at_alliantfs.com] Posted At: Thursday, June 07, 2001 12:40 PM Posted To: server
Conversation: Multithreading in Oracle
Subject: Multithreading in Oracle

Hello everyone,
I am relatively new to Oracel PL/SQL programming. I am working in a DataWarehouse group with Informix as RDBMS on HP Unix environment.. We are now migrating from Informix to Oracle. We use mutithreading in Informix to the advantage of our processes to fork multiple processes. Below are few questions I have relative to programming in Oracle. 1.Can anyone share your knowledge with me as to how to implement Multithreading in Oracle PL/SQL programs. I know it is possible using Java but how do you do this in native PL/SQL. [Sisk] Database sessions are single-threaded. PL/SQL is also single-threaded per se, however, there's ways you can multi-task (just like you would with any other serialized language). Read the Oracle docs about a built-in package called DBMS JOB. I'll also post a note from the Metalink site that provides an example of spawning multiple jobs using DBMS JOB, then serializing them at the end. You could also write an external process to launch multiple instances of a stored proc, etc (Perl would probably be a good choice). Actually, Java does support threads and multi-tasking, but if you load a multi-threaded Java class into, and run it in. the database, JServer (the database JVM) serializes it.

2. Does table fragmentation have any effect on multithreading? [Sisk] Not a simple question, it depends on a whole slew of factors. Read the Oracle docs, that's a good start.

3. Is there a tool/command in Oracle, I can use to view the processes running on the system. (similar to 'onstat -g ses' on Informix) select * from v$session.

I appreciate any opinion on the above topics. Thank You

Shibu Mathews

Overview


PL/SQL does not directly support multitasking. This article provides a workaround using the DBMS JOB package to spawn another process.

Additional Search Words


 

PLSQL MULTITHREAD MULTITHREADING MULTITASK MULTITASKING DBMS JOB Solution: USE DBMS JOB PACKAGE TO SPAWN ANOTHER PROCESS

Workaround Description


 

Since PL/SQL does not support multithreading, communications between tasks
cannot be direct. A way to multitask is to create a stored procedure to handle
an independent task that is being forked. Inside of your PL/SQL routine, you
cannot pass back values directly to the calling routine. You can, however,
store the values in a database table and run a synchronize check to see if
the task has completed by polling for the pid entry in the table.

Below is an example, fork.sql, to create a routine that forks a procedure
(allowing multitasking). Also included is a sample routine to be called by
the fork, as well as a new table that the table will write to.

The script, when run, performs the necessary creations (fork(), forktable,
runme()), and then executes a simple anonymous pl/sql block to fork runme()
and attempts to synchronize (just test to see if it is completed).

It is recommended that a time test be set up as part of the loop condition
(synchronization) to avoid infinite looping. The example below does not

include this.

Example


<<<---fork.sql--->>>
-- fork()

--

-- forks a procedure under a separate 'thread'. This call will be
executed
-- as a different process and contact will be broken.
--
-- Note: if you do not semicolon terminate the procedure call, you
will get
-- ORA-6512 runtime errors with DBMS JOB
--
-- input parameters:
-- procname: the string value of the procedure call. Note: no value
will
-- be returned from procedure.
-- output parameters:
-- pid: as return value. The job number as specified by the sequence
-- sys.jobseq
-- Note: The code below uses a null job to do a run immediate. If the
-- init<sid>.ora parameter JOB QUEUE KEEP CONNECTIONS is set to true,
this
-- portion is not required. If the SNP processes are in sleep mode,
they
-- will not automatically detect a new job for execution.
create or replace function fork( procname varchar2 ) return binary integer as pid binary integer; nid binary integer; begin dbms job.submit( pid, procname, sysdate, null, false ); dbms job.submit( nid, 'null;', sysdate, null, false ); dbms job.run( nid ); return pid; end; /
-- table forktable
-- a sample table to contain data written by runme()
create table forktable ( timestamp date, idnum number, parmstr varchar2(200)) /
-- runme()
-- a sample procedure to demonstrate fork(). Just writes a value
into
-- forktable for demonstration.
-- input parameters:
-- idnum: a 'process' number for synchronizing
-- output parameters:
-- none
create or replace procedure runme( idnum in number ) as begin insert into forktable values (sysdate, idnum, 'runme() executed'); end; / set serveroutput on --
-- usage
-- declare id binary integer; parm varchar2(200); dt date; done boolean:= false; cursor c1 is select parmstr, timestamp from forktable where idnum = 3; begin id:= fork( 'runme( 3 );' ); dbms output.put line( id ); -- attempt to synchronize -- it is recommended that you do time tests -- for a timeout factor while not done loop open c1; fetch c1 into parm, dt; if c1%found then done:= true; end if; close c1; end loop; dbms output.put line( dt ); dbms output.put line( parm ); end;
Received on Fri Jun 08 2001 - 13:17:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US