Home » SQL & PL/SQL » SQL & PL/SQL » Threading in Pl/SQL
icon4.gif  Threading in Pl/SQL [message #271885] Wed, 03 October 2007 05:07 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Threading in Pl/SQL [message #271931 is a reply to message #271885] Wed, 03 October 2007 06:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Threading in Pl/SQL [message #272616 is a reply to message #271885] Fri, 05 October 2007 13:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Threading in Pl/SQL [message #272618 is a reply to message #272616] Fri, 05 October 2007 13:42 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Threading in Pl/SQL [message #272624 is a reply to message #272618] Fri, 05 October 2007 13:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Threading in Pl/SQL [message #272631 is a reply to message #272624] Fri, 05 October 2007 14:35 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Threading in Pl/SQL [message #272638 is a reply to message #272631] Fri, 05 October 2007 14:54 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Threading in Pl/SQL [message #273173 is a reply to message #272638] Tue, 09 October 2007 06:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Threading in Pl/SQL [message #273333 is a reply to message #273180] Wed, 10 October 2007 00:59 Go to previous messageGo to next message
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.
Re: Threading in Pl/SQL [message #274114 is a reply to message #273333] Sat, 13 October 2007 23:32 Go to previous messageGo to next message
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
Re: Threading in Pl/SQL [message #274116 is a reply to message #271885] Sat, 13 October 2007 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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.
Re: Threading in Pl/SQL [message #274118 is a reply to message #274114] Sun, 14 October 2007 01:14 Go to previous messageGo to next message
Frank
Messages: 7880
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
Re: Threading in Pl/SQL [message #274689 is a reply to message #271885] Tue, 16 October 2007 18:53 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: triggers
Next Topic: drop all tables belong to a user
Goto Forum:
  


Current Time: Wed Dec 07 05:18:16 CST 2016

Total time taken to generate the page: 0.11694 seconds