Adding DML Parallelization to Oracle Jobs

When investigating why an Oracle job is taking a long time to execute, normally the DBA will identify the DML instructions or select queries running longer and different approaches might be used to improve the performance, e.g., create new indexes, gather statistics, use of hints, rewrite the queries or other convenient solutions.

One more option could be modifying the Oracle job to run in parallel (at the session level). Below is a small test carried out to show how a job can use Parallel DML where just the job action needs to be modified. The test was performed in a virtual box running Linux Red Hat 7.7 and Oracle Database Enterprise Edition 12.2.0.1.

1. Create a test table using the dba_objects as data source:

show user
USER is "DBUSER1"
create table abc tablespace users as select * from dba_objects;

2. Verify the current ABC table size, just 11 MB after the first load:

select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024) bytes_MB, 
extents, MAX_EXTENTS  from dba_segments
where 
owner = 'DBUSER1' and
segment_name in ('ABC'); 

OWNER                SEGMENT_NA SEGMENT_TY TABLESPACE_NAME        BYTES_MB    EXTENTS MAX_EXTENTS
-------------------- ---------- ---------- -------------------- ---------- ---------- -----------
DBUSER1              ABC        TABLE      USERS                        11         26  2147483645

3. Create a simple procedure to load data into a test table (insert as select instruction):

CREATE or replace PROCEDURE PLOADTEST
is
BEGIN
    insert into abc select * from abc;
    commit;
END;
/   

4. Create a job to run the procedure PLOADTEST, at the beginning it will run the job in serial mode:

BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => 'JOB_TEST',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN 
                            PLOADTEST;  
                            END;',
        start_date 	=> TO_TIMESTAMP_TZ('2021-09-25 01:00:00.000000000 AMERICA/NEW_YORK',
                           'YYYY-MM-DD HH24:MI:SS.FF TZR'),
        repeat_interval => 'FREQ=DAILY;BYHOUR=01;BYMINUTE=00;',
        end_date        => NULL,
        enabled         => TRUE,
        comments        => 'Job to test');
END;
/ 

5. Run the job JOB_TEST a couple of times to populate more data into ABC table:

exec dbms_scheduler.run_job('JOB_TEST',FALSE);

6. Verify the current ABC table size. In the example, its size is now ~1.3GB:

select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024) bytes_MB, 
extents, MAX_EXTENTS  from dba_segments
where 
owner = 'DBUSER1' and
segment_name in ('ABC');  

OWNER                SEGMENT_NA SEGMENT_TY TABLESPACE_NAME        BYTES_MB    EXTENTS MAX_EXTENTS
-------------------- ---------- ---------- -------------------- ---------- ---------- -----------
DBUSER1              ABC        TABLE      USERS                      1344        204  2147483645

7. Check the execution time to complete the job. The latest entry shows three minutes to complete the procedure:

SELECT owner, job_name,status,run_duration FROM dba_scheduler_job_run_details 
WHERE owner = 'DBUSER1' and job_name = 'JOB_TEST'
ORDER BY job_name,actual_start_date;
  
OWNER      JOB_NAME  STATUS      RUN_DURATION
---------- --------- ----------  --------------------
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:00:01
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:00:01
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:00:03
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:00:06
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:00:12
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:00:43
DBUSER1    JOB_TEST  SUCCEEDED   +000 00:03:01

8. Change the Job Action to use parallel DML and four threads. It will depend on how many server configuration–related CPU cores are available and always avoiding extra stress on the system by preventing too many processes from running in parallel. The virtual machine where the test was performed has four CPU cores:

BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
        name         =>  'DBUSER1.JOB_TEST',
        attribute    =>  'job_action',
        value        =>  'BEGIN
                           EXECUTE IMMEDIATE ''ALTER SESSION ENABLE PARALLEL DML'';
                           EXECUTE immediate ''ALTER SESSION FORCE PARALLEL QUERY parallel 4'';
                           PLOADTEST; 
                          END;');
END;
/

9. Run the job again and verify the parallelism usage:

exec dbms_scheduler.run_job('JOB_TEST',FALSE);

select * from v$pq_sysstat;

STATISTIC                   VALUE     CON_ID
---------------------- ---------- ----------
Servers Busy                    4          0

10. Check the execution time to complete the job using parallelism. In the example, it took 51 seconds to complete using parallel DML at the session level:

SELECT owner, job_name,status,run_duration FROM dba_scheduler_job_run_details 
WHERE owner = 'DBUSER1' and job_name = 'JOB_TEST'
ORDER BY job_name,actual_start_date;
 
OWNER      JOB_NAME   STATUS      RUN_DURATION
---------- ---------- ----------  --------------------
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:01
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:01
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:03
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:06
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:12
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:43
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:03:01
DBUSER1    JOB_TEST   SUCCEEDED   +000 00:00:51

11. Verify the current ABC table size. Here, it’s ~2.6GB:

select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024) bytes_MB, 
extents, MAX_EXTENTS  from dba_segments
where 
owner = 'DBUSER1' and
segment_name in ('ABC');
  
OWNER                SEGMENT_NA SEGMENT_TY TABLESPACE_NAME        BYTES_MB    EXTENTS MAX_EXTENTS
-------------------- ---------- ---------- -------------------- ---------- ---------- -----------
DBUSER1              ABC        TABLE      USERS                      2635        338  2147483645

In summary, to load ~1.3GB, the job took 51 seconds when using parallelism, which was faster than its previous run to load ~650MB, where the execution time was three minutes.

Hopefully this will help with job performance improvements, where a quick modification can complete the process faster.

For more helpful tips from the experts at Pythian, subscribe to our weekly blog post emails at the top of this page or here.