Tutorial :Parallelism in PL/SQL



Question:

how can i run one query in pl-sql- in parallel?

i need all the flow...


Solution:1

You can create JOBs in order to run the same query with parallelism.

EXAMPLE

CREATE OR REPLACE PROCEDURE target_deletion  IS     number_of_the_job   NUMBER;  BEGIN     DBMS_JOB.submit (number_of_the_job, 'begin stored_procedure_for_deletion; end;', SYSDATE);  END;  /  

EXPLAINATION

Please suppose you have, in your Oracle DataBase, a stored procedure called exactly as follows:

stored_procedure_for_deletion

If you wish to execute that stored procedure many times with PARALLELISM, you have to create a stored procedure called for example "TARGET_DELETION" (written above), which creates a new job that invokes, with the PL/SQL block:

begin stored_procedure_for_deletion; end;

... the execution of your procedure called "stored_procedure_for_deletion".

The job starts immediately, so you can run the stored procedure target_deletion many consecutive times, in order to run the same procedure with parallelism.


Solution:2

If enabled at instance level, Oracle itself has parallel query features:

http://www.orafaq.com/wiki/Parallel_Query_FAQ

edit: it's not clear what you are trying to do. Maybe you want asynchronous query execution, so the Oracle job suggestion is correct (see the other answer).

Oracle parallel feature is not asynchronous, it just says the optimizer to use a certain number of CPUs in query execution, to speed up the result. For example:

select /*+ PARALLEL(DOGS,4) */ * from DOGS where A>20  

executes your query with parallelism at degree 4.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »