As clearly stated in the Licensing Information guide, all features related to parallel processing (parallel backup and recovery, parallel query/DML, parallel statistics gathering, parallel index build/scans, parallel Data Pump export/import, in-memory parallel execution, parallel statement queuing and parallel spatial index builds) are only available with the Enterprise Edition. However, as of Oracle Database 11g Release 2, there is a feature that provides parallel processing capabilities in the Standard Edition as well. This feature is available through the DBMS_PARALLEL_EXECUTE package.
For example, let’s say that you have to execute an UPDATE statement on all rows of a table containing a huge amount of data. If you can take the table “offline”, provided you have enough space doing a CTAS statement instead of an UPDATE statement is probably much faster. However, if you cannot put the table “offline”, doing it in parallel might be a sensible way to speed-up the execution. Hence, if you are using the Enterprise Edition you can take advantage of the parallel processing features integrated in the SQL engine. Thus, you can execute something like that:
ALTER SESSION ENABLE PARALLEL DML; UPDATE /*+ parallel(t,4) */ t SET col = expr;
Since such a possibility is not available with the Standard Edition, as of Oracle Database 11g Release 2 you might execute a PL/SQL block like the following one to perform the same operation in parallel:
SET SERVEROUTPUT ON DECLARE l_task_name user_parallel_execute_tasks.task_name%TYPE; l_sql_stmt user_parallel_execute_tasks.sql_stmt%TYPE; BEGIN l_task_name := 'px_update'; l_sql_stmt := 'UPDATE t SET col = expr WHERE rowid BETWEEN :start_id AND :end_id'; dbms_parallel_execute.create_task(task_name => l_task_name); dbms_parallel_execute.create_chunks_by_rowid( task_name => l_task_name, table_owner => user, table_name => 'T', by_row => FALSE, chunk_size => 128 ); dbms_parallel_execute.run_task( task_name => l_task_name, sql_stmt => l_sql_stmt, language_flag => dbms_sql.native, parallel_level => 4 ); WHILE (dbms_parallel_execute.task_status(task_name => l_task_name) NOT IN ( dbms_parallel_execute.chunking_failed, dbms_parallel_execute.finished, dbms_parallel_execute.finished_with_error, dbms_parallel_execute.crashed )) LOOP dbms_lock.sleep(1); END LOOP; CASE dbms_parallel_execute.task_status(task_name => l_task_name) WHEN dbms_parallel_execute.chunking_failed THEN dbms_output.put_line('chunking_failed'); WHEN dbms_parallel_execute.finished THEN dbms_output.put_line('finished'); WHEN dbms_parallel_execute.finished_with_error THEN dbms_output.put_line('finished_with_error'); WHEN dbms_parallel_execute.crashed THEN dbms_output.put_line('crashed'); END CASE; dbms_parallel_execute.drop_task(task_name => l_task_name); END; /
Note that using the DBMS_PARALLEL_EXECUTE package is not limited to the Standard Edition, though. I see at least two situations where it can be handy with the Enterprise Edition:
- You do not want to process the whole DML statement in a single transaction.
- You want to process in parallel a PL/SQL block, not a DML statement.
Both situations are relevant if, as of Oracle Database 11g Release 2, you plan to perform such an operation during an online application upgrade by taking advantage of edition-based redefinition. I guess that the package was implemented for that purpose…
Hi,
Could it be that it is the same thing with DBMS_PARALLEL_EXECUTE as with AWR?
I mean, the feature is there and it’s working, but we are not allowed to use it until we have the proper licenses?
Maris
Hi Maris
IMO it is not the case. Otherwise the Licensing Information guide should point out that the feature is not available with the Standard Edition.
Also note that from a technical point of view the implementation does not use the “regular” parallel processing features. It simply takes advantage of the scheduling engine…
Cheers,
Chris
Hi Christian, do you know if this tip works under 11.2.0.2 ? For me, it doesn’t seems to work. Jobs are sucessfull but data is not updated…
Thanks a lot.
Jerome.
Hi Jerome
If you did a test with the example provided in my post, there was an error… Sorry about that. Now I fixed it and, at the same time, I slightly improved the error handling too.
Cheers,
Chris
Hi, I tried this code but my query stocked. I run the query only for few records again it stocked. I am doing a simple update on a table with 10 records. Any suggestion?
Hi
You should check the event the session is waiting for. E.g. you could run a query like “select * from v$session_event where sid = …”.
HTH
Chris
Hi, this is great. I’ve been trying to find a way to do some level of parallel processing on Standard Edition. SE’s licensing allows us to throw in as many cores as we like as long as we’re not exceeding 4 sockets. And I’ve been trying to find a way to maximize the usage of these cores. So this is great.
Does any one know of a easy way to break up a big query into multiple queries that can be fired to an Oracle SE RAC cluster in parallel and then it’ll merge the results together? This is effectively parallel query.
Hi Brian
> Does any one know of a easy way to break up a big query into multiple queries that
> can be fired to an Oracle SE RAC cluster in parallel and then it’ll merge the results together?
Since queries commonly contain GROUP/ORDER BYs as all, I do not see an easy way to do it.
Cheers,
Chris
Thanks for the fix, I’ll try it again ;-)
[…] 11.2 introduced in-memory parallel execution. The book author briefly mentions this feature in one of his blog articles. (page […]
[…] possible that you could improve load performance by leveraging more CPUs using something like the DBMS_PARALLEL_EXECUTE package or by simply running multiple loads simultaneously. Answered by Justin […]
Hi Christian, I have two question.
1.
I see this hint in too many documents and blogs: /*+ rowid (dda) */
I don’t understand why dda? Why not the table alias?
2.
I read that the dbms_parallel_execute very good at big updates and could be faster than pdml update.
I did several tests but I couldn’t able to create such a test case where update is faster with dbms_parallel_execute than with pdml.
Could you give me some concrete examples where dbms_parallel_execute faster than pdml?
Yours Sincerely
Hi Alin
1) It’s wrong. It should be a table alias. I guess is there because everyone copy/pasted what is incorrectly provided in the documentation.
2) No, I’m not able. I do not see a good reason for that…
HTH
Chris
Hi Christian,
Thank you for very quick answer.
It’s important for me, because I will have to speak on Oracle Day and I think the dbms_parallel_execute pkg will be a very good topic.
Of course, I want to tell only a good information about it.
Yours Sincerely
[…] from Oracle on Parallel Execution fundamentals is good. Christian Antognini has also written an article on Parallelism in Standard Edition. Both are recommended […]
Parallel backup, restore, copy, and duplicate database operations with Oracle Standard Edition (SE) and RMAN can be performed just as fast as with Enterprise Edition (EE) by using a commercial product called RmanJ. It controls several invocations of RMAN in parallel. Thus you can use 8, 16, 32 or more channels and the speedup is the same as with a single RMAN invocation with multiple channels in Enterprise Edition.
Actually RmanJ is more than just a parallelization tool for SE. It is a feature-rich general-purpose RMAN-based backup tool that works with both SE and EE. It provides locking to protect against accidental concurrent backups. It supports the use of the new SYSBACKUP user introduced with 12c. For security-minded shops it offers password-based authentication for target, auxiliary, and catalog databases with encrypted passwords. It has built-in timing and reporting features that log elapsed times for all operations and report the data volume of backup and restore operations.
Last but not least RmanJ may be used to implement a standby database with Oracle Standard Edition since it is capable of shipping archived logs to a standby database and applying them with or without a delay. All of this is achieved by leveraging documented RMAN functionality.
Additional information is available on the vendor’s website at http://www.oradbpro.com/rmanj.html.
Hi Norbert
It goes without saying that it’s always possible to parallelize outside of the Oracle Database kernel. Actually, that is also what the DBMS_PARALLEL_EXECUTE package does. But, it’s essential to stress that such a parallelization doesn’t use the parallel features provided by Oracle Database.
Cheers,
Chris
[…] multiple sessions to inspect ranges of data, or perhaps by leveraging the DBMS_PARALLEL package as Christian Antognini explored in his blog post on the subject, although his examples focused mainly on updates to existing […]
[…] https://antognini.ch/2010/09/parallel-processing-with-standard-edition/ […]
[…] Parallel Processing With Standard Edition […]