CODEWITHSUNDEEP
sqloracle-databaseplsql
Logesh Varathan
Logesh Varathan

Reputation: 33

Regular Update vs DBMS Parallel execute

I have recently learned DBMS Parallel execute functionality. I tried to test the performance of DBMS parallel execute with the regular update statement. I don't see the performance improvement over the regular update statement. Is there something is missing. Attaching the sample code details.The table consists of 7,020 rows.

DECLARE
  l_sql_stmt VARCHAR2(1000);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
  -- Chunk the table by ROWID
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'REVPRO_1001', 'RPRO_RC_LINE_G', true, 1000);
  -- Execute the DML in parallel
  l_sql_stmt := 'update /*+ ROWID (dda) */ RPRO_RC_LINE_G e       
                  SET e.NUM2 = 100    
                  WHERE rowid BETWEEN :start_id AND :end_id';

  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 5);
  -- If there is an error, RESUME it for at most 2 times.
  L_try      := 0;
  L_status   := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  DBMS_OUTPUT.PUT_LINE('Status'||L_status);
  WHILE(l_try < 2 AND L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  LOOP
    L_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
    L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  END LOOP;
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
END;

The above block takes Elapsed time: 00:00:03.315 to update the table.

BEGIN
  UPDATE /*+ ROWID (dda) */
    RPRO_RC_LINE_G e SET e.NUM2 = 100 ;
END;

Whereas when I update the same table with simple SQL query, It takes elapsed time 00:00:00.370.

Which is 3 seconds faster than the DBMS parallel execute. Can you please help me on this.

Upvotes: 2

Views: 1887

Answers (2)

William Robertson
William Robertson

Reputation: 16001

There is surely a certain amount of overhead in creating the tasks and managing the dbms_scheduler jobs, and for the tiny number of rows in your test, the plain update has less work to do.

I tried it with a million rows, and the plain update took consistently 36 seconds to complete, while the dbms_parallel_execute version varied from 36 seconds to 9 seconds. (This is on my laptop where I was not expecting much gain from parallel execution. My cpu_count = 2, parallel_threads_per_cpu = 2. I saw completion in 6 seconds using 8 threads.)

Upvotes: 2

Kjetil S.
Kjetil S.

Reputation: 3777

So the simple non-parallel update is about 9 times faster. Seems as if the update is limited by disk speed, not CPU. I'm guessing if SET e.NUM2 = 100 was instead SET e.NUM2 = some_heavy_calculation() the parallel solution would be faster. At least if the calculation is heavy enough and CPU-bound. Your Oracle server could also be configured not to take advantage of more than one CPU kernel. Run show parameters as DBA (or user system) to see if the parameters cpu_count and parallel_threads_per_cpu is set to 1. Or check if your old(?) CPU in fact only have one kernel. Try to change parallel_level=>5 into NULL or 2 or some other number. 5 could be too many.

Upvotes: 0

Related Questions