Blog
News
Downloads
Links
FAQ
Guestbook
PL-SQL parallel execution
Category : ORACLE-SweetDreams - by webMaster_alaindereninfo
Oracle - Sweet Dreams PL-SQL parallel execution
Note: "Sweet Dreams are made of this..."
A "Sweet Dreams" article concerns an improvement, a new feature, that I would like to come alive in a next release of Oracle.
With Oracle, it's possible to launch executions of SELECT in parallel mode. The same for index creation and some other operations.
It exists for a long time, but nothing for PL/SQL.
For servers, it's a long time that exist multi-processors, but for PL/SQL programs, they work only on one processor at a time.
A workaround is possible using DBMS_SCHEDULER (or old DBMS_JOB). But it can be tricky to write/maintain and test this type of job.
Another workaround exists for TABLE PARALLEL EXECUTION in 11gR2 with the package "DBMS_PARALLEL_EXECUTE".
The feature is perfect, for example, to execute parallel UPDATE in the same table. But for independant actions, it's useless.
Have look in this excellent article: http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30plsql-086044.html
I imagine the following extension in PL/SQL to take advantage of parallel execution of programs.
For a PL/SQL (block, procedure/function of a package or independent).
a) Search into Table TABLE1, a certain amount of lines regarding some criterias.
b) Search into Table TABLE2, other amount of lines regarding other criterias.
c) Search into Table TABLE3, another amount of lines regarding another criterias.
d) Compare the result of a), b) and c). And depending of the result (equality, NULL, lower, upper, ...), a new step is executed.
In a classical PL/SQL, the operations should be serialized: First a), second b), and finishing with c).
But a), b) and c) are independant operations, in PL/SQL it's no possibilities to execute them in parallel.
It's possible to use workaround, described before. But the code is heavy to write and maintain, and not obvious for a "standart" developper.
Here is the code, I dream as extension of PL/SQL:
DECLARE
l_result1 NUMBER ;
l_result2 NUMBER ;
l_result3 NUMBER ;
BEGIN
...
BEGIN PARALLEL
SELECT COUNT(*) INTO l_result1 FROM table1 WHERE ... ;
SELECT COUNT(*) INTO l_result2 FROM table2 WHERE ... ;
SELECT COUNT(*) INTO l_result3 FROM table3 WHERE ... ;
END PARALLEL ;
--
IF ( l_result1 IS NULL )
THEN
...
ELSE
IF ( l_result1 = ( l_result2 + l_result3 ) )
THEN
ELSE
IF ( l_result1 > ( l_result2 + l_result3 ) )
THEN
..
ELSE
..
END IF ;
END IF ;
END IF ;
...
EXCEPTION
...
END ;
Explanations:
(1) BEGIN PARALLEL
(2) SELECT COUNT(*) INTO l_result1 FROM table1 WHERE ... ;
(3) SELECT COUNT(*) INTO l_result2 FROM table1 WHERE ... ;
(4) SELECT COUNT(*) INTO l_result3 FROM table3 WHERE ... ;
(5) END PARALLEL ;
(1) prepare the block for parallel execution.
(2), (3), (4) are declaration of statements, which are not executed until the step (5) is not reached.
(5) closes the declaration of statements, and run the execution of (2), (3) et (4) in parallele in differents process. The step (5) is ended when all previous are finished.
Limitations:
1) Too avoid to lauch too much process in parallel, a instance parameter MAX_PARALLEL_PLSQL_EXECUTION should exist. This parameter should be equal, by default, to the number of processors available divided by 2. On a 8 processor machine, the default value should be equal to 4. If a PL/SQL try to the execution of 5 processes, only 4 will start, and the last one will one when one of the fourth is finished.
2) One parallel block at a time: No overlapping of parallel block inside a block declared as parallel.
Creation date : 07/07/2011 : 17:53
Page read 390434 times
Document generated in 1.5 seconds
