Parallel Processing With Standard Edition

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:

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:

  l_task_name user_parallel_execute_tasks.task_name%TYPE;
  l_sql_stmt user_parallel_execute_tasks.sql_stmt%TYPE;
  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);

    task_name   => l_task_name,
    table_owner => user,
    table_name  => 'T',
    by_row      => FALSE,
    chunk_size  => 128

    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 (

  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');

  dbms_parallel_execute.drop_task(task_name => l_task_name);

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…


  1. September 1, 2010    


    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?


    • September 1, 2010    

      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…


  2. jerome jerome
    June 8, 2011    

    Hi Christian, do you know if this tip works under ? For me, it doesn’t seems to work. Jobs are sucessfull but data is not updated…
    Thanks a lot.

  3. June 14, 2011    

    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.


    • Shahram Shahram
      May 30, 2014    

      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?

      • May 30, 2014    


        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 = …”.


  4. July 19, 2011    

    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.

    • July 20, 2011    

      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.


  5. jerome jerome
    July 21, 2011    

    Thanks for the fix, I’ll try it again ;-)

  6. Alin Alin
    October 29, 2012    

    Hi Christian, I have two question.
    I see this hint in too many documents and blogs: /*+ rowid (dda) */
    I don’t understand why dda? Why not the table alias?
    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

    • Christian Antognini Christian Antognini
      October 30, 2012    

      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…


      • Alin Alin
        October 30, 2012    

        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

  7. April 28, 2016    

    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

    • April 28, 2016    

      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.


No Pings Yet

  1. Book Review: Troubleshooting Oracle Performance (Part 2) « Charles Hooper's Oracle Notes on November 8, 2011 at 12:35
  2. How does Oracle Database Standard Edition (One) benefit from running on a dual CPU server? - Admins Goodies on April 6, 2012 at 11:33
  3. Parallelism in Standard Edition? – Part 1 | Oracle Standard Edition – Just love it! on July 16, 2015 at 14:40

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.