OraFAQ Forum: SQL & PL/SQL » Threading in Pl/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Threading in Pl/SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Threading in Pl/SQL [message #271885]

Wed, 03 October 2007 05:07

saravanan_nitt
Messages: 31
Registered: October 2007
Location: Bangalore

Member

hi all,
I am a new to this forum.
any one help me in using multi threaded programming in pl/sql block. First is that possible to do threading in plsql,
if so then explain me how to achieve that.

Thanks in advance.

[mod-edit] illiterate IM speak removed.

[Updated on: Wed, 03 October 2007 08:03] by Moderator

Report message to a moderator

Re: Threading in Pl/SQL [message #271910 is a reply to message #271885]

Wed, 03 October 2007 06:23

Frank
Messages: 7901
Registered: March 2000

Senior Member

The short answer is no, PL/SQL is not multi-threaded.
You could however use job-queueing to create parallel (asynchronous) processes.

Report message to a moderator

Re: Threading in Pl/SQL [message #271931 is a reply to message #271885]

Wed, 03 October 2007 06:57

saravanan_nitt
Messages: 31
Registered: October 2007
Location: Bangalore

Member

Thanks for reply....
mean to say that there is no direct way to achieve multi threading.
can help out doing this with job queuing technique.
Thanks in advance.....

[mod-edit] illiterate IM speak removed.

[Updated on: Wed, 03 October 2007 08:02] by Moderator

Report message to a moderator

Re: Threading in Pl/SQL [message #271935 is a reply to message #271931]

Wed, 03 October 2007 07:05

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It's not an immediate method.
You have to (for instance, it depends on your final needs):
- create a pipe on which the coordinator will wait
- cut your work in several pieces, each piece will end with a signal to the pipe
- execute dbms_job.submit for each work
- wait for all signals

And of course correctly set the parameters to execute jobs.

Regards
Michel

Report message to a moderator

Re: Threading in Pl/SQL [message #272616 is a reply to message #271885]

Fri, 05 October 2007 13:37

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

by multi-threaded we take you to mean, the ability to do parallel processing from a single plsql job. Oracle does not yet support parallel branching in plsql.

You can however rewrite your code to take advantage of Oracle's parallel DML if you like. Or you can as was suggested by previous responders, create your own parallel workloads. This is however a lot of work and prone to problems with deadlock if not done well.

Check out asktomhome website. He has an example of this.

Good luck, Kevin

Report message to a moderator

Re: Threading in Pl/SQL [message #272618 is a reply to message #272616]

Fri, 05 October 2007 13:42

Frank
Messages: 7901
Registered: March 2000

Senior Member

Running processes in parallel might cause locking problems, although the splitting mechanism should take this into account.
Deadlocking however, should NEVER happen. It is the result of a poor locking strategy.

Report message to a moderator

Re: Threading in Pl/SQL [message #272624 is a reply to message #272618]

Fri, 05 October 2007 13:56

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Of course, yet my experience in building parallel job steam management code is that even when the data is split well, there are often reference tables and control tables that get updated. All to often, the same set of reference rows will be updated and/or created by several of the parallel jobs which can easily result in deadlock.

Kevin

Report message to a moderator

Re: Threading in Pl/SQL [message #272631 is a reply to message #272624]

Fri, 05 October 2007 14:35

Frank
Messages: 7901
Registered: March 2000

Senior Member

Deadlocks are a result of poor locking strategy and nothing else.
You should always lock tables in a certain order, starting at a given level.
If every process applies to that rule, you will not see any deadlocks ever.
It is not a coincidence that a deadlock is logged in the alert-log, that is because it is something that is a critical error and should never occur.

Report message to a moderator

Re: Threading in Pl/SQL [message #272638 is a reply to message #272631]

Fri, 05 October 2007 14:54

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Yes thanks, that is the theory. I also know the difficulties of its practice.

The best a single developer can normally do is make sure his own code follows the process correctly. Yet I see so few people doing transtion design these days. Most programmers I meet don't even know what "transaction design" is. I can't recall the last time I saw a programmer actually code a "select for update" type of query.

So I hold little hope of the "ordinary" developer taking the time and effort to construct the proper controls in his/her code to make sure deadlocking does not occur when attempting to redesign a batch process to work as a parallel batch process.

As I said in my first post, building a parallel management control structure is do-able, but a lot of work for those of us who do not tread the path often.

Your summary of the rules should open some eyes, thanks.

Of course, even when the rules are applied, the result is often what appears to be a "hung" system, or an alternative "time-out waiting for resource" type of error. More sauce for the concurrent goose I suppose. In the end, even a clear set of rules may not make for a cut and dry end to a discussion.

Kevin

Report message to a moderator

Re: Threading in Pl/SQL [message #273173 is a reply to message #272638]

Tue, 09 October 2007 06:06

saravanan_nitt
Messages: 31
Registered: October 2007
Location: Bangalore

Member

hi....
i little busy, with my job...
thanks for the response.
actually one of my friend in TCS
worked on threading concept for pl/sql block.
, provide sample code or tutorial for the above specified one.
Thanks...

[mod-edit] removed illiterate IM speak.

[Updated on: Tue, 09 October 2007 12:27] by Moderator

Report message to a moderator

Re: Threading in Pl/SQL [message #273180 is a reply to message #273173]

Tue, 09 October 2007 06:50

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Tell him to provide you his code.

Kevin Meade wrote on Fri, 05 October 2007 20:37

Check out asktomhome website. He has an example of this.

Did you do that?

Regards
Michel

Report message to a moderator

Re: Threading in Pl/SQL [message #273333 is a reply to message #273180]

Wed, 10 October 2007 00:59

saravanan_nitt
Messages: 31
Registered: October 2007
Location: Bangalore

Member

sorry firend........
he moved to some other project. so he cant get he code.
he said that he will send the tutorials.

Report message to a moderator

Re: Threading in Pl/SQL [message #274114 is a reply to message #273333]

Sat, 13 October 2007 23:32

ravichandran_123
Messages: 13
Registered: October 2007
Location: Bangalore

Junior Member

If you are planning to process the data in multi-threading, you can divide the transactions in to different sets and run the sets parallely from Unix in different threads using "nohup".

Ravi

Report message to a moderator

Re: Threading in Pl/SQL [message #274116 is a reply to message #271885]

Sat, 13 October 2007 23:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>If you are planning to process the data in multi-threading, you can divide the transactions in to different sets and run the sets parallely from Unix in different threads using "nohup".

Nothing is impossible for the person who does NOT have to do it.

What makes you conclude OP is using *nix?

Locks, INITRANS & other database internals could make this "solution" difficult/challenging/impossible to implement.

Report message to a moderator

Re: Threading in Pl/SQL [message #274118 is a reply to message #274114]

Sun, 14 October 2007 01:14

Frank
Messages: 7901
Registered: March 2000

Senior Member

What would be the benefit of using the OS to do parallel processing, when
- your database code needs to be aware of the fact that it is run in parallel
- you have to use the database to be able to divide the load
- it is very possible/plausible that nothing in the application is started directly from the unix prompt
- the database is very capable of doing it

Report message to a moderator

Re: Threading in Pl/SQL [message #274689 is a reply to message #271885]

Tue, 16 October 2007 18:53

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

OK, since it seems noone looked (thanks for the plug Michel): here is the link to one of the Tom Kyte posts.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10498431232211

Good luck, Kevin

Report message to a moderator

Previous Topic:	triggers
Next Topic:	drop all tables belong to a user

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 19:23:06 CST 2025