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:

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…

10 Comments

  1. September 1, 2010    

    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

    • 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…

      Cheers,
      Chris

  2. jerome's Gravatar jerome
    June 8, 2011    

    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.

  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.

    Cheers,
    Chris

  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.

      Cheers,
      Chris

  5. jerome's Gravatar jerome
    July 21, 2011    

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

  6. Alin's Gravatar Alin
    October 29, 2012    

    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

    • Christian Antognini's Gravatar 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…

      HTH
      Chris

      • Alin's Gravatar 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

  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

Leave a Reply

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