Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Sure is...
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()
--Received on Fri Jun 08 2001 - 13:17:45 CDT
-- 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;